Michael Dinh
Cloud Control 13c Release 3 Database Template
Got excited to find:
Creating a Database Instance with Preconfigured Repository Using Database Templates
Templates can be download at:
Database Template (with EM 13.3.0.0 repository pre-configured) for Installing Oracle Enterprise Manager Cloud Control 13c Release 3 (13.3.0.0)
Got disappointed to find template is only available for 12.1.0.2.0 DB and 18.1.0.0.0 DB.
Creating a Database Instance with Preconfigured Repository Using Database Templates Caution:
Make sure that the 18.1.0.0.0_Database_Template_for_EM13_3_0_0_0_.zip file is used only for Enterprise Manager Cloud Control 13c Release 3.
It looks like the template version must match exactly from an example below.
EM 13c: Creating an Enterprise Manager 13c Cloud Control Repository using a Database Template Fails: ORA-00603: ORACLE server session terminated by fatal error (Doc ID 2291220.1) While the 12.2.0.1 version of the database is supported for EM 13.2 repository use, a database template for the EM 13.2 repository database is only supplied for the 12.1.0.2 version of the database, and they are specific for the version:
Oracle skipped 12.2 DB version when creating DB template which is disappointing.
Does database really need to be on 12.2?
To or To Not Specify Thread# When Creating SRL?
Just quick and dirty notes on some references.
Notice that the Thread # is 0 for the SRL’s. Resist the temptation to explicitly assign the SRL to a specific thread. If you are using Oracle RAC, a SRL with with Thread # of 0 can be used by any primary instance, any thread from the primary.
https://community.oracle.com/docs/DOC-1007036
Warning: standby redo logs not configured for thread <n> on <db_unique_name> (Doc ID 1956103.1)
Bug 14674237 – VALIDATE DATABASE from BROKER incorrectly reports on standby redo log files (Doc ID 14674237.8)
Warning: standby redo logs not configured for thread 0 on DATABASE
https://jameshuangsj.wordpress.com/2018/01/17/warning-standby-redo-logs-not-configured-for-thread-0-on-database/
When run “Validate Database” DGML command, get “Warning: standby redo logs not configured for thread 0 ” and “Insufficient SRLs” warnings on 12.1.0.2 database with “Database Patch Set Update : 12.1.0.2.170814 (26609783)”.
Oracle 12c DataGuard – Insufficient SRLs reported by DGMGRL VALIDATE DATABASE VERBOSE
https://blog.dbi-services.com/oracle-12c-dataguard-insufficient-srls-reported-by-dgmgrl-validate-database-verbose/
Even on a single instance, use the thread number in your create Standby Redo Log statement.
Troubleshooting GoldenGate OGG-00303: Unable to open credential store. Error code 43,490
After applying Oracle GoldenGate V12.2.0.1.170919 for Oracle Database 12c OPTIMIZER Patch for Bug# 26849949, starting GoldenGate extract failed with OGG-00303: Unable to open credential store. Error code 43,490.
Here is what the report looks like.
$ head -50 E_LAX6.rpt
***********************************************************************
Oracle GoldenGate Capture for Oracle
Version 12.2.0.1.170919 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_171030.0908_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Oct 30 2017 20:59:41
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
Starting at 2018-10-25 15:08:33
***********************************************************************
Operating System Version:
Linux
Version #2 SMP Wed Jul 11 12:11:36 PDT 2018, Release 4.1.12-94.8.5.el7uek.x86_64
Node: localhost
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 23154
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
2018-10-25 15:08:33 INFO OGG-03059 Operating system character set identified as UTF-8.
2018-10-25 15:08:33 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
EXTRACT e_lax
USERIDALIAS gguser
Source Context :
SourceModule : [er.init]
SourceID : [/scratch/aime/adestore/views/aime_adc4150431/oggcore/OpenSys/src/app/er/init.cpp]
SourceFunction : [get_infile_params]
SourceLine : [5554]
ThreadBacktrace : [11] elements
: [/u01/gg/12.2.0/libgglog.so(CMessageContext::AddThreadContext()+0x1b) [0x7f714024709b]]
: [/u01/gg/12.2.0/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x135) [0x7f7140241165]]
: [/u01/gg/12.2.0/libgglog.so(_MSG_ERR_STARTUP_PARAMERROR_ERRORTEXT(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x30) [0x7f71402308d0]]
: [/u01/gg/12.2.0/extract(get_infile_params(time_elt_def*, time_elt_def*, char**, ggs::gglib::ggdatasource::DataSourceParams&, ggs::Heartbeat::MapGeneratorParams&)+0x5da1) [0x5c4c91]]
: [/u01/gg/12.2.0/extract() [0x5f036a]]
: [/u01/gg/12.2.0/extract(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x60) [0x6cea60]]
: [/u01/gg/12.2.0/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x14d) [0x6cfcdd]]
Since I did not have the password for database gguser, I modified the password from the database and recreated credentialstore using ggsci.
info credentialstore
delete credentialstore
create wallet.
add credentialstore
alter credentialstore add user gguser alias gguser.
alter credentialstore add user GGUSER alias GGUSER
info credentialstore
Reason check credentialstore before deleting is to determine aliases.
GGSCI 1> info credentialstore Reading from ./dircrd/: Default domain: OracleGoldenGate Alias: gguser Userid: gguser Alias: GGUSER Userid: GGUSER
Here are the steps from ggserr.log.
2018-10-25 15:44:00 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (ggsuser): create wallet. 2018-10-25 15:44:00 INFO OGG-02096 Oracle GoldenGate Command Interpreter for Oracle: Created wallet at location 'dirwlt'. 2018-10-25 15:44:00 INFO OGG-02096 Oracle GoldenGate Command Interpreter for Oracle: Opened wallet at location 'dirwlt'. 2018-10-25 15:44:10 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (ggsuser): add credentialstore. 2018-10-25 15:44:10 INFO OGG-02096 Oracle GoldenGate Command Interpreter for Oracle: Credential store created in ./dircrd/. 2018-10-25 15:44:22 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (ggsuser): alter credentialstore add user gguser alias gguser. 2018-10-25 15:44:31 INFO OGG-02096 Oracle GoldenGate Command Interpreter for Oracle: Credential store in ./dircrd/ altered. 2018-10-25 15:44:55 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (ggsuser): alter credentialstore add user GGUSER alias GGUSER. 2018-10-25 15:45:06 INFO OGG-02096 Oracle GoldenGate Command Interpreter for Oracle: Credential store in ./dircrd/ altered. 2018-10-25 15:45:15 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (ggsuser): info credentialstore. 2018-10-25 15:45:15 INFO OGG-02096 Oracle GoldenGate Command Interpreter for Oracle: Reading from ./dircrd/:.
Here are the permissions for the files.
$ chmod 775 -R dirwlt/ dircrd/ $ ls -l dirwlt/ dircrd/ dircrd/: total 4 -rwxrwxr-x 1 gguser oinstall 701 Oct 25 15:45 cwallet.sso dirwlt/: total 4 -rwxrwxr-x 1 gguser oinstall 290 Oct 25 15:44 cwallet.sso
Starting extract failed again!
2018-10-25 15:45:30 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (gguser): dblogin useridalias gguser.
2018-10-25 15:45:35 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (gguser): start e*.
2018-10-25 15:45:35 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 10.80.27.191:39060 (START EXTRACT E_LAX ).
2018-10-25 15:45:35 INFO OGG-00960 Oracle GoldenGate Manager for Oracle, mgr.prm: Access granted (rule #5).
2018-10-25 15:45:35 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT E_LAX starting.
2018-10-25 15:45:35 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, e_lax.prm: EXTRACT E_LAX starting.
2018-10-25 15:45:35 INFO OGG-03059 Oracle GoldenGate Capture for Oracle, e_lax.prm: Operating system character set identified as UTF-8.
2018-10-25 15:45:35 INFO OGG-02695 Oracle GoldenGate Capture for Oracle, e_lax.prm: ANSI SQL parameter syntax is used for parameter parsing.
2018-10-25 15:45:35 ERROR OGG-00303 Oracle GoldenGate Capture for Oracle, e_lax.prm: Unable to open credential store. Error code 43,490.
2018-10-25 15:45:35 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, e_lax.prm: PROCESS ABENDING.
Set environment variables for ORACLE_HOME and ORACLE_SID for extract, and restart extract solved the issue.
FYI – environment variables already exist from OS.
2018-10-25 15:45:38 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (gguser): info all.
2018-10-25 15:52:44 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (ggsuser): start e*.
2018-10-25 15:52:44 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 10.80.27.191:39169 (START EXTRACT E_LAX ).
2018-10-25 15:52:44 INFO OGG-00960 Oracle GoldenGate Manager for Oracle, mgr.prm: Access granted (rule #5).
2018-10-25 15:52:44 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT E_LAX starting.
2018-10-25 15:52:44 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, e_lax.prm: EXTRACT E_LAX starting.
2018-10-25 15:52:44 INFO OGG-03059 Oracle GoldenGate Capture for Oracle, e_lax.prm: Operating system character set identified as UTF-8.
2018-10-25 15:52:44 INFO OGG-02695 Oracle GoldenGate Capture for Oracle, e_lax.prm: ANSI SQL parameter syntax is used for parameter parsing.
2018-10-25 15:52:44 INFO OGG-02095 Oracle GoldenGate Capture for Oracle, e_lax.prm: Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_2.
2018-10-25 15:52:44 INFO OGG-02095 Oracle GoldenGate Capture for Oracle, e_lax.prm: Successfully set environment variable ORACLE_SID=sourcedb.
2018-10-25 15:52:44 INFO OGG-02095 Oracle GoldenGate Capture for Oracle, e_lax.prm: Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_2.
2018-10-25 15:52:44 INFO OGG-02095 Oracle GoldenGate Capture for Oracle, e_lax.prm: Successfully set environment variable ORACLE_SID=sourcedb.
2018-10-25 15:52:56 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, e_lax.prm: EXTRACT E_LAX started.
Currently, I don’t know if patching caused the issue or if it is a pre-existing condition.
What’s interesting is the same issue happened for another system where environment variables for ORACLE_HOME and ORACLE_SID were not set for extract.
Instead of restarting just the extract, all the processes were stopped, and restarted.
It would be a good idea to stop and start Goldengate processes before patching in order to identify any pre-existing conditions.
12.1.0.2.0 ORA-01033: ORACLE initialization or shutdown in progress cascade standby
Objective is to create RAC cascade standby (olapdr) from existing standby (oltpdr) on the same host.
Cascade Standby: ORACLE_SID=olap1; db_name=oltp; db_unique_name=olapdr Standby: ORACLE_SID=oltp1; db_name=oltp; db_unique_name=oltpdr
Configuration for standby (oltpdr)
$ srvctl config database -d oltpdr Spfile: +DATA/OLTPDR/spfileoltpdr.ora Password file: +DATA/OLTPDR/orapwoltpdrCopy existing password file from disk to ASM for cascade standby (olapdr)
This did not work and possibly different from password file at ASM.
Please don’t ask me why.
-rw-r----- 1 oracle oinstall 7680 Sep 18 13:28 orapwolap -rw-r----- 1 oracle oinstall 7680 Sep 18 13:28 orapwolap1 -rw-r----- 1 oracle oinstall 7680 May 28 12:08 orapwoltp -rw-r----- 1 oracle oinstall 7680 May 28 12:08 orapwoltp1 $ cp $ORACLE_HOME/dbs/orapwolap /tmp/orapwolapdr ASMCMD> pwcopy /tmp/orapwolapdr +DATA/OLAPDR/orapwolapdrCheck standby (oltpdr)
oltp1> @dataguard.sql Session altered. *** v$database *** DB OPEN DATABASE REMOTE SWITCHOVER DATAGUARD PRIMARY_DB UNIQUE_NAME MODE ROLE PROTECTION_MODE ARCHIVE STATUS BROKER UNIQUE_NAME --------------- ---------------------- ------------------ -------------------- ---------- ------------------ ---------- --------------- oltpdr READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE ENABLED NOT ALLOWED ENABLED oltp *** gv$archive_dest_status *** DB DATABASE RECOVERY INST DEST TARGET UNIQUE_NAME DESTINATION MODE STATUS MODE SCHEDULE PROCESS ----- ----- ---------------- --------------- ------------------------- --------------- ------------ ----------------------- -------- -------- 1 1 LOCAL NONE USE_DB_RECOVERY_FILE_DEST OPEN_READ-ONLY VALID MANAGED REAL TIME APPLY ACTIVE ARCH 5 REMOTE olapdr olapdr UNKNOWN ERROR IDLE ACTIVE ARCH 32 LOCAL NONE USE_DB_RECOVERY_FILE_DEST UNKNOWN VALID IDLE ACTIVE RFS 2 1 LOCAL NONE USE_DB_RECOVERY_FILE_DEST OPEN_READ-ONLY VALID MANAGED REAL TIME APPLY ACTIVE ARCH 5 REMOTE olapdr olapdr UNKNOWN ERROR IDLE ACTIVE ARCH 32 LOCAL NONE USE_DB_RECOVERY_FILE_DEST UNKNOWN VALID IDLE ACTIVE RFS 6 rows selected. INST DEST STATUS SRL GAP_STATUS ERROR ----- ----- ------------ --- --------------- -------------------------------------------------------------------------------- 1 1 VALID NO NONE 5 ERROR NO RESOLVABLE GAP ORA-01033: ORACLE initialization or shutdown in progress 32 VALID NO NONE 2 1 VALID NO NONE 5 ERROR NO RESOLVABLE GAP ORA-01033: ORACLE initialization or shutdown in progress 32 VALID NO NONE 6 rows selected.Suggestion from teammate is to copy password file at ASM from oltpdr to olapdr
ASMCMD> pwcopy +DATA/OLTPDR/orapwoltpdr /tmp/orapwolapdr ASMCMD> pwcopy /tmp/orapwolapdr +DATA/OLAPDR/orapwolapdrCheck standby (oltpdr) and don’t forget to defer and enable dest.
oltp1> alter system set log_archive_dest_state_5=defer; System altered. oltp1> alter system set log_archive_dest_state_5=enable oltp1> @dataguard.sql Session altered. *** v$database *** DB OPEN DATABASE REMOTE SWITCHOVER DATAGUARD PRIMARY_DB UNIQUE_NAME MODE ROLE PROTECTION_MODE ARCHIVE STATUS BROKER UNIQUE_NAME --------------- ---------------------- ------------------ -------------------- ---------- ------------------ ---------- --------------- oltpdr READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE ENABLED NOT ALLOWED ENABLED oltp *** gv$archive_dest_status *** DB DATABASE RECOVERY INST DEST TARGET UNIQUE_NAME DESTINATION MODE STATUS MODE SCHEDULE PROCESS ----- ----- ---------------- --------------- ------------------------- --------------- ------------ ----------------------- -------- -------- 1 1 LOCAL NONE USE_DB_RECOVERY_FILE_DEST OPEN_READ-ONLY VALID MANAGED REAL TIME APPLY ACTIVE ARCH 5 REMOTE olapdr olapdr MOUNTED-STANDBY VALID MANAGED REAL TIME APPLY ACTIVE ARCH 32 LOCAL NONE USE_DB_RECOVERY_FILE_DEST UNKNOWN VALID IDLE ACTIVE RFS 2 1 LOCAL NONE USE_DB_RECOVERY_FILE_DEST OPEN_READ-ONLY VALID MANAGED REAL TIME APPLY ACTIVE ARCH 5 REMOTE olapdr olapdr MOUNTED-STANDBY VALID MANAGED REAL TIME APPLY ACTIVE ARCH 32 LOCAL NONE USE_DB_RECOVERY_FILE_DEST UNKNOWN VALID IDLE ACTIVE RFS 6 rows selected. INST DEST STATUS SRL GAP_STATUS ERROR ----- ----- ------------ --- --------------- -------------------------------------------------------------------------------- 1 1 VALID NO NONE 5 VALID YES RESOLVABLE GAP NONE 32 VALID NO NONE 2 1 VALID NO NONE 5 VALID YES RESOLVABLE GAP NONE 32 VALID NO NONE 6 rows selected.Check standby (olapdr)
olap1> @dataguard.sql Session altered. *** v$database *** DB OPEN DATABASE REMOTE SWITCHOVER DATAGUARD PRIMARY_DB UNIQUE_NAME MODE ROLE PROTECTION_MODE ARCHIVE STATUS BROKER UNIQUE_NAME --------------- ---------------------- ------------------ -------------------- ---------- ------------------ ---------- --------------- olapdr MOUNTED PHYSICAL STANDBY MAXIMUM PERFORMANCE ENABLED NOT ALLOWED DISABLED oltp *** gv$archive_dest_status *** DB DATABASE RECOVERY INST DEST TARGET UNIQUE_NAME DESTINATION MODE STATUS MODE SCHEDULE PROCESS ----- ----- ---------------- --------------- ------------------------- --------------- ------------ ----------------------- -------- -------- 1 1 LOCAL NONE USE_DB_RECOVERY_FILE_DEST MOUNTED-STANDBY VALID MANAGED REAL TIME APPLY ACTIVE ARCH 32 LOCAL NONE USE_DB_RECOVERY_FILE_DEST UNKNOWN VALID IDLE ACTIVE RFS 2 1 LOCAL NONE USE_DB_RECOVERY_FILE_DEST MOUNTED-STANDBY VALID MANAGED REAL TIME APPLY ACTIVE ARCH 32 LOCAL NONE USE_DB_RECOVERY_FILE_DEST UNKNOWN VALID IDLE ACTIVE RFS INST DEST STATUS SRL GAP_STATUS ERROR ----- ----- ------------ --- --------------- -------------------------------------------------------------------------------- 1 1 VALID NO NONE 32 VALID NO NONE 2 1 VALID NO NONE 32 VALID NO NONE *** v$archived_log *** TIME THREAD# ARCHIVED APPLIED GAP -------------------- -------- -------- -------- -------- 23-SEP-2018 09:26:05 1 37550 37467 83 23-SEP-2018 09:26:05 2 32631 32566 65 *** gv$managed_standby *** CLIENT DELAY INST PID THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS ----- ------------------------ -------- ---------- -------- ------------ --------- -------- ------ 1 399156 2 N/A MRP0 APPLYING_LOG 32570 721960 0 olap1> r 1 select inst_id inst,PID,thread#,client_process,process,status,sequence#,block#,DELAY_MINS 2 from gv$managed_standby 3 where status not in ('CLOSING','IDLE','CONNECTED') 4 order by inst_id, status desc, thread#, sequence# 5* CLIENT DELAY INST PID THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS ----- ------------------------ -------- ---------- -------- ------------ --------- -------- ------ 1 399156 2 N/A MRP0 APPLYING_LOG 32570 733658 0 olap1> r 1 select inst_id inst,PID,thread#,client_process,process,status,sequence#,block#,DELAY_MINS 2 from gv$managed_standby 3 where status not in ('CLOSING','IDLE','CONNECTED') 4 order by inst_id, status desc, thread#, sequence# 5* CLIENT DELAY INST PID THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS ----- ------------------------ -------- ---------- -------- ------------ --------- -------- ------ 1 399156 1 N/A MRP0 APPLYING_LOG 37474 499677 0 2 366691 2 UNKNOWN RFS RECEIVING 32632 1073153 0 olap1>
Next, configure DataGuard Broker and not looking pretty.
RMAN-03002: ORA-19693: backup piece already included
I have been cursed trying to create 25TB standby database.
Active duplication using standby as source failed due to bug.
Backup based duplication using standby as source failed due to bug again.
Now performing traditional restore.
Both attempts failed with RMAN-20261: ambiguous backup piece handle
RMAN> list backuppiece '/bkup/ovtdkik0_1_1.bkp'; RMAN> change backuppiece '/bkup/ovtdkik0_1_1.bkp' uncatalog;
What’s in the backup?
RMAN> spool log to /tmp/list.log RMAN> list backup; RMAN> exit
There are 2 identical backuppiece and don’t know how this could have happened.
$ grep ovtdkik0_1_1 /tmp/list.log
201792 1 AVAILABLE /bkup/ovtdkik0_1_1.bkp
202262 1 AVAILABLE /bkup/ovtdkik0_1_1.bkp
RMAN> delete backuppiece 202262;
Restart restore and is running again.
Don’t Drop Your Career Using Drop Database
I first learned about drop database in 2007.
Environment contains standby database oltpdr.
Duplicate standby database olapdr on the same host using oltpdr as source failed during restore phase.
Clean up data files from failed olapdr duplication.
olap1> show parameter db%name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string oltp
db_unique_name string olapdr
olap1> select count(*) from gv$session;
COUNT(*)
----------
90
Elapsed: 00:00:00.00
olap1> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
Elapsed: 00:00:00.03
olap1> startup force mount restrict exclusive;
ORACLE instance started.
Total System Global Area 2.5770E+10 bytes
Fixed Size 6870952 bytes
Variable Size 5625976920 bytes
Database Buffers 1.9998E+10 bytes
Redo Buffers 138514432 bytes
Database mounted.
olap1> show parameter db%name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string oltp
db_unique_name string olapdr
olap1> select count(*) from gv$session;
COUNT(*)
----------
92
Elapsed: 00:00:00.01
olap1> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
Elapsed: 00:00:00.04
At this point, I was ready to run drop database and somehow an angel was watching over me and I decided to check v$datafile.
olap1> select name from v$datafile where rownum < 10; NAME ----------------------------------------------------------- +DATA/OLTPDR/DATAFILE/system.4069.986394171 +DATA/OLTPDR/DATAFILE/dev_odi_temp.4067.986394187 +DATA/OLTPDR/DATAFILE/sysaux.4458.985845085 +DATA/OLTPDR/DATAFILE/big_dmstaging_data_new_2.4687.986498821 +DATA/OLTPDR/DATAFILE/account_toll_index.3799.985714921 +DATA/OLTPDR/DATAFILE/users.2524.985777377 +DATA/OLTPDR/DATAFILE/dev_ias_temp.4141.985846937 +DATA/OLTPDR/DATAFILE/dev_stb.4143.985846937 +DATA/OLTPDR/DATAFILE/dev_odi_user.4144.985846937 9 rows selected. Elapsed: 00:00:00.01 olap1> exitStrange data files are the same for source and target.
oltp1> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY Elapsed: 00:00:00.07 oltp1> select name from v$datafile where rownum < 10; NAME ----------------------------------------------------------- +DATA/OLTPDR/DATAFILE/system.4069.986394171 +DATA/OLTPDR/DATAFILE/dev_odi_temp.4067.986394187 +DATA/OLTPDR/DATAFILE/sysaux.4458.985845085 +DATA/OLTPDR/DATAFILE/big_dmstaging_data_new_2.4687.986498821 +DATA/OLTPDR/DATAFILE/account_toll_index.3799.985714921 +DATA/OLTPDR/DATAFILE/users.2524.985777377 +DATA/OLTPDR/DATAFILE/dev_ias_temp.4141.985846937 +DATA/OLTPDR/DATAFILE/dev_stb.4143.985846937 +DATA/OLTPDR/DATAFILE/dev_odi_user.4144.985846937 9 rows selected. Elapsed: 00:00:00.01 oltp1> exitCheck data files from ASM.
ASMCMD> cd DATA ASMCMD> ls OLAPDR/ OLTP/ OLTPDR/ SCHDDBDR/ _MGMTDB/ ASMCMD> cd OLAPDR ASMCMD> ls CONTROLFILE/ DATAFILE/ ASMCMD> cd DATAFILE ASMCMD> pwd +DATA/OLAPDR/DATAFILE ASMCMD> exitShutdown olapdr.
olap1> show parameter db%name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string oltp db_unique_name string olapdr olap1> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED Elapsed: 00:00:00.03 olap1> shut abort; ORACLE instance shut down. olap1> exitManually remove data files from ASM.
$ asmcmd lsof -G +DATA|grep -ic OLAPDR 0 $ asmcmd ls +DATA/OLAPDR/DATAFILE|wc -l 1665 $ asmcmd lsof -G +DATA/OLAPDR/DATAFILE|wc -l 0 $ asmcmd ASMCMD> cd datac1 ASMCMD> cd olapdr ASMCMD> ls CONTROLFILE/ DATAFILE/ ASMCMD> cd datafile ASMCMD> pwd +DATA/olapdr/datafile ASMCMD> rm * You may delete multiple files and/or directories. Are you sure? (y/n) y
What would have happened if drop database was executed?
Does anyone know for sure?
Would you have executed drop database?
Differences Between Validate Preview [Summary]
Summary is equivalent to – list backup of database summary versus list backup of database.
RMAN> restore database validate preview summary from tag=stby_dup;
Starting restore at 20-SEP-2018 21:19:48 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=33 instance=hawk1 device type=DISK List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- -------------------- ------- ------- ---------- --- 119 B 0 A DISK 18-SEP-2018 13:56:33 1 1 NO STBY_DUP using channel ORA_DISK_1
RMAN> restore database validate preview from tag=stby_dup;
Starting restore at 20-SEP-2018 21:18:44
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 instance=hawk1 device type=DISK
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
119 Incr 0 1.34G DISK 00:00:15 18-SEP-2018 13:56:33
BP Key: 121 Status: AVAILABLE Compressed: NO Tag: STBY_DUP
Piece Name: /tmp/HAWK_djtde1c3_1_1.bkp
List of Datafiles in backup set 119
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
1 0 Incr 6038608 18-SEP-2018 13:56:19 +DATA/hawkb/datafile/system.306.984318067
2 0 Incr 6038608 18-SEP-2018 13:56:19 +DATA/hawkb/datafile/sysaux.307.984318067
3 0 Incr 6038608 18-SEP-2018 13:56:19 +DATA/hawkb/datafile/undotbs1.309.984318093
4 0 Incr 6038608 18-SEP-2018 13:56:19 +DATA/hawkb/datafile/users.310.984318093
5 0 Incr 6038608 18-SEP-2018 13:56:19 +DATA/hawkb/datafile/undotbs2.311.984318095
6 0 Incr 6038608 18-SEP-2018 13:56:19 +DATA/hawkb/datafile/undotbs3.312.984318095
using channel ORA_DISK_1
Following is the same for both.
RMAN> restore database validate preview summary from tag=stby_dup;
RMAN> restore database validate preview from tag=stby_dup;
List of Archived Log Copies for database with db_unique_name HAWKB
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - --------------------
849 1 506 A 18-SEP-2018 13:55:08
Name: +FRA/hawkb/archivelog/2018_09_18/thread_1_seq_506.551.987170199
852 1 507 A 18-SEP-2018 13:56:39
Name: +FRA/hawkb/archivelog/2018_09_18/thread_1_seq_507.552.987199227
856 1 508 A 18-SEP-2018 22:00:26
Name: +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_508.554.987220639
860 1 509 A 19-SEP-2018 03:57:18
Name: +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_509.556.987258729
862 1 510 A 19-SEP-2018 14:32:07
Name: +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_510.557.987285627
864 1 511 A 19-SEP-2018 22:00:27
Name: +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_511.558.987287235
868 1 512 A 19-SEP-2018 22:27:15
Name: +FRA/hawkb/archivelog/2018_09_20/thread_1_seq_512.560.987325879
872 1 513 A 20-SEP-2018 09:11:18
Name: +FRA/hawkb/archivelog/2018_09_20/thread_1_seq_513.562.987364831
847 2 173 A 18-SEP-2018 13:55:08
Name: +FRA/hawkb/archivelog/2018_09_18/thread_2_seq_173.550.987170199
854 2 174 A 18-SEP-2018 13:56:38
Name: +FRA/hawkb/archivelog/2018_09_19/thread_2_seq_174.553.987210305
858 2 175 A 19-SEP-2018 01:05:05
Name: +FRA/hawkb/archivelog/2018_09_19/thread_2_seq_175.555.987253211
866 2 176 A 19-SEP-2018 13:00:10
Name: +FRA/hawkb/archivelog/2018_09_19/thread_2_seq_176.559.987287239
870 2 177 A 19-SEP-2018 22:27:18
Name: +FRA/hawkb/archivelog/2018_09_20/thread_2_seq_177.561.987328815
Media recovery start SCN is 6038608
Recovery must be done beyond SCN 6038608 to clear datafile fuzziness
channel ORA_DISK_1: starting validation of datafile backup set
channel ORA_DISK_1: reading from backup piece /tmp/HAWK_djtde1c3_1_1.bkp
channel ORA_DISK_1: piece handle=/tmp/HAWK_djtde1c3_1_1.bkp tag=STBY_DUP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete, elapsed time: 00:00:08
using channel ORA_DISK_1
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_18/thread_1_seq_506.551.987170199
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_18/thread_1_seq_507.552.987199227
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_508.554.987220639
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_509.556.987258729
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_510.557.987285627
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_1_seq_511.558.987287235
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_20/thread_1_seq_512.560.987325879
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_20/thread_1_seq_513.562.987364831
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_18/thread_2_seq_173.550.987170199
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_2_seq_174.553.987210305
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_2_seq_175.555.987253211
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_19/thread_2_seq_176.559.987287239
channel ORA_DISK_1: scanning archived log +FRA/hawkb/archivelog/2018_09_20/thread_2_seq_177.561.987328815
Finished restore at 20-SEP-2018 21:20:11
RMAN>
tnsping for DataGuard
I am preparing Dataguard for switchover with 1 primary and 3 standbys and should be able to tnsping all the services from log_archive_config=DG_CONFIG=DG_CONFIG=(HAWKA,HAWKB,HAWKC)
Not sure how valuable this may be for you as I wanted to perform all the tasks in one command and know where the error is at.
tnsping HAWKC is failing in the 2nd position.
[oracle@db-fs-1 ~]$ { tnsping HAWKA & tnsping HAWKC & tnsping HAWKB & echo ; } > /tmp/tnsping_`hostname -s`; ls -l /tmp/tnsping_`hostname -s`
[1] 18375
[2] 18376
[3] 18377
-rw-r--r-- 1 oracle oinstall 1208 Sep 6 00:45 /tmp/tnsping_db-fs-1
[1] Done tnsping HAWKA
[2]- Exit 1 tnsping HAWKC
[3]+ Done tnsping HAWKB
[oracle@db-fs-1 ~]$
tnsping HAWKC is failing in the 3rd position.
[oracle@db-fs-1 ~]$ { tnsping HAWKA & tnsping HAWKB & tnsping HAWKC & echo $? ; } > /tmp/tnsping_`hostname -s`; ls -l /tmp/tnsping_`hostname -s`
[1] 18433
[2] 18434
[3] 18435
-rw-r--r-- 1 oracle oinstall 1210 Sep 6 00:46 /tmp/tnsping_db-fs-1
[1] Done tnsping HAWKA
[2]- Done tnsping HAWKB
[3]+ Exit 1 tnsping HAWKC
[oracle@db-fs-1 ~]$
tnsping HAWKC is failing in the 3rd position.
There were 3 processes spawned and had to press enter to get final results.
[oracle@db-fs-1 ~]$ { tnsping HAWKA & tnsping HAWKB & tnsping HAWKC & echo $? ; } > /tmp/tnsping_`hostname -s`; ls -l /tmp/tnsping_`hostname -s`
[1] 18469
[2] 18470
[3] 18471
-rw-r--r-- 1 oracle oinstall 837 Sep 6 00:47 /tmp/tnsping_db-fs-1
[1] Done tnsping HAWKA
[3]+ Exit 1 tnsping HAWKC
[oracle@db-fs-1 ~]$
[2]+ Done tnsping HAWKB
[oracle@db-fs-1 ~]$
Remove tnsping HAWKC to demo working results.
[oracle@db-fs-1 ~]$ { tnsping HAWKA & tnsping HAWKB & echo ; } > /tmp/tnsping_`hostname -s`; ls -l /tmp/tnsping_`hostname -s`
[1] 18500
[2] 18501
-rw-r--r-- 1 oracle oinstall 955 Sep 6 00:47 /tmp/tnsping_db-fs-1
[1]- Done tnsping HAWKA
[2]+ Done tnsping HAWKB
[oracle@db-fs-1 ~]$
How Backup Spfile to Pfile Saved My Arse
Typically when I perform backup review, I always suggest to add the following:
run {
allocate channel c1 device type disk;
SQL "alter database backup controlfile to trace as ''/tmp/ctl_@_trace.sql'' reuse resetlogs";
SQL "create pfile=''/tmp/init@.ora'' from spfile";
release channel c1;
}
Example:
RMAN> run {
allocate channel c1 device type disk;
2> 3> SQL "alter database backup controlfile to trace as ''/tmp/ctl_@_trace.sql'' reuse resetlogs";
4> SQL "create pfile=''/tmp/init@.ora'' from spfile";
5> release channel c1;
6> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=31 instance=hawk1 device type=DISK
sql statement: alter database backup controlfile to trace as ''/tmp/ctl_@_trace.sql'' reuse resetlogs
sql statement: create pfile=''/tmp/init@.ora'' from spfile
released channel: c1
RMAN> exit
[oracle@racnode-dc1-1 tmp]$ ll /tmp/init*
-rw-r--r-- 1 oracle dba 1978 Aug 25 21:11 /tmp/inithawk1.ora
[oracle@racnode-dc1-1 tmp]$ ll /tmp/ctl*
-rw-r--r-- 1 oracle dba 7318 Aug 25 21:11 /tmp/ctl_hawk1_trace.sql
[oracle@racnode-dc1-1 tmp]$ sysresv|tail -1
Oracle Instance alive for sid "hawk1"
[oracle@racnode-dc1-1 tmp]$
When on RAC, do not create pfile to it’s default destination, e.g. SQL “create pfile from spfile”;
ORIGINAL LOCATION: ==================================================================================================== $ asmcmd ls -l +DATA/SOXPA/PARAMETERFILE Type Redund Striped Time Sys Name PARAMETERFILE UNPROT COARSE AUG 17 10:00:00 Y spfile.321.984394591 PARAMETERFILE UNPROT COARSE AUG 17 10:00:00 N spfilehawka.ora => +DATA/HAWKA/PARAMETERFILE/spfile.321.984394591 BIG OOPS: NOTHING THERE! ==================================================================================================== $ asmcmd ls -l +DATA/HAWKA/PARAMETERFILE ERROR: Created wrong spfile. ==================================================================================================== $ asmcmd ls -l +DATA/WH02A/PARAMETERFILE Type Redund Striped Time Sys Name PARAMETERFILE UNPROT COARSE AUG 23 19:00:00 Y spfile.380.984672023 PARAMETERFILE UNPROT COARSE AUG 23 19:00:00 N spfilewh02a.ora => +DATA/WH02A/PARAMETERFILE/spfile.380.984672023 PARAMETERFILE UNPROT COARSE AUG 24 14:00:00 N spfilehawka.ora => +DATA/WH01A/PARAMETERFILE/spfile.321.985008497 Create copy of pfile. ==================================================================================================== $ ll *.good -rw-r--r--. 1 oracle oinstall 2207 Aug 24 14:57 inithawka4.ora.good Check controlfile location for pfile. ==================================================================================================== $ cat inithawka4.ora.good *.control_files='+DATA/hawka/controlfile/current.272.984393927','+FRA/hawka/controlfile/current.307.984393927'#Restore Controlfile Check database and create new spfile from pfile. ==================================================================================================== HOST04:(SYS@hawka4):PRIMARY> show parameter spfile; NAME TYPE VALUE ------------------------------ ----------- ---------------------------------------------------------------------------------------------------- spfile string +DATA/hawka/parameterfile/spfilehawka.ora HOST04:(SYS@hawka4):PRIMARY> show parameter control_file NAME TYPE VALUE ------------------------------ ----------- ---------------------------------------------------------------------------------------------------- control_file_record_keep_time integer 7 control_files string +DATA/hawka/controlfile/current.272.984393927, +FRA/hawka/controlfile/current.307.984393927 HOST04:(SYS@hawka4):PRIMARY> create spfile='+DATA/HAWKA/PARAMETERFILE/spfilehawka4.ora' from pfile='/u01/app/oracle/db/11.2.0.4/dbs/inithawka4.ora.good'; File created. HOST04:(SYS@hawka4):PRIMARY> exit rmalias and mkalias for NEW SPFILE. ==================================================================================================== $ asmcmd ls -l +DATA/HAWKA/PARAMETERFILE Type Redund Striped Time Sys Name PARAMETERFILE UNPROT COARSE AUG 24 15:00:00 Y spfile.1077.985015077 PARAMETERFILE UNPROT COARSE AUG 24 15:00:00 N spfilehawka4.ora => +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077 $ asmcmd ASMCMD> cd +DATA/HAWKA/PARAMETERFILE ASMCMD> ls -lt Type Redund Striped Time Sys Name PARAMETERFILE UNPROT COARSE AUG 24 15:00:00 N spfilehawka4.ora => +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077 PARAMETERFILE UNPROT COARSE AUG 24 15:00:00 Y spfile.1077.985015077 ASMCMD> rmalias spfilehawka4.ora ASMCMD> mkalias +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077 spfilehawka.ora ASMCMD> ls -lt Type Redund Striped Time Sys Name PARAMETERFILE UNPROT COARSE AUG 24 15:00:00 N spfilehawka.ora => +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077 PARAMETERFILE UNPROT COARSE AUG 24 15:00:00 Y spfile.1077.985015077 ASMCMD> exit $ asmcmd ls -l +DATA/HAWKA/PARAMETERFILE Type Redund Striped Time Sys Name PARAMETERFILE UNPROT COARSE AUG 24 15:00:00 Y spfile.1077.985015077 PARAMETERFILE UNPROT COARSE AUG 24 15:00:00 N spfilehawka.ora => +DATA/HAWKA/PARAMETERFILE/spfile.1077.985015077 $ Verify pfile can be created from spfile. ==================================================================================================== HOST04:(SYS@hawka4):PRIMARY> create pfile='/tmp/init@.ora' from spfile; File created. HOST04:(SYS@hawka4):PRIMARY> exit ==================================================================================================== oracle@p2dbccx04:hawka4:/home/oracle $ ll /tmp/inithawka4.ora -rw-r--r--. 1 oracle asmadmin 2207 Aug 24 15:24 /tmp/inithawka4.ora
RMAN: Synchronize standby database using production archivelog backupset
If you have not read RMAN: Synchronize standby database using production archivelog, then please do so.
# Primary archivelog is on local vs shared storage. # Primary RMAN archivelog backupset resides on shared folder with Standby. # Full backup is performed once per day and include archivelog with format arch_DB02_`date '+%Y%m%d' # MANAGED REAL TIME APPLY is running. PRI: /shared/prod/DB02/rman/ SBY: /shared/backup/arch/DB02a/
#!/bin/sh -e
# Michael Dinh: Aug 21, 2018
# RMAN sync standby using production archivelog backupset
#
. ~/working/dinh/dinh.env
. ~/working/dinh/DB02a.env
sysresv|tail -1
set -x
# List production archivelog backupset for current day
ls -l /shared/prod/DB02/rman/arch_DB02_`date '+%Y%m%d'`*
# Copy production archivelog backupset for current day to standby
cp -ufv /shared/prod/DB02/rman/arch_DB02_`date '+%Y%m%d'`* /shared/backup/arch/DB02a
rman msglog /tmp/rman_sync_standby.log > /dev/null << EOF
set echo on;
connect target;
show all;
# Catalog production archivelog backupset from standby
catalog start with '/shared/backup/arch/DB02a' noprompt;
# Restore production archivelog backupset to standby
restore archivelog from time 'trunc(sysdate)-1';
exit
EOF
sleep 15m
# Verify Media Recovery Log from alert log
tail -20 $ORACLE_BASE/diag/rdbms/$ORACLE_UNQNAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log
exit
$ crontab -l 00 12 * * * /home/oracle/working/dinh/rman_sync_standby.sh > /tmp/rman_sync_standby.sh.out 2>&1 $ ll /tmp/rman* -rw-r--r--. 1 oracle oinstall 7225 Aug 22 12:01 /tmp/rman_sync_standby.log -rw-r--r--. 1 oracle oinstall 4318 Aug 22 12:16 /tmp/rman_sync_standby.sh.out
+ tail -20 /u01/app/oracle/diag/rdbms/DB02a/DB02a2/trace/alert_DB02a2.log ALTER DATABASE RECOVER managed standby database using current logfile nodelay disconnect ORA-1153 signalled during: ALTER DATABASE RECOVER managed standby database using current logfile nodelay disconnect ... Tue Aug 21 15:41:27 2018 Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST Tue Aug 21 15:54:30 2018 db_recovery_file_dest_size of 204800 MB is 21.54% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Wed Aug 22 12:01:21 2018 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31636.1275.984830461 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31637.1276.984830461 Wed Aug 22 12:01:46 2018 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31638.1278.984830487 Wed Aug 22 12:01:58 2018 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31639.1277.984830487 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31640.1279.984830487 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31641.1280.984830487 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31642.1281.984830489 Media Recovery Waiting for thread 1 sequence 31643 + exit # Manual recovery: WAIT_FOR_LOG and BLOCK#=0 and never increment. SQL> r 1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS 2 from gv$managed_standby 3 where 1=1 4 and status not in ('CLOSING','IDLE','CONNECTED') 5 order by status desc, thread#, sequence# 6* CLIENT DELAY PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS -------- ----- -------- ---------- -------- ------------ --------- -------- ------ 94734 2 1 N/A MRP0 WAIT_FOR_LOG 31643 0 0 SQL>
$ cat /tmp/rman_sync_standby.log Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 22 12:00:58 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. RMAN> echo set on RMAN> connect target; connected to target database: DB02 (DBID=1816794213, not open) RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name DB02A are: CONFIGURE RETENTION POLICY TO REDUNDANCY 7; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; CONFIGURE DEVICE TYPE 'SBT_TAPE' BACKUP TYPE TO BACKUPSET PARALLELISM 1; 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 'SBT_TAPE' PARMS 'SBT_LIBRARY=/u01/app/oracle/product/11.2.0/dbhome_1/lib/libddobk.so, ENV=(STORAGE_UNIT=dd-u99,BACKUP_HOST=dd860.ccx.carecentrix.com,ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)'; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/db/11.2.0.4/dbs/snapcf_DB02a2.f'; # default RMAN> catalog start with '/shared/backup/arch/DB02a' noprompt; searching for all files that match the pattern /shared/backup/arch/DB02a List of Files Unknown to the Database ===================================== File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1 File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1 File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1 File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1 File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1 File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1 cataloging files... cataloging done List of Cataloged Files ======================= File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1 File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1 File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1 File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1 File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1 File Name: /shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1 RMAN> restore archivelog from time 'trunc(sysdate)-1'; Starting restore at 22-AUG-2018 12:01:00 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=285 instance=DB02a2 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=3 instance=DB02a2 device type=DISK archived log for thread 1 with sequence 31630 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31630.496.984755257 archived log for thread 1 with sequence 31631 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31631.497.984755273 archived log for thread 1 with sequence 31632 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31632.498.984755273 archived log for thread 1 with sequence 31633 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31633.499.984755275 archived log for thread 1 with sequence 31634 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31634.500.984755275 archived log for thread 1 with sequence 31635 is already on disk as file +FRA/DB02a/archivelog/2018_08_21/thread_1_seq_31635.501.984755275 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=31636 channel ORA_DISK_1: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1 channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=31637 channel ORA_DISK_2: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1 channel ORA_DISK_1: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1 tag=TAG20180822T110121 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=31638 channel ORA_DISK_1: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1 channel ORA_DISK_2: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1 tag=TAG20180822T110121 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:25 channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=31639 channel ORA_DISK_2: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1 channel ORA_DISK_2: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1 tag=TAG20180822T113906 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:01 channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=31640 channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=31641 channel ORA_DISK_2: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1 channel ORA_DISK_2: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1 tag=TAG20180822T113906 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:01 channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=31642 channel ORA_DISK_2: reading from backup piece /shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1 channel ORA_DISK_2: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1 tag=TAG20180822T113906 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: piece handle=/shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1 tag=TAG20180822T110121 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:17 Finished restore at 22-AUG-2018 12:01:44 RMAN> exit
$ cat /tmp/rman_sync_standby.sh.out ORACLE_SID = [oracle] ? The Oracle base has been set to /u01/app/oracle Oracle Instance alive for sid "DB02a2" CURRENT_INSTANCE=DB02a2 ORACLE_UNQNAME=DB02a OTHER_INSTANCE=DB02a3,DB02a4 ORACLE_SID=DB02a2 ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/db/11.2.0.4 NLS_DATE_FORMAT=DD-MON-YYYY HH24:MI:SS Oracle Instance alive for sid "DB02a2" ++ date +%Y%m%d + ls -l /shared/prod/DB02/rman/arch_DB02_20180822_9ttb6h01_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_9utb6h02_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_9vtb6h02_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_a9tb6j6q_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_aatb6j6q_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_abtb6j6q_1_1 -rw-r-----. 1 oracle dba 1900124160 Aug 22 11:02 /shared/prod/DB02/rman/arch_DB02_20180822_9ttb6h01_1_1 -rw-r-----. 1 oracle dba 1938098176 Aug 22 11:02 /shared/prod/DB02/rman/arch_DB02_20180822_9utb6h02_1_1 -rw-r-----. 1 oracle dba 1370842112 Aug 22 11:01 /shared/prod/DB02/rman/arch_DB02_20180822_9vtb6h02_1_1 -rw-r-----. 1 oracle dba 11870720 Aug 22 11:39 /shared/prod/DB02/rman/arch_DB02_20180822_a9tb6j6q_1_1 -rw-r-----. 1 oracle dba 3584 Aug 22 11:39 /shared/prod/DB02/rman/arch_DB02_20180822_aatb6j6q_1_1 -rw-r-----. 1 oracle dba 3072 Aug 22 11:39 /shared/prod/DB02/rman/arch_DB02_20180822_abtb6j6q_1_1 ++ date +%Y%m%d + cp -ufv /shared/prod/DB02/rman/arch_DB02_20180822_9ttb6h01_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_9utb6h02_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_9vtb6h02_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_a9tb6j6q_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_aatb6j6q_1_1 /shared/prod/DB02/rman/arch_DB02_20180822_abtb6j6q_1_1 /shared/backup/arch/DB02a \u2018/shared/prod/DB02/rman/arch_DB02_20180822_9ttb6h01_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_9ttb6h01_1_1\u2019 \u2018/shared/prod/DB02/rman/arch_DB02_20180822_9utb6h02_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_9utb6h02_1_1\u2019 \u2018/shared/prod/DB02/rman/arch_DB02_20180822_9vtb6h02_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_9vtb6h02_1_1\u2019 \u2018/shared/prod/DB02/rman/arch_DB02_20180822_a9tb6j6q_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_a9tb6j6q_1_1\u2019 \u2018/shared/prod/DB02/rman/arch_DB02_20180822_aatb6j6q_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_aatb6j6q_1_1\u2019 \u2018/shared/prod/DB02/rman/arch_DB02_20180822_abtb6j6q_1_1\u2019 -> \u2018/shared/backup/arch/DB02a/arch_DB02_20180822_abtb6j6q_1_1\u2019 + rman msglog /tmp/rman_sync_standby.log + sleep 15m + tail -20 /u01/app/oracle/diag/rdbms/DB02a/DB02a2/trace/alert_DB02a2.log ALTER DATABASE RECOVER managed standby database using current logfile nodelay disconnect ORA-1153 signalled during: ALTER DATABASE RECOVER managed standby database using current logfile nodelay disconnect ... Tue Aug 21 15:41:27 2018 Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST Tue Aug 21 15:54:30 2018 db_recovery_file_dest_size of 204800 MB is 21.54% used. This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Wed Aug 22 12:01:21 2018 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31636.1275.984830461 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31637.1276.984830461 Wed Aug 22 12:01:46 2018 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31638.1278.984830487 Wed Aug 22 12:01:58 2018 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31639.1277.984830487 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31640.1279.984830487 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31641.1280.984830487 Media Recovery Log +FRA/DB02a/archivelog/2018_08_22/thread_1_seq_31642.1281.984830489 Media Recovery Waiting for thread 1 sequence 31643 + exit
RMAN: Synchronize standby database using production archivelog
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 2018select 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
RMAN Commands
Note to self to configure and clear settings, and all things RMAN.
Will add more as time goes by.
catalog backuppiece '/u01/app/oracle/backup/HAWK_3241949199_20180816_bctand12_1_1.bkp';
++++ Remove summary to get full details.
list backup of archivelog all summary completed after 'sysdate-1';
list backup of archivelog from sequence 243 summary;
list backup summary completed after 'sysdate-1';
list backup of controlfile summary tag=STBY;
list backup of controlfile summary;
list backup of spfile summary;
list backup by file;
backup incremental level 0 check logical database filesperset 1 tag=LEVEL0 plus archivelog filesperset 8 tag=LEVEL0;
backup current controlfile for standby tag=STBY;
+++ Remove preview/validate/summary for actual restore.
restore controlfile validate from tag=STBY;
restore controlfile validate preview summary from tag=STBY; (Error if insufficient backup)
restore spfile validate preview summary;
restore database validate;
restore database validate preview summary;
restore database until time "TRUNC(sysdate)+17/24" validate preview summary;
configure controlfile autobackup on;
configure controlfile autobackup clear;
configure controlfile autobackup format for device type disk to '/media/swrepo/backup/%d_%i_%f.ctl';
configure controlfile autobackup format for device type disk clear;
configure device type disk backup type to compressed backupset parallelism 2;
configure device type disk clear;
configure channel device type disk format '/media/swrepo/backup/%d_%i_%t_%u.bkp' maxpiecesize 1 g maxopenfiles 1;
configure channel device type disk clear;
configure archivelog deletion policy to backed up 1 times to disk applied on all standby;
configure archivelog deletion policy clear;
configure db_unique_name 'hawka' connect identifier 'hawka';
configure db_unique_name 'hawka' clear;
ReCreate ASM Disks RAC
A long time ago I had written about ReCreate ASM Disks; however this was single instance.
Basically, need to duplicate 11.2.0.4 database to 12.2 RAC. Unfortunately, compatible.rdbms=12.1.0.2.0.
alter diskgroup DATA set attribute ‘compatible.rdbms’ = ‘11.2’ does not work.
You are thinking, “Why not create with 11.2 to start with?”
I am using oravirt (Mikael Sandström) · GitHub to build RAC environment.
This is the best, hands down resource to build sand box and unfortunately for me, have not figured out how to configured all the details.
NOTE: This is a newly created environment and does not contain any database.
15:51:36 SYS @ +ASM1:>select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;
GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY
------------ ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
1 CRS 12.2.0.1.0 11.2.0.0.0
2 DATA 12.2.0.1.0 12.1.0.2.0
3 FRA 12.2.0.1.0 12.1.0.2.0
15:51:44 SYS @ +ASM1:>alter diskgroup DATA set attribute 'compatible.rdbms' = '11.2';
alter diskgroup DATA set attribute 'compatible.rdbms' = '11.2'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.rdbms
ORA-15244: new compatibility setting less than current [12.1.0.2.0]
15:51:52 SYS @ +ASM1:>
+++ Review DG attributes
[oracle@racnode-dc1-1 sql]$ asmcmd lsattr -l -G DATA
Name Value
access_control.enabled FALSE
access_control.umask 066
appliance._partnering_type GENERIC
au_size 4194304
cell.smart_scan_capable FALSE
cell.sparse_dg allnonsparse
compatible.advm 12.2.0.1.0
compatible.asm 12.2.0.1.0
compatible.rdbms 12.1.0.2.0
content.check FALSE
content.type data
disk_repair_time 3.6h
failgroup_repair_time 24.0h
idp.boundary auto
idp.type dynamic
logical_sector_size 512
phys_meta_replicated true
preferred_read.enabled FALSE
scrub_async_limit 1
scrub_metadata.enabled FALSE
sector_size 512
thin_provisioned FALSE
[oracle@racnode-dc1-1 sql]$ asmcmd lsattr -l -G FRA
Name Value
access_control.enabled FALSE
access_control.umask 066
appliance._partnering_type GENERIC
au_size 4194304
cell.smart_scan_capable FALSE
cell.sparse_dg allnonsparse
compatible.advm 12.2.0.1.0
compatible.asm 12.2.0.1.0
compatible.rdbms 12.1.0.2.0
content.check FALSE
content.type data
disk_repair_time 3.6h
failgroup_repair_time 24.0h
idp.boundary auto
idp.type dynamic
logical_sector_size 512
phys_meta_replicated true
preferred_read.enabled FALSE
scrub_async_limit 1
scrub_metadata.enabled FALSE
sector_size 512
thin_provisioned FALSE
+++ Review DG Path
[oracle@racnode-dc1-1 sql]$ asmcmd lsdsk -G DATA
Path
ORCL:DATA01
[oracle@racnode-dc1-1 sql]$ asmcmd lsdsk -G FRA
Path
ORCL:FRA01
+++ Remove DG from Cluster.
[oracle@racnode-dc1-1 sql]$ srvctl remove diskgroup -diskgroup DATA -force
[oracle@racnode-dc1-1 sql]$ srvctl remove diskgroup -diskgroup FRA -force
+++ Dismount DG before drop.
15:56:28 SYS @ +ASM1:>drop diskgroup DATA;
drop diskgroup DATA
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15073: diskgroup DATA is mounted by another ASM instance
15:58:07 SYS @ +ASM1:>select inst_id, name, state from gv$asm_diskgroup;
INST_ID NAME STATE
---------- ------------------------------ -----------
2 CRS MOUNTED
2 DATA MOUNTED
2 FRA MOUNTED
1 CRS MOUNTED
1 DATA MOUNTED
1 FRA MOUNTED
6 rows selected.
15:58:37 SYS @ +ASM1:>alter diskgroup DATA dismount;
Diskgroup altered.
15:58:47 SYS @ +ASM1:>alter diskgroup FRA dismount;
Diskgroup altered.
15:59:02 SYS @ +ASM1:>select inst_id, name, state from gv$asm_diskgroup order by 1,2;
INST_ID NAME STATE
---------- ------------------------------ -----------
1 CRS MOUNTED
1 DATA DISMOUNTED
1 FRA DISMOUNTED
2 CRS MOUNTED
2 DATA MOUNTED
2 FRA MOUNTED
6 rows selected.
15:59:10 SYS @ +ASM1:>
+++ Drop DG from 2nd instance.
16:00:42 SYS @ +ASM2:>drop diskgroup DATA including contents;
Diskgroup dropped.
16:01:06 SYS @ +ASM2:>drop diskgroup FRA including contents;
Diskgroup dropped.
16:01:17 SYS @ +ASM2:>select inst_id, name, state from gv$asm_diskgroup order by 1,2;
INST_ID NAME STATE
---------- ------------------------------ -----------
1 CRS MOUNTED
2 CRS MOUNTED
16:01:27 SYS @ +ASM2:>
+++ Review ASM Disks.
[root@racnode-dc1-2 ~]# /etc/init.d/oracleasm listdisks
CRS01
DATA01
FRA01
[root@racnode-dc1-2 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks: [ OK ]
+++ Create and mount DG.
16:18:32 SYS @ +ASM2:>create diskgroup FRA external redundancy disk 'ORCL:FRA01' ATTRIBUTE 'compatible.asm'='12.2.0.1.0', 'compatible.rdbms'='11.2.0.0.0', 'au_size'='4194304';
Diskgroup created.
16:19:07 SYS @ +ASM2:>create diskgroup DATA external redundancy disk 'ORCL:DATA01' ATTRIBUTE 'compatible.asm'='12.2.0.1.0', 'compatible.rdbms'='11.2.0.0.0', 'au_size'='4194304';
Diskgroup created.
16:19:31 SYS @ +ASM2:>
+++ Mount DG.
16:20:34 SYS @ +ASM1:>select inst_id, name, state from gv$asm_diskgroup order by 1,2;
INST_ID NAME STATE
---------- ------------------------------ -----------
1 CRS MOUNTED
1 DATA DISMOUNTED
1 FRA DISMOUNTED
2 CRS MOUNTED
2 DATA MOUNTED
2 FRA MOUNTED
6 rows selected.
16:20:37 SYS @ +ASM1:>alter diskgroup DATA mount;
Diskgroup altered.
16:21:01 SYS @ +ASM1:>alter diskgroup FRA mount;
Diskgroup altered.
16:21:10 SYS @ +ASM1:>select inst_id, name, state from gv$asm_diskgroup order by 1,2;
INST_ID NAME STATE
---------- ------------------------------ -----------
1 CRS MOUNTED
1 DATA MOUNTED
1 FRA MOUNTED
2 CRS MOUNTED
2 DATA MOUNTED
2 FRA MOUNTED
6 rows selected.
16:21:42 SYS @ +ASM1:>select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;
GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY
------------ ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
1 CRS 12.2.0.1.0 11.2.0.0.0
3 DATA 12.2.0.1.0 11.2.0.0.0
2 FRA 12.2.0.1.0 11.2.0.0.0
16:21:45 SYS @ +ASM1:>
+++ Review ASM
[oracle@racnode-dc1-1 sql]$ srvctl status asm -v
ASM is running on racnode-dc1-1,racnode-dc1-2
Detailed state on node racnode-dc1-1: Started
Detailed state on node racnode-dc1-2: Started
[oracle@racnode-dc1-1 sql]$ asmcmd lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 512 4096 4194304 40952 40592 0 40592 0 Y CRS/
MOUNTED EXTERN N 512 512 4096 4194304 8188 8056 0 8056 0 N DATA/
MOUNTED EXTERN N 512 512 4096 4194304 12284 12152 0 12152 0 N FRA/
[oracle@racnode-dc1-1 sql]$
Cluster Resource To Check When Patching RAC DBFS OGG
crsctl stat res|grep -i type|sort -u
TYPE=app.appvipx.type TYPE=local_resource TYPE=ora.asm.type TYPE=ora.cluster_vip_net1.type TYPE=ora.cvu.type TYPE=ora.database.type TYPE=ora.diskgroup.type TYPE=ora.listener.type TYPE=ora.mgmtdb.type TYPE=ora.mgmtlsnr.type TYPE=ora.network.type TYPE=ora.oc4j.type TYPE=ora.ons.type TYPE=ora.scan_listener.type TYPE=ora.scan_vip.type TYPE=xag.goldengate.type
crsctl stat res -p -w 'TYPE = ora.database.type' | egrep '^NAME|AUTO_START'
crsctl stat res -t -w '((TARGET != ONLINE) or (STATE != ONLINE)'
crsctl stat res -t -w 'TYPE = xag.goldengate.type' -- OGG Resource
crsctl stat res -t -w 'TYPE = app.appvipx.type' -- OGG VIP
crsctl stat res -t -w 'TYPE = local_resource' -- DBFS Mount
crsctl stat res -t -w 'TYPE = ora.database.type' -- DB resource (including DBFS)
You might ask, why not use crsctl stat res -t?
For this specific environment, there are 190 lines of output and needed to focus on what’s important.
Patching GoldenGate with DBFS
There seems to be no consistency as to what directories should be on DBFS for when GoldenGate is implemented with RAC.
Here I will share my thoughts based on issues encountered.
oracle@test1:/opt/oracle/12.2.0/ggs01$ ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033_FBO Linux, x64, 64bit (optimized), Oracle 11g on Jan 23 2017 21:54:15 Operating system character set identified as UTF-8. Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved. GGSCI (test1) 1> create subdirs Creating subdirectories under current directory /oracle/12.2.0/ggs01 Parameter files /oracle/12.2.0/ggs01/dirprm: created Report files /oracle/12.2.0/ggs01/dirrpt: created Checkpoint files /oracle/12.2.0/ggs01/dirchk: created Process status files /oracle/12.2.0/ggs01/dirpcs: created SQL script files /oracle/12.2.0/ggs01/dirsql: created Database definitions files /oracle/12.2.0/ggs01/dirdef: created Extract data files /oracle/12.2.0/ggs01/dirdat: created Temporary files /oracle/12.2.0/ggs01/dirtmp: created Credential store files /oracle/12.2.0/ggs01/dircrd: created Masterkey wallet files /oracle/12.2.0/ggs01/dirwlt: created Dump files /oracle/12.2.0/ggs01/dirdmp: created GGSCI (test1) 2> $ ls -ld dir* lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 dirchk -> /dbfs_client/ggs01/dirchk lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 dircrd -> /dbfs_client/ggs01/dircrd lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 dirdat -> /dbfs_client/ggs01/dirdat lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 dirdef -> /dbfs_client/ggs01/dirdef lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 dirdmp -> /dbfs_client/ggs01/dirdmp lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 dirout -> /dbfs_client/ggs01/dirout drwxr-x--- 2 ggsuser oinstall 4096 Mar 20 2017 dirpcs lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 dirprm -> /dbfs_client/ggs01/dirprm lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 dirrpt -> /dbfs_client/ggs01/dirrpt lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 dirsql -> /dbfs_client/ggs01/dirsql GoldenGate maintains data that it swaps to disk in dirtmp. With all the issues for DBFS, might be better on local. lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 dirtmp -> /dbfs_client/ggs01/dirtmp lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 dirwlt -> /dbfs_client/ggs01/dirwlt lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 dirwww -> /dbfs_client/ggs01/dirwww lrwxrwxrwx 1 ggsuser oinstall 23 Mar 20 2017 BR -> /dbfs_client/ggs01/BR
Here are errors when applying GoldenGate Patchset.
The errors were due to the stack being down from after running opatchauto apply -norestart which results in DBFS offline for the instance.
Errors can be avoided if directories are local as they should be.
The following actions have failed: CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirout CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/image CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/schema CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style CopyAction::apply(): cannot mkdirs on parent directory /oracle/12.2.0/ggs01/dirwww/style
Use Oracle RAC database as a baseline.
Are alert logs, trace files, etc… on shared volume if Oracle software is installed locally?
Playing With Service Relocation 12c
With 12c, use verbose to display services running. [oracle@racnode-dc1-1 rac_relocate]$ srvctl -V srvctl version: 12.1.0.2.0 [oracle@racnode-dc1-1 rac_relocate]$ srvctl status database -d hawk -v Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open. Instance hawk2 is running on node racnode-dc1-2 with online services p21,p22,p23,p24,p25. Instance status: Open. [oracle@racnode-dc1-1 rac_relocate]$ srvctl status instance -d hawk -i hawk1 -v Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open. [oracle@racnode-dc1-1 rac_relocate]$ srvctl status instance -d hawk -i hawk2 -v Instance hawk2 is running on node racnode-dc1-2 with online services p21,p22,p23,p24,p25. Instance status: Open. There is option to provide comma delimited list of services to check the status. Unfortunately, option is not available for relocation which I failed to understand. [oracle@racnode-dc1-1 rac_relocate]$ srvctl status service -d hawk -s "p11,p12,p13,p14" Service p11 is running on instance(s) hawk1 Service p12 is running on instance(s) hawk1 Service p13 is running on instance(s) hawk1 Service p14 is running on instance(s) hawk1 [oracle@racnode-dc1-1 rac_relocate]$ srvctl status service -d hawk -s "p21,p22,p23,p24,p25" Service p21 is running on instance(s) hawk2 Service p22 is running on instance(s) hawk2 Service p23 is running on instance(s) hawk2 Service p24 is running on instance(s) hawk2 Service p25 is running on instance(s) hawk2
Puzzled that status for services is able to use delimited list where as relocation is not.
I have blogged about new features for service failover: 12.1 Improved Service Failover
Another test shows that it’s working as it should be.
[oracle@racnode-dc1-1 ~]$ srvctl status database -d hawk -v Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open. Instance hawk2 is running on node racnode-dc1-2 with online services p21,p22,p23,p24,p25. Instance status: Open. [oracle@racnode-dc1-1 ~]$ srvctl stop instance -d hawk -instance hawk1 -failover [oracle@racnode-dc1-1 ~]$ srvctl status database -d hawk -v Instance hawk1 is not running on node racnode-dc1-1 Instance hawk2 is running on node racnode-dc1-2 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open. [oracle@racnode-dc1-1 ~]$ [root@racnode-dc1-1 ~]# crsctl stop crs [root@racnode-dc1-1 ~]# crsctl start crs [oracle@racnode-dc1-1 ~]$ srvctl status database -d hawk -v Instance hawk1 is not running on node racnode-dc1-1 Instance hawk2 is running on node racnode-dc1-2 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open. [oracle@racnode-dc1-1 ~]$ [oracle@racnode-dc1-1 ~]$ srvctl start database -d hawk [oracle@racnode-dc1-1 ~]$ srvctl status database -d hawk -v Instance hawk1 is running on node racnode-dc1-1. Instance status: Open. Instance hawk2 is running on node racnode-dc1-2 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open. [oracle@racnode-dc1-1 ~]$
However, the requirement is to relocate services versus failover.
Here are scripts and demo for that.
Script will only work for 2-nodes RAC and service is running on 1 instance only.
[oracle@racnode-dc1-1 ~]$ srvctl config service -d hawk |egrep 'Service name|instances' Service name: p11 Preferred instances: hawk1 Available instances: hawk2 Service name: p12 Preferred instances: hawk1 Available instances: hawk2 Service name: p13 Preferred instances: hawk1 Available instances: hawk2 Service name: p14 Preferred instances: hawk1 Available instances: hawk2 Service name: p21 Preferred instances: hawk2 Available instances: hawk1 Service name: p22 Preferred instances: hawk2 Available instances: hawk1 Service name: p23 Preferred instances: hawk2 Available instances: hawk1 Service name: p24 Preferred instances: hawk2 Available instances: hawk1 Service name: p25 Preferred instances: hawk2 Available instances: hawk1 [oracle@racnode-dc1-1 ~]$DEMO:
[oracle@racnode-dc1-1 rac_relocate]$ ls *relocate*.sh relocate_service.sh validate_relocate_service.sh [oracle@racnode-dc1-1 rac_relocate]$ ls *restore*.sh restore_service_instance1.sh restore_service_instance2.sh [oracle@racnode-dc1-1 rac_relocate]$ ======================================================================== +++++++ SAVE SERVICES LOCATION AND PREVENT ACCIDENTAL OVERWRITE ======================================================================== [oracle@racnode-dc1-1 rac_relocate]$ srvctl status database -d hawk -v > /tmp/service.org [oracle@racnode-dc1-1 rac_relocate]$ chmod 400 /tmp/service.org; ll /tmp/service.org; cat /tmp/service.org -r-------- 1 oracle oinstall 222 Jul 18 14:54 /tmp/service.org Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open. Instance hawk2 is running on node racnode-dc1-2 with online services p21,p22,p23,p24,p25. Instance status: Open. [oracle@racnode-dc1-1 rac_relocate]$ srvctl status database -d hawk -v > /tmp/service.org -bash: /tmp/service.org: Permission denied [oracle@racnode-dc1-1 rac_relocate]$ ======================================================================== +++++++ RELOCATE SERVICES FROM INSTANCE 1 TO 2 Validate is similar to RMAN validate. No relocation is performed and only syntax is provided for verification. ======================================================================== [oracle@racnode-dc1-1 rac_relocate]$ ./validate_relocate_service.sh ./validate_relocate_service.sh: line 4: 1: ---> USAGE: ./validate_relocate_service.sh -db_unique_name -oldinst# -newinst# [oracle@racnode-dc1-1 rac_relocate]$ ./validate_relocate_service.sh hawk 1 2 + OUTF=/tmp/service_1.conf + srvctl status instance -d hawk -instance hawk1 -v + ls -l /tmp/service_1.conf -rw-r--r-- 1 oracle oinstall 109 Jul 18 14:59 /tmp/service_1.conf + cat /tmp/service_1.conf Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open. + set +x ************************************** ***** SERVICES THAT WILL BE RELOCATED: ************************************** srvctl relocate service -d hawk -service p11 -oldinst hawk1 -newinst hawk2 srvctl relocate service -d hawk -service p12 -oldinst hawk1 -newinst hawk2 srvctl relocate service -d hawk -service p13 -oldinst hawk1 -newinst hawk2 srvctl relocate service -d hawk -service p14 -oldinst hawk1 -newinst hawk2 [oracle@racnode-dc1-1 rac_relocate]$ ./relocate_service.sh hawk 1 2 -rw-r--r-- 1 oracle oinstall 109 Jul 18 15:00 /tmp/service_1.conf Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open. + srvctl relocate service -d hawk -service p11 -oldinst hawk1 -newinst hawk2 + set +x + srvctl relocate service -d hawk -service p12 -oldinst hawk1 -newinst hawk2 + set +x + srvctl relocate service -d hawk -service p13 -oldinst hawk1 -newinst hawk2 + set +x + srvctl relocate service -d hawk -service p14 -oldinst hawk1 -newinst hawk2 + set +x + srvctl status instance -d hawk -instance hawk1 -v Instance hawk1 is running on node racnode-dc1-1. Instance status: Open. + srvctl status instance -d hawk -instance hawk2 -v Instance hawk2 is running on node racnode-dc1-2 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open. + set +x [oracle@racnode-dc1-1 rac_relocate]$ ======================================================================== +++++++ RELOCATE SERVICES FROM INSTANCE 2 TO 1 ======================================================================== [oracle@racnode-dc1-1 rac_relocate]$ ./relocate_service.sh hawk 2 1 -rw-r--r-- 1 oracle oinstall 129 Jul 18 15:02 /tmp/service_2.conf Instance hawk2 is running on node racnode-dc1-2 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open. + srvctl relocate service -d hawk -service p11 -oldinst hawk2 -newinst hawk1 + set +x + srvctl relocate service -d hawk -service p12 -oldinst hawk2 -newinst hawk1 + set +x + srvctl relocate service -d hawk -service p13 -oldinst hawk2 -newinst hawk1 + set +x + srvctl relocate service -d hawk -service p14 -oldinst hawk2 -newinst hawk1 + set +x + srvctl relocate service -d hawk -service p21 -oldinst hawk2 -newinst hawk1 + set +x + srvctl relocate service -d hawk -service p22 -oldinst hawk2 -newinst hawk1 + set +x + srvctl relocate service -d hawk -service p23 -oldinst hawk2 -newinst hawk1 + set +x + srvctl relocate service -d hawk -service p24 -oldinst hawk2 -newinst hawk1 + set +x + srvctl relocate service -d hawk -service p25 -oldinst hawk2 -newinst hawk1 + set +x + srvctl status instance -d hawk -instance hawk2 -v Instance hawk2 is running on node racnode-dc1-2. Instance status: Open. + srvctl status instance -d hawk -instance hawk1 -v Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open. + set +x [oracle@racnode-dc1-1 rac_relocate]$ ======================================================================== +++++++ RESTORE SERVICES FOR INSTANCE ======================================================================== [oracle@racnode-dc1-1 rac_relocate]$ srvctl status database -d hawk -v Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open. Instance hawk2 is running on node racnode-dc1-2. Instance status: Open. [oracle@racnode-dc1-1 rac_relocate]$ ./restore_service_instance2.sh ./restore_service_instance2.sh: line 4: 1: ---> USAGE: ./restore_service_instance2.sh -db_unique_name [oracle@racnode-dc1-1 rac_relocate]$ ./restore_service_instance2.sh hawk + srvctl relocate service -d hawk -service p21 -oldinst hawk1 -newinst hawk2 + set +x + srvctl relocate service -d hawk -service p22 -oldinst hawk1 -newinst hawk2 + set +x + srvctl relocate service -d hawk -service p23 -oldinst hawk1 -newinst hawk2 + set +x + srvctl relocate service -d hawk -service p24 -oldinst hawk1 -newinst hawk2 + set +x + srvctl relocate service -d hawk -service p25 -oldinst hawk1 -newinst hawk2 + set +x [oracle@racnode-dc1-1 rac_relocate]$ srvctl status database -d hawk -v Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open. Instance hawk2 is running on node racnode-dc1-2 with online services p21,p22,p23,p24,p25. Instance status: Open. [oracle@racnode-dc1-1 rac_relocate]$CODE:
========================================================================
+++++++ validate_relocate_service.sh
========================================================================
#!/bin/sh -e
DN=`dirname $0`
BN=`basename $0`
DB=${1:?"---> USAGE: $DN/$BN -db_unique_name -oldinst# -newinst#"}
OLD=${2:?"---> USAGE: $DN/$BN -db_unique_name -oldinst# -newinst#"}
NEW=${3:?"---> USAGE: $DN/$BN -db_unique_name -oldinst# -newinst#"}
set -x
OUTF=/tmp/service_${OLD}.conf
srvctl status instance -d ${DB} -instance ${DB}${OLD} -v > $OUTF
ls -l $OUTF;cat $OUTF
set +x
export svc=`tail -1 $OUTF | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
IFS=","
echo
echo "**************************************"
echo "***** SERVICES THAT WILL BE RELOCATED:"
echo "**************************************"
for s in ${svc}
do
echo "srvctl relocate service -d ${DB} -service ${s} -oldinst ${DB}${OLD} -newinst ${DB}${NEW}"
done
exit
========================================================================
+++++++ relocate_service.sh
========================================================================
#!/bin/sh -e
DN=`dirname $0`
BN=`basename $0`
DB=${1:?"---> USAGE: $DN/$BN -db_unique_name -oldinst# -newinst#"}
OLD=${2:?"---> USAGE: $DN/$BN -db_unique_name -oldinst# -newinst#"}
NEW=${3:?"---> USAGE: $DN/$BN -db_unique_name -oldinst# -newinst#"}
OUTF=/tmp/service_${OLD}.conf
srvctl status instance -d ${DB} -instance ${DB}${OLD} -v > $OUTF
ls -l $OUTF;cat $OUTF
export svc=`tail -1 $OUTF | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
IFS=","
for s in ${svc}
do
set -x
srvctl relocate service -d ${DB} -service ${s} -oldinst ${DB}${OLD} -newinst ${DB}${NEW}
set +x
done
set -x
srvctl status instance -d ${DB} -instance ${DB}${OLD} -v
srvctl status instance -d ${DB} -instance ${DB}${NEW} -v
set +x
exit
========================================================================
+++++++ restore_service_instance1.sh
========================================================================
#!/bin/sh -e
DN=`dirname $0`
BN=`basename $0`
DB=${1:?"---> USAGE: $DN/$BN -db_unique_name"}
export svc=`head -1 /tmp/service.org | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
IFS=","
for s in ${svc}
do
set -x
srvctl relocate service -d ${DB} -service ${s} -oldinst ${DB}2 -newinst ${DB}1
set +x
done
exit
========================================================================
+++++++ restore_service_instance2.sh
========================================================================
#!/bin/sh -e
DN=`dirname $0`
BN=`basename $0`
DB=${1:?"---> USAGE: $DN/$BN -db_unique_name"}
export svc=`tail -1 /tmp/service.org | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
IFS=","
for s in ${svc}
do
set -x
srvctl relocate service -d ${DB} -service ${s} -oldinst ${DB}1 -newinst ${DB}2
set +x
done
exit
How To Delete Integrated Extract Replicat
Why is this important?
If processes are not unregistered from database, they are orphaned.
Hence:
unregister replicat $replicat_name DATABASE
unregister extract $extract_name DATABASE
$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13 Operating system character set identified as UTF-8. Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved. GGSCI 1> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING E_DB 00:00:05 00:00:00 EXTRACT RUNNING P_DB 00:00:00 00:00:00 REPLICAT RUNNING R_OWL 00:00:00 00:00:03 REPLICAT STOPPED R_JAY 00:00:00 00:24:15 GGSCI 2> info E_DB debug EXTRACT E_DB Last Started 2018-06-18 08:48 Status RUNNING Checkpoint Lag 00:00:05 (updated 00:00:00 ago) Process ID 365696 Log Read Checkpoint Oracle Integrated Redo Logs 2018-07-13 20:20:45 Seqno 151864, RBA 31183128 SCN 188.1068581841 (808522433489) GGSCI 3> info r_jay debug REPLICAT R_JAY Last Started 2018-06-16 11:42 Status STOPPED INTEGRATED Checkpoint Lag 00:00:00 (updated 578:24:33 ago) Log Read Checkpoint File ./dirdat/ 2018-06-19 17:55:08.604509 RBA 409140739 GGSCI 4> info r_owl debug REPLICAT R_OWL Last Started 2018-06-16 11:42 Status RUNNING Checkpoint Lag 00:00:00 (updated 00:00:03 ago) Process ID 284878 Log Read Checkpoint File ./dirdat/ 2018-07-13 20:21:09.000628 RBA 132791008 GGSCI 5> exit SQL> @pr "select * from DBA_APPLY" APPLY_NAME : OGG$R_JAY QUEUE_NAME : OGGQ$R_JAY APPLY_CAPTURED : YES APPLY_USER : GGSUSER13 APPLY_TAG : 00 STATUS : DISABLED MAX_APPLIED_MESSAGE_NUMBER : 0 STATUS_CHANGE_TIME : 19-jun-2018 17:56:28 MESSAGE_DELIVERY_MODE : CAPTURED PURPOSE : GoldenGate Apply LCRID_VERSION : 2 ------------------------- APPLY_NAME : OGG$R_MIG - ORPHANED REPLICAT AS PROCESS DOES NOT EXISTS QUEUE_NAME : OGGQ$R_MIG QUEUE_OWNER : GGSUSER12 APPLY_CAPTURED : YES APPLY_USER : GGSUSER12 APPLY_TAG : 00 STATUS : DISABLED MAX_APPLIED_MESSAGE_NUMBER : 0 STATUS_CHANGE_TIME : 24-feb-2018 07:12:24 MESSAGE_DELIVERY_MODE : CAPTURED PURPOSE : GoldenGate Apply LCRID_VERSION : 2 ------------------------- APPLY_NAME : OGG$E_DB QUEUE_NAME : OGG$Q_E_DB QUEUE_OWNER : GGSUSER13 APPLY_CAPTURED : YES RULE_SET_NAME : RULESET$_9 APPLY_TAG : 00 STATUS : ENABLED MAX_APPLIED_MESSAGE_NUMBER : 0 STATUS_CHANGE_TIME : 18-jun-2018 08:48:02 MESSAGE_DELIVERY_MODE : CAPTURED PURPOSE : GoldenGate Capture LCRID_VERSION : 2 ------------------------- PL/SQL procedure successfully completed. SQL> @pr "select * from DBA_CAPTURE" CAPTURE_NAME : OGG$CAP_E_DB QUEUE_NAME : OGG$Q_E_DB START_SCN : 776572270090 STATUS : ENABLED CAPTURED_SCN : 808484653323 APPLIED_SCN : 808484649784 USE_DATABASE_LINK : NO FIRST_SCN : 776572270090 SOURCE_DATABASE : DB01 SOURCE_DBID : 689358028 SOURCE_RESETLOGS_SCN : 279476595350 SOURCE_RESETLOGS_TIME : 826720979 LOGMINER_ID : 1 MAX_CHECKPOINT_SCN : 808510343793 REQUIRED_CHECKPOINT_SCN : 808484621637 LOGFILE_ASSIGNMENT : IMPLICIT STATUS_CHANGE_TIME : 18-jun-2018 08:48:04 VERSION : 12.2.0.1.0 CAPTURE_TYPE : LOCAL CHECKPOINT_RETENTION_TIME : 7 PURPOSE : GoldenGate Capture SOURCE_ROOT_NAME : DB01 CLIENT_NAME : E_DB CLIENT_STATUS : ATTACHED OLDEST_SCN : 776572270090 FILTERED_SCN : 773378341423 ------------------------- PL/SQL procedure successfully completed.
Oracle Cloud Orchestration
Oracle Cloud has a pretty cool concept (Orchestration) to recreate an instance; however, it’s all hard coded.
The orchestration cannot be shared with some else to create the configuration with different name or from different account.
What version is the Orchestration and shouldn’t it be in the metadata.
{
"account" : "/Compute-601138841/default",
"description" : "",
"tags" : [ ],
"name" : "/Compute-601138841/me@yahoo.com/qs-classic",
"objects" : [ {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"managed" : true,
"description" : "qs-classic Storage Volume",
"bootable" : true,
"shared" : false,
"imagelist" : "/oracle/public/OL_7.2_UEKR4_x86_64",
"size" : "128G",
"properties" : [ "/oracle/public/storage/default" ],
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_storage"
},
"label" : "qs-classic_storage_1",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "StorageVolume",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/storage_1"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"description" : "qs-classic Security Rule (IP Network) Egress",
"tags" : [ "qs-classic" ],
"flowDirection" : "egress",
"acl" : "{{qs-classic_AccessControlList:name}}",
"enabledFlag" : true,
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_SecurityRule_Egress"
},
"label" : "qs-classic_SecurityRule_Egress",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "SecurityRule",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/f10f9f0d-9577-4f83-9b81-e1cc9d8bc9df"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"ipAddressPool" : "/oracle/public/public-ippool",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_IP_eth0_public"
},
"label" : "qs-classic_IP_eth0_public",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "IpAddressReservation",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/ac8dbfc5-d560-47e1-8d33-aeb54a0cc4c8"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"description" : "qs-classic Security Rule (IP Network)",
"tags" : [ "qs-classic" ],
"flowDirection" : "ingress",
"acl" : "{{qs-classic_AccessControlList:name}}",
"enabledFlag" : true,
"secProtocols" : [ "/oracle/public/ssh" ],
"dstVnicSet" : "{{qs-classic_VnicSet:name}}",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_SecurityRule"
},
"label" : "qs-classic_SecurityRule",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "SecurityRule",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/0ddfc441-7855-47a4-856f-15c400265975"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"appliedAcls" : [ "{{qs-classic_AccessControlList:name}}" ],
"description" : "qs-classic Virtual NIC Set",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_VnicSet",
"tags" : [ "qs-classic" ]
},
"label" : "qs-classic_VnicSet",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "VirtualNicSet",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/f7841713-54d1-4c9a-a6cb-32e3e84c753f"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"enabledFlag" : true,
"description" : "qs-classic Access Control List",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_AccessControlList",
"tags" : [ "qs-classic" ]
},
"label" : "qs-classic_AccessControlList",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "Acl",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/2f2915ca-5047-45bb-8875-0ae183a6425f"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : true,
"template" : {
"ipAddressPool" : "/oracle/public/cloud-ippool",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic_IP_eth0_cloud"
},
"label" : "qs-classic_IP_eth0_cloud",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "IpAddressReservation",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/e3f78f72-3eca-4dd1-a054-fdd3fee8a51b"
}, {
"account" : "/Compute-601138841/default",
"desired_state" : "inherit",
"description" : "",
"persistent" : false,
"template" : {
"networking" : {
"eth0" : {
"vnic" : "/Compute-601138841/me@yahoo.com/qs-classic_eth0",
"ipnetwork" : "/Compute-601138841/default",
"is_default_gateway" : true,
"nat" : [ "network/v1/ipreservation:{{qs-classic_IP_eth0_public:name}}", "network/v1/ipreservation:{{qs-classic_IP_eth0_cloud:name}}" ],
"vnicsets" : [ "{{qs-classic_VnicSet:name}}" ]
}
},
"name" : "/Compute-601138841/me@yahoo.com/qs-classic",
"boot_order" : [ 1 ],
"storage_attachments" : [ {
"volume" : "{{qs-classic_storage_1:name}}",
"index" : 1
} ],
"label" : "qs-classic",
"shape" : "oc3",
"imagelist" : "/oracle/public/OL_7.2_UEKR4_x86_64",
"sshkeys" : [ "/Compute-601138841/me@yahoo.com/qs-classic" ]
},
"label" : "qs-classic_instance",
"orchestration" : "/Compute-601138841/me@yahoo.com/qs-classic",
"type" : "Instance",
"name" : "/Compute-601138841/me@yahoo.com/qs-classic/instance"
} ],
"desired_state" : "active"
}
Using Postman For REST API on Oracle Cloud Infrastructure
Just completed quick training for Oracle Cloud Infrastructure (OCI)
REST API can be used on OCI for automation and presentation.
I find presentation for JSON results from CLI on server is UGLY.
Finally, I found POSTMAN which does to very nice job of presentation.
POSTMAN has results history, command history, search function.
https://www.getpostman.com/apps
Using GoldenGate LogDump To Find Bad Data
GoldenGate Primary Extract from source database captured data without any issues;
however, target was not able to consume the data since GoldenGate process would ABEND.
Unfortunately, I cannot provide all the details but high level.
Logdump 2433 >pos 0 +++ Starting with GoldenGate 12.2 TDR – Table Definition Record is in trail +++ This will provide metadata for the table Reading forward from RBA 0 Logdump 2434 >SCANFORMETADATA ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x00) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 86 (x0056) IO Time : 2018/03/25 18:24:23.307.797 IOType : 170 (xaa) OrigNode : 1 (x01) TransInd : . (x01) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) DDR/TDR Idx: (001, 000) AuditPos : 3277290592 Continued : N (x00) RecCount : 1 (x01) 2018/03/25 18:24:23.307.797 Metadata Len 86 RBA 1689 Name: * DDR Version: 1 Database type: ORACLE Character set ID: CESU-8 National character set ID: UTF-16 Locale: neutral Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14 TimeZone: GMT-05:00 Global name: DBNAME * Logdump 2435 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x00) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 561 (x0231) IO Time : 2018/03/25 18:28:16.317.879 IOType : 170 (xaa) OrigNode : 2 (x02) TransInd : . (x01) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) DDR/TDR Idx: (001, 001) AuditPos : 3352410292 Continued : N (x00) RecCount : 1 (x01) 2018/03/25 18:28:16.317.879 Metadata Len 561 RBA 1826 Name: SCHEMA.TABLE * 1)Name 2)Data Type 3)External Length 4)Fetch Offset 5)Scale 6)Level 7)Null 8)Bump if Odd 9)Internal Length 10)Binary Length 11)Table Length 12)Most Sig DT 13)Least Sig DT 14)High Precision 15)Low Precision 16)Elementary Item 17)Occurs 18)Key Column 19)Sub DataType 20)Native DataType 21)Character Set 22)Character Length 23)LOB Type 24)Partial Type * TDR version: 1 Definition for table SCHEMA.SCHEMA Record Length: 4298 Columns: 7 ID 64 50 0 0 0 1 0 50 50 50 0 0 0 0 1 0 1 2 2 -1 0 0 0 A B C D E F G H I J K L M N O P Q R Position 18)Key Column identify column is Primary Key; hence, ID a primary key column. I labeled each column using the alphabet for reference and R is the 18th letter of the alphabet. Even though ID is the first column of the table, GoldenGate offset starts with 0 SQL> desc SCHEMA.TABLE Name Null? Type ----------------------------------------- -------- ---------------------------- 0 ID NOT NULL NUMBER 5 TEXT VARCHAR2(4000) SQL> r 1 select b.column_name,a.constraint_type 2 from dba_constraints a, dba_cons_columns b 3 where a.table_name = b.table_name 4 and a.constraint_name=b.constraint_name 5 and a.constraint_type = 'P' 6 and a.table_name='TABLE' 7 and a.owner='SCHEMA' 8* COLUMN_NAME C ------------------------------ - ID P Logdump 2500 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x0c) UndoFlag : . (x00) BeforeAfter: A (x41) RecLength : 885 (x0375) IO Time : 2018/05/10 14:16:43.000.514 IOType : 15 (x0f) OrigNode : 255 (xff) TransInd : . (x02) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 36261 AuditPos : 2145515112 Continued : N (x00) RecCount : 1 (x01) 2018/05/10 14:16:43.000.514 FieldComp Len 885 RBA 88598006 Name: SCHEMA.TABLE (TDR Index: 1) After Image: .................... 0000 000c 0000 0008 3239 3533 3432 3936 0001 0009 | ........29534296.... 0000 0005 3937 3932 3600 0200 0500 0000 0131 0003 | .................... 0005 0000 0001 3600 0400 0b00 0000 0731 3738 3337 | .................... 3939 0005 0325 0000 0321 456e 6a6f 7920 796f 7572 | .................... 2073 7461 7920 696e 206f 7572 2068 6f74 656c 7320 | .................... 7769 7468 2074 6865 206d 6f73 7420 6469 7363 6f75 | .................... 6e74 6564 2072 6174 | .................... Column 0 (x0000), Len 12 (x000c) 0000 0008 3239 3533 3432 3936 | ....29534296 --- PRIMARY KEY VALUE Column 1 (x0001), Len 9 (x0009) 0000 0005 3937 3932 36 | ....97926 Column 2 (x0002), Len 5 (x0005) 0000 0001 31 | ....1 Column 3 (x0003), Len 5 (x0005) 0000 0001 36 | ....6 SQL> select id, substr(TEXT,1,30) txt, vsize(TEXT), length(TEXT) from SCHEMA.TABLE where ID in (29534296); ID LTXT VSIZE(LTEXT) LENGTH(LTEXT) ---------- ------------------------------ ------------ ------------- 29534296 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 801 800 SQL> For database, NLS_LENGTH_SEMANTICS=BYTE; hence, 800 characters should equal 800 bytes. However, one character is most likely a multi-byte character which target cannot consume as it is not able to handle multi-byte.