Home > 11g > creation of snapshot database and reverting back to physical standby.

creation of snapshot database and reverting back to physical standby.

March 9, 2014

One of the 11g new feature is stanpshot database. i.e. nothing but converting physical standby database into normal database for read write operation.  This is mostly useful to perform any kind of testing before implementing in PRODUCTION.

 

1) Ensure that DB is set with FRA parameters.

db_recovery_file_dest

db_recovery_file_dest_size

2) stop the recovery if any.

alter database recover managed standby database cancel;

3) shutdown the DB and start in mount stage.

shutdown immediate;
startup mount;

4) now check the database status.

select open_mode,database_role from v$database;

5) Now covert the database to snapshot database.

alter database covert to snapshot standby;

6) Now check the database status.

select open_mode,database_role from v$database;
7) alter database open;
8) again check the status.

select open_mode,database_role from v$database;
to move back to physical standby

1) shutdown the database.

2) startup the database in mount state.

3) alter database convert to physical standby;

4) shutdown immediate;

5) startup;

6) start recovery;

 

 

 

complete log is as follows.

SQL> show parameter recovery_

NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
recovery_parallelism integer 0

SQL> alter system set db_recovery_file_dest_size=1G;

System altered.

SQL> alter system set db_recovery_file_dest=’/u01/app/FRA’ ;

System altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ ONLY WITH APPLY

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 405020672 bytes
Fixed Size 2228824 bytes
Variable Size 130026920 bytes
Database Buffers 268435456 bytes
Redo Buffers 4329472 bytes
Database mounted.

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
MOUNTED

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
——————– —————-
MOUNTED SNAPSHOT STANDBY

SQL> alter database open;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
——————– —————-
READ WRITE SNAPSHOT STANDBY

SQL> conn scott/tiger
Connected.
SQL> select * from test;

SRNO
———-
1
2
3

SQL> insert into TEST values(&srno);
Enter value for srno: 4
old 1: insert into TEST values(&srno)
new 1: insert into TEST values(4)

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

SRNO
———-
1
2
3
4

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 405020672 bytes
Fixed Size 2228824 bytes
Variable Size 130026920 bytes
Database Buffers 268435456 bytes
Redo Buffers 4329472 bytes
Database mounted.
SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
——————– —————-
MOUNTED SNAPSHOT STANDBY

SQL> alter database convert to physical standby;

Database altered.

SQL> select open_mode,database_role from v$database;
select open_mode,database_role from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 405020672 bytes
Fixed Size 2228824 bytes
Variable Size 130026920 bytes
Database Buffers 268435456 bytes
Redo Buffers 4329472 bytes
Database mounted.
SQL> alter database open;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
——————– —————-
READ ONLY PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
——————– —————-
READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> conn scott/tiger
Connected.
SQL> select * from test;

SRNO
———-
1
2
3

SQL>

Advertisements
Categories: 11g
%d bloggers like this: