Create Physical Standby Database with real-time apply

Documentation can be found here

MANUAL CONFIGURATION

Source database is PRD

Configuration:Oracle restart

2 diskgroups, +DATA and +FRA

db_create_file_dest=+DATA

db_recovery_file_dest=+FRA

Network configuration

[STB] Listener.ora (under oracle account, not grid or you will get RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges)

SID_LIST_LISTENER_STB =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STB_DGMGRL.mydomain.fr)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = STB)
)
)

LISTENER_STB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.mydomain.fr)(PORT = 1521))
)
)
ADR_BASE_LISTENER_STB = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_STB = ON

[PRD] Listener.ora (oracle home should be the rdbms home, not grid or you will get ORA-01031: insufficient privileges)

SID_LIST_LISTENER_PRD =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRD_DGMGRL.mydomain.fr)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = PRD)
)
)

LISTENER_PRD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.mydomain.fr)(PORT = 1521))
)
)
ADR_BASE_LISTENER_PRD = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_PRD = ON

[STB & PRD] tnsnames.ora (under oracle home account)

PRD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.mydomain.fr)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRD_DGMGRL.mydomain.fr)
(SID = PRD)
(SERVER = DEDICATED)
)
)

STB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.mydomain.fr)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STB_DGMGRL.mydomain.fr)
(SID = STB)
(SERVER = DEDICATED)
)
)

Reload listeners lsnrctl reload

Create password file (same as PRD).

Create initSTB.ora file with at least db_name=’STB’

Create folder /u01/app/oracle/admin/STB/adump on standby server

Set parameters for primary

Check archive log ans set archivelog mode if not set.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14

If not set:

  • Shutdown immediate; (not abort or oracle will request media recovery when setting archivelog on)
  • startup mount;
  • alter database archivelog;
  • alter database open;

Set flashback on: alter database flashback on;

(set the required db_flashback_retention_target, default is 1440 minutes)

Set force logging on: alter database force logging;

Set standby_management_file to auto: alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

Create standby redologs (one group more that redo logs)

alter database add standby logfile thread 1 group 6 size 52428800, group 7 size 52428800, group 8 size 52428800, group 9 size 52428800;

 

Invoke rman

rman target sys/xxxxx@PRD auxiliary sys/xxxxx@STB

Recovery Manager: Release 11.2.0.3.0 – Production on Tue Jul 16 21:30:26 2013

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

connected to target database: PRD (DBID=1883658296)
connected to auxiliary database: STB (not mounted)

RMAN>run {
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database dorecover spfile
parameter_value_convert=’+DATA/PRD’,’+DATA/STB’
set db_unique_name = ‘STB’
set control_files=’+DATA’
set cluster_database = ‘false’
set audit_file_dest = ‘/u01/app/oracle/admin/STB/adump’
set db_create_file_dest = ‘+DATA’
set db_recovery_file_dest=’+FRA’
nofilenamecheck;
}

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/16/2013 22:00:56
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-03113: end-of-file on communication channel
Process ID: 933
Session ID: 135 Serial number: 3

in STB alert file:

Errors in file /u01/app/oracle/diag/rdbms/stb/STB/trace/STB_rbal_900.trc:
ORA-15183: ASMLIB initialization error [driver/agent not installed]
WARNING: FAILED to load library: /opt/oracle/extapi/32/asm/orcl/1/libasm.so
Errors in file /u01/app/oracle/diag/rdbms/stb/STB/trace/STB_rbal_900.trc:
ORA-15183: ASMLIB initialization error [driver/agent not installed]
Tue Jul 16 22:00:55 2013
SUCCESS: diskgroup DATA was mounted
Errors in file /u01/app/oracle/diag/rdbms/stb/STB/trace/STB_ora_933.trc (incident=7145):
ORA-00600: internal error code, arguments: [kfioTranslateIO03], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/stb/STB/incident/incdir_7145/STB_ora_933_i7145.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/stb/STB/trace/STB_ora_933.trc (incident=7146):
ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/stb/STB/incident/incdir_7146/STB_ora_933_i7146.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
ERROR: unrecoverable error ORA-600 raised in ASM I/O path; terminating process 933
Dumping diagnostic data in directory=[cdmp_20130716220056], requested by (instance=1, osid=933), summary=[incident=7145].

To fix:

as root user:
cd $ORACLE_HOME/bin
chgrp asmadmin oracle
chmod 6751 oracle

run {
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate auxiliary channel s1 type disk;
duplicate target database for standby from active database dorecover spfile
parameter_value_convert=’+DATA/PRD’,’+DATA/STB’
set db_unique_name = ‘STB’
set control_files=’+DATA’
set cluster_database = ‘false’
set audit_file_dest = ‘/u01/app/oracle/admin/STB/adump’
set db_create_file_dest = ‘+DATA’
set db_recovery_file_dest=’+FRA’
nofilenamecheck;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14>
using target database control file instead of recovery catalog
allocated channel: p1
channel p1: SID=34 device type=DISK

allocated channel: p2
channel p2: SID=30 device type=DISK

allocated channel: s1
channel s1: SID=10 device type=DISK

Starting Duplicate Db at 16-JUL-13

contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwPRD’ auxiliary format
‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSTB’ targetfile
‘+DATA/prd/spfileprd.ora’ auxiliary format
‘/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileSTB.ora’ ;
sql clone “alter system set spfile= ”/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileSTB.ora””;
}
executing Memory Script

Starting backup at 16-JUL-13
Finished backup at 16-JUL-13

sql statement: alter system set spfile= ”/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileSTB.ora”

contents of Memory Script:
{
sql clone “alter system set db_unique_name =
”STB” comment=
”” scope=spfile”;
sql clone “alter system set control_files =
”+DATA” comment=
”” scope=spfile”;
sql clone “alter system set cluster_database =
false comment=
”” scope=spfile”;
sql clone “alter system set audit_file_dest =
”/u01/app/oracle/admin/STB/adump” comment=
”” scope=spfile”;
sql clone “alter system set db_create_file_dest =
”+DATA” comment=
”” scope=spfile”;
sql clone “alter system set db_recovery_file_dest =
”+FRA” comment=
”” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_unique_name = ”STB” comment= ”” scope=spfile

sql statement: alter system set control_files = ”+DATA” comment= ”” scope=spfile

sql statement: alter system set cluster_database = false comment= ”” scope=spfile

sql statement: alter system set audit_file_dest = ”/u01/app/oracle/admin/STB/adump” comment= ”” scope=spfile

sql statement: alter system set db_create_file_dest = ”+DATA” comment= ”” scope=spfile

sql statement: alter system set db_recovery_file_dest = ”+FRA” comment= ”” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 849530880 bytes

Fixed Size 1348244 bytes
Variable Size 499125612 bytes
Database Buffers 343932928 bytes
Redo Buffers 5124096 bytes
allocated channel: s1
channel s1: SID=136 device type=DISK

contents of Memory Script:
{
sql clone “alter system set control_files =
”+DATA/stb/controlfile/current.256.820966029” comment=
”Set by RMAN” scope=spfile”;
backup as copy current controlfile for standby auxiliary format ‘+DATA/stb/controlfile/current.257.820966029’;
sql clone “alter system set control_files =
”+DATA/stb/controlfile/current.257.820966029” comment=
”Set by RMAN” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set control_files = ”+DATA/stb/controlfile/current.256.820966029” comment= ”Set by RMAN” scope=spfile

Starting backup at 16-JUL-13
channel p1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PRD.f tag=TAG20130716T220708 RECID=2 STAMP=820966029
channel p1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-JUL-13

sql statement: alter system set control_files = ”+DATA/stb/controlfile/current.257.820966029” comment= ”Set by RMAN” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 849530880 bytes

Fixed Size 1348244 bytes
Variable Size 499125612 bytes
Database Buffers 343932928 bytes
Redo Buffers 5124096 bytes
allocated channel: s1
channel s1: SID=134 device type=DISK

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 clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
datafile 5 auxiliary format new
;
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 16-JUL-13
channel p1: starting datafile copy
input datafile file number=00001 name=+DATA/prd/datafile/system.256.819994615
channel p2: starting datafile copy
input datafile file number=00002 name=+DATA/prd/datafile/sysaux.257.819994615
output file name=+DATA/stb/datafile/system.258.820966045 tag=TAG20130716T220724
channel p1: datafile copy complete, elapsed time: 00:00:07
channel p1: starting datafile copy
input datafile file number=00005 name=+DATA/prd/datafile/example.265.819994689
output file name=+DATA/stb/datafile/sysaux.259.820966045 tag=TAG20130716T220724
channel p2: datafile copy complete, elapsed time: 00:00:08
channel p2: starting datafile copy
input datafile file number=00003 name=+DATA/prd/datafile/undotbs1.258.819994615
output file name=+DATA/stb/datafile/undotbs1.261.820966055 tag=TAG20130716T220724
channel p2: datafile copy complete, elapsed time: 00:00:01
channel p2: starting datafile copy
input datafile file number=00004 name=+DATA/prd/datafile/users.259.819994615
output file name=+DATA/stb/datafile/users.262.820966055 tag=TAG20130716T220724
channel p2: datafile copy complete, elapsed time: 00:00:01
output file name=+DATA/stb/datafile/example.260.820966053 tag=TAG20130716T220724
channel p1: datafile copy complete, elapsed time: 00:00:06
Finished backup at 16-JUL-13

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like “+FRA/prd/archivelog/2013_07_16/thread_1_seq_15.278.820966059” auxiliary format
“+FRA” ;
catalog clone start with “+FRA”;
switch clone datafile all;
}
executing Memory Script

Starting backup at 16-JUL-13
channel p1: starting archived log copy
input archived log thread=1 sequence=15 RECID=13 STAMP=820966058
output file name=+FRA/stb/archivelog/2013_07_16/thread_1_seq_15.256.820966059 RECID=0 STAMP=0
channel p1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 16-JUL-13

searching for all files that match the pattern +FRA

List of Files Unknown to the Database
=====================================
File Name: +fra/STB/ARCHIVELOG/2013_07_16/thread_1_seq_15.256.820966059
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: +fra/STB/ARCHIVELOG/2013_07_16/thread_1_seq_15.256.820966059

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=820966060 file name=+DATA/stb/datafile/system.258.820966045
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=820966060 file name=+DATA/stb/datafile/sysaux.259.820966045
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=820966060 file name=+DATA/stb/datafile/undotbs1.261.820966055
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=820966060 file name=+DATA/stb/datafile/users.262.820966055
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=820966060 file name=+DATA/stb/datafile/example.260.820966053

contents of Memory Script:
{
set until scn 1134410;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 16-JUL-13

starting media recovery

archived log for thread 1 with sequence 15 is already on disk as file +FRA/stb/archivelog/2013_07_16/thread_1_seq_15.256.820966059
archived log file name=+FRA/stb/archivelog/2013_07_16/thread_1_seq_15.256.820966059 thread=1 sequence=15
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-JUL-13
Finished Duplicate Db at 16-JUL-13
released channel: p1
released channel: p2
released channel: s1

Setup broker configuration: documentation is here

On PRD and STB configure the broker.

[PRD]

  1. Change the configuration filenames for the database:

[STB]

  1. Change the configuration filenames for the database:

 

Specifying “ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1=+DATA;” is not supported with ASM you have to enter full name with path!

Note: do not forget to activate flashback feature here!

 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Put database in maxavailability

DGMGRL> edit configuration set protection mode as maxavailability;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Failed.

You need to modify some property before…
DGMGRL> edit database ‘PRD’ set property ‘LogXptMode’=SYNC;
Property “LogXptMode” updated
DGMGRL> edit database ‘STB’ set property ‘LogXptMode’=SYNC;
Property “LogXptMode” updated
DGMGRL> edit database ‘STB’ set property ‘NetTimeout’ = ’20’;
Property “NetTimeout” updated
DGMGRL> edit database ‘PRD’ set property ‘NetTimeout’ = ’20’;
Property “NetTimeout” updated
DGMGRL> edit configuration set protection mode as maxavailability;
Succeeded.
DGMGRL> show confguration;
DGMGRL> show configuration;

Configuration – MYPRRD

Protection Mode: MaxAvailability
Databases:
PRD – Primary database
STB – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

[PRD]
SQL> SELECT status, recovery_mode FROM v$archive_dest_status where dest_name = ‘LOG_ARCHIVE_DEST_2’;

STATUS RECOVERY_MODE
——— ———————–
VALID MANAGED REAL TIME APPLY

Done

Some good tips regarding dgmgrl command line here but think about that you should better focus on Oracle documentation here

Register STB database with Oracle restart, if applicable.

Move spfile to asm:

[oracle@standby ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Wed Jul 17 10:20:17 2013

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

connected to target database: PRD (DBID=1883658296, not open)

RMAN> backup spfile;

Starting backup at 17-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-JUL-13
channel ORA_DISK_1: finished piece 1 at 17-JUL-13
piece handle=+FRA/stb/backupset/2013_07_17/nnsnf0_tag20130717t102023_0.276.821010025 tag=TAG20130717T102023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-JUL-13

RMAN> restore spfile to ‘+DATA/STB/spfileSTB.ora’;

Starting restore at 17-JUL-13
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring SPFILE
output file name=+DATA/STB/spfileSTB.ora
channel ORA_DISK_1: reading from backup piece +FRA/stb/backupset/2013_07_17/nnsnf0_tag20130717t102023_0.276.821010025
channel ORA_DISK_1: piece handle=+FRA/stb/backupset/2013_07_17/nnsnf0_tag20130717t102023_0.276.821010025 tag=TAG20130717T102023
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-JUL-13

register database:

srvctl add database -d STB -o $ORACLE_HOME -r PHYSICAL_STANDBY -s mount -p +data/stb/spfileSTB.ora

Check database:
[oracle@standby ~]$ srvctl config database -d STB

Database unique name: STB
Database name:
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +data/stb/spfileSTB.ora
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Database instance: STB
Disk Groups:
Services:

Check if database is running
[oracle@standby ~]$ srvctl status database -d STB
Database is not running.

Not appear to be running from cluster ware point of view…

So we stop the database to start it with the grid infrastructure
[oracle@standby ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 17 10:24:55 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Now start the databsae
[oracle@standby ~]$ srvctl status database -d STB

Database is not running.
[oracle@standby ~]$ srvctl start database -d STB
PRCR-1079 : Failed to start resource ora.stb.db
CRS-5010: Update of configuration file “/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initSTB.ora” failed: details at “(:CLSN00014:)” in “/u01/app/grid/product/11.2.0/grid/log/standby/agent/ohasd/oraagent_grid/oraagent_grid.log”
CRS-5017: The resource action “ora.stb.db start” encountered the following error:
CRS-5010: Update of configuration file “/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initSTB.ora” failed: details at “(:CLSN00014:)” in “/u01/app/grid/product/11.2.0/grid/log/standby/agent/ohasd/oraagent_grid/oraagent_grid.log”
. For details refer to “(:CLSN00107:)” in “/u01/app/grid/product/11.2.0/grid/log/standby/agent/ohasd/oraagent_grid/oraagent_grid.log”.

CRS-2674: Start of ‘ora.stb.db’ on ‘standby’ failed

Solution:

Oracle restart must access and modify to the initSTB.ora file in $ORACLE_HOME/dbs

So: chmod 775 $ORACLE_HOME/dbs

[oracle@standby ~]$ srvctl start database -d STB

[oracle@standby ~]$ srvctl status database -d STB
Database is running.

Restart the two servers and everything show start automatically!

After reboot of both nodes,

 

[oracle@primary ~]$ dgmgrl sys/xxxxx@prd
DGMGRL for Linux: Version 11.2.0.3.0 – Production

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

Welcome to DGMGRL, type “help” for information.
Connected.
DGMGRL> show configuration;

Configuration – MYPRRD

Protection Mode: MaxAvailability
Databases:
PRD – Primary database
STB – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Done!

 

GUI using OEM CONFIGURATION

Here is the GUI version,

I have searched into the Oracle documentation but I was unable to find the location where the GUI process is full described…

So here it is…

Reminder: OS is OEL 5.4 x86, Oracle version is 11.2.0.3 x86 and Grid control version is 11.1.0.1.0

I will use an other machine “single” to setup a new standby for my PRD database using the GUI.

 

Here are my databases under my Grid Control

– Grid DB in Grid host
– JKO DB in single host
– PRD primary DB in primary host
– STB standby DB in standby host

grid1

 

Click on PRD database an go to Availability TABgrid2

In Data Gard section, select Setup and Manage

grid3

Click on “Add Standby Database”grid4

 

Nextgrid5

 

Nextgrid6

 

Enter standby Instance name, target host and OS credentialsgrid7

 

Enter ASM sys password when requested and select target diskgroup for your database.

Enter listener information (remember that there is listener using 1521 port under the grid infrastructure)

grid8

 

Enter DB unique name and target name (under OEM). Select SYSADM for monitoring purpose

grid9

 

Click Finishgrid10

 

grid12

Running…grid13

Running…grid14

 

Running…grid15

Boom! Failed! Why?grid16

Error while enabling flashback on!

grid17

Creation of the standby fail with error ORA-38706

The standby is running and is mounted, databroker is configured and the standby is reported as (disabled).

Tying to set flashback on give this result:

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38788: More standby database recovery is needed

So what’s the problem?

After a quick search, here is why…

Creating Standby Database With Enterprise Manager Failing [ID 1400482.1]
Symptoms
=== ODM Issue Clarification ===
Standby database creation getting error:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38788: More standby database recovery is needed
ORA-38706 signalled during: alter database flashback on…
Cause
Bug 12923814 FLASHBACK AND ARL DELETION OPTIONS IGNORED IN ADD STANDBY DATABASE WIZARD – this bug is unpublished and is fixed in 12g grid.
Solution
By default a standby database is created without DORECOVER, when the backup is online the files will be inconsistent. With a standby database, this is OK as managed recovery will take care of making files consistent. However, in order to turn ‘flashback ON’, the files must be consistent.
Thus, once it fails:
1. recover standby database applying enough redo to make files consistent.
2. cancel recovery and turn on flashback database
3. operate standby database normally

 

So I have done the workaround by getting all missing archives and applying them.
SQL> recover standby database until cancel;
ORA-00279: change 1576163 generated at needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1576163 for thread 1 is in sequence #171

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/tmp/thread_1_seq_172.431.821135379
ORA-00279: change 1576235 generated at 07/18/2013 21:09:39 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1576235 for thread 1 is in sequence #173
ORA-00278: log file ‘/tmp/thread_1_seq_172.431.821135379’ no longer needed for
this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/tmp/thread_1_seq_173.432.821137287
ORA-00279: change 1578645 generated at 07/18/2013 21:41:26 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1578645 for thread 1 is in sequence #174
ORA-00278: log file ‘/tmp/thread_1_seq_173.432.821137287’ no longer needed for
this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database flashback on;

Database altered.

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

Database altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@single admin]$

After having remove the ‘STY’ database from broker configuration then added again, now the configuration is ok.

From OEM point of view, now the configuration is OK too.

grid18

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The following two tabs change content below.

Jacques

I am Oracle Certified Master 11g & 12c database architect with significant experience in heterogeneous environments, and strong ability to lead complex and critical projects requiring multiple technical implementations. at Trivadis SA

5 thoughts on “Create Physical Standby Database with real-time apply

  1. Justiono

    This is great and helpful! By the way, creating physical standby using OEM, if your screen #8, why do you chose using listener from DB home and not using default listener in Grid Infrastructure? Did you also encounter problem with error “default listener is not running” (while it is actually running)?

    Reply
  2. Jacques Post author

    Hi Justino,

    In the test case, I have first created the standby manually. So the common mistake while setting up the listener was to set oracle_home to the grid home and not rdbms home…
    In this case you have insufficient privilege error during the duplicate.
    So in order to separate both listeners, I have decided to create a dedicated one.
    But you are right, normally we could use the default from grid.
    I will redo the test case in the following days and I will let you now if I am facing the bug you are referring to.
    Cheers,
    jko

    Reply
    1. Justiono

      It may not be a bug, Jacques. It may be just my mistake and I will redo from my end either. I’ll keep you posted.

      Reply
  3. AnnettJuicy

    Hello. I see that you don’t update your blog too often. I
    know that writing articles is boring and time consuming.

    But did you know that there is a tool that allows you
    to create new posts using existing content (from article directories or other pages from your niche)?
    And it does it very well. The new posts are unique and pass the copyscape test.
    You should try miftolo’s tools

    Reply
  4. KAYSWELL

    Thanks for your write-up. One other thing is that often individual American states have their own laws which affect home owners, which makes it extremely tough for the our elected representatives to come up with a whole new set of rules concerning foreclosures on homeowners. The problem is that a state has got own legal guidelines which may interact in an undesirable manner on the subject of foreclosure insurance policies.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *