本文共 8922 字，大约阅读时间需要 29 分钟。
[20170203]建立dataguard的standby控制文件.txt
--这个问题困扰我很久,链接:
--就是使用rman的方式建立控制文件,会出现ora-01665错误.$ oerr ora 1665
01665, 00000, "control file is not a standby control file" // *Cause: Attempting to mount, recover or activate a standby database // without a standby control file. // *Action: Create a standby control file before attempting to use the database // as a standby database.--//节日放假期间,看了一些文档猜测为什么出现这样的错误.做一个记录.
1.问题重新演示:
SYS@book> @ &r/ver1
PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionRMAN> backup current controlfile for standby format '/tmp/aa.ctl';
Starting backup at 2017-02-03 10:49:18 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=35 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=46 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=56 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including standby control file in backup set channel ORA_DISK_1: starting piece 1 at 2017-02-03 10:49:20 channel ORA_DISK_1: finished piece 1 at 2017-02-03 10:49:21 piece handle=/tmp/aa.ctl tag=TAG20170203T104919 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2017-02-03 10:49:21Starting Control File and SPFILE Autobackup at 2017-02-03 10:49:21
piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2017_02_03/o1_mf_s_934973362_d97vxl12_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2017-02-03 10:49:23$ cd /tmp
$ scp /tmp/aa.ctl oracle@192.168.100.40:$PWD oracle@192.168.100.40's password: aa.ctl 100% 9728KB 9.5MB/s 00:00--//在dg上执行:
SYS@bookdg> startup nomount ORACLE instance started.Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytesRMAN> restore controlfile from '/tmp/aa.ctl';
Starting restore at 2017-02-03 10:56:07 using channel ORA_DISK_1channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/mnt/ramdisk/book/control01.ctl output file name=/mnt/ramdisk/book/control02.ctl Finished restore at 2017-02-03 10:56:09SYS@bookdg> alter database mount standby database;
Database altered. --这次正常ok.SYS@bookdg> select database_role from v$database;
DATABASE_ROLE -------------------------------- PHYSICAL STANDBY--//DATABASE_ROLE='PHYSICAL STANDBY'也正常,估计这个是11.2.0.3的bug.
2.实际上我认真看文档,正确的执行restore如下:
SYS@bookdg> shutdown abort; ORACLE instance shut down.SYS@bookdg> startup nomount
ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytes$ rm control0[12].ctl
/bin/rm: remove regular file `control01.ctl'? y /bin/rm: remove regular file `control02.ctl'? yRMAN> restore standby controlfile from '/tmp/aa.ctl' ;
--//注意多了standby参数与前面对比. Starting restore at 2017-02-03 11:02:45 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/mnt/ramdisk/book/control01.ctl output file name=/mnt/ramdisk/book/control02.ctl Finished restore at 2017-02-03 11:02:46SYS@bookdg> alter database mount standby database;
Database altered.SYS@bookdg> select database_role from v$database;
DATABASE_ROLE -------------------------------- PHYSICAL STANDBY3.换一种方式建立standby 控制文件:
--在主库上执行:
RMAN> backup current controlfile format '/tmp/bb.ctl'; Starting backup at 2017-02-03 11:05:18 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 2017-02-03 11:05:20 channel ORA_DISK_1: finished piece 1 at 2017-02-03 11:05:21 piece handle=/tmp/bb.ctl tag=TAG20170203T110519 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2017-02-03 11:05:21 Starting Control File and SPFILE Autobackup at 2017-02-03 11:05:21 piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2017_02_03/o1_mf_s_934974321_d97wvk8d_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2017-02-03 11:05:22$ scp /tmp/bb.ctl oracle@192.168.100.40:$PWD
oracle@192.168.100.40's password: bb.ctl 100% 9728KB 9.5MB/s 00:00--再次重复上面的步骤,注意我这里建立的控制文件不是standby的.
RMAN> restore standby controlfile from '/tmp/bb.ctl' ;
Starting restore at 2017-02-03 11:08:25 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=129 device type=DISKchannel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/mnt/ramdisk/book/control01.ctl output file name=/mnt/ramdisk/book/control02.ctl Finished restore at 2017-02-03 11:08:29SYS@bookdg> alter database mount standby database;
Database altered.SYS@bookdg> select database_role from v$database;
DATABASE_ROLE -------------------------------- PHYSICAL STANDBY--//这样也能正常建立standby 控制文件.
4.继续看文档,发现可以采用copy方式:
BACKUP AS COPY STANDBY CONTROLFILE ;--在主库上执行:
RMAN> BACKUP AS COPY STANDBY CONTROLFILE ; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "standby": expecting one of: "archivelog, as, auxiliary, backupset, backup, channel, check, controlfilecopy, copies, copy, cumulative, current, database, datafilecopy, datafile, db_file_name_convert, db_recovery_file_dest, device, diskratio, duration, filesperset, force, format, for, from, full, incremental, keep, maxsetsize, nochecksum, noexclude, nokeep, not, pool, proxy, recovery, reuse, section, skip readonly, skip, spfile, tablespace, tag, to, validate, (" RMAN-01007: at line 1 column 16 file: standard inputRMAN> backup as copy standby controlfile format '/tmp/cc.ctl';
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "standby": expecting one of: "archivelog, as, auxiliary, backupset, backup, channel, check, controlfilecopy, copies, copy, cumulative, current, database, datafilecopy, datafile, db_file_name_convert, db_recovery_file_dest, device, diskratio, duration, filesperset, force, format, for, from, full, incremental, keep, maxsetsize, nochecksum, noexclude, nokeep, not, pool, proxy, recovery, reuse, section, skip readonly, skip, spfile, tablespace, tag, to, validate, (" RMAN-01007: at line 1 column 16 file: standard input --//不过我的测试无法执行.估计oracle的官方文档也存在问题.5.当然在sqlplus下也可以建立:
SYS@book> alter database create standby controlfile as '/tmp/cc.ctl';
Database altered.$ scp /tmp/cc.ctl oracle@192.168.100.40:$PWD
oracle@192.168.100.40's password: cc.ctl 100% 9680KB 9.5MB/s 00:01SYS@bookdg> shutdown abort;
ORACLE instance shut down.$ cp /tmp/cc.ctl /mnt/ramdisk/book/control01.ctl
$ cp /tmp/cc.ctl /mnt/ramdisk/book/control02.ctlSYS@bookdg> startup nomount
ORACLE instance started. Total System Global Area 634732544 bytes Fixed Size 2255792 bytes Variable Size 197133392 bytes Database Buffers 427819008 bytes Redo Buffers 7524352 bytesSYS@bookdg> alter database mount standby database;
Database altered.SYS@bookdg> select database_role from v$database;
DATABASE_ROLE ---------------- PHYSICAL STANDBY--OK.
--总结:
--我估计11.2.0.3存在bug. --btw:正好公司还有一套11.2.0.3的测试系统,重复前面的步骤,11.2.0.3确实存在这个bug,具体细节不再重复演示了.SYS@xxxxdg> alter database mount standby database;
alter database mount standby database * ERROR at line 1: ORA-01665: control file is not a standby control file--//如果这样执行:
RMAN> restore standby controlfile from '/tmp/aa.ctl'; Starting restore at 2017-02-03 11:50:04 using channel ORA_DISK_1 channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/xxxx/control01.ctl output file name=/u01/app/oracle/oradata/xxxx/control02.ctl Finished restore at 2017-02-03 11:50:05SYS@xxxxdg> alter database mount standby database;
Database altered.SYS@xxxxdg> select database_role from v$database ;
DATABASE_ROLE -------------------------------- PHYSICAL STANDBY-- //不知道这个问题算bug,还是不算^_^.
转载地址：http://ljzsl.baihongyu.com/