/* || Oracle 11g DataGuard Listing 8 || || Illustrates how to create and manage a Logical Standby database within the || of an Oracle Database 11g Data Guard environment. || || Author: Jim Czuprynski || || Usage Notes: || These examples are provided to demonstrate various Oracle 11gR1 Data Guard || features, and they should be carefully proofread before being executed || against any existing Oracle database to avoid potential damage! */ /* || Listing 8.1: || Creating and populating new objects for demonstration of Logical Standby Database features */ ----- -- Create TYPE for storing phone numbers in "international" format ----- DROP TYPE oe.i18l_phone_nbr; CREATE TYPE oe.i18l_phone_nbr AS OBJECT ( country NUMBER(3) ,area_code NUMBER(3) ,prefix NUMBER(3) ,suffix NUMBER(4) ,extn NUMBER(6) ); / ----- -- Create new table that stores home, office, and FAX phone numbers -- for a customer entry ----- DROP TABLE oe.cust_phone_nbrs PURGE; CREATE TABLE oe.cust_phone_nbrs ( customer_id NUMBER(5) ,hme_phone_nbr OE.I18L_PHONE_NBR ,ofc_phone_nbr OE.I18L_PHONE_NBR ,fax_phone_nbr OE.I18L_PHONE_NBR ) TABLESPACE example; ----- -- Create new table from existing customer data that permits application logic -- to drive a primary key value creation by specifying RELY DISABLE on the -- column's primary key constraint ----- DROP TABLE oe.subcustomers PURGE; CREATE TABLE oe.subcustomers AS SELECT customer_id ,cust_first_name first_name ,cust_last_name last_name ,date_of_birth dob ,gender FROM oe.customers WHERE 1=0; ALTER TABLE oe.subcustomers ADD CONSTRAINT subcustomers_pk PRIMARY KEY (customer_id) RELY DISABLE; ----- -- Populate OE.CUST_PHONE_NBRS table ----- INSERT INTO oe.cust_phone_nbrs VALUES (25259 ,OE.I18L_PHONE_NBR(1,312,555,1212,35467) ,OE.I18L_PHONE_NBR(1,847,555,1456,NULL) ,OE.I18L_PHONE_NBR(1,847,555,1459,NULL) ); COMMIT; SELECT customer_id ,hme_phone_nbr ,ofc_phone_nbr FROM oe.cust_phone_nbrs ; ----- -- Populate OE.SUBCUSTOMERS table ----- INSERT INTO oe.subcustomers SELECT customer_id ,cust_first_name first_name ,cust_last_name last_name ,date_of_birth dob ,gender FROM oe.customers WHERE customer_id BETWEEN 100 AND 500 ; COMMIT; /* || Listing 8.2: || Finding unsupported objects for Logical Standby Databases */ ----- -- View: DBA_LOGSTDBY_UNSUPPORTED_TABLE -- Purpose: Lists tables unsupported for Logical Standby ----- TTITLE 'Tables Unsupported for Logical Standby|(from DBA_LOGSTDBY_UNSUPPORTED_TABLE)' COL owner FORMAT A20 HEADING 'Schema Owner' COL table_name FORMAT A30 HEADING 'Table Name' SELECT owner ,table_name FROM dba_logstdby_unsupported_table ORDER BY owner, table_name ; TTITLE OFF Tables Unsupported for Logical Standby (from DBA_LOGSTDBY_UNSUPPORTED_TABLE) Schema Owner Table Name -------------------- ------------------------------ IX AQ$_ORDERS_QUEUETABLE_G IX AQ$_ORDERS_QUEUETABLE_H IX AQ$_ORDERS_QUEUETABLE_I IX AQ$_ORDERS_QUEUETABLE_S IX AQ$_ORDERS_QUEUETABLE_T IX AQ$_STREAMS_QUEUE_TABLE_C IX AQ$_STREAMS_QUEUE_TABLE_G IX AQ$_STREAMS_QUEUE_TABLE_H IX AQ$_STREAMS_QUEUE_TABLE_I IX AQ$_STREAMS_QUEUE_TABLE_S IX AQ$_STREAMS_QUEUE_TABLE_T IX ORDERS_QUEUETABLE IX STREAMS_QUEUE_TABLE OE CATEGORIES_TAB OE CUSTOMERS OE CUST_PHONE_NBRS OE PURCHASEORDER OE WAREHOUSES PM ONLINE_MEDIA PM PRINT_MEDIA SH DIMENSION_EXCEPTIONS 21 rows selected. ----- -- View: DBA_LOGSTDBY_UNSUPPORTED -- Purpose: Lists tables unsupported for Logical Standby because at least one column -- has an unsupported datatype ----- TTITLE 'Tables Unsupported for Logical Standby|Because of Columns With Unsupported Datatypes|(from DBA_LOGSTDBY_UNSUPPORTED)' COL owner FORMAT A12 HEADING 'Schema Owner' COL table_name FORMAT A24 HEADING 'Table Name' COL column_name FORMAT A30 HEADING 'Column Name' COL data_type FORMAT A20 HEADING 'Data Type' WRAP COL attributes FORMAT A30 HEADING 'Attributes' WRAP SELECT owner ,table_name ,column_name ,data_type ,attributes FROM dba_logstdby_unsupported ORDER BY owner, table_name, column_name ; TTITLE OFF Tables Unsupported for Logical Standby Because of Columns With Unsupported Datatypes (from DBA_LOGSTDBY_UNSUPPORTED) Schema Owner Table Name Column Name Data Type Attributes ------------ ------------------------ ------------------------------ -------------------- ------------------------------ IX AQ$_ORDERS_QUEUETABLE_G ADDRESS# NUMBER AQ queue table IX AQ$_ORDERS_QUEUETABLE_G DBS_SIGN OBJECT AQ queue table IX AQ$_ORDERS_QUEUETABLE_G MSGID RAW AQ queue table ..... << Edited for sake of brevity >> ..... OE CATEGORIES_TAB CATEGORY_DESCRIPTION VARCHAR2 Object Table OE CATEGORIES_TAB CATEGORY_ID NUMBER Object Table OE CATEGORIES_TAB CATEGORY_NAME VARCHAR2 Object Table OE CATEGORIES_TAB PARENT_CATEGORY_ID NUMBER Object Table OE CUSTOMERS CUST_ADDRESS OBJECT OE CUSTOMERS CUST_GEO_LOCATION OBJECT OE CUSTOMERS PHONE_NUMBERS VARRAYOE CUST_PHONE_NBRS FAX_PHONE_NBR OBJECT OE CUST_PHONE_NBRS HME_PHONE_NBR OBJECT OE CUST_PHONE_NBRS OFC_PHONE_NBR OBJECTOE PURCHASEORDER SYS_NC_ROWINFO$ OPAQUE Object Table OE WAREHOUSES WH_GEO_LOCATION OBJECT PM ONLINE_MEDIA PRODUCT_AUDIO OBJECT PM ONLINE_MEDIA PRODUCT_PHOTO OBJECT PM ONLINE_MEDIA PRODUCT_PHOTO_SIGNATURE OBJECT PM ONLINE_MEDIA PRODUCT_TESTIMONIALS OBJECT PM ONLINE_MEDIA PRODUCT_THUMBNAIL OBJECT PM ONLINE_MEDIA PRODUCT_VIDEO OBJECT PM PRINT_MEDIA AD_GRAPHIC BFILE PM PRINT_MEDIA AD_HEADER OBJECT PM PRINT_MEDIA AD_TEXTDOCS_NTAB NESTED TABLE Unsupported Virtual Column SH DIMENSION_EXCEPTIONS BAD_ROWID ROWID 175 rows selected. ----- -- View: DBA_LOGSTDBY_NOT_UNIQUE -- Purpose: Lists tables unsupported for Logical Standby because they don't have -- unique row identifiers (e.g. primary / unique key) ----- TTITLE 'Tables Unsupported for Logical Standby|Because of Missing Unique Row Identifiers|(from DBA_LOGSTDBY_NOT_UNIQUE)' COL owner FORMAT A20 HEADING 'Schema Owner' COL table_name FORMAT A30 HEADING 'Table Name' COL bad_column FORMAT A04 HEADING 'Bad|Col' SELECT owner ,table_name ,bad_column FROM dba_logstdby_not_unique WHERE TABLE_NAME NOT IN (SELECT DISTINCT table_name FROM dba_logstdby_unsupported) ; TTITLE OFF Sat Dec 12 page 1 Tables Unsupported for Logical Standby Because of Missing Unique Row Identifiers (from DBA_LOGSTDBY_NOT_UNIQUE) Bad Schema Owner Table Name Col -------------------- ------------------------------ ---- SCOTT BONUS N SCOTT SALGRADE N SH SALES N SH COSTS N SH SUPPLEMENTARY_DEMOGRAPHICS N HR MINI_EMPS N 6 rows selected. /* || Listing 8.3: || Verifying establishment of SQL Apply for a new Logical Standby Database */ # Check if the archived redo logs were registered: SQL> SELECT sequence#, first_time, next_time, dict_begin, dict_end SQL> FROM dba_logstdby_log; # Force a log file switch at the primary database: SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; # Query DBA_LOGSTDBY_LOG to see if the logical standby received the new archived redo logs: SQL> SELECT sequence#, first_time, next_time, dict_begin, dict_end SQL> FROM dba_logstdby_log; # Query V$LOGSTDBY_STATS to see if the logical standby is applying the transactions: SQL> SELECT name, value SQL> FROM v$logstdby_stats SQL> WHERE name = 'coordinator state'; # Check ongoing progress of SQL Apply against the logical standby from $LOGSTDBY_PROGRESS: SQL> SELECT sid, serial#, spid, type, high_scn, applied_scn, latest_scn SQL> FROM v$logstdby_progress; /* || Listing 8.4: || Tracing the successful creation of a new Logical Standby Database */ >> Primary database alert log entries: . . . << Edited for sake of brevity >> . . . Fri Nov 27 17:10:00 2009 LNS: Standby redo logfile selected for thread 1 sequence 74 for destination LOG_ARCHIVE_DEST_2 Fri Nov 27 17:10:02 2009 ALTER SYSTEM SET log_archive_dest_3='' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH; ALTER SYSTEM SET log_archive_config='dg_config=(orcl_stdby1)' SCOPE=BOTH; Thread 1 advanced to log sequence 75 Current log# 3 seq# 75 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log LNS: Standby redo logfile selected for thread 1 sequence 75 for destination LOG_ARCHIVE_DEST_2 Thread 1 cannot allocate new log, sequence 76 Checkpoint not complete Current log# 3 seq# 75 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log Thread 1 advanced to log sequence 76 Current log# 1 seq# 76 mem# 0: /u01/app/oracle/oradata/orcl/redo01.log LNS: Standby redo logfile selected for thread 1 sequence 76 for destination LOG_ARCHIVE_DEST_2 Fri Nov 27 17:24:38 2009 Thread 1 advanced to log sequence 77 Current log# 2 seq# 77 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log Fri Nov 27 17:24:38 2009 LNS: Standby redo logfile selected for thread 1 sequence 77 for destination LOG_ARCHIVE_DEST_2 Fri Nov 27 17:28:06 2009 Thread 1 advanced to log sequence 78 Current log# 3 seq# 78 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log Fri Nov 27 17:28:07 2009 LNS: Standby redo logfile selected for thread 1 sequence 78 for destination LOG_ARCHIVE_DEST_2 Fri Nov 27 17:28:34 2009 ALTER SYSTEM SET log_archive_dest_1='LOCATION="/u01/app/oracle/flash_recovery_area/ORCL/", valid_for=(ALL_LOGFILES,ALL_ROLES)' SCOPE=BOTH; Fri Nov 27 17:28:46 2009 NSV2 started with pid=31, OS id=24422 Fri Nov 27 17:28:50 2009 ALTER SYSTEM SET log_archive_config='dg_config=(orcl_stdby1,lsby)' SCOPE=BOTH; Fri Nov 27 17:28:58 2009 ALTER SYSTEM SET log_archive_dest_2='service="orcl_stdby1"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL compression=DISABLE max_failure=0 max_connections=1 reopen=300 db_unique_name="orcl_stdby1" net_timeout=30 valid_for=(online_logfile,primary_role)' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_3='service="lsby"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL compression=DISABLE max_failure=0 max_connections=1 reopen=300 db_unique_name="lsby" net_timeout=30 valid_for=(online_logfile,primary_role)' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH; Fri Nov 27 17:28:59 2009 Thread 1 cannot allocate new log, sequence 79 Checkpoint not complete Current log# 3 seq# 78 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log Fri Nov 27 17:28:59 2009 LNS2 started with pid=38, OS id=24430 Thread 1 advanced to log sequence 79 Current log# 1 seq# 79 mem# 0: /u01/app/oracle/oradata/orcl/redo01.log ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_3 ****************************************************************** Fri Nov 27 17:29:02 2009 ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2 ****************************************************************** LNS: Standby redo logfile selected for thread 1 sequence 79 for destination LOG_ARCHIVE_DEST_3 LNS: Standby redo logfile selected for thread 1 sequence 79 for destination LOG_ARCHIVE_DEST_2 Fri Nov 27 17:29:06 2009 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid ..... << Edited for sake of brevity >> ..... Fri Nov 27 18:02:20 2009 ALTER SYSTEM SET log_archive_dest_1='LOCATION="/u01/app/oracle/flash_recovery_area/ORCL/", valid_for=(ONLINE_LOGFILES,ALL_ROLES)' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_3='SERVICE="lsby", LGWR ASYNC NOAFFIRM delay=0 OPTIONAL compression=DISABLE max_failure=0 max_connections=1 reopen=300 db_unique_name="lsby" net_timeout=30 valid_for=(standby_logfiles,standby_role)' SCOPE=BOTH; Fri Nov 27 18:02:34 2009 Fri Nov 27 18:02:34 2009Logminer Bld: Build started Fri Nov 27 18:02:35 2009 Thread 1 advanced to log sequence 82 Current log# 1 seq# 82 mem# 0: /u01/app/oracle/oradata/orcl/redo01.log Fri Nov 27 18:02:35 2009 Logminer Bld: Lockdown Complete. DB_TXN_SCN is 0 5131882 LockdownSCN is 5131882 Fri Nov 27 18:02:35 2009 LNS: Standby redo logfile selected for thread 1 sequence 82 for destination LOG_ARCHIVE_DEST_2 Fri Nov 27 18:02:45 2009 Thread 1 advanced to log sequence 83 Current log# 2 seq# 83 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log LNS: Standby redo logfile selected for thread 1 sequence 83 for destination LOG_ARCHIVE_DEST_2 Fri Nov 27 18:02:47 2009 Fri Nov 27 18:02:47 2009 Logminer Bld: DoneThread 1 cannot allocate new log, sequence 84 Checkpoint not complete Current log# 2 seq# 83 mem# 0: /u01/app/oracle/oradata/orcl/redo02.log Thread 1 advanced to log sequence 84 Current log# 3 seq# 84 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log Fri Nov 27 18:02:53 2009 LNS: Standby redo logfile selected for thread 1 sequence 84 for destination LOG_ARCHIVE_DEST_2 Fri Nov 27 18:05:24 2009 ALTER SYSTEM SET log_archive_dest_2='service="orcl_stdby1"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL compression=DISABLE max_failure=0 max_connections=1 reopen=300 db_unique_name="orcl_stdby1" net_timeout=30 valid_for=(online_logfile,primary_role)' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_3='service="lsby"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL compression=DISABLE max_failure=0 max_connections=1 reopen=300 db_unique_name="lsby" net_timeout=30 valid_for=(online_logfile,primary_role)' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH; Fri Nov 27 18:05:24 2009 Thread 1 cannot allocate new log, sequence 85 Checkpoint not complete Current log# 3 seq# 84 mem# 0: /u01/app/oracle/oradata/orcl/redo03.log Fri Nov 27 18:05:24 2009 ****************************************************************** LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_3 ****************************************************************** LNS: Archive log rejected (thread 1 sequence 84) at host 'lsby' ORA-16401: archivelog rejected by RFS ..... << Edited for sake of brevity >> ..... Fri Nov 27 18:18:29 2009 ALTER SYSTEM SET log_archive_dest_2='service="orcl_stdby1"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL compression=DISABLE max_failure=0 max_connections=1 reopen=300 db_unique_name="orcl_stdby1" net_timeout=30 valid_for=(online_logfile,primary_role)' SCOPE=BOTH;ALTER SYSTEM SET log_archive_dest_3='service="lsby"',' LGWR ASYNC NOAFFIRM delay=0 OPTIONAL compression=DISABLE max_failure=0 max_connections=1 reopen=300 db_unique_name="lsby" net_timeout=30 valid_for=(online_logfile,primary_role)' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH; ALTER SYSTEM SET log_archive_dest_state_3='ENABLE' SCOPE=BOTH;>> Logical standby database (LSBY) alert log entries: Fri Nov 27 17:13:21 2009 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 2 Autotune of undo retention is turned on. IMODE=BR ILAT =18 LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 11.1.0.6.0. Using parameter settings in server-side spfile /u01/app/oracle/product/11.1.0/db_1/dbs/spfilelsby.ora System parameters with non-default values: processes = 150 sga_target = 400M control_files = "/u01/app/oracle/oradata/lsby/control01.ctl" db_file_name_convert = "/u01/app/oracle/oradata/orcl/" db_file_name_convert = "/u01/app/oracle/oradata/lsby/" log_file_name_convert = "/u01/app/oracle/oradata/orcl/" log_file_name_convert = "/u01/app/oracle/oradata/lsby/" db_block_size = 8192 compatible = "11.1.0.0.0" log_archive_config = "nodg_config" log_archive_dest_1 = "location="/u01/app/oracle/flash_recovery_area/LSBY/"" log_archive_dest_1 = "valid_for=(ALL_LOGFILES,ALL_ROLES)" log_archive_dest_2 = "service=orcl_primary ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl_primary" log_archive_dest_3 = "SERVICE=lsby ASYNC DB_UNIQUE_NAME=lsby VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)" log_archive_dest_state_1 = "ENABLE" log_archive_dest_state_2 = "ENABLE" log_archive_dest_state_3 = "ENABLE" log_archive_max_processes= 4 log_archive_min_succeed_dest= 1 log_archive_trace = 0 log_archive_format = "log_%s_%t_%r.arc" fal_client = "lsby" fal_server = "orcl_primary" fal_server = "orcl_stdby1" archive_lag_target = 0 db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area" db_recovery_file_dest_size= 8G standby_file_management = "AUTO" undo_tablespace = "UNDOTBS1" sec_case_sensitive_logon = FALSE remote_login_passwordfile= "EXCLUSIVE" db_domain = "" dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)" audit_file_dest = "/u01/app/oracle/admin/orcl/adump" audit_trail = "DB" db_name = "orcl" db_unique_name = "lsby" open_cursors = 300 pga_aggregate_target = 150M dg_broker_start = TRUE dg_broker_config_file1 = "/u01/app/oracle/product/11.1.0/db_1/dbs/dr1orcl_primary.dat" dg_broker_config_file2 = "/u01/app/oracle/product/11.1.0/db_1/dbs/dr2orcl_primary.dat" diagnostic_dest = "/u01/app/oracle" Fri Nov 27 17:13:21 2009 PMON started with pid=2, OS id=6093 Fri Nov 27 17:13:21 2009 VKTM started with pid=3, OS id=6095 at elevated priority VKTM running at (20)ms precision Fri Nov 27 17:13:22 2009 ..... << Edited for sake of brevity >> ..... Physical Standby Database mounted. Lost write protection disabled Completed: ALTER DATABASE MOUNT Starting Data Guard Broker (DMON) Fri Nov 27 17:13:30 2009 INSV started with pid=24, OS id=6145 Fri Nov 27 17:14:39 2009 alter database recover to logical standby lsby Media Recovery Start: Managed Standby Recovery (lsby) Fast Parallel Media Recovery enabled Managed Standby Recovery not using Real Time Apply Media Recovery Waiting for thread 1 sequence 70 Fri Nov 27 17:23:31 2009 Recovery interrupted! Media Recovery user canceled with status 1013 ORA-16043 signalled during: alter database recover to logical standby lsby... Fri Nov 27 17:23:59 2009 alter database recover managed standby database using current logfile disconnect Attempt to start background Managed Standby Recovery process (lsby) Fri Nov 27 17:23:59 2009 MRP0 started with pid=25, OS id=6158 MRP0: Background Managed Standby Recovery process started (lsby) Fast Parallel Media Recovery enabled Managed Standby Recovery starting Real Time Apply parallel recovery started with 2 processes Waiting for all non-current ORLs to be archived... Completed: alter database recover managed standby database using current logfile disconnect Media Recovery Waiting for thread 1 sequence 70 Fri Nov 27 17:27:19 2009 Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/flash_recovery_area/LSBY/ Fri Nov 27 17:28:26 2009 db_recovery_file_dest_size of 8192 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Fri Nov 27 17:28:50 2009 NSV0 started with pid=28, OS id=6192 Fri Nov 27 17:28:55 2009 RSM0 started with pid=30, OS id=6200 ALTER SYSTEM SET log_archive_dest_1='location="/u01/app/oracle/flash_recovery_area/LSBY/"','valid_for=(ALL_LOGFILES,ALL_ROLES)' SCOPE=BOTH SID='lsby'; ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH SID='lsby'; ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='lsby'; ALTER SYSTEM SET log_archive_format='log_%s_%t_%r.arc' SCOPE=SPFILE SID='lsby'; ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*'; ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*'; ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*'; ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*'; ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/lsby/' SCOPE=SPFILE; ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/lsby/' SCOPE=SPFILE; ALTER SYSTEM SET fal_server='orcl_primary','orcl_stdby1' SCOPE=BOTH; ALTER SYSTEM SET fal_client='lsby' SCOPE=BOTH; ALTER SYSTEM SET log_archive_config='dg_config=(orcl_primary)' SCOPE=BOTH; ALTER SYSTEM SET log_archive_config='dg_config=(orcl_primary,orcl_stdby1)' SCOPE=BOTH; Fri Nov 27 17:29:03 2009 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[1]: Assigned to RFS process 6208 RFS[1]: Identified database type as 'physical standby' Primary database is in MAXIMUM PERFORMANCE mode RFS LogMiner: Client disabled from further notification Primary database is in MAXIMUM PERFORMANCE mode RFS[1]: Successfully opened standby log 6: '/u01/app/oracle/oradata/lsby/srl03.log' Fri Nov 27 17:29:06 2009 Fetching gap sequence in thread 1, gap sequence 70-77 Fri Nov 27 17:29:07 2009 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[2]: Assigned to RFS process 6222 RFS[2]: Identified database type as 'physical standby' kcrrvslf: active RFS archival for log 6 thread 1 sequence 79 Fri Nov 27 17:29:07 2009 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[3]: Assigned to RFS process 6224 RFS[3]: Identified database type as 'physical standby' RFS[2]: Successfully opened standby log 5: '/u01/app/oracle/oradata/lsby/srl02.log' RFS[3]: Archived Log: '/u01/app/oracle/flash_recovery_area/LSBY/log_71_1_696272632.arc' RFS[3]: Archived Log: '/u01/app/oracle/flash_recovery_area/LSBY/log_73_1_696272632.arc' RFS[3]: Archived Log: '/u01/app/oracle/flash_recovery_area/LSBY/log_74_1_696272632.arc' RFS[2]: Archived Log: '/u01/app/oracle/flash_recovery_area/LSBY/log_72_1_696272632.arc' RFS[2]: Archived Log: '/u01/app/oracle/flash_recovery_area/LSBY/log_75_1_696272632.arc' RFS[3]: Archived Log: '/u01/app/oracle/flash_recovery_area/LSBY/log_76_1_696272632.arc' RFS[2]: Archived Log: '/u01/app/oracle/flash_recovery_area/LSBY/log_77_1_696272632.arc' Fri Nov 27 17:29:08 2009 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[4]: Assigned to RFS process 6230 RFS[4]: Identified database type as 'physical standby' RFS[4]: Archived Log: '/u01/app/oracle/flash_recovery_area/LSBY/log_70_1_696272632.arc' Fri Nov 27 17:29:19 2009 ALTER SYSTEM SET log_archive_dest_1='location="/u01/app/oracle/flash_recovery_area/LSBY/"','valid_for=(ALL_LOGFILES,ALL_ROLES)' SCOPE=BOTH SID='lsby'; ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH SID='lsby'; ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='lsby'; ALTER SYSTEM SET log_archive_format='log_%s_%t_%r.arc' SCOPE=SPFILE SID='lsby'; ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*'; ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*'; ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*'; ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*'; ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/lsby/' SCOPE=SPFILE; ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/lsby/' SCOPE=SPFILE; ALTER SYSTEM SET fal_server='orcl_primary','orcl_stdby1' SCOPE=BOTH; ALTER SYSTEM SET fal_client='lsby' SCOPE=BOTH; Fri Nov 27 17:29:21 2009 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[5]: Assigned to RFS process 6248 RFS[5]: Identified database type as 'physical standby' Primary database is in MAXIMUM PERFORMANCE mode Primary database is in MAXIMUM PERFORMANCE mode RFS[5]: Successfully opened standby log 6: '/u01/app/oracle/oradata/lsby/srl03.log' Fri Nov 27 17:29:37 2009 Media Recovery Log /u01/app/oracle/flash_recovery_area/LSBY/log_70_1_696272632.arc Media Recovery Log /u01/app/oracle/flash_recovery_area/LSBY/log_71_1_696272632.arc Incomplete Recovery applied until change 5128352 time 11/27/2009 16:56:44 MRP0: Media Recovery Complete (lsby) MRP0: Background Media Recovery process shutdown (lsby) Fri Nov 27 17:30:13 2009 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[6]: Assigned to RFS process 6261 RFS[6]: Identified database type as 'physical standby' Fri Nov 27 17:32:16 2009 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE Attempt to start background Managed Standby Recovery process (lsby) Fri Nov 27 17:32:16 2009 MRP0 started with pid=27, OS id=6269 MRP0: Background Managed Standby Recovery process started (lsby) Fast Parallel Media Recovery enabled Managed Standby Recovery starting Real Time Apply parallel recovery started with 2 processes Waiting for all non-current ORLs to be archived... Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE Media Recovery Log /u01/app/oracle/flash_recovery_area/LSBY/log_71_1_696272632.arc Incomplete Recovery applied until change 5128352 time 11/27/2009 16:56:44 MRP0: Media Recovery Complete (lsby) MRP0: Background Media Recovery process shutdown (lsby) Fri Nov 27 17:32:24 2009 Redo Shipping Client Connected as PUBLIC ..... << Edited for sake of brevity >> ..... RFS[8]: Identified database type as 'physical standby' Fri Nov 27 17:59:50 2009alter database recover managed standby database cancel ORA-16136 signalled during: alter database recover managed standby database cancel... Fri Nov 27 18:03:37 2009 alter database recover to logical standby lsby Media Recovery Start: Managed Standby Recovery (lsby) Fast Parallel Media Recovery enabled Managed Standby Recovery not using Real Time Apply Media Recovery Log /u01/app/oracle/flash_recovery_area/LSBY/log_71_1_696272632.arc Incomplete Recovery applied until change 5128352 time 11/27/2009 16:56:44 Media Recovery Complete (lsby) tkcrrxms: Killing 3 processes (all RFS) RESETLOGS after incomplete recovery UNTIL CHANGE 5128352 Resetting resetlogs activation ID 1224046609 (0x48f57811) Online log /u01/app/oracle/oradata/lsby/redo01.log: Thread 1 Group 1 was previously cleared Online log /u01/app/oracle/oradata/lsby/redo02.log: Thread 1 Group 2 was previously cleared Online log /u01/app/oracle/oradata/lsby/redo03.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 5128350 Fri Nov 27 18:03:37 2009 Setting recovery target incarnation to 6 Converting standby mount to primary mount. ACTIVATE STANDBY: Complete - Database mounted as primary (lsby) *** DBNEWID utility started *** DBID will be changed from 1210321736 to new DBID of 2169894329 for database ORCL DBNAME will be changed from ORCL to new DBNAME of LSBY Starting datafile conversion Setting recovery target incarnation to 1 Datafile conversion completeFailed to find temporary file: /u01/app/oracle/oradata/lsby/temp01.dbf Database name changed to LSBY. Modify parameter file and generate a new password file before restarting. Database ID for database LSBY changed to 2169894329. 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 lsby Fri Nov 27 18:04:49 2009 Shutting down instance: further logons disabled Stopping background process MMNL Stopping background process MMON Shutting down instance (immediate) License high water mark = 8 Waiting for shared server 'S000' to die All dispatchers and shared servers shutdown ALTER DATABASE CLOSE NORMAL ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL... ARCH: Archival disabled due to shutdown: 1089 Shutting down archive processes Archiving is disabled Fri Nov 27 18:04:53 2009 ARCH shutting down ARC3: Archival stopped Fri Nov 27 18:04:53 2009 ARCH shutting down ARC2: Archival stopped Fri Nov 27 18:04:53 2009 ARCH shutting down ARC1: Archival stopped Fri Nov 27 18:04:53 2009 ARCH shutting down ARC0: Archival stopped Shutting down Data Guard Broker processes Fri Nov 27 18:04:56 2009 Completed: Data Guard Broker shutdown Fri Nov 27 18:04:57 2009 Stopping background process VKTM: ARCH: Archival disabled due to shutdown: 1089 Shutting down archive processes Archiving is disabled Archive process shutdown avoided: 0 active Fri Nov 27 18:04:59 2009 Instance shutdown complete Fri Nov 27 18:05:01 2009 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 Picked latch-free SCN scheme 2 Autotune of undo retention is turned on. IMODE=BR ILAT =18 LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 11.1.0.6.0. Using parameter settings in server-side spfile /u01/app/oracle/product/11.1.0/db_1/dbs/spfilelsby.ora System parameters with non-default values: processes = 150 sga_target = 400M control_files = "/u01/app/oracle/oradata/lsby/control01.ctl" db_file_name_convert = "/u01/app/oracle/oradata/orcl/" db_file_name_convert = "/u01/app/oracle/oradata/lsby/" log_file_name_convert = "/u01/app/oracle/oradata/orcl/" log_file_name_convert = "/u01/app/oracle/oradata/lsby/" db_block_size = 8192 compatible = "11.1.0.0.0" log_archive_config = "dg_config=(orcl_primary,orcl_stdby1)" log_archive_dest_1 = "location="/u01/app/oracle/flash_recovery_area/LSBY/"" log_archive_dest_1 = "valid_for=(ALL_LOGFILES,ALL_ROLES)" log_archive_dest_2 = "service=orcl_primary ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=orcl_primary" log_archive_dest_3 = "SERVICE=lsby ASYNC DB_UNIQUE_NAME=lsby VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)" log_archive_dest_state_1 = "ENABLE" log_archive_dest_state_2 = "ENABLE" log_archive_dest_state_3 = "ENABLE" log_archive_max_processes= 4 log_archive_min_succeed_dest= 1 log_archive_trace = 0 log_archive_format = "log_%s_%t_%r.arc" fal_client = "lsby" fal_server = "orcl_primary" fal_server = "orcl_stdby1" archive_lag_target = 0 db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area" db_recovery_file_dest_size= 8G standby_file_management = "AUTO" undo_tablespace = "UNDOTBS1" sec_case_sensitive_logon = FALSE remote_login_passwordfile= "EXCLUSIVE" db_domain = "" dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)" audit_file_dest = "/u01/app/oracle/admin/orcl/adump" audit_trail = "DB" db_name = "LSBY" db_unique_name = "lsby" open_cursors = 300 pga_aggregate_target = 150M dg_broker_start = TRUE dg_broker_config_file1 = "/u01/app/oracle/product/11.1.0/db_1/dbs/dr1orcl_primary.dat" dg_broker_config_file2 = "/u01/app/oracle/product/11.1.0/db_1/dbs/dr2orcl_primary.dat" diagnostic_dest = "/u01/app/oracle" Fri Nov 27 18:05:01 2009 PMON started with pid=2, OS id=6330 Fri Nov 27 18:05:01 2009 VKTM started with pid=3, OS id=6332 at elevated priority VKTM running at (20)ms precision Fri Nov 27 18:05:01 2009 ..... << Edited for sake of brevity >> ..... Fri Nov 27 18:05:02 2009 DMON started with pid=18, OS id=6366 Fri Nov 27 18:05:02 2009 ALTER DATABASE MOUNT Setting recovery target incarnation to 1 Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/flash_recovery_area/LSBY/ Successful mount of redo thread 1, with mount id 2169944849 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT Starting Data Guard Broker (DMON) Fri Nov 27 18:05:10 2009 NSV0 started with pid=21, OS id=6380 Fri Nov 27 18:05:14 2009 INSV started with pid=22, OS id=6382 Fri Nov 27 18:05:20 2009 RSM0 started with pid=23, OS id=6390 ALTER DATABASE MOUNT ORA-1100 signalled during: ALTER DATABASE MOUNT... Fri Nov 27 18:05:25 2009 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[1]: Assigned to RFS process 6396 RFS[1]: Identified database type as 'logical standby' Primary database is in MAXIMUM PERFORMANCE mode RFS LogMiner: Client enabled and ready for notification ..... << Edited for sake of brevity >> ..... Archive log rejected (thread 1 sequence 85) by RFS clients Archive log rejected (thread 1 sequence 85) by RFS clients Fri Nov 27 18:09:32 2009 alter database open resetlogs Data Guard Broker initializing... Archive log rejected (thread 1 sequence 85) by RFS clients Data Guard Broker initialization complete RESETLOGS after complete recovery through change 5128353 Fri Nov 27 18:09:52 2009 Setting recovery target incarnation to 2 Fri Nov 27 18:09:52 2009 Assigning activation ID 2169944849 (0x8156bb11) LGWR: STARTING ARCH PROCESSES Fri Nov 27 18:09:53 2009 ARC0 started with pid=27, OS id=6430 Fri Nov 27 18:09:53 2009 ARC2 started with pid=29, OS id=6434 Fri Nov 27 18:09:53 2009 ARC1 started with pid=28, OS id=6432 ARC0: Archival started ARC1: Archival started ARC2: Archival started ARC3: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: Becoming the 'no FAL' ARCH ARC0: Becoming the 'no SRL' ARCH ARC1: Becoming the heartbeat ARCH Fri Nov 27 18:09:53 2009 ARC3 started with pid=30, OS id=6436 Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/lsby/redo01.log Successful open of redo thread 1 Fri Nov 27 18:09:53 2009 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Fri Nov 27 18:09:53 2009 SMON: enabling cache recovery Fri Nov 27 18:09:53 2009 ..... << Edited for sake of brevity >> ..... ********************************************************** WARNING: Files may exists in db_recovery_file_dest that are not known to the database. Use the RMAN command CATALOG RECOVERY AREA to re-catalog any such files. If files cannot be cataloged, then manually delete them using OS command. One of the following events caused this: 1. A backup controlfile was restored. 2. A standby controlfile was restored. 3. The controlfile was re-created. 4. db_recovery_file_dest had previously been enabled and then disabled. ********************************************************** Starting background process FBDA Starting background process SMCO Fri Nov 27 18:10:01 2009 SMCO started with pid=32, OS id=6440 Fri Nov 27 18:10:01 2009 FBDA started with pid=31, OS id=6438 Archive log rejected (thread 1 sequence 85) by RFS clients Archive log rejected (thread 1 sequence 85) by RFS clients Archive log rejected (thread 1 sequence 85) by RFS clients LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation skipped -- detected logical instantiation LOGSTDBY: Validation complete LOGSTDBY: Attempting to pre-register on-disk logfiles Primary database is in MAXIMUM PERFORMANCE mode RFS[1]: Successfully opened standby log 6: '/u01/app/oracle/oradata/lsby/srl03.log' Global Name changed to LSBY Fri Nov 27 18:10:18 2009 RFS LogMiner: RFS id [6409] assigned as thread [1] PING handler Fri Nov 27 18:10:29 2009 db_recovery_file_dest_size of 8192 MB is 0.00% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Fri Nov 27 18:10:44 2009 Completed: alter database open resetlogs Fri Nov 27 18:12:58 2009Completed: alter database add supplemental log data (primary key, unique index) columns alter database add supplemental log data for procedural replicationSUPLOG: Updating supplemental logging attributes at scn = 5129646 SUPLOG: minimal = ON, primary key = ON SUPLOG: unique = ON, foreign key = OFF, all column = OFF SUPLOG: procedural replication = OFF SUPLOG: Updated supplemental logging attributes in control file at scn = 5129647 SUPLOG: minimal = ON, primary key = ON SUPLOG: unique = ON, foreign key = OFF, all column = OFF SUPLOG: procedural replication = OFF SUPLOG: Updated supplemental logging attributes at scn = 5129647 SUPLOG: minimal = ON, primary key = ON SUPLOG: unique = ON, foreign key = OFF, all column = OFF SUPLOG: procedural replication = ON..... Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOBFRAG$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOGMNR_BUILDLOG have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_SEED$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_DICTIONARY$ have been marked unusable Indexes of table SYSTEM.LOGMNR_ATTRCOL$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_ATTRIBUTE$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_CCOL$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_CDEF$ have been rebuilt and are now usable .....<< Edited for sake of brevity >> .....LOGSTDBY status: ORA-16246: User initiated abort apply successfully completed Completed: alter database abort logical standby applyALTER SYSTEM SET log_archive_dest_1='location="/u01/app/oracle/flash_recovery_area/LSBY/"','valid_for=(ALL_LOGFILES,ALL_ROLES)' SCOPE=BOTH SID='lsby'; ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH SID='lsby'; ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='lsby'; ALTER SYSTEM SET log_archive_format='log_%s_%t_%r.arc' SCOPE=SPFILE SID='lsby'; LOGSTDBY: APPLY_UNSET: MAX_EVENTS_RECORDED LOGSTDBY: APPLY_UNSET: PRESERVE_COMMIT_ORDER LOGSTDBY: APPLY_UNSET: RECORD_SKIP_ERRORS LOGSTDBY: APPLY_UNSET: RECORD_SKIP_DDL LOGSTDBY: APPLY_UNSET: RECORD_APPLIED_DDL ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*'; ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*'; ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*'; ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*'; ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/lsby/' SCOPE=SPFILE; ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/lsby/' SCOPE=SPFILE; ALTER SYSTEM SET fal_server='orcl_primary','orcl_stdby1' SCOPE=BOTH; ALTER SYSTEM SET fal_client='lsby' SCOPE=BOTH; ALTER SYSTEM SET log_archive_config='dg_config=(orcl_primary)' SCOPE=BOTH; ALTER SYSTEM SET log_archive_config='dg_config=(orcl_primary,orcl_stdby1)' SCOPE=BOTH; LOGSTDBY: APPLY_UNSET: MAX_EVENTS_RECORDED LOGSTDBY: APPLY_UNSET: PRESERVE_COMMIT_ORDER LOGSTDBY: APPLY_UNSET: RECORD_SKIP_ERRORS LOGSTDBY: APPLY_UNSET: RECORD_SKIP_DDL LOGSTDBY: APPLY_UNSET: RECORD_APPLIED_DDL LOGSTDBY: APPLY_UNSET: MAX_SGA LOGSTDBY: APPLY_UNSET: MAX_SERVERSALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE ALTER DATABASE START LOGICAL STANDBY APPLY (lsby) with optional part IMMEDIATE Attempt to start background Logical Standby process Fri Nov 27 18:18:04 2009 LSP0 started with pid=24, OS id=6537 Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATELOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 201 LOGMINER: Memory Size = 30M, Checkpoint interval = 150M LOGMINER: SpillScn 5133277, ResetLogScn 5078859 Fri Nov 27 18:18:05 2009 LOGMINER: session#=1, reader MS00 pid=37 OS id=6539 sid=144 started Fri Nov 27 18:18:05 2009 LOGMINER: session#=1, builder MS01 pid=38 OS id=6541 sid=124 started Fri Nov 27 18:18:05 2009 LOGMINER: session#=1, preparer MS02 pid=39 OS id=6543 sid=119 started LOGMINER: Begin mining logfile for session 1 thread 1 sequence 85, /u01/app/oracle/flash_recovery_area/LSBY/log_85_1_696272632.arc LOGMINER: Turning ON Log Auto Delete LOGMINER: End mining logfile for session 1 thread 1 sequence 85, /u01/app/oracle/flash_recovery_area/LSBY/log_85_1_696272632.arc Fri Nov 27 18:18:06 2009 LOGSTDBY Analyzer process AS00 started with pid=40 OS id=6545 Fri Nov 27 18:18:07 2009 LOGSTDBY Apply process AS03 started with pid=47 OS id=6551 Fri Nov 27 18:18:07 2009 LOGSTDBY Apply process AS01 started with pid=45 OS id=6547 Fri Nov 27 18:18:07 2009 LOGSTDBY Apply process AS02 started with pid=46 OS id=6549 Fri Nov 27 18:18:07 2009 LOGSTDBY Apply process AS04 started with pid=48 OS id=6553 Fri Nov 27 18:18:07 2009 LOGSTDBY Apply process AS05 started with pid=49 OS id=6555 Fri Nov 27 18:18:09 2009 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[5]: Assigned to RFS process 6563 RFS[5]: Identified database type as 'logical standby' Primary database is in MAXIMUM PERFORMANCE mode RFS LogMiner: Client enabled and ready for notification Primary database is in MAXIMUM PERFORMANCE mode RFS[5]: Successfully opened standby log 6: '/u01/app/oracle/oradata/lsby/srl03.log' Fri Nov 27 18:18:10 2009 NSV2 started with pid=51, OS id=6569 Fri Nov 27 18:18:20 2009 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid ..... << Edited for sake of brevity >> ..... LOGMINER: End mining logfile for session 1 thread 1 sequence 86, /u01/app/oracle/flash_recovery_area/LSBY/log_86_1_696272632.arc LOGMINER: Begin mining logfile for session 1 thread 1 sequence 87, /u01/app/oracle/oradata/lsby/srl03.log Fri Nov 27 18:18:25 2009 alter database abort logical standby apply Fri Nov 27 18:18:25 2009 LOGSTDBY Analyzer process AS00 pid=40 OS id=6545 stopped Fri Nov 27 18:18:25 2009 LOGSTDBY Apply process AS01 pid=45 OS id=6547 stopped Fri Nov 27 18:18:25 2009 LOGSTDBY Apply process AS05 pid=49 OS id=6555 stopped Fri Nov 27 18:18:25 2009 LOGSTDBY Apply process AS03 pid=47 OS id=6551 stopped Fri Nov 27 18:18:25 2009 LOGSTDBY Apply process AS02 pid=46 OS id=6549 stopped Fri Nov 27 18:18:25 2009 LOGSTDBY Apply process AS04 pid=48 OS id=6553 stopped LOGMINER: session#=1, reader MS00 pid=37 OS id=6539 sid=144 stopped Fri Nov 27 18:18:25 2009 LOGMINER: session#=1, builder MS01 pid=38 OS id=6541 sid=124 stopped Fri Nov 27 18:18:25 2009 LOGMINER: session#=1, preparer MS02 pid=39 OS id=6543 sid=119 stopped Fri Nov 27 18:18:28 2009 LOGSTDBY status: ORA-16246: User initiated abort apply successfully completed Completed: alter database abort logical standby apply ALTER SYSTEM SET log_archive_dest_1='location="/u01/app/oracle/flash_recovery_area/LSBY/"','valid_for=(ALL_LOGFILES,ALL_ROLES)' SCOPE=BOTH SID='lsby'; ALTER SYSTEM SET log_archive_dest_state_1='ENABLE' SCOPE=BOTH SID='lsby'; ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='lsby'; ALTER SYSTEM SET log_archive_format='log_%s_%t_%r.arc' SCOPE=SPFILE SID='lsby'; LOGSTDBY: APPLY_UNSET: MAX_EVENTS_RECORDED LOGSTDBY: APPLY_UNSET: PRESERVE_COMMIT_ORDER LOGSTDBY: APPLY_UNSET: RECORD_SKIP_ERRORS LOGSTDBY: APPLY_UNSET: RECORD_SKIP_DDL LOGSTDBY: APPLY_UNSET: RECORD_APPLIED_DDL ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*'; ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*'; ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*'; ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*'; ALTER SYSTEM SET db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/lsby/' SCOPE=SPFILE; ALTER SYSTEM SET log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/lsby/' SCOPE=SPFILE; ALTER SYSTEM SET fal_server='orcl_primary','orcl_stdby1' SCOPE=BOTH; ALTER SYSTEM SET fal_client='lsby' SCOPE=BOTH; LOGSTDBY: APPLY_UNSET: MAX_EVENTS_RECORDED LOGSTDBY: APPLY_UNSET: PRESERVE_COMMIT_ORDER LOGSTDBY: APPLY_UNSET: RECORD_SKIP_ERRORS LOGSTDBY: APPLY_UNSET: RECORD_SKIP_DDL LOGSTDBY: APPLY_UNSET: RECORD_APPLIED_DDL LOGSTDBY: APPLY_UNSET: MAX_SGA LOGSTDBY: APPLY_UNSET: MAX_SERVERS ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE ALTER DATABASE START LOGICAL STANDBY APPLY (lsby) with optional part IMMEDIATE Attempt to start background Logical Standby process Fri Nov 27 18:18:29 2009 LSP0 started with pid=24, OS id=6595 LOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 201 LOGMINER: Memory Size = 30M, Checkpoint interval = 150M LOGMINER: SpillScn 5133332, ResetLogScn 5078859 Fri Nov 27 18:18:29 2009 LOGMINER: session#=1, reader MS00 pid=37 OS id=6597 sid=144 started Fri Nov 27 18:18:29 2009 LOGMINER: session#=1, builder MS01 pid=38 OS id=6599 sid=119 started Fri Nov 27 18:18:29 2009 LOGMINER: session#=1, preparer MS02 pid=39 OS id=6601 sid=99 started Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE LOGMINER: Begin mining logfile for session 1 thread 1 sequence 87, /u01/app/oracle/oradata/lsby/srl03.log LOGMINER: Turning ON Log Auto Delete Fri Nov 27 18:18:30 2009 RFS LogMiner: Client enabled and ready for notification RFS LogMiner: Registered logfile [/u01/app/oracle/flash_recovery_area/LSBY/log_87_1_696272632.arc] to LogMiner session id [1] Fri Nov 27 18:18:31 2009 LOGSTDBY Analyzer process AS00 started with pid=40 OS id=6603 Fri Nov 27 18:18:31 2009 LOGSTDBY Apply process AS01 started with pid=45 OS id=6605 Fri Nov 27 18:18:31 2009 LOGSTDBY Apply process AS02 started with pid=46 OS id=6607 Fri Nov 27 18:18:31 2009 LOGSTDBY Apply process AS04 started with pid=48 OS id=6611 LOGMINER: End mining logfile for session 1 thread 1 sequence 87, /u01/app/oracle/oradata/lsby/srl03.log Fri Nov 27 18:18:31 2009 LOGSTDBY Apply process AS05 started with pid=49 OS id=6613 Fri Nov 27 18:18:31 2009 LOGSTDBY Apply process AS03 started with pid=47 OS id=6609 /* || Listing 8.5: || Confirming the Data Guard configuration now includes the new Logical Standby database */ DGMGRL> show configuration verbose Configuration Name: MAA_orcl Enabled: YES Protection Mode: MaxPerformanceDatabases: orcl_primary - Primary database orcl_stdby1 - Physical standby database lsby - Logical standby databaseFast-Start Failover: DISABLED Current status for "MAA_orcl": SUCCESS DGMGRL> show database verbose lsbyDatabase Name: lsby Role: LOGICAL STANDBY Enabled: YES Intended State: APPLY-ON Instance(s): lsbyProperties: DGConnectIdentifier = 'lsby' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' DelayMins = '0' Binding = 'OPTIONAL' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' LsbyASkipTxnCfgPr = '0,0,0' LsbyDSkipTxnCfgPr = '0,0,0' LsbyASkipCfgPr = '' LsbyDSkipCfgPr = '' LsbyASkipErrorCfgPr = '' LsbyDSkipErrorCfgPr = '' LsbyMaxEventsRecorded = '0' LsbyPreserveCommitOrder = '' LsbyRecordSkipErrors = '' LsbyRecordSkipDdl = '' LsbyRecordAppliedDdl = '' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' FastStartFailoverTarget = '' StatusReport = '(monitor)' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' LsbyParameters = '(monitor)' LsbySkipTxnTable = '(monitor)' LsbySkipTable = '(monitor)' LsbyFailedTxnInfo = '(monitor)' HostName = '11gStdby' SidName = 'lsby' StandbyArchiveLocation = '/u01/app/oracle/flash_recovery_area/LSBY/' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = 'log_%s_%t_%r.arc' LsbyMaxSga = '0' LsbyMaxServers = '0' LatestLog = '(monitor)' TopWaitEvents = '(monitor)' Current status for "lsby": SUCCESS
