Working with Fast Start Failover (FSFO) in DATA GUARD

Working with Fast Start Failover (FSFO) in DATA GUARD

Fast start failover is a feature which allows a primary database to failover to one of the target standby terminal automatically in case any error conditions becomes true

Prerequisites of FSFO

# First we will check whether flashback is enabled or not on both primary and standby

# FastStartFailoverTarget property of broker should be set to a target standby terminal

# enable fast_start failover

# start the observer

__________________________________________________________________________

Lets check on primary database

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL> @role

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          READ WRITE

_____________________________________________

Lets check on standby database

SQL> @role

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY MOUNTED

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

DGMGRL> connect sys@prod
Password:
Connected.
DGMGRL> enable database sby1;
Enabled.
DGMGRL>
DGMGRL>
DGMGRL> show configuration

Configuration - dgconf1

  Protection Mode: MaxPerformance
  Databases:
    prod - Primary database
    sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>
DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    (none)

DGMGRL> enable fast_start failover condition '1578';
Succeeded.
DGMGRL>
DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    ORA-01578: ORACLE data block corrupted (file # %s, block # %s)


## We will simulate an insufficient privilege error and invoke fast start failover


DGMGRL> enable fast_start failover condition '1031';
Succeeded.
DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

  Oracle Error Conditions:
    ORA-01578: ORACLE data block corrupted (file # %s, block # %s)
    ORA-01031: insufficient privileges


DGMGRL> show database verbose prod

Database - prod

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    prod

  Properties:
    DGConnectIdentifier             = 'prod'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'node4.oracle.com'
    SidName                         = 'prod'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node4.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=prod_DGMGRL)(INSTANCE_NAME=prod)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL>
DGMGRL> edit database prod set property 'FastStartFailoverTarget' = 'sby1';
Property "FastStartFailoverTarget" updated

DGMGRL> enable fast_start failover ;
Enabled.

Now we will simulate the failure and invoke error number 1031 (insufficient privileges)

SQL> conn scott/s
Connected.
SQL> alter system set log_checkpoints_to_alert=true;
alter system set log_checkpoints_to_alert=true
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> /
alter system set log_checkpoints_to_alert=true
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> /
alter system set log_checkpoints_to_alert=true
*
ERROR at line 1:
ORA-01031: insufficient privileges


DGMGRL>
DGMGRL> start observer
Observer started

14:08:19.81  Sunday, November 29, 2015
Initiating Fast-Start Failover to database "sby1"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "sby1"
14:08:21.11  Sunday, November 29, 2015


DGMGRL> connect sys@sby1
Password:
Connected.
DGMGRL> stop observer
Error: ORA-16636: fast-start failover target standby in error state, cannot stop observer

Failed.

DGMGRL> disable fast_start failover;
Error: ORA-16652: fast-start failover target standby database is disabled

Failed.
DGMGRL> disable fast_start failover force;
Disabled.
DGMGRL> stop observer
Done.
DGMGRL>
DGMGRL> reinstate database prod;
Reinstating database "prod", please wait...
Operation requires shutdown of instance "prod" on database "prod"
Shutting down instance "prod"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "prod" on database "prod"
Starting instance "prod"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "prod" ...
Reinstatement of database "prod" succeeded


DGMGRL> show fast_start failover

Fast-Start Failover: DISABLED

  Threshold:        30 seconds
  Target:           (none)
  Observer:         (none)
  Lag Limit:        30 seconds
  Shutdown Primary: TRUE
  Auto-reinstate:   TRUE

Configurable Failover Conditions
  Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                 YES
    Datafile Offline               YES

  Oracle Error Conditions:
    ORA-01578: ORACLE data block corrupted (file # %s, block # %s)
    ORA-01031: insufficient privileges


You can give all oracle error numbers for fast start failover conditions except ora-600 and ora-7445

How to do a switchover in Data Guard with Data guard Broker

How to do a switchover with Data guard Broker

Below you can see i have primary database prod and standby database sby1, so in this demo we will switchover where standby (sby1) becomes primary and primary (prod) becomes standby

DGMGRL>  show configuration

Configuration - dgconf1

  Protection Mode: MaxPerformance
  Databases:
    prod - Primary database
    sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to sby1;
Performing switchover NOW, please wait...
New primary database "sby1" is opening...
Operation requires shutdown of instance "prod" on database "prod"
Shutting down instance "prod"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "prod" on database "prod"
Starting instance "prod"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "sby1"

DGMGRL>
DGMGRL>   show configuration

Configuration - dgconf1

  Protection Mode: MaxPerformance
  Databases:
    sby1 - Primary database
    prod - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

How to enable flashback in Standby database

How to enable flashback in Standby database

Below is the command to start MRP process

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

Below we get error because MRP is still active and applying the changes

SQL>  alter database flashback on;
 alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

Below is the command to stop the MRP process

SQL>  recover managed standby database cancel;
Media recovery complete.

So now we will be able to enable flashback

SQL>  alter database flashback on;

Database altered.

Below is the command to start the MRP process

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

Changing delaymins property of a standby through broker

Changing delaymins property of a standby through broker

Sometimes we dont want MRP process to apply the redo changes immediately to standby database, so in this case we have to use delaymins feature. Let us see how we can work with it :-

My standby database is sby1

DGMGRL> edit database sby1 set property 'DelayMins' = '2';

At primary

SQL> @role

DATABASE_ROLE    OPEN_MODE
---------------- --------------------
PRIMARY          READ WRITE

SQL> @logswitch

System altered.

### On standby

SQL> select archived,sequence#,applied,to_Char(completion_Time,'dd-mon-yyyy hh24:mi:ss') from v$archived_log
...
....
......

ARC  SEQUENCE# APPLIED   TO_CHAR(COMPLETION_T
--- ---------- --------- --------------------
YES         70 YES       29-nov-2015 10:02:19
YES         71 NO        29-nov-2015 10:02:36

## As we can see Applied is NO

## Now after we set delaymins to 0 then immediately MRP will apply the changes to standby

DGMGRL> edit database sby1 set property 'DelayMins' = '0';
Property "DelayMins" updated

## And now we will see applied column will have YES

SQL> @arch
..
.....
........
ARC  SEQUENCE# APPLIED   TO_CHAR(COMPLETION_T
--- ---------- --------- --------------------
YES         70 YES       29-nov-2015 10:02:19
YES         71 YES       29-nov-2015 10:02:36
YES         72 YES       29-nov-2015 10:02:55
YES         73 YES       29-nov-2015 10:06:06

## Finally we confirm and see all the properties with delaymins for our standby database sby1

DGMGRL> show database verbose sby1

Database - sby1

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    sby1

  Properties:
    DGConnectIdentifier             = 'sby1'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'prod, sby1'
    LogFileNameConvert              = 'prod, sby1'
    FastStartFailoverTarget         = ''
    StatusReport                    = '(monitor)'
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'node4.example.com'
    SidName                         = 'sby1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node4.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sby1_DGMGRL)(INSTANCE_NAME=sby1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

_Optimizer_ignore_hints can be changed at session level

I was recently asked by one of my clients about whether we can change the value of the initilization parameter _optimizer_ignore_hints at the session level or not. Well the answer is yes we can change the value of this parameter at session level. Here is a test of it on one of my instance running on 11.2.0.1.0 database version.

SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 28 22:38:30 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>  alter session set "_optimizer_ignore_hints" = TRUE ;

Session altered.

How to configure a physical standby in 11G

My primary database name will be PROD

Standby database name will be SBY1

Below are the contents of primary Pfile :-

[oracle@node4 u01]$ cat /u01/initprod.ora
prod.__db_cache_size=322961408
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
prod.__pga_aggregate_target=314572800
prod.__sga_target=524288000
prod.__shared_io_pool_size=0
prod.__shared_pool_size=176160768
prod.__streams_pool_size=8388608
*.archive_lag_target=0
*.audit_file_dest='/u01/app/oracle/admin/prod/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prod/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_unique_name='prod'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'
*.log_archive_config='dg_config=(prod,sby1)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)'
*.log_archive_dest_2='service=sby1 valid_for=(primary_role,online_logfiles) db_unique_name=sby1'
*.log_archive_dest_state_2='ENABLE'
prod.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.log_archive_min_succeed_dest=1
prod.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=314572800
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='prod'
*.sga_target=524288000
*.standby_file_management='auto'
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'


Below are the contents of Standby Pfile

[oracle@node4 u01]$ cat initsby1.ora
*.audit_file_dest='/u01/app/oracle/admin/sby1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/sby1/control01.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_domain=''
*.db_name='prod'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sby1XDB)'
*.open_cursors=300
*.pga_aggregate_target=314572800
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=524288000
*.undo_tablespace='UNDOTBS1'

log_archive_Config='dg_config=(prod,sby1)'
log_archive_Dest_2='service=prod valid_for=(primary_role,online_logfiles) db_unique_name=prod'

db_unique_name=sby1
service_names=sby1
standby_File_management=auto
db_file_name_convert='prod','sby1'
log_file_name_convert='prod','sby1'
_____________________________________

Make sure you create appropriate directories which are mentioned below :-

/u01/app/oracle/admin/sby1/adump
/u01/app/oracle/oradata/sby1

Start the standby instance in no mount

SQL> create spfile from pfile='/u01/initsby1.ora';

File created.

SQL> startup force nomount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2214936 bytes
Variable Size             159384552 bytes
Database Buffers          356515840 bytes
Redo Buffers                3821568 bytes

Below are network files entries for TNS and Listener :-

[oracle@node4 ~]$ 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.

PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node4.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = prod)
    )
  )

CAT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node4.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cat)
    )
  )

SBY1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node4.example.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = sby1)
    )
  )

[oracle@node4 ~]$ 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.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = sby1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = sby1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = prod)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = prod)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = sby1_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = sby1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = prod_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = prod)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node4.example.com)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle


Do the following on primary database RMAN Prompt :-

[oracle@node4 ~]$ rman target/

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Nov 28 22:14:23 2015

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

connected to target database: PROD (DBID=308370143)

RMAN> connect auxiliary sys@sby1

auxiliary database Password:
connected to auxiliary database: PROD (not mounted)

RMAN>  duplicate target database for standby  from active database;

Starting Duplicate Db at 28-NOV-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

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

Starting backup at 28-NOV-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
Finished backup at 28-NOV-15

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/sby1/control01.ctl';
}
executing Memory Script

Starting backup at 28-NOV-15
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_prod.f tag=TAG20151128T221444 RECID=4 STAMP=896998484
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-NOV-15

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/sby1/temo02.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/sby1/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/sby1/sysaux01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/sby1/undotbs01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/sby1/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/sby1/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/sby1/tbs1a.dbf";
   set newname for datafile  7 to
 "/u01/app/oracle/oradata/sby1/undo2.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u01/app/oracle/oradata/sby1/system01.dbf"   datafile
 2 auxiliary format
 "/u01/app/oracle/oradata/sby1/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u01/app/oracle/oradata/sby1/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u01/app/oracle/oradata/sby1/users01.dbf"   datafile
 5 auxiliary format
 "/u01/app/oracle/oradata/sby1/example01.dbf"   datafile
 6 auxiliary format
 "/u01/app/oracle/oradata/sby1/tbs1a.dbf"   datafile
 7 auxiliary format
 "/u01/app/oracle/oradata/sby1/undo2.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/sby1/temo02.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 28-NOV-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/prod/system01.dbf
output file name=/u01/app/oracle/oradata/sby1/system01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/prod/sysaux01.dbf
output file name=/u01/app/oracle/oradata/sby1/sysaux01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/prod/undotbs01.dbf
output file name=/u01/app/oracle/oradata/sby1/undotbs01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/prod/example01.dbf
output file name=/u01/app/oracle/oradata/sby1/example01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/app/oracle/oradata/prod/tbs1a.dbf
output file name=/u01/app/oracle/oradata/sby1/tbs1a.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/prod/undo2.dbf
output file name=/u01/app/oracle/oradata/sby1/undo2.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/prod/users01.dbf
output file name=/u01/app/oracle/oradata/sby1/users01.dbf tag=TAG20151128T221450
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-NOV-15

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=4 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=9 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/tbs1a.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=10 STAMP=896998540 file name=/u01/app/oracle/oradata/sby1/undo2.dbf
Finished Duplicate Db at 28-NOV-15

Add standby logfiles with below command :-

 alter database add standby logfile group &a ('/u01/app/oracle/oradata/sby1/&a') size 50m;

Make sure you add one extra log group on standby for standby logfiles

#Finally start the MRP Process

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

SQL> select archived,sequence#,applied,to_Char(completion_Time,'dd-mon-yyyy hh24:mi:ss') from v$archived_log

ARC  SEQUENCE# APPLIED   TO_CHAR(COMPLETION_T
--- ---------- --------- --------------------
YES         57 YES       28-nov-2015 22:17:27
YES         58 YES       28-nov-2015 22:17:28

How to configure a logical standby

First step is to configure a physical standby, below is the link of my blog post where you can see how to configure a physical standby :-

Configure a physical standby

After configuring the physical standby follow below simple steps to configure a logical standby :-

Do the following steps on standby database :-

SQL> select archived,sequence#,applied,to_Char(completion_Time,'dd-mon-yyyy hh24:mi:ss') from v$archived_log;
...
....
......

ARC  SEQUENCE# APPLIED   TO_CHAR(COMPLETION_T
--- ---------- --------- --------------------
YES         43 YES       28-nov-2015 21:15:26
YES         44 YES       28-nov-2015 21:15:28
YES         45 YES       28-nov-2015 21:36:11

Stop the MRP Process

SQL> recover managed standby database cancel;
Media recovery complete.

Do the below step on primary database

Build a Dictionary in the Redo Data

A LogMiner dictionary must be built into the redo data so that the LogMiner component of SQL Apply can properly interpret changes it sees in the redo. As part of building the LogMiner dictionary, supplemental logging is automatically set up to log primary key and unique-constraint/index columns. The supplemental logging information ensures each update contains enough information to logically identify each row that is modified by the statement.

To build the LogMiner dictionary, issue the following statement:

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.

Do the below steps on standby database

SQL> alter database recover to logical standby sby1;

Database altered.

Shutdown the standby instance and bring it to mount state

SQL> shu immediate
ORA-01507: database not mounted


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

Total System Global Area  521936896 bytes
Fixed Size                  2214936 bytes
Variable Size             159384552 bytes
Database Buffers          356515840 bytes
Redo Buffers                3821568 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> alter database start logical standby apply immediate;

Database altered.

SQL> desc v$logstdby_progress
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 APPLIED_SCN                                        NUMBER
 APPLIED_TIME                                       DATE
 RESTART_SCN                                        NUMBER
 RESTART_TIME                                       DATE
 LATEST_SCN                                         NUMBER
 LATEST_TIME                                        DATE
 MINING_SCN                                         NUMBER
 MINING_TIME                                        DATE
 RESETLOGS_ID                                       NUMBER

SQL> select APPLIED_SCN,LATEST_SCN from v$logstdby_progress;

APPLIED_SCN LATEST_SCN
----------- ----------
    1294315    1294373

My session at Sangam 15

Hello everyone, Sangam 15 was a great event with more than 800 participants at Hyderabad, There were some great sessions from Tim hall, Francisco Munoz Alvarez, Satyendra Kumar, Sai Ram, Kamran Agayev. My session at Sangam 15 was on SQL Plan Management and what is new in 12C. Below are some pictures which i am sharing from the event :-

Sangam 15

http://www.sangam15.com/

Sangam, the largest independent Oracle user’s group conference will feature over 90+ in-depth technical sessions presented by expert speakers from inside and outside of Oracle. Over 800+ database administrators, developers and architects professionals are expected at the conference from November 21st to 22nd 2015 at HICC in Hyderabad. This is our moment of glory and time for you to join us!

After the first year of registering as a user group and getting to understand the interests of Oracle user community in India, through technology days (TechDay) and technology nights (TechNight) in various cities around the country, we launched a user conference calling speakers from around the world to share their knowledge and experience with users in India. Our first conference was Sangam09 held in September 2009 in Bangalore. We invited Tom Kyte to provide three, half day seminar series. Along with the seminar by Tom Kyte, we also invited key speakers from inside and outside Oracle to do sessions for the remaining period of the day. This was the first event conducted by us at such a large scale. We had a lot to learn from the interests of attendees and how to reach out to those who could not attend. Our second conference Sangam10 was held in September 2010 in Hyderabad. We invited Jonathan Lewis to provide two, half day seminar series. Along with the seminar by Jonathan Lewis, we also invited key speakers from inside and outside Oracle to do sessions for the remaining period of the day. Based on a survey we conducted at Sangam09 and Sangam10, we discovered that Oracle users in India preferred to have this event towards the end of the week/weekend. So they could use their personal time to learn and educate themselves. Sangam11 which was hosted on Friday and Saturday was an outcome of this survey and request from our users. 

Our previous key speakers

Sangam14 - Tom Kyte, Maria Colgan, Jim Viscusi

Sangam13 – Arup Nanda

Sangam12 – Tom Kyte, Andrew Holdsworth, Graham Wood

Sangam11 – Arup Nanda

Sangam10 – Jonathan Lewis

Sangam09 – Tom Kyte 

Please have a look at our previous Sangam events:

Sangam14 – Day1, Day2, Day3

Sangam13 – Day1, Day2

Sangam12 – Day1, Day2

Sangam11 – Day1, Day2, Video1, video2

Sangam10 – Day1, Day2

Sangam09 – Day1, Day2, Day3

Agenda

As part of the survey in Sangam15, we asked attendees who they would like to listen to and learn from in 2014. Apart from key sessions, we had breakout sessions from various key speakers from around the world. We had a great response from several Oracle experts from around the world. To ensure that the papers selected match our user’s interests, we requested attendees from previous Sangam conference to help us with the selection process. Through a ranking system with 1 being the low and 10 being the high we requested our volunteers to rank the various abstracts and thus the papers with the highest scores were selected.

ORA-65093: multitenant container database not set up properly

Today when i was trying to mount my Far Sync Instance, i got the following error :-

SQL> startup mount
ORACLE instance started.

Total System Global Area  218103808 bytes
Fixed Size                  2922712 bytes
Variable Size             159385384 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes

ORA-65093: multitenant container database not set up properly

## This error is applicable for multitenant 12C Database option

## Lets see what this indicates

[oracle@node4 u01]$ oerr ora 65093
65093, 00000, "multitenant container database not set up properly"
// *Cause:  An attempt was made to open a multitenant container database without
//          the correct parameter set for a multitenant container database in
//          the initialization parameter file.
// *Action: Set the 'enable_pluggable_database=true' parameter for the
//          multitenant container database in the initialization parameter file
//          and restart the database.
//

## To resolve this we need to make sure that initialization parameter enable_pluggable_database   is set to true

SQL> show parameter enable_pluggable_database

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_pluggable_database            boolean     TRUE

## Then i was able to mount the Far Sync Instance

SQL>  startup mount
ORACLE instance started.

Total System Global Area  272629760 bytes
Fixed Size                  2923336 bytes
Variable Size             213910712 bytes
Database Buffers           50331648 bytes
Redo Buffers                5464064 bytes
Database mounted.