Wednesday, 11 December 2019

Using RMAN backup restore 12c database from one host to another host on Linux Open suse12 SP1

Scenario Preview:

Using RMAN backup restore SMRPATCH7 database from one host to another host.


 Face some oracle error.

ORA Error  Found:

ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/SMRPATCH7/redo01.log'

Source OS : SUSE 12 SP4 | sgdcpl02
Destination OS : SUSE 12 SP1 |sgdcpl08


  •  Command to check existing data files and log files.

RMAN> set pagesize  200 linesize 200
select 'set newname for datafile ' || a.FILE# || ' to "' || a.NAME || '";'
  from v$datafile a
union all
select 'set newname for tempfile ' || a.FILE# || ' to "' || a.NAME || '";'
  from v$tempfile a
union all
SELECT 'SQL "ALTER DATABASE RENAME FILE ''''' || a.MEMBER || '''''  to  ''''' ||
       a.MEMBER || ''''' ";'
  FROM v$logfile a;
RMAN> 2> 3> 4> 5> 6> 7> 8> 9>


'SETNEWNAMEFORDATAFILE'||A.FILE#||'TO"'||A.NAME||'";'
--------------------------------------------------------------------------------

set newname for datafile 1 to "/u01/app/oracle/oradata/SMRPATCH7/system01.dbf";


set newname for datafile 2 to "/u01/app/oracle/oradata/SMRPATCH7/idata01.DBF";


set newname for datafile 3 to "/u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF";


set newname for datafile 4 to "/u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf";


set newname for datafile 5 to "/u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf";


set newname for datafile 6 to "/u01/app/oracle/oradata/SMRPATCH7/users01.dbf";


set newname for datafile 7 to "/u01/app/oracle/oradata/SMRPATCH7/indx01.DBF";


set newname for tempfile 1 to "/u01/app/oracle/oradata/SMRPATCH7/temp01.dbf";


SQL "ALTER DATABASE RENAME FILE ''/u04/MasterDB/oradata/SMRPATCH7/redo03.log''  to  ''/u04/MasterDB/oradata/SMRPATCH7/redo03.log'' ";


SQL "ALTER DATABASE RENAME FILE ''/u04/MasterDB/oradata/SMRPATCH7/redo02.log''  to  ''/u04/MasterDB/oradata/SMRPATCH7/redo02.log'' ";


SQL "ALTER DATABASE RENAME FILE ''/u04/MasterDB/oradata/SMRPATCH7/redo01.log''  to  ''/u04/MasterDB/oradata/SMRPATCH7/redo01.log'' ";


11 rows selected

RMAN>

RMAN> run
2> {
3> allocate channel c1 device type disk format '/u04/MasterDB/rmanbkp_SMRPATCH7/Backup_%d_DB_%u_%s_%p_%T';
4> allocate channel c2 device type disk format '/u04/MasterDB/rmanbkp_SMRPATCH7/Backup_%d_DB_%u_%s_%p_%T';
5> allocate channel c3 device type disk format '/u04/MasterDB/rmanbkp_SMRPATCH7/Backup_%d_DB_%u_%s_%p_%T';
6> allocate channel c4 device type disk format '/u04/MasterDB/rmanbkp_SMRPATCH7/Backup_%d_DB_%u_%s_%p_%T';
7> crosscheck backup;
8> crosscheck archivelog all;
9> report obsolete;
10> delete noprompt obsolete;
11> sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
12> BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 tag='FULL_BACKUP_Daily' DATABASE INCLUDE CURRENT CONTROLFILE FILESPERSET 1;
13> BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL;
14> #delete noprompt archivelog all backed up 1 times to disk;
15> copy current controlfile to '/u04/MasterDB/rmanbkp_SMRPATCH7/control01_SMRPATCH7_%d_%u_%T.bak';
16> release channel c1;
17> release channel c2;
18> release channel c3;
19> release channel c4;
20> }
#################################################################################


  • oracle@sgdcpl08:/u02/rman_backup> . oraenv
ORACLE_SID = [SMRPATCH7] ?
The Oracle base remains unchanged with value /u01/app/oracle
oracle@sgdcpl08:/u02/rman_backup> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 11 11:13:18 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

  • SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1593835520 bytes
Fixed Size                  2924880 bytes
Variable Size            1207963312 bytes
Database Buffers          369098752 bytes
Redo Buffers               13848576 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
oracle@sgdcpl08:/u02/rman_backup> rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Dec 11 11:13:42 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SMRPATCH (not mounted)

  • RMAN> restore controlfile from '/u02/rman_backup/control01_SMRPATCH7_SMRPATCH_91uj5a59_20191211.bak';

Starting restore at 11-DEC-2019 11:14:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=807 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/SMRPATCH7/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/SMRPATCH7/control02.ctl
Finished restore at 11-DEC-2019 11:14:12

  • RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1



  • RMAN> catalog start with '/u02/rman_backup/';

searching for all files that match the pattern /u02/rman_backup/

List of Files Unknown to the Database
=====================================
File Name: /u02/rman_backup/rman_SMRPATCH7.log

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
no files cataloged

List of Files Which Were Not Cataloged
=======================================
File Name: /u02/rman_backup/rman_SMRPATCH7.log
  RMAN-07517: Reason: The file header is corrupted

RMAN> RUN
{
set newname for datafile 1 to "/u01/app/oracle/oradata/SMRPATCH7/system01.dbf";
set newname for datafile 3 to "/u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF";
set newname for datafile 4 to "/u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf";
set newname for datafile 5 to "/u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf";
set newname for datafile 2 to "/u01/app/oracle/oradata/SMRPATCH7/idata01.DBF";
set newname for datafile 6 to "/u01/app/oracle/oradata/SMRPATCH7/users01.dbf";
set newname for datafile 7 to "/u01/app/oracle/oradata/SMRPATCH7/indx01.DBF";
2> 3> 4> 5> 6> set newname for tempfile 1 to "/u01/app/oracle/oradata/SMRPATCH7/temp01.dbf";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
recover database;
}7> 8> 9> 10> 11> 12> 13> 14> 15>
executing command: SET NEWNAME

executing command: SET NEWNAME

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 11-DEC-2019 11:17:09
using channel ORA_DISK_1

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 00002 to /u01/app/oracle/oradata/SMRPATCH7/idata01.DBF
channel ORA_DISK_1: reading from backup piece /u02/rman_backup/Backup_SMRPATCH_DB_8luj59vt_277_1_20191211
channel ORA_DISK_1: piece handle=/u02/rman_backup/Backup_SMRPATCH_DB_8luj59vt_277_1_20191211 tag=FULL_BACKUP_DAILY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00007 to /u01/app/oracle/oradata/SMRPATCH7/indx01.DBF
channel ORA_DISK_1: reading from backup piece /u02/rman_backup/Backup_SMRPATCH_DB_8muj59vt_278_1_20191211
channel ORA_DISK_1: piece handle=/u02/rman_backup/Backup_SMRPATCH_DB_8muj59vt_278_1_20191211 tag=FULL_BACKUP_DAILY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 00004 to /u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u02/rman_backup/Backup_SMRPATCH_DB_8ouj59vu_280_1_20191211
channel ORA_DISK_1: piece handle=/u02/rman_backup/Backup_SMRPATCH_DB_8ouj59vu_280_1_20191211 tag=FULL_BACKUP_DAILY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 00006 to /u01/app/oracle/oradata/SMRPATCH7/users01.dbf
channel ORA_DISK_1: reading from backup piece /u02/rman_backup/Backup_SMRPATCH_DB_8quj5a01_282_1_20191211
channel ORA_DISK_1: piece handle=/u02/rman_backup/Backup_SMRPATCH_DB_8quj5a01_282_1_20191211 tag=FULL_BACKUP_DAILY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
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 /u01/app/oracle/oradata/SMRPATCH7/system01.dbf
channel ORA_DISK_1: reading from backup piece /u02/rman_backup/Backup_SMRPATCH_DB_8nuj59vu_279_1_20191211
channel ORA_DISK_1: piece handle=/u02/rman_backup/Backup_SMRPATCH_DB_8nuj59vu_279_1_20191211 tag=FULL_BACKUP_DAILY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00003 to /u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF
channel ORA_DISK_1: reading from backup piece /u02/rman_backup/Backup_SMRPATCH_DB_8kuj59vt_276_1_20191211
channel ORA_DISK_1: piece handle=/u02/rman_backup/Backup_SMRPATCH_DB_8kuj59vt_276_1_20191211 tag=FULL_BACKUP_DAILY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
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 00005 to /u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /u02/rman_backup/Backup_SMRPATCH_DB_8juj59vt_275_1_20191211

channel ORA_DISK_1: piece handle=/u02/rman_backup/Backup_SMRPATCH_DB_8juj59vt_275_1_20191211 tag=FULL_BACKUP_DAILY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 11-DEC-2019 11:20:21

datafile 1 switched to datafile copy
input datafile copy RECID=27 STAMP=1026732021 file name=/u01/app/oracle/oradata/SMRPATCH7/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=28 STAMP=1026732021 file name=/u01/app/oracle/oradata/SMRPATCH7/idata01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=29 STAMP=1026732021 file name=/u01/app/oracle/oradata/SMRPATCH7/ilog01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=30 STAMP=1026732021 file name=/u01/app/oracle/oradata/SMRPATCH7/sysaux01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=31 STAMP=1026732021 file name=/u01/app/oracle/oradata/SMRPATCH7/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=32 STAMP=1026732021 file name=/u01/app/oracle/oradata/SMRPATCH7/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=33 STAMP=1026732021 file name=/u01/app/oracle/oradata/SMRPATCH7/indx01.DBF


Starting recover at 11-DEC-2019 11:20:21
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1401 is already on disk as file /u02/rman_backup/o1_mf_1_1401_gz1gt84c_.arc
archived log file name=/u02/rman_backup/o1_mf_1_1401_gz1gt84c_.arc thread=1 sequence=1401
unable to find archived log
archived log thread=1 sequence=1402
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/11/2019 11:20:22
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 1402 and starting SCN of 36001430

  • RMAN> select member from v$logfile;



MEMBER
--------------------------------------------------------------------------------

/u04/MasterDB/oradata/SMRPATCH7/redo03.log


/u04/MasterDB/oradata/SMRPATCH7/redo02.log


/u04/MasterDB/oradata/SMRPATCH7/redo01.log


/u04/MasterDB/oradata/SMRPATCH7/redo04.log


/u04/MasterDB/oradata/SMRPATCH7/redo05.log


/u04/MasterDB/oradata/SMRPATCH7/redo06.log





############################################################################


RMAN> SQL "ALTER DATABASE RENAME FILE ''/u04/MasterDB/oradata/SMRPATCH7/redo01.log''  to ''/u01/app/oracle/oradata/SMRPATCH7/redo01.log'' ";

sql statement: ALTER DATABASE RENAME FILE ''/u04/MasterDB/oradata/SMRPATCH7/redo01.log''  to ''/u01/app/oracle/oradata/SMRPATCH7/redo01.log''

RMAN> SQL "ALTER DATABASE RENAME FILE ''/u04/MasterDB/oradata/SMRPATCH7/redo02.log''  to  ''/u01/app/oracle/oradata/SMRPATCH7/redo02.log'' ";

sql statement: ALTER DATABASE RENAME FILE ''/u04/MasterDB/oradata/SMRPATCH7/redo02.log''  to  ''/u01/app/oracle/oradata/SMRPATCH7/redo02.log''

RMAN> SQL "ALTER DATABASE RENAME FILE ''/u04/MasterDB/oradata/SMRPATCH7/redo03.log''  to  ''/u01/app/oracle/oradata/SMRPATCH7/redo03.log'' ";

sql statement: ALTER DATABASE RENAME FILE ''/u04/MasterDB/oradata/SMRPATCH7/redo03.log''  to  ''/u01/app/oracle/oradata/SMRPATCH7/redo03.log''


RMAN> select member from v$logfile;


MEMBER
--------------------------------------------------------------------------------

/u01/app/oracle/oradata/SMRPATCH7/redo03.log


/u01/app/oracle/oradata/SMRPATCH7/redo02.log


/u01/app/oracle/oradata/SMRPATCH7/redo01.log

 RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 12/11/2019 11:35:28
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/SMRPATCH7/redo01.log'

RMAN>  alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 12/11/2019 11:35:43
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/SMRPATCH7/redo01.log'

RMAN> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CLEARING_CURRENT
         3          1 CLEARING
         2          1 CLEARING

RMAN> alter database clear logfile group 1;

Statement processed

RMAN> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         3          1 CLEARING
         2          1 CLEARING

RMAN> alter database clear logfile group 2;

Statement processed

RMAN> alter database clear logfile group 3;

Statement processed

RMAN> select group#,thread#,status from v$log;

    GROUP#    THREAD# STATUS
---------- ---------- ----------------
         1          1 CURRENT
         3          1 UNUSED
         2          1 UNUSED

RMAN> alter database open resetlogs;

Statement processed

RMAN> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
SMRPATCH  READ WRITE           PRIMARY



Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)