Dataguard Config

[jnrpeardba]

Hi Tim,

just a quick question regarding Dataguard.

I am about to set up production environment which will consist of 6 oracle instances for the Primary (different application feeds used for each instance).
When I setup the standby will I need to also create 6 instances on the standby? It's not really clear from the document I have just read on your site. I understand from
a one to one relationship this is quite simple, just not sure about multiple environments.

Many thanks

Jnrpeardba

[Tim...]

Hi.

Yes. Each instance on the primary server will need a separate standby instance on the standby server.

Cheers

Tim...

[jnrpeardba]

Hi Tim,

further to my initial query, can you clarify the best method to deploy Dataguard.

Do I

a) build 2 seperate environments on separate machines and then configure Dataguard

b) Build the primary, then perform the DG configuration, which will in fact then also create the standby

Also for example I have one instance on each box. My instance names are is db1 and and db2. Can I set up a unique name, referenced by Dataguard called db1_primary and db1_standby. I wanted to keep the same instance names on both machines, but somehow enable dataguard to use unique names,in the event of configuration and failover etc. This is my understanding of unique names - can you inform me if this is correct or incorrect.

Having read through the document, it appears there are a number of ways, but I was trying to find the simplest and easiest method.


Many thanks,

Jnrpeardba

[Tim...]

Hi.

1) You have the Oracle software installed on both machines in advance.
2) You have an Oracle database (the primary DB) running on one machine.
3) You do not have an Oracle database running on the second machine, as this is created during the dataguard setup process.

This is explained in the assumptions at the top of the article.

http://www.oracle-base.com/articles/11g ... -11gr2.php

Cheers

Tim...

[jnrpeardba]

OK thanks, will be trying out my first DG deployment later this week, using the attached document

[Tim...]

[jnrpeardba]

Hi Tim,

in the process of following the doc http://www.oracle-base.com/articles/11g/data-guard-setup-11gr2.php -
I'm hitting an error when I attempt to restore the database on the standby. Up until that point everything works and is completed methodically.

example

rman> restore database

starting restore at 02-Mar-13
using target .....


RMAN-03002: failure of restore command at 03/02/2013 17:05
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

When I list the backupset via rman I can see this files, including the datafiles numbered 1-4
eg

Code: Select all

Piece Name: /u01/app/oracle/flash_recovery_area/SIDPRD01/backupset/2013_03_01/o1_mf_nnndf_TAG2013031t172528_8mls499l_.bkp

I attempt to recover database but am met with the same error stating RMAN-6094: datafile 1 must be restored
When I issue RMAN> restore datafile 1; it attempts to restore then fails again with

Code: Select all

no backup or copy of datafile 1 found to restore
no backup or copy of datafile 2 found to restore
no backup or copy of datafile 3 found to restore
no backup or copy of datafile 4 found to restore


The init<stby> file is correct as via rman when I issue a 'startup mount' the database starts up fine. I copied the files from the primary and placed them in the suggested area as mentioned in the above doc

I even tried the following:

Code: Select all

RMAN>
{
set until time "to_date('01-03-2013 17:28:00', 'DD-MM-YYYY HH24:MI:SS')";
restore database
recover database
}


And still receiving the same error. I have a total of 7 files (tablespaces) and no complaints about the other 3. The four files it complains about are (system, sysuax,undotbs, users) - There is something I am fundamentally doing wrong, but cannot see what it is. The primary database is up and running fine. There are users, tbs it has been shutdown and started on a number of occasssions all working as should. I even had some users on there this morning testing and all seems fine. Its just the standby config giving me this restore issue. Even the standby listener starts correctly. So near yet ....

Any ideas would be most appreciated

regards

Jnrpeardba

[Tim...]

Hi.

If you are creating the standby from a backup, you have to make the backup files available on the standby server (copy or share). They need to be available using the same path that is used on the primary database.

In the article, the backupset files and archived redo log files are copied across to the standby server.

http://www.oracle-base.com/articles/11g ... _files_man

They are placed in the same location as used on the primary server. As a result, when they are needed by the RESTORE, they are found. I'm guessing you have not performed this step, or have altered the location of the files somewhat.

I'll make this a little clearer in the article.

Cheers

Tim...

[jnrpeardba]

hi Timt,

just for clarification. Do I copy the the backup files to a new backupset folder and the archivelog file to a new archivelog location on the standby - i.e a like for like?

many thanks

Jnrpeardba

[Tim...]

Hi.

If you look at what I do in the article is should be clear. I take the files from the primary database and put them in exactly the same location on the standby databases.

Cheers

Tim...

[jnrpeardba]

Hi Tim,

Many thanks for your feedback last night, was progressing nicely today until the final hurdle.
I managed to get past all of the phases, however on the last phase I receive this error - see below:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

Database altered.

SQL>
Database altered.

SQL>
Database altered.

SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
Session altered.

SQL>

SEQUENCE# FIRST_TIME NEXT_TIME APPLIED
---------- -------------------- -------------------- ---------
5 01-MAR-2013 12:25:28 01-MAR-2013 22:01:49 NO
6 01-MAR-2013 22:01:49 02-MAR-2013 10:08:51 NO
7 02-MAR-2013 10:08:51 03-MAR-2013 11:23:49 NO
8 03-MAR-2013 11:23:49 03-MAR-2013 16:00:04 NO
9 03-MAR-2013 16:00:04 03-MAR-2013 18:29:14 NO
10 03-MAR-2013 18:29:14 03-MAR-2013 18:30:16 NO
11 03-MAR-2013 18:30:16 03-MAR-2013 18:31:14 NO
12 03-MAR-2013 18:31:14 03-MAR-2013 18:32:15 NO

8 rows selected.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
--------------- ---------------- -----------------------------------------------
1 COGPRD01_STBY pipdlr3hdb02

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

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 939495424 bytes
Fixed Size 2233960 bytes
Variable Size 251660696 bytes
Database Buffers 679477248 bytes
Redo Buffers 6123520 bytes
Database mounted.
SQL> alter database open read only;
alter database open read only

ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u03/datafiles/cogprd01/system01.dbf'

SQL>

[oracle@pipdlr3hdb02 COGPRD01]$ ls -ltr /u03/datafiles/cogprd01/system01.dbf
-rw-rw----. 1 oracle oracle 744497152 Mar 3 19:21 /u03/datafiles/cogprd01/system01.dbf
[oracle@pipdlr3hdb02 COGPRD01]$ rman target=/

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 3 19:30:03 2013

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

connected to target database: COGPRD01 (DBID=935724546, not open)

RMAN> list backupset;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 133.51M DISK 00:00:03 03-MAR-13
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20130303T183115
Piece Name: /u01/app/oracle/fast_recovery_area/COGPRD01/backupset/2013_03_03/o1_mf_annnn_TAG20130303T183115_8m75qmwv_.bkp

List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 5 1043541 01-MAR-13 1069212 01-MAR-13
1 6 1069212 01-MAR-13 1103311 02-MAR-13
1 7 1103311 02-MAR-13 1138351 03-MAR-13
1 8 1138351 03-MAR-13 1155020 03-MAR-13
1 9 1155020 03-MAR-13 1178822 03-MAR-13
1 10 1178822 03-MAR-13 1179134 03-MAR-13
1 11 1179134 03-MAR-13 1179202 03-MAR-13

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1017.52M DISK 00:00:40 03-MAR-13
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20130303T183123
Piece Name: /u01/app/oracle/fast_recovery_area/COGPRD01/backupset/2013_03_03/o1_mf_nnndf_TAG20130303T183123_8m75qwl1_.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1179220 03-MAR-13 /u03/datafiles/cogprd01/system01.dbf
2 Full 1179220 03-MAR-13 /u03/datafiles/cogprd01/sysaux01.dbf
3 Full 1179220 03-MAR-13 /u03/datafiles/cogprd01/undotbs01.dbf
4 Full 1179220 03-MAR-13 /u03/datafiles/cogprd01/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 9.36M DISK 00:00:04 03-MAR-13
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20130303T183123
Piece Name: /u01/app/oracle/fast_recovery_area/COGPRD01/backupset/2013_03_03/o1_mf_ncsnf_TAG20130303T183123_8m75sdz4_.bkp
SPFILE Included: Modification time: 03-MAR-13
SPFILE db_unique_name: COGPRD01
Control File Included: Ckp SCN: 1179261 Ckp time: 03-MAR-13

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
4 30.00K DISK 00:00:00 03-MAR-13
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20130303T183215
Piece Name: /u01/app/oracle/fast_recovery_area/COGPRD01/backupset/2013_03_03/o1_mf_annnn_TAG20130303T183215_8m75sj40_.bkp

List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 12 1179202 03-MAR-13 1179273 03-MAR-13

RMAN>

Will I have to complete another restore or is there an easier way I can bring my standby up to completion?

May thanks and kind regards

Jnrpeardba

ps: the doc is fab by the way

[Tim...]

Hi.

Before you can do anything with the standby you have to make sure managed recovery has worked. You do this by checking the logs have shipped and been applied to the standby. From the query output you've displayed it appears no archived redo logs have been applied, so managed recovery has not completed. You need to investigate why this has not happened.

Cheers

Tim...

[jnrpeardba]

Hi Tim,
Latest developments

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


Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 939495424 bytes
Fixed Size 2233960 bytes
Variable Size 251660696 bytes
Database Buffers 679477248 bytes
Redo Buffers 6123520 bytes
Database mounted.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u03/datafiles/dsprd01/system01.dbf'

so I attempted to troubleshoot

on primary
-------------
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 34
Next log sequence to archive 36
Current log sequence 36
SQL>

[oracle@pipdl3rhdb01 archivelog]$ pwd
/u01/app/oracle/fast_recovery_area/DSPRD01/archivelog

[oracle@pipdl3rhdb01 archivelog]$ ls -ltr
total 20
drwxrwx---. 2 oracle oracle 4096 Mar 4 12:15 2013_03_01
drwxrwx---. 2 oracle oracle 4096 Mar 4 12:15 2013_03_02
drwxrwx---. 2 oracle oracle 4096 Mar 4 13:17 2013_03_03
drwxrwx---. 2 oracle oracle 4096 Mar 4 13:52 2013_03_04
drwxrwx---. 2 oracle oracle 4096 Mar 5 09:54 2013_03_05

# cd /u01/app/oracle/fast_recovery_area/DSPRD01/archivelog/2013_03_05/
# ls -ltr
total 24948
-rw-rw----. 1 oracle oracle 25317888 Mar 5 09:36 o1_mf_1_32_8mch47pv_.arc
-rw-rw----. 1 oracle oracle 2048 Mar 5 09:36 o1_mf_1_33_8mch48p1_.arc
-rw-rw----. 1 oracle oracle 217088 Mar 5 09:54 o1_mf_1_34_8mcj7bcg_.arc
-rw-rw----. 1 oracle oracle 1536 Mar 5 09:54 o1_mf_1_35_8mcj7bt0_.arc

SQL> select process, client_process, sequence#, status from V$managed_standby;

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 35 CLOSING
ARCH ARCH 34 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 34 OPENING
ARCH ARCH 35 OPENING
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

30 rows selected.

SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN 34 AND 35;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/DSPRD01/archivelog/2013_03_05/o1_mf_1_34_8mcj
/u01/app/oracle/fast_recovery_area/DSPRD01/archivelog/2013_03_05/o1_mf_1_35_8mcj

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1360507

on standby
------------

SQL> select process, client_process, sequence#, status from V$managed_standby;

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

30 rows selected.

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

SQL> select process, client_process, sequence#, status from V$managed_standby;

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED

30 rows selected.

SQL>

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1277680

[oracle@pipdlr3hdb02 archivelog]$ pwd
/u01/app/oracle/fast_recovery_area/DSPRD01/archivelog

[oracle@pipdlr3hdb02 archivelog]$ ls -ltr
total 20
drwxrwx---. 2 oracle oracle 4096 Mar 4 13:25 2013_03_04
drwxrwx---. 2 oracle oracle 4096 Mar 4 13:25 2013_03_03
drwxrwx---. 2 oracle oracle 4096 Mar 4 13:25 2013_03_01
drwxrwx---. 2 oracle oracle 4096 Mar 4 13:25 2013_03_02
drwxrwx---. 2 oracle oracle 4096 Mar 5 10:08 2013_03_05
[oracle@pipdlr3hdb02 archivelog]$ pwd


The alert log does not indicate why the log files are not being applied

a) is it possible for me to apply the log files from the standby directory in order to get the standby databse opened in read-only mode. If so how do I perform this?
b) Are there any parameters I can check on the standby which may be in-correct
c) When I issue a backup database plus archivelog on the primary - is there a command restore database plus archivelog for the standby

As mentioned I used the DG 11gR2 doc on this site - which I found easy to use and other than the last command it all worked without hiccup

I'm hoping not to have to recreate the standby database again as it's not a simple process and the fact that I have done this on six occassions
with the same result, appears something is going slightly wrong and needs / can be amended

Any pointers which may help will be most appreciated

These are brand new databases on the primary and no user has created any tables, so I am confused as to why the arc logs are not being applied.

Much appreciated again

Jnrpeardba

[Tim...]

Hi.

It would appear your backup is not suitable for the recovery. You really need to stat again, beginning with a new backup.

The process shouldn't be overly time consuming to repeat provided you have scripted all the steps as you have gone along. You aim should be that repeating this process is incredibly easy as you will no doubt have to manually recreate the standby database in future.

Cheers

Tim...

[jnrpeardba]

Hi Tim,

My 5 standby databases have now been created successfully using duplication to recreate the new standby databases
However only 2 of these database are shipping and applying logs successfully

On one of the problematic databases shows the following when I query:-

Code: Select all

select status, gap_status from v$archive_dest_status where dest_id = 2;

Code: Select all

SEQUENCE# FIRST_TIME           NEXT_TIME            APPLIED
---------- -------------------- -------------------- ---------
       549 19-MAR-2013 10:24:08 19-MAR-2013 10:24:09 NO
       550 19-MAR-2013 10:24:09 19-MAR-2013 10:25:31 NO
       551 19-MAR-2013 10:25:31 19-MAR-2013 14:25:12 NO

on the primary

Code: Select all

SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;

Code: Select all

STATUS    GAP_STATUS
--------- ------------------------
ERROR     RESOLVABLE GAP

q1. What does this mean with a status error

Code: Select all

select * from v$dataguard_status order by timestamp;

Code: Select all

FACILITY                 SEVERITY         DEST_ID MESSAGE_NUM ERROR_CODE CAL TIM
------------------------ ------------- ---------- ----------- ---------- --- ---
Log Transport Services   Error                  2        2861      12543 YES 19-
Log Transport Services   Error                  0        2862      12543 YES 19-
Log Transport Services   Error                  2        2863      12543 YES 19-

256 rows selected.

q2. what does the severity error mean when associated to Log Transport Services?

Any ideas or suggestions what I can do to remedy this?

Many thanks

Jnrpeardba