Thinking Out Loud

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.

Advertisement

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
;

Blog at WordPress.com.