In this post I'll create an ASM database over a host from the backup of a NON-ASM database located on a different host using RMAN utility.
Here, I'll create new ASM managed database on host 'stby' with the backup of database 'db1' which is OMF.
Database_Name HOST File_Management
db1 prim OMF
db1(new database) stby ASM
Step 1.
Take the Datafile, Archivelog and Controlfile backup of the NON-ASM database in '/u01/bkp/'.
RMAN> backup database format '/u01/bkp/db_%U.bkp';
Starting backup at 10-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/db1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0kp092u5_1_1.bkp tag=TAG20140210T113845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
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
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0lp09307_1_1.bkp tag=TAG20140210T113845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 10-FEB-14
RMAN> backup archivelog all format '/u01/bkp/arc_%U.bkp';
Starting backup at 10-FEB-14
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=7 STAMP=838829575
input archived log thread=1 sequence=10 RECID=8 STAMP=838884652
input archived log thread=1 sequence=11 RECID=9 STAMP=838901252
input archived log thread=1 sequence=12 RECID=10 STAMP=838901331
input archived log thread=1 sequence=13 RECID=11 STAMP=838905416
input archived log thread=1 sequence=14 RECID=12 STAMP=838939737
input archived log thread=1 sequence=15 RECID=13 STAMP=838978232
input archived log thread=1 sequence=16 RECID=14 STAMP=839014248
input archived log thread=1 sequence=17 RECID=15 STAMP=839052021
input archived log thread=1 sequence=18 RECID=16 STAMP=839095566
input archived log thread=1 sequence=19 RECID=17 STAMP=839140243
input archived log thread=1 sequence=20 RECID=18 STAMP=839161908
input archived log thread=1 sequence=21 RECID=19 STAMP=839162150
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/arc_0mp09696_1_1.bkp tag=TAG20140210T123550 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 10-FEB-14
RMAN> backup current controlfile format '/u01/bkp/control_%U.ctl';
Starting backup at 10-FEB-14
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 current control file in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/control_0np096bf_1_1.ctl tag=TAG20140210T123703 comment=NO
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-14
Step 2.
Copy the backup to the hosts where needs to create the ASM database.
[oracle@prim bkp]$ scp -r * oracle@stby:/u01/bkp/
oracle@stby's password:
arc_0mp09696_1_1.bkp 100% 263MB 23.9MB/s 00:11
control_0np096bf_1_1.ctl 100% 9568KB 9.3MB/s 00:00
db_0kp092u5_1_1.bkp 100% 1070MB 14.3MB/s 01:15
db_0lp09307_1_1.bkp 100% 9600KB 3.1MB/s 00:03
Here, I'll create new ASM managed database on host 'stby' with the backup of database 'db1' which is OMF.
Database_Name HOST File_Management
db1 prim OMF
db1(new database) stby ASM
Step 1.
Take the Datafile, Archivelog and Controlfile backup of the NON-ASM database in '/u01/bkp/'.
RMAN> backup database format '/u01/bkp/db_%U.bkp';
Starting backup at 10-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=150 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/db1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0kp092u5_1_1.bkp tag=TAG20140210T113845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
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
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/db_0lp09307_1_1.bkp tag=TAG20140210T113845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 10-FEB-14
Starting backup at 10-FEB-14
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=7 STAMP=838829575
input archived log thread=1 sequence=10 RECID=8 STAMP=838884652
input archived log thread=1 sequence=11 RECID=9 STAMP=838901252
input archived log thread=1 sequence=12 RECID=10 STAMP=838901331
input archived log thread=1 sequence=13 RECID=11 STAMP=838905416
input archived log thread=1 sequence=14 RECID=12 STAMP=838939737
input archived log thread=1 sequence=15 RECID=13 STAMP=838978232
input archived log thread=1 sequence=16 RECID=14 STAMP=839014248
input archived log thread=1 sequence=17 RECID=15 STAMP=839052021
input archived log thread=1 sequence=18 RECID=16 STAMP=839095566
input archived log thread=1 sequence=19 RECID=17 STAMP=839140243
input archived log thread=1 sequence=20 RECID=18 STAMP=839161908
input archived log thread=1 sequence=21 RECID=19 STAMP=839162150
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/arc_0mp09696_1_1.bkp tag=TAG20140210T123550 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 10-FEB-14
RMAN> backup current controlfile format '/u01/bkp/control_%U.ctl';
Starting backup at 10-FEB-14
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 current control file in backup set
channel ORA_DISK_1: starting piece 1 at 10-FEB-14
channel ORA_DISK_1: finished piece 1 at 10-FEB-14
piece handle=/u01/bkp/control_0np096bf_1_1.ctl tag=TAG20140210T123703 comment=NO
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-FEB-14
Step 2.
Copy the backup to the hosts where needs to create the ASM database.
[oracle@prim bkp]$ scp -r * oracle@stby:/u01/bkp/
oracle@stby's password:
arc_0mp09696_1_1.bkp 100% 263MB 23.9MB/s 00:11
control_0np096bf_1_1.ctl 100% 9568KB 9.3MB/s 00:00
db_0kp092u5_1_1.bkp 100% 1070MB 14.3MB/s 01:15
db_0lp09307_1_1.bkp 100% 9600KB 3.1MB/s 00:03
Step3.
Copy the parameter file of the OMF database to the new database server and modify the parameter for the ASM.
[oracle@stby dbs]$ more initdb1.ora
db1.__db_cache_size=687865856
db1.__java_pool_size=16777216
db1.__large_pool_size=16777216
db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
db1.__pga_aggregate_target=687865856
db1.__sga_target=1006632960
db1.__shared_io_pool_size=0
db1.__shared_pool_size=268435456
db1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/db1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA','+DATA'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='oracle.com'
*.db_name='db1'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1XDB)'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
Step 4.
Start the database in nomount mode using the newly modified parameter.
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdb1.ora';
ORACLE instance started.
Total System Global Area 171581440 bytes
Fixed Size 1343668 bytes
Variable Size 117444428 bytes
Database Buffers 50331648 bytes
Redo Buffers 2461696 bytes
Step5.
Restore the control file from the the controlfile backup.
RMAN> restore controlfile from '/u01/bkp/control_0np096bf_1_1.ctl';
Starting restore at 10-FEB-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
output file name=+DATA/db1/controlfile/current.283.839163177
output file name=+DATA/db1/controlfile/current.281.839163181
Finished restore at 10-FEB-14
Step 5.
Open the database in mount mode.
RMAN> sql 'alter database mount';
sql statement: alter database mount
released channel: ORA_DISK_1
Step 6.
Bounce the database and update the pfile for the control file location and then open the database in mount mode. Otherwise in future after bounce the database Oracle will throw the error as below.
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info
excerpts of the updated pfile with the controlfile location.
*.audit_file_dest='/u01/app/oracle/admin/db1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/DB1/CONTROLFILE/current.281.839163181','+DATA/DB1/CONTROLFILE/current.283.839163177'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='oracle.com'
*.db_name='db1'
*.diagnostic_dest='/u01/app/oracle'
SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initdb1.ora';
ORACLE instance started.
Total System Global Area 171581440 bytes
Fixed Size 1343668 bytes
Variable Size 117444428 bytes
Database Buffers 50331648 bytes
Redo Buffers 2461696 bytes
Database mounted.
Step7.
Restore the datafile.
Check in the v$datafile and v$tempfile for the file id.
SQL> select FILE#,NAME from v$datafile;
SQL> select FILE#,NAME from v$tempfile;
RMAN> run
2> {
3> set newname for datafile 1 to '+DATA';
4> set newname for datafile 2 to '+DATA';
5>
6> set newname for datafile 3 to '+DATA';
7> set newname for datafile 4 to '+DATA';
8> set newname for datafile 5 to '+DATA';
9> set newname for tempfile 1 to '+DATA';
10> restore database;
11> switch datafile all;
12> switch tempfile all;
13> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 10-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA
channel ORA_DISK_1: restoring datafile 00002 to +DATA
channel ORA_DISK_1: restoring datafile 00003 to +DATA
channel ORA_DISK_1: restoring datafile 00004 to +DATA
channel ORA_DISK_1: restoring datafile 00005 to +DATA
channel ORA_DISK_1: reading from backup piece /u01/bkp/db_0kp092u5_1_1.bkp
channel ORA_DISK_1: piece handle=/u01/bkp/db_0kp092u5_1_1.bkp tag=TAG20140210T113845
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 10-FEB-14
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=839163435 file name=+DATA/db1/datafile/system.279.839163339
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=839163435 file name=+DATA/db1/datafile/sysaux.285.839163339
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=839163435 file name=+DATA/db1/datafile/undotbs1.280.839163343
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=839163436 file name=+DATA/db1/datafile/users.282.839163343
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=839163436 file name=+DATA/db1/datafile/test.284.839163341
renamed tempfile 1 to +DATA in control file
Step8.
Recover the database.
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 137.97M DISK 00:00:07 06-FEB-14
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20140206T161117
Piece Name: /u01/bkp/01ovv1d5_1_1.bkp
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 791516 04-FEB-14 816435 05-FEB-14
1 4 816435 05-FEB-14 858512 05-FEB-14
1 5 858512 05-FEB-14 859840 05-FEB-14
1 6 859840 05-FEB-14 859843 05-FEB-14
1 7 859843 05-FEB-14 869252 05-FEB-14
1 8 869252 05-FEB-14 898900 06-FEB-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 6.50K DISK 00:00:00 06-FEB-14
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20140206T161256
Piece Name: /u01/bkp/04ovv1g8_1_1.bkp
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 9 898900 06-FEB-14 898952 06-FEB-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7 177.34M DISK 00:00:07 07-FEB-14
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20140207T120732
Piece Name: /u01/bkp/07p017g5_1_1.bkp
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 791516 04-FEB-14 816435 05-FEB-14
1 4 816435 05-FEB-14 858512 05-FEB-14
1 5 858512 05-FEB-14 859840 05-FEB-14
1 6 859840 05-FEB-14 859843 05-FEB-14
1 7 859843 05-FEB-14 869252 05-FEB-14
1 8 869252 05-FEB-14 898900 06-FEB-14
1 9 898900 06-FEB-14 898952 06-FEB-14
1 10 898952 06-FEB-14 926119 07-FEB-14
1 11 926119 07-FEB-14 933143 07-FEB-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
10 3.00K DISK 00:00:00 07-FEB-14
BP Key: 10 Status: AVAILABLE Compressed: NO Tag: TAG20140207T120851
Piece Name: /u01/bkp/0ap017ij_1_1.bkp
List of Archived Logs in backup set 10
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 12 933143 07-FEB-14 933178 07-FEB-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
14 178.69M DISK 00:00:09 07-FEB-14
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20140207T131656
Piece Name: /u01/bkp/archivelog_0fp01bi8_1_1.bkp
List of Archived Logs in backup set 14
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 3 791516 04-FEB-14 816435 05-FEB-14
1 4 816435 05-FEB-14 858512 05-FEB-14
1 5 858512 05-FEB-14 859840 05-FEB-14
1 6 859840 05-FEB-14 859843 05-FEB-14
1 7 859843 05-FEB-14 869252 05-FEB-14
1 8 869252 05-FEB-14 898900 06-FEB-14
1 9 898900 06-FEB-14 898952 06-FEB-14
1 10 898952 06-FEB-14 926119 07-FEB-14
1 11 926119 07-FEB-14 933143 07-FEB-14
1 12 933143 07-FEB-14 933178 07-FEB-14
1 13 933178 07-FEB-14 934988 07-FEB-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
21 263.13M DISK 00:00:17 10-FEB-14
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20140210T123550
Piece Name: /u01/bkp/arc_0mp09696_1_1.bkp
List of Archived Logs in backup set 21
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 9 898900 06-FEB-14 898952 06-FEB-14
1 10 898952 06-FEB-14 926119 07-FEB-14
1 11 926119 07-FEB-14 933143 07-FEB-14
1 12 933143 07-FEB-14 933178 07-FEB-14
1 13 933178 07-FEB-14 934988 07-FEB-14
1 14 934988 07-FEB-14 953914 07-FEB-14
1 15 953914 07-FEB-14 974151 08-FEB-14
1 16 974151 08-FEB-14 993762 08-FEB-14
1 17 993762 08-FEB-14 1016061 09-FEB-14
1 18 1016061 09-FEB-14 1039552 09-FEB-14
1 19 1039552 09-FEB-14 1062286 10-FEB-14
1 20 1062286 10-FEB-14 1090918 10-FEB-14
1 21 1090918 10-FEB-14 1091253 10-FEB-14
RMAN>list backup of database;
RMAN>report schema;
RMAN> run
2> {
3> set until sequence 21 thread 1 ;
4> recover database;
5> }
executing command: SET until clause
Starting recover at 10-FEB-14
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_DISK_1: reading from backup piece /u01/bkp/arc_0mp09696_1_1.bkp
channel ORA_DISK_1: piece handle=/u01/bkp/arc_0mp09696_1_1.bkp tag=TAG20140210T123550
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_20_838668830.dbf thread=1 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 10-FEB-14
Step 9.
Open the database in resetlog mode.
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 02/10/2014 13:03:07
RMAN-11003: failure during parse/execution of SQL statement: alter database open resetlogs
ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/db1/redo01.log'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
Additional information: 1
SQL> select GROUP#,MEMBERS,STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CLEARING_CURRENT
3 1 CLEARING
2 1 CLEARING
SQL> alter database add logfile
2 group 4 '+DATA' size 50M;
Database altered.
SQL> alter database add logfile
2 group 5 '+DATA' size 50M;
Database altered.
SQL> alter database add logfile
2 group 6 '+DATA' size 50M;
Database altered.
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open
SQL> select GROUP#,STATUS,MEMBER from v$logfile;
GROUP# STATUS MEMBER
---------- ------- --------------------------------------------------
3 /u01/app/oracle/oradata/db1/redo03.log
2 /u01/app/oracle/oradata/db1/redo02.log
1 /u01/app/oracle/oradata/db1/redo01.log
4 +DATA/db1/onlinelog/group_4.286.839164409
5 +DATA/db1/onlinelog/group_5.287.839164465
6 +DATA/db1/onlinelog/group_6.288.839164475
6 rows selected.
SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;
GROUP# MEMBERS STATUS ARC
---------- ---------- ---------------- ---
1 1 CLEARING_CURRENT NO
2 1 CLEARING YES
6 1 UNUSED YES
4 1 UNUSED YES
5 1 UNUSED YES
3 1 CLEARING YES
6 rows selected.
I tried to recover again the database using the controlfile but it didn't work.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1090918 generated at 02/10/2014 12:31:44 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_21_838668830.dbf
ORA-00280: change 1090918 for thread 1 is in sequence #21
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/db1/redo01.log'
I tried to recover again the database using the controlfile but it didn't work.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1090918 generated at 02/10/2014 12:31:44 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_21_838668830.dbf
ORA-00280: change 1090918 for thread 1 is in sequence #21
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/db1/redo01.log'
Since the group 1 log file was not able to archived so it prevent database to open.
We need to clear that unarchived log.
SQL> alter database clear unarchived logfile group 1;
Database altered.
SQL> select GROUP#,MEMBERS,STATUS,ARCHIVED from v$log;
GROUP# MEMBERS STATUS ARC
---------- ---------- ---------------- ---
1 1 CURRENT NO
2 1 UNUSED YES
3 1 UNUSED YES
4 1 UNUSED YES
5 1 UNUSED YES
6 1 UNUSED YES
6 rows selected.
SQL> alter database open resetlogs;
Database altered.
Now the database is OPEN.
SQL> select NAME,STATUS from v$datafile;
NAME STATUS
-------------------------------------------------- -------
+DATA/db1/datafile/system.279.839163339 SYSTEM
+DATA/db1/datafile/sysaux.285.839163339 ONLINE
+DATA/db1/datafile/undotbs1.280.839163343 ONLINE
+DATA/db1/datafile/users.282.839163343 ONLINE
+DATA/db1/datafile/test.284.839163341 ONLINE
SQL> select NAME,STATUS from v$tempfile;
NAME STATUS
-------------------------------------------------- -------
+DATA/db1/tempfile/temp.289.839165941 ONLINE
SQL> select GROUP#,MEMBERS,STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 UNUSED
3 1 UNUSED
4 1 UNUSED
5 1 UNUSED
6 1 UNUSED
6 rows selected.
No comments:
Post a Comment