Rolling forward physical standby database using RMAN incremental SCN backup
*************************************************************************************************
At PRIMARY database
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
17
At STANDBY database
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
10
Mistakenly archive log sequence no 11 deleted from primary database archive log location, till archive logs#16 received at physical standby.
But due missing archive log# 11, MRP process has been stopped, which results archive log gap.
Below is practical illustration, how to roll forward physical standby databases using RMAN incremental SCN backup.
1) Take min SCN from out of 3 queries from Physical standby
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------------
962773
SQL> select database_role, current_scn from v$database;
SQL> select min(checkpoint_change#) from v$datafile_header;
2) Stop MRP process at standby database
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3) Check MRP status
select RECOVERY_MODE from v$archive_dest_status;
select process,sequence#,status from v$managed_standby;
4) Take SCN based backup from primary database in order to sync with physical standby
$ rman target sys/dg123@prod
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 24 12:00:20 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (DBID=3216454145)
RMAN> backup incremental from scn 962773 database format '/u01/prod/fra/StbyIncr_%U';
Starting backup at 24-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
backup will be obsolete on date 01-JUL-17
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/prod/datafiles/prod/system01.dbf
input datafile file number=00002 name=/u01/prod/datafiles/prod/sysaux01.dbf
input datafile file number=00003 name=/u01/prod/datafiles/prod/undotbs01.dbf
input datafile file number=00004 name=/u01/prod/datafiles/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-JUN-17
channel ORA_DISK_1: finished piece 1 at 24-JUN-17
piece handle=/u01/prod/fra/StbyIncr_0as7jiv4_1_1 tag=TAG20170624T120908 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
using channel ORA_DISK_1
backup will be obsolete on date 01-JUL-17
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 24-JUN-17
channel ORA_DISK_1: finished piece 1 at 24-JUN-17
piece handle=/u01/prod/fra/StbyIncr_0bs7jiv5_1_1 tag=TAG20170624T120908 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 24-JUN-17
RMAN> backup current controlfile for standby format '/u01/prod/fra/Stby_control_%U';
Starting backup at 24-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 24-JUN-17
channel ORA_DISK_1: finished piece 1 at 24-JUN-17
piece handle=/u01/prod/fra/Stby_control_0cs7jive_1_1 tag=TAG20170624T120918 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUN-17
RMAN> exit
5) Copy RMAN backup including controlfile to standby FRA.
6) Connect RMAN to standby target
$ rman target sys/dg123@standby
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 24 12:14:20 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (DBID=3170486146)
RMAN> catalog start with '/u01/standby/fra/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/standby/fra/
List of Files Unknown to the Database
=====================================
File Name: /u01/standby/fra/Stby_control_0cs7jive_1_1
File Name: /u01/standby/fra/StbyIncr_0as7jiv4_1_1
File Name: /u01/standby/fra/StbyIncr_0bs7jiv5_1_1
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/standby/fra/Stby_control_0cs7jive_1_1
File Name: /u01/standby/fra/StbyIncr_0as7jiv4_1_1
File Name: /u01/standby/fra/StbyIncr_0bs7jiv5_1_1
7) Before proceed with recover database, make sure standby database should be MOUNT state. If not below error will occure
RMAN> recover database noredo;
Starting recover at 24-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/standby/datafiles/system01.dbf
destination for restore of datafile 00002: /u01/standby/datafiles/sysaux01.dbf
destination for restore of datafile 00003: /u01/standby/datafiles/undotbs01.dbf
destination for restore of datafile 00004: /u01/standby/datafiles/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/standby/fra/StbyIncr_0as7jiv4_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/24/2017 12:16:25
ORA-19870: error while restoring backup piece /u01/standby/fra/StbyIncr_0as7jiv4_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1
so,
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size 2213016 bytes
Variable Size 754977640 bytes
Database Buffers 520093696 bytes
Redo Buffers 8781824 bytes
Database mounted.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@stand dbs]$
[oracle@stand dbs]$ rman target sys/dg123@standby
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 24 13:05:24 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (DBID=3170486146, not open)
RMAN> recover database noredo;
Starting recover at 24-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/standby/datafiles/system01.dbf
destination for restore of datafile 00002: /u01/standby/datafiles/sysaux01.dbf
destination for restore of datafile 00003: /u01/standby/datafiles/undotbs01.dbf
destination for restore of datafile 00004: /u01/standby/datafiles/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/standby/fra/StbyIncr_0as7jiv4_1_1
channel ORA_DISK_1: piece handle=/u01/standby/fra/StbyIncr_0as7jiv4_1_1 tag=TAG20170624T120908
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 24-JUN-17
RMAN>
[oracle@stand dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jun 24 13:06:30 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size 2213016 bytes
Variable Size 754977640 bytes
Database Buffers 520093696 bytes
Redo Buffers 8781824 bytes
SQL> !
[oracle@stand fra]$ ll
total 19720
drwxr-x--- 4 oracle oinstall 4096 Jun 24 11:37 standby
-rw-r----- 1 oracle oinstall 9797632 Jun 24 12:13 Stby_control_0cs7jive_1_1
-rw-r----- 1 oracle oinstall 557056 Jun 24 12:13 StbyIncr_0as7jiv4_1_1
-rw-r----- 1 oracle oinstall 9797632 Jun 24 12:14 StbyIncr_0bs7jiv5_1_1
[oracle@stand fra]$
[oracle@stand fra]$ rman target sys/dg123@standby
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 24 13:07:29 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (not mounted)
RMAN> restore standby controlfile from '/u01/standby/fra/Stby_control_0cs7jive_1_1';
Starting restore at 24-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/standby/datafiles/stndcnt.ctl
Finished restore at 24-JUN-17
RMAN> exit
Recovery Manager complete.
SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size 2213016 bytes
Variable Size 754977640 bytes
Database Buffers 520093696 bytes
Redo Buffers 8781824 bytes
Database mounted.
SQL>
SQL>
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY RECOVERY NEEDED
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
17
SQL>
SQL> /
MAX(SEQUENCE#)
--------------
18
SQL> select count(1) from sinu.emp;
select count(1) from sinu.emp;
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL>
SQL>
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.
SQL>
SQL>
SQL>
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> select count(1) from sinu.emp;
COUNT(1)
----------
4
SQL> select * from sinu.emp;
ID
----------
1
1
1
1
SQL>
*************************************************************************************************
At PRIMARY database
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
17
At STANDBY database
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
10
Mistakenly archive log sequence no 11 deleted from primary database archive log location, till archive logs#16 received at physical standby.
But due missing archive log# 11, MRP process has been stopped, which results archive log gap.
Below is practical illustration, how to roll forward physical standby databases using RMAN incremental SCN backup.
1) Take min SCN from out of 3 queries from Physical standby
SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------------
962773
SQL> select database_role, current_scn from v$database;
SQL> select min(checkpoint_change#) from v$datafile_header;
2) Stop MRP process at standby database
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3) Check MRP status
select RECOVERY_MODE from v$archive_dest_status;
select process,sequence#,status from v$managed_standby;
4) Take SCN based backup from primary database in order to sync with physical standby
$ rman target sys/dg123@prod
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 24 12:00:20 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (DBID=3216454145)
RMAN> backup incremental from scn 962773 database format '/u01/prod/fra/StbyIncr_%U';
Starting backup at 24-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
backup will be obsolete on date 01-JUL-17
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/prod/datafiles/prod/system01.dbf
input datafile file number=00002 name=/u01/prod/datafiles/prod/sysaux01.dbf
input datafile file number=00003 name=/u01/prod/datafiles/prod/undotbs01.dbf
input datafile file number=00004 name=/u01/prod/datafiles/prod/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-JUN-17
channel ORA_DISK_1: finished piece 1 at 24-JUN-17
piece handle=/u01/prod/fra/StbyIncr_0as7jiv4_1_1 tag=TAG20170624T120908 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
using channel ORA_DISK_1
backup will be obsolete on date 01-JUL-17
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 24-JUN-17
channel ORA_DISK_1: finished piece 1 at 24-JUN-17
piece handle=/u01/prod/fra/StbyIncr_0bs7jiv5_1_1 tag=TAG20170624T120908 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 24-JUN-17
RMAN> backup current controlfile for standby format '/u01/prod/fra/Stby_control_%U';
Starting backup at 24-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 24-JUN-17
channel ORA_DISK_1: finished piece 1 at 24-JUN-17
piece handle=/u01/prod/fra/Stby_control_0cs7jive_1_1 tag=TAG20170624T120918 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-JUN-17
RMAN> exit
5) Copy RMAN backup including controlfile to standby FRA.
6) Connect RMAN to standby target
$ rman target sys/dg123@standby
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 24 12:14:20 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (DBID=3170486146)
RMAN> catalog start with '/u01/standby/fra/';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/standby/fra/
List of Files Unknown to the Database
=====================================
File Name: /u01/standby/fra/Stby_control_0cs7jive_1_1
File Name: /u01/standby/fra/StbyIncr_0as7jiv4_1_1
File Name: /u01/standby/fra/StbyIncr_0bs7jiv5_1_1
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/standby/fra/Stby_control_0cs7jive_1_1
File Name: /u01/standby/fra/StbyIncr_0as7jiv4_1_1
File Name: /u01/standby/fra/StbyIncr_0bs7jiv5_1_1
7) Before proceed with recover database, make sure standby database should be MOUNT state. If not below error will occure
RMAN> recover database noredo;
Starting recover at 24-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/standby/datafiles/system01.dbf
destination for restore of datafile 00002: /u01/standby/datafiles/sysaux01.dbf
destination for restore of datafile 00003: /u01/standby/datafiles/undotbs01.dbf
destination for restore of datafile 00004: /u01/standby/datafiles/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/standby/fra/StbyIncr_0as7jiv4_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/24/2017 12:16:25
ORA-19870: error while restoring backup piece /u01/standby/fra/StbyIncr_0as7jiv4_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1
so,
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size 2213016 bytes
Variable Size 754977640 bytes
Database Buffers 520093696 bytes
Redo Buffers 8781824 bytes
Database mounted.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@stand dbs]$
[oracle@stand dbs]$ rman target sys/dg123@standby
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 24 13:05:24 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (DBID=3170486146, not open)
RMAN> recover database noredo;
Starting recover at 24-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/standby/datafiles/system01.dbf
destination for restore of datafile 00002: /u01/standby/datafiles/sysaux01.dbf
destination for restore of datafile 00003: /u01/standby/datafiles/undotbs01.dbf
destination for restore of datafile 00004: /u01/standby/datafiles/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/standby/fra/StbyIncr_0as7jiv4_1_1
channel ORA_DISK_1: piece handle=/u01/standby/fra/StbyIncr_0as7jiv4_1_1 tag=TAG20170624T120908
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 24-JUN-17
RMAN>
[oracle@stand dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sat Jun 24 13:06:30 2017
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size 2213016 bytes
Variable Size 754977640 bytes
Database Buffers 520093696 bytes
Redo Buffers 8781824 bytes
SQL> !
[oracle@stand fra]$ ll
total 19720
drwxr-x--- 4 oracle oinstall 4096 Jun 24 11:37 standby
-rw-r----- 1 oracle oinstall 9797632 Jun 24 12:13 Stby_control_0cs7jive_1_1
-rw-r----- 1 oracle oinstall 557056 Jun 24 12:13 StbyIncr_0as7jiv4_1_1
-rw-r----- 1 oracle oinstall 9797632 Jun 24 12:14 StbyIncr_0bs7jiv5_1_1
[oracle@stand fra]$
[oracle@stand fra]$ rman target sys/dg123@standby
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jun 24 13:07:29 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: prod (not mounted)
RMAN> restore standby controlfile from '/u01/standby/fra/Stby_control_0cs7jive_1_1';
Starting restore at 24-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/standby/datafiles/stndcnt.ctl
Finished restore at 24-JUN-17
RMAN> exit
Recovery Manager complete.
SQL> shut immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1286066176 bytes
Fixed Size 2213016 bytes
Variable Size 754977640 bytes
Database Buffers 520093696 bytes
Redo Buffers 8781824 bytes
Database mounted.
SQL>
SQL>
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY RECOVERY NEEDED
SQL>
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
17
SQL>
SQL> /
MAX(SEQUENCE#)
--------------
18
SQL> select count(1) from sinu.emp;
select count(1) from sinu.emp;
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL>
SQL>
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database disconnect from session using current logfile;
Database altered.
SQL>
SQL>
SQL>
SQL> select OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;
OPEN_MODE PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
SQL> select count(1) from sinu.emp;
COUNT(1)
----------
4
SQL> select * from sinu.emp;
ID
----------
1
1
1
1
SQL>
Happy DBA'ing :)
No comments:
Post a Comment