RMAN - Restore to new host operating system using CDB standby database using rman backup on 12.2.0.1.0 database
Scenario: We have 2 node CDB RAC 12.2.0.1.0 verion having non rac single physical standby database. We take rman backup from standby database.
2 node rac instance : rac1, rac2
Physical standby : racdr
Using rman backup we restore into new host with same name and figure out steps and challanges.
We take rman backup
- Take rman backup from standby database and transfer to new host machine
- Create pfile and mentioned and create directory mentioned in pfile
- Start database in no-mount stage using pfile
- Connect rman console and restore control from standby backup
- Open database in mount stage ( database in physical standby mode)
- Catalog the backup from location
- Restore and recover database
- Clear redolog file and add new redologfile
- Failover standby database to primary database
- Remove undo tablespace and redo thread #2
- Remove temporary tablespace and create new temporary tablespace, make it default tablespace.
- Start database in nomount stage on new hosts ( new pfile already created before startup)
[oracle@oem dbs]$ . oraenv
ORACLE_SID = [oemrep] ? racdr
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.2.0/dbhome_1
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1862270976 bytes
Fixed Size 8794024 bytes
Variable Size 687865944 bytes
Database Buffers 1157627904 bytes
Redo Buffers 7983104 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
- Recover controlfile using rman backup taken from standby database
[oracle@oem dbs]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Dec 29 13:36:48 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: RAC (not mounted)
RMAN> restore controlfile from '/u01/rman_srv4/control_RAC_20211217.bak';
Starting restore at 29-DEC-2021 13:37:01
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=537 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/rac/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/rac/control02.ctl
Finished restore at 29-DEC-2021 13:37:03
- Open database in mount stage
RMAN> alter database mount;
Statement processed
released channel: ORA_DISK_1
- Catalog rman backup from standby database
RMAN> catalog start with '/u01/rman_srv4';
searching for all files that match the pattern /u01/rman_srv4
List of Files Unknown to the Database
=====================================
File Name: /u01/rman_srv4/Backup_RAC_DB_0t0go0cb_29_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1f0gvcji_47_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_0u0go0cb_30_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_0s0go0cb_28_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_110go0dp_33_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_0v0go0do_31_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1k0gvck4_52_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_0r0go0br_27_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1m0gvcl7_54_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_1e0gvcif_46_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_100go0do_32_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1h0gvck1_49_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_1o0gvcma_56_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_120go0e8_34_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1l0gvck4_53_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_1i0gvck1_50_1_20211217
File Name: /u01/rman_srv4/standbycontrol_RAC_1r0gvcn6_1_1.ctl
File Name: /u01/rman_srv4/Backup_RAC_DB_0q0go0bc_26_1_20211214
File Name: /u01/rman_srv4/control_RAC_20211217.bak
File Name: /u01/rman_srv4/Backup_RAC_DB_0p0go0bc_25_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_0o0go0bc_24_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1p0gvcmb_57_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_1g0gvcjq_48_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_130go0en_35_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1d0gvcif_45_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_140go0en_36_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1j0gvck2_51_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_1n0gvcl7_55_1_20211217
Do you really want to catalog the above files (enter YES or NO)? YEs
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/rman_srv4/Backup_RAC_DB_0t0go0cb_29_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1f0gvcji_47_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_0u0go0cb_30_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_0s0go0cb_28_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_110go0dp_33_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_0v0go0do_31_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1k0gvck4_52_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_0r0go0br_27_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1m0gvcl7_54_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_1e0gvcif_46_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_100go0do_32_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1h0gvck1_49_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_1o0gvcma_56_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_120go0e8_34_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1l0gvck4_53_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_1i0gvck1_50_1_20211217
File Name: /u01/rman_srv4/standbycontrol_RAC_1r0gvcn6_1_1.ctl
File Name: /u01/rman_srv4/Backup_RAC_DB_0q0go0bc_26_1_20211214
File Name: /u01/rman_srv4/control_RAC_20211217.bak
File Name: /u01/rman_srv4/Backup_RAC_DB_0p0go0bc_25_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_0o0go0bc_24_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1p0gvcmb_57_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_1g0gvcjq_48_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_130go0en_35_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1d0gvcif_45_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_140go0en_36_1_20211214
File Name: /u01/rman_srv4/Backup_RAC_DB_1j0gvck2_51_1_20211217
File Name: /u01/rman_srv4/Backup_RAC_DB_1n0gvcl7_55_1_20211217
RMAN> restore database;
Starting restore at 29-DEC-2021 15:31:05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=549 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 00013 to /u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/datafile/undo_2.319.1091217683
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/RACDR/D0706A7D1C5F1532E0534738A8C0B3F6/datafile/o1_mf_soetbs_jvk2n7gk_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rmanbkp/Backup_RAC_DB_1k0gvck4_52_1_20211217
channel ORA_DISK_1: errors found reading piece handle=/u01/app/oracle/rmanbkp/Backup_RAC_DB_1k0gvck4_52_1_20211217
channel ORA_DISK_1: failover to piece handle=/u01/rman_srv4/Backup_RAC_DB_1k0gvck4_52_1_20211217 tag=TAG20211217T154259
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:46
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/racdr/datafile/system.257.1088276431
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/racdr/datafile/undotbs1.259.1088276501
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/racdr/datafile/users.260.1088276503
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rmanbkp/Backup_RAC_DB_1l0gvck4_53_1_20211217
channel ORA_DISK_1: errors found reading piece handle=/u01/app/oracle/rmanbkp/Backup_RAC_DB_1l0gvck4_53_1_20211217
channel ORA_DISK_1: failover to piece handle=/u01/rman_srv4/Backup_RAC_DB_1l0gvck4_53_1_20211217 tag=TAG20211217T154259
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/racdr/datafile/sysaux.258.1088276477
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/racdr/datafile/undotbs2.272.1088276835
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rmanbkp/Backup_RAC_DB_1m0gvcl7_54_1_20211217
channel ORA_DISK_1: errors found reading piece handle=/u01/app/oracle/rmanbkp/Backup_RAC_DB_1m0gvcl7_54_1_20211217
channel ORA_DISK_1: failover to piece handle=/u01/rman_srv4/Backup_RAC_DB_1m0gvcl7_54_1_20211217 tag=TAG20211217T154259
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 00010 to /u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/datafile/system.280.1088277275
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/datafile/sysaux.281.1088277275
channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/datafile/undotbs1.279.1088277275
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rmanbkp/Backup_RAC_DB_1n0gvcl7_55_1_20211217
channel ORA_DISK_1: errors found reading piece handle=/u01/app/oracle/rmanbkp/Backup_RAC_DB_1n0gvcl7_55_1_20211217
channel ORA_DISK_1: failover to piece handle=/u01/rman_srv4/Backup_RAC_DB_1n0gvcl7_55_1_20211217 tag=TAG20211217T154259
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 00005 to /u01/app/oracle/oradata/racdr/4700a987085b3dfae05387e5e50a8c7b/datafile/system.269.1088276619
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/racdr/4700a987085b3dfae05387e5e50a8c7b/datafile/undotbs1.270.1088276619
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rmanbkp/Backup_RAC_DB_1p0gvcmb_57_1_20211217
channel ORA_DISK_1: errors found reading piece handle=/u01/app/oracle/rmanbkp/Backup_RAC_DB_1p0gvcmb_57_1_20211217
channel ORA_DISK_1: failover to piece handle=/u01/rman_srv4/Backup_RAC_DB_1p0gvcmb_57_1_20211217 tag=TAG20211217T154259
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
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/racdr/4700a987085b3dfae05387e5e50a8c7b/datafile/sysaux.268.1088276619
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rmanbkp/Backup_RAC_DB_1o0gvcma_56_1_20211217
channel ORA_DISK_1: errors found reading piece handle=/u01/app/oracle/rmanbkp/Backup_RAC_DB_1o0gvcma_56_1_20211217
channel ORA_DISK_1: failover to piece handle=/u01/rman_srv4/Backup_RAC_DB_1o0gvcma_56_1_20211217 tag=TAG20211217T154259
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 29-DEC-2021 15:33:37
RMAN> recover database;
Starting recover at 29-DEC-2021 15:33:56
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=89
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rmanbkp/Backup_RAC_DB_1h0gvck1_49_1_20211217
channel ORA_DISK_1: errors found reading piece handle=/u01/app/oracle/rmanbkp/Backup_RAC_DB_1h0gvck1_49_1_20211217
channel ORA_DISK_1: failover to piece handle=/u01/rman_srv4/Backup_RAC_DB_1h0gvck1_49_1_20211217 tag=TAG20211217T154207
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/o1_mf_1_89_jwrdngkf_.arc RECID=131 STAMP=1092584039
media recovery complete, elapsed time: 00:00:00
Finished recover at 29-DEC-2021 15:34:01
RMAN>
- Recover further archivelog. Copied from DR and recovery of archivelog to new hosts
[root@oem sf_software_26_sept_2021]# cp *.arc /u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29
[oracle@oem 2021_12_29]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Wed Dec 29 16:08:23 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: RAC (DBID=2676258589, not open)
RMAN> recover database;
Starting recover at 29-DEC-2021 16:08:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
starting media recovery
archived log for thread 1 with sequence 89 is already on disk as file /u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_1_89_1088276576.arc
archived log for thread 1 with sequence 90 is already on disk as file /u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_1_90_1088276576.arc
archived log for thread 1 with sequence 91 is already on disk as file /u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_1_91_1088276576.arc
archived log for thread 1 with sequence 92 is already on disk as file /u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_1_92_1088276576.arc
archived log for thread 2 with sequence 69 is already on disk as file /u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_2_69_1088276576.arc
archived log for thread 2 with sequence 70 is already on disk as file /u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_2_70_1088276576.arc
archived log for thread 2 with sequence 71 is already on disk as file /u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_2_71_1088276576.arc
archived log file name=/u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_2_69_1088276576.arc thread=2 sequence=69
archived log file name=/u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_1_89_1088276576.arc thread=1 sequence=89
archived log file name=/u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_1_90_1088276576.arc thread=1 sequence=90
archived log file name=/u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_1_91_1088276576.arc thread=1 sequence=91
archived log file name=/u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_2_70_1088276576.arc thread=2 sequence=70
archived log file name=/u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_1_92_1088276576.arc thread=1 sequence=92
archived log file name=/u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/arch_2_71_1088276576.arc thread=2 sequence=71
media recovery complete, elapsed time: 00:00:03
Finished recover at 29-DEC-2021 16:08:33
RMAN>
- We have done restoration using rman backup from physical standby database so we are unable to open database in resetlog options.
RMAN> alter database open resetlog;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 12/29/2021 16:08:55
ORA-02288: invalid OPEN mode
RMAN> exit
- First Failover from physical standby database to primary database to current new host database (But during conversion we find standby redo logfile not created on redo logfile location we cleared it first and add new redo logfile then perform failover ).
Recovery Manager complete.
[oracle@oem 2021_12_29]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Wed Dec 29 16:37:57 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 RACPDB MOUNTED
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC MOUNTED PHYSICAL STANDBY
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdr/datafile/system.257.1088276431
/u01/app/oracle/oradata/racdr/datafile/sysaux.258.1088276477
/u01/app/oracle/oradata/racdr/datafile/undotbs1.259.1088276501
/u01/app/oracle/oradata/racdr/4700a987085b3dfae05387e5e50a8c7b/datafile/system.2
69.1088276619
/u01/app/oracle/oradata/racdr/4700a987085b3dfae05387e5e50a8c7b/datafile/sysaux.2
68.1088276619
/u01/app/oracle/oradata/racdr/datafile/users.260.1088276503
/u01/app/oracle/oradata/racdr/4700a987085b3dfae05387e5e50a8c7b/datafile/undotbs1
NAME
--------------------------------------------------------------------------------
.270.1088276619
/u01/app/oracle/oradata/racdr/datafile/undotbs2.272.1088276835
/u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/datafile/system.2
80.1088277275
/u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/datafile/sysaux.2
81.1088277275
/u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/datafile/undotbs1
.279.1088277275
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdr/d0706a7d1c5f1532e0534738a8c0b3f6/datafile/undo_2.3
19.1091217683
/u01/app/oracle/oradata/RACDR/D0706A7D1C5F1532E0534738A8C0B3F6/datafile/o1_mf_so
etbs_jvk2n7gk_.dbf
13 rows selected.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdr/tempfile/temp.267.1088276611
+DATA/RAC/D07045B084567D8BE0534738A8C057CB/TEMPFILE/temp.271.1088276639
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdr/onlinelog/group_2.264.1088276577
/u01/app/oracle/oradata/racdr/onlinelog/group_2.266.1088276589
/u01/app/oracle/oradata/racdr/onlinelog/group_1.263.1088276577
/u01/app/oracle/oradata/racdr/onlinelog/group_1.265.1088276589
/u01/app/oracle/oradata/racdr/onlinelog/group_3.273.1088277009
/u01/app/oracle/oradata/racdr/onlinelog/group_3.274.1088277017
/u01/app/oracle/oradata/racdr/onlinelog/group_4.275.1088277023
/u01/app/oracle/oradata/racdr/onlinelog/group_4.276.1088277033
/u01/app/oracle/oradata/racdr/onlinelog/redo05.log
/u01/app/oracle/oradata/racdr/onlinelog/redo06.log
/u01/app/oracle/oradata/racdr/onlinelog/redo07.log
11 rows selected.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
*
ERROR at line 1:
ORA-16136: Managed Standby Recovery not active
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 5 of thread 1
ORA-00312: online log 5 thread 1:
'/u01/app/oracle/oradata/racdr/onlinelog/redo05.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdr/onlinelog/group_2.264.1088276577
/u01/app/oracle/oradata/racdr/onlinelog/group_2.266.1088276589
/u01/app/oracle/oradata/racdr/onlinelog/group_1.263.1088276577
/u01/app/oracle/oradata/racdr/onlinelog/group_1.265.1088276589
/u01/app/oracle/oradata/racdr/onlinelog/group_3.273.1088277009
/u01/app/oracle/oradata/racdr/onlinelog/group_3.274.1088277017
/u01/app/oracle/oradata/racdr/onlinelog/group_4.275.1088277023
/u01/app/oracle/oradata/racdr/onlinelog/group_4.276.1088277033
/u01/app/oracle/oradata/racdr/onlinelog/redo05.log
/u01/app/oracle/oradata/racdr/onlinelog/redo06.log
/u01/app/oracle/oradata/racdr/onlinelog/redo07.log
11 rows selected.
SQL> select bytes from v$log;
BYTES
----------
209715200
209715200
209715200
209715200
SQL> select GROUP#,THREAD#,STATUS from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 UNUSED
2 1 UNUSED
3 2 UNUSED
4 2 UNUSED
SQL> alter database add logfile group 5 ('/u01/app/oracle/oradata/racdr/onlinelog/redo05.log') size 200m;
alter database add logfile group 5 ('/u01/app/oracle/oradata/racdr/onlinelog/redo05.log') size 200m
*
ERROR at line 1:
ORA-01275: Operation ADD LOGFILE is not allowed if standby file management is
automatic.
SQL> sho parameter ORA-01275: Operation ADD LOGFILE is not allowed if standby file management is
automatic.
SQL> SP2-0042: unknown command "automatic." - rest of line ignored.
SQL> sho parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enabled_PDBs_on_standby string *
standby_archive_dest string ?#/dbs/arch
standby_db_preserve_states string NONE
standby_file_management string AUTO
SQL> alter system set standby_file_management='MANUAL';
System altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE group 8 '/u01/app/oracle/oradata/racdr/onlinelog/redo08.log' size 200m;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE group 9 '/u01/app/oracle/oradata/racdr/onlinelog/redo09.log' size 200m;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE group 10 '/u01/app/oracle/oradata/racdr/onlinelog/redo10.log' size 200m;
Database altered.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdr/onlinelog/group_2.264.1088276577
/u01/app/oracle/oradata/racdr/onlinelog/group_2.266.1088276589
/u01/app/oracle/oradata/racdr/onlinelog/group_1.263.1088276577
/u01/app/oracle/oradata/racdr/onlinelog/group_1.265.1088276589
/u01/app/oracle/oradata/racdr/onlinelog/group_3.273.1088277009
/u01/app/oracle/oradata/racdr/onlinelog/group_3.274.1088277017
/u01/app/oracle/oradata/racdr/onlinelog/group_4.275.1088277023
/u01/app/oracle/oradata/racdr/onlinelog/group_4.276.1088277033
/u01/app/oracle/oradata/racdr/onlinelog/redo05.log
/u01/app/oracle/oradata/racdr/onlinelog/redo06.log
/u01/app/oracle/oradata/racdr/onlinelog/redo07.log
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdr/onlinelog/redo08.log
/u01/app/oracle/oradata/racdr/onlinelog/redo09.log
/u01/app/oracle/oradata/racdr/onlinelog/redo10.log
14 rows selected.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH
*
ERROR at line 1:
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 6 of thread 2
ORA-00312: online log 6 thread 2:
'/u01/app/oracle/oradata/racdr/onlinelog/redo06.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 6;
Database altered.
SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 7;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 7;
Database altered.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdr/onlinelog/group_2.264.1088276577
/u01/app/oracle/oradata/racdr/onlinelog/group_2.266.1088276589
/u01/app/oracle/oradata/racdr/onlinelog/group_1.263.1088276577
/u01/app/oracle/oradata/racdr/onlinelog/group_1.265.1088276589
/u01/app/oracle/oradata/racdr/onlinelog/group_3.273.1088277009
/u01/app/oracle/oradata/racdr/onlinelog/group_3.274.1088277017
/u01/app/oracle/oradata/racdr/onlinelog/group_4.275.1088277023
/u01/app/oracle/oradata/racdr/onlinelog/group_4.276.1088277033
/u01/app/oracle/oradata/racdr/onlinelog/redo08.log
/u01/app/oracle/oradata/racdr/onlinelog/redo09.log
/u01/app/oracle/oradata/racdr/onlinelog/redo10.log
11 rows selected.
- Perform failover after clearing old redolog and adding new redolog file. 8,9,10 and we drop 7,8,9 redolog later.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.
SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;
Database altered.
SQL> select name,open_mode ,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC MOUNTED PRIMARY
- Open database in read write mode
SQL> alter database open;
Database altered.
SQL> select name,open_mode ,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
RAC READ WRITE PRIMARY
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 RACPDB MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> sho pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 RACPDB READ WRITE NO
SQL>
- New host redo logfile location
[root@oem onlinelog]# pwd
/u01/app/oracle/oradata/racdr/onlinelog
[root@oem onlinelog]# ll
total 2662472
-rw-r----- 1 oracle oinstall 209715712 Dec 29 17:54 group_1.263.1088276577
-rw-r----- 1 oracle oinstall 209715712 Dec 29 17:54 group_1.265.1088276589
-rw-r----- 1 oracle oinstall 209715712 Dec 29 18:04 group_2.264.1088276577
-rw-r----- 1 oracle oinstall 209715712 Dec 29 18:04 group_2.266.1088276589
-rw-r----- 1 oracle oinstall 209715712 Dec 29 17:54 group_3.273.1088277009
-rw-r----- 1 oracle oinstall 209715712 Dec 29 17:54 group_3.274.1088277017
-rw-r----- 1 oracle oinstall 209715712 Dec 29 17:55 group_4.275.1088277023
-rw-r----- 1 oracle oinstall 209715712 Dec 29 17:55 group_4.276.1088277033
-rw-r----- 1 oracle oinstall 209715712 Dec 29 17:52 redo06.log
-rw-r----- 1 oracle oinstall 209715712 Dec 29 17:53 redo07.log
-rw-r----- 1 oracle oinstall 209715712 Dec 29 17:34 redo08.log
-rw-r----- 1 oracle oinstall 209715712 Dec 29 17:35 redo09.log
-rw-r----- 1 oracle oinstall 209715712 Dec 29 17:35 redo10.log
[root@oem onlinelog]#
SQL> alter system set standby_file_management='AUTO';
System altered.
SQL> sho parameter standby
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enabled_PDBs_on_standby string *
standby_archive_dest string ?#/dbs/arch
standby_db_preserve_states string NONE
standby_file_management string AUTO
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL>
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ --------- ----------
1 1 5 209715200 512 2 NO CURRENT 4648936 29-DEC-21 1.8447E+19 0
2 1 4 209715200 512 2 YES INACTIVE 4648932 29-DEC-21 4648936 29-DEC-21 0
3 2 1 209715200 512 2 YES INACTIVE 4647695 29-DEC-21 4647698 29-DEC-21 0
4 2 2 209715200 512 2 YES INACTIVE 4647698 29-DEC-21 4647844 29-DEC-21 0
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_ CON_ID
---------- ------- ------- ---------------------------------------------------------------------- --- ----------
2 ONLINE /u01/app/oracle/oradata/racdr/onlinelog/group_2.264.1088276577 NO 0
2 ONLINE /u01/app/oracle/oradata/racdr/onlinelog/group_2.266.1088276589 NO 0
1 ONLINE /u01/app/oracle/oradata/racdr/onlinelog/group_1.263.1088276577 NO 0
1 ONLINE /u01/app/oracle/oradata/racdr/onlinelog/group_1.265.1088276589 NO 0
3 ONLINE /u01/app/oracle/oradata/racdr/onlinelog/group_3.273.1088277009 NO 0
3 ONLINE /u01/app/oracle/oradata/racdr/onlinelog/group_3.274.1088277017 NO 0
4 ONLINE /u01/app/oracle/oradata/racdr/onlinelog/group_4.275.1088277023 NO 0
4 ONLINE /u01/app/oracle/oradata/racdr/onlinelog/group_4.276.1088277033 NO 0
5 STANDBY /u01/app/oracle/fast_recovery_area/RAC/archivelog/2021_12_29/o1_mf_2_0 YES 0
_jwrnvch0_.arc
9 rows selected.
SQL>
- Check and disable the closed state thread#2 and clear unarchived logfile 3 and 4
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED PUBLIC
SQL> select group# from v$log where THREAD#=2;
GROUP#
----------
3
4
SQL> alter database disable thread 2;
Database altered.
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> alter database clear unarchived logfile group 4;
Database altered.
SQL> select THREAD#, STATUS, ENABLED from v$thread;
THREAD# STATUS ENABLED
---------- ------ --------
1 OPEN PUBLIC
2 CLOSED DISABLED
SQL> sho parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
- Remove undo tablespace UNDOTBS2
SQL> select tablespace_name from dba_tablespaces where contents='UNDO';
TABLESPACE_NAME
------------------------------
UNDOTBS1
UNDOTBS2
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
- Drop temporary tablespace and add new temporary tablespace temp1 and make as default tablespace
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdr/tempfile/temp.267.1088276611
+DATA/RAC/D07045B084567D8BE0534738A8C057CB/TEMPFILE/temp.271.1088276639
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
------------------------------
TEMP
SQL> create temporary tablespace TEMP1 tempfile '/u01/app/oracle/oradata/racdr/tempfile/temp001.dbf' size 50m;
Tablespace created.
SQL> alter database default temporary tablespace TEMP1;
Database altered.
SQL> drop tablespace TEMP including contents and datafiles;
Tablespace dropped.
SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME
------------------------------
TEMP1
SQL>
NOTE : --- increase pga_target , sga , temp file , undo file if required, match with production requirement.
No comments:
Post a Comment