1.PURPOSE AND SCOPE
At another article of mine ,I explained installation of Physical standby and this article we are going to convert physical standby database to logical standby.
2.PRACTICE
2.1. At primary database we need to execute a query to see which tables are not supported by logical standby
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> Select * from dba_logstdby_unsupported; OWNER TABLE_NAME COLUMN_NAME DATA_TYPE --------------------- -------------------------- ---------------- --------------- OE CATEGORIES_TAB CATEGORY_ID Object Table NUMBER OE CATEGORIES_TAB PARENT_CATEGORY_ID Object Table OWNER TABLE_NAME COLUMN_NAME DATA_TYPE --------------------- -------------------------- ---------------- --------------- WMSYS WM$UDTRIG_INFO TRIG_CODE LONG WMSYS WM$VERSIONED_TABLES UNDO_CODE WM$ED_UNDO_CODE_TABLE_TYPE |
2.2. And also we need to find tables which hasnt got primary key or non-null unique indexes.We can execute query below for it.
N = It represent that it receive enough coloumn information for that table.
Y = It represent that LONG, CLOB or BLOB datatype at the table.We can bypass this problem using supplemental logging.
|
1 2 3 4 5 6 7 8 9 10 |
SQL> SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED); OWNER TABLE_NAME B ------------------------------ ------------------------------ - SCOTT BONUS N SCOTT SALGRADE N SH SALES N SH COSTS N SH SUPPLEMENTARY_DEMOGRAPHICS N |
2.3. Lets check supplemental logging.
|
1 2 3 4 5 |
SQL> select supplemental_log_data_pk,supplemental_log_data_ui from v$database; SUP SUP --- --- NO NO |
2.4. If it is closed we need to open it for tables which hasnt got primary key or non-null unique indexes .
|
1 2 3 4 5 6 7 |
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY,UNIQUE INDEX) COLUMNS; Database altered. SQL> ALTER SYSTEM SWITCH LOGFILE; System altered. |
2.5. We can also open parallelism. // Optional.
|
1 |
SQL> ALTER SYSTEM SET LOG_PARALLELISM=1 SCOPE=BOTH; |
2.6. We need to stop physcal standby.
|
1 2 3 |
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered. |
2.7. At primary database log_archive_dest_1 parameter set to All_LOGFILES and ALL_ROLES ,we need to change this to ONLINE_LOGFILES and ALL_ROLES .And we need to set Log_archive_dest_3 and enable it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
SQL> show parameter log NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string LOCATION=/u01/app/oracle/datab ases/prim/redo/ VALID_FOR=(ALL_LOGFILES,ALL_RO LES) DB_UNIQUE_NAME=bugra log_archive_dest_2 string service=stdby lgwr async noaff irm valid_for=(online_logfiles ,primary_role) db_unique_name= stdby log_archive_dest_3 string log_archive_dest_state_1 string ENABLE log_archive_dest_state_2 string enable log_archive_dest_state_3 string enable remote_login_passwordfile string EXCLUSIVE sec_case_sensitive_logon boolean TRUE sec_max_failed_login_attempts integer 10 SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/databases/prim/redo/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bugra' scope=both; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/databases/prim/redo2/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=bugra' scope=both; System altered. SQL> alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both; System altered. |
2.8. We need to create logminer for Redo log dictionary so with this we can use ONLINE_LOGFILE parameter which we set it to LOG_ARCHIVE_DEST_1.
|
1 2 3 |
SQL> EXECUTE DBMS_LOGSTDBY.BUILD; PL/SQL procedure successfully completed. |
2.9. So now redo logs are prepared for Logical standby. Lets staPhysical standby makirt the recover process at logical standby.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY stdby; Standby Alertloglari Media Recovery Complete (stdby) tkcrrxms: Killing 4 processes (all RFS) RESETLOGS after incomplete recovery UNTIL CHANGE 904454 Resetting resetlogs activation ID 772557144 (0x2e0c4958) Online log /u01/app/oracle/product/11.1.0/oradata/stdby/redogroup_1.262.720476831: Thread 1 Group 1 was previously cleared Online log /u01/app/oracle/product/11.1.0/oradata/stdby/redogroup_1.263.720476839: Thread 1 Group 1 was previously cleared Online log /u01/app/oracle/product/11.1.0/oradata/stdby/redogroup_2.264.720476843: Thread 1 Group 2 was previously cleared Online log /u01/app/oracle/product/11.1.0/oradata/stdby/redogroup_2.265.720476851: Thread 1 Group 2 was previously cleared Standby became primary SCN: 904452 Sat Sep 18 02:28:01 2010 Setting recovery target incarnation to 3 Converting standby mount to primary mount. ACTIVATE STANDBY: Complete - Database mounted as primary (stdby) *** DBNEWID utility started *** DBID will be changed from 772585308 to new DBID of 1331092977 for database BUGRA DBNAME will be changed from BUGRA to new DBNAME of STDBY Starting datafile conversion Setting recovery target incarnation to 1 Datafile conversion complete Failed to find temporary file: /u01/app/oracle/product/11.1.0/oradata/stdby/data/temp.268.720476923 Database name changed to STDBY. Modify parameter file and generate a new password file before restarting. Database ID for database STDBY changed to 1331092977. All previous backups and archived redo logs for this database are unusable. Database has been shutdown, open with RESETLOGS option. Succesfully changed database name and ID. *** DBNEWID utility finished succesfully *** Completed: ALTER DATABASE RECOVER TO LOGICAL STANDBY stdby Sat Sep 18 02:28:14 2010 destination database instance is 'started' not 'mounted' |
2.10. Our logical standby opened with nomount option and now we need to change log_archive_dest of 1,2 and 3.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
SQL> Show parameter log NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string location=/u01/app/oracle/produ ct/11.1.0/oradata/stdby/arc1/ valid_for=(all_logfiles,all_ro les) db_unique_name=stdby log_archive_dest_2 string service=bugra lgwr async noaff irm valid_for=(online_logfiles ,primary_role) db_unique_name= bugra log_archive_dest_3 string log_archive_dest_state_1 string enable log_archive_dest_state_2 string ENABLE log_archive_dest_state_3 string enable SQL> alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/oracle/product/11.1.0/oradata/stdby/arc1/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'scope=both; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=bugra ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bugra' scope=both; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/app/oracle/product/11.1.0/oradata/stdby/arc2/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=stdby' scope=both; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both; System altered. |
2.11. We are closing database and open it at mount stage and after of it we need to open database with resetlogs option.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount; ORA-32004: obsolete and/or deprecated parameter(s) specified ORACLE instance started. Total System Global Area 146472960 bytes Fixed Size 1298472 bytes Variable Size 92278744 bytes Database Buffers 50331648 bytes Redo Buffers 2564096 bytes Database mounted. SQL> ALTER DATABASE OPEN RESETLOGS; Database altered. SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; Database altered. |
2.12. Lets check logical standby Alert log.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Completed: alter database open ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE ALTER DATABASE START LOGICAL STANDBY APPLY (stdby) with optional part IMMEDIATE Attempt to start background Logical Standby process Sat Sep 18 03:32:48 2010 LSP0 started with pid=24, OS id=32724 Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE LOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 201 LOGMINER: Memory Size = 30M, Checkpoint interval = 150M LOGMINER: SpillScn 0, ResetLogScn 522753 Sat Sep 18 03:33:12 2010 LOGMINER: session#=1, reader MS00 pid=28 OS id=32739 sid=82 started Sat Sep 18 03:33:14 2010 LOGMINER: session#=1, builder MS01 pid=29 OS id=32741 sid=79 started Sat Sep 18 03:33:14 2010 LOGMINER: session#=1, preparer MS02 pid=30 OS id=32743 sid=78 started LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 41, /u01/app/oracle/product/11.1.0/oradata/stdby/arc1/1_41_720476831.dbf LOGMINER: Turning ON Log Auto Delete |
2.13. Also we can change V$database .
|
1 2 3 4 5 6 |
SQL> select log_mode,open_mode , database_role from v$database; LOG_MODE OPEN_MODE DATABASE_ROLE ------------ ---------- ---------------- ARCHIVELOG READ WRITE LOGICAL STANDBY |
Leave a Reply