Thinking Out Loud

August 21, 2018

RMAN: Synchronize standby database using production archivelog

Filed under: 11g,Dataguard,RMAN — mdinh @ 11:39 pm

I know what you are thinking, “Why is this nut of a DBA writing shell script to synchronize standby with achivelog !”

It just happens the environment is very restrictive and NO changes can be made without any change control.

In one week, there’s a planned switchover to RAC standby and it would be nice to have standby duplicated and ready for switchover.

How is this going to work as standby will lag for days until switchover?

Have no fear, there’s a script for that.

# Primary archivelog resides on shared folder with Standby.
# MANAGED REAL TIME APPLY is running.
PRI: /shared/prod/DB01/arch/
SBY: /shared/backup/arch/DB01/

#!/bin/sh 
# rman_cat_arc.sh
# Michael Dinh Aug 21, 2018
#
# Don't forget to set environment here.
#
set -x
# list 5 most recent achivelog
ls -lrt /shared/prod/DB01/arch/|tail -5
# copy archivelog created in last 1 hour since cron runs script every 1 hour.
/bin/find /shared/prod/DB01/arch/ -type f -mmin -60 -exec cp -ufv {} /shared/backup/arch/DB01/ \;
# 
rman msglog /tmp/rman_cat_arc.log > /dev/null << EOF
set echo on;
connect target;
# delete achivelog older than 3 hours
delete force noprompt archivelog until time 'sysdate-3/24';
catalog start with '/shared/backup/arch/DB01/' noprompt;
EOF
# Review alert log
tail -20f $ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log
exit
 
# crontab
26 * * * * /home/oracle/rman_cat_arc.sh > /tmp/rman_cat_arc.sh.out 2>&1

# logs
$ ll /tmp/rman*
-rw-r--r--. 1 oracle oinstall 2664 Aug 21 11:26 /tmp/rman_cat_arc.log
-rw-r--r--. 1 oracle oinstall 1852 Aug 21 11:26 /tmp/rman_cat_arc.sh.out

# alert log 
Tue Aug 21 08:26:09 2018
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86391.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86392.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86393.arc
Media Recovery Waiting for thread 1 sequence 86394
Tue Aug 21 09:26:06 2018
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86394.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86395.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86396.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86397.arc
Media Recovery Waiting for thread 1 sequence 86398
Tue Aug 21 10:26:08 2018
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86398.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86399.arc
Media Recovery Waiting for thread 1 sequence 86400
Tue Aug 21 11:26:06 2018
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86400.arc
Media Recovery Log /shared/backup/arch/DB01/DB01_1_717897269_86401.arc
Media Recovery Waiting for thread 1 sequence 86402
Tue Aug 21 11:49:03 2018

select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
from gv$managed_standby
where 1=1
and status not in ('CLOSING','IDLE','CONNECTED')
order by status desc, thread#, sequence#
;

# Manual recovery: WAIT_FOR_LOG and BLOCK#=0 and never increment.
*** gv$managed_standby ***
                        CLIENT                                               DELAY
     PID  INST  THREAD# PROCESS    PROCESS  STATUS       SEQUENCE#   BLOCK#   MINS
-------- ----- -------- ---------- -------- ------------ --------- -------- ------
  411795     4        1 N/A        MRP0     WAIT_FOR_LOG     86178        0      0
  
# MANAGED REAL TIME APPLY: APPLYING_LOG and BLOCK#>0 increments
*** gv$managed_standby ***
                        CLIENT                                               DELAY
     PID  INST  THREAD# PROCESS    PROCESS  STATUS       SEQUENCE#   BLOCK#   MINS
-------- ----- -------- ---------- -------- ------------ --------- -------- ------
  245652     4        1 N/A        MRP0     APPLYING_LOG     86410      472      0  
Advertisements

May 11, 2018

DataGuard Convention

Filed under: Dataguard,dgmgrl — mdinh @ 11:58 am

Good convention and implementation make life and automation so much simpler and more time for golfing.

I have seen some really poor and really good implementation and here’s a good one.

Wish I can take credit for it and unfortunately I cannot.

The scripts were created by whoa.

Scripts an be run from primary or standby for any instances provided profile to source database environment exists on host.

Use ORACLE_UNQNAME for DataGuard Environment

====================================================================================================
+++ PRIMARY RACONENODE
====================================================================================================
SQL> show parameter db%name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
SQL> 

$ sysresv|tail -1
Oracle Instance alive for sid "test_1"

$ env|grep ORACLE

ORACLE_SID=test_1 (db_name)
ORACLE_UNQNAME=test (db_unique_name)

$ srvctl config database -d $ORACLE_UNQNAME
Database unique name: test
Database name: test
Oracle home: /u01/app/oracle/product/11g/db_1
Oracle user: oracle
Spfile: +FLASH/test/spfiletest.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: test
Database instances:
Disk Groups: FLASH,DATA
Mount point paths:
Services: testsvc
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: test
Candidate servers: host01,host02
Database is administrator managed

====================================================================================================
+++ STANDBY NON-RAC
====================================================================================================
SQL> show parameter db%name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      testdr
SQL> 

$ sysresv|tail -1
Oracle Instance alive for sid "test"

$ env|grep ORACLE
ORACLE_SID=test (db_name)
ORACLE_UNQNAME=testdr (db_unique_name)

$ srvctl config database -d $ORACLE_UNQNAME
Database unique name: testdr
Database name: test
Oracle home: /u01/app/oracle/product/11g/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Database instance: test
Disk Groups: DATA,FLASH
Services:

====================================================================================================
DATAGUARD BROKER CONFIGURATION
====================================================================================================
DGMGRL> show configuration

Configuration - dg_test (db_name)

  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database (db_unique_name)
    testdr - Physical standby database (db_unique_name)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database test

Database - test

  Role:            PRIMARY
  Intended State:  TRANSPORT-OFF
  Instance(s):
    test_1
    test_2

Database Status:
SUCCESS

DGMGRL> show database testdr

Database - testdr

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       7 seconds (computed 0 seconds ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    test

Database Status:
SUCCESS

DGMGRL> exit

====================================================================================================
ls -l dg*.sh
====================================================================================================
-rwxr-xr-x    1 oracle   dba             377 May 08 21:50 dg_lag.sh
-rwxr-x---    1 oracle   dba             445 May 08 20:12 dg_start.sh
-rwxr-xr-x    1 oracle   dba             337 May 08 20:05 dg_status.sh
-rwxr-x---    1 oracle   dba             447 May 08 20:12 dg_stop.sh

====================================================================================================
dg_lag.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
show database ${ORACLE_SID} SendQEntries
show database ${ORACLE_UNQNAME} RecvQEntries
show database ${ORACLE_UNQNAME}
exit
END
}
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

====================================================================================================
cat dg_start.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
edit database ${ORACLE_SID} set state='TRANSPORT-ON';
edit database ${ORACLE_UNQNAME} set state='APPLY-ON';
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
}
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

====================================================================================================
dg_status.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
}
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

====================================================================================================
dg_stop.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
edit database ${ORACLE_SID} set state='TRANSPORT-OFF';
edit database ${ORACLE_UNQNAME} set state='APPLY-OFF';
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
}
check_dg
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit


March 3, 2018

Upgrade 12.2 Journey – DataGuard

Filed under: 12.2,Dataguard,upgrade,Upgrade 12.2 Journey — mdinh @ 6:07 pm

Oracle Data Guard Broker Changes in Oracle Database 12c Release 2 (12.2.0.1)

How to resolve MRP stuck issues on a physical standby database? (Doc ID 1221163.1)
Starting from 12.2 use V$DATAGUARD_PROCESS view instead of v$managed_standby

February 19, 2018

How Do You Create Data Guard Configuration?

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 11:30 pm

I have taken for granted to create Data Guard Configuration the same way most of the time that I don’t know what goes wrong when done differently.

oracle@racnode-dc1-1:hawk1:/home/oracle
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 19 23:41:49 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

23:41:49 SYS @ hawk1:>show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      hawk
db_unique_name                       string      hawk
pdb_file_name_convert                string

23:42:04 SYS @ hawk1:>alter system set dg_broker_start=true sid='*' scope=memory;

System altered.

23:42:40 SYS @ hawk1:>


+++ CREATE CONFIGURATION USING UPPER CASE WITHOUT QUOTES

oracle@racnode-dc1-1:hawk1:/home/oracle
$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.

--- NO QUOTES USE AND ALL UPPERCASE - EASIEST METHOD
--- Broker convert database to match that of db_unique_name

DGMGRL> CREATE CONFIGURATION DG_CONFIG AS PRIMARY DATABASE IS HAWK CONNECT IDENTIFIER IS HAWK;
Configuration "dg_config" created with primary database "hawk"

DGMGRL> show configuration

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database hawk

Database - hawk

  Role:               PRIMARY
  Intended State:     OFFLINE
  Instance(s):
    hawk1
    hawk2

Database Status:
DISABLED

DGMGRL> remove configuration
Removed configuration

--- CONFIGURATION IS UPPERCASE 
--- Does it look better in uppercase?
DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS PRIMARY DATABASE IS 'hawk' CONNECT IDENTIFIER IS HAWK;
Configuration "DG_CONFIG" created with primary database "hawk"
DGMGRL> show configuration

Configuration - DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
  hawk - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database hawk

Database - hawk

  Role:               PRIMARY
  Intended State:     OFFLINE
  Instance(s):
    hawk1
    hawk2

Database Status:
DISABLED

DGMGRL> remove configuration
Removed configuration

+++ ISSUES OCCUR WHEN USING UPPERCASE WITH QUOTES FOR DATABASE
+++ Not sure if this will work as I have not tested end to end. Why create it this way to begin with?
DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS PRIMARY DATABASE IS 'HAWK' CONNECT IDENTIFIER IS HAWK;
Configuration "DG_CONFIG" created with primary database "HAWK"
DGMGRL> show configuration

Configuration - DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
  HAWK - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database hawk
Object "hawk" was not found

DGMGRL> show database HAWK
Object "hawk" was not found

DGMGRL> show database 'HAWK';

Database - HAWK

  Role:               PRIMARY
  Intended State:     OFFLINE
  Instance(s):
    hawk1
    hawk2

Database Status:
DISABLED

DGMGRL>

REFERENCE:

CREATE CONFIGURATION

CREATE CONFIGURATION configuration_name AS PRIMARY DATABASE IS database-name CONNECT IDENTIFIER IS connect-identifier;

database-name
The name that will be used by the broker to refer to the primary database. 
It must match (case-insensitive) the value of the primary database DB_UNIQUE_NAME initialization parameter.

February 15, 2018

12c DataGuard / Broker Pitfalls

Filed under: 12c,Dataguard — mdinh @ 1:01 pm

In a broker configuration, you use the DGConnectIdentifer property to specify a connect identifier for each database.

The connect identifier for a database must:
Allow all other databases in the configuration to reach it.
Allow all instances of an Oracle RAC database to be reached.
Specify a service that all instances dynamically register with the listeners so that connect-time failover on an Oracle RAC database is possible.

The service should NOT be one that is defined and managed by Oracle Clusterware.

A static service needs to be defined and registered only if Oracle Clusterware or Oracle Restart is not being used.

Else, by default, the broker assumes a static service name of db_unique_name_DGMGRL.db_domain and expects the listener has been started with the following content in the listener.ora file:

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = port_num))
  )
)

SID_LIST_LISTENER=
(SID_LIST=
  (SID_DESC=
    (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
    (ORACLE_HOME=oracle_home)
    (SID_NAME=sid_name)
    (ENVS="TNS_ADMIN=oracle_home/network/admin")
  )
)  

As of Oracle Database 12c Release 1 (12.1), for all databases to be added to a broker configuration, any LOG_ARCHIVE_DEST_n parameters that have the SERVICE attribute set, but not the NOREGISTER attribute, must be cleared.

Create Configuration Failing with ORA-16698 (Doc ID 1582179.1)

Oracle Data Guard Installation

 

November 2, 2017

Monitoring Standby – SQLPlus or DGMGRL

Filed under: Dataguard — mdinh @ 3:00 am

Here is an example using dgmgrl


DGMGRL> show database roverdb SendQEntries
PRIMARY_SEND_QUEUE
        STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs) 
           roverstby     ARCHIVED        936921167                1                13019  10/31/2017 10:47:04  10/31/2017 10:48:31     746413367424     746413483999          1819004 
           roverstby     ARCHIVED        936921167                1                13023  10/31/2017 10:51:40  10/31/2017 10:52:19     746413767648     746413883688          1809094 
           roverstby     ARCHIVED        936921167                1                13031  10/31/2017 10:57:02  10/31/2017 10:57:44     746414728981     746414851377          1924909 
           roverstby     ARCHIVED        936921167                1                13032  10/31/2017 10:57:44  10/31/2017 10:58:23     746414851377     746414967877          1815042 
           roverstby     ARCHIVED        936921167                1                13033  10/31/2017 10:58:23  10/31/2017 10:59:02     746414967877     746415089206          1798857 
           roverstby     ARCHIVED        936921167                1                13034  10/31/2017 10:59:02  10/31/2017 10:59:41     746415089206     746415217514          1818919 
                          CURRENT        936921167                1                13036  10/31/2017 11:29:41                          746415239037                               628 
                          CURRENT        936921167                2                12359  10/31/2017 11:29:05                          746415238854                               864 

DGMGRL> show database roverstby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs) 
         NOT_APPLIED        936921167                1                13020  10/31/2017 10:48:31  10/31/2017 10:48:49     746413483999     746413509640           385949 
         NOT_APPLIED        936921167                1                13021  10/31/2017 10:48:49  10/31/2017 10:50:19     746413509640     746413636246          1885417 
         NOT_APPLIED        936921167                1                13022  10/31/2017 10:50:19  10/31/2017 10:51:40     746413636246     746413767648          1944637 
         NOT_APPLIED        936921167                1                13024  10/31/2017 10:52:19  10/31/2017 10:52:58     746413883688     746413999759          1819116 
         NOT_APPLIED        936921167                1                13025  10/31/2017 10:52:58  10/31/2017 10:53:40     746413999759     746414124264          1868420 
         NOT_APPLIED        936921167                1                13026  10/31/2017 10:53:40  10/31/2017 10:54:22     746414124264     746414244619          1890478 
         NOT_APPLIED        936921167                1                13027  10/31/2017 10:54:22  10/31/2017 10:55:02     746414244619     746414363387          1843514 
         NOT_APPLIED        936921167                1                13028  10/31/2017 10:55:02  10/31/2017 10:55:41     746414363387     746414484244          1818826 
         NOT_APPLIED        936921167                1                13029  10/31/2017 10:55:41  10/31/2017 10:56:20     746414484244     746414605367          1813344 
         NOT_APPLIED        936921167                1                13030  10/31/2017 10:56:20  10/31/2017 10:57:02     746414605367     746414728981          1904385 
         NOT_APPLIED        936921167                1                13035  10/31/2017 10:59:41  10/31/2017 11:29:41     746415217514     746415239037            79395 
   PARTIALLY_APPLIED        936921167                2                12352  10/31/2017 10:40:04  10/31/2017 10:47:07     746413130730     746413371576             1980 
         NOT_APPLIED        936921167                2                12353  10/31/2017 10:47:07  10/31/2017 10:50:22     746413371576     746413640990             1658 
         NOT_APPLIED        936921167                2                12354  10/31/2017 10:50:22  10/31/2017 10:53:01     746413640990     746414010894             1774 
         NOT_APPLIED        936921167                2                12355  10/31/2017 10:53:01  10/31/2017 10:55:04     746414010894     746414371654             1541 
         NOT_APPLIED        936921167                2                12356  10/31/2017 10:55:04  10/31/2017 10:57:04     746414371654     746414736501             1532 
         NOT_APPLIED        936921167                2                12357  10/31/2017 10:57:04  10/31/2017 10:59:04     746414736501     746415097318             1485 
         NOT_APPLIED        936921167                2                12358  10/31/2017 10:59:04  10/31/2017 11:29:05     746415097318     746415238854             6101 

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

September 26, 2017

Dedicated Network for DataGuard

Filed under: Dataguard — mdinh @ 12:50 pm

Just some notes.

Using a separate network for DataGuard in 12c RAC
https://dbamarco.wordpress.com/2015/04/15/using-a-separate-network-for-dataguard-in-12c-rac/

PUTTING DATA GUARD TRAFFIC ON DEDICATED NETWORK INTERFACE
https://ilmarkerm.eu/blog/2015/07/putting-data-guard-traffic-on-dedicated-network-interface/

How configure Multiples Public Network in a Grid Infrastructure 11g R2 (11.2) environment
https://levipereira.wordpress.com/2011/10/22/how-configure-multiples-public-network-an-grid-infrastructure-11g-r2-11-2-environment/

September 20, 2017

RMAN Backup from Standby w Recovery Catalog Part 2

Filed under: Dataguard,RMAN — mdinh @ 11:15 pm

RMAN Backup from Standby w Recovery Catalog

What! There’s a part 2?

FRA from primary was getting full since archivelog deletion was not working.

  1. Why is there a need to delete archivelog since FRA performs clean up?
  2. If FRA performs clean up then why is the destination full?
  3. Go to 1.

Reminds me of, “Who’s on first, What’s on second, I Don’t Know is on third”

Notice the old configuration.

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

Archivelog deletion.

connect target;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
list archivelog all;
delete noprompt archivelog until time 'sysdate-7/24';
list archivelog all;

Archivelog deleted.


archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31831.359.955164907 RECID=87186 STAMP=955164906
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31832.886.955166705 RECID=87189 STAMP=955166705
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31833.594.955168505 RECID=87191 STAMP=955168504
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31834.411.955170305 RECID=87193 STAMP=955170304
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31835.418.955172107 RECID=87195 STAMP=955172106
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31836.448.955173905 RECID=87197 STAMP=955173906
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31837.438.955175705 RECID=87199 STAMP=955175705
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31838.811.955176699 RECID=87201 STAMP=955176703
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31839.922.955178497 RECID=87203 STAMP=955178499
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31840.468.955180295 RECID=87205 STAMP=955180294
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31841.910.955182097 RECID=87207 STAMP=955182097
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31842.830.955183897 RECID=87209 STAMP=955183897
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31843.986.955185697 RECID=87211 STAMP=955185696
deleted archived log
archived log file name=+FRA/QA/ARCHIVELOG/2017_09_20/thread_1_seq_31844.879.955186507 RECID=87212 STAMP=955186506
Deleted 14 objects

OH SH*T

RMAN> connect target;

connected to target database: QA (DBID=3476258591)

RMAN> connect catalog cat@rman

recovery catalog database Password:
connected to recovery catalog database

RMAN> list backup of archivelog from logseq=31831 until logseq=31844 thread=1 summary;

specification does not match any backup in the repository

Sign of JOY!

RMAN> list backup of archivelog from logseq=31831 until logseq=31844 thread=1 summary for db_unique_name all;


specification does not match any backup in the repository

List of Backups for database with db_unique_name QADR
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
812913  B  A  A DISK        2017-09-20:05:43:11 1       1       YES        DAILY_ARCHLOG_BKUP
812915  B  A  A DISK        2017-09-20:05:43:30 1       1       YES        DAILY_ARCHLOG_BKUP
813550  B  A  A DISK        2017-09-20:09:36:44 1       1       YES        DAILY_ARCHLOG_BKUP
813551  B  A  A DISK        2017-09-20:09:37:55 1       1       YES        DAILY_ARCHLOG_BKUP
813559  B  A  A DISK        2017-09-20:09:41:33 1       1       YES        DAILY_ARCHLOG_BKUP
813570  B  A  A DISK        2017-09-20:09:45:17 1       1       YES        DAILY_ARCHLOG_BKUP
814104  B  A  A DISK        2017-09-20:13:36:44 1       1       YES        DAILY_ARCHLOG_BKUP
814105  B  A  A DISK        2017-09-20:13:37:55 1       1       YES        DAILY_ARCHLOG_BKUP
814112  B  A  A DISK        2017-09-20:13:41:05 1       1       YES        DAILY_ARCHLOG_BKUP
814114  B  A  A DISK        2017-09-20:13:41:45 1       1       YES        DAILY_ARCHLOG_BKUP

RMAN> show all;

RMAN configuration parameters for database with db_unique_name QADR are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;

Wondering if
CONFIGURE DEFAULT DEVICE TYPE TO DISK
had anything to do with
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO ‘SBT_TAPE’ APPLIED ON ALL STANDBY
not working?

You might ask, why not just use “CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY” only.

That depends if you want accuracy or not.

Was the requirement backup to tape or to disk?

If the requirement is backup to tape, feel lucky FRA got full; otherwise, there’s a false pretense of good backup going to tape.

 

RMAN Backup from Standby w Recovery Catalog

Filed under: Dataguard,RMAN,standby — mdinh @ 1:05 am

Apologies as this is not a very clean post as there is too much info to disseminate.

Hopefully, the info may be useful at some point.

Try connect using connect target sys/password@tns vs connect connect target;

Otherwise, RMAN> resync catalog from db_unique_name all will fail or get the error below.

RMAN-06820: WARNING: failed to archive current log at primary database

When using RMAN recovery catalog, “from db_unique_name and for db_unique_name” are introduced.
Either specify the required db_unique_name or use ALL.

References:

RMAN-06613: Connect identifier for DB_UNIQUE_NAME not configured (Doc ID 1598653.1) 
List backup on Standby database returns no data in a Dataguard Configuration (Doc ID 1382885.1) 
ORA-17629 : RMAN Resync Catalog from db_unique_name all fails (Doc ID 1301769.1) 
RESYNC CATALOG FROM DB_UNIQUE_NAME ALL fails ORA-17629, ORA-17628 (Doc ID 1327156.1)

DEMO:

$ rman

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 18 15:26:30 2017

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

RMAN> connect target sys@qa

target database Password:
connected to target database: QA (DBID=147966131)

RMAN> connect catalog cat@rman

recovery catalog database Password:
connected to recovery catalog database

RMAN> resync catalog from db_unique_name all;


starting full resync of recovery catalog
full resync complete

resyncing from database with DB_UNIQUE_NAME QADR

RMAN> show all for db_unique_name all;


RMAN configuration parameters for database with db_unique_name QA are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qa/%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backups/rman/qa/%U' MAXPIECEZE 20 G;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' MAXPIECEZE 5 G;
CONFIGURE MAXSETZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESON ALGORITHM 'BAC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'QA' CONNECT IDENTIFIER  'QA';
CONFIGURE DB_UNIQUE_NAME 'QADR' CONNECT IDENTIFIER  'QADR';
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/11g/dbs/snapcf_qa.f'; # default

RMAN configuration parameters for database with db_unique_name QADR are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qadr/%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO BACKUPSET PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backups/rman/qadr/%U' MAXPIECEZE 32 G;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)' MAXPIECEZE 32 G;
CONFIGURE MAXSETZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESON ALGORITHM 'BAC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE DB_UNIQUE_NAME 'QA' CONNECT IDENTIFIER  'QA';
CONFIGURE DB_UNIQUE_NAME 'QADR' CONNECT IDENTIFIER  'QADR';
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE' APPLIED ON ALL STANDBY;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/11g/dbs/snapcf_qa.f'; # default

RMAN> exit

ALL controlfile autobackup going to disk, will probably needs further investigation.

$ rman

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Sep 18 20:08:27 2017

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

RMAN> connect target;

connected to target database: QA (DBID=3476258591)

RMAN> connect catalog cat@rman

recovery catalog database Password:
connected to recovery catalog database

RMAN> list backup of controlfile summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
787503  B  F  A SBT_TAPE    2017-09-14:17:10:41 1       1       NO         TAG20170914T171041

RMAN> list backupset 787503;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
787503  Full    13.75M     SBT_TAPE    00:00:00     2017-09-14:17:10:41
        BP Key: 787508   Status: AVAILABLE  Compressed: NO  Tag: TAG20170914T171041
        Handle: c-3476258591-20170914-05   Media: 758343
  Control File Included: Ckp SCN: 9869799255   Ckp time: 2017-09-14:17:10:41
  SPFILE Included: Modification time: 2017-08-16:17:19:20

RMAN> list backup of controlfile summary for db_unique_name all;


List of Backups for database with db_unique_name QA
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
787503  B  F  A SBT_TAPE    2017-09-14:17:10:41 1       1       NO         TAG20170914T171041

List of Backups for database with db_unique_name QADR
===============
Key     TY LV S Device Type Completion Time     #Pieces #Copies Compressed Tag
------- -- -- - ----------- ------------------- ------- ------- ---------- ---
795620  B  F  A DISK        2017-09-16:20:12:59 1       1       NO         TAG20170916T201259
796112  B  F  A DISK        2017-09-16:21:35:35 1       1       NO         TAG20170916T213535
796981  B  F  A DISK        2017-09-17:01:38:27 1       1       NO         TAG20170917T013827
798063  B  F  A DISK        2017-09-17:05:38:42 1       1       NO         TAG20170917T053841
799073  B  F  A DISK        2017-09-17:09:40:17 1       1       NO         TAG20170917T094016
800391  B  F  A DISK        2017-09-17:13:40:52 1       1       NO         TAG20170917T134052
801182  B  F  A DISK        2017-09-17:17:41:18 1       1       NO         TAG20170917T174118
801914  B  F  A DISK        2017-09-17:19:55:17 1       1       NO         TAG20170917T195517
802532  B  F  A DISK        2017-09-17:21:35:26 1       1       NO         TAG20170917T213526
803350  B  F  A DISK        2017-09-18:01:35:55 1       1       NO         TAG20170918T013555
804191  B  F  A DISK        2017-09-18:05:36:21 1       1       NO         TAG20170918T053620
805093  B  F  A DISK        2017-09-18:09:37:49 1       1       NO         TAG20170918T093749
805935  B  F  A DISK        2017-09-18:13:38:14 1       1       NO         TAG20170918T133814
807696  B  F  A DISK        2017-09-18:17:38:31 1       1       NO         TAG20170918T173831
808143  B  F  A DISK        2017-09-18:19:51:45 1       1       NO         TAG20170918T195145

RMAN> list backupset 808143;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 09/18/2017 20:10:47
RMAN-06160: no backup pieces found for backup set key: 808143

RMAN> list backupset 808143 for db_unique_name QADR;

List of Backup Set for database with db_unique_name QADR
===================

--- Notice %d is db_name and not db_unique_name which is disappointing

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
808143  Full    13.80M     DISK        00:00:00     2017-09-18:19:51:45
        BP Key: 808152   Status: AVAILABLE  Compressed: NO  Tag: TAG20170918T195145
        Piece Name: /backups/rman/qadr/QA_3476258591_c-3476258591-20170918-05.ctl
  Standby Control File Included: Ckp SCN: 9902230362   Ckp time: 2017-09-18:19:45:07
  SPFILE Included: Modification time: 2017-09-16:22:12:41

RMAN> exit

When configurations are the same for primary and standby, do this:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl' FOR DB_UNIQUE_NAME ALL

versus

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%d_%I_%F.ctl' FOR DB_UNIQUE_NAME 'QA';
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%d_%I_%F.ctl' FOR DB_UNIQUE_NAME 'QADR';
RMAN> show CONTROLFILE AUTOBACKUP FORMAT for db_unique_name all;

RMAN configuration parameters for database with db_unique_name QA are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qa/%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';

RMAN configuration parameters for database with db_unique_name QADR are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%d_%I_%F.ctl';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backups/rman/qadr/%d_%I_%F.ctl';

RMAN>
Next Page »

Blog at WordPress.com.