檢視:dba_hist_wr_control查詢到兩套庫的awr保留策略

我愛睡蓮發表於2021-06-17

問題描述:有一個問題,有同事在查詢awr報告收集策略的時候,發現有兩個庫的策略,一套自己的,另一套已經找不到屬於誰了,那麼究竟是什麼情景會出現這樣的場景呢?

 

 

 

1.一開始網上找解答也沒有得到解決,詢問技術群和同事得到了兩種解答,得到了兩種比較靠譜的答案。一種是懷疑這個庫之前匯入過AWR報告;一種是說這個庫之前做過不完全恢復,導致遺留了兩種不通的DBID

 

2.測試了匯入匯出AWR報告的方式,將一個庫的AWR報告匯入到另一個庫中,測試中使用的是11g到19c的環境

匯出AWR:@$ORACLE_HOME/rdbms/admin/awrextr.sql

匯入AWR:@$ORACLE_HOME/rdbms/admin/awrload.sql

 

3.11g匯出AWR報告:

SQL> @$ORACLE_HOME/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the       ~
~  following information:                       ~
~     (1) database id                           ~
~     (2) snapshot range to extract                   ~
~     (3) name of directory object                   ~
~     (4) name of dump file                       ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     DB Name      Host
------------ ------------ ------------
* 459838110  PROD      orcl

The default database id is the local one: ' 459838110'.  To use this
database id, press <return> to continue, otherwise enter an alternative.

Enter value for dbid: 459838110

Using 459838110 for Database ID


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days: 10

Listing the last 10 days of Completed Snapshots

DB Name        Snap Id      Snap Started
------------ --------- ------------------
PROD           141 03 Jun 2021 15:16
           142 03 Jun 2021 16:00
           143 11 Jun 2021 11:15
           144 11 Jun 2021 12:06


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 141
Begin Snapshot Id specified: 141

Enter value for end_snap: 144
End   Snapshot Id specified: 144


Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name               Directory Path
------------------------------ -------------------------------------------------
DATA_FILE_DIR               /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
                   ma/sales_history/

DATA_PUMP_DIR               /u01/app/oracle/admin/PROD/dpdump/
GGS_DDL_TRACE               /u01/app/oracle/diag/rdbms/prod/PROD/trace
LOG_FILE_DIR               /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
                   ma/log/

MEDIA_DIR               /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
                   ma/product_media/


Directory Name               Directory Path
------------------------------ -------------------------------------------------
ORACLE_OCM_CONFIG_DIR           /u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts
                   /orcl/state

ORACLE_OCM_CONFIG_DIR2           /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
SS_OE_XMLDIR               /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
                   ma/order_entry/

SUBDIR                   /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
                   ma/order_entry//2002/Sep

XMLDIR                   /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

Choose a Directory Name from the above list (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_141_144.
To use this name, press <return> to continue, otherwise enter
an alternative.
這裡schema名稱必須是oracle資料庫中不存在的,不然會異常退出,必須是兩個資料庫之間沒有的schema名字
Enter value for file_name: AWREXPIMP

Using the dump file prefix: AWREXPIMP
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /u01/app/oracle/admin/PROD/dpdump/
|   AWREXPIMP.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Extract Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   /u01/app/oracle/admin/PROD/dpdump/
|   AWREXPIMP.log
|

Channel(Socket) closed from remote host(ks) at 12:29:37.

 

查詢當前庫的DBID

SQL> select dbid from v$database;

      DBID
----------
 459838110

 

4.匯入AWR到19c庫,將需要匯入的dmp檔案提前放到目標庫的directories下,匯入過程輸入的時候注意不要加檔案字尾,要不然會遇到各種報錯

SQL> @?/rdbms/admin/awrload
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                  ~
~     (2) name of dump file                      ~
~     (3) staging schema name to load AWR data into          ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name               Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR               /u01/app/oracle/admin/test/dpdump/
JAVA$JOX$CUJS$DIRECTORY$       /u01/app/oracle/product/19.2.0/db_1/javavm/admin/
OPATCH_INST_DIR            /u01/app/oracle/product/19.2.0/db_1/OPatch
OPATCH_LOG_DIR               /u01/app/oracle/product/19.2.0/db_1/rdbms/log
OPATCH_SCRIPT_DIR           /u01/app/oracle/product/19.2.0/db_1/QOpatch
ORACLE_BASE               /u01/app/oracle
ORACLE_HOME               /u01/app/oracle/product/19.2.0/db_1
ORACLE_OCM_CONFIG_DIR           /u01/app/oracle/product/19.2.0/db_1/ccr/state
ORACLE_OCM_CONFIG_DIR2           /u01/app/oracle/product/19.2.0/db_1/ccr/state
SDO_DIR_ADMIN               /u01/app/oracle/product/19.2.0/db_1/md/admin
SDO_DIR_WORK
XMLDIR                   /u01/app/oracle/product/19.2.0/db_1/rdbms/xml
XSDDIR                   /u01/app/oracle/product/19.2.0/db_1/rdbms/xml/sch
                   ema


Choose a Directory Name from the list above (case-sensitive).

Enter value for directory_name: DATA_PUMP_DIR

Using the dump directory: DATA_PUMP_DIR

Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:

Enter value for file_name: AWREXPIMP

Loading from the file name: AWREXPIMP.dmp

Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.


The default staging schema name is AWR_STAGE.
To use this name, press <return> to continue, otherwise enter
an alternative.

Enter value for schema_name: AWRUSE

Using the staging schema name: AWRUSE

Choose the Default tablespace for the AWRUSE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWRUSE users's default tablespace.  This is the
tablespace in which the AWR data will be staged.

TABLESPACE_NAME
------------------------------------------------------------
CONTENTS                   DEFAULT TABLESPACE
------------------------------------------ ------------------
SYSAUX
PERMANENT                   *

USERS
PERMANENT


Pressing <return> will result in the recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: USERS

Using tablespace USERS as the default tablespace for the AWRUSE


Choose the Temporary tablespace for the AWRUSE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWRUSE user's temporary tablespace.

TABLESPACE_NAME
------------------------------------------------------------
CONTENTS                   DEFAULT TEMP TABLESPACE
------------------------------------------ -----------------------
TEMP
TEMPORARY                   *


Pressing <return> will result in the database's default temporary
tablespace (identified by *) being used.

Enter value for temporary_tablespace: TEMP

Using tablespace TEMP as the temporary tablespace for AWRUSE




... Creating AWRUSE user

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /u01/app/oracle/admin/test/dpdump/
|   AWREXPIMP.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Load Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR load operation can be
|  monitored in the following directory/file:
|   /u01/app/oracle/admin/test/dpdump/
|   AWREXPIMP.log
|
... Dropping AWRUSE user

End of AWR Load

查詢目標庫19c的DBID

SQL> select DBID from v$database;

      DBID
----------
2371813226

查詢當前的dba_hist_wr_control

SQL> r
  1* select * from dba_hist_wr_control

      DBID SNAP_INTERVAL                                   RETENTION                                   TOPNSQL            CON_ID   SRC_DBID SRC_DBNAME
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------- ---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2371813226 +00000 01:00:00.0                                   +00008 00:00:00.0                               DEFAULT             0 2371813226 test
 459838110 +00000 01:00:00.0                                   +00008 00:00:00.0                               DEFAULT             0  459838110

 

 

 

在生成awr報告的時候也可以看到

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num    DB Name      Instance      Host
------------ ---------- ---------    ----------   ------
  459838110     1    PROD         PROD      orcl
* 2371813226     1    TEST         test      rac1

 

 

 如果想要清理掉這個資訊

SQL> exec dbms_swrf_internal.unregister_database(459838110);

PL/SQL procedure successfully completed.

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL                                   RETENTION                                   TOPNSQL            CON_ID   SRC_DBID SRC_DBNAME
---------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- -------------------- ---------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2371813226 +00000 01:00:00.0                                   +00008 00:00:00.0                               DEFAULT             0 2371813226 test

 

 

 

5.再有一種可能是不完全恢復會不會導致出現兩種DBID,還沒有測試,

incarnation:

Resetlogs命令表示一個資料庫邏輯生存期的結束和另一個資料庫邏輯生存期的開始,Oracle把這個資料庫邏輯生存期稱為incarnation;每次使用resetlogs開啟資料庫,就會使incarnation + 1,也就是產生一個新的incarnation;如果想要恢復到之前incarnation的scn/time,就需要先恢復到之前的incarnation;

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     2371813226       PARENT  1          2019:04:1700:55:59
2       2       TEST     2371813226       CURRENT 1920977    2021:05:2716:46:36

 

參考這個文件在同一套庫上恢復的,DBID是不會發生改變的

https://www.cnblogs.com/bicewow/p/11205685.html

相關文章