Thinking Out Loud

August 25, 2021

Find Tablespace Info Using RMAN report schema

Filed under: RMAN — mdinh @ 8:26 pm

A quick and dirty post. This also works for PDBs.

[oracle@ol7-112-dg1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Aug 25 12:40:16 2021
Version 19.3.0.0.0

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

connected to target database: HAWK (DBID=3331620895)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name HAWK

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1040     SYSTEM               YES     /u01/oradata/hawk/system01.dbf
2    890      SYSAUX               NO      /u01/oradata/hawk/sysaux01.dbf
3    1025     UNDOTBS1             YES     /u01/oradata/hawk/undotbs01.dbf
4    10       USERS                NO      /u01/oradata/hawk/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    37       TEMP                 32767       /u01/oradata/hawk/temp01.dbf

RMAN> exit


Recovery Manager complete.

[oracle@ol7-112-dg1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 25 12:40:44 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter tablespace users add datafile size 1m;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

[oracle@ol7-112-dg1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Aug 25 12:41:23 2021
Version 19.3.0.0.0

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

connected to target database: HAWK (DBID=3331620895)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name HAWK

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    1040     SYSTEM               YES     /u01/oradata/hawk/system01.dbf
2    890      SYSAUX               NO      /u01/oradata/hawk/sysaux01.dbf
3    1025     UNDOTBS1             YES     /u01/oradata/hawk/undotbs01.dbf
4    10       USERS                NO      /u01/oradata/hawk/users01.dbf
5    1        USERS                NO      /u01/oradata/HAWK/datafile/o1_mf_users_jldgmgxd_.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    37       TEMP                 32767       /u01/oradata/hawk/temp01.dbf

RMAN> exit


Recovery Manager complete.
[oracle@ol7-112-dg1 ~]$

August 20, 2021

AutoUpgrade 21.2.210721 Data Guard ORA-16532

Filed under: 19c,Dataguard,upgrade — mdinh @ 8:25 pm

Basically, I was somewhat purposely reckless as I wanted to see how much the new AutoUpgrade will do.

After Data Guard System Upgrade, Error reported ORA-16532 (Doc ID 2186456.1)

Environment is single instance and no Grid installed.

Learned something new :=)

Copy dg_broker_config_file and network files from old home to new home.

$ORACLE_HOME = /opt/oracle/product/19c/dbhome_1

cp -v /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/dr*.dat $ORACLE_HOME/dbs
cp -v /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/* $ORACLE_HOME/network/admin


[oracle@ol7-112-dg1 admin]$ dgmgrl /
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Fri Aug 20 19:40:23 2021
Version 19.3.0.0.0

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

Welcome to DGMGRL, type "help" for information.
Connected to "hawk"
Connected as SYSDG.
DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
      Error: ORA-16525: The Oracle Data Guard broker is not yet available.

Fast-Start Failover:  Disabled

Configuration Status:
ERROR   (status updated 11 seconds ago)

DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database
      Warning: ORA-16853: apply lag has exceeded specified threshold

Fast-Start Failover:  Disabled

Configuration Status:
WARNING   (status updated 39 seconds ago)

DGMGRL> show configuration

Configuration - my_dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk      - Primary database
    hawk_stby - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 55 seconds ago)

DGMGRL>

RMAN BACKUP RECOVERY AREA

Filed under: RMAN — mdinh @ 1:44 am

The current backup solution is Disk-To-Disk-To-Tape (D2D2T).

Here are the backups scheduled from cron.

$ crontab -l|grep backup
30 13 * * 0 /home/oracle/scripts/backup.ksh -s $ORACLE_SID -t level0
30 13 * * 1,2,3,4,5,6 /home/oracle/scripts/backup.ksh -s $ORACLE_SID -t level1
50 * * * * /home/oracle/scripts/backup.ksh -s $ORACLE_SID -t arch
30 15 * * 6 /home/oracle/scripts/backup_sbt.ksh -s $ORACLE_SID -t level0
10 15 * * 0,1,2,3,4,5 /home/oracle/scripts/backup_sbt.ksh -s $ORACLE_SID -t level1

For small databases, it’s not a big deal; however, when the database size is terabytes it might not be an optimal solution to punish the database twice for each backup type.

Backing Up from the Fast Recovery Area (FRA) to Oracle Database Backup Cloud Service

The solution selected was – BACKUP DEVICE TYPE sbt RECOVERY AREA and here is a snippet of the shell script.

Please don’t ask me why a function is created for simple RMAN backup. It’s a loosing battle.

backup_database_inc() {
        RECOVERY_WINDOW=`echo "show retention policy;"| $RMAN_BIN target / | awk '/CONFIGURE RETENTION POLICY TO/ {print $(NF-1);}'`
        RMAN_CMDSTRING="
        $RMAN_CONNECT_TARGET_CMD
show all;
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 8 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO COMPRESSED BACKUPSET;
set encryption off;
run {
    set command id to '$SCRIPT_NAME:LEVEL${BACKUP_LEVEL}:$BACKUP_TAG';
    crosscheck archivelog all;
    delete noprompt expired archivelog all;
    backup device type disk  tag '${BACKUP_TAG}'
    incremental level ${BACKUP_LEVEL}
    database
    plus archivelog not backed up 1 times;
    delete noprompt obsolete recovery window of 14 days device type disk;
}
set encryption on;
run {
    set command id to '$SCRIPT_NAME:ARCH:$BACKUP_TAG';
    backup device type sbt recovery area;
    delete noprompt archivelog all backed up 1 times to disk;
    delete noprompt backup device type sbt_tape completed before 'sysdate - 20';
}
exit;
"

Next, let’s review the backup from V$RMAN_BACKUP_JOB_DETAILS.

Notice INPUT_TYPE = RECVR AREA

$ sqlplus / as sysdba @ new_backup_review.sql 

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 19 19:28:30 2021
Version 19.11.0.0.0

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


Connected to:
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

SQL> select name, database_role from v$database
  2  ;

NAME      DATABASE_ROLE
--------- ----------------
XXXXXXX   PRIMARY

SQL> alter session set nls_date_format = 'YYYY-MON-DD';

Session altered.

SQL> select
  2  TRUNC(start_time) start_date,
  3  input_type,
  4  output_device_type output_type,
  5  status,
  6  round(sum(elapsed_seconds)/60) minutes,
  7  round(sum(input_bytes)/1024/1024) input_mb,
  8  round(sum(output_bytes)/1024/1024) output_mb
  9  from V$RMAN_BACKUP_JOB_DETAILS
 10  group by TRUNC(start_time), input_type, output_device_type, status
 11  order by start_date asc, input_type asc, 2
 12  ;

START_DATE  INPUT_TYPE    OUTPUT_TYPE       STATUS          MINUTES     INPUT_MB    OUTPUT_MB
----------- ------------- ----------------- ------------ ---------- ------------ ------------
2021-JUL-25 ARCHIVELOG    *                 COMPLETED             4        3,362          408
2021-JUL-26 ARCHIVELOG    *                 COMPLETED            13       11,214        1,375
2021-JUL-26 DB INCR       *                 COMPLETED             2        2,766          841
2021-JUL-26 DB INCR       SBT_TAPE          COMPLETED             1          316           30
2021-JUL-27 ARCHIVELOG    *                 COMPLETED            14       11,013        1,222
2021-JUL-27 DB INCR       *                 COMPLETED             2        3,375          953
2021-JUL-27 DB INCR       SBT_TAPE          COMPLETED             1          237           25
2021-JUL-28 ARCHIVELOG    *                 COMPLETED            15       10,827        1,118
2021-JUL-28 DB INCR       SBT_TAPE          COMPLETED             1          238           24
2021-JUL-28 DB INCR       *                 COMPLETED             2        3,011          874
2021-JUL-29 ARCHIVELOG    *                 COMPLETED            15       10,713        1,092
2021-JUL-29 DB INCR       *                 COMPLETED             2        2,901          841
2021-JUL-29 DB INCR       SBT_TAPE          COMPLETED             1          236           23
2021-JUL-30 ARCHIVELOG    *                 COMPLETED            15       10,601        1,011
2021-JUL-30 DB INCR       *                 COMPLETED             2        2,783          813
2021-JUL-30 DB INCR       SBT_TAPE          COMPLETED             1          227           21
2021-JUL-31 ARCHIVELOG    *                 COMPLETED            14       10,230          955
2021-JUL-31 DB INCR       SBT_TAPE          COMPLETED             3       26,311        3,828
2021-JUL-31 DB INCR       *                 COMPLETED             2        3,004          819
2021-AUG-01 ARCHIVELOG    *                 COMPLETED            14       10,937        1,197
2021-AUG-01 DB INCR       SBT_TAPE          COMPLETED             1          257           29
2021-AUG-01 DB INCR       *                 COMPLETED             6      105,712       16,038
2021-AUG-02 ARCHIVELOG    *                 COMPLETED            13       10,488          982
2021-AUG-02 DB INCR       *                 COMPLETED             2        2,257          632
2021-AUG-02 DB INCR       SBT_TAPE          COMPLETED             1          236           23
2021-AUG-03 ARCHIVELOG    *                 COMPLETED            14       10,675        1,074
2021-AUG-03 DB INCR       SBT_TAPE          COMPLETED             1          223           19
2021-AUG-03 DB INCR       *                 COMPLETED             2        2,750          796
2021-AUG-04 ARCHIVELOG    *                 COMPLETED            16       10,750        1,109
2021-AUG-04 DB INCR       *                 COMPLETED             2        2,741          775
2021-AUG-04 DB INCR       SBT_TAPE          COMPLETED             1          237           23
2021-AUG-05 ARCHIVELOG    *                 COMPLETED            17       10,493          980
2021-AUG-05 DB INCR       *                 COMPLETED             3        2,738          788
2021-AUG-05 DB INCR       SBT_TAPE          COMPLETED             1          222           19
2021-AUG-06 ARCHIVELOG    *                 COMPLETED            15       10,466          962
2021-AUG-06 DB INCR       SBT_TAPE          COMPLETED             1          222           19
2021-AUG-06 DB INCR       *                 COMPLETED             2        2,604          750
2021-AUG-07 ARCHIVELOG    *                 COMPLETED            15       10,915        1,195
2021-AUG-07 DB INCR       *                 COMPLETED             2        2,784          780
2021-AUG-07 DB INCR       SBT_TAPE          COMPLETED             1       27,377        4,201
2021-AUG-08 ARCHIVELOG    *                 COMPLETED            15       10,872        1,190
2021-AUG-08 DB INCR       SBT_TAPE          COMPLETED             1          252           27
2021-AUG-08 DB INCR       *                 COMPLETED             7      104,131       15,833
2021-AUG-09 ARCHIVELOG    *                 COMPLETED            13       10,472          967
2021-AUG-09 DB INCR       *                 COMPLETED             2        2,313          657
2021-AUG-09 DB INCR       SBT_TAPE          COMPLETED             1          236           22
2021-AUG-10 ARCHIVELOG    *                 COMPLETED            14       10,724        1,089
2021-AUG-10 DB INCR       *                 COMPLETED             2        2,755          786
2021-AUG-10 DB INCR       SBT_TAPE          COMPLETED             1          223           19
2021-AUG-11 ARCHIVELOG    *                 COMPLETED            16       10,753        1,094
2021-AUG-11 DB INCR       SBT_TAPE          COMPLETED             1          223           20
2021-AUG-11 DB INCR       *                 COMPLETED             2        2,813          806
2021-AUG-12 ARCHIVELOG    *                 COMPLETED            15       10,527          993
2021-AUG-12 DB INCR       *                 COMPLETED             2        2,755          794
2021-AUG-12 DB INCR       SBT_TAPE          COMPLETED             1          224           20
2021-AUG-13 ARCHIVELOG    *                 COMPLETED            15       10,541          997
2021-AUG-13 DB INCR       *                 COMPLETED             2        2,625          774
2021-AUG-13 DB INCR       SBT_TAPE          COMPLETED             1          236           24
2021-AUG-14 ARCHIVELOG    *                 COMPLETED            15       10,799        1,131
2021-AUG-14 DB INCR       SBT_TAPE          COMPLETED             1       25,945        3,837
2021-AUG-14 DB INCR       *                 COMPLETED             2        2,953          851
2021-AUG-15 ARCHIVELOG    *                 COMPLETED            14       10,832        1,151
2021-AUG-15 DB INCR       SBT_TAPE          COMPLETED             1          238           24
2021-AUG-15 DB INCR       *                 COMPLETED             6      104,284       15,951
2021-AUG-16 ARCHIVELOG    *                 COMPLETED            14       10,396          914
2021-AUG-16 DB INCR       *                 COMPLETED             2        2,362          683
2021-AUG-16 DB INCR       SBT_TAPE          COMPLETED             1          224           20
2021-AUG-17 ARCHIVELOG    *                 COMPLETED            19       10,678        1,059
2021-AUG-17 DB INCR       *                 COMPLETED             2        2,577          721
2021-AUG-17 DB INCR       SBT_TAPE          COMPLETED             1          241           25
2021-AUG-17 RECVR AREA    *                 COMPLETED            63       50,162       49,461
2021-AUG-18 ARCHIVELOG    *                 COMPLETED             3        1,981           98
2021-AUG-18 ARCHIVELOG    DISK              COMPLETED             0          205           17
2021-AUG-18 RECVR AREA                      COMPLETED             0            0            0
2021-AUG-18 RECVR AREA    *                 COMPLETED           125      146,534       76,160
2021-AUG-18 RECVR AREA    SBT_TAPE          COMPLETED            14        2,462        1,530
2021-AUG-19 RECVR AREA    *                 COMPLETED            42       23,975        5,028

77 rows selected.

SQL> /* "OUTPUT_DEVICE_TYPE (*) indicates more than one device" */
SQL> exit
Disconnected from Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

August 19, 2021

OCI- How to retrieve instance metadata from command line

Filed under: OCI — mdinh @ 9:17 pm

When oci-metadata is not available or installed, use curl.

Some information have been removed to protect the innocent.

$ curl -L http://169.254.169.254/opc/v1/instance/
{
  "availabilityDomain" : "SVYj:US-ASHBURN-AD-2",
  "faultDomain" : "FAULT-DOMAIN-1",
  "compartmentId" :
  "displayName" :
  "hostname" : 
  "id" :
  "metadata" : {
    "ssh_authorized_keys" : 
    "nodeNumber" : "0",
    "user_data" :
    "sshkey-resourceID" : 
    "agentAuth" : "true",
    "dbSystemShape" : "Exadata.Quarter3.100"
  },
  "region" : "iad",
  "canonicalRegionName" : "us-ashburn-1",
  "ociAdName" : "iad-ad-2",
  "regionInfo" : {
    "realmKey" : "oc1",
    "realmDomainComponent" : "oraclecloud.com",
    "regionKey" : "IAD",
    "regionIdentifier" : "us-ashburn-1"
  },
  "shape" : "ExadataCavium",
  "state" : "Running",
  "timeCreated" : 1613174787736
}

Reference: OCI- How to retrieve instance metadata from command line (Doc ID 2570001.1)

How To Determine Host Is On OCI

August 14, 2021

Validating RMAN Tape Backup

Filed under: RAC,Recovery,RMAN — mdinh @ 2:12 pm

Lately, I have been validating a lot of database backups to tape.

The validation is made easy because channels are configured in RMAN.

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' FORMAT '%d_%U' PARMS 'SBT_LIBRARY=/var/opt/oracle/dbaas_acfs/$ORACLE_SID/opc/libopc.so, ENV=(OPC_PFILE=/var/opt/oracle/dbaas_acfs/$ORACLE_SID/opc/opc$ORACLE_SID.ora)' CONNECT '*';

CONFIGURE CHANNEL DEVICE TYPE DISK CONNECT '*';

Here is the generic RMAN script which has been used successfully for all databases so far.

RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS is for all databases (luckily)

[oracle@rac01 dinh]$ rman checksyntax @restore_validate.rman

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Aug 13 11:18:20 2021

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

RMAN> set echo on
2> spool log to restore_validate.log
3> connect target;
4> show all;
5> restore spfile validate device type=SBT_TAPE;
6> restore controlfile validate device type=SBT_TAPE;
7> restore database until time "SYSDATE" validate device type=SBT_TAPE;
8> restore archivelog from time="SYSDATE-14" validate device type=SBT_TAPE;
9> exit
[oracle@rac01 dinh]$

The database size is 2.4 TB.
There are 1,495 backup pieces.
Restore validate completed in ~1 hr.

--- Verify retention policy.
[oracle@rac01 dinh]$ grep -i "policy" restore_validate.log
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
[oracle@rac01 dinh]$ 

--- Check restore timing.
[oracle@rac01 dinh]$ grep "restore at" restore_validate.log
Starting restore at 2021-AUG-13 11:20:04
Finished restore at 2021-AUG-13 11:20:21
Starting restore at 2021-AUG-13 11:20:21
Finished restore at 2021-AUG-13 11:20:35
Starting restore at 2021-AUG-13 11:20:36
Finished restore at 2021-AUG-13 11:34:01
Starting restore at 2021-AUG-13 11:34:02
Finished restore at 2021-AUG-13 12:13:51
[oracle@rac01 dinh]$

--- Still unclear why archived logs from DISK are scanned.
[oracle@rac01 dinh]$ grep "scanning archived log" restore_validate.log|grep ORA
channel ORA_SBT_TAPE_1: scanning archived log +RECOC1/db_unique_name/ARCHIVELOG/2021_08_13/thread_1_seq_68216.70749.1080469891
channel ORA_SBT_TAPE_4: scanning archived log +RECOC1/db_unique_name/ARCHIVELOG/2021_08_13/thread_1_seq_68217.64086.1080473423
channel ORA_SBT_TAPE_2: scanning archived log +RECOC1/db_unique_name/ARCHIVELOG/2021_08_13/thread_1_seq_68218.10749.1080473497
channel ORA_SBT_TAPE_3: scanning archived log +RECOC1/db_unique_name/ARCHIVELOG/2021_08_13/thread_2_seq_70946.42152.1080473423
channel ORA_SBT_TAPE_2: scanning archived log +RECOC1/db_unique_name/ARCHIVELOG/2021_08_13/thread_2_seq_70947.12624.1080473499
[oracle@rac01 dinh]$

--- Verify All backup pieces are from TAPE.
[oracle@rac01 dinh]$ grep "piece handle" restore_validate.log|grep -v TAPE

--- There are 1495 backup pieces - Needs Improvements.
[oracle@rac01 dinh]$ grep -c "piece handle" restore_validate.log
1495
[oracle@rac01 dinh]$

--- Examples of backup pieces.
[oracle@rac01 dinh]$ grep "piece handle" restore_validate.log|head
channel ORA_SBT_TAPE_1: piece handle=c-2010814236-20210813-16 tag=TAG20210813T103149
channel ORA_SBT_TAPE_1: piece handle=c-2010814236-20210813-16 tag=TAG20210813T103149
channel ORA_SBT_TAPE_1: piece handle=$ORACLE_SID_sa05a48k_1_1 tag=BKP_$ORACLE_SID1_202107310200
channel ORA_SBT_TAPE_3: piece handle=$ORACLE_SID_iu05si7j_1_1 tag=BKP_$ORACLE_SID1_202108070200
channel ORA_SBT_TAPE_1: piece handle=$ORACLE_SID_j105si7k_1_1 tag=BKP_$ORACLE_SID1_202108070200
channel ORA_SBT_TAPE_2: piece handle=$ORACLE_SID_sr05a496_1_1 tag=BKP_$ORACLE_SID1_202107310200
channel ORA_SBT_TAPE_3: piece handle=$ORACLE_SID_j305si7k_1_1 tag=BKP_$ORACLE_SID1_202108070200
channel ORA_SBT_TAPE_1: piece handle=$ORACLE_SID_j205si7k_1_1 tag=BKP_$ORACLE_SID1_202108070200
channel ORA_SBT_TAPE_4: piece handle=$ORACLE_SID_s905a48k_1_1 tag=BKP_$ORACLE_SID1_202107310200
channel ORA_SBT_TAPE_2: piece handle=$ORACLE_SID_j505si7k_1_1 tag=BKP_$ORACLE_SID1_202108070200
[oracle@rac01 dinh]$

[oracle@rac01 dinh]$ grep "piece handle" restore_validate.log|tail
channel ORA_SBT_TAPE_4: piece handle=$ORACLE_SID_5206cvrd_1_1 tag=BKP_$ORACLE_SID1_202108130730
channel ORA_SBT_TAPE_4: piece handle=$ORACLE_SID_5n06d6sm_1_1 tag=BKP_$ORACLE_SID1_202108130930
channel ORA_SBT_TAPE_2: piece handle=$ORACLE_SID_5e06d3bu_1_1 tag=BKP_$ORACLE_SID1_202108130830
channel ORA_SBT_TAPE_3: piece handle=$ORACLE_SID_5d06d3bu_1_1 tag=BKP_$ORACLE_SID1_202108130830
channel ORA_SBT_TAPE_3: piece handle=$ORACLE_SID_6106dac5_1_1 tag=BKP_$ORACLE_SID1_202108131030
channel ORA_SBT_TAPE_1: piece handle=$ORACLE_SID_5c06d3bu_1_1 tag=BKP_$ORACLE_SID1_202108130830
channel ORA_SBT_TAPE_4: piece handle=$ORACLE_SID_5m06d6sm_1_1 tag=BKP_$ORACLE_SID1_202108130930
channel ORA_SBT_TAPE_3: piece handle=$ORACLE_SID_5v06dac5_1_1 tag=BKP_$ORACLE_SID1_202108131030
channel ORA_SBT_TAPE_2: piece handle=$ORACLE_SID_5l06d6sm_1_1 tag=BKP_$ORACLE_SID1_202108130930
channel ORA_SBT_TAPE_1: piece handle=$ORACLE_SID_5u06dac5_1_1 tag=BKP_$ORACLE_SID1_202108131030
[oracle@rac01 dinh]$

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.

Next Page »

Blog at WordPress.com.