Thinking Out Loud

October 28, 2017

Use ORACLE_UNQNAME for DataGuard Environment

Filed under: 11g,Dataguard — mdinh @ 2:25 pm

If you are running only 1 database on the host, then it may not be useful.

However, if you run multiple databases, then it makes it easier to automate provided there are consistencies and/or conventions.

DB configuration

HOST01:(SYS@qa):PHYSICAL STANDBY> show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      qa
db_unique_name                       string      qadr

OS configuration

$ env|grep ORACLE
ORACLE_BASE=/u01/app/oracle
ORACLE_SID=qa
ORACLE_UNQNAME=qadr
ORACLE_HOME=/u01/app/oracle/db/11g
$ ps -ef|grep pmon
  oracle  9896050        1   0 16:11:12      -  0:03 asm_pmon_+ASM
  oracle 10354862        1   0 20:06:31      -  0:02 ora_pmon_qa

Check DB status using srvctl

srvctl status database -d $ORACLE_UNQNAME -v
Database qadr is running with online services qarosvc
#!/bin/sh -e
. /opt/oracle/oracle_qa_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
exit
$ ./d.sh
DGMGRL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration

Configuration - dgqa

  Protection Mode: MaxPerformance
  Databases:
    qa   - Primary database
    qadr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database qa

Database - qa

  Enterprise Manager Name: qa_cluster
  Role:                    PRIMARY
  Intended State:          TRANSPORT-ON
  Instance(s):
    qa_1
    qa_2

Database Status:
SUCCESS

DGMGRL> show database qadr

Database - qadr

  Enterprise Manager Name: qa1
  Role:                    PHYSICAL STANDBY
  Intended State:          APPLY-ON
  Transport Lag:           0 seconds (computed 0 seconds ago)
  Apply Lag:               0 seconds (computed 1 second ago)
  Apply Rate:              937.00 KByte/s
  Real Time Query:         ON
  Instance(s):
    qa

Database Status:
SUCCESS

DGMGRL> exit

crsctl stat res -w “STATE = ONLINE”|egrep “db$|TYPE=ora.database.type”

NAME=ora.qadr.db
TYPE=ora.database.type
NAME=ora.qa2dr.db
TYPE=ora.database.type
NAME=ora.stageqadr.db
TYPE=ora.database.type
NAME=ora.testdr.db
TYPE=ora.database.type

dg_show.sh

#!/bin/sh -e
. /opt/oracle/oracle_qa_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
. /opt/oracle/oracle_qa2_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
. /opt/oracle/oracle_stageqa_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
. /opt/oracle/oracle_test_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
exit

Improved dg_show.sh using function.

#!/bin/sh -e
check_dg()
{
  dgmgrl -echo << END
  connect /
  show configuration
  show database ${ORACLE_SID}
  show database ${ORACLE_UNQNAME}
  exit
  END
}
. /opt/oracle/oracle_qa_env
check_dg
. /opt/oracle/oracle_qa2_env
check_dg
. /opt/oracle/oracle_stageqa_env
check_dg
. /opt/oracle/oracle_test_env
check_dg
exit
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: