Michael Dinh
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.
Goldengate REPORTING P2
Previous post for Goldengate REPORTING
Goldengate reporting has to be the least implemented functionality until it is needed and often in hindsight.
Here is an example of what I would normally implement.
STATOPTIONS RESETREPORTSTATS
REPORT AT 00:01
REPORTROLLOVER AT 00:01
REPORTCOUNT EVERY 15 MINUTES, RATE
DISCARDROLLOVER AT 00:01 ON SUNDAY
If business only cares about monthly data, then would not make sense to collect daily nor would it make sense for business to request such.
DataGuard Convention
Good convention and implementation make life and automation so much simpler and more time for golfing.
I have seen some really poor and really good implementation and here’s a good one.
Wish I can take credit for it and unfortunately I cannot.
The scripts were created by whoa.
Scripts an be run from primary or standby for any instances provided profile to source database environment exists on host.
Use ORACLE_UNQNAME for DataGuard Environment
==================================================================================================== +++ PRIMARY RACONENODE ==================================================================================================== SQL> show parameter db%name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string test db_unique_name string test SQL> $ sysresv|tail -1 Oracle Instance alive for sid "test_1" $ env|grep ORACLE ORACLE_SID=test_1 (db_name) ORACLE_UNQNAME=test (db_unique_name) $ srvctl config database -d $ORACLE_UNQNAME Database unique name: test Database name: test Oracle home: /u01/app/oracle/product/11g/db_1 Oracle user: oracle Spfile: +FLASH/test/spfiletest.ora Domain: Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: test Database instances: Disk Groups: FLASH,DATA Mount point paths: Services: testsvc Type: RACOneNode Online relocation timeout: 30 Instance name prefix: test Candidate servers: host01,host02 Database is administrator managed ==================================================================================================== +++ STANDBY NON-RAC ==================================================================================================== SQL> show parameter db%name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string test db_unique_name string testdr SQL> $ sysresv|tail -1 Oracle Instance alive for sid "test" $ env|grep ORACLE ORACLE_SID=test (db_name) ORACLE_UNQNAME=testdr (db_unique_name) $ srvctl config database -d $ORACLE_UNQNAME Database unique name: testdr Database name: test Oracle home: /u01/app/oracle/product/11g/db_1 Oracle user: oracle Spfile: Domain: Start options: open Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Database instance: test Disk Groups: DATA,FLASH Services: ==================================================================================================== DATAGUARD BROKER CONFIGURATION ==================================================================================================== DGMGRL> show configuration Configuration - dg_test (db_name) Protection Mode: MaxPerformance Databases: test - Primary database (db_unique_name) testdr - Physical standby database (db_unique_name) Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> show database test Database - test Role: PRIMARY Intended State: TRANSPORT-OFF Instance(s): test_1 test_2 Database Status: SUCCESS DGMGRL> show database testdr Database - testdr Role: PHYSICAL STANDBY Intended State: APPLY-OFF Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 7 seconds (computed 0 seconds ago) Apply Rate: (unknown) Real Time Query: OFF Instance(s): test Database Status: SUCCESS DGMGRL> exit ==================================================================================================== ls -l dg*.sh ==================================================================================================== -rwxr-xr-x 1 oracle dba 377 May 08 21:50 dg_lag.sh -rwxr-x--- 1 oracle dba 445 May 08 20:12 dg_start.sh -rwxr-xr-x 1 oracle dba 337 May 08 20:05 dg_status.sh -rwxr-x--- 1 oracle dba 447 May 08 20:12 dg_stop.sh ==================================================================================================== dg_lag.sh ==================================================================================================== #!/bin/sh -e check_dg() { dgmgrl -echo << END connect / show database ${ORACLE_SID} SendQEntries show database ${ORACLE_UNQNAME} RecvQEntries show database ${ORACLE_UNQNAME} exit END } . ~/oracle_staging check_dg . ~/oracle_testing check_dg exit ==================================================================================================== cat dg_start.sh ==================================================================================================== #!/bin/sh -e check_dg() { dgmgrl -echo << END connect / edit database ${ORACLE_SID} set state='TRANSPORT-ON'; edit database ${ORACLE_UNQNAME} set state='APPLY-ON'; show configuration show database ${ORACLE_SID} show database ${ORACLE_UNQNAME} exit END } . ~/oracle_staging check_dg . ~/oracle_testing check_dg exit ==================================================================================================== dg_status.sh ==================================================================================================== #!/bin/sh -e check_dg() { dgmgrl -echo << END connect / show configuration show database ${ORACLE_SID} show database ${ORACLE_UNQNAME} exit END } . ~/oracle_staging check_dg . ~/oracle_testing check_dg exit ==================================================================================================== dg_stop.sh ==================================================================================================== #!/bin/sh -e check_dg() { dgmgrl -echo << END connect / edit database ${ORACLE_SID} set state='TRANSPORT-OFF'; edit database ${ORACLE_UNQNAME} set state='APPLY-OFF'; show configuration show database ${ORACLE_SID} show database ${ORACLE_UNQNAME} exit END } check_dg . ~/oracle_staging check_dg . ~/oracle_testing check_dg exit
DBFS Nightmare
==================================================================================================== How to cleanup DBFS tablespace after removing files at DBFS filesystem (Doc ID 2331565.1) ==================================================================================================== High level overview of the DBMS_DBFS_SFS.REORGANIZEFS procedure: ---------------------------------------------------------------------------------------------------- 1) Create a NEW tablespace 2) Create a temporary filesystem with dbms_dbfs_sfs.createFilesystem in the new tablespace. 3) Run dbms_dbfs_sfs.reorganizeFS -->> EXEC DBMS_DBFS_SFS.REORGANIZEFS(SRCSTORE=>'FS_FS1', DSTSTORE=>'FS_TMP_FS'); 4) The dbfs data is now in the smaller NEW tablespace. 5) Drop the temporay filesystem with dbms_dbfs_sfs.dropFilesystem 6) The OLD original tablespace is empty now. ---------------------------------------------------------------------------------------------------- To reuse the same tablespace again please follow the below steps ---------------------------------------------------------------------------------------------------- 7) Create a temporary filesystem with dbms_dbfs_sfs.createFilesystem in the OLD ORIGINAL tablespace. 8) Run dbms_dbfs_sfs.reorganizeFS 9) The dbfs data is now in the smaller the ORIGINAL tablespace. 10) Drop the temporay filesystem with dbms_dbfs_sfs.dropFilesystem 11) The NEW small tablespace is empty now and can be dropped. ==================================================================================================== DBFS tablespace keep growing not using expired blocks (Doc ID 2327299.1) ==================================================================================================== ---------------------------------------------------------------------------------------------------- First perform the DBFS re-org to cleanup the tablespace after removing the files at file system level by following below document ---------------------------------------------------------------------------------------------------- How to cleanup DBFS tablespace after removing files at DBFS filesystem (Doc ID 2331565.1) ---------------------------------------------------------------------------------------------------- After the re-org, set the below parameter to reuse the expired blocks ---------------------------------------------------------------------------------------------------- ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 0; ==================================================================================================== ORA-1654 - DBFS FREE SPACE NOT RECLAIMED AFTER CLEARING FILES (Doc ID 1948305.1) ==================================================================================================== ---------------------------------------------------------------------------------------------------- a) Check Lob retention setting: ---------------------------------------------------------------------------------------------------- ex. SQL> select table_name,retention_type,retention_value,retention,securefile from dba_lobs where table_name = 'T_FS1'; ---------------------------------------------------------------------------------------------------- b) Change LOB retention to none. ---------------------------------------------------------------------------------------------------- ex. SQL> alter table DBFS_USER.T_FS1 modify lob (FILEDATA) (retention none);
After countless discussions, team found GOLD.
12.1 Shrinking and Reorganizing DBFS Filesystems STEPS:sqlplus /as sysdba
create bigfile tablespace DBFS_NEW datafile size 8G autoextend on next 1G maxsize 70G;
grant dba to dbfs_user;
alter user dbfs_user default role all;
exit
+++ REORGANIZE DBFS FILESYSTEM FS1 IN TABLESPACE DBFS_TS INTO A NEW TABLESPACE DBFS_NEW,
+++ USING A TEMPORARY FILESYSTEM NAMED TMP_FS, WHERE ALL FILESYSTEMS BELONG TO DATABASE USER DBFS_USER
cd $ORACLE_HOME/rdbms/admin
sqlplus dbfs_user
@dbfs_create_filesystem DBFS_NEW TMP_FS
exec dbms_dbfs_sfs.reorganizefs('FS1','TMP_FS');
@dbfs_drop_filesystem TMP_FS
purge user_recyclebin;
exit
sqlplus / aa sysdba
revoke dba from dbfs_user;
select count(*) from dba_extents where tablespace_name='DBFS_TS';
select count(*) from dba_extents where tablespace_name='DBFS_NEW';
-- DROP TABLESPACE HAVING ZERO EXTENTS
-- BE CAREFUL IN CASE USER AND DBFS ARE USING SAME TABLESPACE
Example:
create user dbfs_user identified by **** default tablespace dbfs_ts quota unlimited on dbfs_ts;
@$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem.sql dbfs_ts FS1
TRUE CONFESSION:
I created an imperfect plan at first that might have saved me from a major catastrophe.
Create DBFS_TMP in a separate Disk Group since I was concern existing may not have enough storage.
Reorg from DBFS_TS to DBFS_TMP (bad idea to name tablespace as DBFS_TMP as LOB Segments migrated here).
Reorg from DBFS_TMP back to DBFS_TS.
Drop tablespace DBFS_TMP.
There’s 29G difference between old and new. How much if this space will be reclaimable due to HWM in data file?
Performing reorg twice is really not an option in prod.
RESULTS:SEGMENT_NAME TABLESPACE_NAME MB EXTENTS ------------------------------ ------------------------------ ---------- ---------- LOB_SFS$_FST_1 DBFS_TS 30647.1875 1851 LOB_SFS$_FST_6225924 DBFS_NEW 1025.125 130WARNINGS:
This has only been tested in a vacuum (non-prod env w very low activities) – YMMV.
Multiplex Redo Log
When db_create_online_log_dest_1 is defined, REDO log is not multiplexed which is good for creating STANDBY REDO.
REDO log is created at db_create_online_log_dest_1 ONLY.
However, when creating ONLINE REDO, db_create_online_log_dest_1 should NOT be defined to be multiplexed.
REDO logs are created at db_create_file_dest and db_recovery_file_dest.
[oracle@db-asm-1 sql]$ sqlplus / as sysdba @ logfile.sql SQL*Plus: Release 12.2.0.1.0 Production on Thu May 3 05:56:30 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 05:56:30 SYS @ owl:>show parameter db_create NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA db_create_online_log_dest_1 +FRA db_create_online_log_dest_2 db_create_online_log_dest_3 db_create_online_log_dest_4 db_create_online_log_dest_5 05:56:30 SYS @ owl:>show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 7116M 05:56:30 SYS @ owl:>select group#,member,type,status from v$logfile order by 1,2 asc; GROUP# MEMBER TYPE STATUS ---------- -------------------------------------------------------------------------------- ------- ------- 1 +DATA/OWL/ONLINELOG/group_1.261.962643743 ONLINE 1 +FRA/OWL/ONLINELOG/group_1.257.962643743 2 +DATA/OWL/ONLINELOG/group_2.262.962643743 2 +FRA/OWL/ONLINELOG/group_2.258.962643743 3 +DATA/OWL/ONLINELOG/group_3.263.962643745 3 +FRA/OWL/ONLINELOG/group_3.259.962643745 6 rows selected. 05:56:30 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$log order by 1,2; GROUP# THREAD# SEQUENCE# BYTES STATUS ---------- ---------- ---------- ---------- ---------------- 1 1 79 104857600 CURRENT 2 1 77 104857600 INACTIVE 3 1 78 104857600 INACTIVE 05:56:30 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$standby_log order by 1,2; no rows selected 05:56:30 SYS @ owl:>alter database add standby logfile thread 1 group 11 size 104857600; Database altered. 05:57:03 SYS @ owl:>alter system set db_create_online_log_dest_1=''; System altered. 05:57:39 SYS @ owl:>alter database add logfile thread 1 group 4 size 104857600; Database altered. 05:58:01 SYS @ owl:>@logfile.sql 05:58:06 SYS @ owl:>set lines 200 tab off trimsp on pages 1000 05:58:06 SYS @ owl:>col member for a80 05:58:06 SYS @ owl:>break on TYPE 05:58:06 SYS @ owl:>set echo on 05:58:06 SYS @ owl:>show parameter db_create NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATA db_create_online_log_dest_1 db_create_online_log_dest_2 db_create_online_log_dest_3 db_create_online_log_dest_4 db_create_online_log_dest_5 05:58:06 SYS @ owl:>show parameter db_recovery_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +FRA db_recovery_file_dest_size big integer 7116M 05:58:06 SYS @ owl:>select group#,member,type,status from v$logfile order by 1,2 asc; GROUP# MEMBER TYPE STATUS ---------- -------------------------------------------------------------------------------- ------- ------- 1 +DATA/OWL/ONLINELOG/group_1.261.962643743 ONLINE 1 +FRA/OWL/ONLINELOG/group_1.257.962643743 2 +DATA/OWL/ONLINELOG/group_2.262.962643743 2 +FRA/OWL/ONLINELOG/group_2.258.962643743 3 +DATA/OWL/ONLINELOG/group_3.263.962643745 3 +FRA/OWL/ONLINELOG/group_3.259.962643745 4 +DATA/OWL/ONLINELOG/group_4.267.975131881 4 +FRA/OWL/ONLINELOG/group_4.295.975131881 11 +FRA/OWL/ONLINELOG/group_11.296.975131823 STANDBY 9 rows selected. 05:58:07 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$log order by 1,2; GROUP# THREAD# SEQUENCE# BYTES STATUS ---------- ---------- ---------- ---------- ---------------- 1 1 79 104857600 CURRENT 2 1 77 104857600 INACTIVE 3 1 78 104857600 INACTIVE 4 1 0 104857600 UNUSED 05:58:07 SYS @ owl:>select group#,thread#,sequence#,bytes,status from v$standby_log order by 1,2; GROUP# THREAD# SEQUENCE# BYTES STATUS ---------- ---------- ---------- ---------- ---------- 11 1 0 104857600 UNASSIGNED 05:58:07 SYS @ owl:>
Who’s Gathering DB Stats?
There was an incident where statistics were being gathered during prime operating hours causing performance issues. One DBA already verified GATHER_STATS_JOB has already been configured to not run during critical hours. Speculation is stats are being gathered manually and how to prove this? AUTO JOB has OPERATION : gather_database_stats (auto). MANUAL JOB is not being run by scheduler either; otherwise, there would be JOB_NAME. Half of the mystery is solve, but where is gather_table_stats running from? Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production SQL> @pr "SELECT ID,OPERATION,START_TIME,END_TIME,STATUS,JOB_NAME,SESSION_ID FROM dba_optstat_operations where START_TIME>trunc(sysdate-1) ORDER BY start_time DESC" ID : 6939 OPERATION : gather_table_stats START_TIME : 26-APR-18 05.00.12.387231 PM -07:00 END_TIME : 26-APR-18 05.00.21.509607 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 2871 ------------------------- ID : 6918 OPERATION : export_stats_for_dp START_TIME : 26-APR-18 03.47.09.574643 PM -07:00 END_TIME : 26-APR-18 03.47.25.336241 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 201 ------------------------- ID : 6917 OPERATION : gather_table_stats START_TIME : 26-APR-18 03.10.16.126374 PM -07:00 END_TIME : 26-APR-18 04.59.51.410241 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 2871 ------------------------- ID : 6916 OPERATION : gather_table_stats START_TIME : 26-APR-18 02.09.44.123132 PM -07:00 END_TIME : 26-APR-18 02.09.45.695904 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 2871 ------------------------- ID : 6915 OPERATION : gather_table_stats START_TIME : 26-APR-18 12.57.11.352671 PM -07:00 END_TIME : 26-APR-18 02.09.43.579331 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 2871 ------------------------- ID : 6922 OPERATION : restore_table_stats START_TIME : 26-APR-18 02.00.00.949528 AM -07:00 END_TIME : 26-APR-18 02.00.01.297300 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 877 ------------------------- ID : 6914 OPERATION : gather_table_stats START_TIME : 25-APR-18 11.57.35.764007 PM -07:00 END_TIME : 26-APR-18 12.05.11.086928 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 870 ------------------------- ID : 6921 OPERATION : gather_database_stats (auto) START_TIME : 25-APR-18 10.00.06.197933 PM -07:00 END_TIME : 26-APR-18 02.00.01.621582 AM -07:00 STATUS : TIMED OUT JOB_NAME : ORA$AT_OS_OPT_SY_16648 SESSION_ID : 877 ------------------------- ID : 6913 OPERATION : gather_table_stats START_TIME : 25-APR-18 02.26.33.270421 PM -07:00 END_TIME : 25-APR-18 02.26.42.759697 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 1921 ------------------------- ID : 6912 OPERATION : gather_table_stats START_TIME : 25-APR-18 12.20.35.728909 PM -07:00 END_TIME : 25-APR-18 02.26.18.996076 PM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 864 ------------------------- ID : 6911 OPERATION : gather_table_stats START_TIME : 25-APR-18 10.13.45.127514 AM -07:00 END_TIME : 25-APR-18 10.13.46.570807 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 864 ------------------------- ID : 6910 OPERATION : gather_table_stats START_TIME : 25-APR-18 08.57.44.914619 AM -07:00 END_TIME : 25-APR-18 10.13.44.623245 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 864 ------------------------- ID : 6920 OPERATION : restore_table_stats START_TIME : 25-APR-18 02.00.00.220393 AM -07:00 END_TIME : 25-APR-18 02.00.00.490261 AM -07:00 STATUS : COMPLETED JOB_NAME : SESSION_ID : 208 ------------------------- PL/SQL procedure successfully completed. SQL>
Skip Goldengate Replicat Transaction
Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.15 17640173 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_131101.0605.2_FBO Linux, x64, 64bit (optimized), Oracle 11g on Nov 19 2013 03:18:45 Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
==================================================================================================== ORA-02292: integrity constraint (OWNER.MARY_JOE_FK) violated - child record found (status = 2292). DELETE FROM "OWNER"."T_JOE" WHERE "JOENUMMER" = :b0. ==================================================================================================== +++ SKIPTRANSACTION GGSCI> start replicat REP1 SKIPTRANSACTION +++ REVIEW PRM [gguser]$ grep -i discard rep1.prm --REPERROR (DEFAULT, DISCARD) REPERROR (-1, DISCARD) REPERROR (2291, DISCARD) DISCARDFILE ./discard/rep1.discard append, MEGABYTES 1024 DISCARDROLLOVER AT 00:01 [gguser]$ +++ REVIEW SKIPPING FROM DISCARD [gguser]$ grep -c "Skipping delete from OWNER.T_JOE" rep1.discard 15276 [gguser]$ grep -A2 "Skipping delete from OWNER.T_JOE" ./discard/rep1.discard|head Skipping delete from OWNER.T_JOE at seqno 4475 rba 87850906 * JOENUMMER = 1 -- Skipping delete from OWNER.T_JOE at seqno 4475 rba 87851339 * JOENUMMER = 2 -- Skipping delete from OWNER.T_JOE at seqno 4475 rba 87851735 * [gguser@viz-cp-dc1-p11 oracle]$ grep -A2 "Skipping delete from OWNER.T_JOE" ./discard/rep1.discard|tail * JOENUMMER = 50093291 -- Skipping delete from OWNER.T_JOE at seqno 4475 rba 94033367 * JOENUMMER = 50094681 -- Skipping delete from OWNER.T_JOE at seqno 4475 rba 94033767 * JOENUMMER = 50094741 +++ REVIEW RBA FROM DISCARD [gguser]$ grep rba rep1.discard|head -1 Aborting transaction on ./dirdat/nd beginning at seqno 4475 rba 87850906 [gguser]$ grep rba rep1.discard|tail -1 Skipping delete from OWNER.T_JOE at seqno 4475 rba 94033767 [gguser]$ +++ NOTICE MATCH WITH LOGDUMP Logdump 23 >scanforendtrans End of Transaction found at RBA 94033767 ==================================================================================================== GATHER DATA ====================================================================================================
GGATE@SQL> r
1 select count(*) from
2 (
3 (select JOENUMMER from OWNER.T_JOE minus select JOENUMMER from OWNER.T_JOE@dblink)
4 union all
5 (select JOENUMMER from OWNER.T_JOE@dblink minus select JOENUMMER from OWNER.T_JOE)
6 )
7*
COUNT(*)
———-
15273
GGATE@SQL>
+++ CREATE TEMPORARY TABLE
GGATE@SQL> create table T_JOE_DEL as select JOENUMMER from OWNER.T_JOE minus select JOENUMMER from OWNER.T_JOE@dblink;
+++ REVIEW DATA FROM TEMPORARY TABLE TO COMPARE WITH DISCARD
GGATE@SQL> r
1 select * from (
2 select JOENUMMER from T_JOE_DEL order by 1 asc
3* ) where rownum <11
JOE
————
1
2
3
21
23
24
25
26
27
28
10 rows selected.
GGATE@SQL>
GGATE@SQL> r
1 select * from (
2 select JOE from T_JOE_DEL order by 1 desc
3* ) where rownum <11
JOE
————
50094741
50094681
50093291
50093221
50093191
50093101
50092851
50092791
50092781
50092741
10 rows selected.
GGATE@SQL>
==================================================================================================== CORRECT DATA ==================================================================================================== GGATE@SQL> delete from OWNER.T_JOE where JOENUMMER in (select JOENUMMER from T_JOE_DEL); 15273 rows deleted. GGATE@SQL> commit; Commit complete. ==================================================================================================== VERIFY ROW COUNT ==================================================================================================== +++ USING COUNT MAY NOT BE THE BEST OPTION. GGATE@SQL> select count(*) from OWNER.T_JOE; COUNT(*) ---------- 9939 GGATE@SQL> select count(*) from OWNER.T_JOE@dblink; COUNT(*) ---------- 9939 GGATE@SQL> ==================================================================================================== REVIEW REPORT FILE ==================================================================================================== [gguser]$ grep SKIPTRANSACTION REP1*.rpt rep1.rpt:2018-04-17 12:15:15 INFO OGG-01370 User requested START SKIPTRANSACTION. The current transaction will be skipped. Transaction ID 22.30.1923599, position Seqno 4475, RBA 87850906. [gguser]$ grep -i skip ggserr.log 2018-04-17 12:15:14 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (gguser): start replicat rep1 SKIPTRANSACTION. 2018-04-17 12:15:14 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 10.232.135.44:33310 (START REPLICAT rep1 SKIPTRANSACTION). 2018-04-17 12:15:15 INFO OGG-01370 Oracle GoldenGate Delivery for Oracle, rep1.prm: User requested START SKIPTRANSACTION. The current transaction will be skipped. Transaction ID 22.30.1923599, position Seqno 4475, RBA 87850906. [gguser]$ ==================================================================================================== LOGDUMP TO FIND END OF TRANSACTONS ==================================================================================================== Logdump 15 >open ./dirdat/nd004475 Current LogTrail is ./dirdat/nd004475 Logdump 16 >detail on Logdump 17 >fileheader detail Logdump 18 >ghdr on Logdump 19 >detail data Logdump 20 >ggstoken detail Logdump 21 >pos 87850906 Reading forward from RBA 87850906 Logdump 22 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 310 (x0136) IO Time : 2018/04/17 10:47:16.475.512 IOType : 3 (x03) OrigNode : 255 (xff) TransInd : . (x00) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 167409 AuditPos : 779280 Continued : N (x00) RecCount : 1 (x01) 2018/04/17 10:47:16.475.512 Delete Len 310 RBA 87850906 Name: OWNER.T_JOE Before Image: Partition 4 G b GGS tokens: TokenID x52 'R' ORAROWID Info x00 Length 20 4141 4148 6b55 4141 5441 4141 6264 7141 4159 0001 | AAAHkUAATAAAbdqAAY.. TokenID x4c 'L' LOGCSN Info x00 Length 10 3732 3833 3730 3834 3135 | 7283708415 TokenID x36 '6' TRANID Info x00 Length 13 3232 2e33 302e 3139 3233 3539 39 | 22.30.1923599 Logdump 23 >scanforendtrans End of Transaction found at RBA 94033767 ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 331 (x014b) IO Time : 2018/04/17 10:47:16.429.234 IOType : 3 (x03) OrigNode : 255 (xff) TransInd : . (x02) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 167409 AuditPos : 13903264 Continued : N (x00) RecCount : 1 (x01) 2018/04/17 10:47:16.429.234 Delete Len 331 RBA 94033767 Name: OWNER.T_JOE Before Image: Partition 4 G e GGS tokens: TokenID x52 'R' ORAROWID Info x00 Length 20 4141 4148 6b55 4141 5741 4141 4e6c 6c41 4177 0001 | AAAHkUAAWAAANllAAw.. Logdump 24 >n ___________________________________________________________________ Hdr-Ind : E (x45) Partition : . (x04) UndoFlag : . (x00) BeforeAfter: B (x42) RecLength : 174 (x00ae) IO Time : 2018/04/17 10:47:24.429.491 IOType : 15 (x0f) OrigNode : 255 (xff) TransInd : . (x00) FormatType : R (x52) SyskeyLen : 0 (x00) Incomplete : . (x00) AuditRBA : 167409 AuditPos : 13947088 Continued : N (x00) RecCount : 1 (x01) 2018/04/17 10:47:24.429.491 FieldComp Len 174 RBA 94034190 Name: OWNER.NEW_DATA Before Image: Partition 4 G b GGS tokens: TokenID x52 'R' ORAROWID Info x00 Length 20 4141 4148 6a59 4141 5441 4142 794b 4541 412f 0001 | AAAHjYAATAAByKEAA/.. TokenID x4c 'L' LOGCSN Info x00 Length 10 3732 3833 3730 3834 3538 | 7283708458 TokenID x36 '6' TRANID Info x00 Length 13 3132 2e31 362e 3330 3031 3139 37 | 12.16.3001197 Logdump 25 >open ./dirdat/nd004475 Current LogTrail is ./dirdat/nd004475 Logdump 26 >count LogTrail ./dirdat/nd004475 has 92822 records Total Data Bytes 92730182 Avg Bytes/Record 999 Delete 20937 Insert 5405 FieldComp 724 LargeObject 65755 Others 1 Before Images 21163 After Images 71658 Average of 1589 Transactions Bytes/Trans ..... 61161 Records/Trans ... 58 Files/Trans ..... 5 Logdump 27 >detail on Logdump 28 >filter inc filename OWNER.T_JOE Logdump 29 >count Scanned 10000 records, RBA 12734577, 2018/04/17 07:25:42.524.558 Scanned 20000 records, RBA 25670230, 2018/04/17 08:00:11.480.213 Scanned 30000 records, RBA 38698934, 2018/04/17 08:30:24.488.669 Scanned 40000 records, RBA 51436567, 2018/04/17 08:59:11.452.549 Scanned 50000 records, RBA 63868041, 2018/04/17 09:43:10.477.605 Scanned 60000 records, RBA 76010927, 2018/04/17 10:14:59.472.122 Scanned 70000 records, RBA 94264594, 2018/04/17 10:47:31.447.436 LogTrail ./dirdat/nd004475 has 15296 records Total Data Bytes 4757365 Avg Bytes/Record 311 Delete 15296 Before Images 15296 Filtering matched 15296 records suppressed 77526 records Average of 2 Transactions Bytes/Trans ..... 2745786 Records/Trans ... 7648 Files/Trans ..... 110 OWNER.T_JOE Partition 4 Total Data Bytes 4757365 Avg Bytes/Record 311 Delete 15296 Before Images 15296 Logdump 30 >
Framework To Run SQL For All Active DB Instances
Requirement is to configure hugepages for multiple RAC database instances.
pmon processes
grid 12692 1 0 09:39 ? 00:00:00 asm_pmon_+ASM1 grid 13296 1 0 09:39 ? 00:00:00 mdb_pmon_-MGMTDB oracle 13849 1 0 09:40 ? 00:00:00 ora_pmon_DEV1 oracle 13851 1 0 09:40 ? 00:00:00 ora_pmon_QA1 oracle 13854 1 0 09:40 ? 00:00:00 ora_pmon_PERF1 oracle 13855 1 0 09:40 ? 00:00:00 ora_pmon_TEST1 oracle 14998 1 0 09:40 ? 00:00:00 ora_pmon_INT1
Create parameter.sh which will run parameter.sql.
You might be thinking, WTH is this person thinking!
I wanted SQL script to be reusable.
Run parameter.sql
oracle@racnode-dc1-1:hawk1:/home/oracle
$ sqlplus / as sysdba @ parameter.sql
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 14 13:25:56 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
NAME CDB
------------------------------ ---
HAWK NO
NAME DISPLAY_VALUE INST_ID CON_ID DEFAULT_VALUE ISDEFAULT
------------------------------ ------------- ------- ---------- ------------- ---------
cluster_database TRUE 1 0 FALSE FALSE
TRUE 2 0 FALSE FALSE
cluster_database_instances 2 1 0 4294967295 TRUE
2 2 0 4294967295 TRUE
db_file_name_convert 1 0 NULL TRUE
2 0 NULL TRUE
db_name hawk 1 0 NULL FALSE
hawk 2 0 NULL FALSE
db_unique_name hawk 1 0 NONE TRUE
hawk 2 0 NONE TRUE
instance_groups 1 0 NULL TRUE
2 0 NULL TRUE
instance_name hawk1 1 0 NULL TRUE
hawk2 2 0 NULL TRUE
instance_number 1 1 0 0 FALSE
2 2 0 0 FALSE
instance_type RDBMS 1 0 NONE TRUE
RDBMS 2 0 NONE TRUE
memory_max_target 0 1 0 0 TRUE
0 2 0 0 TRUE
memory_target 0 1 0 0 TRUE
0 2 0 0 TRUE
pdb_file_name_convert 1 0 NULL TRUE
2 0 NULL TRUE
pga_aggregate_limit 2G 1 0 1 TRUE
2G 2 0 1 TRUE
pga_aggregate_target 256M 1 0 0 FALSE
256M 2 0 0 FALSE
sga_max_size 768M 1 0 1000 TRUE
768M 2 0 1000 TRUE
sga_target 768M 1 0 0 FALSE
768M 2 0 0 FALSE
use_large_pages TRUE 1 0 NULL FALSE
TRUE 2 0 NULL FALSE
34 rows selected.
13:25:56 SYS @ hawk1:>
Run parameter.sh
oracle@racnode-dc1-1:hawk1:/u01/app/oracle/12.1.0.2/db1
$ ~/parameter.sh
******** Current ora_pmon:
----------------------------------------
ora_pmon_hawk1
----------------------------------------
******** SQL Script: /home/oracle/parameter.sql
The Oracle base remains unchanged with value /u01/app/oracle
Oracle Instance alive for sid "hawk1"
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 14 13:26:55 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
13:26:55 SYS @ hawk1:>13:26:55 SYS @ hawk1:>13:26:55 SYS @ hawk1:>
NAME CDB
------------------------------ ---
HAWK NO
NAME DISPLAY_VALUE INST_ID CON_ID DEFAULT_VALUE ISDEFAULT
------------------------------ ------------- ------- ---------- ------------- ---------
cluster_database TRUE 1 0 FALSE FALSE
TRUE 2 0 FALSE FALSE
cluster_database_instances 2 1 0 4294967295 TRUE
2 2 0 4294967295 TRUE
db_file_name_convert 1 0 NULL TRUE
2 0 NULL TRUE
db_name hawk 1 0 NULL FALSE
hawk 2 0 NULL FALSE
db_unique_name hawk 1 0 NONE TRUE
hawk 2 0 NONE TRUE
instance_groups 1 0 NULL TRUE
2 0 NULL TRUE
instance_name hawk1 1 0 NULL TRUE
hawk2 2 0 NULL TRUE
instance_number 1 1 0 0 FALSE
2 2 0 0 FALSE
instance_type RDBMS 1 0 NONE TRUE
RDBMS 2 0 NONE TRUE
memory_max_target 0 1 0 0 TRUE
0 2 0 0 TRUE
memory_target 0 1 0 0 TRUE
0 2 0 0 TRUE
pdb_file_name_convert 1 0 NULL TRUE
2 0 NULL TRUE
pga_aggregate_limit 2G 1 0 1 TRUE
2G 2 0 1 TRUE
pga_aggregate_target 256M 1 0 0 FALSE
256M 2 0 0 FALSE
sga_max_size 768M 1 0 1000 TRUE
768M 2 0 1000 TRUE
sga_target 768M 1 0 0 FALSE
768M 2 0 0 FALSE
use_large_pages TRUE 1 0 NULL FALSE
TRUE 2 0 NULL FALSE
34 rows selected.
13:26:55 SYS @ hawk1:>Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@racnode-dc1-1:hawk1:/u01/app/oracle/12.1.0.2/db1
$
Requirements are .sh and .sql must have the same name and reside in the same location.
.sh can be called from any location.
parameter.sh
#!/bin/sh
# --------------------------------------------------------------------------------
# parameter.sh
# MDinh April 12, 2018
#
# Shell script will run SQL script having the same base name
# for all active database instances.
# --------------------------------------------------------------------------------
DN=`dirname $0`
BN=`basename $0`
SQL_SCRIPT_DIR=$DN
SQL=`echo $BN|cut -d'.' -f1`.sql
echo
echo "******** Current ora_pmon:"
echo "----------------------------------------"
ps -eo cmd|grep ora_pmon|grep -v grep
echo "----------------------------------------"
echo
echo "******** SQL Script: "$SQL_SCRIPT_DIR/$SQL
echo
for x in `ps -eo cmd|grep ora_pmon|grep -v grep|awk -F "_" '{print $NF}'`
do
ORAENV_ASK=NO
set -a
ORACLE_SID=$x
. oraenv
set +a
sysresv|tail -1
sqlplus -L "/ as sysdba" << EOF
whenever sqlerror exit sql.sqlcode
whenever oserror exit 1
start $SQL_SCRIPT_DIR/$SQL
exit
EOF
if [ "$?" != "0" ]; then
echo "$ORACLE_SID ERROR: Running $SQL_SCRIPT_DIR/$SQL"
exit 1
fi
done
exit
parameter.sql
col name for a30
col value for a30
col default_value for a13
col display_value for a13
col inst_id for 99
break on name
set lines 200 pages 1000 trimsp on tab off
select name,CDB from v$database
;
select name,display_value,inst_id,con_id,default_value,isdefault
from gv$parameter
where regexp_like (name,'^sga|^pga|^memory|^cluster.*database|^instance|use_large_pages|db.*name','i')
order by name,value,inst_id
;
Frame work for shell script is the same. Just make a copy and update any comments.
oracle@racnode-dc1-1:hawk1:/home/oracle
$ ll
total 36
-rwxr-xr-x 1 oracle oinstall 19 Feb 10 20:44 db.env
-rwxr-xr-x 1 oracle oinstall 49 Feb 10 20:45 gi.env
-rwxr-xr-x 1 oracle oinstall 1020 Apr 14 13:24 parameter.sh
-rw-r--r-- 1 oracle oinstall 414 Apr 14 13:24 parameter.sql
-rwxr-xr-x 1 oracle oinstall 1038 Apr 14 13:23 set_db_use_large_pages_only.sh
-rw-r--r-- 1 oracle oinstall 430 Apr 12 17:50 set_db_use_large_pages_only.sql
-rwxr-xr-x 1 oracle oinstall 1038 Apr 14 13:23 set_db_use_large_pages_true.sh
-rw-r--r-- 1 oracle oinstall 430 Apr 12 17:53 set_db_use_large_pages_true.sql
-rw-r--r-- 1 oracle oinstall 1909 Jan 29 02:39 wc.sql
oracle@racnode-dc1-1:hawk1:/home/oracle
$ diff parameter.sh set_db_use_large_pages_true.sh
3c3
# set_db_use_large_pages_true.sh
oracle@racnode-dc1-1:hawk1:/home/oracle
$ ./set_db_use_large_pages_true.sh
******** Current ora_pmon:
----------------------------------------
ora_pmon_hawk1
----------------------------------------
******** SQL Script: ./set_db_use_large_pages_true.sql
The Oracle base remains unchanged with value /u01/app/oracle
Oracle Instance alive for sid "hawk1"
SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 14 13:48:17 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
13:48:17 SYS @ hawk1:>13:48:17 SYS @ hawk1:>13:48:17 SYS @ hawk1:>
NAME CDB
------------------------------ ---
HAWK NO
NAME DISPLAY_VALUE INST_ID CON_ID DEFAULT_VALUE ISDEFAULT
------------------------------ ------------- ------- ---------- ------------- ---------
use_large_pages TRUE 1 0 NULL FALSE
TRUE 2 0 NULL FALSE
13:48:17 SYS @ hawk1:>alter system set USE_LARGE_PAGES=TRUE scope=spfile sid='*'
13:48:17 2 ;
System altered.
13:48:17 SYS @ hawk1:>Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
oracle@racnode-dc1-1:hawk1:/home/oracle
$