28 | 03 | 2024
Latest Articles
Popular Articles

Data Guard

Create a logical standby database and a Data Guard Broker configuration

User Rating:  / 0
PoorBest 

Create a logical standby database and a Data Guard Broker configuration.




Check for unsupported datatypes and for tables without primary nor unique key.


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';.


Check whether force logging has been enabled at database level.



10:18:43 PLATINUM_USER_25/08/2011 15:59>select force_logging from v$database;

FOR
---
YES


Take a backup of your database

Take a standby control file backup of your database

Restore your database as a physical standby database, including post installation  tasks like creation of standby redo logs.
Create a Data Guard configuration for your new physical standby database and monitor recovery.

See also Create a physical standby database and a Data Guard Broker configuration.



DGMGRL> show configuration;

Configuration
Name:                PLATINUM_LSDB
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
PLATINUM      - Primary database
PLATINUM_LSDB - Physical standby database

Current status for "PLATINUM_LSDB":
SUCCESS

DGMGRL> show database 'PLATINUM' sendqentries;
PRIMARY_SEND_QUEUE
STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs)
CURRENT        730220334                1                 4061  08/26/2011 10:38:20                         8842011829734                                97

DGMGRL> show database 'PLATINUM_LSDB' recvqentries;
STANDBY_RECEIVE_QUEUE
STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs).




Once we have a full operational and synchronized physical standby database, once we checked whether there are no unsupported data types and we do not have tables without unique key, we are ready to continue with the latest steps ie the conversion of the physical database to a logical standby database.





DGMGRL> edit database 'PLATINUM_LSDB' set state='LOG-APPLY-OFF';
Succeeded.





We monitor whether there are no active transactions. For this we select from v$transaction and from dba_2pc_pending
Dba_2pc_pending lists the components of the 2 phase commit XA transactions.
I personally ran into an issue while trying to setup a logical standby database while there was a remaining prepared global transaction component which wasn' t rolled back nor committed.






SQL> select count(*) from v$transaction;

COUNT(*)
----------
1


SQL> select count(*) from v$transaction;

COUNT(*)
----------
1


SQL> select a.username,a.sql_id,a.prev_sql_id from v$session a,v$transaction t w here a.saddr =  t.ses_addr;

USERNAME                       SQL_ID        PREV_SQL_ID
------------------------------ ------------- -------------
SCHEMAOWNER                                    85kp65ct9j032

SQL> /

USERNAME                       SQL_ID        PREV_SQL_ID
------------------------------ ------------- -------------
SCHEMAOWNER                                    85kp65ct9j032

SQL> /

USERNAME                       SQL_ID        PREV_SQL_ID
------------------------------ ------------- -------------
SCHEMAOWNER                                    85kp65ct9j032

SQL> /

USERNAME                       SQL_ID        PREV_SQL_ID
------------------------------ ------------- -------------
SCHEMAOWNER                                    85kp65ct9j032

SQL> /

USERNAME                       SQL_ID        PREV_SQL_ID
------------------------------ ------------- -------------
SCHEMAOWNER                                    85kp65ct9j032



SQL> select a.sid, a.serial#  from v$session a,v$transaction t where a.saddr = t.ses_addr;

SID    SERIAL#
---------- ----------
288       3093

SQL> alter system kill session '288,3093' immediate;

System altered.

SQL> select count(*) from v$transaction;

COUNT(*)
----------
0


SQL > select count(*) from dba_2PC pending;
 

 

We are now ready to build the data dictionary into the redo stream. This is done at level of the primary database.

 

SQL> exec dbms_logstdby.build;
PL/SQL procedure successfully completed.

 

Fri Aug 26 10:56:12 2011
Fri Aug 26 10:56:12 2011
Logminer Bld: Build started
Fri Aug 26 10:56:12 2011
Thread 1 advanced to log sequence 4064
Current log# 2 seq# 4064 mem# 0: /u01/oradata/PLATINUM/redo02.log
Fri Aug 26 10:56:12 2011
Fri Aug 26 10:56:12 2011
Logminer Bld: Lockdown Complete.  DB_TXN_SCN is 2058 2969135246
Fri Aug 26 10:56:13 2011
LNS: Standby redo logfile selected for thread 1 sequence 4064 for destination LOG_ARCHIVE_DEST_1
LNS0 started with pid=19, OS id=8976
Fri Aug 26 10:56:20 2011
Thread 1 advanced to log sequence 4065
Current log# 3 seq# 4065 mem# 0: /u01/oradata/PLATINUM/redo03.log
Fri Aug 26 10:56:20 2011
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_1
ARCH: Standby redo logfile selected for thread 1 sequence 4064 for destination LOG_ARCHIVE_DEST_1
Fri Aug 26 10:56:21 2011
Logminer Bld: Done
Fri Aug 26 10:56:21 2011
LNS: Standby redo logfile selected for thread 1 sequence 4065 for destination LOG_ARCHIVE_DEST_1
Fri Aug 26 10:56:24 2011
Thread 1 cannot allocate new log, sequence 4066
Checkpoint not complete
Current log# 3 seq# 4065 mem# 0: /u01/oradata/PLATINUM/redo03.log
LNS0 started with pid=19, OS id=9318
Fri Aug 26 10:56:29 2011
Thread 1 advanced to log sequence 4066
Current log# 1 seq# 4066 mem# 0: /u01/oradata/PLATINUM/redo01.log
Fri Aug 26 10:56:30 2011
LNS: Standby redo logfile selected for thread 1 sequence 4066 for destination LOG_ARCHIVE_DEST_1.


We are now ready to convert the physical standby to a logical standby database. This is to be done at level of the standby database.



SQL> alter database recover to logical standby PALLADIU;

Database altered.




alter database recover to logical standby PALLADIU
Fri Aug 26 10:57:33 2011
Media Recovery Start: Managed Standby Recovery (PLATINUM)
Fri Aug 26 10:57:33 2011
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 7 processes
Media Recovery Log /u01/flash_recovery_area/PLATINUM_LSDB/archivelog/2011_08_26/o1_mf_1_4061_75gq3tx9_.arc
Media Recovery Log /u01/flash_recovery_area/PLATINUM_LSDB/archivelog/2011_08_26/o1_mf_1_4062_75gqf9jj_.arc
Media Recovery Log /u01/flash_recovery_area/PLATINUM_LSDB/archivelog/2011_08_26/o1_mf_1_4063_75gqxfnc_.arc
Media Recovery Log /u01/flash_recovery_area/PLATINUM_LSDB/archivelog/2011_08_26/o1_mf_1_4064_75gqxo0z_.arc
Media Recovery Log /u01/flash_recovery_area/PLATINUM_LSDB/archivelog/2011_08_26/o1_mf_1_4065_75gqyhqz_.arc
Fri Aug 26 10:57:35 2011
Incomplete Recovery applied until change 8842011831459
Fri Aug 26 10:57:35 2011
Media Recovery Complete (PLATINUM)
RESETLOGS after incomplete recovery UNTIL CHANGE 8842011831459
Resetting resetlogs activation ID 2007987566 (0x77af756e)
Online log /u01/flash_recovery_area/PLATINUM_LSDB/onlinelog/o1_mf_1_75gpsg0s_.log: Thread 1 Group 1 was previously cleared
Online log /u01/flash_recovery_area/PLATINUM_LSDB/onlinelog/o1_mf_2_75gpsgl5_.log: Thread 1 Group 2 was previously cleared
Online log /u01/flash_recovery_area/PLATINUM_LSDB/onlinelog/o1_mf_3_75gpsgxq_.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 8842011831457
Fri Aug 26 10:57:40 2011
Setting recovery target incarnation to 2
Fri Aug 26 10:57:40 2011
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/product/10.2.0/dbs/arch
Converting standby mount to primary mount.
Fri Aug 26 10:57:40 2011
ACTIVATE STANDBY: Complete - Database mounted as primary (PLATINUM)
*** DBNEWID utility started ***
DBID will be changed from 2007998574 to new DBID of 1982432228 for database PLATINUM
DBNAME will be changed from PLATINUM to new DBNAME of PALLADIU
Starting datafile conversion
Setting recovery target incarnation to 1
Datafile conversion complete
Failed to find temporary file: /u01/oradata/PLATINUM/temp01.dbf
Database name changed to PALLADIU.
Modify parameter file and generate a new password file before restarting.
Database ID for database PALLADIU changed to 1982432228.
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 PALLADIU
Fri Aug 26 10:58:31 2011
ARC1: Archival disabled due to instance shutdown
Shutting down archive processes
Archiving is disabled
Fri Aug 26 10:58:41 2011
ARCH shutting down
ARC0: Archival stopped
Fri Aug 26 10:58:46 2011
ARCH shutting down
ARC1: Archival stopped.







SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.5770E+10 bytes
Fixed Size                  2119344 bytes
Variable Size            1.5703E+10 bytes
Database Buffers         1.0050E+10 bytes
Redo Buffers               14655488 bytes
Database mounted.
SQL> select database_role from v$database;

DATABASE_ROLE
------------------------------------------------
LOGICAL STANDBY


SQL> alter database open resetlogs;

Database altered.

 


ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/oradata/PLATINUM/temp01.dbf'
Fri Aug 26 11:03:45 2011
File 201 not verified due to error ORA-01157
Fri Aug 26 11:03:45 2011
Dictionary check complete
Fri Aug 26 11:03:45 2011
SMON: enabling tx recovery
Fri Aug 26 11:03:45 2011
Re-creating tempfile /u01/oradata/PLATINUM/temp01.dbf
Database Characterset is UTF8
Threshold validation cannot be done before catproc is loaded.
**********************************************************
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.
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.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=20, OS id=29435
Fri Aug 26 11:03:46 2011
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation skipped -- detected logical instantiation
Fri Aug 26 11:03:46 2011
LOGSTDBY: Validation complete
Global Name changed to PALLADIU
Fri Aug 26 11:03:47 2011
Errors in file /u01/app/oracle/admin/PALLADIU/udump/PALLADIU_ora_27989.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 2, column 15:
PL/SQL: ORA-16224: Database Guard is enabled
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored
Completed: alter database open resetlogs.



DGMGRL> show configuration;

Configuration
Name:                PLATINUM_LSDB
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
PLATINUM      - Primary database
PLATINUM_LSDB - Physical standby database

Current status for "PLATINUM_LSDB":
Warning: ORA-16607: one or more databases have failed


DGMGRL> disable configuration;
Disabled.
DGMGRL> remove database 'PLATINUM_LSDB';
Removed database "PLATINUM_LSDB" from the configuration

DGMGRL> add database 'PLATINUM_LSDB' as connect identifier is PLATINUM_LSDB maintained as logical;
Database "PLATINUM_LSDB" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration
Name:                PLATINUM_LSDB
Enabled:             YES
Protection Mode:     MaxPerformance
Fast-Start Failover: DISABLED
Databases:
PLATINUM      - Primary database
PLATINUM_LSDB - Logical standby database

Current status for "PLATINUM_LSDB":
SUCCESS.



ALTER SYSTEM SET log_archive_dest_2='location="/u01/standby_archive_dest/PALLADIU"','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)' SCOPE=BOTH SID='PALLADIU';
Fri Aug 26 11:10:06 2011
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='PALLADIU';
Fri Aug 26 11:10:06 2011
ALTER SYSTEM SET standby_archive_dest='/u01/standby_archive_dest/PALLADIU' SCOPE=BOTH SID='PALLADIU';
Fri Aug 26 11:10:06 2011
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='PALLADIU';
Fri Aug 26 11:10:06 2011
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='PALLADIU';
Fri Aug 26 11:10:06 2011
ALTER SYSTEM SET standby_file_management='auto' SCOPE=BOTH SID='*';
Fri Aug 26 11:10:06 2011
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Fri Aug 26 11:10:06 2011
ALTER SYSTEM SET log_archive_max_processes=2 SCOPE=BOTH SID='*';
Fri Aug 26 11:10:06 2011
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Fri Aug 26 11:10:06 2011
ALTER SYSTEM SET fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=primary-host)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PLATINUM_XPT)(SERVER=dedicated)))' SCOPE=BOTH;
Fri Aug 26 11:10:06 2011
ALTER SYSTEM SET fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=standby-host.my-domain.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PLATINUM_LSDB_XPT)(INSTANCE_NAME=PALLADIU)(SERVER=dedicated)))' SCOPE=BOTH;
Fri Aug 26 11:10:06 2011
ALTER SYSTEM SET log_archive_config='dg_config=(PLATINUM)' SCOPE=BOTH;
Fri Aug 26 11:10:06 2011
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
Fri Aug 26 11:10:06 2011
LOGMINER: Error 308 encountered, failed to read missing logfile /u01/standby_archive_dest/PALLADIU1_4064_730220334.dbf
LOGSTDBY: Unable to register recovery logfiles, will resend
Fri Aug 26 11:10:06 2011
LOGMINER: Error 308 encountered, failed to read missing logfile /u01/standby_archive_dest/PALLADIU/1_4065_730220334.dbf
Fri Aug 26 11:10:06 2011
ALTER DATABASE START LOGICAL STANDBY APPLY (PALLADIU)
with optional part
IMMEDIATE
Attempt to start background Logical Standby process
LSP0 started with pid=26, OS id=1520
Fri Aug 26 11:10:07 2011
LOGSTDBY Parameter:            DISABLE_APPLY_DELAY =
LOGSTDBY Parameter:                      REAL_TIME =
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
Fri Aug 26 11:10:07 2011
LOGSTDBY status: ORA-16111: log mining and apply setting up
Fri Aug 26 11:10:07 2011
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
Fri Aug 26 11:10:09 2011
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[1]: Assigned to RFS process 1522
RFS[1]: Identified database type as 'logical standby'
Fri Aug 26 11:10:09 2011
RFS LogMiner: Client enabled and ready for notification
RFS[1]: Successfully opened standby log 100: '/u01/flash_recovery_area/PLATINUM_LSDB/onlinelog/o1_mf_100_75gq9h9o_.log'
Fri Aug 26 11:10:09 2011
RFS LogMiner: Client enabled and ready for notification
Fri Aug 26 11:10:09 2011
RFS LogMiner: Registered logfile [/u01/standby_archive_dest/PALLADIU/1_4067_730220334.dbf] to LogMiner session id [1]
Fri Aug 26 11:10:10 2011
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[2]: Assigned to RFS process 1524
RFS[2]: Identified database type as 'logical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Fri Aug 26 11:10:10 2011
RFS LogMiner: Client enabled and ready for notification
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 100: '/u01/flash_recovery_area/PLATINUM_LSDB/onlinelog/o1_mf_100_75gq9h9o_.log'
Fri Aug 26 11:10:11 2011
RFS LogMiner: Client enabled and ready for notification
Fri Aug 26 11:10:11 2011
Primary database is in MAXIMUM PERFORMANCE mode
Fri Aug 26 11:10:11 2011
RFS LogMiner: Registered logfile [/u01/standby_archive_dest/PALLADIU/1_4068_730220334.dbf] to LogMiner session id [1]
Fri Aug 26 11:10:11 2011
RFS[2]: Successfully opened standby log 102: '/u01/flash_recovery_area/PLATINUM_LSDB/onlinelog/o1_mf_102_75gq9wy8_.log'
NSV1 started with pid=29, OS id=1874
Fri Aug 26 11:10:48 2011
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[3]: Assigned to RFS process 1878
RFS[3]: Identified database type as 'logical standby'
Fri Aug 26 11:10:48 2011
RFS LogMiner: Client enabled and ready for notification
Fri Aug 26 11:10:48 2011
RFS LogMiner: RFS id [1878] assigned as thread [1] PING handler
RFS[3]: Archived Log: '/u01/standby_archive_dest/PALLADIU/1_4064_730220334.dbf'
Fri Aug 26 11:10:48 2011
RFS LogMiner: Registered logfile [/u01/standby_archive_dest/PALLADIU/1_4064_730220334.dbf] to LogMiner session id [1]
RFS[3]: Archived Log: '/u01/standby_archive_dest/PALLADIU/1_4065_730220334.dbf'
Fri Aug 26 11:10:48 2011
RFS LogMiner: Registered logfile [/u01/standby_archive_dest/PALLADIU/1_4065_730220334.dbf] to LogMiner session id [1]
RFS[3]: Archived Log: '/u01/standby_archive_dest/PALLADIU/1_4066_730220334.dbf'
Fri Aug 26 11:10:48 2011
RFS LogMiner: Registered logfile [/u01/standby_archive_dest/PALLADIU/1_4066_730220334.dbf] to LogMiner session id [1]
LOGMINER: session# = 1, reader process P000 started with pid=31 OS id=1880
LOGMINER: session# = 1, builder process P001 started with pid=32 OS id=1882
LOGMINER: session# = 1, preparer process P002 started with pid=33 OS id=1884
Fri Aug 26 11:10:49 2011
LOGMINER: Begin mining logfile: /u01/standby_archive_dest/PALLADIU/1_4064_730220334.dbf
Fri Aug 26 11:10:49 2011
LOGMINER: Turning ON Log Auto Delete
Fri Aug 26 11:10:56 2011
alter database abort logical standby apply
Fri Aug 26 11:10:56 2011
ALTER DATABASE ABORT LOGICAL STANDBY APPLY
Fri Aug 26 11:10:57 2011
Thread 1 advanced to log sequence 2
Current log# 2 seq# 2 mem# 0: /u01/flash_recovery_area/PLATINUM_LSDB/onlinelog/o1_mf_2_75grch1v_.log
Fri Aug 26 11:10:59 2011
LOGMINER: End mining logfile: /u01/standby_archive_dest/PALLADIU/1_4064_730220334.dbf
Fri Aug 26 11:10:59 2011
LOGMINER: Begin mining logfile: /u01/standby_archive_dest/PALLADIU/1_4065_730220334.dbf
Fri Aug 26 11:10:59 2011
LOGMINER: End mining logfile: /u01/standby_archive_dest/PALLADIU/1_4065_730220334.dbf
Fri Aug 26 11:10:59 2011
LOGMINER: Begin mining logfile: /u01/standby_archive_dest/PALLADIU/1_4066_730220334.dbf
Fri Aug 26 11:11:04 2011
Thread 1 advanced to log sequence 3
Current log# 3 seq# 3 mem# 0: /u01/flash_recovery_area/PLATINUM_LSDB/onlinelog/o1_mf_3_75grchgs_.log
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ATTRCOL$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CCOL$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CDEF$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COL$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COLTYPE$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ICOL$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_IND$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDCOMPART$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDPART$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDSUBPART$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOB$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOBFRAG$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_OBJ$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TAB$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABCOMPART$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABPART$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABSUBPART$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TS$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TYPE$ have been marked unusable
Fri Aug 26 11:11:05 2011
Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_USER$ 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
Indexes of table  SYSTEM.LOGMNR_COL$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_COLTYPE$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_DICTIONARY$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_ICOL$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_IND$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_INDCOMPART$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_INDPART$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_INDSUBPART$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_LOB$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_LOBFRAG$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_OBJ$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_TAB$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_TABCOMPART$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_TABPART$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_TABSUBPART$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_TS$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_TYPE$ have been rebuilt and are now usable
Indexes of table  SYSTEM.LOGMNR_USER$ have been rebuilt and are now usable
LSP2 started with pid=34, OS id=2236
Fri Aug 26 11:11:14 2011
LOGMINER: Begin mining logfile: /u01/standby_archive_dest/PALLADIU/1_4064_730220334.dbf
Fri Aug 26 11:11:14 2011
LOGMINER: Turning ON Log Auto Delete
LOGSTDBY Analyzer process P003 started with pid=35 OS id=2238
LOGSTDBY Apply process P005 started with pid=37 OS id=2242
LOGSTDBY Apply process P007 started with pid=39 OS id=2246
LOGSTDBY Apply process P008 started with pid=40 OS id=2248
LOGSTDBY Apply process P004 started with pid=36 OS id=2240
LOGSTDBY Apply process P006 started with pid=38 OS id=2244
Fri Aug 26 11:11:15 2011
LOGSTDBY: Shutdown acknowledged
LOGSTDBY Apply process P005 pid=37 OS id=2242 stopped
LOGSTDBY Analyzer process P003 pid=35 OS id=2238 stopped
LOGSTDBY Apply process P004 pid=36 OS id=2240 stopped
LOGSTDBY Apply process P007 pid=39 OS id=2246 stopped
LOGSTDBY Apply process P006 pid=38 OS id=2244 stopped
LOGSTDBY Apply process P008 pid=40 OS id=2248 stopped
Fri Aug 26 11:11:15 2011
LOGSTDBY status: ORA-16246: User initiated abort apply successfully completed
Fri Aug 26 11:11:15 2011
Completed: alter database abort logical standby apply
Fri Aug 26 11:11:15 2011
ALTER SYSTEM SET log_archive_dest_2='location="/u01/standby_archive_dest/PALLADIU"','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)' SCOPE=BOTH SID='PALLADIU';
Fri Aug 26 11:11:15 2011
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='PALLADIU';
Fri Aug 26 11:11:15 2011
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH SID='PALLADIU';
Fri Aug 26 11:11:15 2011
ALTER SYSTEM SET standby_archive_dest='/u01/standby_archive_dest/PALLADIU' SCOPE=BOTH SID='PALLADIU';
Fri Aug 26 11:11:15 2011
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='PALLADIU';
Fri Aug 26 11:11:15 2011
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='PALLADIU';
Fri Aug 26 11:11:15 2011
ALTER SYSTEM SET standby_file_management='auto' SCOPE=BOTH SID='*';
Fri Aug 26 11:11:15 2011
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Fri Aug 26 11:11:16 2011
ALTER SYSTEM SET log_archive_max_processes=2 SCOPE=BOTH SID='*';
Fri Aug 26 11:11:16 2011
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Fri Aug 26 11:11:16 2011
ALTER SYSTEM SET fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=primary-host)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PLATINUM_XPT)(SERVER=dedicated)))' SCOPE=BOTH;
Fri Aug 26 11:11:16 2011
ALTER SYSTEM SET fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=standby-host.my-domain.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=PLATINUM_LSDB_XPT)(INSTANCE_NAME=PALLADIU)(SERVER=dedicated)))' SCOPE=BOTH;
Fri Aug 26 11:11:16 2011
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
Fri Aug 26 11:11:16 2011
ALTER DATABASE START LOGICAL STANDBY APPLY (PALLADIU)
with optional part
IMMEDIATE
Attempt to start background Logical Standby process
LSP0 started with pid=26, OS id=2250
LOGSTDBY status: ORA-16111: log mining and apply setting up
Fri Aug 26 11:11:16 2011
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
LOGMINER: session# = 1, reader process P000 started with pid=31 OS id=1880
LOGMINER: session# = 1, builder process P001 started with pid=32 OS id=1882
LOGMINER: session# = 1, preparer process P002 started with pid=33 OS id=1884
LSP2 started with pid=34, OS id=2252
Fri Aug 26 11:11:16 2011
LOGMINER: Turning ON Log Auto Delete
LOGMINER: Begin mining logfile: /u01/standby_archive_dest/PALLADIU/1_4064_730220334.dbf
Fri Aug 26 11:11:16 2011
LOGMINER: Turning ON Log Auto Delete
LOGSTDBY Analyzer process P003 started with pid=35 OS id=2238
LOGSTDBY Apply process P005 started with pid=37 OS id=2242
LOGSTDBY Apply process P007 started with pid=39 OS id=2246
LOGSTDBY Apply process P004 started with pid=36 OS id=2240
LOGSTDBY Apply process P006 started with pid=38 OS id=2244
LOGSTDBY Apply process P008 started with pid=40 OS id=2248
Fri Aug 26 11:11:16 2011
LOGSTDBY Parameter:            DISABLE_APPLY_DELAY =
LOGSTDBY Parameter:                      REAL_TIME =
Completed: ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE
Fri Aug 26 11:11:17 2011
LOGMINER: End mining logfile: /u01/standby_archive_dest/PALLADIU/1_4064_730220334.dbf
Fri Aug 26 11:11:17 2011
LOGMINER: Begin mining logfile: /u01/standby_archive_dest/PALLADIU/1_4065_730220334.dbf
Fri Aug 26 11:11:17 2011
LOGMINER: End mining logfile: /u01/standby_archive_dest/PALLADIU/1_4065_730220334.dbf
Fri Aug 26 11:11:17 2011
LOGMINER: Begin mining logfile: /u01/standby_archive_dest/PALLADIU/1_4066_730220334.dbf
Fri Aug 26 11:11:18 2011
LOGMINER: Log Auto Delete - deleting: /u01/standby_archive_dest/PALLADIU/1_4064_730220334.dbf
Deleted file /u01/standby_archive_dest/PALLADIU/1_4064_730220334.dbf
Fri Aug 26 11:11:18 2011.




Note that the creation of the logical standby automatically enables supplemental logging.


SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;

SUPPLEMENTAL_LOG_DATA_MI SUPPLEMEN SUPPLEMEN SUPPLEMEN SUPPLEMEN
------------------------ --------- --------- --------- ---------
IMPLICIT                 YES       YES       NO        NO

Elapsed: 00:00:00.00.