Thinking Out Loud

July 17, 2021

RMAN Backup Tuning – MAXOPENFILES

Filed under: 19c,RMAN — mdinh @ 5:03 pm

Currently, database backup is performed using RMAN from shell script.

The shell script has has 2,347 lines. Does anyone KISS anymore?

Anyhow, I recalled very early in my career when someone coined the phrased, “Doing It The Dinh’s Way”.

Basically, it’s not effective nor efficient to debug and troubleshoot shell script.

Client provided the following info and off I went.

Database backup changed from filesperset 4 to filesperset 1.

Rollback change and still facing performance issues.

Level 1 increment backup with filesperset = 4 still running > 24 hours – killed.

Can you try a few things not using the rman script in crontab, but using your own rman command?

Request was to disable block change tracking – DONE.

Database size is 272G with the following datafiles.

SQL> select file_id, file_name, round(bytes/1024/1024/1024) gb from dba_data_files order by 1 asc;

 FILE_ID FILE_NAME                                                          GB
-------- ------------------------------------------------------------ --------
       1 +DATA_XXXDWP01/XXXdwp01/datafile/system.256.964878417               1
       2 +DATA_XXXDWP01/XXXdwp01/datafile/sysaux.257.964878419               4
       3 +DATA_XXXDWP01/XXXdwp01/datafile/undotbs1.258.964878421            21
       4 +DATA_XXXDWP01/XXXdwp01/datafile/gg_tbs.261.964878453               5
       5 +DATA_XXXDWP01/XXXdwp01/datafile/undotbs2.262.964878457            19
       6 +DATA_XXXDWP01/XXXdwp01/datafile/users.264.965655143               31
       7 +DATA_XXXDWP01/XXXdwp01/datafile/users.267.1020339295              32
       8 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.268.1027263011           0
       9 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.269.1027263013           0
      10 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.270.1027263013           0
      11 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.271.1027263013           0
      12 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.272.1027263015           0
      13 +DATA_XXXDWP01/XXXdwp01/datafile/users.274.1046508647              32
      14 +DATA_XXXDWP01/XXXdwp01/datafile/users.275.1056870315              32
      15 +DATA_XXXDWP01/XXXdwp01/datafile/users.276.1066387693              32
      16 +DATA_XXXDWP01/XXXdwp01/datafile/users.277.1066387695              32
      17 +DATA_XXXDWP01/XXXdwp01/datafile/users.278.1066387697              32

17 rows selected.

SQL>

Backup completed in 3.25 hours using MAXPIECESIZE 2G MAXOPENFILES 1.

Believe further improvements by increasing MAXPIECESIZE to 4G; however, client wanted to leave as is.

Backupset size is 24G.

I believe performance may be related to Pure Storage Deduplication; however, there’s no more time nor different destination to test with.

MAXOPENFILES ≤ 4
Each buffer = 1MB, total buffer size for channel is up to 16MB

MAXOPENFILES = 1 => 16 buffers/file, 1 MB/buffer = 16 MB/file
Optimal for ASM or striped system

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jul 16 11:08:18 2021 Version 19.8.0.0.0

RMAN> connect target;
2> set echo on
3> spool log to backup.log
4> show all;
5> run {
6> allocate channel c1 device type disk format '/backup/XXXDWP01/%d_%I_%T_%U_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
7> allocate channel c2 device type disk format '/backup/XXXDWP01/%d_%I_%T_%U_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
8> allocate channel c3 device type disk format '/backup/XXXDWP01/%d_%I_%T_%U_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
9> allocate channel c4 device type disk format '/backup/XXXDWP01/%d_%I_%T_%U_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
10> backup incremental level 1 filesperset 1 database;
11> }
12> exit

June 25, 2021

The Horror Of Restore Validate For SBT_TAPE Backups

Filed under: 12.2,RMAN — mdinh @ 10:20 pm

For the database environment, there are database backups to disk and tape.

I wanted to validate tape backup by using validate preview summary device type=SBT_TAPE.

I hit a brick wall as the restore was reading backups from both DISK and SBT_TAPE.

Here is the command used:

restore database until time "TRUNC(sysdate)" validate preview summary device type=SBT_TAPE;

Oracle support came through with the following info:

Reading from Disk because of the preview command.

The command is now replaced with:

restore database   until time "TRUNC(sysdate)" validate device type=SBT_TAPE;
restore archivelog until time "TRUNC(sysdate)" validate device type=SBT_TAPE;

Another option is to use RETENTION POLICY.

RMAN> show RETENTION POLICY;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

RMAN> restore archivelog from time='SYSDATE-7' validate device type=SBT_TAPE;

Verify missing archivelog backup.

--- list backup of archivelog sequence 89961 thread 2;

RMAN-06025: no backup of archived log for thread 2 with sequence 3712 and starting SCN of 11284417423066 found to restore

How to Check/Validate that RMAN Backups Are Good (Doc ID 466221.1)

--- Report of backups that are used to do the restore and recover:
restore database preview;

--- Check the backup pieces are good:
restore database validate;

Works beautifully and learned something new today.

June 16, 2021

How To Determine Host Is On OCI

Filed under: OCI — mdinh @ 7:35 pm

Learned something new today.

https://docs.oracle.com/en-us/iaas/Content/Compute/References/oci-metadata.htm

Host is running OEM 13.4.0.0.0

[oracle@oemhost ~]$ oci-metadata
Instance details:
  Display Name: oemhost
  Region: phx - us-phoenix-1 (Phoenix, AZ, USA)
  Canonical Region Name: us-phoenix-1
  Availability Domain: jjZD:PHX-AD-2
  Fault domain: FAULT-DOMAIN-1
  OCID: ocid1.instance.oc1.phx.X
  Compartment OCID: ocid1.compartment.oc1..X
  Instance shape: VM.Standard2.16
  Image ID: ocid1.image.oc1.phx.X
  Created at: 1592424850729
  state: Running
  agentConfig:
    managementDisabled: False
    monitoringDisabled: False
  ociAdName: phx-ad-2
  hostname: oemhost
  regionInfo:
    regionKey: PHX
    realmDomainComponent: oraclecloud.com
    regionIdentifier: us-phoenix-1
    realmKey: oc1
  Instance Metadata:
    ssh_authorized_keys: ssh-rsa *
Networking details:
  VNIC OCID: ocid1.vnic.oc1.phx.X
  VLAN Tag: 293
  Private IP address: 10.157.38.66
  MAC address: 00:00:17:01:47:77
  Subnet CIDR block: 10.157.38.64/26
  Virtual router IP address: 10.157.38.65
[oracle@oemhost ~]$

June 8, 2021

Create Delete RAC DB Using dbca silent

Filed under: 19c,dbca — mdinh @ 10:18 pm

Tested version.

[oracle@ol7-19-lax1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
31305087;OCW RELEASE UPDATE 19.8.0.0.0 (31305087)
31281355;Database Release Update : 19.8.0.0.200714 (31281355)

OPatch succeeded.
[oracle@ol7-19-lax1 ~]$

Create database using dbca silent.

--- Set environment variables to be used by dbca.
export ORACLE_UNQNAME=owl
export PDB_NAME=mice
export NODE1=ol7-19-lax1
export NODE2=ol7-19-lax2
export SYS_PASSWORD=Oracle_4U
export PDB_PASSWORD=Oracle_4U

Note: -gdbName global_database_name (-gdbname oradb.example.com)

dbca -silent -createDatabase \
  -templateName General_Purpose.dbc \
  -gdbname ${ORACLE_UNQNAME} -responseFile NO_VALUE \
  -characterSet AL32UTF8 \
  -sysPassword ${SYS_PASSWORD} \
  -systemPassword ${SYS_PASSWORD} \
  -createAsContainerDatabase true \
  -numberOfPDBs 1 \
  -pdbName ${PDB_NAME} \
  -pdbAdminPassword ${PDB_PASSWORD} \
  -databaseType MULTIPURPOSE \
  -automaticMemoryManagement false \
  -totalMemory 1024 \
  -redoLogFileSize 50 \
  -emConfiguration NONE \
  -ignorePreReqs \
  -nodelist ${NODE1},${NODE2} \
  -storageType ASM \
  -diskGroupName +DATA \
  -recoveryGroupName +RECO \
  -useOMF true \
  -asmsnmpPassword ${SYS_PASSWORD}

[oracle@ol7-19-lax1 ~]$ dbca -silent -createDatabase \
>   -templateName General_Purpose.dbc \
>   -gdbname ${ORACLE_UNQNAME} -responseFile NO_VALUE \
>   -characterSet AL32UTF8 \
>   -sysPassword ${SYS_PASSWORD} \
>   -systemPassword ${SYS_PASSWORD} \
>   -createAsContainerDatabase true \
>   -numberOfPDBs 1 \
>   -pdbName ${PDB_NAME} \
>   -pdbAdminPassword ${PDB_PASSWORD} \
>   -databaseType MULTIPURPOSE \
>   -automaticMemoryManagement false \
>   -totalMemory 1024 \
>   -redoLogFileSize 50 \
>   -emConfiguration NONE \
>   -ignorePreReqs \
>   -nodelist ${NODE1},${NODE2} \
>   -storageType ASM \
>   -diskGroupName +DATA \
>   -recoveryGroupName +RECO \
>   -useOMF true \
>   -asmsnmpPassword ${SYS_PASSWORD}
Prepare for db operation
7% complete
Copying database files
27% complete
Creating and starting Oracle instance
28% complete
31% complete
35% complete
37% complete
40% complete
Creating cluster database views
41% complete
53% complete
Completing Database Creation
57% complete
59% complete
60% complete
Creating Pluggable Databases
64% complete
80% complete
Executing Post Configuration Actions
100% complete
Database creation complete. For details check the logfiles at:
 /u01/app/oracle/cfgtoollogs/dbca/owl.
Database Information:
Global Database Name:owl
System Identifier(SID) Prefix:owl
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/owl/owl.log" for further details.
[oracle@ol7-19-lax1 ~]$

Log files are located at ORACLE_BASE/cfgtoollogs/dbca/${ORACLE_UNQNAME}

[oracle@ol7-19-lax1 ~]$ cd $ORACLE_BASE/cfgtoollogs/dbca/${ORACLE_UNQNAME}
[oracle@ol7-19-lax1 owl]$ pwd
/u01/app/oracle/cfgtoollogs/dbca/owl

[oracle@ol7-19-lax1 owl]$ ls -l
total 23576
-rw-r-----. 1 oracle oinstall    10422 Jun  8 21:20 catclust0.log
-rw-------. 1 oracle oinstall   201621 Jun  8 21:20 catclust_catcon_31776.lst
-rw-r-----. 1 oracle oinstall     2450 Jun  8 21:14 cloneDBCreation.log
-rw-r-----. 1 oracle oinstall      380 Jun  8 20:57 CloneRmanRestore.log
-rw-r-----. 1 oracle oinstall    44272 Jun  8 21:20 CreateClustDBViews.log
-rw-r-----. 1 oracle oinstall     1711 Jun  8 21:49 DBDetails.log
-rw-r-----. 1 oracle oinstall     9948 Jun  8 21:19 execemx0.log
-rw-------. 1 oracle oinstall   200759 Jun  8 21:19 execemx_catcon_31544.lst
-rw-r-----. 1 oracle oinstall      910 Jun  8 21:20 lockAccount.log
-rw-r-----. 1 oracle oinstall     9560 Jun  8 21:18 ordlib0.log
-rw-------. 1 oracle oinstall   200561 Jun  8 21:18 ordlib_catcon_31269.lst
-rw-r-----. 1 oracle oinstall      796 Jun  8 21:51 owl0.log
-rw-r-----. 1 oracle oinstall      952 Jun  8 21:34 owl.log
-rw-r-----. 1 oracle oinstall        0 Jun  8 21:33 PDBCreation.log
-rw-r-----. 1 oracle oinstall       28 Jun  8 21:34 plugDatabase1R.log
-rw-r-----. 1 oracle oinstall     4105 Jun  8 21:18 plugDatabase.log
-rw-r-----. 1 oracle oinstall    46082 Jun  8 21:33 postDBCreation.log
-rw-r-----. 1 oracle oinstall       24 Jun  8 21:34 postPDBCreation.log
-rw-r-----. 1 oracle oinstall    88296 Jun  8 21:19 postScripts.log
-rw-r-----. 1 oracle oinstall        0 Jun  8 21:50 rmanUtil
-rw-r-----. 1 oracle oinstall     1479 Jun  8 21:49 ShutdownInst.log
-rw-r-----. 1 oracle oinstall 18726912 Jun  8 20:57 tempControl.ctl
-rw-r-----. 1 oracle oinstall  2670547 Jun  8 21:34 trace.log_2021-06-08_08-55-55PM
-rw-r-----. 1 oracle oinstall  1649779 Jun  8 21:51 trace.log_2021-06-08_09-49-34PM
-rw-r-----. 1 oracle oinstall    15420 Jun  8 21:32 utlrp0.log
-rw-------. 1 oracle oinstall   200463 Jun  8 21:32 utlrp_catcon_3004.lst
[oracle@ol7-19-lax1 owl]$

Here is the created database.

[oracle@ol7-19-lax1 owl]$ srvctl config database -d owl
Database unique name: owl
Database name: owl
Oracle home: /u01/app/oracle/product/19.0.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/OWL/PARAMETERFILE/spfile.311.1074720717
Password file: +DATA/OWL/PASSWORD/pwdowl.298.1074718605
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECO,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: oper
Database instances: owl1,owl2
Configured nodes: ol7-19-lax1,ol7-19-lax2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed

[oracle@ol7-19-lax1 owl]$ srvctl status database -d owl
Instance owl1 is running on node ol7-19-lax1
Instance owl2 is running on node ol7-19-lax2

--- Newly created database was not added to oratab.
[oracle@ol7-19-lax1 owl]$ cat /etc/oratab
#Backup file is  /u01/app/oracle/product/19.0.0/dbhome_1/srvm/admin/oratab.bak.ol7-19-lax1.oracle line added by Agent
+ASM1:/u01/app/19.0.0/grid:N
hawk1:/u01/app/oracle/product/19.0.0/dbhome_1:N
[oracle@ol7-19-lax1 owl]$ 

Delete database using dbca silent.

dbca -silent -deleteDatabase -sourceDB ${ORACLE_UNQNAME} -sysDBAUserName sys -sysDBAPassword ${SYS_PASSWORD}

[oracle@ol7-19-lax1 ~]$ dbca -silent -deleteDatabase -sourceDB ${ORACLE_UNQNAME} -sysDBAUserName sys -
sysDBAPassword ${SYS_PASSWORD}

[WARNING] [DBT-19202] The Database Configuration Assistant will delete the Oracle instances and datafiles for your database. All information in the database will be destroyed.
Prepare for db operation
32% complete
Connecting to database
39% complete
42% complete
45% complete
48% complete
52% complete
55% complete
58% complete
65% complete
Updating network configuration files
68% complete
Deleting instances and datafiles
77% complete
87% complete
97% complete
100% complete
Database deletion completed.
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/owl/owl0.log" for further details.
[oracle@ol7-19-lax1 ~]$

June 5, 2021

Data Pump Compatible Version

Filed under: 12.2,18c,DataPump — mdinh @ 12:51 am

Import failed as shown below:

Import: Release 18.0.0.0.0 - Production on Fri Jun 4 13:07:19 2021
Version 18.6.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39358: Export dump file version 18.0.0.0 not compatible with target version 12.1.0.2.0

Here are the compatible settings for source and target.

Source is 18.6.0.0.0 with compatible=18.0.0.0
Target is 18.6.0.0.0 with compatible=12.1.0.2

Run export with version.

expdp version=12.1.0.2

That’s all folks.

June 2, 2021

emcli dg_verify_config create_srls

Filed under: Dataguard,emcli — mdinh @ 10:36 pm

Convention.

DB_NAME=ORACLE_SID=XXXXXXX

Validate Data Guard Configuration.

$ ./dgmgrl_validate_srl.sh
======================== XXXXXXX1 ========================
XXXXXXX_PHOENIX

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (XXXXXXX_SYDNEY)        (XXXXXXX_PHOENIX)
    1         4                       4                       Insufficient SRLs
    2         4                       4                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (XXXXXXX_PHOENIX)       (XXXXXXX_SYDNEY)
    1         4                       4                       Insufficient SRLs
    2         4                       4                       Insufficient SRLs

--------------------------------------------------

Using emcli to verify Data Guard Configuration and Create Missing SRLs.



[oracle@emhost bin]$ $OMS_HOME/bin/emcli version
Oracle Enterprise Manager 13c EM CLI Version 13.4.0.0.0

[oracle@emhost bin]$ $OMS_HOME/bin/emcli login -username=sysman
Enter password :
Login successful

[oracle@emhost bin]$ $OMS_HOME/bin/emcli sync
Synchronized successfully

--- Find target name and verify targets do not have domain name as there is BUG what will cause failure.

[oracle@emhost bin]$ cd $OMS_HOME/bin/

[oracle@emhost bin]$ ./emcli get_targets -targets=rac_database -format=name:csv | grep XXXXXXX
1,Up,rac_database,XXXXXXX_PHOENIX
1,Up,rac_database,XXXXXXX_SYDNEY

--- Verify Data Guard configuration and create missing SRLs.
[oracle@emhost bin]$ ./emcli dg_verify_config -primary_target_name="XXXXXXX_SYDNEY" -primary_target_type="rac_database" -create_srls
Parsing command line arguments...
Verify Data Guard Configuration procedure VERIFY_DG_CONFIG_20210526091058954 has been submitted for primary database XXXXXXX_SYDNEY.

--- Check progress for VERIFY_DG_CONFIG_20210526091058954 
[oracle@emhost bin]$ ./emcli get_instances | grep -B1 VERIFY_DG_CONFIG_20210526091058954
GUID                              Execution GUID                    Type  Name                                Status     Owner   Instance Status
C32FCFCACD2E2650E05317279D0A7830  C32FCFCACD312650E05317279D0A7830  HA    VERIFY_DG_CONFIG_20210526091058954  Running    SYSMAN  Running

[oracle@emhost bin]$ ./emcli get_instances | grep -B1 VERIFY_DG_CONFIG_20210526091058954
GUID                              Execution GUID                    Type  Name                                Status     Owner   Instance Status
C32FCFCACD2E2650E05317279D0A7830  C32FCFCACD312650E05317279D0A7830  HA    VERIFY_DG_CONFIG_20210526091058954  Succeeded  SYSMAN  Succeeded

--- View results using GUID = C32FCFCACD2E2650E05317279D0A7830  
[oracle@emhost bin]$ ./emcli get_instance_status -instance=C32FCFCACD2E2650E05317279D0A7830 -xml -details -showJobOutput | grep -B50 "Data Guard configuration verification complete."
Processing is 0% complete.
Processing is 0% complete.
Processing is 0% complete.
Initializing
Connected to instance exasydad1x8anz-1nn7a1:XXXXXXX1
Starting alert log monitor...
Updating Data Guard link on database homepage...
Skipping verification of fast-start failover static services check.

Data Protection Settings:
  Protection mode : Maximum Performance
  Redo Transport Mode settings:
    XXXXXXX_PHOENIX: ASYNC
    XXXXXXX_SYDNEY: ASYNC

  Checking standby redo log files.....Done
   (Standby redo log files needed : 4)

Checking Data Guard status
  XXXXXXX_PHOENIX : Normal
  Write operation in progress
  XXXXXXX_SYDNEY : Normal

Checking inconsistent properties

Checking agent status
  XXXXXXX_PHOENIX
    exaphxad3x8na-kvnxa1.dbexaphoad3.phx.oraclevcn.com ... OK
    exaphxad3x8na-kvnxa2.dbexaphoad3.phx.oraclevcn.com ... OK

  XXXXXXX_SYDNEY
    exasydad1x8anz-1nn7a1.excsad1client.syd.oraclevcn.com ... OK
    exasydad1x8anz-1nn7a2.excsad1client.syd.oraclevcn.com ... OK


Checking applied log on XXXXXXX_PHOENIX...OK


Processing completed.

Standby Redo Log Files
Standby redo log files are recommended for all transport modes. 
They are required for certain features such as real-time apply and elevated protection modes.
Database Host/Cluster Size (MB) Log File Location Thread
XXXXXXX_PHOENIX  Unknown  4000.0  Oracle-managed file  2
XXXXXXX_PHOENIX  Unknown  4000.0  Oracle-managed file  1
XXXXXXX_SYDNEY   Unknown  4000.0  Oracle-managed file  2
XXXXXXX_SYDNEY   Unknown  4000.0  Oracle-managed file  1

--- Insufficient SRLs were create for both primary and standby.
Successfully created the required standby redo log files for all databases.
Successfully resolved the issues detected during verification.
Data Guard configuration verification complete.
$

Example of failure due to BUG or inconsistency for target name.

WARNING: 

DB_NAME=ORACLE_SID=YYYYYYY


$ $OMS_HOME/bin/emcli get_targets -targets=rac_database -format=name:csv | grep YYYYYYY
1,Up,rac_database,YYYYYYY.excsad2client.oraclevcn.com
1,Up,rac_database,YYYYYYY_ASHBURN


The below message in verify configuration due to Bug 32252460 in 13.4 and fixed in 14.1 GC

WARNING: Broker name (YYYYYYY) and target name (YYYYYYY.excsad2client.oraclevcn.com) do not match.
Checking standby redo log files.....not checked due to broker name mismatch. 

Need to match target name and broker name as a workaround to have emcli create srls.

June 1, 2021

Query OEM mgmt$(target|target_properties)

Filed under: emcli,Grid Control — mdinh @ 9:41 pm

TARGET_TYPE oracle_database can be a database or an instance; however, rac_database is a database

select t.TARGET_TYPE, t.TYPE_QUALIFIER3, count(*)
from mgmt$target_properties p, mgmt$target t
where p.TARGET_GUID=t.TARGET_GUID
and p.PROPERTY_NAME='DataGuardStatus'
group by t.TARGET_TYPE, t.TYPE_QUALIFIER3
order by 2,1 desc
;

There is 1 single instance database, 105 RAC databases, 210 RAC instances.

TARGET_TYPE                    TYPE_QUALIFIER3        COUNT(*)
------------------------------ -------------------- ----------
rac_database                   DB                          105
oracle_database                DB                            1
oracle_database                RACINST                     210

DataGuardStatus does not mean Data Guard exist unless PROPERTY_VALUE is populated

select 
t.TARGET_TYPE, t.TYPE_QUALIFIER3, 
NVL(REGEXP_REPLACE(p.property_value,'[[:space:]]'),'NO DataGuard') PROPERTY_VALUE, count(*)
from mgmt$target_properties p, mgmt$target t
where p.TARGET_GUID=t.TARGET_GUID
and p.PROPERTY_NAME='DataGuardStatus'
group by t.TARGET_TYPE, t.TYPE_QUALIFIER3, PROPERTY_VALUE
order by 2,1 desc
;

There are 48 RAC Primary and 49 RAC Physical Standby because 1 RAC database has 2 Physical Standby.

TARGET_TYPE                    TYPE_QUALIFIER3      PROPERTY_VALUE                   COUNT(*)
------------------------------ -------------------- ------------------------------ ----------
rac_database                   DB                   NO DataGuard                            8
rac_database                   DB                   PhysicalStandby                        49
rac_database                   DB                   Primary                                48
oracle_database                DB                   NO DataGuard                            1
oracle_database                RACINST              NO DataGuard                           16
oracle_database                RACINST              PhysicalStandby                        98
oracle_database                RACINST              Primary                                96

Here’s how to determine the values for TYPE_QUALIFIER1-4

SQL> select distinct NVL(REGEXP_REPLACE(TYPE_QUALIFIER1,'[[:space:]]'),NULL) TYPE_QUALIFIER from mgmt$target order by 1;

SQL> c/TYPE_QUALIFIER1/TYPE_QUALIFIER2
SQL> c/TYPE_QUALIFIER2/TYPE_QUALIFIER3
SQL> c/TYPE_QUALIFIER3/TYPE_QUALIFIER4

SQL to gather primary and standby targets.

-- db.sql
set echo off lines 300 pages 500 trimsp on tab off
col HOST_NAME       for a30
col TARGET_TYPE     for a20
col TYPE1           for a9
col TYPE3           for a9
col TYPE4           for a9
col PROPERTY_VALUE  for a23
col PROPERTY_NAME   for a17
col TARGET_NAME     for a60
BREAK ON HOST_NAME SKIP 1 ON PROPERTY_VALUE ON TARGET_TYPE ON PROPERTY_NAME ON TYPE3
select
  REGEXP_SUBSTR(t.HOST_NAME,'[^.]+',1,1) host_name,
--  REGEXP_SUBSTR(t.TARGET_NAME,'[^.]+',1,1) target_name, t.TARGET_TYPE,
  t.TARGET_NAME, t.TARGET_TYPE,
  NVL(REGEXP_REPLACE(property_value,'[[:space:]]'), 'Primary: NO DataGuard') PROPERTY_VALUE,
  p.PROPERTY_NAME,
  TYPE_QUALIFIER1 type1, TYPE_QUALIFIER3 type3,
  (CASE TYPE_QUALIFIER4 WHEN 'FullLLFile+CDB' THEN 'CDB' WHEN 'FullLLFile' THEN 'DB' ELSE NULL END) type4
from mgmt$target_properties p, mgmt$target t
where p.TARGET_GUID=t.TARGET_GUID
and   p.PROPERTY_NAME='DataGuardStatus' -- Find Data Guard
and   t.TYPE_QUALIFIER3='DB'            -- Find Database
order by PROPERTY_VALUE desc, t.TARGET_TYPE, t.HOST_NAME, type1 ASC, type4
;

May 22, 2021

emcli dg_verify_config

Filed under: Dataguard,dgmgrl,emcli — mdinh @ 1:43 am

I must love looking for trouble.

There are 47 RAC databases with Data Guard distributed among 7 clusters.

Being as lazy as I am, I did not want to connect to all the hosts to verify Data Guard configuration and create standby redo logs (SRL) for environments with Insufficient SRLs.

After some searching, I have found emcli dg_verify_config can be used; however, the documentation is not that great.

Currently working with Oracle Support and crossing my fingers.

$ $OMS_HOME/bin/emcli login -username=sysman
Enter password :

Login successful
$ $OMS_HOME/bin/emcli sync
Synchronized successfully

$ $OMS_HOME/bin/emcli dg_verify_config -primary_target_name="APEX18_XXXXXXX" -primary_target_type="rac_database" -verify_only
Parsing command line arguments...
Verify Data Guard Configuration procedure VERIFY_DG_CONFIG_20210520114056703 has been submitted for primary database APEX18_XXXXXXX.

$ $OMS_HOME/bin/emcli dg_verify_config -primary_target_name="APEX18_XXXXXXX" -primary_target_type="rac_database" -create_srls

The intention is to run emcli from OMS host to verify config and create srls where required.

However, there’s no documentation for how to retrieve results from verify_only at the OS level.

Seeing that I am struggling with Oracle support, Lead Database Consultant shared his implementation to verify Data Guard status before performing switchover.

Using the same concept, check for Insufficient SRLs from standby databases.

--- Prerequisite: Data Guard Broker is implemented.

--- Find Oracle Data Guard monitor process (DMON) and write to sids.txt file.
ps -ef|grep [d]mon|grep -v ASM|sort -k8 | awk -F "_" '{print $3}' > sids.txt

--- Create shell script dgmgrl_validate_srl.sh
arr=(`grep '^[A-Z].*' < sids.txt`)
for i in "${arr[@]}"
do
  echo ======================== $i ========================
  . oraenv <<< $i >/dev/null
  stby=`dgmgrl / "show configuration" | grep "Physical standby" | awk  '{print $1;}'`
  echo $stby
  dgmgrl / "validate database verbose '$stby'" | grep -B4 "Insufficient SRLs"
done


--- Run dgmgrl_validate_srl.sh
$ ./dgmgrl_validate_srl.sh
======================== ORACLE_SID ========================
XXXXXXXX_SPYBRACDR

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (XXXXXXXX_SPYBRAC)      (XXXXXXXX_SPYBRACDR)
    0         10                      0                       Insufficient SRLs
--

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (XXXXXXXX_SPYBRACDR)    (XXXXXXXX_SPYBRAC)
    0         10                      0                       Insufficient SRLs


Future Log File Groups Configuration - When current standby becomes primary.

Checking is different from fixing as 50%+ of RAC databases have issues with Insufficient SRLs.

Currently both processes (shell script and emcli) are deficient since only standby databases are checked.

When there is switchover or failover for primary databases, then check will have to be performed yet again.

At least, using emcli dg_verify_config -create_srls, does simplify the process since it can be execute from OMS host if it works.

May 14, 2021

Gather Your Session Info For Killing

Filed under: 11g,linux,oracle — mdinh @ 4:40 am

So there I was, running emremove.sql as part of pre-upgrade task; however, it was taking longer than expected.

Session was stuck at the output shown below and desperately CTRL-C did not work.

14:35:05 472  /
old  70:     IF (upper('&LOGGING') = 'VERBOSE')
new  70:     IF (upper('VERBOSE') = 'VERBOSE')

^C

^C^C

^C

I checked for blocking session and there were blocking locks from SYS which was really strange.

I made a gutsy call and kill SYS session from OS prompt based on timestamp.

~ $ ps -ef|grep sysdba
oracle    57147 231962  0 May12 pts/4    00:00:00 sqlplus   as sysdba
oracle   155919 139352  0 14:34 pts/1    00:00:00 sqlplus   as sysdba
oracle   244619 216760  0 15:25 pts/5    00:00:00 grep --color=auto sysdba

~ $ kill -9 155919

As it turns out, another DBA was logged in as sysdba causing havoc.

I was lucky to have killed the correct SYS session and will you be as lucky as I was?

Based on my near disaster, it would be better to create good practice of gathering your session info to be able to kill the correct session.

Here is current session info.

SQL> @my
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
  2  where a.addr = b.paddr
  3  and b.audsid = userenv('sessionid')
  4  and b.sid=userenv('sid')
  5  ;

       SID    SERIAL# PROCESSID                CLIENTPID
---------- ---------- ------------------------ ------------------------
         5        101 16428                    16427

SQL>

[oracle@ol7-112-dg1 ~]$ ps -ef|grep 16427
oracle   16427  8573  0 03:44 pts/0    00:00:00 sqlplus   as sysdba
oracle   16428 16427  0 03:44 ?        00:00:00 oraclehawk (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   16461 11677  0 03:45 pts/1    00:00:00 grep --color=auto 16427
[oracle@ol7-112-dg1 ~]$

Kill OS process using sqlplus PROCESSID – don’t know session is killed until DML is performed.

[oracle@ol7-112-dg1 ~]$ kill -9 16428

SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 16428
Session ID: 5 Serial number: 101


SQL>

Another test

--- Session Info
SQL> @my
SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
  2  where a.addr = b.paddr
  3  and b.audsid = userenv('sessionid')
  4  and b.sid=userenv('sid')
  5  ;

       SID    SERIAL# PROCESSID                CLIENTPID
---------- ---------- ------------------------ ------------------------
         5        103 16533                    16532

SQL>

--- From another session, check waits for above session
SQL> r
  1  select NVL(s.username,'(oracle)') AS username, s.sid, s.serial#,
  2  sw.event, sw.seconds_in_wait, sw.state
  3  from v$session_wait sw, v$session s
  4  where s.sid = sw.sid and s.sid=&sid
  5*
Enter value for sid: 5
old   4: where s.sid = sw.sid and s.sid=&sid
new   4: where s.sid = sw.sid and s.sid=5

USERNAME               SID    SERIAL# EVENT                          SECONDS_IN_WAIT STATE
--------------- ---------- ---------- ------------------------------ --------------- -------------------
SYS                      5        115 SQL*Net message from client                169 WAITING

SQL>

Kill OS process using sqlplus CLIENTPID – immediate feedback –

[oracle@ol7-112-dg1 ~]$ ps -ef|grep 16532
oracle   16532  8573  0 03:46 pts/0    00:00:00 sqlplus   as sysdba
oracle   16533 16532  0 03:46 ?        00:00:00 oraclehawk (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   16557 11677  0 03:47 pts/1    00:00:00 grep --color=auto 16532
[oracle@ol7-112-dg1 ~]$


[oracle@ol7-112-dg1 ~]$ kill -9 16532


SQL> Killed
[oracle@ol7-112-dg1 ~]$

Hopefully you will never have to kill your own session.

When you need kill your session, it’s better to have the correct information versus guessing.

May 1, 2021

Extract DB User Password

Filed under: 19c,oracle — mdinh @ 12:40 am

For some reason, I had a mental block in trying to extract password for database users.

Here are some options.

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

SQL>

-- From notes:
SQL> set echo off head off verify off feedb off pages 0 long 10000 longchunk 10000 trimspool on lines 2000 timing off term off
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'PRETTY',TRUE);

SQL> r
  1  select 'alter user '||username||' identified by values '||REGEXP_SUBSTR(DBMS_METADATA.get_ddl ('USER',USERNAME), '''[^'']+''')||';' ddl
  2  from dba_users where username='CTXSYS'
  3*
alter user CTXSYS identified by values 'S:29174843DD6989AA921A152BF37264659F042C2D8C216D97F6176AF13E4F;T:7B311EEA53E028F937CFABC4D6F6142E3027195E099798CD9E67910ABE6C621E9C23780121F208451B95AB558A4862F206A917C93B50D96E8F573FE7E1A4B2E98D77B9504BC2EBB457B63600127E34D';

SQL>


-- From colleague
SQL> select name, spare4 from sys.user$ where name='CTXSYS';
CTXSYS
S:29174843DD6989AA921A152BF37264659F042C2D8C216D97F6176AF13E4F;T:7B311EEA53E028F937CFABC4D6F6142E3027195E099798CD9E67910ABE6C621E9C23780121F208451B95AB558A4862F206A917C93B50D96E8F573FE7E1A34B2E98D77B9504BC2EBB457B63600127E34D

SQL>

-- From asktom
SQL> r
  1  with t as
  2  (select TO_CHAR(dbms_metadata.get_ddl('USER','CTXSYS')) ddl from dual )
  3  select replace(substr(ddl,1,instr(ddl,'DEFAULT')-1),'CREATE','ALTER')||';' sql
  4* from t

ALTER USER "CTXSYS" IDENTIFIED BY VALUES 'S:29174843DD6989AA921A152BF37264659F042C2D8C216D97F6176AF13E4F;T:7B311EEA53E028F937CFABC4D6F6142E3027195E099798CD9E67910ABE6C621E9C23780121F208451B95AB558A4862F206A917C93B50D96E8F573FE7E1A34B2E98D77B9504BC2EBB457B63600127E34D';

SQL>
Next Page »

Blog at WordPress.com.