Overview:
This article explain the process of converting Physical Standby in Read Only mode to a Snapshot Standby.Snapshot Standby open DB in Read Write mode for disaster recovery testing. This is to allow the database in DR site to be accessed by applications and perform tests on how the database could be accessed and application purposes be met during a disaster scenario while the actual primary database is not shutdown completely.
Environment Details:
|
Hostname
|
Node1 and Node2
|
|
Operating system
|
Linux
|
|
Environment
|
Production and Standby
|
|
Oracle Home
|
/u01/app/oracle/product/11.2.0/db
|
Prerequisites for converting the
database
database
- Ensure
the FRA is configured.
SQL> sho parameter recover
Note:- Snapshot standby need not have Flashback enabled at
db level but if it is enabled then FRA would have already been configured
db level but if it is enabled then FRA would have already been configured
- Ensure the redo transport parameters for the
primary and redo apply for the standby are configured:
SQL> set lin 999
SQL> column NAME format
a30
a30
SQL> column VALUE format
a84
a84
SQL> select NAME, VALUE
from v$parameter
from v$parameter
where NAME in
(‘db_name’, ‘db_unique_name’, ‘control_files’,
‘log_archive_config’,’fal_client’, ‘fal_server’,
(‘db_name’, ‘db_unique_name’, ‘control_files’,
‘log_archive_config’,’fal_client’, ‘fal_server’,
‘log_archive_dest_1’,
‘log_archive_dest_2’, ‘log_archive_dest_3’, ‘log_archive_dest_4’,
‘log_archive_dest_2’, ‘log_archive_dest_3’, ‘log_archive_dest_4’,
‘log_archive_dest_state_1′,’log_archive_dest_state_2’,‘log_archive_dest_state_3’,
‘log_archive_dest_state_4′,’log_file_name_convert’,
‘remote_login_passwordfile’);
‘log_archive_dest_state_4′,’log_file_name_convert’,
‘remote_login_passwordfile’);
- Ensure the standby redo log files are
configured on the primary and standby database.
SQL> column member
format a50
format a50
SQL> SELECT a.group#,
a.member, b.bytes/1024/1024 bytes_MB FROM v$logfile a,
v$log b WHERE a.group# = b.group# union select a.group#,
a.member, b.bytes/1024/1024 bytes_MB from v$logfile a,
v$standby_log b WHERE a.group# =
b.group#;
a.member, b.bytes/1024/1024 bytes_MB FROM v$logfile a,
v$log b WHERE a.group# = b.group# union select a.group#,
a.member, b.bytes/1024/1024 bytes_MB from v$logfile a,
v$standby_log b WHERE a.group# =
b.group#;
Checks on DR Test
- Run the following query to pull information
about the database on both Primary and standby
SQL> set lin 175
SQL> column NAME format
a10
a10
SQL> column LOG_MODE
format a10
format a10
SQL> column OPEN_MODE
format a10
format a10
SQL> COLUMN CONTROLFILE_TYPE
format a16
format a16
SQL> column
PROTECTION_MODE format a20
PROTECTION_MODE format a20
SQL> column
PROTECTION_LEVEL format a20
PROTECTION_LEVEL format a20
SQL> column
DATABASE_ROLE format a20
DATABASE_ROLE format a20
SQL> column GUARD_STATUS
format a12
format a12
SQL> column
DATAGUARD_BROKER format a16
DATAGUARD_BROKER format a16
SQL> select NAME,DATABASE_ROLE,LOG_MODE,CONTROLFILE_TYPE, OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,GUARD_STATUS,FORCE_LOGGING,DATAGUARD_BROKER from v$database;
- Verifying That the Standby Has Received All
Redo, you can carry your individual steps.
On Primary and on
standby –> To check if the scn’s are close.
standby –> To check if the scn’s are close.
SQL> set numwidth 20
SQL> select current_scn
from v$database;
from v$database;
On Standby –>
To check if the MRP is running
To check if the MRP is running
SQL> select
status,process from v$managed_standby where process like ‘%MRP%’;
status,process from v$managed_standby where process like ‘%MRP%’;
STATUS
PROCESS
PROCESS
————————————————
————————————
————————————
APPLYING_LOG MRP0
On standby –> to Check if all or near to all logs
are applied
are applied
SQL> SELECT
SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
And to check real
time apply On primary / standby –> It should say “MANAGED REAL TIME
APPLY” in one of the rows.
time apply On primary / standby –> It should say “MANAGED REAL TIME
APPLY” in one of the rows.
SQL> SELECT
RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
SQL> SELECT
CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
Compare the
sequence# from primary to standby query.
If the standby is not receiving the current redo, you cannot convert/switchover.
Note that if your primary is a RAC, you should see multiple LGWR to RFS
connections, one for each primary thread. You must validate that each primary
thread is caught up.
sequence# from primary to standby query.
If the standby is not receiving the current redo, you cannot convert/switchover.
Note that if your primary is a RAC, you should see multiple LGWR to RFS
connections, one for each primary thread. You must validate that each primary
thread is caught up.
- Check the apply is caught up
Once determined the
redo stream is current, ensure all redo has been applied to standby database.
redo stream is current, ensure all redo has been applied to standby database.
On the standby
database:
database:
SQL> SELECT
CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
CLIENT_PROCESS,PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;
Note: if you don’t
see MRP0, the apply is not running
see MRP0, the apply is not running
To
convert/switchover, you must start the apply service and wait for it to catch
up with the current redo stream before starting. If you see the MRP0 line but
it has a status of WAIT_FOR_GAP, you cannot convert/switchover until the gap
has been resolved.
convert/switchover, you must start the apply service and wait for it to catch
up with the current redo stream before starting. If you see the MRP0 line but
it has a status of WAIT_FOR_GAP, you cannot convert/switchover until the gap
has been resolved.
- Verify there are no large Gaps
Identify the
current sequence number for each thread on the primary database.
current sequence number for each thread on the primary database.
On the Primary:
SQL> SELECT
THREAD#,SEQUENCE#,STATUS FROM V$LOG;
THREAD#,SEQUENCE#,STATUS FROM V$LOG;
Verify the target
physical standby database has applied up to, but not including the logs from
the primary query. On the standby the following query should be within 1 or 2
of the primary query result.
physical standby database has applied up to, but not including the logs from
the primary query. On the standby the following query should be within 1 or 2
of the primary query result.
On the Standby
database:
database:
SQL> SELECT
THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = ‘YES’ AND RESETLOGS_CHANGE# = SELECTRESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS =’CURRENT’) GROUP BY THREAD#;
THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = ‘YES’ AND RESETLOGS_CHANGE# = SELECTRESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS =’CURRENT’) GROUP BY THREAD#;
Convert to Physical
standby to Snapshot Standby manual way
standby to Snapshot Standby manual way
- On standby
to be converted to snapshot mode.
Tail the Alert Logs (optional) on all
instances:
instances:
Locate alert logs by showing database parameter
background_dump_dest:
background_dump_dest:
SQL> SHOW PARAMETER background_dump_dest;
% tail -f /u01/app/diag/rdbms/standby/standby1/trace/alert*
- Stop MRP
SQL> alter database recover managed standby
database cancel;
Database alerted
database cancel;
Database alerted
- Record SCN
and timestamp
SQL>
col current_scn format 99999999999999
col current_scn format 99999999999999
SQL>
select current_scn from v$database;
select current_scn from v$database;
- Manual
SQLPLUS Convert to snapshot standby:
For
standalone
standalone
SQL> shutdown
immediate
immediate
SQL> startup
mount
mount
For
RAC
RAC
$srvctl
stop database -d DBNAME
stop database -d DBNAME
$srvctl
start database -d DBNAME -o mount
start database -d DBNAME -o mount
SQL>
select open_mode, database_role from v$database;
select open_mode, database_role from v$database;
SQL>
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
SQL>
shutdown immediate
shutdown immediate
SQL>
startup mount
startup mount
SQL>
select open_mode, database_role from v$database;
select open_mode, database_role from v$database;
SQL>
ALTER DATABASE OPEN;
ALTER DATABASE OPEN;
SQL> select open_mode, database_role from v$database;
Convert to
Snapshot Standby back to Physical standby manual way
For RAC
$srvctl stop database -d DBNAME
$srvctl startup database -d DBNAME
-o mount
-o mount
SQL> ALTER
DATABASE CONVERT TO PHYSICAL STANDBY;
DATABASE CONVERT TO PHYSICAL STANDBY;
srvctl stop database -d DBNAME
For Standalone
SQL> shutdown immediate
SQL> startup mount
SQL> ALTER DATABASE CONVERT TO
PHYSICAL STANDBY;
PHYSICAL STANDBY;
SQL> shutdown immediate
Restart database
For standalone
SQL> startup mount
For RAC
srvctl start database -d DBNAME
-o mount
-o mount
- Start MRP and open read only
SQL> alter
database open read only;
database open read only;
SQL> alter
database recover managed standby database using current logfile disconnect;
database recover managed standby database using current logfile disconnect;
- Perform the Physical standby check out
On Primary:
Check the alert log of primary database and tail the alert.
Check the archive error destination
Check the archive error destination
sql>col
DEST_NAME for a40
DEST_NAME for a40
sql>set
lines 200
lines 200
sql>select
DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest;
DEST_ID,DEST_NAME,STATUS,ERROR from v$archive_dest;
Check the current archive log (sequence)
sql>select thread#,max(sequence#) from v$log where
status=’CURRENT’ group by thread# order by 1;
status=’CURRENT’ group by thread# order by 1;
On Standby:
Check the alert log of standby database and tail the alert.
Check the current archive log (sequence) applied.
sql>select thread#,max(sequence#) from
v$archived_log where applied=’YES’ group by thread# order by 1;
v$archived_log where applied=’YES’ group by thread# order by 1;
Check the status of MRP
$ps -ef |grep mrp
sql>select PROCESS,THREAD#,SEQUENCE#,STATUS from
v$managed_standby where process=’MRP0′;
v$managed_standby where process=’MRP0′;
PROCESS THREAD# SEQUENCE# STATUS
—————————
———- ———- ————————————
———- ———- ————————————
MRP0 1 279486 WAIT_FOR_LOG
Conclusion:
From this article we have learnt how to convert Physical standby database to Snapshot standby database using manual method.
Leave a Reply