[20170203]建立dataguard的standby控制檔案

[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方式:

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,還是不算^_^.