STEPS TO CONVERT PHYSICAL STANDBY IN TO LOGICAL STANDY DATABASE

1 create a physical standby database
2 stop redoapply on physical standby database
3 prepare primary database to support a logical standby database
4 Build a logminer dictionary in the redo data
5 Transition to a logical standby database
6 Open the logical standby database
varify that the logical standby database is performing properly

1 create a physical standby database
Physical standby is the base for the logical standby database
Ensure that both primary and standby database are in sync

SQL>  select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            255


SQL>  select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            255

2 stop redoapply on physical standby database


SQL>alter database recover managed standby database cancel;

3 prepare primary database to support a logical standby database
 to have smooth role reversal modify initilization parameter of primary
set undo retention initialization parameter to 3600

SQL>EXECUTE DBMS_LOGSTDBY.BUILD;
check alert log

 ALTER DATABASE RECOVER TO LOGICAL STANDBY;

shut down database and bring back to mount stage
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;



build a logminer dictionary into redo data so that logminer componet of SQL apply

show parameter log_archive_dest_1
alter system set log_archiv_dest_1='location=location=/primary/arch valid_for=(standby_logfiles,standby_roles)db _unique_name=standby' scope=both


mkdir -p /pri1mary/arch1

alter system set log_archiv_dest_3='location='location=/primary/arch1 valid_for
=(online_logfiles,standby_role)db _unique_name=standby' scope=both

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;


do a log switch on primary
and see that archive log files are generated on primary





CHECKING FOR UNSPOORTED OBJECTS
Query DBA_LOGSTDBY_UNSUPPORTED_TABLE  on the primary database for unsupported tables;
SQL>  SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED;
CHECKING FOR TABLES WITH UNSUPPORTED DATA TYPES
Query DBA_LOGSTDBY_UNSUPPORTED_TABLE  on the primary database for unsupported tables;
SQL>select table_name, column_name, attributes,data_type from dba_logstdby_unsupported ;

this view does not show any tables from sys schema because changes from sys schema object are not applied to the logical standby database
in addition logical standby database , In addition this view does not show table with compression


find tables without unique logical identifier in the primary database
Query DBA_LOGSTDBY_NOT_UNIQUE on the primary database to find tables without a unqiue identifier

SQL>select owner, table_name from dba_logstdby_not_unique where (owner,table_name) NOT IN (select distinct owner, table_name from dba_logstdby_unsupported)

Logical standby are used when you want to create additional objects on standby database


CONVERTING PHYSICAL STANDBY TO LOGICAL STANDBY

April 30, 2014 · by anargodjaev · in Oracle İntroduction · Leave a comment
      1 Vote

„ Check Protection Mode is MaxPerformance or change it to MaxPerformance
„ Check LogXptMode is ‘ASYNC’ or change LogXptMode to ‘ASYNC’
„ Disable configuration and exit from dgmgrl
 Archive all online redologfile

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
„ Following commands will be executed only on Secondary Database phyocm

SQL> SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
„ Open another terminal and check alert log file

[oracle@ocm bdump]$ tail -f alert_phyocm.log
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY phyocm;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
SQL> ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;


805-422-8671