[20170203]建立dataguard的standby控制文件.txt
--这个问题困扰我很久,链接:http://blog.itpub.net/267265/viewspace-1151324/
--就是使用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 Production
RMAN> 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:21
Starting 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 bytes
RMAN> restore controlfile from '/tmp/aa.ctl';
Starting restore at 2017-02-03 10:56:07
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 10:56:09
SYS@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'? y
RMAN> 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:46
SYS@bookdg> alter database mount standby database;
Database altered.
SYS@bookdg> select database_role from v$database;
DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY
3.换一种方式建立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=DISK
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:08:29
SYS@bookdg> alter database mount standby database;
Database altered.
SYS@bookdg> select database_role from v$database;
DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY
--//这样也能正常建立standby 控制文件.
4.继续看文档,发现可以采用copy方式:
http://docs.oracle.com/cd/B19306_01/backup.102/b14194/rcmsynta009.htm
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 input
RMAN> 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:01
SYS@bookdg> shutdown abort;
ORACLE instance shut down.
$ cp /tmp/cc.ctl /mnt/ramdisk/book/control01.ctl
$ cp /tmp/cc.ctl /mnt/ramdisk/book/control02.ctl
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
SYS@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:05
SYS@xxxxdg> alter database mount standby database;
Database altered.
SYS@xxxxdg> select database_role from v$database ;
DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY
-- //不知道这个问题算bug,还是不算^_^.
