Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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.

  

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

7 Steps to configure BDR replication in postgresql

Postgresql Database Tutorial android app