视图：dba_hist_wr_control查询到两套库的awr保留策略
问题描述：有一个问题，有同事在查询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
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
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
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
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是不会发生改变的
手机扫一扫
移动阅读更方便
你可能感兴趣的文章