Michael Dinh
How To Resolve Tablespace Created With Windows Path In Linux
Now I understand the rationale for not providing DBA privileges to inexperience developers.
Currently, I am uncertain if the current environment is production or not.
Here is demo for 11.2.0.4 to demonstrate the issue and resolution.
Reference:
https://docs.oracle.com/cd/E18283_01/server.112/e17120/dfiles005.htm#i1006478
[oracle@ol7-112-dg1 trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 4 14:46:37 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/oradata
----------------------------------------
--- Windows path used for datafile:
----------------------------------------
SQL> create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M;
Tablespace created.
SQL> set lines 200
SQL> col name for a80
SQL> select file#,name from v$datafile order by 1;
FILE# NAME
---------- --------------------------------------------------------------------------------
1 /u01/oradata/hawk/system01.dbf
2 /u01/oradata/hawk/sysaux01.dbf
3 /u01/oradata/hawk/undotbs01.dbf
4 /u01/oradata/hawk/users01.dbf
5 /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf
SQL> alter tablespace TEST offline normal;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TEST OFFLINE
6 rows selected.
SQL> host
----------------------------------------
--- Rename datafile from OS
----------------------------------------
[oracle@ol7-112-dg1 trace]$ mv -fv /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf /u01/oradata/test.dbf
‘/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf’ -> ‘/u01/oradata/test.dbf’
[oracle@ol7-112-dg1 trace]$ exit
exit
SQL> alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf';
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TEST OFFLINE
6 rows selected.
SQL> alter tablespace TEST online;
Tablespace altered.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
TEST ONLINE
6 rows selected.
SQL> drop tablespace TEST including contents and datafiles;
Tablespace dropped.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7-112-dg1 trace]$
----------------------------------------
--- Alert Log: NO ERRORS!
----------------------------------------
[oracle@ol7-112-dg1 trace]$ tail -25 alert_hawk.log
LNS: Standby redo logfile selected for thread 1 sequence 126 for destination LOG_ARCHIVE_DEST_2
Sat Dec 04 14:47:17 2021
create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M
Completed: create tablespace TEST datafile 'D:\Oracle\oradata\E1Local\TEST.dbf' SIZE 16M
Sat Dec 04 14:48:08 2021
alter tablespace TEST offline normal
Completed: alter tablespace TEST offline normal
Sat Dec 04 14:48:08 2021
Starting background process SMCO
Sat Dec 04 14:48:08 2021
SMCO started with pid=36, OS id=5159
Sat Dec 04 14:48:19 2021
Checker run found 2 new persistent data failures
Sat Dec 04 14:49:33 2021
alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf'
Completed: alter tablespace TEST rename datafile '/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '/u01/oradata/test.dbf'
Sat Dec 04 14:49:42 2021
Checker run found 1 new persistent data failures
Sat Dec 04 14:50:00 2021
alter tablespace TEST online
Completed: alter tablespace TEST online
Sat Dec 04 14:50:33 2021
drop tablespace TEST including contents and datafiles
Deleted file /u01/oradata/test.dbf
Completed: drop tablespace TEST including contents and datafiles
[oracle@ol7-112-dg1 trace]$
----------------------------------------
--- Resolution for 19c with ASM
----------------------------------------
SQL> alter session set container=PDB;
SQL> ALTER DATABASE MOVE DATAFILE '/u02/app/oracle/product/19.0.0.0/dbhome_1/dbs/D:OracleoradataE1LocalTEST.dbf' TO '+DATAC1';Flashback DataGuard Primary Database
Practicing flashback just in case it is needed during production deployment.
It’s recommended to use DGMGRL where DataGuard Broker is used.
The disadvantage is commands will be different for each environment vs simple and consistent
recover managed standby database cancel and
recover managed standby database using current logfile disconnect
--------------------------------------------------
--- CHECK FLASHBACK ENABLED:
--------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
### PRIMARY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;
CURRENT_SCN NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
1056941 HAWK READ WRITE PRIMARY YES
SQL>
-- Enable flashback if required:
alter database flashback on;
select current_scn,name,open_mode,database_role,flashback_on from v$database;
### STANDBY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;
CURRENT_SCN NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
1037296 HAWK MOUNTED PHYSICAL STANDBY YES
SQL>
-- Enable flashback if required:
recover managed standby database cancel;
alter database flashback on;
select current_scn,name,open_mode,database_role,flashback_on from v$database;
--------------------------------------------------
--- CREATE GRP:
--------------------------------------------------
### PRIMARY:
SQL> create restore point RP_TEST guarantee flashback database;
Restore point created.
SQL>
--------------------------------------------------
--- CHECK GRP:
--------------------------------------------------
Depending on database version, GRP is replicated to standby.
### PRIMARY:
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
SCN NAME GUA
---------- ------------------------------ ---
1057112 RP_TEST YES
SQL>
### STANDBY:
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
no rows selected
SQL>
##################################################
### FLASHBACK TO RESTORE POINT:
##################################################
### Flashback PRIMARY:
SQL> select current_scn,name,open_mode,database_role,flashback_on from v$database;
CURRENT_SCN NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ON
----------- ------------------------------ -------------------- ---------------- ------------------
1057767 HAWK READ WRITE PRIMARY YES
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 469765280 bytes
Database Buffers 1124073472 bytes
Redo Buffers 7319552 bytes
Database mounted.
SQL> select scn, name, GUARANTEE_FLASHBACK_DATABASE from v$restore_point;
SCN NAME GUA
---------- ------------------------------ ---
1057112 RP_TEST YES
SQL> flashback database to restore point RP_TEST;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL>
### Flashback STANDBY:
SQL> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active
--- (USE PRIMARY v$restore_point.SCN)
SQL> flashback standby database to scn &scn;
Enter value for scn: 1057112
old 1: flashback standby database to scn &scn
new 1: flashback standby database to scn 1057112
Flashback complete.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL>
--------------------------------------------------
--- Check DG using dgmgrl:
--------------------------------------------------
DGMGRL> show configuration
Configuration - my_dg_config
Protection Mode: MaxPerformance
Databases:
hawk - Primary database
hawk_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> show database hawk
Database - hawk
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
hawk
Database Status:
SUCCESS
DGMGRL> show database hawk_stby
Database - hawk_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 12 seconds ago)
Apply Lag: 0 seconds (computed 12 seconds ago)
Apply Rate: 0 Byte/s
Real Time Query: OFF
Instance(s):
hawk
Database Status:
SUCCESS
DGMGRL>
Q.E.D.AHFCTL setresourcelimit CPU
It was detemrined that AHF was using too much CPU affecting RAC cluster performance.
AHF was shutdown temporarily over the weekend to prevent issues with application.
--- SHORT version to limit CPU resource:
# /opt/oracle.ahf/bin/ahfctl setresourcelimit -value 0.5
Tool: tfa, Resource: cpu, Limit value: 0.5 set successfully
# /opt/oracle.ahf/bin/ahfctl getresourcelimit
Tool: tfa, Resource: cpu, Limit value: 0.5
Tool: tfa, Resource: kmem no resource limit set
Tool: tfa, Resource: swmem no resource limit set--- LONG version - start AHF and limit CPU resource:
# /opt/oracle.ahf/bin/ahfctl startahf -all
Starting TFA..
Waiting up to 100 seconds for TFA to be started..
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
. . . . .
Successfully started TFA Process..
. . . . .
TFA Started and listening for commands
INFO: Starting exachk scheduler in background.
Details for the process can be found at /u01/app/grid/oracle.ahf/data/xxxxxxxxxxxxxx-21acd-9gu5j2/diag/exachk/compliance_start_251021_112642.log
# /opt/oracle.ahf/bin/ahfctl setresourcelimit -value 0.5
Tool: tfa, Resource: cpu, Limit value: 0.5 set successfully
# /opt/oracle.ahf/bin/ahfctl getresourcelimit
Tool: tfa, Resource: cpu, Limit value: 0.5
Tool: tfa, Resource: kmem no resource limit set
Tool: tfa, Resource: swmem no resource limit set
# /opt/oracle.ahf/bin/ahfctl set autodiagcollect=ON
Successfully set autodiagcollect=ON
.-------------------------------------------------.
| xxxxxxxxxxxxxx-21acd-9gu5j2 |
+-----------------------------------------+-------+
| Configuration Parameter | Value |
+-----------------------------------------+-------+
| Auto Diagcollection ( autodiagcollect ) | ON |
'-----------------------------------------+-------'
# /opt/oracle.ahf/bin/ahfctl set chaautocollect=ON
Successfully set chaautocollect=ON
.---------------------------------.
| xxxxxxxxxxxxxx-21acd-9gu5j2 |
+-------------------------+-------+
| Configuration Parameter | Value |
+-------------------------+-------+
| chaautocollect | ON |
'-------------------------+-------'
# /opt/oracle.ahf/bin/ahfctl set minicollection=ON
Successfully set minicollection=ON
.-----------------------------------------------------------.
| xxxxxxxxxxxxxx-21acd-9gu5j2 |
+---------------------------------------------------+-------+
| Configuration Parameter | Value |
+---------------------------------------------------+-------+
| Generation of Mini Collections ( minicollection ) | ON |
'---------------------------------------------------+-------'
# /opt/oracle.ahf/bin/ahfctl statusahf
.--------------------------------------------------------------------------------------------------------------------.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+-----------------------------+---------------+--------+------+------------+----------------------+------------------+
| xxxxxxxxxxxxxx-21acd-9gu5j2 | RUNNING | 151372 | 5000 | 21.1.4.0.0 | 21140020210628122659 | COMPLETE |
| xxxxxxxxxxxxxx-21acd-9gu5j1 | RUNNING | 89572 | 5000 | 21.1.4.0.0 | 21140020210628122659 | COMPLETE |
'-----------------------------+---------------+--------+------+------------+----------------------+------------------'
------------------------------------------------------------
Master node = xxxxxxxxxxxxxx-21acd-9gu5j1
exachk daemon version = 21.1.4
Install location = /opt/oracle.ahf/exachk
Started at = Mon Oct 25 11:27:10 CDT 2021
Scheduler type = TFA Scheduler
------------------------------------------------------------
ID: exachk.autostart_client_exatier1
------------------------------------------------------------
AUTORUN_FLAGS = -usediscovery -profile exatier1 -syslog -dball -showpass -tag autostart_client_exatier1 -readenvconfig
COLLECTION_RETENTION = 7
AUTORUN_SCHEDULE = 3 2 * * 1,2,3,4,5,6
------------------------------------------------------------
------------------------------------------------------------
ID: exachk.autostart_client
------------------------------------------------------------
AUTORUN_FLAGS = -usediscovery -syslog -tag autostart_client -readenvconfig
COLLECTION_RETENTION = 14
AUTORUN_SCHEDULE = 3 3 * * 0
------------------------------------------------------------
Next auto run starts on Oct 26, 2021 02:03:00
ID:exachk.AUTOSTART_CLIENT_EXATIER1
# /opt/oracle.ahf/bin/tfactl get collect -match
.------------------------------------------------------------------------------.
| xxxxxxxxxxxxxx-21acd-9gu5j2 |
+----------------------------------------------------------------------+-------+
| Configuration Parameter | Value |
+----------------------------------------------------------------------+-------+
| ISA Data Gathering ( collection.isa ) | ON |
| collectTrm | OFF |
| collectAllDirsByFile | ON |
| Auto Diagcollection ( autodiagcollect ) | ON |
| Generation of Mini Collections ( minicollection ) | ON |
| chaautocollect | ON |
| Maximum File Collection Size (MB) ( maxFileCollectionSize ) | 5120 |
| Maximum Collection Size of Core Files (MB) ( maxCoreCollectionSize ) | 500 |
| minTimeForAutoDiagCollection | 300 |
'----------------------------------------------------------------------+-------'Recover Dropped User Using Standby Database
A critical user was accidentally dropped around one week ago and will need be recovered.
Here are semi detailed steps used to flashback standby database, export user from standby, and import to primary.
============================================================
### Recover user that was deleted about week ago.
============================================================
Request Created: 14/Oct/2021 6:41 AM
DB version: 12.1.0.2.0
==============================
### CURRENT FLASHBACK INFO:
==============================
SQL>
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE
----- -------------- --------- ----------------
ORC1 STANDBY1 MOUNTED PHYSICAL STANDBY
INST OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_MB EST_FLASHBACK_MB
----- -------------------- --------------------- ---------------- ------------ ----------------
1 984681951010 04-OCT-2021 18:44:28 7200 1767000 895861
CURRENT_SCN
------------
985044762265
SQL>
==============================
### STOP STANDBY APPLY:
==============================
DGMGRL> edit database 'STANDBY1' set state='APPLY-OFF';
Succeeded.
DGMGRL>
========================================
### FLASHBACK USING TIMESTAMP FAILED:
========================================
SQL> shutdown abort;
SQL> startup mount restrict exclusive;
SQL> flashback database to timestamp TO_TIMESTAMP('2021-10-05 00:00:00','YYYY-MM-DD HH24:MI:SS');
flashback database to timestamp TO_TIMESTAMP('2021-10-05 00:00:00','YYYY-MM-DD HH24:MI:SS')
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
SQL>
========================================
### FLASHBACK USING SCN:
to_timestamp can't convert to SCN properly
========================================
SQL> flashback database to scn 984681951011;
========================================
### FLASHBACK WILL EVENTUALLY FAIL:
========================================
SQL> flashback database to scn 984681951011;
flashback database to scn 984681951011
*
ERROR at line 1:
ORA-38861: flashback recovery stopped before reaching recovery target
SQL>
========================================
### CHECK ALERT LOG:
========================================
Thu Oct 14 13:59:52 2021
Errors in file /u01/app/oracle/diag/rdbms/STANDBY1/ORC1/trace/ORC1_pr00_12838.trc:
ORA-00283: recovery session canceled due to errors
ORA-38861: flashback recovery stopped before reaching recovery target
ORA-16016: archived log for thread 1 sequence# 477183 unavailable
ORA-38861: signalled during: flashback database to scn 984681951011...
========================================
### RESTORE ARCHIVELOG from alert log:
========================================
RMAN> restore archivelog logseq 477183;
========================================
### CONTINUE FLASHBACK:
========================================
SQL> flashback database to scn 984681951011;
========================================
### DO NOT OPEN READ ONLY
WITHOUT CANCELING REDO APPLY WITHOUT ADG.
========================================
Active Data Guard enables read-only access to a physical standby database while Redo Apply is active.
SQL> recover managed standby database cancel;
SQL> alter database open read only;
========================================
### CHECK FOR DROPPED USER:
========================================
SQL> select created, username from dba_users where username='XXX';
CREATED USERNAME
-------------------------- --------
2008-SEP-18 20:19:33 XXX
SQL>
========================================
### USING DATAPUMP DID NOT WORK:
========================================
How To Use DataPump Export (EXPDP) To Export From Physical Standby Database (Doc ID 1356592.1)
--- For physical standby
We can execute exp in physical standby database when it is in read only
https://dbaminds.wordpress.com/2016/01/07/perform-export-expdp-from-physical-standby-and-logical-standby/
--- Use Snapshot Standby - did not test.
https://dohdatabase.com/2021/04/22/datapump-export-from-data-guard/
========================================
### EXP WORKED: PERFORMED BY CLIENT
========================================
### From STANDBY:
$ cat exp.par
file=exp.dmp
compress=n
grants=y
indexes=y
direct=y
log=exp.log
rows=y
consistent=y
owner=schema
triggers=y
constraints=y
exp userid=system parfile=exp.par
scp exp.dmp oracle@target:/home/oracle/
### From PRIMARY:
$ cat imppar
file=exp.dmp
grants=y
indexes=y
rows=y
log=imp.log
fromuser=schema
commit=n
constraints=y
compile=y
imp userid=system parfile=imp.par
========================================
### RECOVER STANDBY TO CURRENT TIME:
========================================
SQL> recover managed standby database using current logfile parallel 4 disconnect;
Media recovery complete.
SQL>
========================================
### RESTORE ARCHIVELOG TO FRA IS BEING DELETED:
========================================
Example:
Thu Oct 14 18:02:30 2021
Deleted Oracle managed file +FRA/STANDBY1/ARCHIVELOG/2021_10_14/thread_1_seq_477816.752.1085939417
Thu Oct 14 18:02:31 2021
Deleted Oracle managed file +FRA/STANDBY1/ARCHIVELOG/2021_10_14/thread_1_seq_477794.1120.1085939341
========================================
### RESTORE ARCHIVELOG TO NEW DESTINATION:
========================================
mkdir -p /ubb1/rman/ORC1/archivelog
========================================
### CHECK ALERT LOG FOR GAP SEQUENCE:
========================================
grep 'Fetching gap sequence in thread' /u01/app/oracle/diag/rdbms/STANDBY1/ORC1/trace/alert_ORC1.log
========================================
### RESOLVE ARCHIVE GAP:
========================================
Fetching gap sequence in thread 1, gap sequence 477604-477604
RMAN> run {
set archivelog destination to '/ubb1/rman/ORC1/archivelog';
restore archivelog from sequence 477604 until sequence 477604;}2> 3>
========================================
### FOR REFERENCE: 157 gap sequences
========================================
$ grep -c 'Fetching gap sequence in thread' /u01/app/oracle/diag/rdbms/STANDBY1/ORC1/trace/alert_ORC1.log
157
========================================
### RECOVER ALL ARCHIVELOG TO SAVE TIME:
========================================
SQL> r
1 select PID,inst_id inst,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
2 from gv$managed_standby
3 where BLOCK#>1
4 and status not in ('CLOSING','IDLE')
5 order by status desc, thread#, sequence#
6*
CLIENT DELAY
PID INST THREAD# PROCESS PROCESS STATUS SEQUENCE# BLOCK# MINS
------------------------ ----- -------- ------------ --------- ------------ --------- -------- ------
8723 1 1 LGWR RFS RECEIVING 483532 119582 0
29047 1 1 N/A MRP0 APPLYING_LOG 477715 1545345 0
SQL>
========================================
### RESTORE ARCHIVELOG UNTIL SEQUENCE 483515:
========================================
RMAN> run {
set archivelog destination to '/ubb1/rman/ORC1/archivelog';
restore archivelog from sequence 477715 until sequence 483515;}2> 3>
ALTERNATIVE:
set archivelog destination to '/ubb1/rman/ORC1/archivelog';
restore archivelog from sequence 477715 until sequence 483515;
========================================
### ENABLE REDO APPLY USING DGMGRL:
========================================
SQL> recover managed standby database cancel;
Media recovery complete.
SQL>
DGMGRL> show configuration
Configuration - linkdg
Protection Mode: MaxPerformance
Members:
PRIMARY - Primary database
STANDBY1 - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the database
STANDBY2 - Physical standby database
SBY3 - Physical standby database
Warning: ORA-16532: Oracle Data Guard broker configuration does not exist
Fast-Start Failover: DISABLED
Configuration Status:
ERROR (status updated 28 seconds ago)
DGMGRL> show database STANDBY1
Database - STANDBY1
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 1 minute 6 seconds (computed 0 seconds ago)
Average Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
ORC1
Database Status:
SUCCESS
DGMGRL> edit database 'STANDBY1' set state='APPLY-ON';
Succeeded.
DGMGRL> show database STANDBY1
Database - STANDBY1
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 6.01 MByte/s
Real Time Query: OFF
Instance(s):
ORC1
Database Status:
SUCCESS
DGMGRL> validate database STANDBY1;
Database Role: Physical standby database
Primary Database: PRIMARY
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
DGMGRL> show configuration
Configuration - linkdg
Protection Mode: MaxPerformance
Members:
PRIMARY - Primary database
STANDBY1 - Physical standby database
STANDBY2 - Physical standby database
SBY3 - Physical standby database
Warning: ORA-16532: Oracle Data Guard broker configuration does not exist
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 15 seconds ago)
DGMGRL>
Q.E.D.How To Change RMAN Config For Standby DB
Here is the typical error when changing RMAN configuration for standby database.
[oracle@ol7-112-dg2 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 12 21:02:07 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, not open)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default
RMAN> configure retention policy to recovery window of 7 days;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 10/12/2021 21:03:28
RMAN-05021: this configuration cannot be changed for a BACKUP or STANDBY control file
RMAN> exit
Recovery Manager complete.
[oracle@ol7-112-dg2 ~]$There are suggestions found from MOS. Yuck!
Steps to recreate a Physical Standby Controlfile (Doc ID 459411.1)
Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files (Doc ID 734862.1)
Thanks to teammate for the easy method.
[oracle@ol7-112-dg2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 12 21:04:04 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> set pages 200
SQL> col value format a50
SQL> select name, database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
HAWK PHYSICAL STANDBY
SQL> select * from v$rman_configuration;
no rows selected
SQL> set serveroutput on
SQL> !vi t.sql
SQL> @t.sql
SQL> DECLARE
2 x NUMBER;
3 BEGIN
4 x := dbms_backup_restore.setconfig('RETENTION POLICY','TO RECOVERY WINDOW OF 7 DAYS');
5 dbms_output.put_line('setconfig returned ' || x);
6 END;
7 /
setconfig returned 1
PL/SQL procedure successfully completed.
SQL> select * from v$rman_configuration;
CONF# NAME
---------- -----------------------------------------------------------------
VALUE CON_ID
-------------------------------------------------- ----------
1 RETENTION POLICY
TO RECOVERY WINDOW OF 7 DAYS 0
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-dg2 ~]$
[oracle@ol7-112-dg2 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Oct 12 21:08:47 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, not open)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name HAWK_STBY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
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 RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/19c/dbhome_1/dbs/snapcf_hawk.f'; # default
RMAN> exit
Recovery Manager complete.
[oracle@ol7-112-dg2 ~]$Find Tablespace Info Using RMAN report schema
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 ~]$
AutoUpgrade 21.2.210721 Data Guard ORA-16532
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
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.0OCI- How to retrieve instance metadata from command line
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)
Validating RMAN Tape Backup
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]$RMAN Backup Tuning – MAXOPENFILES
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 datafiies.
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> exitThe Horror Of Restore Validate For SBT_TAPE Backups
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 restoreHow 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.
How To Determine Host Is On OCI
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 ~]$Create Delete RAC DB Using dbca silent
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 ~]$
Data Pump Compatible Version
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.2That’s all folks.
__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-60baca8712286', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy', } } }); });emcli dg_verify_config create_srls
Convention.
DB_NAME=ORACLE_SID=XXXXXXXValidate 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.
Query OEM mgmt$(target|target_properties)
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
;
emcli dg_verify_config
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.
Gather Your Session Info For Killing
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
^CI 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.
Extract DB User Password
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>