Standby Creation Steps Using RMAN
Take the primary database backup using rman:
With compression:
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup AS COMPRESSED BACKUPSET full database tag ORCL_FULL format '/u02/rmanbkp/full%u' ;
backup tag ORCL_ARCHIVE format '/u02/rmanbkp/%d_%T_%s_%p_ARCHIVE' archivelog all;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
run
{
backup current controlfile for standby format '/u02/rmanbkp/standby.ctl';
}
Without compression:
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
backup database plus archivelog format '/u02/rmanbkp/rmanlog';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
run
{
backup current controlfile for standby format '/u02/rmanbkp/standby.ctl';
}
Once rman backup succesfully completed, transfer the backups from primary to standby:
oracle@mqm-testdb1:/u02/rmanbkp$ scp * oracle@10.40.135.23:/u02/rmanbkp_BKP08032020/
Note down the archive log sequence number.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7618
Next log sequence to archive 7626
Current log sequence 7626
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7619
Next log sequence to archive 7627
Current log sequence 7627
SQL> exit
oracle@mqm-testdb2:~$ echo $ORACLE_SID
QPROD2
oracle@mqm-testdb1:/u02/rmanbkp$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 22:45:00 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7257
Next log sequence to archive 7265
Current log sequence 7265
SQL> alter system switch logfile;
System altered.
oracle@mqm-testdb2:~$ echo $ORACLE_SID
QPROD1
Note down the dbid from the primary:
SQL> select dbid from v$database;
DBID
----------
3195744369
Prepare the pfile and nomount the standby database:
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ vi initQPRODN1.ora
QPRODN1.__data_transfer_cache_size=0
QPRODN1.__db_cache_size=11072962560
QPRODN1.__java_pool_size=234881024
QPRODN1.__large_pool_size=2785017856
QPRODN1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
QPRODN1.__pga_aggregate_target=3221225472
QPRODN1.__sga_target=16106127360
QPRODN1.__shared_io_pool_size=0
QPRODN1.__shared_pool_size=1946157056
QPRODN1.__streams_pool_size=0
#*.cluster_database=TRUE
CONTROL_FILES='+REDO/QPRODN/CONTROLFILE/current.1815.945626485','+DATA/QPRODN/CONTROLFILE/current.284.945626485'
*.compatible='12.1.0.2.0'
*.db_file_name_convert='+DATA/QPROD/DATAFILE/','+DATA/QPRODN/DATAFILE'
*.db_name='QPROD'
*.db_recovery_file_dest_size=42949672960
*.db_recovery_file_dest='+REDO'
*.db_unique_name='QPRODN'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srpstbXDB)'
*.fal_server='QPROD'
QPRODN1.instance_number=1
QPRODN2.instance_number=2
QPRODN1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mqm-testdb5-vip)(PORT=1521))))'
QPRODN2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mqm-testdb6-vip)(PORT=1521))))'
*.log_archive_config='DG_CONFIG=(QPROD,QPRODN)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=QPRODN'
*.log_archive_dest_2='service=QPROD valid_for=(online_logfiles,primary_role) db_unique_name=QPROD'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=5
*.log_file_name_convert='+DATA/QPROD/ONLINELOG/','+REDO/QPROD/ONLINELOG/','+DATA/QPRODN/ONLINELOG','+REDO/QPRODN/ONLINELOG'
*.remote_listener='eftstest-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=15g
*.standby_file_management='AUTO'
QPRODN1.thread=1
QPRODN2.thread=2
Start the standby database in nomount.
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 21:29:22 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initQPRODN1.ora';
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size 7651632 bytes
Variable Size 4966063824 bytes
Database Buffers 1.1073E+10 bytes
Redo Buffers 59449344 bytes
SQL>
Restore the controlfile, make sure it will be created in asm diskgroup:
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Mar 8 21:30:18 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: QPROD (not mounted)
RMAN> restore standby controlfile from '/u02/QPRODN_BKP08032020/standby.ctl';
Starting restore at 08-MAR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1441 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+REDO/QPRODN/CONTROLFILE/current.284.1034544631
output file name=+DATA/QPRODN/CONTROLFILE/current.313.1034544631
Finished restore at 08-MAR-20
RMAN> exit
shutdown the database and update pfile with newly created controlfiles;
ex:
vi initQPRODN1.ora
CONTROL_FILES='+REDO/QPRODN/CONTROLFILE/current.284.1034544631','+DATA/QPRODN/CONTROLFILE/current.313.1034544631'
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ cat initQPRODN1.ora
QPRODN1.__data_transfer_cache_size=0
QPRODN1.__db_cache_size=11072962560
QPRODN1.__java_pool_size=234881024
QPRODN1.__large_pool_size=2785017856
QPRODN1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
QPRODN1.__pga_aggregate_target=3221225472
QPRODN1.__sga_target=16106127360
QPRODN1.__shared_io_pool_size=0
QPRODN1.__shared_pool_size=1946157056
QPRODN1.__streams_pool_size=0
#*.cluster_database=TRUE
CONTROL_FILES='+REDO/QPRODN/CONTROLFILE/current.284.1034544631','+DATA/QPRODN/CONTROLFILE/current.313.1034544631'
*.compatible='12.1.0.2.0'
*.db_file_name_convert='+DATA/QPROD/DATAFILE/','+DATA/QPRODN/DATAFILE'
*.db_name='QPROD'
*.db_recovery_file_dest_size=42949672960
*.db_recovery_file_dest='+REDO'
*.db_unique_name='QPRODN'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srpstbXDB)'
*.fal_server='QPROD'
QPRODN1.instance_number=1
QPRODN2.instance_number=2
QPRODN1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mqm-testdb5-vip)(PORT=1521))))'
QPRODN2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mqm-testdb6-vip)(PORT=1521))))'
*.log_archive_config='DG_CONFIG=(QPROD,QPRODN)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=QPRODN'
*.log_archive_dest_2='service=QPROD valid_for=(online_logfiles,primary_role) db_unique_name=QPROD'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=5
*.log_file_name_convert='+DATA/QPROD/ONLINELOG/','+REDO/QPROD/ONLINELOG/','+DATA/QPRODN/ONLINELOG','+REDO/QPRODN/ONLINELOG'
*.remote_listener='eftstest-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=15g
*.standby_file_management='AUTO'
QPRODN1.thread=1
QPRODN2.thread=2
Startup the database on nomount and issue the alter databse mount command with newly created controlfile:
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 21:34:05 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initQPRODN1.ora';
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size 7651632 bytes
Variable Size 4966063824 bytes
Database Buffers 1.1073E+10 bytes
Redo Buffers 59449344 bytes
SQL> alter database mount;
Database altered.
Goto the backup location and verify the backups once before going to restore:
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$ ls -ltr
total 80502410
-rw-r----- 1 oracle oinstall 900897792 Mar 8 20:42 QPROD_20200308_93_1_ARCHIVE
-rw-r----- 1 oracle oinstall 777929728 Mar 8 20:42 QPROD_20200308_94_1_ARCHIVE
-rw-r----- 1 oracle oinstall 425146368 Mar 8 20:42 QPROD_20200308_95_1_ARCHIVE
-rw-r----- 1 oracle oinstall 387843072 Mar 8 20:42 QPROD_20200308_96_1_ARCHIVE
-rw-r----- 1 oracle oinstall 315320832 Mar 8 20:42 QPROD_20200308_97_1_ARCHIVE
-rw-r----- 1 oracle oinstall 9584386048 Mar 8 20:43 full2puqjgua
-rw-r----- 1 oracle oinstall 7514497024 Mar 8 20:45 full2quqjgub
-rw-r----- 1 oracle oinstall 8994889728 Mar 8 20:46 full2ruqjgub
-rw-r----- 1 oracle oinstall 12259835904 Mar 8 20:49 full2suqjguc
-rw-r----- 1 oracle oinstall 25165824 Mar 8 20:49 standby.ctl
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Mar 8 21:35:56 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: QPROD (DBID=3195744369, not open)
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 QPROD 3195744369 PARENT 174129451 07-MAR-17
2 2 QPROD 3195744369 CURRENT 232778945 02-JUN-17
Catalog the rman backup pieces and verify one more time:
RMAN> catalog start with '/u02/QPRODN_BKP08032020';
Starting implicit crosscheck backup at 08-MAR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1729 device type=DISK
Crosschecked 20 objects
Finished implicit crosscheck backup at 08-MAR-20
Starting implicit crosscheck copy at 08-MAR-20
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 08-MAR-20
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7528.285.1034534711
searching for all files that match the pattern /u02/QPRODN_BKP08032020
List of Files Unknown to the Database
=====================================
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_93_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/full2puqjgua
File Name: /u02/QPRODN_BKP08032020/full2ruqjgub
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_96_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_97_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_94_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_95_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/standby.ctl
File Name: /u02/QPRODN_BKP08032020/full2quqjgub
File Name: /u02/QPRODN_BKP08032020/full2suqjguc
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_93_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/full2puqjgua
File Name: /u02/QPRODN_BKP08032020/full2ruqjgub
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_96_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_97_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_94_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/QPROD_20200308_95_1_ARCHIVE
File Name: /u02/QPRODN_BKP08032020/standby.ctl
File Name: /u02/QPRODN_BKP08032020/full2quqjgub
File Name: /u02/QPRODN_BKP08032020/full2suqjguc
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 QPROD 3195744369 PARENT 174129451 07-MAR-17
2 2 QPROD 3195744369 CURRENT 232778945 02-JUN-17
RMAN>
Restore the database with rman:
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 QPROD 3195744369 PARENT 174129451 07-MAR-17
2 2 QPROD 3195744369 CURRENT 232778945 02-JUN-17
RMAN> RUN
{
ALLOCATE CHANNEL c6 DEVICE TYPE disk;
ALLOCATE CHANNEL c7 DEVICE TYPE disk;
ALLOCATE CHANNEL c8 DEVICE TYPE disk;
ALLOCATE CHANNEL c9 DEVICE TYPE disk;
restore database;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
}
allocated channel: c6
channel c6: SID=1729 device type=DISK
allocated channel: c7
channel c7: SID=1761 device type=DISK
allocated channel: c8
channel c8: SID=1793 device type=DISK
allocated channel: c9
channel c9: SID=1825 device type=DISK
Starting restore at 08-MAR-20
channel c6: starting datafile backup set restore
channel c6: specifying datafile(s) to restore from backup set
channel c6: restoring datafile 00005 to +DATA/QPRODN/datafileundotbs2.328.945626765
channel c6: restoring datafile 00014 to +DATA/QPRODN/datafilesysaux.357.949233333
channel c6: restoring datafile 00018 to +DATA/QPRODN/datafilesysaux.289.949667263
channel c6: restoring datafile 00022 to +DATA/QPRODN/datafileach_arch_sec.364.955635241
channel c6: restoring datafile 00028 to +DATA/QPRODN/datafileach_arch_sec.410.955635337
channel c6: reading from backup piece /u02/QPRODN_BKP08032020/full2quqjgub
channel c7: starting datafile backup set restore
channel c7: specifying datafile(s) to restore from backup set
channel c7: restoring datafile 00002 to +DATA/QPRODN/datafileach.465.945626749
channel c7: restoring datafile 00003 to +DATA/QPRODN/datafilesysaux.290.945626751
channel c7: restoring datafile 00004 to +DATA/QPRODN/datafileundotbs1.298.945626761
channel c7: restoring datafile 00009 to +DATA/QPRODN/datafileindx.296.945626753
channel c7: restoring datafile 00010 to +DATA/QPRODN/datafilelobspace.297.945626761
channel c7: restoring datafile 00013 to +DATA/QPRODN/datafileach.477.947075519
channel c7: restoring datafile 00015 to +DATA/QPRODN/datafilesysaux.462.949233349
channel c7: restoring datafile 00019 to +DATA/QPRODN/datafilesysaux.306.950083981
channel c7: reading from backup piece /u02/QPRODN_BKP08032020/full2puqjgua
channel c8: starting datafile backup set restore
channel c8: specifying datafile(s) to restore from backup set
channel c8: restoring datafile 00001 to +DATA/QPRODN/datafilesystem.390.945626751
channel c8: restoring datafile 00007 to +DATA/QPRODN/datafileach_arch.334.945626751
channel c8: restoring datafile 00012 to +DATA/QPRODN/datafileach.286.945626751
channel c8: restoring datafile 00017 to +DATA/QPRODN/datafilesysaux.285.949538521
channel c8: restoring datafile 00023 to +DATA/QPRODN/datafileach_arch_sec.375.955635253
channel c8: restoring datafile 00026 to +DATA/QPRODN/datafileach_arch.470.955635313
channel c8: restoring datafile 00027 to +DATA/QPRODN/datafileach_arch.312.955635321
channel c8: restoring datafile 00029 to +DATA/QPRODN/datafilepatrol.389.990708027
channel c8: reading from backup piece /u02/QPRODN_BKP08032020/full2ruqjgub
channel c9: starting datafile backup set restore
channel c9: specifying datafile(s) to restore from backup set
channel c9: restoring datafile 00006 to +DATA/QPRODN/datafileusers.436.945626769
channel c9: restoring datafile 00008 to +DATA/QPRODN/datafileach_rpt.295.945626753
channel c9: restoring datafile 00011 to +DATA/QPRODN/datafileach.408.945626751
channel c9: restoring datafile 00016 to +DATA/QPRODN/datafilesysaux.409.949238837
channel c9: restoring datafile 00020 to +DATA/QPRODN/datafileach_arch_sec.468.950434697
channel c9: restoring datafile 00021 to +DATA/QPRODN/datafileach_rpt_sec.427.950438609
channel c9: restoring datafile 00024 to +DATA/QPRODN/datafileach_arch_sec.388.955635263
channel c9: restoring datafile 00025 to +DATA/QPRODN/datafileach_arch.386.955635301
channel c9: reading from backup piece /u02/QPRODN_BKP08032020/full2suqjguc
channel c6: piece handle=/u02/QPRODN_BKP08032020/full2quqjgub tag=ORCL_FULL
channel c6: restored backup piece 1
channel c6: restore complete, elapsed time: 00:41:36
channel c7: piece handle=/u02/QPRODN_BKP08032020/full2puqjgua tag=ORCL_FULL
channel c7: restored backup piece 1
channel c7: restore complete, elapsed time: 00:42:36
channel c8: piece handle=/u02/QPRODN_BKP08032020/full2ruqjgub tag=ORCL_FULL
channel c8: restored backup piece 1
channel c8: restore complete, elapsed time: 00:44:46
channel c9: piece handle=/u02/QPRODN_BKP08032020/full2suqjguc tag=ORCL_FULL
channel c9: restored backup piece 1
channel c9: restore complete, elapsed time: 00:58:26
Finished restore at 08-MAR-20
released channel: c6
released channel: c7
released channel: c8
released channel: c9
Recover the database with rman:
RMAN> RUN
{
ALLOCATE CHANNEL c6 DEVICE TYPE disk;
ALLOCATE CHANNEL c7 DEVICE TYPE disk;
ALLOCATE CHANNEL c8 DEVICE TYPE disk;
ALLOCATE CHANNEL c9 DEVICE TYPE disk;
recover database;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
}
allocated channel: c6
channel c6: SID=1729 device type=DISK
allocated channel: c7
channel c7: SID=1761 device type=DISK
allocated channel: c8
channel c8: SID=1793 device type=DISK
allocated channel: c9
channel c9: SID=1825 device type=DISK
Starting recover at 08-MAR-20
starting media recovery
channel c6: starting archived log restore to default destination
channel c6: restoring archived log
archived log thread=1 sequence=7625
channel c6: restoring archived log
archived log thread=2 sequence=7264
channel c6: reading from backup piece /u02/QPRODN_BKP08032020/QPROD_20200308_96_1_ARCHIVE
channel c6: piece handle=/u02/QPRODN_BKP08032020/QPROD_20200308_96_1_ARCHIVE tag=ORCL_ARCHIVE
channel c6: restored backup piece 1
channel c6: restore complete, elapsed time: 00:00:03
archived log file name=+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_2_seq_7264.286.1034548749 thread=2 sequence=7264
archived log file name=+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7625.287.1034548749 thread=1 sequence=7625
channel default: deleting archived log(s)
archived log file name=+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_2_seq_7264.286.1034548749 RECID=3 STAMP=1034548749
archived log thread=1 sequence=7625
released channel: c6
released channel: c7
released channel: c8
released channel: c9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/08/2020 22:39:12
ORA-15028: ASM file '+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7625.287.1034548749' not dropped; currently being accessed
Now note down the sequence and perform the recovery:
Recovery Manager complete.
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 22:39:32 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/QPRODN/DATAFILE/system.269.1034545139'
Primary side:
Note down the last sequence number of thread 1 and thread 2
ASMCMD> cd 2020_03_08/
ASMCMD> ls
thread_1_seq_7619.771.1034478411
thread_1_seq_7620.2679.1034503371
thread_1_seq_7621.943.1034515519
thread_1_seq_7622.801.1034515531
thread_1_seq_7623.2705.1034518935
thread_1_seq_7624.806.1034528083
thread_2_seq_7258.1057.1034497099
thread_2_seq_7259.778.1034515523
thread_2_seq_7260.1050.1034515531
thread_2_seq_7261.889.1034518937
thread_2_seq_7262.2211.1034520955
thread_2_seq_7263.971.1034528083
ASMCMD> cd ARCHIVELOG/
ASMCMD>
ASMCMD> ls
2020_03_08/
ASMCMD> cd 2020_03_08/
ASMCMD> ls
thread_1_seq_7619.771.1034478411
thread_1_seq_7620.2679.1034503371
thread_1_seq_7621.943.1034515519
thread_1_seq_7622.801.1034515531
thread_1_seq_7623.2705.1034518935
thread_1_seq_7624.806.1034528083
thread_1_seq_7625.2889.1034541371
thread_2_seq_7258.1057.1034497099
thread_2_seq_7259.778.1034515523
thread_2_seq_7260.1050.1034515531
thread_2_seq_7261.889.1034518937
thread_2_seq_7262.2211.1034520955
thread_2_seq_7263.971.1034528083
thread_2_seq_7264.1028.1034541369
ASMCMD> ls
thread_1_seq_7619.771.1034478411
thread_1_seq_7620.2679.1034503371
thread_1_seq_7621.943.1034515519
thread_1_seq_7622.801.1034515531
thread_1_seq_7623.2705.1034518935
thread_1_seq_7624.806.1034528083
thread_1_seq_7625.2889.1034541371
thread_1_seq_7626.797.1034548997
thread_2_seq_7258.1057.1034497099
thread_2_seq_7259.778.1034515523
thread_2_seq_7260.1050.1034515531
thread_2_seq_7261.889.1034518937
thread_2_seq_7262.2211.1034520955
thread_2_seq_7263.971.1034528083
thread_2_seq_7264.1028.1034541369
ASMCMD> ls
thread_1_seq_7619.771.1034478411
thread_1_seq_7620.2679.1034503371
thread_1_seq_7621.943.1034515519
thread_1_seq_7622.801.1034515531
thread_1_seq_7623.2705.1034518935
thread_1_seq_7624.806.1034528083
thread_1_seq_7625.2889.1034541371
thread_1_seq_7626.797.1034548997
thread_2_seq_7258.1057.1034497099
thread_2_seq_7259.778.1034515523
thread_2_seq_7260.1050.1034515531
thread_2_seq_7261.889.1034518937
thread_2_seq_7262.2211.1034520955
thread_2_seq_7263.971.1034528083
thread_2_seq_7264.1028.1034541369
thread_2_seq_7265.2785.1034549121
ASMCMD> exit
In my case log sequence numbers are:
oracle@mqm-testdb1:/u02/rmanbkp$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Mar 8 22:57:05 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: QPROD (DBID=3195744369)
RMAN> backup format '/u02/rmanbkp/%d_%s_%p_%c_%t.arc.bkp' archivelog from sequence 7264 until sequence 7265 thread 1;
Starting backup at 08-MAR-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1064 instance=QPROD1 device type=DISK
specification does not match any archived log in the repository
backup cancelled because there are no files to backup
Finished backup at 08-MAR-20
RMAN> backup format '/u02/rmanbkp/%d_%s_%p_%c_%t.arc.bkp' archivelog from sequence 7264 until sequence 7265 thread 2;
Starting backup at 08-MAR-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=7264 RECID=14888 STAMP=1034541371
input archived log thread=2 sequence=7265 RECID=14891 STAMP=1034549122
channel ORA_DISK_1: starting piece 1 at 08-MAR-20
channel ORA_DISK_1: finished piece 1 at 08-MAR-20
piece handle=/u02/rmanbkp/QPROD_101_1_1_1034549851.arc.bkp tag=TAG20200308T225730 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 08-MAR-20
Starting Control File and SPFILE Autobackup at 08-MAR-20
piece handle=+REDO/QPROD/AUTOBACKUP/2020_03_08/s_1034549877.1014.1034549879 comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAR-20
RMAN> backup format '/u02/rmanbkp/%d_%s_%p_%c_%t.arc.bkp' archivelog from sequence 7625 until sequence 7626 thread 1;
Starting backup at 08-MAR-20
using channel ORA_DISK_1
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=7625 RECID=14889 STAMP=1034541372
input archived log thread=1 sequence=7626 RECID=14890 STAMP=1034548997
channel ORA_DISK_1: starting piece 1 at 08-MAR-20
channel ORA_DISK_1: finished piece 1 at 08-MAR-20
piece handle=/u02/rmanbkp/QPROD_103_1_1_1034549887.arc.bkp tag=TAG20200308T225807 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 08-MAR-20
Starting Control File and SPFILE Autobackup at 08-MAR-20
piece handle=+REDO/QPROD/AUTOBACKUP/2020_03_08/s_1034549904.992.1034549909 comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAR-20
RMAN> exit
Recovery Manager complete.
Transfer the older archives from primary to standby:
oracle@mqm-testdb1:/u02/rmanbkp$ pwd
/u02/rmanbkp
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$ ls -ltr
total 81706022
-rw-r----- 1 oracle asmadmin 7514497024 Mar 8 20:06 full2quqjgub
-rw-r----- 1 oracle asmadmin 9584386048 Mar 8 20:11 full2puqjgua
-rw-r----- 1 oracle asmadmin 8994889728 Mar 8 20:12 full2ruqjgub
-rw-r----- 1 oracle asmadmin 12259835904 Mar 8 20:36 full2suqjguc
-rw-r----- 1 oracle asmadmin 387843072 Mar 8 20:36 QPROD_20200308_96_1_ARCHIVE
-rw-r----- 1 oracle asmadmin 425146368 Mar 8 20:36 QPROD_20200308_95_1_ARCHIVE
-rw-r----- 1 oracle asmadmin 777929728 Mar 8 20:36 QPROD_20200308_94_1_ARCHIVE
-rw-r----- 1 oracle asmadmin 900897792 Mar 8 20:36 QPROD_20200308_93_1_ARCHIVE
-rw-r----- 1 oracle asmadmin 315320832 Mar 8 20:36 QPROD_20200308_97_1_ARCHIVE
-rw-r----- 1 oracle asmadmin 25165824 Mar 8 20:37 standby.ctl
-rw-r----- 1 oracle asmadmin 396113408 Mar 8 22:57 QPROD_101_1_1_1034549851.arc.bkp
-rw-r----- 1 oracle asmadmin 219527168 Mar 8 22:58 QPROD_103_1_1_1034549887.arc.bkp
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$
oracle@mqm-testdb1:/u02/rmanbkp$ scp QPROD_101_1_1_1034549851.arc.bkp oracle@10.40.135.23:/u02/QPRODN_BKP08032020/
Password:
QPROD_101_1_1_10345 100% |********************************************
oracle@mqm-testdb1:/u02/rmanbkp$ scp QPROD_103_1_1_1034549887.arc.bkp oracle@10.40.135.23:/u02/QPRODN_BKP08032020/
Password:
Password:
QPROD_103_1_1_10345 100% |********************************************
Apply the missing archives in the standby side:
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 23:01:07 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Mar 8 23:01:16 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: QPROD (DBID=3195744369, not open)
RMAN> catalog start with '/u02/QPRODN_BKP08032020';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u02/QPRODN_BKP08032020
List of Files Unknown to the Database
=====================================
File Name: /u02/QPRODN_BKP08032020/QPROD_101_1_1_1034549851.arc.bkp
File Name: /u02/QPRODN_BKP08032020/QPROD_103_1_1_1034549887.arc.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u02/QPRODN_BKP08032020/QPROD_101_1_1_1034549851.arc.bkp
File Name: /u02/QPRODN_BKP08032020/QPROD_103_1_1_1034549887.arc.bkp
RMAN> run
{
restore archivelog from logseq=7264 until logseq=7265 thread=2;
restore archivelog from logseq=7625 until logseq=7626 thread=1;
}2> 3> 4> 5>
Starting restore at 08-MAR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1441 device type=DISK
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7264
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=7265
channel ORA_DISK_1: reading from backup piece /u02/QPRODN_BKP08032020/QPROD_101_1_1_1034549851.arc.bkp
channel ORA_DISK_1: piece handle=/u02/QPRODN_BKP08032020/QPROD_101_1_1_1034549851.arc.bkp tag=TAG20200308T225730
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-MAR-20
Starting restore at 08-MAR-20
using channel ORA_DISK_1
archived log for thread 1 with sequence 7625 is already on disk as file +REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7625.287.1034548749
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7626
channel ORA_DISK_1: reading from backup piece /u02/QPRODN_BKP08032020/QPROD_103_1_1_1034549887.arc.bkp
channel ORA_DISK_1: piece handle=/u02/QPRODN_BKP08032020/QPROD_103_1_1_1034549887.arc.bkp tag=TAG20200308T225807
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-MAR-20
RMAN> recover database;
Starting recover at 08-MAR-20
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 7625 is already on disk as file +REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7625.287.1034548749
archived log for thread 1 with sequence 7626 is already on disk as file +REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7626.289.1034550181
archived log for thread 2 with sequence 7265 is already on disk as file +REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_2_seq_7265.288.1034550179
archived log file name=+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_2_seq_7265.288.1034550179 thread=2 sequence=7265
archived log file name=+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7625.287.1034548749 thread=1 sequence=7625
archived log file name=+REDO/QPRODN/ARCHIVELOG/2020_03_08/thread_1_seq_7626.289.1034550181 thread=1 sequence=7626
media recovery complete, elapsed time: 00:00:03
Finished recover at 08-MAR-20
RMAN> exit
Open the database in read only mode:
Recovery Manager complete.
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$
oracle@mqm-testdb5:/u02/QPRODN_BKP08032020$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 23:03:24 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> alter database open read only;
Database altered.
Modify the standby parameters and mount the database:
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ cat initQPRODN1.ora
QPRODN1.__data_transfer_cache_size=0
QPRODN1.__db_cache_size=11072962560
QPRODN1.__java_pool_size=234881024
QPRODN1.__large_pool_size=2785017856
QPRODN1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
QPRODN1.__pga_aggregate_target=3221225472
QPRODN1.__sga_target=16106127360
QPRODN1.__shared_io_pool_size=0
QPRODN1.__shared_pool_size=1946157056
QPRODN1.__streams_pool_size=0
#*.cluster_database=TRUE
CONTROL_FILES='+REDO/QPRODN/CONTROLFILE/current.284.1034544631','+DATA/QPRODN/CONTROLFILE/current.313.1034544631'
*.compatible='12.1.0.2.0'
*.db_file_name_convert='+DATA/QPROD/DATAFILE/','+DATA/QPRODN/DATAFILE'
*.db_name='QPROD'
*.DB_CREATE_FILE_DEST='+DATA'
*.db_recovery_file_dest_size=42949672960
*.db_recovery_file_dest='+REDO'
*.db_unique_name='QPRODN'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srpstbXDB)'
*.fal_server='QPROD'
QPRODN1.instance_number=1
QPRODN2.instance_number=2
QPRODN1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mqm-testdb5-vip)(PORT=1521))))'
QPRODN2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mqm-testdb6-vip)(PORT=1521))))'
*.log_archive_config='DG_CONFIG=(QPROD,QPRODN)'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=QPRODN'
*.log_archive_dest_2='service=QPROD valid_for=(online_logfiles,primary_role) db_unique_name=QPROD'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=5
*.log_file_name_convert='+DATA/QPROD/ONLINELOG/','+REDO/QPROD/ONLINELOG/','+DATA/QPRODN/ONLINELOG','+REDO/QPRODN/ONLINELOG'
*.remote_listener='eftstest-scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=15g
*.standby_file_management='AUTO'
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 23:43:20 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initQPRODN1.ora';
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size 7651632 bytes
Variable Size 4966063824 bytes
Database Buffers 1.1073E+10 bytes
Redo Buffers 59449344 bytes
SQL> alter database mount;
Database altered.
Drop the older (standby) redolog files:
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE DROP LOGFILE GROUP 7;
ALTER DATABASE DROP LOGFILE GROUP 8;
ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 10;
ALTER DATABASE DROP LOGFILE GROUP 11;
ALTER DATABASE DROP LOGFILE GROUP 12;
ALTER DATABASE DROP LOGFILE GROUP 13;
ALTER DATABASE DROP LOGFILE GROUP 14;
ALTER DATABASE DROP LOGFILE GROUP 15;
ALTER DATABASE DROP LOGFILE GROUP 16;
ALTER DATABASE DROP LOGFILE GROUP 17;
ALTER DATABASE DROP LOGFILE GROUP 18;
SQL> ALTER DATABASE DROP LOGFILE GROUP 9
*
ERROR at line 1:
ORA-01623: log 9 is current log for instance QPRODN1 (thread 1) - cannot drop
ORA-00312: online log 9 thread 1: '+REDO/QPROD/ONLINELOG/group_9'
ORA-00312: online log 9 thread 1: '+REDO/QPROD/ONLINELOG/group_9'
SQL> ALTER DATABASE DROP LOGFILE GROUP 12
*
ERROR at line 1:
ORA-01623: log 12 is current log for instance QPROD1 (thread 2) - cannot drop
ORA-00312: online log 12 thread 2: '+REDO/QPROD/ONLINELOG/group_12'
ORA-00312: online log 12 thread 2: '+REDO/QPROD/ONLINELOG/group_12'
Note:
Current online redolog group will be not dropped, leave it this error and proceed further.
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
GROUP#
ARC
STATUS
---------- --- ----------------
9
NO
CURRENT
12
NO
CURRENT
Add the new redolog groups in standby side:
SQL> ALTER DATABASE ADD LOGFILE THREAD 1
GROUP 1('+DATA', '+REDO') SIZE 500M,
GROUP 3('+DATA', '+REDO') SIZE 500M,
GROUP 5('+DATA', '+REDO') SIZE 500M,
GROUP 7('+DATA', '+REDO') SIZE 500M,
GROUP 11('+DATA', '+REDO') SIZE 500M,
GROUP 13('+DATA', '+REDO') SIZE 500M,
GROUP 15('+DATA', '+REDO') SIZE 500M,
GROUP 17('+DATA', '+REDO') SIZE 500M ;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2
GROUP 2('+DATA', '+REDO') SIZE 500M,
GROUP 4('+DATA', '+REDO') SIZE 500M,
GROUP 6('+DATA', '+REDO') SIZE 500M,
GROUP 8('+DATA', '+REDO') SIZE 500M,
GROUP 10('+DATA', '+REDO') SIZE 500M,
GROUP 14('+DATA', '+REDO') SIZE 500M,
GROUP 16('+DATA', '+REDO') SIZE 500M,
GROUP 18('+DATA', '+REDO') SIZE 500M;
Database altered.
Note: we have total 18 groups since group 9 and 12 are in current state we cannot drop it and re-create it now, we will add it later.
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
GROUP# ARC STATUS
---------- --- ----------------
1 YES UNUSED
9 NO CURRENT
17 YES UNUSED
15 YES UNUSED
13 YES UNUSED
11 YES UNUSED
7 YES UNUSED
5 YES UNUSED
3 YES UNUSED
10 YES UNUSED
18 YES UNUSED
GROUP# ARC STATUS
---------- --- ----------------
8 YES UNUSED
16 YES UNUSED
2 YES UNUSED
12 NO CURRENT
4 YES UNUSED
14 YES UNUSED
6 YES UNUSED
18 rows selected.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initQPRODN1.ora';
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size 7651632 bytes
Variable Size 4966063824 bytes
Database Buffers 1.1073E+10 bytes
Redo Buffers 59449344 bytes
SQL> alter database mount;
Database altered.
SQL>
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
GROUP# ARC STATUS
---------- --- ----------------
1 YES UNUSED
9 NO CURRENT
17 YES UNUSED
15 YES UNUSED
13 YES UNUSED
11 YES UNUSED
7 YES UNUSED
5 YES UNUSED
3 YES UNUSED
10 YES UNUSED
18 YES UNUSED
GROUP# ARC STATUS
---------- --- ----------------
8 YES UNUSED
16 YES UNUSED
2 YES UNUSED
12 NO CURRENT
4 YES UNUSED
14 YES UNUSED
6 YES UNUSED
18 rows selected.
Example of dropping a redologfiles:
SQL> ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 9
*
ERROR at line 1:
ORA-01623: log 9 is current log for instance QPRODN1 (thread 1) - cannot drop
ORA-00312: online log 9 thread 1: '+REDO/QPROD/ONLINELOG/group_9'
ORA-00312: online log 9 thread 1: '+REDO/QPROD/ONLINELOG/group_9'
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter database open read only;
Database altered.
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initQPRODN1.ora';
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size 7651632 bytes
Variable Size 4966063824 bytes
Database Buffers 1.1073E+10 bytes
Redo Buffers 59449344 bytes
SQL> alter database mount;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 9
*
ERROR at line 1:
ORA-01623: log 9 is current log for instance QPRODN1 (thread 1) - cannot drop
ORA-00312: online log 9 thread 1: '+REDO/QPROD/ONLINELOG/group_9'
ORA-00312: online log 9 thread 1: '+REDO/QPROD/ONLINELOG/group_9'
Now drop the standby redolog files and re-create it in standby side:
SQL> alter database drop standby logfile group 19;
alter database drop standby logfile group 20;
alter database drop standby logfile group 21;
alter database drop standby logfile group 22;
alter database drop standby logfile group 23;
alter database drop standby logfile group 24;
alter database drop standby logfile group 25;
alter database drop standby logfile group 26;
alter database drop standby logfile group 27;
alter database drop standby logfile group 28;
alter database drop standby logfile group 29;
alter database drop standby logfile group 30;
alter database drop standby logfile group 31;
alter database drop standby logfile group 32;
alter database drop standby logfile group 33;
alter database drop standby logfile group 34;
alter database drop standby logfile group 35;
alter database drop standby logfile group 36;
alter database drop standby logfile group 37;
alter database drop standby logfile group 38;
alter database drop standby logfile group 39;
alter database drop standby logfile group 40;
alter database drop standby logfile group 41;
alter database drop standby logfile group 42;
alter database drop standby logfile group 43;
alter database drop standby logfile group 44;
alter database drop standby logfile group 45;
alter database drop standby logfile group 46;
alter database drop standby logfile group 47;
alter database drop standby logfile group 48;
alter database drop standby logfile group 49;
alter database drop standby logfile group 50;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP 19 ('+DATA', '+REDO') SIZE 500M,
GROUP 20 ('+DATA', '+REDO') SIZE 500M,
GROUP 21 ('+DATA', '+REDO') SIZE 500M,
GROUP 22 ('+DATA', '+REDO') SIZE 500M,
GROUP 23 ('+DATA', '+REDO') SIZE 500M,
GROUP 24 ('+DATA', '+REDO') SIZE 500M,
GROUP 25 ('+DATA', '+REDO') SIZE 500M,
GROUP 26 ('+DATA', '+REDO') SIZE 500M,
GROUP 27 ('+DATA', '+REDO') SIZE 500M,
GROUP 28 ('+DATA', '+REDO') SIZE 500M;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 29 ('+DATA', '+REDO') SIZE 500M,
GROUP 30 ('+DATA', '+REDO') SIZE 500M,
GROUP 31 ('+DATA', '+REDO') SIZE 500M,
GROUP 32 ('+DATA', '+REDO') SIZE 500M,
GROUP 33 ('+DATA', '+REDO') SIZE 500M,
GROUP 34 ('+DATA', '+REDO') SIZE 500M,
GROUP 35 ('+DATA', '+REDO') SIZE 500M,
GROUP 36 ('+DATA', '+REDO') SIZE 500M,
GROUP 37 ('+DATA', '+REDO') SIZE 500M,
GROUP 38 ('+DATA', '+REDO') SIZE 500M;
Take a backup and create spfile in asm diskgroup with a pfile.
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ cp initQPRODN1.ora initQPRODN1.ora_org09032020
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 00:52:46 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initQPRODN1.ora';
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size 7651632 bytes
Variable Size 4966063824 bytes
Database Buffers 1.1073E+10 bytes
Redo Buffers 59449344 bytes
SQL> create spfile='+DATA/QPRODN/SPFILE/spfileeftstn.ora' from pfile;
File created.
SQL> exit
Copy the pfile to node2 and provide the spfile location in pfile.
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ scp initQPRODN1.ora oracle@10.40.135.24:/u01/app/oracle/product/12.1.0/db_1/dbs
initQPRODN1.ora 100% |***********************************************************************************************************************************************
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ ssh oracle@NODE2
oracle@mqm-testdb6:/u01/app/oracle/product/12.1.0/db_1/dbs$ mv initQPRODN1.ora initQPRODN2.ora
oracle@mqm-testdb6:/u01/app/oracle/product/12.1.0/db_1/dbs$
oracle@mqm-testdb6:/u01/app/oracle/product/12.1.0/db_1/dbs$
oracle@mqm-testdb6:/u01/app/oracle/product/12.1.0/db_1/dbs$ cat initQPRODN2.ora
spfile='+DATA/QPRODN/SPFILE/spfileeftstn.ora'
Remove and add the $ORACLE_HOME through srvctl:
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$srvctl add database -db QPRODN -o /u01/app/oracle/product/12.1.0/db_1 -startoption mount -role physical_standby -pwfile +DATA/QPRODN/PASSWORD/orrapwQPRODs
PRCD-1000 : Database QPRODn already exists
PRCR-1086 : resource ora.QPRODn.db is already registered
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ srvctl remove database -db QPRODN
Remove the database QPRODN? (y/[n]) yes
rapwQPRODs-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ srvctl add database -db QPRODN -o /u01/app/oracle/product/12.1.0/db_1 -startoption mount -role physical_standby -pwfile +DATA/QPRODN/PASSWORD/or
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ srvctl add instance -instance QPRODN -db QPRODn -node mqm-testdb5
Add instance through srvctl:
oracle@mqm-testdb6:~$ srvctl add instance -instance QPRODN2 -db QPRODn -node mqm-testdb6
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ srvctl modify database -d QPRODN -spfile '+DATA/QPRODN/SPFILE/spfileQPRODN.ora'
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ srvctl config database -d QPRODN
Database unique name: QPRODN
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/QPRODN/SPFILE/spfileQPRODN.ora
Password file: +DATA/QPRODN/PASSWORD/orapwQPROD
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: QPRODN1,QPRODN2
Configured nodes: mqm-testdb5,mqm-testdb6
Database is administrator managed
Startup the database and check the mrp status:
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/dbs$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 01:03:09 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1.6106E+10 bytes
Fixed Size 7651632 bytes
Variable Size 4966063824 bytes
Database Buffers 1.1073E+10 bytes
Redo Buffers 59449344 bytes
SQL> alter database mount standby database;
Database altered.
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
SQL> /
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
SQL>
racle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl config scan
SCAN name: mqmtest-scan, Network: 1
Subnet IPv4: 10.50.125.0/255.255.255.0/net0, static
Subnet IPv6:
SCAN 0 IPv4 VIP: 10.50.125.19
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 1 IPv4 VIP: 10.50.125.17
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
SCAN 2 IPv4 VIP: 10.50.125.18
SCAN VIP is enabled.
SCAN VIP is individually enabled on nodes:
SCAN VIP is individually disabled on nodes:
Add service through srvctl:
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl add service -database QPRODN -service QPRODNSN -preferred QPRODN1,QPRODN2
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl ENABLE SERVICE -database QPRODN -service QPRODNSN
PRCC-1010 : QPRODNSN was already enabled
PRCR-1002 : Resource ora.QPRODn.QPRODnsn.svc is already enabled
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl START SERVICE -database QPRODN -service QPRODNSN
PRCD-1084 : Failed to start service QPRODNSN
PRCR-1079 : Failed to start resource ora.QPRODn.QPRODnsn.svc
CRS-2800: Cannot start resource 'ora.QPRODn.db' as it is already in the INTERMEDIATE state on server 'mqm-testdb5'
CRS-2632: There are no more servers to try to place resource 'ora.QPRODn.QPRODnsn.svc' on that would satisfy its placement policy
CRS-2674: Start of 'ora.QPRODn.db' on 'mqm-testdb6' failed
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 01:43:31 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 01:45:40 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> show parameter dispatcher;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=srpstb
XDB)
max_dispatchers integer
SQL> alter system set dispatchers='(PROTOCOL=TCP) (SERVICE=QPRODNXDB)' scope=both sid='*';
System altered.
SQL> show parameter dispatcher;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=QPROD
NXDB)
max_dispatchers integer
SQL> exit
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): mqm-testdb6,mqm-testdb5
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl stop listener
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl start listener
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): mqm-testdb6,mqm-testdb5
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 02:04:14 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
SQL> exit
oracle@mqm-testdb5:/u01/app/oracle/product/12.1.0/db_1/network/admin$ srvctl stop instance -d QPRODN -i QPRODN2
oracle@mqm-testdb5:/tmp$ ls -ltrh pwdQPROD
-rw-r----- 1 oracle oinstall 7.5K Mar 9 02:17 pwdQPROD
oracle@mqm-testdb5:/tmp$ pwd
/tmp
oracle@mqm-testdb5:/tmp$ srvctl config database
QPRODN
oracle@mqm-testdb5:/tmp$ srvctl add database -db QPRODN -o /u01/app/oracle/product/12.1.0/db_1 -startoption mount -role physical_standby -pwfile +DATA/QPRODN/PASSWORD/pwdQPROD
PRCS-1007 : Server pool QPRODN already exists
PRCR-1086 : server pool ora.QPRODN is already registered
oracle@mqm-testdb5:/tmp$ srvctl status database -db QPRODN
Instance QPRODN1 is running on node mqm-testdb5
Instance QPRODN2 is not running on node mqm-testdb6
oracle@mqm-testdb5:/tmp$ srvctl config database -db QPRODN
Database unique name: QPRODN
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/QPRODN/SPFILE/spfileQPRODN.ora
Password file: +DATA/QPRODN/PASSWORD/orapwQPROD
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: REDO,DATA
Mount point paths:
Services: QPRODNSN
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: QPRODN1,QPRODN2
Configured nodes: mqm-testdb5,mqm-testdb6
Database is administrator managed
oracle@mqm-testdb5:/tmp$ srvctl modify database -db QPRODN -o /u01/app/oracle/product/12.1.0/db_1 -startoption mount -role physical_standby -pwfile +DATA/QPRODN/PASSWORD/pwdQPROD
oracle@mqm-testdb5:/tmp$ srvctl config database -db QPRODN
Database unique name: QPRODN
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/QPRODN/SPFILE/spfileQPRODN.ora
Password file: +DATA/QPRODN/PASSWORD/pwdQPROD
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: REDO,DATA
Mount point paths:
Services: QPRODNSN
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: QPRODN1,QPRODN2
Configured nodes: mqm-testdb5,mqm-testdb6
Database is administrator managed
oracle@mqm-testdb5:/tmp$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 02:30:18 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CLOSING 7631 1
ARCH CLOSING 7273 2
RFS IDLE 0 0
RFS IDLE 7632 1
RFS IDLE 0 0
RFS IDLE 7274 2
RFS IDLE 0 0
RFS IDLE 0 0
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
15 rows selected.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CLOSING 7631 1
ARCH CLOSING 7273 2
RFS IDLE 0 0
RFS IDLE 7632 1
RFS IDLE 0 0
RFS IDLE 7274 2
RFS IDLE 0 0
RFS IDLE 0 0
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
MRP0 APPLYING_LOG 7632 1
16 rows selected.
SQL> select name,open_mode,database_role,cdb from v$database;
NAME OPEN_MODE DATABASE_ROLE CDB
--------- -------------------- ---------------- ---
QPROD MOUNTED PHYSICAL STANDBY NO
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> @sync.sql;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 7631 7631 0
2 7273 7273 0
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:37:27 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CLOSING 7632 1
ARCH CLOSING 7631 1
ARCH CLOSING 7274 2
RFS IDLE 0 0
RFS IDLE 7633 1
RFS IDLE 0 0
RFS IDLE 7275 2
RFS IDLE 0 0
RFS IDLE 0 0
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
MRP0 APPLYING_LOG 7275 2
16 rows selected.
SQL> select thread#,group#,status,bytes/1024/1024 from v$log;
THREAD# GROUP# STATUS BYTES/1024/1024
---------- ---------- ---------------- ---------------
1 1 UNUSED 500
1 9 CLEARING 500
1 17 UNUSED 500
1 15 UNUSED 500
1 13 UNUSED 500
1 11 UNUSED 500
1 7 UNUSED 500
1 5 UNUSED 500
1 3 UNUSED 500
2 10 UNUSED 500
2 18 UNUSED 500
THREAD# GROUP# STATUS BYTES/1024/1024
---------- ---------- ---------------- ---------------
2 8 UNUSED 500
2 16 UNUSED 500
2 2 UNUSED 500
2 12 CLEARING 500
2 4 UNUSED 500
2 14 UNUSED 500
2 6 UNUSED 500
18 rows selected.
SQL> ALTER DATABASE DROP LOGFILE GROUP 9;
ALTER DATABASE DROP LOGFILE GROUP 9
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> alter system set standby_file_management=’MANUAL;^C
SQL>
SQL>
SQL> alter system set standby_file_management='MANUAL';
System altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 9;
Database altered.
SQL> ALTER DATABASE DROP LOGFILE GROUP 12;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 9('+DATA', '+REDO') SIZE 500M;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 12('+DATA', '+REDO') SIZE 500M;
Database altered.
SQL> alter system set standby_file_management='AUTO';
System altered.
SQL> select thread#,group#,status,bytes/1024/1024 from v$log;
THREAD# GROUP# STATUS BYTES/1024/1024
---------- ---------- ---------------- ---------------
1 1 UNUSED 500
1 9 UNUSED 500
1 17 UNUSED 500
1 15 UNUSED 500
1 13 UNUSED 500
1 11 UNUSED 500
1 7 UNUSED 500
1 5 UNUSED 500
1 3 UNUSED 500
2 10 UNUSED 500
2 18 UNUSED 500
THREAD# GROUP# STATUS BYTES/1024/1024
---------- ---------- ---------------- ---------------
2 8 UNUSED 500
2 16 UNUSED 500
2 2 UNUSED 500
2 12 UNUSED 500
2 4 UNUSED 500
2 14 UNUSED 500
2 6 UNUSED 500
18 rows selected.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:49:14 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> @syn.sql;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 7632 7632 0
2 7274 7274 0
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:49:46 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select thread#,group#,status,bytes/1024/1024 from v$log;
THREAD# GROUP# STATUS BYTES/1024/1024
---------- ---------- ---------------- ---------------
1 1 UNUSED 500
1 9 UNUSED 500
1 17 UNUSED 500
1 15 UNUSED 500
1 13 UNUSED 500
1 11 UNUSED 500
1 7 UNUSED 500
1 5 UNUSED 500
1 3 UNUSED 500
2 10 UNUSED 500
2 18 UNUSED 500
THREAD# GROUP# STATUS BYTES/1024/1024
---------- ---------- ---------------- ---------------
2 8 UNUSED 500
2 16 UNUSED 500
2 2 UNUSED 500
2 12 UNUSED 500
2 4 UNUSED 500
2 14 UNUSED 500
2 6 UNUSED 500
18 rows selected.
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CLOSING 7632 1
ARCH CLOSING 7631 1
ARCH CLOSING 7274 2
RFS IDLE 0 0
RFS IDLE 7633 1
RFS IDLE 0 0
RFS IDLE 7275 2
RFS IDLE 0 0
RFS IDLE 0 0
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
MRP0 APPLYING_LOG 7633 1
16 rows selected.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:50:27 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
oracle@mqm-testdb5:~$ srvctl start instance -d QPRODN -i QPRODN2
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:51:56 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> alter database open read only;
Database altered.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb6:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:52:42 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database open read only;
Database altered.
oracle@mqm-testdb6:~$ srvctl status database -d QPRODN
Instance QPRODN1 is running on node mqm-testdb5
Instance QPRODN2 is running on node mqm-testdb6
oracle@mqm-testdb6:~$
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 11:54:00 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select name,open_mode,database_role,cdb from v$database;
NAME OPEN_MODE DATABASE_ROLE CDB
--------- -------------------- ---------------- ---
QPROD READ ONLY PHYSICAL STANDBY NO
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CLOSING 7632 1
ARCH CLOSING 7631 1
ARCH CLOSING 7274 2
RFS IDLE 0 0
RFS IDLE 7633 1
RFS IDLE 0 0
RFS IDLE 7275 2
RFS IDLE 0 0
RFS IDLE 0 0
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
15 rows selected.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
ARCH CLOSING 7632 1
ARCH CLOSING 7631 1
ARCH CLOSING 7274 2
RFS IDLE 0 0
RFS IDLE 7633 1
RFS IDLE 0 0
RFS IDLE 7275 2
RFS IDLE 0 0
RFS IDLE 0 0
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
MRP0 APPLYING_LOG 7633 1
16 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> select name,open_mode,database_role,cdb from v$database;
NAME OPEN_MODE DATABASE_ROLE CDB
--------- -------------------- ---------------- ---
QPROD READ ONLY WITH APPLY PHYSICAL STANDBY NO
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 12:00:17 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
SQL>
SQL>
SQL>
SQL> exit
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:11:26 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> alter database recover managed standby database cancel;
Database altered.
oracle@mqm-testdb5:~$ srvctl config database -d QPRODN
Database unique name: QPRODN
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/QPRODN/SPFILE/spfileQPRODN.ora
Password file: +DATA/QPRODN/PASSWORD/pwdQPROD
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: REDO,DATA
Mount point paths:
Services: QPRODNSN
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: QPRODN1,QPRODN2
Configured nodes: mqm-testdb5,mqm-testdb6
Database is administrator managed
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$ srvctl config database -d QPRODN
Database unique name: QPRODN
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/QPRODN/SPFILE/spfileQPRODN.ora
Password file: +DATA/QPRODN/PASSWORD/pwdQPROD
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: REDO,DATA
Mount point paths:
Services: QPRODNSN
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: QPRODN1,QPRODN2
Configured nodes: mqm-testdb5,mqm-testdb6
Database is administrator managed
oracle@mqm-testdb5:~$ srvctl modify database -d QPRODN -pwfile '+DATA/QPRODN/PASSWORD/pwdQPROD'
oracle@mqm-testdb5:~$ srvctl config database -d QPRODN
Database unique name: QPRODN
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/db_1
Oracle user: oracle
Spfile: +DATA/QPRODN/SPFILE/spfileQPRODN.ora
Password file: +DATA/QPRODN/PASSWORD/pwdQPROD
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: REDO,DATA
Mount point paths:
Services: QPRODNSN
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: QPRODN1,QPRODN2
Configured nodes: mqm-testdb5,mqm-testdb6
Database is administrator managed
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:40:33 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CLOSING 7275 2
ARCH CONNECTED 0 0
ARCH CLOSING 7633 1
ARCH CLOSING 7631 1
ARCH CLOSING 7274 2
RFS IDLE 0 0
RFS IDLE 7634 1
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
14 rows selected.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 13:56:42 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> show parameter dg_broker_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/12.1.0
/db_1/dbs/dr1QPRODN.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.1.0
/db_1/dbs/dr2QPRODN.dat
SQL>
SQL>
SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;
INSTANCE_NAME
----------------
QPRODN1
SQL>
SQL>
SQL>
SQL> alter system set dg_broker_start=FALSE scope=both sid='*';
System altered.
SQL> alter system set dg_broker_config_file1='+DATA/QPRODN/DGBROKERCONFIGFILE/dr1QPRODN.dat' scope=both sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='+DATA/QPRODN/DGBROKERCONFIGFILE/dr2QPRODN.dat' scope=both sid='*';
System altered.
SQL> alter system set dg_broker_start=TRUE scope=both sid='*';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='' scope=both;
System altered.
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CLOSING 7275 2
ARCH CONNECTED 0 0
ARCH CLOSING 7634 1
ARCH CLOSING 7631 1
ARCH CLOSING 7274 2
RFS IDLE 7277 2
MRP0 APPLYING_LOG 7277 2
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
RFS IDLE 0 0
RFS IDLE 0 0
13 rows selected.
SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$ dgmgrl
DGMGRL for Solaris: Version 12.1.0.2.0 - 64bit Production
Copyright (c) 2000, 2013, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys_1234@QPRODN
Connected as SYSDBA.
DGMGRL> show configuration;
Configuration - testrac_dg
Protection Mode: MaxPerformance
Members:
QPROD - Primary database
QPRODn - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 53 seconds ago)
oracle@mqm-testdb5:~$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 9 14:47:28 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> select process,status,sequence#,thread# from v$managed_standby;
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
ARCH CLOSING 7275 2
ARCH CONNECTED 0 0
ARCH CLOSING 7634 1
ARCH CLOSING 7631 1
ARCH CLOSING 7274 2
RFS IDLE 7277 2
MRP0 APPLYING_LOG 7277 2
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
PROCESS STATUS SEQUENCE# THREAD#
--------- ------------ ---------- ----------
RFS IDLE 0 0
RFS IDLE 0 0
13 rows selected.
SQL> @syn.sql;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 7634 7634 0
2 7276 7276 0
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$
oracle@mqm-testdb5:~$ srvctl status database -d QPRODN
Instance QPRODN1 is running on node mqm-testdb5
Instance QPRODN2 is running on node mqm-testdb6