STEPS TO CREATE SNAPSHOT STANDBY DATABASE
PRIMARY:
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
GPROD READ WRITE PRIMARY
SQL>
STANDBY
SQL> select name, open_mode, database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
——— ——————– —————-
GPROD MOUNTED PHYSICAL STANDBY
SQL>
MAKE THE FLASHBACK ON
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
NO
SQL> alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON
——————
YES
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 1006632960 bytes
Fixed Size 8628160 bytes
Variable Size 360712256 bytes
Database Buffers 633339904 bytes
Redo Buffers 3952640 bytes
Database mounted.
CONVERT PHYSICAL STANDBY TO SNAPSHOT STANDBY
SQL> alter database convert to snapshot standby;
Database altered.
SQL> select status, instance_name, database_role,open_mode from v$database, v$instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ——————–
MOUNTED GPROD SNAPSHOT STANDBY MOUNTED
SQL>
CREATE A USER IN SNAPSHOT STANDBY DATABASE AND CREATE A TABLE INSIDE THE USER
SQL> create user u1 identified by u1 quota unlimited on users;
User created.
SQL> grant connect, resources to u1;
grant connect, resources to u1
*
ERROR at line 1:
ORA-01919: role ‘RESOURCES’ does not exist
SQL> grant connect, resource to u1;
Grant succeeded.
SQL> conn u1/u1
Connected.
SQL> create table test(id number, name varchar2(80));
Table created.
SQL> insert into test values(1, ‘a’);
1 row created
SQL> select * from test;
ID
———-
NAME
——————————————————————————–
1
a
CONVERT SNAPSHOT STANDBT TO PHYSICAL STANDBY
SQL> alter database convert to physical standby;
Database altered.
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance
2 ;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ——————–
MOUNTED GPROD PHYSICAL STANDBY MOUNTED
CHECK WHEATHER THE CREATED USER IS AVAILABLE OR NOT
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
———— —————- —————- ——————–
OPEN GPROD PHYSICAL STANDBY READ ONLY
SQL> select * from u1.test;
select * from u1.test
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
THE USER IS AVAILABLE ONLY IN THE SNAPSHOT STANDBY DATABASE
Posted by :Sakthi sethu perumal S
