【背景】
rman catalog database是11.2.0.3,rman catalog schema升级12.2版本,主要是兼容11g和12c版本.
之前没有采用catalog库记录ORACLE备份信息(数据库也比较多),为了更好配合备份项目以及后续更好地维护与管理备份,对目前采用本地控制文件管理备份信息,变更为成采用统一catalog数据库来记录备份信息.其中N套DATAGUARD库,出现一套(主库是rac,dataguard是单节点,版本是11.2.0.3)无法通过catalog访问相关信息(主库备份OK).
【主要错误如下】
RMAN>list backup;
ORA-20079: full resync from primary database is not done
ORA-20072: full resync from noncurrent controlfile
1、关于rman tnsname配置建议
ORACLE建议在一般会在主库设置主备连接串信息:如下
rman> configure db_unique_name prod connect identifier 'prod';
rman> configure db_unique_name stby connect identifier 'stby';
--其实只要在ORACLE对应版本ORACLE_HOME/network/admin/tnsnames.ora中配置与db_unique_name同名
tnsname即可,不需要通过configure。2者效果等同.
2、在DATAGUARD库使用操作系统认证list backup报错
rman target catalog rman/xx@catadb
Recovery Manager: Release 11.2.0.3.0
connected to recovery catalog database
RMAN> list backup;
doing automatic resync from primary
=========================================
RMAN-00569: ERROR MESSAGE STACK FOLLOWS
ORA-20079: full resync from primary database is not done
3、在DATAGUARD库使用操作系统认证指定db_unique_name可以同步,但是list backup继续报错
rman target catalog rman/xx@catadb
Recovery Manager: Release 11.2.0.3.0
connected to recovery catalog database
RMAN> resync catalog from db_unique_name NTEST;
resyncing from database with DB_UNIQUE_NAME NTEST
starting full resync of recovery catalog
full resync complete
RMAN> list backup;
ORA-20079: full resync from primary database is not done
--即使同步catalog成功后,还是无法从catalog获取信息.
4、主库上查看catalog正常且可以列出备库信息,重新resync也成功
rman target catalog rman/rman@catadb
Recovery Manager: Release 11.2.0.3.0
connected to recovery catalog database
RMAN> list db_unique_name of database;
List of Databases
DB Key DB Name DB ID Database Role Db_unique_name
------- ------- ----------------- --------------- ------------------
1485806 TESTDB 3034219314 PRIMARY NTESTDB
1485806 TESTDB 3034219314 STANDBY PHYTESTDB
RMAN> resync catalog;
starting full resync of recovery catalog
full resync complete
5、DATAGUARD上再次执行resync catalog
【使用密码认证方式】
rman target sys/xxx@PHYTESTDB catalog rman/xx@catadb
Recovery Manager: Release 11.2.0.3.0
RMAN> list backup;
ORA-20079: full resync from primary database is not done
doing automatic resync from primary
resyncing from database with DB_UNIQUE_NAME NTESTDB
automatic resync from primary done
ORA-20079: full resync from primary database is not done
doing automatic resync from primary
RMAN-00571:
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 09/18/2018 22:59:52
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of partial resync command on default channel at 09/18/2018 22:59:52
ORA-20072: full resync from noncurrent controlfile
5957
【使用操作系统认证方式】
RMAN> list backup;
ORA-20079: full resync from primary database is not done
doing automatic resync from primary
resyncing from database with DB_UNIQUE_NAME NTESTDB
automatic resync from primary done
ORA-20079: full resync from primary database is not done
--通过上述验证发现备库能成功同步主库catalog,但是无法查询catalog信息,例如show all;report schema;list backup;
包括MOS给出建议--虽然可以成功同步catalog,但是执行其他命令.执行命令就报错.
Configure primary db_unique_name. Steps include:
rman target sys/<password>@<primary tnsname> catalog rman/<pwd>@<catalog>
rman> configure db_unique_name prod connect identifier 'prod';
rman> configure db_unique_name stby connect identifier 'stby';
rman> list db_unique_name of database;
rman> show all;
rman> resync catalog;
- Connect to standby
rman target sys/<password>@<standby> catalog rman/<pwd>@<catalog>
rman> show all;
rman> resync catalog;
6、通过对比主备库控制中记录thread不相同,主库存在2个,备库存在4
【主库】
--实例信息
SELECT thread#, instance_name, host_name, version, archiver, log_switch_wait FROM gv$instance ORDER BY thread#;
THREAD# INSTANCE_NAME HOST_NAME VERSION ARCHIVE LOG_SWITCH_WAIT
---------- ---------------- -------------------- ----------------- ------- ---------------
1 testdb1 testdb1 11.2.0.3.0 STARTED
2 testdb2 testdb2 11.2.0.3.0 STARTED
--线程信息
SQL> SELECT thread#, instance, status FROM v$thread;
THREAD# INSTANCE STATUS
---------- ---------- ------
1 testdb1 OPEN
2 testdb1 OPEN
【DATAGUARD】
--实例信息
SELECT thread#, instance_name, host_name, version, archiver, log_switch_wait FROM gv$instance ORDER BY thread#;
1 testdb testdb 11.2.0.3.0 STARTED
--线程信息
SELECT thread#, instance, status FROM v$thread;
THREAD# INSTANCE STATUS
---------- ----------------------------
1 testdb OPEN
2 testdb2 CLOSED
3 testdb3 CLOSED
4 testdb4 CLOSED
【为什么会出现这样信息--后来了解,原来这个testdb主库是4个节点rac,后续迁移切换到2个节点rac主机,应该是手动删除无用3,4thread,但是对应备库控制却没有更新
,导致2边控制文件记录信息不一致导致,所以备库同步到主库信息,依然无法使用】
7、备控制文件重新即可--规避重建控制文件方式,采用修改控制文件方式
由于正常情况下备库控制文件是只读,为什么说正常情况下,切换snapshot模式是可以更改控制文件信息,例如thread信息,在physical standby模式无法更新,只能重建控制文件，
本次巧妙采用snapshot standby特性来更改控制文件信息.
--convert phyiscal standby to snapshot standby
alter database convert to snapshot standby;
--删除thread 3和 thread 4对应redo log即可.
SQL> SELECT thread#, group#, sequence#, bytes, archived ,status FROM v$log ORDER BY thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
---------- ---------- ---------- ---------- --- ----------------
1 1 136354 536870912 YES CLEARING
1 2 136355 536870912 YES CLEARING
1 3 136356 536870912 YES CLEARING
1 4 136357 536870912 YES CURRENT
2 5 139326 536870912 YES CLEARING
2 6 139327 536870912 YES CLEARING
2 7 139328 536870912 YES CURRENT
2 8 139325 536870912 YES CLEARING
3 11 0 536870912 YES UNUSED
3 12 0 536870912 YES UNUSED
4 15 0 536870912 YES UNUSED
4 16 0 536870912 YES UNUSED
--disable thread 3 and 4
SQL> alter database disable thread 3;
SQL> alter database disable thread 4;
SQL> alter database drop logfile group 11;
Database altered.
SQL> alter database drop logfile group 12;
Database altered.
SQL> alter database drop logfile group 15;
Database altered.
SQL> alter database drop logfile group 16;
Database altered.
--check thread--成功删除无用线程
SQL> SELECT thread#, instance, status FROM v$thread;
THREAD# INSTANCE STATUS
---------- -------------------------------------------------------------------------------
1 testdb OPEN
2 testdb2 CLOSED
--convert snapshot standby to phyiscal standby
alter database convert to phyiscal standby;
8、在dataguard上成功resync catalog且可以查询catalog信息
rman target / catalog rman/xx@catadb
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 19 18:47:56 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
RMAN> resync catalog;
starting partial resync of recovery catalog
partial resync complete
RMAN> report schema;
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
