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

Archive for May, 2010

RMAN Delete failures due to Mismatched status RMAN-06207,RMAN-06214

Posted by Hendry chinnapparaj on May 31, 2010

Problem

————

The Delete obsolete command could not delete some files due to mismatched status.

RMAN> delete obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to recovery window of 30 days

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=760 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.4.1.0

using channel ORA_DISK_1

Deleting the following obsolete backups and copies:

Type                 Key    Completion Time    Filename/Handle

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

Backup Set           16778666 30-04-2010 10:21:19

Backup Piece       16778668 30-04-2010 10:21:19 CF_c-183511008-20100430-04

Backup Set           16779716 30-04-2010 12:27:07

Do you really want to delete the above objects (enter YES or NO)? yes

RMAN-06207: WARNING: 13 objects could not be deleted for DISK channel(s) due

RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status

RMAN-06210: List of Mismatched objects

RMAN-06211: ==========================

RMAN-06212:   Object Type   Filename/Handle

RMAN-06213: ————— —————————————————

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091130-00

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091130-01

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091130-02

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091208-01

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091218-00

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091218-01

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091218-02

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091218-03

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100112-00

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100112-01

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100112-02

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100112-03

RMAN-06214: Backup Piece    /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100112-04

Solution

———–

Do a crosscheck of the backuppiece which has the issue a

RMAN> crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091130-00’;

using channel ORA_SBT_TAPE_1

using channel ORA_DISK_1

crosschecked backup piece: found to be ‘EXPIRED’

backup piece handle=/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091130-00 recid=18616 stamp=704280485

Crosschecked 1 objects

crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091130-01’;

crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091130-02’;

crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091208-01’;

crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091218-00’;

crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091218-01’;

crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091218-02’;

crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091218-03’;

crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100112-00’;

crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100112-01’;

crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100112-02’;

crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100112-03’;

crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100112-04’;

crosscheck backuppiece ”;

crosscheck backuppiece ”;

After crosscheck, run the delete obsolete again

RMAN> delete obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to recovery window of 30 days

using channel ORA_SBT_TAPE_1

using channel ORA_DISK_1

Deleting the following obsolete backups and copies:

Type                 Key    Completion Time    Filename/Handle

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

Backup Set           16795362 01-05-2010 10:21:30

Backup Piece       16795364 01-05-2010 10:21:30 CF_c-183511008-20100501-05

Backup Set           17277715 30-11-2009 09:28:06

Backup Piece       17277743 30-11-2009 09:28:06 /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091130-00

Backup Set           17277716 30-11-2009 09:29:02

backup piece handle=/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091130-00 recid=18616 stamp=704280485

deleted backup piece

backup piece handle=/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091130-01 recid=18617 stamp=704280541

deleted backup piece

backup piece handle=/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20091130-02 recid=18618 stamp=704280591

deleted backup

RMAN> delete obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to recovery window of 30 days

using channel ORA_SBT_TAPE_1

using channel ORA_DISK_1

no obsolete backups found

other commands to delete are

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

RMAN> crosscheck backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100511-49’;

allocated channel: ORA_SBT_TAPE_1

channel ORA_SBT_TAPE_1: sid=689 devtype=SBT_TAPE

channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.4.1.0

using channel ORA_DISK_1

crosschecked backup piece: found to be ‘AVAILABLE’

backup piece handle=/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100511-49 recid=25248 stamp=718721036

Crosschecked 1 objects

RMAN> delete force backuppiece ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100511-49’;

using channel ORA_SBT_TAPE_1

using channel ORA_DISK_1

List of Backup Pieces

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

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

16971260 16971238 1   1   AVAILABLE   DISK        /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100511-49

Do you really want to delete the above objects (enter YES or NO)? yes

deleted backup piece

backup piece handle=/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100511-49 recid=25248 stamp=718721036

Deleted 1 objects

RMAN> delete noprompt backuppiece       ‘/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100511-08’;

using channel ORA_SBT_TAPE_1
using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
——- ——- — — ———– ———– ———-
16970426 16970385 1   1   AVAILABLE   DISK        /u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100511-08
deleted backup piece
backup piece handle=/u01/oracle/product/10.2/db_1/dbs/CF_c-183511008-20100511-08 recid=25182 stamp=718719698
Deleted 1 objects

Posted in RMAN10G | Leave a Comment »

ORA-16628: the broker protection mode is inconsistent with the database setting

Posted by Hendry chinnapparaj on May 24, 2010

Problem and Diagnosis

————————–

ORA-16628: the broker protection mode is inconsistent with the database setting

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

Warning: ORA-16607: one or more databases have failed

Cd /u01/oracle/product/10.2.0/db_1/admin/cpstest/bdump

Cat drccpstest.log

DG 2010-05-24-10:13:03        0 2 719363232 Operation CTL_GET_STATUS cancelled during phase 2, error = ORA-16628

DG 2010-05-24-10:14:03        0 2 0 RSM Error: protection mode ‘MaxPerformance’ set in Broker metadata is inconsistent with the actual protection mode ‘MAXIMUM AVAILABILITY’ currently set in database

DG 2010-05-24-10:14:03        0 2 0 RSM0: HEALTH CHECK ERROR: ORA-16628: the broker protection mode is inconsistent with the database setting

DGMGRL> show database verbose cpstest_f

Database

Name:            cpstest_f

Current status for “cpstest_f”:

Error: ORA-16628: the broker protection mode is inconsistent with the database setting

Cause

——–

You have changed the protection_mode to maximum availability in the database using Sql, but not in the data guard broker

Solution

———–

DGMGRL> Edit configuration set protection mode as maxavailability;

Succeeded.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

This gets replicated to standby database as well

Posted in Data Guard and DR | Leave a Comment »

Apply Oracle CPUApr2010 – 9352191 for Oracle10.2.0.4 in Aix5L

Posted by Hendry chinnapparaj on May 21, 2010

Hendry Dasan

Monday, 9 August 2010

Apply Oracle CPUApr2010 – 9352191 for Oracle10.2.0.4 in Aix5L

oracle@uatu009 CPUAPR2010>uname -a

AIX uatu009 3 5 00CB7FAD4C00

oracle@uatu009 CPUAPR2010>cat /etc/oratab

cpsua3:/u01/oracle/product/10.2/db_1:Y

cpsua4:/u01/oracle/product/10.2/db_1:Y

streams:/u01/oracle/product/10.2/db_1:N

Databases Instances Running

oracle@uatu009 CPUAPR2010>ps -ef|grep pmon

oracle 1814638 2375704   0 09:54:48  pts/1  0:00 grep pmon

oracle 2199660       1   0   Aug 03      –  1:40 ora_pmon_cpstest

oracle 2220236       1   0   Sep 03      – 113:27 ora_pmon_cpsua4

oracle 2662640       1   0   Sep 03      – 117:35 ora_pmon_cpsua3

Listener

oracle@uatu009 CPUAPR2010>ps -ef|grep tns

oracle 1007868       1   0   Jul 18      – 127:18 /u01/oracle/product/10.2/db_1/bin/tnslsnr LISTENER –inherit

Ensure that the $PATH has the following executables: make, ar, ld, and nm.

oracle@uatu009 CPUAPR2010>cd /usr/ccs/bin

oracle@uatu009 bin>pwd

/usr/ccs/bin

oracle@uatu009 bin>pwd

/usr/ccs/bin

oracle@uatu009 bin>ls -lrt make

-r-xr-xr-x    1 bin      bin          106350 Mar 17 2009  make

oracle@uatu009 bin>ls -lrt ar

-r-xr-xr-x    1 bin      bin           61084 May 15 2008  ar

oracle@uatu009 bin>ls -lrt ld

-r-xr-xr-x    1 bin      bin           39202 Jul 17 2008  ld

oracle@uatu009 bin>ls -lrt nm

-r-xr-xr-x    1 bin      bin           38488 Jun 21 2008  nm

oracle@uatu009 bin>echo $PATH

/usr/bin:/etc:/usr/sbin:/usr/ucb:/u01/oracle/bin:/usr/bin/X11:/sbin:/u01/oracle/product/10.2/db_1/bin:/u01/oracle/product/10.2/db_1/OPatch:/u01/oracle/product/10.2/db_1/bin:/usr/local/bin:/usr/ccs/bin:/etc:/u01/local/bwdba/scripts/general:/u01/local/bwdba/scripts/sql:.

Here the /usr/ccs/bin is under the path, if not you have to do the following

export PATH=$PATH:/usr/ccs/bin

oracle@uatu009 CPUAPR2010>pwd

/u01/local/bwdba/media/patch/CPUAPR2010

oracle@uatu009 CPUAPR2010>unzip p9352191_10204_AIX5L.zip

oracle@uatu009 CPUAPR2010>pwd

/u01/local/bwdba/media/patch/CPUAPR2010

oracle@uatu009 CPUAPR2010>ls -lrt

total 11376

drwxrwxr-x   40 oracle   dba            4096 Mar 24 03:19 9352191

-rw-r—–    1 oracle   dba         5813524 May 20 11:05 p9352191_10204_AIX5L.zip

set pages 3000

set lines 150

set time on

set timing on

column name format a30

column time format a35

column what format a70

column schema_user format a15

column object_name format a30

column name format a30

column time format a35

SELECT COMP_NAME,VERSION, STATUS FROM SYS.DBA_REGISTRY;

select Job, schema_user, broken, what from dba_jobs;

select count(*) from dba_objects where status=’INVALID’;

select owner,object_name,OBJECT_TYPE,status from dba_objects where status=’INVALID’;

show parameter compatible

the output stored  for cpsua3 and cpsua4 under

C:\H_ONCALL_SUPPORT\CHANGES 2010\AUGUST\cps patch uat

oracle@uatu009 CPUAPR2010>. oraenv

ORACLE_SID = [oracle] ? cpsua3

oracle@uatu009 CPUAPR2010>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Aug 9 13:47:53 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      cpsua3

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

oracle@uatu009 CPUAPR2010>. oraenv

ORACLE_SID = [cpsua3] ? cpsua4

oracle@uatu009 CPUAPR2010>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Aug 9 14:04:19 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      cpsua4

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

racle@uatu009 logs>. oraenv

ORACLE_SID = [cpstest] ?

ORACLE_HOME = [/u01/oracle] ? /u01/oracle/product/10.2/db_1

oracle@uatu009 logs>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Aug 9 14:12:21 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      cpstest

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

oracle@uatu009 logs>

oracle@uatu009 logs>

oracle@uatu009 logs>ps -ef|grep pmon

oracle 2572480 2736354   0 14:14:01  pts/0  0:00 grep pmon

oracle@uatu009 logs>ps -ef|grep tns

oracle 1007868       1   1   Jul 18      – 127:22 /u01/oracle/product/10.2/db_1/bin/tnslsnr LISTENER -inherit

oracle 2474212 2736354   0 14:09:33  pts/0  0:00 grep tns

oracle@uatu009 logs>lsnrctl stop

LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Production on 09-AUG-2010 14:09:44

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=uatu009.greatwest.com)(PORT=1521)))

The command completed successfully

oracle@uatu009 logs>ps -ef|grep tns

oracle 2654300 2736354   0 14:09:54  pts/0  0:00 grep tns

check if enterprise manager is running

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

oracle@uatu009 oracle>ps -ef|grep em

root  127110       1   0   Jul 18      –  0:00 /usr/lib/errdemon

daemon  204908  176242   0   Jul 18      –  0:00 /usr/sbin/rpc.statd -d 0 -t 50

root  282828  176242   0   Jul 18      –  0:09 /usr/sbin/qdaemon

oracle 2449536 2375704   0 14:15:28  pts/1  0:00 grep em

DO BACKUP OF ORAINVENTORY AND BINARIES

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

oracle@uatu009 CPUAPR2010>cd

oracle@uatu009 oracle>pwd

/u01/oracle

oracle@uatu009 oracle>ls -lrt

total 112

drwxrwxr-x    6 oracle   dba            4096 Feb  2 2009  oraInventory

oracle@uatu009 oracle>tar -cf – oraInventory|gzip -c > oraInventory_aug_9th.tar.gz

oracle@uatu009 oracle>pwd

/u01/oracle

oracle@uatu009 oracle>ls -lrt

total 1744

-rw-r–r–    1 oracle   dba          835290 Aug  9 14:18 oraInventory_aug_9th.tar.gz

oracle@uatu009 10.2>pwd

/u01/oracle/product/10.2

oracle@uatu009 10.2>ls -rlt

total 8

drwxr-x—   74 oracle   dba            4096 Aug 19 2009  db_1

oracle@uatu009 10.2>tar -cf – db_1|gzip -c > db_1_aug_9th.tar.gz

oracle@uatu009 10.2>ls -lrt

total 3776040

drwxr-x—   74 oracle   dba            4096 Aug 19 2009  db_1

-rw-r–r–    1 oracle   dba      1933241252 Aug  9 14:42 db_1_aug_9th.tar.gz

oracle@uatu009 10.2>opatch lsinventory

Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3

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

Oracle Home       : /u01/oracle/product/10.2/db_1

Central Inventory : /u01/oracle/oraInventory

from           : /etc/oraInst.loc

OPatch version    : 10.2.0.4.3

OUI version       : 10.2.0.4.0

OUI location      : /u01/oracle/product/10.2/db_1/oui

Log file location : /u01/oracle/product/10.2/db_1/cfgtoollogs/opatch/opatch2010-08-09_14-28-11PM.log

Lsinventory Output file location : /u01/oracle/product/10.2/db_1/cfgtoollogs/opatch/lsinv/lsinventory2010-08-09_14-28-11PM.txt

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

Installed Top-level Products (3):

Oracle Database 10g                                                  10.2.0.1.0

Oracle Database 10g Release 2 Patch Set 2                            10.2.0.3.0

Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0

There are 3 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

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

OPatch succeeded.

oracle@uatu009 CPUAPR2010>pwd

/u01/local/bwdba/media/patch/CPUAPR2010

‘oracle@uatu009 CPUAPR2010>cd 9352191

oracle@uatu009 9352191>pwd

/u01/local/bwdba/media/patch/CPUAPR2010/9352191

oracle@uatu009 9352191>opatch napply -skip_subset -skip_duplicate

Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3

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

UTIL session

Oracle Home       : /u01/oracle/product/10.2/db_1

Central Inventory : /u01/oracle/oraInventory

from           : /etc/oraInst.loc

OPatch version    : 10.2.0.4.3

OUI version       : 10.2.0.4.0

OUI location      : /u01/oracle/product/10.2/db_1/oui

Log file location : /u01/oracle/product/10.2/db_1/cfgtoollogs/opatch/opatch2010-08-09_14-52-46PM.log

Invoking utility “napply”

Checking conflict among patches…

Checking if Oracle Home has components required by patches…

Checking skip_duplicate

Checking skip_subset

Checking conflicts against Oracle Home…

OPatch continues with these patches:    7155248   7155249   7155250   7155251   7155252   7155253   7155254   7197583   7375611   7375613   7375617   7609057   7609058   8309592   8309632   8309642   8568395   8568397   8568398   8568402   8568404   8568405   8836667   8836671   8836675   8836677   8836678   8836681   8836683   8836684   8836686   9173244   9173248   9352191   9442328   9442331   9442335   9442339

Do you want to proceed? [y|n]

Y

User Responded with: Y

Running prerequisite checks…

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = ‘/u01/oracle/product/10.2/db_1’)

Is the local system ready for patching? [y|n]

Y

ApplySession applying interim patch ‘8568398’ to OH ‘/u01/oracle/product/10.2/db_1’

Backing up files affected by the patch ‘8568398’ for rollback. This might take a while…

Patching component oracle.rdbms, 10.2.0.4.0…

Updating archive file “/u01/oracle/product/10.2/db_1/lib/libserver10.a”  with “lib/libserver10.a/krvg.o”

Updating archive file “/u01/oracle/product/10.2/db_1/lib/libserver10.a”  with “lib/libserver10.a/knld.o”

Updating archive file “/u01/oracle/product/10.2/db_1/lib/libserver10.a”  with “lib/libserver10.a/prsc.o”

Updating archive file “/u01/oracle/product/10.2/db_1/lib/libserver10.a”  with “lib/libserver10.a/prsg.o”

Updating archive file “/u01/oracle/product/10.2/db_1/lib/libserver10.a”  with “lib/libserver10.a/prssz.o”

Copying file to “/u01/oracle/product/10.2/db_1/rdbms/lib/jox.o”

Copying file to “/u01/oracle/product/10.2/db_1/lib/libjox10.a”

The following actions have failed:

Copy failed from ‘/u01/local/bwdba/media/patch/CPUAPR2010/9352191/8568398/files/lib/libjox10.a’ to ‘/u01/oracle/product/10.2/db_1/lib/libjox10.a’…

Do you want to proceed? [y|n]

The following actions have failed:

Copy failed from ‘/u01/local/bwdba/media/patch/CPUAPR2010/9352191/8568398/files/lib/libjox10.a’ to ‘/u01/oracle/product/10.2/db_1/lib/libjox10.a’…

Do you want to proceed? [y|n]

Y

The step below is very important – give full path while copying

oracle@uatu009 db_1>cd lib

oracle@uatu009 lib>ls -lrt libjox10.a

-rw-r–r–    1 oracle   dba        13257611 May 10 2008  libjox10.a

oracle@uatu009 lib>mv libjox10.a libjox10.a.orig

Cp /u01/local/bwdba/media/patch/CPUAPR2010/9352191/8568398/files/lib/libjox10.a  /u01/oracle/product/10.2/db_1/lib/

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

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

The following warnings have occurred during OPatch execution:

1) OUI-67294:

*******************************************************************************************

Oracle Configuration Manager is installed but not configured.  OCM enables Oracle to

provide superior, proactive support for our customers. Oracle strongly recommends customers

configure OCM. To complete the configuration of OCM, refer to the OCM Installation and

Administration Guide (http://www.oracle.com/technology/documentation/ocm.html).

*******************************************************************************************

2) OUI-67124:Copy failed from ‘/u01/local/bwdba/media/patch/CPUAPR2010/9352191/8568398/files/lib/libjox10.a’ to ‘/u01/oracle/product/10.2/db_1/lib/libjox10.a’…

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

OPatch Session completed with warnings.

OPatch completed with warnings.

Unix script to run  by Midrange team

Can you pls run the cpu_root.sh as below and get back

export ORACLE_HOME=/u01/oracle/product/10.2/db_1

cd /u01/local/bwdba/media/patch/CPUAPR2010/9352191

oracle@uatu009 9352191>ls -lrt cpu_root.sh

-rw-rw-r–    1 oracle   dba             320 Dec 12 2008  cpu_root.sh

oracle@uatu009 9352191>pwd

/u01/local/bwdba/media/patch/CPUAPR2010/9352191

sh cpu_root.sh

Run the catbundle script on every database

oracle@uatu009 9352191>cd $ORACLE_HOME/rdbms/admin

oracle@uatu009 admin>pwd

/u01/oracle/product/10.2/db_1/rdbms/admin

oracle@uatu009 admin>

oracle@uatu009 admin>echo $ORACLE_SID

cpsua4

oracle@uatu009 admin>. oraenv

ORACLE_SID = [cpsua4] ? cpstest

ORACLE_HOME = [/u01/oracle] ? /u01/oracle/product/10.2/db_1

oracle@uatu009 admin>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Aug 9 15:38:14 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup

/u01/oracle/.kshrc[19]: hostname:  not found

ORACLE instance started.

Total System Global Area  536870912 bytes

Fixed Size                  2085392 bytes

Variable Size             222301680 bytes

Database Buffers          306184192 bytes

Redo Buffers                6299648 bytes

Database mounted.

Database opened.

SQL> @catbundle.sql cpu apply

 

Search the logs under

oracle@uatu009 catbundle>pwd

/u01/oracle/product/10.2/db_1/cfgtoollogs/catbundle

Look for errors below

 

/ORA

and

/ERR

 

:q!

racle@uatu009 admin>pwd

/u01/oracle/product/10.2/db_1/rdbms/admin

oracle@uatu009 admin>. oraenv

ORACLE_SID = [cpstest] ? cpsua3

oracle@uatu009 admin>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Aug 9 15:47:37 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup

/u01/oracle/.kshrc[19]: hostname:  not found

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size                  2083728 bytes

Variable Size             218104944 bytes

Database Buffers         1023410176 bytes

Redo Buffers               14692352 bytes

Database mounted.

Database opened.

SQL> @catbundle.sql cpu apply

oracle@uatu009 admin>. oraenv

ORACLE_SID = [cpsua3] ? cpsua4

oracle@uatu009 admin>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Aug 9 15:52:06 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup

/u01/oracle/.kshrc[19]: hostname:  not found

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size                  2083728 bytes

Variable Size             687866992 bytes

Database Buffers          553648128 bytes

Redo Buffers               14692352 bytes

Database mounted.

Database opened.

SQL> @catbundle.sql cpu apply

Check the following log file for errors:

/u01/oracle/product/10.2/db_1/cfgtoollogs/catbundle/catbundle_CPU_CPSUA4_AP

VIEW RECOMPILATION

———————————

Cpsua3

———

SELECT * FROM registry$history where ID = ‘6452863’;

No rows selected

If no rows selected, then you need to run the recompile script as below

racle@uatu009 catbundle>cd /u01/oracle/product/10.2/db_1/cpu/view_recompile

oracle@uatu009 view_recompile>ls -lrt

total 24

-rw-r–r–    1 oracle   dba            5143 Jul 10 2008  view_recompile_jan2008cpu.sql

-rw-r–r–    1 oracle   dba            2095 Jul 10 2008  recompile_precheck_jan2008cpu.sql

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      cpsua3

SQL> @recompile_precheck_jan2008cpu.sql

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade

/u01/oracle/.kshrc[19]: hostname:  not found

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size                  2083728 bytes

Variable Size             301991024 bytes

Database Buffers          939524096 bytes

Redo Buffers               14692352 bytes

Database mounted.

Database opened.

SQL> @view_recompile_jan2008cpu.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

1 row created.

Commit complete.

No. of Invalid Objects is :1337

Please refer to README.html to for instructions on validating these objects

PL/SQL procedure successfully completed.

Logfile for the current viewrecomp.sql session is : vcomp_CPSUA3_09Aug2010_16_51_04.log

SQL> exit

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

/u01/oracle/.kshrc[19]: hostname:  not found

ORACLE instance started.

Total System Global Area 1258291200 bytes

Fixed Size                  2083728 bytes

Variable Size             301991024 bytes

Database Buffers          939524096 bytes

Redo Buffers               14692352 bytes

Database mounted.

Database opened.

SQL> show parameter db_name

NAME                                 TYPE        VALUE

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

db_name                              string      cpsua3

SELECT * FROMregistry$historywhereID=‘6452863’;

ACTION_TIME                     ACTION

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

NAMESPACE                      VERSION                                ID

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

COMMENTS

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

BUNDLE_SERIES

——————————

09-AUG-10 04.53.29.686534 PM    CPU

6452863

view recompilation

1 row selected.

/u01/oracle/product/10.2/db_1/cpu/view_recompile

DO the same VIEW RECOMPILATION for other databases

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

oracle@uatu009 admin>opatch lsinventory

Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3

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

Oracle Home       : /u01/oracle/product/10.2/db_1

Central Inventory : /u01/oracle/oraInventory

from           : /etc/oraInst.loc

OPatch version    : 10.2.0.4.3

OUI version       : 10.2.0.4.0

OUI location      : /u01/oracle/product/10.2/db_1/oui

Log file location : /u01/oracle/product/10.2/db_1/cfgtoollogs/opatch/opatch2010-08-09_16-18-41PM.log

Lsinventory Output file location : /u01/oracle/product/10.2/db_1/cfgtoollogs/opatch/lsinv/lsinventory2010-08-09_16-18-41PM.txt

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

Installed Top-level Products (3):

Oracle Database 10g                                                  10.2.0.1.0

Oracle Database 10g Release 2 Patch Set 2                            10.2.0.3.0

Oracle Database 10g Release 2 Patch Set 3                            10.2.0.4.0

There are 3 products installed in this Oracle Home.

Interim patches (38) :

Patch  9442339      : applied on Mon Aug 09 15:27:12 WST 2010

Created on 23 Mar 2010, 12:15:41 hrs PST8PDT

Bugs fixed:

8210889, 8479537, 9442339, 6923450

Patch  9442335      : applied on Mon Aug 09 15:27:06 WST 2010

Created on 23 Mar 2010, 12:15:40 hrs PST8PDT

Bugs fixed:

9442335

Patch  9442331      : applied on Mon Aug 09 15:27:02 WST 2010

Created on 23 Mar 2010, 12:15:39 hrs PST8PDT

Bugs fixed:

9442331

Patch  9442328      : applied on Mon Aug 09 15:26:58 WST 2010

Created on 23 Mar 2010, 12:15:38 hrs PST8PDT

Bugs fixed:

9442328

Patch  9352191      : applied on Mon Aug 09 15:26:53 WST 2010

Created on 23 Mar 2010, 12:15:33 hrs PST8PDT

Bugs fixed:

8534387, 8290506, 7375644, 9352191, 7150470, 7592346, 9119226, 8836308

Patch  9173248      : applied on Mon Aug 09 15:26:49 WST 2010

Created on 9 Dec 2009, 07:37:56 hrs PST8PDT

Bugs fixed:

9173248

Patch  9173244      : applied on Mon Aug 09 15:26:44 WST 2010

Created on 9 Dec 2009, 07:37:54 hrs PST8PDT

Bugs fixed:

9173244

Patch  8836686      : applied on Mon Aug 09 15:26:39 WST 2010

Created on 4 Sep 2009, 11:00:36 hrs PST8PDT

Bugs fixed:

8836686

Patch  8836684      : applied on Mon Aug 09 15:26:35 WST 2010

Created on 4 Sep 2009, 11:00:34 hrs PST8PDT

Bugs fixed:

8836684

Patch  8836683      : applied on Mon Aug 09 15:26:31 WST 2010

Created on 4 Sep 2009, 11:00:33 hrs PST8PDT

Bugs fixed:

8309623, 8836683

Patch  8836681      : applied on Mon Aug 09 15:26:26 WST 2010

Created on 4 Sep 2009, 11:00:31 hrs PST8PDT

Bugs fixed:

8836681, 8309587

Patch  8836678      : applied on Mon Aug 09 15:26:20 WST 2010

Created on 4 Sep 2009, 11:00:29 hrs PST8PDT

Bugs fixed:

8836678

Patch  8836677      : applied on Mon Aug 09 15:26:16 WST 2010

Created on 11 Sep 2009, 01:30:13 hrs PST8PDT

Bugs fixed:

8836677

Patch  8836675      : applied on Mon Aug 09 15:26:11 WST 2010

Created on 4 Sep 2009, 11:00:23 hrs PST8PDT

Bugs fixed:

8309637, 8836675

Patch  8836671      : applied on Mon Aug 09 15:26:06 WST 2010

Created on 4 Sep 2009, 11:00:23 hrs PST8PDT

Bugs fixed:

8836671

Patch  8836667      : applied on Mon Aug 09 15:26:02 WST 2010

Created on 4 Sep 2009, 11:00:21 hrs PST8PDT

Bugs fixed:

8836667

Patch  8568405      : applied on Mon Aug 09 15:25:58 WST 2010

Created on 9 Jun 2009, 18:00:27 hrs PST8PDT

Bugs fixed:

8568405

Patch  8568404      : applied on Mon Aug 09 15:25:53 WST 2010

Created on 9 Jun 2009, 18:00:25 hrs PST8PDT

Bugs fixed:

8568404

Patch  8568402      : applied on Mon Aug 09 15:25:48 WST 2010

Created on 9 Jun 2009, 18:00:23 hrs PST8PDT

Bugs fixed:

6870937, 8568402

Patch  8568398      : applied on Mon Aug 09 15:25:43 WST 2010

Created on 9 Jun 2009, 18:00:20 hrs PST8PDT

Bugs fixed:

6392076, 8568398

Patch  8568397      : applied on Mon Aug 09 15:22:10 WST 2010

Created on 9 Jun 2009, 18:00:17 hrs PST8PDT

Bugs fixed:

8568397

Patch  8568395      : applied on Mon Aug 09 15:22:05 WST 2010

Created on 9 Jun 2009, 18:00:15 hrs PST8PDT

Bugs fixed:

8309639, 8568395

Patch  8309642      : applied on Mon Aug 09 15:22:00 WST 2010

Created on 18 Mar 2009, 20:38:30 hrs PST8PDT

Bugs fixed:

8309642

Patch  8309632      : applied on Mon Aug 09 15:21:55 WST 2010

Created on 18 Mar 2009, 20:37:46 hrs PST8PDT

Bugs fixed:

8309632

Patch  8309592      : applied on Mon Aug 09 15:21:51 WST 2010

Created on 18 Mar 2009, 20:36:51 hrs PST8PDT

Bugs fixed:

8309592

Patch  7609058      : applied on Mon Aug 09 15:21:46 WST 2010

Created on 9 Jun 2009, 17:59:56 hrs PST8PDT

Bugs fixed:

7609058

Patch  7609057      : applied on Mon Aug 09 15:21:42 WST 2010

Created on 4 Jun 2009, 12:10:30 hrs PST8PDT

Bugs fixed:

7609057

Patch  7375617      : applied on Mon Aug 09 15:21:37 WST 2010

Created on 16 Sep 2008, 02:00:43 hrs PST8PDT

Bugs fixed:

7375617

Patch  7375613      : applied on Mon Aug 09 15:21:33 WST 2010

Created on 16 Sep 2008, 02:00:41 hrs PST8PDT

Bugs fixed:

7375613

Patch  7375611      : applied on Mon Aug 09 15:21:29 WST 2010

Created on 16 Sep 2008, 02:00:38 hrs PST8PDT

Bugs fixed:

7375611

Patch  7197583      : applied on Mon Aug 09 15:21:24 WST 2010

Created on 10 Jul 2008, 03:17:32 hrs PST8PDT

Bugs fixed:

7197583

Patch  7155254      : applied on Mon Aug 09 15:21:16 WST 2010

Created on 10 Jul 2008, 03:17:30 hrs PST8PDT

Bugs fixed:

7155254

Patch  7155253      : applied on Mon Aug 09 15:20:03 WST 2010

Created on 10 Jul 2008, 03:17:27 hrs PST8PDT

Bugs fixed:

7155253

Patch  7155252      : applied on Mon Aug 09 15:19:04 WST 2010

Created on 10 Jul 2008, 03:17:25 hrs PST8PDT

Bugs fixed:

7155252

Patch  7155251      : applied on Mon Aug 09 15:18:59 WST 2010

Created on 10 Jul 2008, 03:17:24 hrs PST8PDT

Bugs fixed:

7155251

Patch  7155250      : applied on Mon Aug 09 15:18:54 WST 2010

Created on 10 Jul 2008, 03:17:22 hrs PST8PDT

Bugs fixed:

7155250

Patch  7155249      : applied on Mon Aug 09 15:18:49 WST 2010

Created on 10 Jul 2008, 03:17:01 hrs PST8PDT

Bugs fixed:

7155249

Patch  7155248      : applied on Mon Aug 09 15:18:44 WST 2010

Created on 10 Jul 2008, 03:06:50 hrs PST8PDT

Bugs fixed:

7155248

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

OPatch succeeded.

oracle@uatu009 admin>

Posted in Upgrade and Patching | Leave a Comment »

Simple switchover from primary site to DR using Data Guard Broker

Posted by Hendry chinnapparaj on May 18, 2010

Hendry Dasan – DBA

Tuesday, 18 May 2010

Simple switchover from primary site to DR using Data Guard Broker

Server and Database original setup Details

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

1.1     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) in Dasan Street
  • Standby Site: 1 Node (DEVU009N4) in Christ Court
  • Storage: ASM11gR2
  • Database Name: CPSTEST
  • Instance Name: CPSTEST
  • Primary DB_UNIQUE_NAME: CPSTEST_J
  • Standby DB_UNIQUE_NAME: CPSTEST_F
  • Standby Type: Physical Standby

TEST2 à SWITCHOVER FROM DASAN STREET to CHRIST COURT

SQL>  show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cps

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

SQL>

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> /

System altered.

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n3

MAXIMUM AVAILABILITY OK                              141         141

18/05 13:18:10          0

CONNECTION FROM THE REMOTE CLIENT TO PRIMARY SERVER

 

C:\Documents and Settings\bb15908>sqlplus dgtest/dgtest@cps

SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 18 13:12:16 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

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

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

Now I connect to the current standby server(devu009n4) using dgmrgl tool and invoke the switchover from JS to FC

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

Make sure you connect as “sys” user for a successful switchover.

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> switchover to cpstest_f;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance “cpstest” on database “cpstest_f”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_f”

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

SQL> conn / as sysdba

Connected.

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cps

without disconnecting the dgtest client, query the database again – the hostname indicates that the client has failedover to the other server

C:\Documents and Settings\bb15908>sqlplus dgtest/dgtest@cps

SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 18 13:12:16 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

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

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL>

Also confirmed from this query

SQL> Select failover_type, failover_method, failed_over from v$session where username =’DGTEST’;

FAILOVER_TYPE FAILOVER_M FAI

————- ———- —

SELECT        BASIC      YES

Also ensure that all the readonly datafiles and other files are ONLINE

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

no rows selected

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

Check if the data guard is in sync

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM AVAILABILITY OK                              149         146

18/05 13:27:03          3

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES

SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Posted in Data Guard and DR | Leave a Comment »

Oracle10g Database Failover Test using Data Guard Broker

Posted by Hendry chinnapparaj on May 18, 2010

Hendry Dasan – DBA

Tuesday, 18 May 2010

Oracle10g Database Failover Test using Data Guard Broker

The following is a step by step operation on the Data Guard Failover operations for Oracle10g Database in an Oracle11gR2 ASM Env. After the successful failover, the original data Guard Configuration is reverted with a switchover

Server and Database original setup Details

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

1.1     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) in Dasan Street
  • Standby Site: 1 Node (DEVU009N4) in Christ Court
  • Storage: ASM11gR2
  • Database Name: CPSTEST
  • Instance Name: CPSTEST
  • Primary DB_UNIQUE_NAME: CPSTEST_J
  • Standby DB_UNIQUE_NAME: CPSTEST_F
  • Standby Type: Physical Standby

BUT IN THE CURRENT SETTING THE PRIMARY IS “cpstest_f” and standby is “cpstest_j”

Connect to primary server – devu009n4 and query the below

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL> connect sys/cpstest

Connected.

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> select database_role,PROTECTION_MODE from v$database;

DATABASE_ROLE    PROTECTION_MODE

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

PRIMARY          MAXIMUM AVAILABILITY

In standby database server

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES

SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL>

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

NOW KILL THE PRIMARY SERVER DEVU009N4 – CAN PULLOUT / DISABLE THE NETWORK INTERFACE

After killing ther primary server, connect to dgmgrl tool in standby database – may be a bit slow, but gets connected

devu009n3 $

devu009n3 $ dgmgrl /

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

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

Welcome to DGMGRL, type “help” for information.

Connected.

DGMGRL> DGMGRL>

DGMGRL>

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

Error: ORA-16625: cannot reach the database

DGMGRL> failover to cpstest_j;

Performing failover NOW, please wait…

Failover succeeded, new primary is “cpstest_j”

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select PROTECTION_MODE,PROTECTION_LEVEL from v$database;

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL>  select FLASHBACK_ON from v$database;

FLASHBACK_ON

——————

YES

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database (disabled)

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL> show database cpstest_j;

Database

Name:            cpstest_j

Role:            PRIMARY

Enabled:         YES

Intended State:  ONLINE

Instance(s):

cpstest

Current status for “cpstest_j”:

SUCCESS

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

no rows selected

query from the already connected remote client and test which server it connected to – the output below shows that the client has successfully failedover to the new primary server without being disconnected.

15:04:55 SQL> show user

USER is “DGTEST”

15:21:01 SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

Alert log in new primary shows now

PING[ARC0]: Heartbeat failed to connect to standby ‘(DESCRIPTION=(ADDRESS_LIST=(address=(protocol=TCP)(host=devu009n3)(port=1521)))(CONNECT_DATA=(SERVICE_NAME=cpstest_j_XPT)(INSTANCE_NAME=cpstest)(SERVER=dedicated)))’. Error is 16009.

Tue May 18 15:15:26 2010

ALTER SYSTEM SET log_archive_dest_state_2=’RESET’ SCOPE=BOTH;

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cps

NOW BRING UP THE OLD PRIMARY SERVER – ENABLE NETWORK INTERFACE

Reinstate the old primary as new standby

devu009n4 $ . oraenv

ORACLE_SID = [cpstest] ?

devu009n4 $ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 18 15:16:47 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

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

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL>

SQL> startup mount

ORACLE instance started.

Total System Global Area  314572800 bytes

Fixed Size                  2083760 bytes

Variable Size             138413136 bytes

Database Buffers          171966464 bytes

Redo Buffers                2109440 bytes

Database mounted.

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          MOUNTED

IMPORTANT – DON’T ISSUE THE FOLLOWING COMMAND IN THE OLD PRIMARY DATABASE AS SHOWN BELOW

YOU WILL GET  ORA-16795 ERRORS

GMGRL> connect sys/cpstest

Connected.

DGMGRL>

DGMGRL>

DGMGRL>

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> reinstate database cpstest_f;

Error: ORA-16795: database resource guard detects that database re-creation is required

Configuration details cannot be determined by DGMGRL

1.2      ORA-16795: database resource guard detects that database re-creation is required

Cause: In the act of failover or switchover, the database resource guard may have detected that re-creation of the database is necessary. This occurs when the database resource guard recognizes a situation in which the database in question cannot be a viable standby database for the new primary database. Until this error status is resolved for this database, information about this database and the broker configuration to which it belongs is unavailable to a broker client that is connected to this database. Therefore, all commands directed by that client to this database cannot be completed.
Action: Re-create (or flash back) the standby database. Connect to the primary database in the broker configuration and reenable broker management of that database. At this point you may connect to that standby database and resume issuing client commands. Alternatively, many client commands that cannot be completed at the standby database when in this error state can be completed successfully when issued to the primary database. In this case, simply reconnect to the primary database and retry the command.

SQL> select flashback_on from v$database;

FLASHBACK_ON

——————

YES

SO CONNECT TO NEW PRIMARY DATABASE AND ISSUE THE BELOW FOR REINSTATING THE OLD PRIMARY AS NEW STANDBY DATABASE

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> reinstate database cpstest_f;

Reinstating database “cpstest_f”, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_f”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Continuing to reinstate database “cpstest_f” …

Reinstatement of database “cpstest_f” succeeded

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

Check if the data guard is in sync

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n3

MAXIMUM PERFORMANCE  CHECK CONFIGURATION               4           4

18/05 15:51:19          0

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

no rows selected

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cps

The remote client is still connected

15:21:06 SQL> show user

USER is “DGTEST”

15:53:11 SQL> show user

USER is “DGTEST”

15:53:16 SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

Query the new standby database now

SQL> conn / as sysdba

Connected.

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

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

no rows selected

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cpstest_f

DGMGRL> connect sys/cpstest

Connected.

DGMGRL>  show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

Reverting to original data guard configuration

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

Check if the data guard is in sync

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n3

MAXIMUM PERFORMANCE  CHECK CONFIGURATION               6           6

18/05 16:06:19          0

Remote client connected to devu009n3 as being the primary server

16:08:12 SQL> show user

USER is “DGTEST”

16:08:15 SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL> switchover to cpstest_f;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance “cpstest” on database “cpstest_f”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_f”

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

SQL> conn / as sysdba

Connected.

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cpstest_j

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

SQL>

Again check the remote client connection – failedover to primary database now

16:08:12 SQL> show user

USER is “DGTEST”

16:08:15 SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

16:08:24 SQL>

16:16:58 SQL>

16:16:59 SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

16:17:03 SQL> show user

USER is “DGTEST”

Now connect to PRIMARY DATABASE SERVER – devu009n4 and query

 

SQL> conn / as sysdba

Connected.

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cps

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

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

no rows selected

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxPerformance

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

Check if the data guard is in sync

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> alter system archive log current;

System altered.

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

LOG_ARCHIVED – LOG_APPLIED LOG_GAP

FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

FROM GV$INSTANCE

ORDER BY INST_ID) NAME,

(SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

FROM GV$ARCHIVE_DEST_STATUS

WHERE DEST_ID = 2

ORDER BY INST_ID) STAT,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1

AND ARCHIVED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) ARCH,

(SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

TO_CHAR (MAX (COMPLETION_TIME),

‘DD/MM HH24:MI:SS’

) TIME_APPLIED

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 2

AND APPLIED = ‘YES’

AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

FROM V$ARCHIVED_LOG

WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

GROUP BY THREAD#

ORDER BY THREAD#) APPL

WHERE NAME.INST_ID = STAT.INST_ID

AND NAME.INST_ID = ARCH.THREAD#

AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM PERFORMANCE  CHECK CONFIGURATION              16          16

18/05 16:23:02          0

On the primary database, change the protection mode back to maximum availability

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 MAXIMUM AVAILABILITY

Shalom !

Posted in Data Guard and DR | Leave a Comment »

Client Failover Test Setup and Test in Data Guard Broker Env

Posted by Hendry chinnapparaj on May 18, 2010

Client failover Test for DR

1.1     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) in Dasan Street
  • Standby Site: 1 Node (DEVU009N4) in Christ Court
  • Storage: ASM11gR2
  • Database Name: CPSTEST
  • Instance Name: CPSTEST
  • Primary DB_UNIQUE_NAME: CPSTEST_J
  • Standby DB_UNIQUE_NAME: CPSTEST_F
  • Standby Type: Physical Standby

PRIMARY

————–

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

SQL> SHOW parameter service_names

NAME                                 TYPE        VALUE

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

service_names                        string      cpstest_j

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

devu009n3 $ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 14-MAY-2010 16:16:24

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production

Start Date                13-MAY-2010 13:27:56

Uptime                    1 days 2 hr. 48 min. 27 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /u01/oracle/product/11.2.0/grid/network/admin/listener.ora

Listener Log File         /u01/oracle/diag/tnslsnr/devu009n3/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devu009n3.greatwest.com)(PORT=1521)))

Services Summary…

Service “+ASM” has 1 instance(s).

Instance “+ASM”, status READY, has 1 handler(s) for this service…

Service “cpstest_j” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_DGB” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_XPT” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_dgmgrl” has 1 instance(s).

Instance “cpstest”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

Creating a service

Set echo on

SQL> exec DBMS_SERVICE.CREATE_SERVICE ( –

> service_name => ‘cps’, –

> network_name => ‘cps’, –

> failover_method => ‘BASIC’, –

> failover_type => ‘SELECT’, –

> failover_retries => 180, –

> failover_delay => 1);

PL/SQL procedure successfully completed.

Start the service

SQL> exec dbms_service.start_service(‘cps’);

PL/SQL procedure successfully completed.

Now the service added to listener

devu009n3 $ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production on 14-MAY-2010 16:18:39

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

————————

Alias                     LISTENER

Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production

Start Date                13-MAY-2010 13:27:56

Uptime                    1 days 2 hr. 50 min. 42 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      ON

Listener Parameter File   /u01/oracle/product/11.2.0/grid/network/admin/listener.ora

Listener Log File         /u01/oracle/diag/tnslsnr/devu009n3/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devu009n3.greatwest.com)(PORT=1521)))

Services Summary…

Service “+ASM” has 1 instance(s).

Instance “+ASM”, status READY, has 1 handler(s) for this service…

Service “cps” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_DGB” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_XPT” has 1 instance(s).

Instance “cpstest”, status READY, has 1 handler(s) for this service…

Service “cpstest_j_dgmgrl” has 1 instance(s).

Instance “cpstest”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

SQL> show parameter service_names

NAME                                 TYPE        VALUE

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

service_names                        string      cps

Note the value has changed to “cps” now from cpstest_j

creating an after startup trigger

SQL> @create_trigger.sql

SQL> set echo on

SQL> create or replace trigger manage_service after startup on database

2  declare

3     role varchar(30);

4  begin

5     select database_role into role from v$database;

6     if role = ‘PRIMARY’ then

7        DBMS_SERVICE.START_SERVICE(‘cps’);

8     Else

9        DBMS_SERVICE.STOP_SERVICE(‘cps’);

10     End if;

11  End;

12  /

Trigger created.

Create Oracle net service name on primary and standby

cps =

(DESCRIPTION =

(ADDRESS_LIST =

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

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

)

(CONNECT_DATA =

(SERVICE_NAME = cps.greatwest.com)

)

)

devu009n3 $ tnsping cps

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Production on 14-MAY-2010 16:44:27

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3.greatwest.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4.greatwest.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cps.greatwest.com)))

OK (0 msec)

devu009n4 $ tnsping cps

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Production on 14-MAY-2010 16:42:28

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n3.greatwest.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = devu009n4.greatwest.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cps.greatwest.com)))

OK (10 msec)

Testing the Implementation

——————————–

Create user dgtest identified by dgtest;

Grant create session to dgtest;

Grant select_catalog_role to dgtest;

Connect to primary instance now

devu009n4 $ sqlplus dgtest/dgtest@cps

SQL*Plus: Release 10.2.0.4.0 – Production on Fri May 14 17:16:43 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

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

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME

————— —————-

HOST_NAME

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

VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT

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

LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO

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

1 cpstest

devu009n3

10.2.0.4.0        14-MAY-10 OPEN         NO           1 STARTED

ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO

SQL> select failover_type,failover_method,failed_over from v$session where username=’DGTEST’;

FAILOVER_TYPE FAILOVER_M FAI

————- ———- —

SELECT        BASIC      NO

devu009n3 $ dgmgrl

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

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

Welcome to DGMGRL, type “help” for information.

DGMGRL> sys/cpstest

Unrecognized command “sys”, try “help”

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL> switchover to cpstest_f;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

atabase dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_f”

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Primary database

cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

Select failover_type, failover_method, failed_over from v$session where username = ‘DGTEST’;

FAILOVER_TYPE FAILOVER_M FAI

————- ———- —

SELECT        BASIC      YES

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> show parameter service

NAME                                 TYPE        VALUE

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

service_names                        string      cps

Also test from a remote client – my laptop – connect thru sqlplus to the primary server when the stby listener is down

– enter the tns string in my laptop 10g tns

cps =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = devu009n5.greatwest.com)(PORT = 1521))

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

)

(CONNECT_DATA =

(SERVICE_NAME = cps)

)

)

— note I have given a wrong server name for the first host, thereby trying to use the second host to connect

— it has connected to the primary server – second line – host

C:\Documents and Settings\bb15908>sqlplus dgtest/dgtest@cps

SQL*Plus: Release 10.2.0.4.0 – Production on Fri May 14 17:40:10 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

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

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

Posted in Data Guard and DR | Leave a Comment »

Client failover and DR Switchover

Posted by Hendry chinnapparaj on May 18, 2010

Server and Database original setup Details

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

1.1     Environment Details – original setting

  • 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)- in Dasan street
  • Standby Site: 1 Node (DEVU009N4)- in Christ court
  • Storage: ASM11gR2
  • Database Name: CPSTEST
  • Instance Name: CPSTEST
  • Primary DB_UNIQUE_NAME: CPSTEST_J
  • Standby DB_UNIQUE_NAME: CPSTEST_F
  • Standby Type: Physical Standby

Tnsnames.ora setting in the  remote client

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

CPS =

(DESCRIPTION =

(ADDRESS_LIST =

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

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

)

(CONNECT_DATA =

(SERVICE_NAME = cps)

)

)

Data Guard Config Now

DGMGRL> show configuration;

Configuration

Name:                cpstest_dgb

Enabled:             YES

Protection Mode:     MaxAvailability

Fast-Start Failover: DISABLED

Databases:

cpstest_j – Physical standby database

cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

Note:_ the primary database is already at the DR Site before this exercise.

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cps

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n4

MAXIMUM AVAILABILITY OK                              134         134

18/05 12:01:30          0

Connection from the remote client – pointing to primary database server (Christ court)

select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> show parameter service_name

NAME                                 TYPE        VALUE

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

service_names                        string      cpstest_f

DOING A SWITCHOVER NOW from the current Primary Node(devu009n4) while the TOAD Client is still connected.

TEST1 à SWITCHOVER FROM FRAMCE COURT TO DASAN STREET

DGMGRL> CONNECT SYS/CPSTEST

Connected.

DGMGRL> switchover to cpstest_j;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_f”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_j”

DGMGRL>

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n4

SQL> show parameter service_name à Note:- the service_name changed back to cpstest_f in the standby role.

NAME                                 TYPE        VALUE

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

service_names                        string      cpstest_f

Connection from the remote TOAD client again – pointing to primary database server (Dasan Street)

select host_name from v$instance;

HOST_NAME

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

devu009n3

Also from the command prompt / remote sqlplus client connection to primary database

C:\Documents and Settings\bb15908>sqlplus dgtest/dgtest@cps

SQL*Plus: Release 10.2.0.4.0 – Production on Tue May 18 13:12:16 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

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

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

Query the new Primary database

SQL>  show parameter service_name à Note:- The Primary database always has the service_names as ”cps”

NAME                                 TYPE        VALUE

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

service_names                        string      cps

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PRIMARY          READ WRITE

SQL> select host_name from v$instance;

HOST_NAME

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

devu009n3

Posted in Data Guard and DR | Leave a Comment »

Deinstall 11g ASM Grid Infra

Posted by Hendry chinnapparaj on May 17, 2010

 

Deinstall 11g ASM Grid Infra

devu009n3 $ pwd

/u01/oracle/product/11.2.0/grid

devu009n3 $ cd deinstall

devu009n3 $ pwd

/u01/oracle/product/11.2.0/grid/deinstall

devu009n3 $ ./deinstall

Checking for required files and bootstrapping …

Please wait …

Location of logs /tmp/deinstall2010-04-28_07-49-16-AM/logs/

############ ORACLE DEINSTALL & DECONFIG TOOL START ############

######################## CHECK OPERATION START ########################

Install check configuration START

Checking for existence of the Oracle home location /u01/oracle/product/11.2.0/grid

Oracle Home type selected for de-install is: SIHA

Oracle Base selected for de-install is: /u01/oracle

Checking for existence of central inventory location /u01/oraInventory

Checking for existence of the Oracle Grid Infrastructure home /u01/oracle/product/11.2.0/grid

Install check configuration END

Traces log file: /tmp/deinstall2010-04-28_07-49-16-AM/logs//crsdc.log

Network Configuration check config START

Network de-configuration trace file location: /tmp/deinstall2010-04-28_07-49-16-AM/logs/netdc_check5264144922161138317.log

Specify all Oracle Restart enabled listeners that are to be de-configured [LISTENER]:

Network Configuration check config END

Asm Check Configuration START

ASM de-configuration trace file location: /tmp/deinstall2010-04-28_07-49-16-AM/logs/asmcadc_check9186920000200769692.log

ASM configuration was not detected in this Oracle home. Was ASM configured in this Oracle home (y|n) [n]:

ASM was not detected in the Oracle Home

######################### CHECK OPERATION END #########################

####################### CHECK OPERATION SUMMARY #######################

Oracle Grid Infrastructure Home is: /u01/oracle/product/11.2.0/grid

The cluster node(s) on which the Oracle home exists are: (Please input nodes seperated by “,”, eg: node1,node2,…)null

Oracle Home selected for de-install is: /u01/oracle/product/11.2.0/grid

Inventory Location where the Oracle home registered is: /u01/oraInventory

Following Oracle Restart enabled listener(s) will be de-configured: LISTENER

ASM was not detected in the Oracle Home

Do you want to continue (y – yes, n – no)? [n]: y

A log of this session will be written to: ‘/tmp/deinstall2010-04-28_07-49-16-AM/logs/deinstall_deconfig2010-04-28_07-51-00-AM.out’

Any error messages from this session will be written to: ‘/tmp/deinstall2010-04-28_07-49-16-AM/logs/deinstall_deconfig2010-04-28_07-51-00-AM.err’

######################## CLEAN OPERATION START ########################

ASM de-configuration trace file location: /tmp/deinstall2010-04-28_07-49-16-AM/logs/asmcadc_clean5696239145019448612.log

ASM Clean Configuration END

Network Configuration clean config START

Network de-configuration trace file location: /tmp/deinstall2010-04-28_07-49-16-AM/logs/netdc_clean6907918871439468084.log

De-configuring Oracle Restart enabled listener(s): LISTENER

De-configuring listener: LISTENER

    Stopping listener: LISTENER

    Listener stopped successfully.

    Unregistering listener: LISTENER

    Listener unregistered successfully.

    Deleting listener: LISTENER

    Listener deleted successfully.

Listener de-configured successfully.

De-configuring Listener configuration file…

Listener configuration file de-configured successfully.

De-configuring Naming Methods configuration file…

Naming Methods configuration file de-configured successfully.

De-configuring backup files…

Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

—————————————->

Run the following command as the root user or the administrator on node “devu009n3”.

/tmp/deinstall2010-04-28_07-49-16-AM/perl/bin/perl -I/tmp/deinstall2010-04-28_07-49-16-AM/perl/lib -I/tmp/deinstall2010-04-28_07-49-16-AM/crs/install /tmp/deinstall2010-04-28_07-49-16-AM/crs/install/roothas.pl -force  -delete -paramfile /tmp/deinstall2010-04-28_07-49-16-AM/response/deinstall_Ora11g_gridinfrahome1.rsp

Press Enter after you finish running the above commands

<—————————————-

devu009n3 $ ./tmp/deinstall2010-04-28_07-49-16-AM/perl/bin/perl -I

ksh: ./tmp/deinstall2010-04-28_07-49-16-AM/perl/bin/perl:  not found

devu009n3 $ -04-28_07-49-16-AM/response/deinstall_Ora11g_gridinfrahome1.rsp                                       <

2010-04-28 08:03:58: Checking for super user privileges

You must be logged in as root to run this script.

Log in as root and rerun this script.

2010-04-28 08:03:58: Not running as authorized user

Insufficient privileges to execute this script

devu009n3 $

devu009n3 # -49-16-AM/response/deinstall_Ora11g_gridinfrahome1.rsp           <

2010-04-28 08:32:59: Checking for super user privileges

2010-04-28 08:32:59: User has super user privileges

2010-04-28 08:32:59: Parsing the host name

Using configuration parameter file: /tmp/deinstall2010-04-28_07-49-16-AM/response/deinstall_Ora11g_gridinfrahome1.rsp

CRS-4133: Oracle High Availability Services has been stopped.

Successfully deconfigured Oracle Restart stack

<—————————————-

Oracle Universal Installer clean START

Detach Oracle home ‘/u01/oracle/product/11.2.0/grid’ from the central inventory on the local node : Done

Delete directory ‘/u01/oracle/product/11.2.0/grid’ on the local node : Done

Delete directory ‘/u01/oraInventory’ on the local node : Done

The Oracle Base directory ‘/u01/oracle’ will not be removed on local node. The directory is not empty.

Oracle Universal Installer cleanup was successful.

Oracle Universal Installer clean END

Oracle install clean START

Clean install operation removing temporary directory ‘/tmp//install’ on node ‘devu009n3’

Oracle install clean END

Moved default properties file /tmp/deinstall2010-04-28_07-49-16-AM/response/deinstall_Ora11g_gridinfrahome1.rsp as /tmp/deinstall2010-04-28_07-49-16-AM/response/deinstall_Ora11g_gridinfrahome1.rsp4

######################### CLEAN OPERATION END #########################

####################### CLEAN OPERATION SUMMARY #######################

Following Oracle Restart enabled listener(s) were de-configured successfully: LISTENER

Oracle Restart was already stopped and de-configured on node “devu009n3”

Oracle Restart is stopped and de-configured successfully.

Successfully detached Oracle home ‘/u01/oracle/product/11.2.0/grid’ from the central inventory on the local node.

Successfully deleted directory ‘/u01/oracle/product/11.2.0/grid’ on the local node.

Successfully deleted directory ‘/u01/oraInventory’ on the local node.

Oracle Universal Installer cleanup was successful.

Run ‘rm -rf /etc/oraInst.loc’ as root on node(s) ‘devu009n3’ at the end of the session.

Oracle install successfully cleaned up the temporary directories.

#######################################################################

############# ORACLE DEINSTALL & DECONFIG TOOL END #############

devu009n3 $

Posted in Oracle11gR2 GRID INFRA and ASM | Leave a Comment »

Error ORA-16627 operation disallowed since no standby databases

Posted by Hendry chinnapparaj on May 17, 2010

 

Error ORA-16627 operation disallowed since no standby databases

1.1     Initiate Switchover

 

On either the primary or the standby side start a DGMGRL session and issue the following:

DGMGRL> switchover to cpstest_f;

Performing switchover NOW, please wait…

Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode

Failed.

Unable to switchover, primary database is still “cpstest_j”

DGMGRL> show database verbose cpstest_j;

Database

  Name:            cpstest_j

  Role:            PRIMARY

  Enabled:         YES

  Intended State:  ONLINE

  Instance(s):

    cpstest

  Properties:

    InitialConnectIdentifier        = ‘cpstest_j’

    ObserverConnectIdentifier       = ”

    LogXptMode                      = ‘ASYNC

    Dependency                      = ”

    DelayMins                       = ‘0’

    Binding                         = ‘OPTIONAL’

    MaxFailure                      = ‘0’

    MaxConnections                  = ‘1’

    ReopenSecs                      = ‘300’

    NetTimeout                      = ‘180’

    LogShipping                     = ‘ON’

    PreferredApplyInstance          = ”

    ApplyInstanceTimeout            = ‘0’

    ApplyParallel                   = ‘AUTO’

    StandbyFileManagement           = ‘AUTO’

    ArchiveLagTarget                = ‘0’

    LogArchiveMaxProcesses          = ‘2’

    LogArchiveMinSucceedDest        = ‘1’

    DbFileNameConvert               = ”

    LogFileNameConvert              = ”

    FastStartFailoverTarget         = ”

    StatusReport                    = ‘(monitor)’

    InconsistentProperties          = ‘(monitor)’

    InconsistentLogXptProps         = ‘(monitor)’

    SendQEntries                    = ‘(monitor)’

    LogXptStatus                    = ‘(monitor)’

    RecvQEntries                    = ‘(monitor)’

    HostName                        = ‘devu009n3’

    SidName                         = ‘cpstest’

    LocalListenerAddress            = ‘(address=(protocol=TCP)(host=devu009n3)(port=1521))’

    StandbyArchiveLocation          = ‘?/dbs/arch’

    AlternateLocation               = ”

    LogArchiveTrace                 = ‘0’

    LogArchiveFormat                = ‘ARC%S_%R.%T’

    LatestLog                       = ‘(monitor)’

    TopWaitEvents                   = ‘(monitor)’

Current status for “cpstest_j”:

SUCCESS

DGMGRL> show database verbose cpstest_f;

Database

  Name:            cpstest_f

  Role:            PHYSICAL STANDBY

  Enabled:         YES

  Intended State:  ONLINE

  Instance(s):

    cpstest

  Properties:

    InitialConnectIdentifier        = ‘cpstest_f’

    ObserverConnectIdentifier       = ”

    LogXptMode                      = ‘SYNC’

    Dependency                      = ”

    DelayMins                       = ‘0’

    Binding                         = ‘OPTIONAL’

    MaxFailure                      = ‘0’

    MaxConnections                  = ‘1’

    ReopenSecs                      = ‘300’

    NetTimeout                      = ‘180’

    LogShipping                     = ‘ON’

    PreferredApplyInstance          = ”

    ApplyInstanceTimeout            = ‘0’

    ApplyParallel                   = ‘AUTO’

    StandbyFileManagement           = ‘AUTO’

    ArchiveLagTarget                = ‘0’

    LogArchiveMaxProcesses          = ‘2’

    LogArchiveMinSucceedDest        = ‘1’

    DbFileNameConvert               = ”

    LogFileNameConvert              = ”

    FastStartFailoverTarget         = ”

    StatusReport                    = ‘(monitor)’

    InconsistentProperties          = ‘(monitor)’

    InconsistentLogXptProps         = ‘(monitor)’

    SendQEntries                    = ‘(monitor)’

    LogXptStatus                    = ‘(monitor)’

    RecvQEntries                    = ‘(monitor)’

    HostName                        = ‘devu009n4’

    SidName                         = ‘cpstest’

    LocalListenerAddress            = ‘(address=(protocol=TCP)(host=devu009n4)(port=1521))’

    StandbyArchiveLocation          = ‘?/dbs/arch’

    AlternateLocation               = ”

    LogArchiveTrace                 = ‘0’

    LogArchiveFormat                = ‘ARC%S_%R.%T’

    LatestLog                       = ‘(monitor)’

    TopWaitEvents                   = ‘(monitor)’

Current status for “cpstest_f”:

SUCCESS

DGMGRL> edit database cpstest_j set property LogXptMode=’SYNC’;

Property “logxptmode” updated

DGMGRL> show database verbose cpstest_j;

Database

  Name:            cpstest_j

  Role:            PRIMARY

  Enabled:         YES

  Intended State:  ONLINE

  Instance(s):

    cpstest

  Properties:

    InitialConnectIdentifier        = ‘cpstest_j’

    ObserverConnectIdentifier       = ”

    LogXptMode                      = ‘SYNC

    Dependency                      = ”

    DelayMins                       = ‘0’

DGMGRL> switchover to cpstest_j;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_f”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_j”

DGMGRL> show configuration;

Configuration

  Name:                cpstest_dgb

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    cpstest_j – Primary database

    cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

Posted in Data Guard and DR | Leave a Comment »

Data Guard Broker Configuration on an existing DataGuard Env

Posted by Hendry chinnapparaj on May 17, 2010

 

Data Guard Broker Configuration on an existing DataGuard Env

 

1        Introduction

 

1.1     Purpose & Scope

This document is intended as a step by step Dataguard Configuration & Operation Guide between CPSTEST Prod Primary Servers & CPSTEST Prod 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)

·       Storage: Veritas Cluster Services

·       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

 

  • Pre-requisite for using Data Guard Broker
  • Creating Configuration
  • Post Configuration Steps
  • Performing a Switchover 

 

3        Pre-requisite for using Data Guard Broker

The following conditions must be true before you can use the broker:

3.1     Pre-create the standby database manually

Refer to oracle10gR2 DataGuard configuration and setup post on this blog

3.2      Define Broker Configuration Files

Two copies of the configuration file are maintained for each database so as to always have a record of the last known valid state of the configuration.

DEVU009N3

SQL>  alter system set dg_broker_config_file1=’/u01/oracle/product/10.2.0/db_1/dbs/dr1cpstest_j.dat’;

System altered.

SQL> alter system set dg_broker_config_file2=’/u01/oracle/product/10.2.0/db_1/dbs/dr2cpstest_j.dat’;

System altered.

DEVU009N4

SQL> alter system set dg_broker_config_file1=’/u01/oracle/product/10.2.0/db_1/dbs/dr1cpstest_f.dat’;

System altered.

SQL> alter system set dg_broker_config_file2=’/u01/oracle/product/10.2.0/db_1/dbs/dr2cpstest_f.dat’;

System altered.

3.3     Starting the Data Guard Broker

After setting up the configuration files, the DG_BROKER_START initialization parameter must be set to TRUE for each database to start the DMON processes.

DEVU009N3

SQL> alter system set DG_BROKER_START=TRUE;

System altered.

DEVU009N4

SQL> alter system set DG_BROKER_START=TRUE;

System altered.

3.4     Configure Network Aliases

Oracle Net Services network files must be set up on the primary database and on the standby database if you configure an existing standby database into the broker configuration.

Add the following entries in TNSNAMES.ORA of Primary as well as standby database.

DEVU009N3 & DEVU009N4

cpstest_j =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = cpstest_j)

    )

  )

cpstest_f =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = cpstest_f)

    )

  )

3.5     Static registration with Local Listener

To enable DGMGRL to restart instances during the course of broker operations, a service with a specific name must be statically registered with the local listener of each instance. The value for the GLOBAL_DBNAME attribute must be set to a concatenation of db_unique_name_DGMGRL.db_domain.

Add the following entries in LISTENER.ORA file.

DEVU009N3

SID_LIST_LISTENER =

    (SID_LIST =

      (SID_DESC =

        (GLOBAL_DBNAME = cpstest_j_dgmgrl)

        (ORACLE_HOME = /u01/oracle/product/11.2.0/grid)

        (SID_NAME = cpstest)

      )

    )

 

 

DEVU009N4

SID_LIST_LISTENER =

    (SID_LIST =

      (SID_DESC =

        (GLOBAL_DBNAME = cpstest_f_dgmgrl)

        (ORACLE_HOME = /u01/oracle/product/11.2.0/grid)

        (SID_NAME = cpstest)

      )

    )

3.6     Creating Data Guard Broker Configuration

 

The following steps show how to create a configuration and add one physical standby database. We can create and manage the configuration from any of the node, Lets do it from DEVU009N3.

Step 1 Invoke DGMGRL.

devu009n3 $ pwd

/u01/oracle

devu009n3 $ dgmgrl

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

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

Welcome to DGMGRL, type “help” for information.

Step 2 Connect to the primary database

DGMGRL> connect sys/cpstest

Connected.

 Note:- connecting as sys is important as it needs to communicate to the other server

Step 3 Create the broker configuration (Note: content in quotes is case sensitive)

 

DGMGRL> CREATE CONFIGURATION ‘cpstest_dgb’ AS PRIMARY DATABASE IS ‘cpstest_j’  CONNECT IDENTIFIER IS ‘cpstest_j’;

Configuration “CPSTEST_DGB” created with primary database “CPSTEST_J”

Step 4 Add a standby database to the configuration

 

ADD DATABASE ‘cpstest_f’ AS CONNECT IDENTIFIER IS ‘cpstest_f’ MAINTAINED AS PHYSICAL;

DGMGRL> ADD DATABASE ‘cpstest_f’ AS CONNECT IDENTIFIER IS ‘cpstest_f’ MAINTAINED AS PHYSICAL;

Database “cpstest_f” added

Step 5 Verify the Configuration

DGMGRL> show configuration;

Configuration

  Name:                cpstest_dgb

  Enabled:             NO

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    cpstest_j – Primary database

    cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

DISABLED

Step 6 Enable Configuration

 

DGMGRL> enable configuration

Enabled.

 

Step 7 Verify Configuration

 

DGMGRL> show configuration;

Configuration

  Name:                cpstest_dgb

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    cpstest_j – Primary database

    cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS


4        Post Configuration steps

4.1     Enable Force Logging

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement on DEVU009N3.

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

4.2     Creating Standby redo log files

Add the Standby redo log files to Primary (DEVU009N3) as well as Standby (DEVU009N4).

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

    GROUP#    THREAD#    MEMBERS

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

         1          1          2

         2          1          2

         3          2          2

         4          2          2

         5          1          2

         6          1          2

         7          2          2

         8          2          2

8 rows selected.

SQL>

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%’;

4.3     Upgrade Protection Mode if already not done

DEVU009N3

DGMGRL> EDIT DATABASE ‘cpstest_j’ SET PROPERTY ‘StandbyFileManagement’=’AUTO’;

Property “StandbyFileManagement” updated.

DGMGRL> EDIT DATABASE ‘cpstest_j’ SET PROPERTY ‘LogXptMode’=’SYNC’;

Property “LogXptMode” updated

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

DEVU009N4

DGMGRL> EDIT DATABASE ‘cpstest_f’ SET PROPERTY ‘StandbyFileManagement’=’AUTO’;

Property “StandbyFileManagement” updated.

DGMGRL> EDIT DATABASE ‘cpstest_f’ PROPERTY ‘LogXptMode’=’SYNC’;

Property “LogXptMode” updated

4.4     Set Desired RMAN Archivelog Retention Policy

 

DEVU009N4

 

Oracle 10g can manage the deletion of archivelogs on the Standby side of a DataGuard setup. This is done by setting the below policy in a RMAN session into the standby database:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

Please note that whenever switchover or failover is carried out unless only for a short time this setting should be toggled between “NONE” and “APPLIED ON STANDBY” on the primary and standby sides respectively.


5        Performing a Switchover

5.1     Pre-requisites for Switchover

 

  • Check DGMGRL for indication of a healthy DataGuard setup again – i.e. “SUCCESS” – this may take a few minutes for this to be returned.

 

DGMGRL> show configuration

Configuration

  Name:                cpsetst_dgb

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    CPSTEST_J – Primary database

    CPSTEST_F – Physical standby database

Current status for “CPSTEST_dgb”:

SUCCESS

5.2     Initiate Switchover

 

On either the primary or the standby side start a DGMGRL session and issue the following:

devu009n4 $ dgmgrl

DGMGRL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – 64bit Production

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

Welcome to DGMGRL, type “help” for information.

DGMGRL> connect sys/cpstest

Connected.

DGMGRL> show configuration;

Configuration

  Name:                cpstest_dgb

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    cpstest_j – Physical standby database

    cpstest_f – Primary database

Current status for “cpstest_dgb”:

SUCCESS

DGMGRL> switchover to cpstest_j;

Performing switchover NOW, please wait…

Operation requires shutdown of instance “cpstest” on database “cpstest_f”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires shutdown of instance “cpstest” on database “cpstest_j”

Shutting down instance “cpstest”…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “cpstest” on database “cpstest_f”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Operation requires startup of instance “cpstest” on database “cpstest_j”

Starting instance “cpstest”…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “cpstest_j”

DGMGRL> show configuration;

Configuration

  Name:                cpstest_dgb

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Fast-Start Failover: DISABLED

  Databases:

    cpstest_j – Primary database

    cpstest_f – Physical standby database

Current status for “cpstest_dgb”:

SUCCESS

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE    OPEN_MODE

—————- ———-

PHYSICAL STANDBY MOUNTED

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

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

devu009n4 $

on new Primary now, query

SELECT INST_NAME, HOST_NAME, PROTECTION_MODE, SYNCHRONIZATION_STATUS,

             LOG_ARCHIVED, LOG_APPLIED, TIME_APPLIED,

             LOG_ARCHIVED – LOG_APPLIED LOG_GAP

        FROM (SELECT   INST_ID, INSTANCE_NAME INST_NAME, HOST_NAME

                  FROM GV$INSTANCE

              ORDER BY INST_ID) NAME,

             (SELECT   INST_ID, PROTECTION_MODE, SYNCHRONIZATION_STATUS

                  FROM GV$ARCHIVE_DEST_STATUS

                 WHERE DEST_ID = 2

              ORDER BY INST_ID) STAT,

             (SELECT   THREAD#, MAX (SEQUENCE#) LOG_ARCHIVED

                  FROM V$ARCHIVED_LOG

                 WHERE DEST_ID = 1

                   AND ARCHIVED = ‘YES’

                   AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

                                         FROM V$ARCHIVED_LOG

                                        WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

              GROUP BY THREAD#

              ORDER BY THREAD#) ARCH,

             (SELECT   THREAD#, MAX (SEQUENCE#) LOG_APPLIED,

                       TO_CHAR (MAX (COMPLETION_TIME),

                                ‘DD/MM HH24:MI:SS’

                               ) TIME_APPLIED

                  FROM V$ARCHIVED_LOG

                 WHERE DEST_ID = 2

                   AND APPLIED = ‘YES’

                   AND RESETLOGS_ID = (SELECT MAX (RESETLOGS_ID)

                                         FROM V$ARCHIVED_LOG

                                        WHERE DEST_ID = 1 AND ARCHIVED = ‘YES’)

              GROUP BY THREAD#

              ORDER BY THREAD#) APPL

       WHERE NAME.INST_ID = STAT.INST_ID

         AND NAME.INST_ID = ARCH.THREAD#

         AND NAME.INST_ID = APPL.THREAD#;

INST_NAME

—————-

HOST_NAME

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

PROTECTION_MODE      SYNCHRONIZATION_STATUS LOG_ARCHIVED LOG_APPLIED

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

TIME_APPLIED      LOG_GAP

————– ———-

cpstest

devu009n3

MAXIMUM AVAILABILITY OK                              105         105

14/05 11:34:24          0

Query to ensure that the datafiles are online

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

no 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     98

Next log sequence to archive   101

Current log sequence           101

Query the standby

select * from v$dataguard_status

FACILITY                 SEVERITY         DEST_ID MESSAGE_NUM ERROR_CODE CAL

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

TIMESTAMP

———

MESSAGE

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

14-MAY-10

Standby controlfile consistent with primary

Remote File Server       Warning                0          49          0 NO

14-MAY-10

RFS[1]: Successfully opened standby log 6: ‘+DATA/cpstest_j/onlinelog/group_6.15

91.718685471’

FACILITY                 SEVERITY         DEST_ID MESSAGE_NUM ERROR_CODE CAL

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

TIMESTAMP

———

MESSAGE

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

Log Apply Services       Warning                0          50          0 NO

14-MAY-10

Media Recovery Waiting for thread 1 sequence 101 (in transit)

50 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     99

Next log sequence to archive   0

Current log sequence           101

5.3     Post Switchover

 

If switchover or failover is carried out and not intended to be reverted within a few hours the RMAN archivelog deletion policy setting should be changed on both databases. The following indicates which setting is correct for each side:

Primary Side:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

Standby Side:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;


6        Opening a Standby Database for Read only

 

You might want to use your physical standby database temporarily for reporting applications. To change the state of the standby database to read-only.

DGMGRL> EDIT DATABASE ‘cpstest_f’ SET STATE=’READ-ONLY’;

Succeeded.

Note:- Opening the standby database in readonly may not be possible if you have turned on auditing which is seen from audit_trail=db init.ora parameter. You have to change the value to open in readonly.

Posted in Data Guard and DR | Leave a Comment »