Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 21 May 2013

Converting Physical Standby database to the Logical standby database

Consider we already created physical standby database and we are now going to convert it into logical standby database using RMAN. For Creation of Physical standby check the other post:
Prerequisite Conditions for Creating a Logical Standby Database
While logical standby databases have some excellent benefits such as: effective system resource, Primary database work load reduction, Increase data protection etc., it has some drawbacks, including restrictions on unsupported objects and datatypes, limited support for DDL and DCL commands.
Determine Compatibility issue of data types:
As we know, Logical standby databases do not support some of the data types such as: BFILE, ROWID, UROWID, User-defined types, Varrays, XML type, Encrypted columns and Spatial, Image. Thus before setting up a logical standby database, ensure on the primary database, the logical standby database can maintain the data types and tables available in your primary database.
SQL> select distinct owner, table_name from dba_logstdby_unsupported order by owner, table_name;
SQL> select column_name,data_type from dba_logstdby_unsupported
where owner='HRMS' and table_name = 'PAY_PAYMENT_MASTER';
Note: If the primary database contains unsupported tables, log apply services automatically exclude the tables when applying redo logs to the logical standby database.
Ensure Table Rows in the Primary Database can be uniquely identified:
The physical organization in a logical standby database is different from that of the primary database, even though the logical standby database is created from a backup copy of the primary database. Thus, ROWIDs contained in the redo records generated by the primary database cannot be used to identify the corresponding row in the logical standby database. The below query will display a list of table that SQL Apply may not be able to uniquely identify
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) and bad_column = 'Y';
If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table else create the primary key.
SQL> Alter table PAY_PAYMENT_MASTER  add primary key (id, name)  rely disable;
When you specify the RELY constraint, the system will assume that rows are unique. If such uniqueness is not present, then SQL Apply will not correctly maintain the table. To improve the performance of SQL Apply, add an index to the columns that uniquely identify the row on the logical standby database.
Step by Step Creating a Logical Standby Database:
Now stop redo apply on your physical standby database (already created). Thus any changes would not be applied to the physical standby (until we complete the creation of the LogMiner dictionary)
SQL> Alter Database Recover Managed Standby Database Cancel;
Adjust Initialization Parameters in Primary database:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=E:\oracle\Sadhan\stdby_arch VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=Sadhan';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=E:\oracle\sadhan\Archive VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sadhan' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=sadstby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sadstby' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
Note: SET the undo_retention is high to halt the possibility of Ora-01555: snapshot too old during LogMiner dictionary creation in primary.
Build a Log Miner Dictionary in Primary:
A LogMiner dictionary is required so that the logical standby database’s Log Apply Services can make sense of incoming change vectors and translate them to appropriate DML, DDL and DCL statements.
SQL>EXECUTE DBMS_LOGSTDBY.BUILD;
Convert Physical Standby DB to Logical Standby DB:
At this point, the physical standby database is ready to be converted to a logical standby database. Issue the below command on secondary database.
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY “db_name”;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
For db_name, specify a database name that is different from the primary database to identify the new logical standby database. If your database is running with spfile, then the database will be updated with new logical standby database name. If database is not running with spfile, then the database issues a message reminding you to set the DB_NAME parameter after shutting down the database.
The redo logs will be applied until the LogMiner dictionary is found in the log files otherwise this command will not performed successfully. You can cancel the SQL statement by issuing a statement from another SQL session:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Close and Mount the Logical Standby Database
SQL> shutdown immediate;
SQL> startup mount;
Create a new Password file (if not)
orapwd file=orapwdsadhan password=oracle entries=20
Adjust Initialization Parameters for the Secondary (logical) Database:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=E:\oracle\Sadhan\stdby_arch VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=sadstby';
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=E:\oracle\sadhan\Archive VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sadstby' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=sadhan ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sadhan' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=E:\oracle\sadhan\ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=sadstby' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
Activate Logical Standby database and start SQL apply in Secondary DB
SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE OPEN RESETLOGS;
Issue the following statement to begin applying redoes data to the logical standby database:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Note: Once you have successfully invoked the ALTER DATABASE START LOGICAL STANDBY APPLY statement, you should take a full backup of the logical standby database, because the backups taken from the primary database cannot be used to restore the logical standby database.
Verify Logical Standby Database Configuration and Performance:
You can use the following SQL examples; alternatively, you could simply monitor the logical standby database’s alert log to insure that redo is indeed being transmitted to and applied against that database.
SQL> CONN hrms/hrms@sadhan
CONNECTED.
SQL> SELECT COUNT(*) FROM PAY_PAYMENT_MASTER;
  COUNT(*)
----------
     10432
SQL> CONN HRMS/HRMS@SADSTBY
CONNECTED.
SQL> SELECT COUNT(*) FROM PAY_PAYMENT_MASTER;
  COUNT(*)
----------
     10432
SQL> CONN HRMS/HRMS@SADHAN
CONNECTED.
SQL> DELETE FROM PAY_PAYMENT_MASTER;
10432 ROWS DELETED.
SQL> SELECT COUNT(*) FROM PAY_PAYMENT_MASTER;
  COUNT(*)
----------
         0
SQL> CONN HRMS/HRMS@SADSTBY
CONNECTED.
SQL> SELECT COUNT(*) FROM PAY_PAYMENT_MASTER;
  COUNT(*)
----------
         0
Monitoring Log file Archival Information
On Primary Server:
SQL> SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT';
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
On Standby Server:
SELECT FILE_NAME FROM DBA_LOGSTDBY_LOG WHERE THREAD#=1 AND SEQUENCE#= (Sequence# from previous query);
Otherwise you can use below query in which APPLIED_SCN and NEWEST_SCN should match
SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;

0 comments:

Post a Comment