News

  1. Home
  2. All posts
All posts, DR, Oracle by Team Ajara

Converting physical standby DB to snapshot standby

Managing Snapshot standby database in Oracle.

In this article we will see step by step procedure to convert physical standby database to snapshot standby database.

At Standby database

Step 1. Check the mode of the database

SQL> sqlplus / as sysdba

SQL>select opnem_mode from v$database;

Step 2

shutdown the DB if the open_mode is read only

SQL > shutdown immediate

SQL> startup mount;

Step 3

Check and stop redo apply

SQL>Select process,Status ,thread#,sequence#, block#, blocks from v$managed_standby;

SQL>alter database recover managed standby database cancel;

Step 4

Check whether fast recovery area has been configured.

SQL>sho parameter db_recover_file_dest

Step 5

Convert Physical Standby to Snapshot standby

SQL>alter database convert to snapshot standby ;

 

Step 6

open the snapshot standby in read/write mode

SQL>alter database open read write;

SQL> Select name,open_mode,database_role,db_unique_name from v$database;

Step 7

At  snapshot standby database

Verify open mode of pluggable data base.

SQL>select name,open_mode from v$pdbs;

if open mode is not read write
SQL> alter pluggable database pdb1 open;

connect to pluggable database
SQL> conn sys/oracle@pdb1 as sysdba

SQL> show con_name

Step 8
Create test user.

SQL>create user snptst identified by snptst ;
SQL>grant dba to snptst;

Step 9

connect to test user

SQL> conn snptst/snptst@pdb1

Create test table
SQL> creat table tst_tab (id number);

SQL> insert into tst_tab values(1);
SQL> insert into tst_tab values(2);

SQL>commit;
SQL> select * from tst_tab;

 

Converting a Snapshot Standby database into a Physical Standby Database

Step 1

verify open mode of database (it should be in read write)

SQL>select open_mode from v$database;

SQL> conn sys/oracle@standby as sysdba

Step 2

If open_mode is read write then shutdown

SQL>shut immediate
SQL> startup mount

Step 3

Convert database to physical standby

SQL>alter database convert to physical standby;

SQL>select name,database_role,open_mode ,db_unique_name from v$database;

Step 4

open standby read only

SQL> alter database open;

Step  5
check open mode of pluggable database.

SQL> select name,open_mode from v$pdbs;

Step 6

open pluggable database

SQL> alter pluggable database pdb1 open;

Check test user and test table which we have created on snapshot database
SQL>conn snptst/snptst@pdb1

o/p ==> should give error

SQL>conn sys/oracle@pdb1 as sysdba

SQL> select * from dba_users where username=’SNPTST’;

no rows selected.
SQL>select * from dba_tables where table_name=TST_SNP’;

no rows selected.

 

 

See also: