Convert Physical Standby Into Logical Standby

 

Transform a physical standby database into a logical standby seamlessly.

Primary Database Changes

Below is the current configuration setup

on Primary

========

dgmgrl sys@test

DGMGRL> show configuration

 

Configuration - my_dgb

 

  Protection Mode: MaxPerformance

  Members:

  test     - Primary database

    test_s - (*) Physical standby database

 

Fast-Start Failover: DISABLED----->>if it is not disable disable it(dgmgrl> disbale fast_start_failover) since we dont need it

 

Configuration Status:

SUCCESS   (status updated 59 seconds ago)

issue with L.stb:-----

1. ---not all the objects are supported by L.stb
2. ---LOB column
3. ---BLOB and CLOB not supported
4. ---Internal Schema will not be supported by L.stb
5. ---DBA will spend 80% of his/her time to manage L.stb big issue.
6. ---you should never recommend L.STB replacement of Active Data Guard if        you can Afford Active Data Guard.
7. ---all your Primary record must be uniques and must have PK else not                   supported by L.stb.

In the primary database, retrieve the list of objects that are not supported by logical Standby.

set pagesize 25;

column owner format a5;

select distinct owner, table_name from dba_logstdby_unsupported order by owner,table_name;

no record because it is blank database and fresh database.

Check the reason behind those unsupported objects from the above query you will get owner and table_name so mention here.

select column_name,data_type from dba_logstdby_unsupported where owner='OE' and table_name= 'CUSTOMERS';

Identify the unsupported schemas on primary( ex: SYS etc..) not supported.

Col owner format a20;

select owner from dba_logstdby_skip where statement_opt = 'INTERNAL SCHEMA';

Make sure each table row in primary database can be uniquely identified. To fix the table, make sure you add a primary key.

SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE;

Redo Apply needs to be stopped on the Physical Standby now.

on Standby

=========

DGMGRL> edit database test_s set state = apply-off;

on Primary

========

Build Log Miner directory on primary

SQL> exec dbms_logstdby.build

Open Physical Standby

Open the physical standby database

On Standby Server

=================

SQL> alter database recover to logical standby test_s;

SQL> shutdown immediate

SQL> startup mount

SQL> alter database open resetlogs;

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

Data Guard Broker Changes

Now we need to remove existing physical standby database from the configuration and add the new logical standby database

Before when we configured Data Gaurd Broker we register Primary and Physical Standby in DGBR. now we have demolish Physical Standby and tell to Data Guard Broker that now we have Logical Standby and Physical standby is not available so that is why we need to remove Physical Standby from Data Guard Broker configuration.

on Primary

========

dgmgrl sys@test

DGMGRL> remove database test_s;

if you see error

do the bellow:

dgmgrl> edit database test set property FastStartFailoverTarget='';

DGMGRL> remove database test_s;

DGMGRL>show configuration;

Start Logical Apply Service on standby server

On Standby

==========

SQL> alter database start logical standby apply immediate;

Now add the new logical standby to the broker configuration

on Primary

========

DGMGRL> add database test_s as connect identifier is test_s;

DGMGRL>show configuration;

DGMGRL> enable database test_s

Let’s check the configuration once again

DGMGRL> show configuration;

 

Configuration - my_dg

 

  Protection Mode: MaxPerformance

  Members:

  test     - Primary database

    test_s - Logical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

SUCCESS   (status updated 5 seconds ago)

Verify logical standby

on standby

=======

SQL> select process,status,sequence# from v$managed_standby;

You cannot see the LSP process under v$managed_standby view but you can check the process at OS level

On standby:

===========

ps -ef|grep lsp

Query to check if logical standby is applying the transactions

SQL> SELECT name, value FROM v$logstdby_stats;

Two simple ways to check logical standby is working fine

Create Object in Logical Standby

• Create user and a table (not with sys schema) in primary  to see if we can use our logical Standby in a normal way. and switch logfile. Wait and see if table reflects on standby
• Monitor the logical standby alert log

on Primary

========

Create user entopdba identified by entopdba123;

grant connect, resource, create table to entopdba;

grant unlimited tablespace to entopdba;

so this user also will be created on L.stb

connect entopdba/entopdba123;

create table logical_table(lno number);

insert into logical_table values(100);

switch some log file

alter system switch logfile;

/

/

on standby

========

sqlplus 

username:entopdba

password:entopdba123

select * from logical_table;

so it means user replicated to logical standby and I was able to conenct.

now can i create my own table on logical Standby and do my testing without touching primary object?    yes

on standby

=========

sqlplus  / as sysdba

SQL>create user lguser identified by lguser123;

SQL>grant connect,resource,create session  to lguser;

SQL>grant unlimited tablespace to lguser;

SQL>create table l_table(lno number);

lets test if we were able to create user,create table on local object of L.stb.

SQL>conn lguser/lguser123

SQL>create table my_test(mtno number);

error Data Guard is enabled.

exit

sqlplus / as sysdba

select name, open_mode, db_unique_name, database_role, guard_status from v$database;

Guard_status

---------------

GS=All      ----------->>> means all objects are guarded and secure inside the database and you can not edit them and you can not create object.

if you set 

GS=standard 

then only Objects on primary side are Guarded and secure but the local object can be modified.

also we have GS=NONE this is never used

SQL>alter database guard standby;

SQL>conn lguser/lguser123

SQL>create table my_test(mtno number);

insert into my_test values(222);

select * from my_test;

it works 

now lets test if we can modify primary object on Logical standby:

on standby

========

connect to user we previous created on primary 

sqlplus 

username: entopdba

password:   entopdba123

we create a table logical_table

SQL>select * from logical_table;

this table is now guarded because it was created on primary so it is primary object on Logical Standby

lets modify it if we can since we set guard to standby;

SQL>insert into logical_table values(5555);

error database Guard is enabled