Step by step Converting Physical Standby to Logical – Oracle Dataguard 11g AND prerequest step by step
PREREQEST:-
* UNDO TABLESPACE
* NON_TABLESPACE FOR CONVERT ARCH(CHANGE VECTOR) TO SQL STATEMENT USING LOGMINOR
1)SQL> select TABLESPACE_NAME,STATUS,FORCE_LOGGING,RETENTION from dba_tablespaces; --> PRIMARY DB
TABLESPACE_NAME STATUS FOR RETENTION
------------------------------ --------- --- -----------
SYSTEM ONLINE NO NOT APPLY
SYSAUX ONLINE NO NOT APPLY
UNDO01 ONLINE NO GUARANTEE
TEMP01 ONLINE NO NOT APPLY
TBS ONLINE NO NOT APPLY
TB7 ONLINE NO NOT APPLY
TEMP02 ONLINE NO NOT APPLY
2)SQL> exec dbms_logmnr_d.set_tablespace('TBS'); -->PRIM DB
PL/SQL procedure successfully completed.
SQL> exec dbms_logstdby.build;
PL/SQL procedure successfully completed.
SQL> alter database add supplemental log data(all) columns; -->TO ENABLE ALL COLUMN LOGGING AT THE DATABASE LEVEL
Database altered.
SQL> alter database add supplemental log data(primary key,unique) columns; -->TO ENABLE PRIMARY AND UNIQUE COLUMN LOGGING AT THE DATABASE LEVEL
Database altered.
SQL> alter database add supplemental log data(foreign key) columns -->TO ENABLE FOREIGN KEY COLUMN LOGGING AT THE DATABASE LEVEL
Database altered
3)SQL> alter database recover managed standby database cancel; --> STANDBY DB
Database altered.
SQL> alter database close;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database recover to logical standby u2(u1 primary unique name( documents));
Database altered.
note : again recreate the password file in standby database.
cd $ORACLE_HOME/dbs
export ORACLE_SID=u2
orapwd file=orapwstd password=sys
4)SQL>shut immediate;
SQL>startup;
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 134220188 bytes
Database Buffers 276824064 bytes
Redo Buffers 6103040 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> alter database start logical standby apply immediate;
Database altered.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
LOGICAL STANDBY
5) prim db
SQL> create table prakash(id int);
Table created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
6)standby logical db
Note : make sure that the changes on the primary is reflected in secondary within 15 mins.
* UNDO TABLESPACE
* NON_TABLESPACE FOR CONVERT ARCH(CHANGE VECTOR) TO SQL STATEMENT USING LOGMINOR
1)SQL> select TABLESPACE_NAME,STATUS,FORCE_LOGGING,RETENTION from dba_tablespaces; --> PRIMARY DB
TABLESPACE_NAME STATUS FOR RETENTION
------------------------------ --------- --- -----------
SYSTEM ONLINE NO NOT APPLY
SYSAUX ONLINE NO NOT APPLY
UNDO01 ONLINE NO GUARANTEE
TEMP01 ONLINE NO NOT APPLY
TBS ONLINE NO NOT APPLY
TB7 ONLINE NO NOT APPLY
TEMP02 ONLINE NO NOT APPLY
2)SQL> exec dbms_logmnr_d.set_tablespace('TBS'); -->PRIM DB
PL/SQL procedure successfully completed.
SQL> exec dbms_logstdby.build;
PL/SQL procedure successfully completed.
SQL> alter database add supplemental log data(all) columns; -->TO ENABLE ALL COLUMN LOGGING AT THE DATABASE LEVEL
Database altered.
SQL> alter database add supplemental log data(primary key,unique) columns; -->TO ENABLE PRIMARY AND UNIQUE COLUMN LOGGING AT THE DATABASE LEVEL
Database altered.
SQL> alter database add supplemental log data(foreign key) columns -->TO ENABLE FOREIGN KEY COLUMN LOGGING AT THE DATABASE LEVEL
Database altered
3)SQL> alter database recover managed standby database cancel; --> STANDBY DB
Database altered.
SQL> alter database close;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> alter database recover to logical standby u2(u1 primary unique name( documents));
Database altered.
note : again recreate the password file in standby database.
cd $ORACLE_HOME/dbs
export ORACLE_SID=u2
orapwd file=orapwstd password=sys
4)SQL>shut immediate;
SQL>startup;
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 134220188 bytes
Database Buffers 276824064 bytes
Redo Buffers 6103040 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> alter database start logical standby apply immediate;
Database altered.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
LOGICAL STANDBY
5) prim db
SQL> create table prakash(id int);
Table created.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
6)standby logical db
Note : make sure that the changes on the primary is reflected in secondary within 15 mins.
Comments
Post a Comment