Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Oracle10gR2 Data Guard Configuration and Setup in 11gR2ASM Env

Posted by Hendry chinnapparaj on May 17, 2010

 

Oracle10g Data Guard Configuration & Setup

 

1        Introduction

 

1.1     Purpose & Scope

This document is intended as a step by step Dataguard Configuration & Operation Guide between NEW CPSTEST Primary Server & NEW CPSTEST  Standby Servers.

1.2     Environment Details

  • OS:  IBM AIX 5.3 TL05
  • Hardware:  IBM P-Series 595 Model 
  • Oracle Software: Oracle 10gR2 Enterprise Edition -10204 (64bit) 
  • ASM Oracle 11Gr2 Instance
  • Primary Site: 1 Node (DEVU009N3) 
  • Standby Site:  1 Node (DEVU009N4) 
  • Database Name: CPSTEST
  • Instance Name: CPSTEST
  • Primary DB_UNIQUE_NAME: CPSTEST_J
  • Standby DB_UNIQUE_NAME: CPSTEST_F
  • Standby Type: Physical Standby

 

 

1.3     Definitions and Acronyms

Acronym Definitions
ASM Automatic Storage Management
RAC Oracle Real Application Cluster
OCR Oracle Cluster Registry
VIP Virtual IP Address

 

1.4     Audience

It is assumed that the intended audience has a general knowledge of database technology.

  • Database Administrators


2        Data Guard Configuration Steps

 

  • Preparing Primary database
  • Creating Standby Database
  • Post Installation Steps

 

3        Preparing the Primary Database

Before you create a standby database you must first ensure the primary database is properly configured.

3.1     Enable Force Logging

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

3.2     Creating Standby redo log files

SQL> SELECT GROUP#, THREAD#, MEMBERS FROM V$LOG;

  GROUP#    THREAD#    MEMBERS

———- ———- ———-

         1          1          2

         2          1          2

         3          1          2

         4          1          2

4 rows selected.

Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database.

(Number of logfile groups + 1)

Based on the above formula, We need to create 5 standby redo logfiles.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 SIZE 100M;

set lines 150

column member format a50

select * from v$logfile;

  GROUP# STATUS  TYPE    MEMBER                                             IS_RECOVERY_DEST_FILE

———- ——- ——- ————————————————– ———————

         4         ONLINE  +DATA/cpstest/onlinelog/group_4.1527.717704813     NO                  

         4         ONLINE  +DATA/cpstest/onlinelog/group_4.1528.717704815     YES                 

         3         ONLINE  +DATA/cpstest/onlinelog/group_3.1525.717704811     NO                  

         3         ONLINE  +DATA/cpstest/onlinelog/group_3.1526.717704813     YES                 

         2         ONLINE  +DATA/cpstest/onlinelog/group_2.1523.717704811     NO                   

         2         ONLINE  +DATA/cpstest/onlinelog/group_2.1524.717704811     YES                 

         1         ONLINE  +DATA/cpstest/onlinelog/group_1.1522.717704809     NO                  

         1         ONLINE  +DATA/cpstest/onlinelog/group_1.1521.717704809     YES                 

         5         STANDBY +DATA/cpstest_j/onlinelog/group_5.1589.718685357   NO                  

         5         STANDBY +DATA/cpstest_j/onlinelog/group_5.1590.718685357   YES                 

         6         STANDBY +DATA/cpstest_j/onlinelog/group_6.1591.718685471   NO                  

         6         STANDBY +DATA/cpstest_j/onlinelog/group_6.1592.718685471   YES                 

         7         STANDBY +DATA/cpstest_j/onlinelog/group_7.1593.718685473   NO                  

         7         STANDBY +DATA/cpstest_j/onlinelog/group_7.1594.718685473   YES                 

         8         STANDBY +DATA/cpstest_j/onlinelog/group_8.1595.718685475   NO                  

         8         STANDBY +DATA/cpstest_j/onlinelog/group_8.1596.718685477   YES                 

         9         STANDBY +DATA/cpstest_j/onlinelog/group_9.1597.718685549   NO                  

         9         STANDBY +DATA/cpstest_j/onlinelog/group_9.1598.718685551   YES                 

18 rows selected.

3.3     Database ASM Settings and Environment

On the primary

Creating extra controlfiles in ASM

This following procedure can be used to create additional controlfiles in the ASM diskgroup.

1. Create a new directory in your disk group using commands similar to the following ones (this is

optional).

alter diskgroup DATA add directory ‘+DATA/CPSTEST/MIRROR’;

or

ASMCMD> pwd

+data/cpstest

ASMCMD> ls

ARCHIVELOG/

BACKUPSET/

CONTROLFILE/

DATAFILE/

ONLINELOG/

TEMPFILE/

ASMCMD> mkdir MIRROR

ASMCMD> ls

ARCHIVELOG/

BACKUPSET/

CONTROLFILE/

DATAFILE/

MIRROR/

ONLINELOG/

TEMPFILE/

ASMCMD> pwd

+data/cpstest/mirror

alter diskgroup DATA add directory ‘+FRA/CPSTEST/MIRROR’;

2. Then, edit the control file entry in your SPFILE/init.ora to point to the new

controlfile. Where the new controlfile is +DATA/CPSTEST/MIRROR/control02.ctl’

*.control_files=’+DATA/control01.ctl’,’+DATA/cpstest/mirror/control02.ctl’

3. Shutdown cleanly your database (all instances if using RAC)

shutdown immediate;

4. Startup the database in nomount mode

startup nomount;

5. Using RMAN, restore the controlfile from one of the existing locations to the new one.

restore controlfile to ‘+DATA/CPSTEST/MIRROR/control02.ctl’ from

‘+DATA/control01.ctl’;

devu009n3 $ rman target /

Recovery Manager: Release 10.2.0.4.0 – Production on Fri May 7 15:06:57 2010

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

connected to target database: cpstest (not mounted)

RMAN> restore controlfile to ‘+DATA/cpstest/mirror/control02.ctl’ from ‘+DATA/control01.ctl’;

Starting restore at 07-MAY-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=541 devtype=DISK

channel ORA_DISK_1: copied control file copy

Finished restore at 07-MAY-10

6. Mount the database and recover the database if necessary.

SQL> alter database mount;

7. Open the database.

SQL> alter database open;

SQL> select name from v$controlfile;

NAME

——————————————————————————–

+DATA/control01.ctl

+DATA/cpstest/mirror/control02.ctl

ASMCMD> cd data

ASMCMD> ls

ASM/

CPSTEST/

CPSTEST_J/

DUPDB/

ASMCMD> pwd

+data/cpstest/CONTROLFILE

ASMCMD> ls -l

Type         Redund  Striped  Time             Sys  Name

CONTROLFILE  UNPROT  FINE     MAY 07 11:00:00  Y    Current.466.717610261

3.4     Set Primary Database init Parameters

On the primary database, you define initialization parameters that controls redo transport services while the database is in the primary role. There are additional parameters you need to add that control the receipt of the redo data and log apply services when the primary database is transitioned to the standby role.

alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(CPSTEST_J,CPSTEST_F)’;

alter system set LOG_ARCHIVE_DEST_1=’location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CPSTEST_J’;

alter system set LOG_ARCHIVE_DEST_2=’SERVICE=CPSTEST_F LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CPSTEST_F’;

alter system set STANDBY_FILE_MANAGEMENT=auto;

alter system set FAL_SERVER=CPSTEST_F;

alter system set FAL_CLIENT=CPSTEST_J;

SQL> alter system set local_listener=”(address=(protocol=TCP)(host=devu009n3)(port=1521))”;

System altered.

SQL> alter system set service_names=”CPSTEST_J”;

System altered.

3.5     Enable Database Flashback & Block Change Tracking

Steps:

Shutdown Database

Startup mount

alter database flashback on;

alter database open;

alter database enable block change tracking using file ‘/u01/oracle/product/10.2.0/db_1/dbs/cpstest_bct.dbf’;

Also check pwdfile

3.6     Backup Primary database

 

RMAN was not available at the time of the building of this configuration. The Install will reference the steps for a tape build but option 2 (Database copy) will be used.

Option 1:

RMAN> show all;

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘CF_%F’;

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE MAXSETSIZE TO UNLIMITED;

CONFIGURE ENCRYPTION FOR DATABASE OFF;

CONFIGURE ENCRYPTION ALGORITHM ‘AES128’;

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/oracle/backup_new/snapcf_cpstest_asm.f’;

Backup script

devu009n3 $ cat backup_full.rcv

run

{

allocate auxiliary channel c1 device type disk;

backup check logical incremental level 0 database filesperset 8 format ‘/u01/oracle/backup_new/cpstest_asm_db_%U’ force;

backup current Controlfile for standby format ‘/u01/oracle/backup_new/cf_cpstest_asm_t%t_s%s_p%p’;

sql ‘alter system archive log current’;

backup format ‘/u01/oracle/backup_new/cpstest_asm_log_%U’ archivelog all not backed up 1 times;

}

exit

devu009n3 $ nohup rman target / cmdfile=backup_full.rcv log=backup_full.log &

Issues with read only datafiles and the workaround done

—————————————————————–

devu009n3 $ tail -f backup_full.log

3> backup check logical incremental level 0 database filesperset 8 format ‘/u01/oracle/backup_new/cpstest_asm_db_%U’ force;

4> backup current Controlfile for standby format ‘/u01/oracle/backup_new/cf_cpstest_asm_t%t_s%s_p%p’;

5> sql ‘alter system archive log current’;

6> backup format ‘/u01/oracle/backup_new/cpstest_asm_log_%U’ archivelog all not backed up 1 times;

7> }

8>

Starting backup at 11-MAY-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=543 devtype=DISK

could not read file header for datafile 46 error reason 15

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 05/11/2010 03:18:43

RMAN-06056: could not access datafile 46

Recovery Manager complete.

[1] +  Done(1)                 nohup rman target / cmdfile=backup_full.rcv log=backup_full.log &

BUG:8276944 – ERROR RMAN-06056 BACKING UP DUPLICATE DATABASE WITH READ ONLY

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME

FROM V$DATAFILE_HEADER

WHERE RECOVER = ‘YES’

OR (RECOVER IS NULL AND ERROR IS NOT NULL);

BUG:6494833 – “OLD CONTROLFILE” VALUE IN ERROR COLUMN OF V$DATAFILE_HEADER

select * from v$backup where file# between 46 and 51;

  FILE# STATUS                CHANGE# TIME    

———- —————— ———- ———

        46 UNKNOWN ERROR               0         

        47 UNKNOWN ERROR               0         

        48 UNKNOWN ERROR               0         

        49 UNKNOWN ERROR               0         

        50 UNKNOWN ERROR               0         

        51 UNKNOWN ERROR               0         

6 rows selected.

alter tablespace cb001 read write;

alter tablespace cb002 read write;

alter tablespace cb003 read write;

alter system switch logfile;

alter tablespace cb001 read only;

alter tablespace cb002 read only;

alter tablespace cb003 read only;

alter system switch logfile;

select * from v$backup where file# between 46 and 51;

  FILE# STATUS                CHANGE# TIME    

———- —————— ———- ———

        46 NOT ACTIVE                  0         

        47 NOT ACTIVE                  0         

        48 NOT ACTIVE                  0         

        49 NOT ACTIVE                  0         

        50 NOT ACTIVE                  0         

        51 NOT ACTIVE                  0         

6 rows selected.

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME

FROM V$DATAFILE_HEADER

WHERE RECOVER = ‘YES’

OR (RECOVER IS NULL AND ERROR IS NOT NULL);

no rows selected.

After the workaround, the backup runs ok

Recovery Manager: Release 10.2.0.4.0 – Production on Tue May 11 07:26:52 2010

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

connected to target database: CPSTEST (DBID=249988753)

RMAN> run

2> {

3> backup check logical incremental level 0 database filesperset 8 format ‘/u01/oracle/backup_new/cpstest_asm_db_%U’ force;

4> backup current Controlfile for standby format ‘/u01/oracle/backup_new/cf_cpstest_asm_t%t_s%s_p%p’;

5> sql ‘alter system archive log current’;

6> backup format ‘/u01/oracle/backup_new/cpstest_asm_log_%U’ archivelog all not backed up 1 times;

7> }

8>

Starting backup at 11-MAY-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=518 devtype=DISK

channel ORA_DISK_1: starting incremental level 0 datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00013 name=+DATA/cpstest/datafile/cps_lm_data_8m.1344.717704285

input datafile fno=00010 name=+DATA/cpstest/datafile/cps_data.1345.717704285

input datafile fno=00026 name=+DATA/cpstest/datafile/aut_lm_data_512k.1346.717704285

input datafile fno=00027 name=+DATA/cpstest/datafile/aut_lm_data_1m.1347.717704287

input datafile fno=00035 name=+DATA/cpstest/datafile/bwa_lm_data_512k.1348.717704287

input datafile fno=00044 name=+DATA/cpstest/datafile/cps_lob_lm_640m.1349.71770

 

3.7     Create the TNS for Redo Shipping

 

Add the TNS Alias in tnsnames.ora for shipping the log to standby site.  You need to add the entry in both the nodes of Primary database for the tnsnames.ora file.

3.7.1     Listener.ora

devu009n3 $ pwd

/u01/oracle/product/11.2.0/grid/network/admin

devu009n3 $ mv tnsnames.ora tnsnames.ora_hd

devu009n3 $ pwd

/u01/oracle/product/11.2.0/grid/network/admin

devu009n3 $ ls -lrt

total 80

-rw-r–r–    1 oracle   dba             211 Apr 28 10:37 sqlnet.ora

-rw-r—–    1 oracle   dba             327 Apr 29 17:04 tnsnames.ora_hd

-rw-r–r–    1 oracle   dba             613 May 12 14:09 listener.ora

devu009n3 $ cat listener.ora

# listener.ora Network Configuration File: /u01/oracle/product/11.2.0/grid/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

SID_LIST_LISTENER =

 (SID_LIST =

   (SID_DESC =

     (GLOBAL_DBNAME = cpstest)

     (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)

     (SID_NAME = cpstest)

    )

   )

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3.bankwest.com)(PORT = 1521))

    )

  )

 

ADR_BASE_LISTENER = /u01/oracle

 

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

 

 

3.7.2     Tnsnames.ora

devu009n3 $ pwd

/u01/oracle/product/10.2.0/db_1/network/admin

devu009n3 $ mv listener.ora_hendry listener.ora_hd

devu009n3 $ ls -lrt

total 40

-rw-r—–    1 oracle   dba             512 Apr 28 17:48 listener.ora_hd

-rw-r—–    1 oracle   dba             703 May 12 13:52 sqlnet.log

-rw-r—–    1 oracle   dba             872 May 12 13:53 tnsnames.ora

devu009n3 $ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

CPSTEST_J =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = CPSTEST)

    )

  )

CPSTEST_F =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = CPSTEST)

    )

  )

CPSTEST_SOURCE =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = cpstest)

    )

  )

ASM =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = +ASM)

    )

  )

Prepare the Standby(1) Database

 

3.8     Set Standby Database Init Parameters

 

alter system set DB_UNIQUE_NAME=’CPSTEST_F’ scope=spfile;

remount standby database

alter system set log_archive_dest_state_2=’defer’ scope=both;

alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(CPSTEST_F,CPSTEST_J)’;

alter system set LOG_ARCHIVE_DEST_1=’location=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CPSTEST_F’;

alter system set LOG_ARCHIVE_DEST_2=’SERVICE=CPSTEST_J LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CPSTEST_J;

alter system set STANDBY_FILE_MANAGEMENT=auto;

alter system set FAL_SERVER=CPSTEST_J;

alter system set FAL_CLIENT=CPSTEST_F;

alter system set local_listener=”(address=(protocol=TCP) (host= devu009n2) (port=1521))” scope=both;

alter system set service_names=”CPSTEST_F” scope=both;

The initcpstest.ora file at standby server

3.9     Create the TNS for Redo Shipping

 

Add the TNS Alias in tnsnames.ora for shipping the log to standby site.  You need add the entry in both the nodes of Standby database.

File Name: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora

3.9.1     Listener.ora

devu009n4 $ pwd

/u01/oracle/product/11.2.0/grid/network/admin

devu009n4 $ mv tnsnames.ora tnsnames.ora_hd

devu009n4 $ ls -lrt

total 40

-rw-r–r–    1 oracle   dba             211 Apr 28 09:28 sqlnet.ora

-rw-r–r–    1 oracle   dba             620 May 12 08:55 listener.ora

-rw-r–r–    1 oracle   dba             512 May 12 08:58 tnsnames.ora_hd

devu009n4 $ cat listener.ora

# listener.ora Network Configuration File: /u01/oracle/product/11.2.0/grid/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = cpstest)

      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)

      (SID_NAME = cpstest)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4.bankwest.com)(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u01/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

3.9.2     Tnsnames.ora

 

devu009n4 $ pwd

/u01/oracle/product/10.2.0/db_1/network/admin

devu009n4 $ ls -lrt

total 16

-rw-r–r–    1 oracle   dba             686 May 12 14:18 tnsnames.ora

devu009n4 $ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

CPSTEST_J =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = cpstest)

    )

  )

CPSTEST_F =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = cpstest)

    )

  )

ASM =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = +ASM)

    )

  )

3.10    Restore the backup on standby

 

Firstly ensure that the steps in Section 3.5 have been completed. Without a recent standby controlfile on tape the duplicate for standby command will go back to any previous copy available, and this may necessitate many more archive logs to be restored than intended.

Copy the backup to disk  thru sftp

devu009n4 $ pwd

/u01/oracle/backup_new

devu009n4 $ sftp oracle@devu009n3

Connecting to devu009n3…

The authenticity of host ‘devu009n3 (10.254.203.118)’ can’t be established.

RSA key fingerprint is bf:fa:07:36:0f:80:94:99:15:a8:34:f2:8a:7c:88:df.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added ‘devu009n3,10.254.203.118’ (RSA) to the list of known hosts.

oracle@devu009n3’s password:

sftp> pwd

Remote working directory: /u01/oracle

sftp> cd /u01/oracle/backup_new

sftp> pwd

Remote working directory: /u01/oracle/backup_new

sftp> mget *

Fetching /u01/oracle/backup_new/cf_cpstest_asm_t718702167_s27_p1 to cf_cpstest_asm_t718702167_s27_p1

/u01/oracle/backup_new/cf_cpstest_asm_t718702167_s27_p1                     100%   10MB  10.4MB/s   00:01

Fetching /u01/oracle/backup_new/cpstest_asm_db_03ldd1e3_1_

nohup rman target sys/cpstest@CPSTEST_J auxiliary / cmdfile=res.rcv log=res.log &

 

devu009n4 $ cat res.rcv

run

{

allocate auxiliary channel c2 DEVICE TYPE DISK;

duplicate target database for standby nofilenamecheck;

}

Exit

 

 

3.11    Creating Standby Log files

 

Query the datafiles to ensure they are valid

set pages 1000 lines 150

column name format a50

select file#,name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

set pages 1000 lines 150 num 777777777777

column name format a50

select file#,name,status,checkpoint_change# from v$datafile where file# between 46 and 51;

SQL> column member format a50

SQL> l

  1* SELECT * FROM V$LOGFILE

SQL> /

    GROUP# STATUS  TYPE    MEMBER                                             IS_

———- ——- ——- ————————————————– —

         4 INVALID ONLINE  +DATA                                              NO

         4 INVALID ONLINE  +DATA                                              YES

         3 INVALID ONLINE  +DATA                                              NO

         3 INVALID ONLINE  +DATA                                              YES

         2 INVALID ONLINE  +DATA                                              NO

         2 INVALID ONLINE  +DATA                                              YES

         1 INVALID ONLINE  +DATA                                              NO

         1 INVALID ONLINE  +DATA                                              YES

         5 INVALID STANDBY +DATA                                              NO

         5 INVALID STANDBY +DATA                                              YES

         6 INVALID STANDBY +DATA                                              NO

         6 INVALID STANDBY +DATA                                              YES

         7 INVALID STANDBY +DATA                                              NO

         7 INVALID STANDBY +DATA                                              YES

         8 INVALID STANDBY +DATA                                              NO

         8 INVALID STANDBY +DATA                                              YES

         9 INVALID STANDBY +DATA                                              NO

         9 INVALID STANDBY +DATA                                              YES

18 rows selected.

alter system set standby_file_management=manual;

alter database drop standby logfile group 5;

alter database drop standby logfile group 6;

alter database drop standby logfile group 7;

alter database drop standby logfile group 8;

alter database drop standby logfile group 9;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 9 SIZE 100M;

alter system set standby_file_management=auto;

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_

———- ——- ——- ————————————————– —

         4 INVALID ONLINE  +DATA                                              NO

         4 INVALID ONLINE  +DATA                                              YES

         3 INVALID ONLINE  +DATA                                              NO

         3 INVALID ONLINE  +DATA                                              YES

         2 INVALID ONLINE  +DATA                                              NO

         2 INVALID ONLINE  +DATA                                              YES

         1 INVALID ONLINE  +DATA                                              NO

         1 INVALID ONLINE  +DATA                                              YES

         5         STANDBY +DATA/cpstest_f/onlinelog/group_5.454.718807697    NO

         5         STANDBY +DATA/cpstest_f/onlinelog/group_5.455.718807699    YES

         6         STANDBY +DATA/cpstest_f/onlinelog/group_6.456.718807767    NO

         6         STANDBY +DATA/cpstest_f/onlinelog/group_6.457.718807767    YES

         7         STANDBY +DATA/cpstest_f/onlinelog/group_7.458.718807773    NO

         7         STANDBY +DATA/cpstest_f/onlinelog/group_7.459.718807773    YES

         8         STANDBY +DATA/cpstest_f/onlinelog/group_8.460.718807785    NO

         8         STANDBY +DATA/cpstest_f/onlinelog/group_8.461.718807785    YES

         9         STANDBY +DATA/cpstest_f/onlinelog/group_9.462.718807793    NO

         9         STANDBY +DATA/cpstest_f/onlinelog/group_9.463.718807795    YES

18 rows selected.

This will create logfiles in both the +FRA and +DATA diskgroups. Only one is required in +FRA so the +DATA logfiles can be removed. The below SQL will provide the syntax required for this:

SQL> select ‘alter database drop standby logfile member ”’ || member || ”’;’ from v$logfile

where type = ‘STANDBY’

and member like ‘+DATA%’;

SQL> select ‘alter database drop standby logfile member ”’ || member || ”’;’ from v$logfile

where type = ‘STANDBY’

and member like ‘+DATA%’;

  2    3

‘ALTERDATABASEDROPSTANDBYLOGFILEMEMBER”’||MEMBER||”’;’

——————————————————————————————————————————————————

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_5.463.718808265’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_5.461.718808267’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_6.459.718808267’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_6.457.718808267’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_7.455.718808269’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_7.462.718808269’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_8.460.718808271’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_8.458.718808271’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_9.456.718808271’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_9.454.718808273’;

10 rows selected.

Select those files where IS_RECOVERY_DEST_FILE=yes and drop them as it’s not required.

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_5.461.718808267’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_6.457.718808267’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_7.462.718808269’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_8.458.718808271’;

alter database drop standby logfile member ‘+DATA/cpstest_f/onlinelog/group_9.454.718808273’;

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_

———- ——- ——- ————————————————– —

         4 INVALID ONLINE  +DATA                                              NO

         4 INVALID ONLINE  +DATA                                              YES

         3 INVALID ONLINE  +DATA                                              NO

         3 INVALID ONLINE  +DATA                                              YES

         2 INVALID ONLINE  +DATA                                              NO

         2 INVALID ONLINE  +DATA                                              YES

         1 INVALID ONLINE  +DATA                                              NO

         1 INVALID ONLINE  +DATA                                              YES

         5         STANDBY +DATA/cpstest_f/onlinelog/group_5.463.718808265    NO

         6         STANDBY +DATA/cpstest_f/onlinelog/group_6.459.718808267    NO

         7         STANDBY +DATA/cpstest_f/onlinelog/group_7.455.718808269    NO

         8         STANDBY +DATA/cpstest_f/onlinelog/group_8.460.718808271    NO

         9         STANDBY +DATA/cpstest_f/onlinelog/group_9.456.718808271    NO

13 rows selected.

3.12    Enable Database Flashback & Block Change Tracking on standby

Set ORACLE_SID=CPSTEST

sqlplus / as sysdba

alter database flashback on;

alter database enable block change tracking using file ‘/u01/oracle/product/10.2.0/db_1/dbs/cpstest_bct.dbf’;


4        Post Installation Steps

4.1     Upgrade the Protection mode

By default database will be running in MAXIMIZE PERFORMANCE mode, upgrade the protection mode to MAXIMIZE AVAILABILITY.

On primary database perform the following activities.

SQL> select NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL

——— ———- ——————– ——————–

CPSTEST   READ WRITE MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Database altered.

SQL> select NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL from v$database;

NAME      OPEN_MODE  PROTECTION_MODE      PROTECTION_LEVEL

——— ———- ——————– ——————–

CPSTEST   READ WRITE MAXIMUM AVAILABILITY RESYNCHRONIZATION

4.2     Start Recovery

On standby database perform the following activities.

RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Also note that this will also autocreate the online redo log groups as below

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                             IS_

———- ——- ——- ————————————————– —

         4         ONLINE  +DATA/cpstest_f/onlinelog/group_4.466.718809089    NO

         4         ONLINE  +DATA/cpstest_f/onlinelog/group_4.467.718809089    YES

         3         ONLINE  +DATA/cpstest_f/onlinelog/group_3.464.718809087    NO

         3         ONLINE  +DATA/cpstest_f/onlinelog/group_3.465.718809087    YES

         2         ONLINE  +DATA/cpstest_f/onlinelog/group_2.457.718809085    NO

         2         ONLINE  +DATA/cpstest_f/onlinelog/group_2.461.718809085    YES

         1         ONLINE  +DATA/cpstest_f/onlinelog/group_1.458.718809083    NO

         1         ONLINE  +DATA/cpstest_f/onlinelog/group_1.462.718809085    YES

         5         STANDBY +DATA/cpstest_f/onlinelog/group_5.463.718808265    NO

         6         STANDBY +DATA/cpstest_f/onlinelog/group_6.459.718808267    NO

         7         STANDBY +DATA/cpstest_f/onlinelog/group_7.455.718808269    NO

         8         STANDBY +DATA/cpstest_f/onlinelog/group_8.460.718808271    NO

         9         STANDBY +DATA/cpstest_f/onlinelog/group_9.456.718808271    NO

13 rows selected.

You can move the logfile members to different disk group as mirroring and also fro improving performance.

4.3     Verify Log shipping & applying

Run the following SQL on Primary database to verify the log shipping & applying.

SQL> alter system set log_archive_dest_state_2=’enable’ scope=memory;

System altered.

SQL> select dest_id, status from v$archive_dest;

   DEST_ID STATUS

———- ———

         1 VALID

         2 VALID

         3 INACTIVE

         4 INACTIVE

         5 INACTIVE

         6 INACTIVE

         7 INACTIVE

         8 INACTIVE

         9 INACTIVE

        10 INACTIVE

10 rows selected.

Run the query below in primary database

select thread#, sequence#, archived, applied from v$archived_log where dest_id=2 and sequence# in ( select max(sequence#) from v$archived_log);

  THREAD#  SEQUENCE# ARC APP

———- ———- — —

1                   71 YES YES

Archive log list

Archived=YES indicates that latest log has been shipped to standby site and Applied=YES indicates that latest log has been applied as well.

Primary check

select

LOG_ARCHIVED_ON_PRIMARY, LOG_APPLIED_ON_STANDBY,  LOG_ARCHIVED_ON_PRIMARY-LOG_APPLIED_ON_STANDBY LOG_GAP

from

(select sequence# LOG_ARCHIVED_ON_PRIMARY from v$archived_log where dest_id=1 and archived=’YES’ and completion_time=(select max(completion_time) from v$archived_log where dest_id=1)) primary,

(select sequence# LOG_APPLIED_ON_STANDBY  from v$archived_log where dest_id=2 and applied=’YES’ and completion_time=(select max(completion_time) from v$archived_log where dest_id=2)) standby;

select * from v$archive_dest_Status;

SELECT THREAD# “Thread”,SEQUENCE# “Last Sequence Generated”

FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)

ORDER BY 1;

Stby check

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference”

  FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

select * from v$archive_gap;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: