[20140429]ORA-01665 错误.txt
$ 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.
--前一阵子,为了学习dgmgrl命令行工具管理dataguard,安装一个standby备用库,遇到的问题,但是为了学习的需要绕过了这个问题.
--今天有空,再探究看看.
SYS@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
RMAN> show all;
RMAN configuration parameters for database with db_unique_name TEST are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle11g/product/11.2.0/db_2/dbs/snapcf_test.f'; # default
RMAN> backup current controlfile for standby format '/tmp/aa%U.ctl';
Starting backup at 2014-04-29 09:15:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=212 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 2014-04-29 09:16:03
channel ORA_DISK_1: finished piece 1 at 2014-04-29 09:16:04
piece handle=/tmp/aa01p6ucuf_1_1.ctl tag=TAG20140429T091558 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014-04-29 09:16:04
Starting Control File and SPFILE Autobackup at 2014-04-29 09:16:05
piece handle=/u01/app/oracle11g/flash_recovery_area/TEST/autobackup/2014_04_29/o1_mf_s_846148566_9oxzbsh9_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2014-04-29 09:16:13
$ scp /tmp/aa01p6ucuf_1_1.ctl oracle11g@192.168.101.115:/tmp
--在备用机器上执行:
RMAN> startup nomount
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 939527632 bytes
Database Buffers 654311424 bytes
Redo Buffers 7344128 bytes
RMAN> restore controlfile from '/tmp/aa01p6ucuf_1_1.ctl';
Starting restore at 2014-04-29 09:21:16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=205 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle11g/oradata/test/control01.ctl
output file name=/u01/app/oracle11g/oradata/test/control02.ctl
Finished restore at 2014-04-29 09:21:17
RMAN> sql 'alter database mount standby database';
sql statement: alter database mount standby database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 04/29/2014 09:21:57
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01665: control file is not a standby control file
--在sqlplus模式下执行:
SYS@testdg> alter database mount standby database;
alter database mount standby database
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
--奇怪我以前都是这么做,从来没有出现这个错误.
SYS@testdg> alter database mount ;
Database altered.
SYS@testdg> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
--很明显拷贝过来的控制文件是主库的,为什么会出现这种情况呢?google找到一个链接:
-- http://www.dbi-services.com/index.php/blog/entry/qora-01665-control-file-is-not-a-standby-control-fileq-what-can-i-do
RMAN> list backup of controlfile completed after 'sysdate - 1/24';
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1 Full 18.89M DISK 00:00:04 2014-04-29 09:16:03
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140429T091558
Piece Name: /tmp/aa01p6ucuf_1_1.ctl
Standby Control File Included: Ckp SCN: 3269976747 Ckp time: 2014-04-29 09:15:59
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2 Full 18.92M DISK 00:00:04 2014-04-29 09:16:10
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20140429T091606
Piece Name: /u01/app/oracle11g/flash_recovery_area/TEST/autobackup/2014_04_29/o1_mf_s_846148566_9oxzbsh9_.bkp
Control File Included: Ckp SCN: 3269976766 Ckp time: 2014-04-29 09:16:06
--感觉并不像链接讲的那样.最大的可能就是打开了CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP OFF;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored
RMAN> backup current controlfile for standby format '/tmp/aa%U.ctl';
Starting backup at 2014-04-29 09:55:55
using channel ORA_DISK_1
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 2014-04-29 09:56:00
channel ORA_DISK_1: finished piece 1 at 2014-04-29 09:56:01
piece handle=/tmp/aa03p6uf9d_1_1.ctl tag=TAG20140429T095556 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2014-04-29 09:56:01
$ scp /tmp/aa03p6uf9d_1_1.ctl oracle11g@192.168.101.115:/tmp
RMAN> restore controlfile from '/tmp/aa03p6uf9d_1_1.ctl';
Starting restore at 2014-04-29 09:57:20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=205 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle11g/oradata/test/control01.ctl
output file name=/u01/app/oracle11g/oradata/test/control02.ctl
Finished restore at 2014-04-29 09:57:21
RMAN> sql 'alter database mount standby database';
sql statement: alter database mount standby database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 04/29/2014 09:57:31
RMAN-11003: failure during parse/execution of SQL statement: alter database mount standby database
ORA-01665: control file is not a standby control file
--依旧不行,难道是bug.最后改用sqlplus建立看看.
SYS@test> alter database create standby controlfile as '/tmp/aa.ctl';
Database altered.
$ scp /tmp/aa.ctl oracle11g@192.168.101.115:/tmp
SYS@testdg> shutdown immediate ;
ORA-01507: database not mounted
$ cp /tmp/aa.ctl control01.ctl
/bin/cp: overwrite `control01.ctl'? y
$ cp /tmp/aa.ctl control02.ctl
/bin/cp: overwrite `control02.ctl'? y
SYS@testdg> startup nomount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 939527632 bytes
Database Buffers 654311424 bytes
Redo Buffers 7344128 bytes
SYS@testdg> alter database mount standby database;
Database altered.
--当时我就是这样做的,不知道为什么以前那种方式不行.
--不过我也google一些链接,下面提供了一种转换的方法,自己也测试看看.
-- http://odenysenko.wordpress.com/2012/07/02/quick-solution-for-ora-01665/
SYS@testdg> shutdown immediate ;
ORA-01109: database not open
Database dismounted.
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 939527632 bytes
Database Buffers 654311424 bytes
Redo Buffers 7344128 bytes
RMAN> restore controlfile from '/tmp/aa03p6uf9d_1_1.ctl';
Starting restore at 2014-04-29 10:06:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=205 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle11g/oradata/test/control01.ctl
output file name=/u01/app/oracle11g/oradata/test/control02.ctl
Finished restore at 2014-04-29 10:06:39
RMAN> sql 'alter database mount ';
sql statement: alter database mount
released channel: ORA_DISK_1
SYS@testdg> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SYS@testdg> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
--关键的转换步骤.
SYS@testdg> select database_role from v$database;
select database_role from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SYS@testdg> shutdown immediate ;
ORA-01507: database not mounted
ORACLE instance shut down.
SYS@testdg> startup nomount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 939527632 bytes
Database Buffers 654311424 bytes
Redo Buffers 7344128 bytes
SYS@testdg> alter database mount standby database;
Database altered.
SYS@testdg> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
--OK,这样也可以实现转换.问题没有定位,不知道那位知道问题在那里?