Snapshot standby example in 11gR2 (Part 2)

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  636100608 bytes
Fixed Size                  1346048 bytes
Variable Size             406849024 bytes
Database Buffers          222298112 bytes
Redo Buffers                5607424 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

SQL> recover managed standby database disconnect;
ORA-01507: database not mounted


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  636100608 bytes
Fixed Size                  1346048 bytes
Variable Size             406849024 bytes
Database Buffers          222298112 bytes
Redo Buffers                5607424 bytes
Database mounted.

SQL>  recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL>

From alert_drorcl.log:

2011-01-10 11:17:41.693000 +08:00
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (drorcl)
krsv_proc_kill: Killing 3 processes (all RFS)
2011-01-10 11:17:43.053000 +08:00
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Stopping background process RVWR
Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/DRORCL/flashback/o1_mf_6lny14kz_.flb
Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/DRORCL/flashback/o1_mf_6lny14yq_.flb
Guaranteed restore point  dropped
Clearing standby activation ID 4067086165 (0xf26acf55)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Completed: alter database convert to physical standby
2011-01-10 11:18:38.426000 +08:00
ALTER DATABASE RECOVER  managed standby database disconnect
ORA-1507 signalled during: ALTER DATABASE RECOVER  managed standby database disconnect  ...
2011-01-10 11:18:46.330000 +08:00
Shutting down instance (abort)
License high water mark = 4
USER (ospid: 10661): terminating the instance
2011-01-10 11:18:47.370000 +08:00
Instance terminated by USER, pid = 10661
Instance shutdown complete
2011-01-10 11:18:50.315000 +08:00
Starting ORACLE instance (normal)
2011-01-10 11:18:51.838000 +08:00
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Autotune of undo retention is turned on.
IMODE=BR
ILAT =84
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiledrorcl.ora
System parameters with non-default values:
  processes                = 500
  sessions                 = 772
  memory_target            = 608M
  control_files            = "/u01/app/oracle/oradata/drorcl/control01.ctl"
  control_files            = "/u01/app/oracle/fast_recovery_area/drorcl/control02.ctl"
  db_file_name_convert     = "prorcl"
  db_file_name_convert     = "drorcl"
  log_file_name_convert    = "prorcl"
  log_file_name_convert    = "drorcl"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  log_archive_dest_1       = "LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=drorcl"
  log_archive_dest_2       = "SERVICE=prorcl VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prorcl COMPRESSION=enable"
  log_archive_dest_state_1 = "ENABLE"
  log_archive_dest_state_2 = "ENABLE"
  fal_server               = "prorcl"
  log_archive_format       = "%t_%s_%r.dbf"
  db_recovery_file_dest    = "/u01/app/oracle/fast_recovery_area"
  db_recovery_file_dest_size= 4977M
  standby_file_management  = "AUTO"
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=drorclXDB)"
  audit_file_dest          = "/u01/app/oracle/admin/drorcl/adump"
  audit_trail              = "DB"
  db_name                  = "prorcl"
  db_unique_name           = "DRORCL"
  open_cursors             = 300
  diagnostic_dest          = "/u01/app/oracle"
PMON started with pid=2, OS id=10756
PSP0 started with pid=3, OS id=10758
VKTM started with pid=4, OS id=10760 at elevated priority
VKTM running at (1)millisec precision with DBRM quantum (100)ms
GEN0 started with pid=5, OS id=10764
DIAG started with pid=6, OS id=10766
2011-01-10 11:18:52.802000 +08:00
DBRM started with pid=7, OS id=10768
DIA0 started with pid=8, OS id=10770
MMAN started with pid=9, OS id=10772
DBW0 started with pid=10, OS id=10774
LGWR started with pid=11, OS id=10776
CKPT started with pid=12, OS id=10778
SMON started with pid=13, OS id=10780
RECO started with pid=14, OS id=10782
MMON started with pid=15, OS id=10784
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=16, OS id=10786
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /u01/app/oracle
2011-01-10 11:18:53.922000 +08:00
ALTER DATABASE   MOUNT
2011-01-10 11:18:59.414000 +08:00
ARCH: STARTING ARCH PROCESSES
ARC0 started with pid=20, OS id=10848
2011-01-10 11:19:00.736000 +08:00
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Successful mount of redo thread 1, with mount id 4067063582
ARC1 started with pid=21, OS id=10850
Physical Standby Database mounted.
Lost write protection disabled
ARC2 started with pid=22, OS id=10852
Completed: ALTER DATABASE   MOUNT
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC2: Becoming the heartbeat ARCH
ARC2: Becoming the active heartbeat ARCH
ARC3 started with pid=23, OS id=10854
2011-01-10 11:19:02.426000 +08:00
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
2011-01-10 11:19:24.519000 +08:00
ALTER DATABASE RECOVER  managed standby database using current logfile disconnect
Attempt to start background Managed Standby Recovery process (drorcl)
MRP0 started with pid=24, OS id=10874
MRP0: Background Managed Standby Recovery process started (drorcl)
2011-01-10 11:19:31.054000 +08:00
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u01/app/oracle/oradata/drorcl/redo01.log
Clearing online log 1 of thread 1 sequence number 1
2011-01-10 11:19:32.300000 +08:00
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/drorcl/redo02.log
Clearing online log 2 of thread 1 sequence number 2
Completed: ALTER DATABASE RECOVER  managed standby database using current logfile disconnect
2011-01-10 11:19:33.993000 +08:00
Clearing online redo logfile 2 complete
Media Recovery Log /u01/app/oracle/fast_recovery_area/DRORCL/archivelog/2011_01_10/o1_mf_1_63_6lny7m56_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/DRORCL/archivelog/2011_01_10/o1_mf_1_64_6lny7mdb_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/DRORCL/archivelog/2011_01_10/o1_mf_1_65_6lny7m8d_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/DRORCL/archivelog/2011_01_10/o1_mf_1_66_6lny7m4h_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/DRORCL/archivelog/2011_01_10/o1_mf_1_67_6lny7m6j_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/DRORCL/archivelog/2011_01_10/o1_mf_1_68_6lny7pm7_.arc
Media Recovery Log /u01/app/oracle/fast_recovery_area/DRORCL/archivelog/2011_01_10/o1_mf_1_69_6lnyl4ct_.arc
Recovery of Online Redo Log: Thread 1 Group 5 Seq 70 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/drorcl/stdby05.log

Snapshot standby example in 11gR2 (Part 1)

SQL> startup mount
ORACLE instance started.

Total System Global Area  636100608 bytes
Fixed Size                  1346048 bytes
Variable Size             406849024 bytes
Database Buffers          222298112 bytes
Redo Buffers                5607424 bytes
Database mounted.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> alter database convert to snapshot standby;
alter database convert to snapshot standby
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_01/10/2011
11:07:53'.
ORA-01153: an incompatible media recovery is active


SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database convert to snapshot standby;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

From alert_drorcl.log:

2011-01-10 11:07:53.939000 +08:00
alter database convert to snapshot standby
ORA-38784 signalled during: alter database convert to snapshot standby...
2011-01-10 11:08:06.648000 +08:00
ALTER DATABASE RECOVER  managed standby database cancel
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/drorcl/drorcl/trace/drorcl_mrp0_10173.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 335626
MRP0: Background Media Recovery process shutdown (drorcl)
2011-01-10 11:08:07.929000 +08:00
Managed Standby Recovery Canceled (drorcl)
Completed: ALTER DATABASE RECOVER  managed standby database cancel
2011-01-10 11:08:20.495000 +08:00
alter database convert to snapshot standby
Starting background process RVWR
RVWR started with pid=27, OS id=10206
Allocated 3981204 bytes in shared pool for flashback generation buffer
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_01/10/2011 11:08:20
krsv_proc_kill: Killing 3 processes (all RFS)
2011-01-10 11:08:23.595000 +08:00
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 335626
Resetting resetlogs activation ID 4067055344 (0xf26a56f0)
Online log /u01/app/oracle/oradata/drorcl/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/drorcl/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/drorcl/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 335624
Setting recovery target incarnation to 2
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby
selec2011-01-10 11:09:36.857000 +08:00
alter database open
Assigning activation ID 4067086165 (0xf26acf55)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/drorcl/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
ARC1: Becoming the 'no SRL' ARCH
SMON: enabling cache recovery
NSA2 started with pid=24, OS id=10241
[10149] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:6705224 end:6705404 diff:180 (1 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
2011-01-10 11:09:38.401000 +08:00
Starting background process QMNC
QMNC started with pid=25, OS id=10243
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open
2011-01-10 11:09:41.012000 +08:00
Thread 1 advanced to log sequence 2 (LGWR switch)
  Current log# 2 seq# 2 mem# 0: /u01/app/oracle/oradata/drorcl/redo02.log
Archived Log entry 53 added for thread 1 sequence 1 ID 0xf26acf55 dest 1:
2011-01-10 11:09:42.379000 +08:00
db_recovery_file_dest_size of 4977 MB is 0.65% 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.
2011-01-10 11:11:47.018000 +08:00
RFS[3]: Assigned to RFS process 10339
RFS[3]: Selected log 6 for thread 1 sequence 63 dbid -227949715 branch 739987437
RFS[3]: Opened log for thread 1 sequence 66 dbid -227949715 branch 739987437
Archived Log entry 54 added for thread 1 sequence 66 rlc 739987437 ID 0xf26a56f0 dest 2:
Archived Log entry 55 added for thread 1 sequence 63 ID 0xf26a56f0 dest 1:
RFS[3]: Opened log for thread 1 sequence 67 dbid -227949715 branch 739987437
Archived Log entry 56 added for thread 1 sequence 67 rlc 739987437 ID 0xf26a56f0 dest 2:
RFS[4]: Assigned to RFS process 10341
RFS[4]: Opened log for thread 1 sequence 65 dbid -227949715 branch 739987437
Archived Log entry 57 added for thread 1 sequence 65 rlc 739987437 ID 0xf26a56f0 dest 2:
RFS[5]: Assigned to RFS process 10343
RFS[5]: Opened log for thread 1 sequence 64 dbid -227949715 branch 739987437
Archived Log entry 58 added for thread 1 sequence 64 rlc 739987437 ID 0xf26a56f0 dest 2:
2011-01-10 11:11:50.482000 +08:00
RFS[4]: Selected log 5 for thread 1 sequence 68 dbid -227949715 branch 739987437
Archived Log entry 59 added for thread 1 sequence 68 ID 0xf26a56f0 dest 1:
RFS[6]: Assigned to RFS process 10345
RFS[6]: Selected log 5 for thread 1 sequence 69 dbid -227949715 branch 739987437

Monitor dataguard status

From Primary:

SQL> select protection_mode,
  2  protection_level,
  3  database_role,
  4  switchover_status
  5  from v$database;

PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY          TO STANDBY


SQL> set pages 999
SQL> select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||'   '||message
  2  from v$dataguard_status;

TO_CHAR(TIMESTAMP,'YYYY-MON-DDHH24:MI:SS')||''||MESSAGE
--------------------------------------------------------------------------------
2011-JAN-10 08:43:34   ARC0: Archival started
2011-JAN-10 08:43:34   ARC1: Archival started
2011-JAN-10 08:43:34   ARC2: Archival started
2011-JAN-10 08:43:34   ARC1: Becoming the 'no FAL' ARCH
2011-JAN-10 08:43:34   ARC1: Becoming the 'no SRL' ARCH
2011-JAN-10 08:43:34   ARC2: Becoming the heartbeat ARCH
2011-JAN-10 08:43:35   Error 12514 received logging on to the standby
2011-JAN-10 08:43:35   PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12514.

2011-JAN-10 08:43:35   ARC1: Beginning to archive thread 1 sequence 58 (305558-3
26454)

2011-JAN-10 08:43:35   ARC1: Completed archiving thread 1 sequence 58 (305558-32
6454)

2011-JAN-10 08:43:35   ARC3: Archival started
2011-JAN-10 08:43:37   Error 12514 received logging on to the standby
2011-JAN-10 08:43:37   FAL[server, ARC2]: Error 12514 creating remote archivelog
 file 'drorcl'

2011-JAN-10 08:43:38   ARC3: Beginning to archive thread 1 sequence 59 (326454-3
26515)

2011-JAN-10 08:43:38   ARC3: Completed archiving thread 1 sequence 59 (326454-32
6515)

2011-JAN-10 08:46:52   ARC2: Standby redo logfile selected for thread 1 sequence
 58 for destination LOG_ARCHIVE_DEST_2

2011-JAN-10 08:46:52   ARC0: Beginning to archive thread 1 sequence 60 (326515-3
26771)

2011-JAN-10 08:46:52   ARC0: Completed archiving thread 1 sequence 60 (326515-32
6771)

2011-JAN-10 08:46:52   LNS: Standby redo logfile selected for thread 1 sequence
61 for destination LOG_ARCHIVE_DEST_2

2011-JAN-10 08:46:52   LNS: Beginning to archive log 1 thread 1 sequence 61
2011-JAN-10 08:46:53   ARC0: Standby redo logfile selected for thread 1 sequence
 60 for destination LOG_ARCHIVE_DEST_2

2011-JAN-10 10:20:03   LNS: Attempting destination LOG_ARCHIVE_DEST_2 network re
connect (3135)

2011-JAN-10 10:20:03   LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect aba
ndoned

2011-JAN-10 10:20:03   Error 3135 for archive log file 1 to 'drorcl'
2011-JAN-10 10:20:03   LNS: Failed to archive log 1 thread 1 sequence 61 (3135)
2011-JAN-10 10:25:22   Error 1031 received logging on to the standby
2011-JAN-10 10:25:22   PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 1031.

2011-JAN-10 10:26:41   Error 1031 received logging on to the standby
2011-JAN-10 10:26:41   PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 1031.

2011-JAN-10 10:28:02   Error 1031 received logging on to the standby
2011-JAN-10 10:28:02   PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 1031.

2011-JAN-10 10:29:21   Error 1031 received logging on to the standby
2011-JAN-10 10:29:21   PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 1031.

2011-JAN-10 10:30:40   Error 12514 received logging on to the standby
2011-JAN-10 10:30:40   PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12514.

2011-JAN-10 10:31:59   Error 12514 received logging on to the standby
2011-JAN-10 10:31:59   PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12514.

2011-JAN-10 10:33:18   Error 12514 received logging on to the standby
2011-JAN-10 10:33:18   PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12514.

2011-JAN-10 10:34:37   Error 12528 received logging on to the standby
2011-JAN-10 10:34:37   PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12528.

2011-JAN-10 10:34:37   ARC3: Beginning to archive thread 1 sequence 61 (326771-3
33704)

2011-JAN-10 10:34:38   ARC3: Completed archiving thread 1 sequence 61 (326771-33
3704)

2011-JAN-10 10:34:38   LNS: Standby redo logfile selected for thread 1 sequence
62 for destination LOG_ARCHIVE_DEST_2

2011-JAN-10 10:34:38   LNS: Beginning to archive log 2 thread 1 sequence 62
2011-JAN-10 10:34:38   ARC0: Standby redo logfile selected for thread 1 sequence
 61 for destination LOG_ARCHIVE_DEST_2


46 rows selected.

From standby database:

SQL> select protection_mode,
  2  protection_level,
  3  database_role,
  4  switchover_status
  5  from v$database;

PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PHYSICAL STANDBY NOT ALLOWED

SQL> select process,
  2  status,
  3  thread#,
  4  sequence#,
  5  block#,
  6  blocks
  7  from v$managed_standby;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1         61      10240       1111
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1         62       1057          1
RFS       IDLE                  0          0          0          0
MRP0      APPLYING_LOG          1         62       1057     102400

8 rows selected.

SQL> select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||'   '||message from v$dataguard_status;

TO_CHAR(TIMESTAMP,'YYYY-MON-DDHH24:MI:SS')||''||MESSAGE
--------------------------------------------------------------------------------
2011-JAN-10 10:34:36   ARC0: Archival started
2011-JAN-10 10:34:37   ARC1: Archival started
2011-JAN-10 10:34:37   ARC2: Archival started
2011-JAN-10 10:34:37   ARC1: Becoming the 'no FAL' ARCH
2011-JAN-10 10:34:37   ARC2: Becoming the heartbeat ARCH
2011-JAN-10 10:34:37   ARC2: Becoming the active heartbeat ARCH
2011-JAN-10 10:34:38   Primary database is in MAXIMUM PERFORMANCE mode
2011-JAN-10 10:34:38   RFS[1]: Assigned to RFS process 8979
2011-JAN-10 10:34:38   RFS[2]: Assigned to RFS process 8981
2011-JAN-10 10:34:38   ARC3: Archival started
2011-JAN-10 10:34:38   ARC3: Beginning to archive thread 1 sequence 61 (326771-3
33704)

2011-JAN-10 10:34:39   ARC3: Completed archiving thread 1 sequence 61 (0-0)
2011-JAN-10 10:34:51   Attempt to start background Managed Standby Recovery proc
ess

2011-JAN-10 10:34:51   MRP0: Background Managed Standby Recovery process started
2011-JAN-10 10:34:57   Managed Standby Recovery not using Real Time Apply
2011-JAN-10 10:34:57   Media Recovery Log /u01/app/oracle/fast_recovery_area/DRO
RCL/archivelog/2011_01_10/o1_mf_1_61_6lnw1yrp_.arc

2011-JAN-10 10:34:57   Media Recovery Waiting for thread 1 sequence 62 (in trans
it)

2011-JAN-10 10:35:06   MRP0: Background Media Recovery cancelled with status 160
37

2011-JAN-10 10:35:06   MRP0: Background Media Recovery process shutdown
2011-JAN-10 10:35:06   Managed Standby Recovery Canceled
2011-JAN-10 10:35:12   Attempt to start background Managed Standby Recovery proc
ess

2011-JAN-10 10:35:12   MRP0: Background Managed Standby Recovery process started
2011-JAN-10 10:35:18   Managed Standby Recovery starting Real Time Apply
2011-JAN-10 10:35:18   Media Recovery Waiting for thread 1 sequence 62 (in trans
it)


24 rows selected.

Recreating password file using "orapwd" will not work for 11g dataguard on standby database

Using orapwd (Will not work)

[oracle@vmxdb06 ~]$ orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdrorcl password=ora123 force=y ignorecase=no


From: vmxdb05: /u01/app/oracle/diag/rdbms/prorcl/prorcl/trace/prorcl_arc2_4538.trc
*** 2011-01-10 10:25:22.084
Error 3113 detaching RFS from standby instance at host 'drorcl'
krsi_verify_network: Ignoring detach error 3113
Redo shipping client performing standby login
OCISessionBegin failed -1
.. Detailed OCI error val is 1017 and errmsg is 'ORA-01017: invalid username/password; logon denied
'
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1031 and errmsg is 'ORA-01031: insufficient privileges
'
OCISessionBegin failed. Error -1
.. Detailed OCI error val is 1031 and errmsg is 'ORA-01031: insufficient privileges
'


From: vmxdb06: /u01/app/oracle/diag/rdbms/drorcl/drorcl/trace/alert_drorcl.log
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
FAL[client, ARC0]: Error 16191 connecting to prorcl for fetching gap sequence


Using SCP to copy password from Primary database

[oracle@vmxdb06 dbhome_1]$ scp vmxdb05:$ORACLE_HOME/dbs/orapwprorcl $ORACLE_HOME/dbs/orapwdrorcl
oracle@vmxdb05's password:
orapwprorcl 100% 1536 1.5KB/s 00:00


From: vmxdb05: /u01/app/oracle/diag/rdbms/prorcl/prorcl/trace/prorcl_arc2_4538.trc
*** 2011-01-10 10:34:37.074 2864 krsi.c
krsi_dst_fail: dest:2 err:12528 force:0 blast:1
Redo shipping client performing standby login
*** 2011-01-10 10:34:37.662 4595 krsu.c
Logged on to standby successfully
Client logon and security negotiation successful!

Setting envrionment variables through .bash_profile and oraenv

This is a very convinient way to set envrionemnt variables for Oracle in TEST envrionment. (For critical production envrionment, I still recommend to set Oracle envrionment variables explicitly using export)
Add following lines to ~/.bash_profile (for ksh/sh, add it to ~/.profile)

ORAENV_ASK=NO
ORACLE_SID=prorcl

. oraenv


Output/Verification:

login as: oracle
oracle@vmxdb05's password:
Last login: Sun Jan 9 21:56:17 2011 from b5-donghua1.lan
The Oracle base has been set to /u01/app/oracle
[oracle@vmxdb05 ~]$ env|grep -e ORA -e LIB
LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0/dbhome_1/lib
ORACLE_SID=prorcl
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

Active dataguard in 11gR2

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> startup mount
ORACLE instance started.

Total System Global Area  636100608 bytes
Fixed Size                  1346048 bytes
Variable Size             398460416 bytes
Database Buffers          230686720 bytes
Redo Buffers                5607424 bytes
Database mounted.
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

SQL> col datum_time for a19
SQL> col time_computed for a19
SQL> col name for a22
SQL> col value for a15

SQL> select name,value,time_computed,datum_time from v$dataguard_stats;

NAME                   VALUE           TIME_COMPUTED       DATUM_TIME
---------------------- --------------- ------------------- -------------------
transport lag          +00 00:00:00    01/09/2011 22:50:56 01/09/2011 22:50:55
apply lag              +00 00:00:00    01/09/2011 22:50:56 01/09/2011 22:50:55
apply finish time                      01/09/2011 22:50:56
estimated startup time 11              01/09/2011 22:50:56


SQL> select count(*) from tab;

  COUNT(*)
----------
      4716

SQL> create table t (id number);
create table t (id number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access


SQL> create global temporary table t(id number);
create global temporary table t(id number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

use 'duplicate database" command to create standby database without backup

Preparation:
==========================

[oracle@vmxdb06 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/dbs/initdrorcl.ora
drorcl.__db_cache_size=247463936
drorcl.__large_pool_size=4194304
drorcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
drorcl.__shared_pool_size=117440512
*.audit_file_dest='/u01/app/oracle/admin/drorcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/drorcl/control01.ctl','/u01/app/oracle/fast_recovery_area/drorcl/control02.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='prorcl','drorcl'
*.db_name='prorcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.db_unique_name='DRORCL'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=drorclXDB)'
*.fal_server='prorcl'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=drorcl'
*.log_archive_dest_2='SERVICE=prorcl VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prorcl'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='prorcl','drorcl'
*.memory_target=635437056
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=555
*.standby_file_management='AUTO



[oracle@vmxdb06 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.


LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmxdb06.lab.dbaglobe.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = drorcl_dgmgrl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = drorcl)
)
)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@vmxdb06 ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmxdb05.lab.dbaglobe.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prorcl)
)
)


DRORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmxdb06.lab.dbaglobe.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = drorcl)
)
)

PRORCL_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmxdb05.lab.dbaglobe.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prorcl_dgmgrl)
)
)


DRORCL_DGMGRL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmxdb06.lab.dbaglobe.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = drorcl_dgmgrl)
)
)


Duplication:
==========================


[oracle@vmxdb05 admin]$ rman target / auxiliary sys/ora123@drorcl_dgmgrl

Recovery Manager: Release 11.2.0.2.0 - Production on Sun Jan 9 18:21:39 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: PRORCL (DBID=4067017581)
connected to auxiliary database: PRORCL (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 09-JAN-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwprorcl' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdrorcl' ;
}
executing Memory Script

Starting backup at 09-JAN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
Finished backup at 09-JAN-11

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/drorcl/control01.ctl';
restore clone controlfile to '/u01/app/oracle/fast_recovery_area/drorcl/control02.ctl' from
'/u01/app/oracle/oradata/drorcl/control01.ctl';
}
executing Memory Script

Starting backup at 09-JAN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_prorcl.f tag=TAG20110109T182208 RECID=2 STAMP=739995729
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 09-JAN-11

Starting restore at 09-JAN-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 09-JAN-11

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/drorcl/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/drorcl/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/drorcl/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/drorcl/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/drorcl/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/drorcl/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/drorcl/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/drorcl/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/drorcl/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/drorcl/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 09-JAN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
output file name=/u01/app/oracle/oradata/drorcl/system01.dbf tag=TAG20110109T182219
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/prorcl/sysaux01.dbf
output file name=/u01/app/oracle/oradata/drorcl/sysaux01.dbf tag=TAG20110109T182219
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/prorcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/drorcl/undotbs01.dbf tag=TAG20110109T182219
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/prorcl/users01.dbf
output file name=/u01/app/oracle/oradata/drorcl/users01.dbf tag=TAG20110109T182219
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 09-JAN-11

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=739995828 file name=/u01/app/oracle/oradata/drorcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=739995828 file name=/u01/app/oracle/oradata/drorcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=739995828 file name=/u01/app/oracle/oradata/drorcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=739995828 file name=/u01/app/oracle/oradata/drorcl/users01.dbf
Finished Duplicate Db at 09-JAN-11

RMAN> exit


Recovery Manager complete.