Hemant K Chitale

Subscribe to Hemant K Chitale feed
I am an Oracle Database Specialist in Singapore.
Please note that this site uses cookies.

Updated: 14 hours 33 min ago

Real Time SQL Monitoring using SQL Developer

Sat, 2020-10-24 11:41

Since a previous post with screenshots was very popular


I have created a  Video Demo on my Youtube Channel



Categories: DBA Blogs

On-Premises SQL Server to Oracle ADB on the Cloud -- 2

Sun, 2020-10-11 10:23

 Having configured connectivity between an On-Premises SQL Server Database to an Oracle ADB Database in the Oracle Cloud in the previous post, I will now copy data from SQL Server to Oracle


  • Create the Target Table in Oracle ADB 

SQL Developer


  • Verify Existing Rows in SQL Server


SQL Server Management Studio

  • Insert into Oracle with Select from SQL Server

SQL Server Management Studio


  • Verify Rows Inserted into Oracle

SQL Developer


  • Create New Row in SQL Server and Verify that it is NOT present in Oracle
SQL Server Management Studio


The last screenshot is literally a NOT IN query between SQL Server On-Premises and Oracle in the Cloud.












Categories: DBA Blogs

On-Premises SQL Server to Oracle ADB on the Cloud - 1

Sat, 2020-10-10 10:58
Setting up a connection between an On-Premises SQL Server Database Instance to an Oracle ADB on the Oracle Cloud

Strong Caveat :  This is only a POC.  Most organisations would NOT allow an open direct connection between an On-Premises Database and and External Site (whether a Database or any other Service).  

1. On my Free-Tier Oracle ADB, I login as the ADMIN user and configure a new database account "ss_user"

SQL Developer to Oracle ADB


2. I install Oracle Client and configure connectivity to the ADB database with the Wallet information

(for detailed instructions see the Oracle Cloud documentation here).  {I used an 18c Client on Windows to connect to 19c ADB, simply because I already and 18c client and didn't want to wait to download the 19c client}

sqlnet.ora



tnsnames.ora



3.  Optionally re-register the OraOLEDB18.DLL file  (using CMD as Administrator)  (you might also need to reboot your Windows or restart the SQL Server Instance)

CMD as Administrator




4.  Define the Linked Server in SQL Server (using SSMS)


Oracle OLEDB Provider




Linked Server Configuration Pag


Linked Server Security Configuration


5.  Test Connectivity

Test Connectivity Option for Linked Server



6  Run queries against from SSMS


SSMS Query and Results Pane


Categories: DBA Blogs

Extracting DDL using SQL Developer

Fri, 2020-10-02 21:50

 The "DDL" command in SQL Developer 20.2 new mimics the command in sqlcl


Here I extract the definition my table OBJECTS_LIST and it's index



Here I extract the code for a Stored Procedure





Categories: DBA Blogs

Verifying an RMAN Backup -- Part 2

Tue, 2020-09-29 10:06

 Continuing on my previous blog post,  the question being "when you receive an RMAN Backup from another DBA, how do you confirm that the database can be restored and recovered to a Consistent Point In Time ?"

The quick steps, without actually running a RESTORE DATABASE command are :

1. Create a dummy parameter file with
    a. DB_NAME the same as the source database
    b. A different DB_UNIQUE_NAME (particulary if you have an existing database on the target server with the same DB_NAME)
    c. CONTROL_FILE specifying a "temporary" location -- you will be removing the control files and restoring them to the actual desired target location when you choose to do a Full Restore
2. Restore the Controlfile
3. Remove all entries about RMAN Backups from the Controlfile (as it has a history of recent backups and may even be a Controlfile backup newer than the Database backup that is provided to you, capturing more recent backups
4. Catalog the set of Backup Pieces that you receive
5. Query the catalog that you now create in the Controlfile to check the ArchiveLogs vis-a-vis the Datafiles in the set of Backup Pieces.


So, I'll demonstrate them again in 19c and a Non-CDB database here.  The source Database DB_NAME is "HEMANT" so I create in RTST parameter file with DB_NAME='HEMANT' and DB_UNIQUE_NAME='RTST'

I then restore the Controlfile, remove all entries of previous backups, CATALOG the Backup Pieces that I have received and then query the Controlfile.  (The CATALOG START WITH updates the Controlfile with information from the Backup Pieces, although the REPORT SCHEMA command is from the database structure in the controlfile).



oracle19c>echo $ORACLE_SID
RTST
oracle19c>cat $ORACLE_HOME/dbs/initRTST.ora
db_name = 'HEMANT'
db_unique_name = 'RTST'
control_files='/tmp/RTST_control.ctl'
#enable_pluggable_database=true
oracle19c>
oracle19c>cd HEMANT_DB_Backup
oracle19c>pwd
/home/oracle/HEMANT_DB_Backup
oracle19c>ls -l
total 140504
-rw-r-----. 1 oracle oinstall 4390912 Sep 29 22:01 0cvbkgui_1_1
-rw-r-----. 1 oracle oinstall 58507264 Sep 29 22:01 0evbkh0d_1_1
-rw-r-----. 1 oracle oinstall 6381568 Sep 29 22:01 0fvbkh0k_1_1
-rw-r-----. 1 oracle oinstall 51978240 Sep 29 22:01 0gvbkh0r_1_1
-rw-r-----. 1 oracle oinstall 2179072 Sep 29 22:01 0hvbkh12_1_1
-rw-r-----. 1 oracle oinstall 1622016 Sep 29 22:01 0ivbkh13_1_1
-rw-r-----. 1 oracle oinstall 4863488 Sep 29 22:01 0jvbkh14_1_1
-rw-r-----. 1 oracle oinstall 2187264 Sep 29 22:01 0kvbkh14_1_1
-rw-r-----. 1 oracle oinstall 11763712 Sep 29 22:01 c-432411782-20200929-06
oracle19c>
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Sep 29 22:05:03 2020
Version 19.3.0.0.0

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

connected to target database (not started)

RMAN> startup nomount;

Oracle instance started

Total System Global Area 268434280 bytes

Fixed Size 8895336 bytes
Variable Size 201326592 bytes
Database Buffers 50331648 bytes
Redo Buffers 7880704 bytes

RMAN> restore controlfile from '/home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06';

Starting restore at 29-SEP-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/RTST_control.ctl
Finished restore at 29-SEP-20

RMAN>
RMAN> delete backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
12 12 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1
13 13 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03
14 14 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1
15 15 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1
16 16 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1
17 17 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1
18 18 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1
19 19 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1
20 20 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1
21 21 1 1 AVAILABLE DISK /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05

Do you really want to delete the above objects (enter YES or NO)? YES

RMAN-06207: warning: 10 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1
RMAN-06214: Backup Piece /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05


RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1 RECID=12 STAMP=1052394450
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03 RECID=13 STAMP=1052394452
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1 RECID=14 STAMP=1052394509
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1 RECID=15 STAMP=1052394516
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1 RECID=16 STAMP=1052394523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1 RECID=17 STAMP=1052394530
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1 RECID=18 STAMP=1052394532
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1 RECID=19 STAMP=1052394532
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1 RECID=20 STAMP=1052394533
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05 RECID=21 STAMP=1052394534
Crosschecked 10 objects


RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
12 12 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1
13 13 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03
14 14 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1
15 15 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1
16 16 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1
17 17 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1
18 18 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1
19 19 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1
20 20 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1
21 21 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1 RECID=12 STAMP=1052394450
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03 RECID=13 STAMP=1052394452
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1 RECID=14 STAMP=1052394509
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1 RECID=15 STAMP=1052394516
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1 RECID=16 STAMP=1052394523
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1 RECID=17 STAMP=1052394530
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1 RECID=18 STAMP=1052394532
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1 RECID=19 STAMP=1052394532
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1 RECID=20 STAMP=1052394533
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05 RECID=21 STAMP=1052394534
Deleted 10 EXPIRED objects


RMAN>
RMAN> list backup;

specification does not match any backup in the repository

RMAN> catalog start with '/home/oracle/HEMANT_DB_Backup/';

searching for all files that match the pattern /home/oracle/HEMANT_DB_Backup/

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06

RMAN>
RMAN> report schema;

RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name RTST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 400 SYSTEM *** /opt/oracle/oradata/HEMANT/system.dbf
2 400 SYSAUX *** /opt/oracle/oradata/HEMANT/sysaux.dbf
3 200 UNDOTBS1 *** /opt/oracle/oradata/HEMANT/undotbs.dbf
4 10 USERS *** /opt/oracle/oradata/HEMANT/users01.dbf
5 10 INDX *** /opt/oracle/oradata/HEMANT/indx01.dbf
6 10 USERS *** /opt/oracle/oradata/HEMANT/users02.dbf
7 10 USERS *** /opt/oracle/oradata/HEMANT/users03.dbf
8 10 USERS *** /opt/oracle/oradata/HEMANT/users04.dbf
9 10 USERS *** /opt/oracle/oradata/HEMANT/users05.dbf
10 10 INDX *** /opt/oracle/oradata/HEMANT/indx02.dbf
11 10 INDX *** /opt/oracle/oradata/HEMANT/indx03.dbf

RMAN>
RMAN> quit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 29 22:07:56 2020
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>
SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select file#, checkpoint_change#, checkpoint_time, completion_time
2 from v$backup_datafile
3 order by 1
4 /

FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME COMPLETION_TIME
---------- ------------------ ------------------ ------------------
0 463290 29-SEP-20 11:49:14 29-SEP-20 11:49:15
1 456249 29-SEP-20 11:48:29 29-SEP-20 11:48:34
2 457590 29-SEP-20 11:48:36 29-SEP-20 11:48:39
3 458680 29-SEP-20 11:48:43 29-SEP-20 11:48:47
4 459759 29-SEP-20 11:48:50 29-SEP-20 11:48:51
5 459765 29-SEP-20 11:48:51 29-SEP-20 11:48:52
6 459779 29-SEP-20 11:48:53 29-SEP-20 11:48:53
7 459759 29-SEP-20 11:48:50 29-SEP-20 11:48:51
8 459765 29-SEP-20 11:48:51 29-SEP-20 11:48:52
9 459779 29-SEP-20 11:48:53 29-SEP-20 11:48:53
10 458680 29-SEP-20 11:48:43 29-SEP-20 11:48:43
11 456249 29-SEP-20 11:48:29 29-SEP-20 11:48:29

12 rows selected.

SQL>
SQL> select file#, checkpoint_change#, checkpoint_time, completion_time
2 from v$backup_datafile
3 order by 2
4 /

FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME COMPLETION_TIME
---------- ------------------ ------------------ ------------------
1 456249 29-SEP-20 11:48:29 29-SEP-20 11:48:34
11 456249 29-SEP-20 11:48:29 29-SEP-20 11:48:29
2 457590 29-SEP-20 11:48:36 29-SEP-20 11:48:39
3 458680 29-SEP-20 11:48:43 29-SEP-20 11:48:47
10 458680 29-SEP-20 11:48:43 29-SEP-20 11:48:43
4 459759 29-SEP-20 11:48:50 29-SEP-20 11:48:51
7 459759 29-SEP-20 11:48:50 29-SEP-20 11:48:51
5 459765 29-SEP-20 11:48:51 29-SEP-20 11:48:52
8 459765 29-SEP-20 11:48:51 29-SEP-20 11:48:52
6 459779 29-SEP-20 11:48:53 29-SEP-20 11:48:53
9 459779 29-SEP-20 11:48:53 29-SEP-20 11:48:53
0 463290 29-SEP-20 11:49:14 29-SEP-20 11:49:15

12 rows selected.

SQL>
SQL> select sequence#, first_change#, next_change#-1, next_time
2 from v$backup_archivelog_details
3 order by sequence#
4 /

SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#-1 NEXT_TIME
---------- ------------- -------------- ------------------
170 442901 448665 29-SEP-20 11:45:17
171 448666 450050 29-SEP-20 11:45:51
172 450051 451367 29-SEP-20 11:47:30
173 451368 454869 29-SEP-20 11:48:19
174 454870 457557 29-SEP-20 11:48:33
175 457558 457611 29-SEP-20 11:48:41
176 457612 459744 29-SEP-20 11:48:48
177 459745 459767 29-SEP-20 11:48:52

8 rows selected.

SQL>


In this case, file#=0  is actually the Controlfile --- so it has the highest Checkpoint SCN and Time.  As I noted in my previous post, it doesn't matter that the Controlfile is "newer" than the Datafiles.  We need to check the Datafiles with the ArchiveLogs. So, we see that the datafiles have slightly different Checkpoint SCNs (the backup was created with FILESPERSET=2 so every pair of datafiles has a Checkpoint).  The highest Datafile Checkpoint is 459779.  But the ArchiveLogs end at 459767.  Therefore, this database cannot be RECOVERed to a Consistent Point In Time.

Should I try doing a RESTORE and RECOVER, nevertheless ?

I first revert to ORACLE_SID=HEMANT and use the initHEMANT.ora parameter file that I obtained from the source server.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>ORACLE_SID=HEMANT;export ORACLE_SID
oracle19c>ls -l $ORACLE_HOME/dbs/initHEMANT.ora
-rw-r--r--. 1 oracle oinstall 693 Sep 28 23:05 /opt/oracle/product/19c/dbhome_1/dbs/initHEMANT.ora
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Sep 29 22:25:13 2020
Version 19.3.0.0.0

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

connected to target database (not started)

RMAN>
RMAN> startup nomount;

Oracle instance started

Total System Global Area 1207958960 bytes

Fixed Size 8895920 bytes
Variable Size 318767104 bytes
Database Buffers 872415232 bytes
Redo Buffers 7880704 bytes

RMAN> restore controlfile from '/home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06';

Starting restore at 29-SEP-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/oracle/oradata/HEMANT/control01.ctl
output file name=/opt/oracle/oradata/HEMANT/control02.ctl
Finished restore at 29-SEP-20

RMAN>
RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN> crosscheck backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1 RECID=12 STAMP=1052394450
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03 RECID=13 STAMP=1052394452
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1 RECID=14 STAMP=1052394509
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1 RECID=15 STAMP=1052394516
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1 RECID=16 STAMP=1052394523
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1 RECID=17 STAMP=1052394530
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1 RECID=18 STAMP=1052394532
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1 RECID=19 STAMP=1052394532
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1 RECID=20 STAMP=1052394533
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05 RECID=21 STAMP=1052394534
Crosschecked 10 objects


RMAN> delete noprompt expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
12 12 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1
13 13 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03
14 14 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1
15 15 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1
16 16 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1
17 17 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1
18 18 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1
19 19 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1
20 20 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1
21 21 1 1 EXPIRED DISK /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0cvbkgui_1_1 RECID=12 STAMP=1052394450
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-03 RECID=13 STAMP=1052394452
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0evbkh0d_1_1 RECID=14 STAMP=1052394509
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0fvbkh0k_1_1 RECID=15 STAMP=1052394516
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0gvbkh0r_1_1 RECID=16 STAMP=1052394523
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0hvbkh12_1_1 RECID=17 STAMP=1052394530
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0ivbkh13_1_1 RECID=18 STAMP=1052394532
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0jvbkh14_1_1 RECID=19 STAMP=1052394532
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0kvbkh14_1_1 RECID=20 STAMP=1052394533
deleted backup piece
backup piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20200929-05 RECID=21 STAMP=1052394534
Deleted 10 EXPIRED objects


RMAN>
RMAN> catalog start with '/home/oracle/HEMANT_DB_Backup/';

searching for all files that match the pattern /home/oracle/HEMANT_DB_Backup/

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
File Name: /home/oracle/HEMANT_DB_Backup/c-432411782-20200929-06

RMAN>
RMAN> restore database;

Starting restore at 29-SEP-20
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/HEMANT/system.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0evbkh0d_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /opt/oracle/oradata/HEMANT/sysaux.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0fvbkh0k_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/HEMANT/undotbs.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/HEMANT/indx02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0gvbkh0r_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/HEMANT/users01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/HEMANT/users03.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0hvbkh12_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/HEMANT/indx01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /opt/oracle/oradata/HEMANT/users04.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0ivbkh13_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to /opt/oracle/oradata/HEMANT/users02.dbf
channel ORA_DISK_1: restoring datafile 00009 to /opt/oracle/oradata/HEMANT/users05.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0kvbkh14_1_1 tag=TAG20200929T114829
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-SEP-20

RMAN>
RMAN> list backup of archivelog all;


List of Backup Sets
===================


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
22 4.19M DISK 00:00:00 29-SEP-20
BP Key: 22 Status: AVAILABLE Compressed: YES Tag: TAG20200929T114730
Piece Name: /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1

List of Archived Logs in backup set 22
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 170 442901 29-SEP-20 448666 29-SEP-20
1 171 448666 29-SEP-20 450051 29-SEP-20
1 172 450051 29-SEP-20 451368 29-SEP-20

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
28 4.64M DISK 00:00:01 29-SEP-20
BP Key: 28 Status: AVAILABLE Compressed: YES Tag: TAG20200929T114852
Piece Name: /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1

List of Archived Logs in backup set 28
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 173 451368 29-SEP-20 454870 29-SEP-20
1 174 454870 29-SEP-20 457558 29-SEP-20
1 175 457558 29-SEP-20 457612 29-SEP-20
1 176 457612 29-SEP-20 459745 29-SEP-20
1 177 459745 29-SEP-20 459768 29-SEP-20

RMAN>
RMAN> recover database until sequence 178;

Starting recover at 29-SEP-20
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/29/2020 22:31:03
RMAN-06556: datafile 6 must be restored from backup older than SCN 459768

RMAN>
RMAN> restore archivelog all;

Starting restore at 29-SEP-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/29/2020 22:35:48
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 178 and starting SCN of 459768 found to restore

RMAN> restore archivelog until sequence 178;

Starting restore at 29-SEP-20
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/29/2020 22:36:17
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 178 and starting SCN of 459768 found to restore

RMAN>
RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name HEMANT
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
9 1 178 A 29-SEP-20
Name: /opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf


RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
validation failed for archived log
archived log file name=/opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf RECID=9 STAMP=1052394543
Crosschecked 1 objects


RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISK
List of Archived Log Copies for database with db_unique_name HEMANT
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
9 1 178 X 29-SEP-20
Name: /opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf RECID=9 STAMP=1052394543
Deleted 1 EXPIRED objects


RMAN>
RMAN> restore archivelog all;

Starting restore at 29-SEP-20
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/29/2020 22:37:59
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 1 with sequence 178 and starting SCN of 459768 found to restore

RMAN> restore archivelog until sequence 177;

Starting restore at 29-SEP-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=170
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=171
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=172
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0cvbkgui_1_1 tag=TAG20200929T114730
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=173
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=174
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=175
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=176
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=177
channel ORA_DISK_1: reading from backup piece /home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1
channel ORA_DISK_1: piece handle=/home/oracle/HEMANT_DB_Backup/0jvbkh14_1_1 tag=TAG20200929T114852
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-SEP-20

RMAN>
RMAN> exit


Recovery Manager complete.
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 29 22:48:53 2020
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 database recover using backup controlfile until cancel;
alter database recover using backup controlfile until cancel
*
ERROR at line 1:
ORA-00279: change 456249 generated at 09/29/2020 11:48:29 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf
ORA-00280: change 456249 for thread 1 is in sequence #174


SQL>
SQL> alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf';
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf'
*
ERROR at line 1:
ORA-00279: change 457558 generated at 09/29/2020 11:48:33 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf
ORA-00280: change 457558 for thread 1 is in sequence #175
ORA-00278: log file '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf' no longer needed for this recovery


SQL> alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf';
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf'
*
ERROR at line 1:
ORA-00279: change 457612 generated at 09/29/2020 11:48:41 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf
ORA-00280: change 457612 for thread 1 is in sequence #176
ORA-00278: log file '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf' no longer needed for this recovery


SQL> alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf';
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf'
*
ERROR at line 1:
ORA-00279: change 459745 generated at 09/29/2020 11:48:48 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf
ORA-00280: change 459745 for thread 1 is in sequence #177
ORA-00278: log file '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf' no longer needed for this recovery


SQL> alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf';
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf'
*
ERROR at line 1:
ORA-00279: change 459768 generated at 09/29/2020 11:48:52 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_178_1052392838.dbf
ORA-00280: change 459768 for thread 1 is in sequence #178
ORA-00278: log file '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf' no longer needed for this recovery


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-10877: error signaled in parallel recovery slave
ORA-10877: error signaled in parallel recovery slave
ORA-01153: an incompatible media recovery is active


SQL> alter database recover cancel;
alter database recover cancel
*
ERROR at line 1:
ORA-01112: media recovery not started


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 6 was not restored from a sufficiently old backup
ORA-01110: data file 6: '/opt/oracle/oradata/HEMANT/users02.dbf'


SQL>



ArchiveLog Sequence#178 had been created in the source server before the controlfile backup but is not in the Backup Pieces I received.  So, Oracle refuses to allow me to RECOVER the database. 
A RESTORE is succesful, but the RECOVER fails.  The database cannot be OPENed.
Datafiles 6 and 9 have a higher Checkpoint SCN than the highest available in the ArchiveLogs.

Unfortunately, the default behaviour of Oracle is only to report the first Datafiles that has a higher SCN, it doesn't report all of them --- the database might have had 10 or 100 Datafiles that are "newer" than the ArchiveLogs.  That is why the SQL queries on V$BACKUP_DATAFILE and V$BACKUP_ARCHIVELOG_DETAILS that I have demonstrated earlier in this post are useful.


This is what the alert log shows :


2020-09-29T22:29:17.560085+08:00
Full restore complete of datafile 1 /opt/oracle/oradata/HEMANT/system.dbf. Elapsed time: 0:00:06
checkpoint is 456249
2020-09-29T22:29:23.415906+08:00
Full restore complete of datafile 2 /opt/oracle/oradata/HEMANT/sysaux.dbf. Elapsed time: 0:00:05
checkpoint is 457590
last deallocation scn is 450639
2020-09-29T22:29:25.874043+08:00
Full restore complete of datafile 10 /opt/oracle/oradata/HEMANT/indx02.dbf. Elapsed time: 0:00:00
checkpoint is 458680
last deallocation scn is 3
2020-09-29T22:29:29.812208+08:00
Full restore complete of datafile 3 /opt/oracle/oradata/HEMANT/undotbs.dbf. Elapsed time: 0:00:04
checkpoint is 458680
last deallocation scn is 3
2020-09-29T22:29:33.129942+08:00
Full restore complete of datafile 4 /opt/oracle/oradata/HEMANT/users01.dbf. Elapsed time: 0:00:01
checkpoint is 459759
last deallocation scn is 3
Full restore complete of datafile 7 /opt/oracle/oradata/HEMANT/users03.dbf. Elapsed time: 0:00:01
checkpoint is 459759
last deallocation scn is 3
Full restore complete of datafile 5 /opt/oracle/oradata/HEMANT/indx01.dbf. Elapsed time: 0:00:00
checkpoint is 459765
last deallocation scn is 3
Full restore complete of datafile 8 /opt/oracle/oradata/HEMANT/users04.dbf. Elapsed time: 0:00:01
checkpoint is 459765
last deallocation scn is 3
2020-09-29T22:29:35.182200+08:00
Full restore complete of datafile 6 /opt/oracle/oradata/HEMANT/users02.dbf. Elapsed time: 0:00:01
checkpoint is 459779
last deallocation scn is 3
Full restore complete of datafile 9 /opt/oracle/oradata/HEMANT/users05.dbf. Elapsed time: 0:00:01
checkpoint is 459779
last deallocation scn is 3
2020-09-29T22:34:44.026271+08:00
alter database recover using backup controlfile
2020-09-29T22:34:44.026373+08:00
Media Recovery Start
Started logmerger process
2020-09-29T22:34:44.322629+08:00
Parallel Media Recovery started with 2 slaves
ORA-279 signalled during: alter database recover using backup controlfile...
2020-09-29T22:35:34.263529+08:00
*************************************************************


2020-09-29T22:49:27.004784+08:00
alter database recover using backup controlfile until cancel
2020-09-29T22:49:27.004864+08:00
Media Recovery Start
Started logmerger process
2020-09-29T22:49:27.132583+08:00
Parallel Media Recovery started with 2 slaves
ORA-279 signalled during: alter database recover using backup controlfile until cancel...
2020-09-29T22:50:52.692824+08:00
alter database recover using backup controlfile
2020-09-29T22:50:52.692943+08:00
Media Recovery Start
ORA-275 signalled during: alter database recover using backup controlfile...
2020-09-29T22:52:19.356431+08:00
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf'
2020-09-29T22:52:19.356498+08:00
Media Recovery Log /opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_174_1052392838.dbf'...
2020-09-29T22:52:36.435252+08:00
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf'
2020-09-29T22:52:36.435374+08:00
Media Recovery Log /opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_175_1052392838.dbf'...
2020-09-29T22:52:51.906865+08:00
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf'
2020-09-29T22:52:51.906956+08:00
Media Recovery Log /opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_176_1052392838.dbf'...
2020-09-29T22:53:18.228572+08:00
alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf'
2020-09-29T22:53:18.228668+08:00
Media Recovery Log /opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf
ORA-279 signalled during: alter database recover logfile '/opt/oracle/archivelog/HEMANT/1_177_1052392838.dbf'...
2020-09-29T22:53:25.958701+08:00
alter database open resetlogs
2020-09-29T22:53:26.113916+08:00
Recovery interrupted!
ORA-10877 signalled during: alter database open resetlogs...
2020-09-29T22:53:35.846419+08:00
2020-09-29T22:53:35.846419+08:00
alter database recover cancel
ORA-1112 signalled during: alter database recover cancel...
2020-09-29T22:54:03.274546+08:00
alter database open resetlogs
2020-09-29T22:54:03.306918+08:00
Signalling error 1152 for datafile 6!
ORA-1152 signalled during: alter database open resetlogs...


So, even if I manually RESTORE the ArchiveLogs and then apply each one with the RECOVER LOGFILE command, Oracle still doesn't allow an OPEN RESETOGS because Sequence#178 is missing.


Categories: DBA Blogs

Verifying an RMAN Backup

Fri, 2020-09-25 10:41
 In general, most database backups with RMAN always include the "correct" set of ArchiveLogs.  This is done either with :
1. BACKUP DATABASE PLUS ARCHIVELOG
or
2. ALTER SYSTEM ARCHIVE LOG CURRENT ;  followed by BACKUP ARCHIVELOG

But if you receive a Backup from another DBA, can you validate that you have all the ArchiveLogs required to RECOVER DATABASE upto a consistent point (SEQUENCE# or TIME ?) ?
If you use an RMAN Catalog schema, you can query that RMAN Catalog schema for information.
But if there is no RMAN Catalog schema, all the information you need is in the Controlfile backup
One technique that can be used is
1. Create a dummy parameter file with
    a. DB_NAME the same as the source database
    b. A different DB_UNIQUE_NAME (particulary if you have an existing database on the target server with the same DB_NAME)
    c. CONTROL_FILE specifying a "temporary" location -- you will be removing the control files and restoring them to the actual desired target location when you choose to do a Full Restore
2. Restore the Controlfile
3. Remove all entries about RMAN Backups from the Controlfile (as it has a history of recent backups and may even be a Controlfile backup newer than the Database backup that is provided to you, capturing more recent backups
4. Catalog the set of Backup Pieces that you receive
5. Query the catalog that you now create in the Controlfile to check the ArchiveLogs vis-a-vis the Datafiles in the set of Backup Pieces.

At the end of the exercise, I discard the "temporary" parameter file that I used and also remove the Contolfile that I have restored.
If I find that the Backup is Good (i.e. ArchiveLogs contain enough Redo (SCNs) to RECOVER the datafiles, I can do a proper RESTORE DATABASE and RECOVER DATABASE or DUPLICATE DATABASE from the Backup.

Let's say that I receive Backup Pieces, organised as would be an FRA :

$pwd
/u01/app/Backup_from_Source/ORCL12C
$ls -l
total 32
drwxr-x--- 3 oracle oinstall 4096 Sep 18 2017 49BFE9E2D73E2038E0530100007F846C
drwxr-x--- 3 oracle oinstall 4096 Sep 18 2017 49BFF8A6BB912582E0530100007F8BE4
drwxr-x--- 3 oracle oinstall 4096 Jun 5 2017 4F793A6D323D1344E0530100007FABC7
drwxr-x--- 3 oracle oinstall 4096 Sep 18 2017 53F8012866211264E0530100007FD493
drwxr-x--- 3 oracle oinstall 4096 Jan 13 2018 5C9E4689632518EBE0530100007F03C5
drwxr-x--- 3 oracle oinstall 4096 Jun 17 22:36 A84987FDF4C51164E0530100007FEB9C
drwxr-x--- 3 oracle oinstall 4096 Sep 24 17:45 autobackup
drwxr-x--- 3 oracle oinstall 4096 Sep 24 17:45 backupset
$


I first create a parameter file as :

$cat initRTST.ora
db_name = 'ORCL12C'
db_unique_name = 'RTST'
control_files='/tmp/RTST_control.ctl'
enable_pluggable_database=true
$


Then, with ORACLE_SID set to RTST, I restore and mount the Controlfile

$ORACLE_SID=RTST;export ORACLE_SID
$ls -l /tmp/RT*
ls: cannot access /tmp/RT*: No such file or directory
$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 25 23:03:06 2020

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/12.2/db_1/dbs/initRTST.ora';
ORACLE instance started.

Total System Global Area 318767104 bytes
Fixed Size 8792152 bytes
Variable Size 251660200 bytes
Database Buffers 50331648 bytes
Redo Buffers 7983104 bytes
SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Sep 25 23:03:50 2020

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

connected to target database: ORCL12C (not mounted)

RMAN> restore controlfile from '/u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp';

Starting restore at 25-SEP-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=179 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/RTST_control.ctl
Finished restore at 25-SEP-20

RMAN>
RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN>


Next, I "clear" information about all other backups from the controlfile.

RMAN> delete noprompt backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=182 device type=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
54 54 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp
55 55 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp
56 56 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp
57 57 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp
58 58 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp
59 59 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp
60 60 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp
61 61 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp
62 62 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp
63 63 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
64 64 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp
65 65 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp
66 66 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
67 67 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
68 68 1 1 AVAILABLE DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp RECID=63 STAMP=1051983568
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp RECID=64 STAMP=1051983703
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp RECID=65 STAMP=1051983676
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp RECID=66 STAMP=1051983711
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp RECID=67 STAMP=1051983712
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp RECID=68 STAMP=1051983737
Deleted 6 objects

RMAN-06207: warning: 9 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp
RMAN-06214: Backup Piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp


RMAN>
RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp RECID=54 STAMP=1051891224
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp RECID=55 STAMP=1051891279
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp RECID=56 STAMP=1051891314
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp RECID=57 STAMP=1051891339
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp RECID=58 STAMP=1051891355
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp RECID=59 STAMP=1051891372
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp RECID=60 STAMP=1051891375
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp RECID=61 STAMP=1051891431
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp RECID=62 STAMP=1051891432
Crosschecked 9 objects


RMAN> delete noprompt expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
54 54 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp
55 55 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp
56 56 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp
57 57 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp
58 58 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp
59 59 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp
60 60 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp
61 61 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp
62 62 1 1 EXPIRED DISK /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp04r2g_.bkp RECID=54 STAMP=1051891224
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp06hp3_.bkp RECID=55 STAMP=1051891279
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp07lof_.bkp RECID=56 STAMP=1051891314
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_23/o1_mf_nnndf_TAG20200923T160023_hpp08cvs_.bkp RECID=57 STAMP=1051891339
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891355_hpp08vpr_.bkp RECID=58 STAMP=1051891355
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160252_hpp09dhy_.bkp RECID=59 STAMP=1051891372
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891375_hpp09hv5_.bkp RECID=60 STAMP=1051891375
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2020_09_23/o1_mf_annnn_TAG20200923T160351_hpp0c7dt_.bkp RECID=61 STAMP=1051891431
deleted backup piece
backup piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2020_09_23/o1_mf_s_1051891432_hpp0c8ol_.bkp RECID=62 STAMP=1051891432
Deleted 9 EXPIRED objects


RMAN> list backup;

specification does not match any backup in the repository

RMAN>


Now I am ready the catalog the Backup Pieces that I have received

RMAN> catalog start with '/u01/app/Backup_from_Source/ORCL12C';

searching for all files that match the pattern /u01/app/Backup_from_Source/ORCL12C

List of Files Unknown to the Database
=====================================
File Name: /u01/app/Backup_from_Source/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/Backup_from_Source/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp
File Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp

RMAN>


Note how the CATALOG command found 3 PDBs in the Backup.
I can now query from RMAN to get information

RMAN> list backup;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
69 Full 163.55M DISK 00:01:06 24-SEP-20
BP Key: 69 Status: AVAILABLE Compressed: YES Tag: TAG20200924T173928
Piece Name: /u01/app/Backup_from_Source/ORCL12C/49BFE9E2D73E2038E0530100007F846C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprths4n_.bkp
List of Datafiles in backup set 69
Container ID: 2, PDB Name: PDB$SEED
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
5 Full 1443131 02-MAR-17 NO /u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf
6 Full 1443131 02-MAR-17 NO /u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf
8 Full 1443131 02-MAR-17 NO /u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
70 Full 503.88M DISK 00:01:46 24-SEP-20
BP Key: 70 Status: AVAILABLE Compressed: YES Tag: TAG20200924T173928
Piece Name: /u01/app/Backup_from_Source/ORCL12C/49BFF8A6BB912582E0530100007F8BE4/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtbjth_.bkp
List of Datafiles in backup set 70
Container ID: 3, PDB Name: ORCL
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
9 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
10 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
11 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf
12 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/users01.dbf
13 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf
14 Full 3285704 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
71 Full 18.22M DISK 00:00:01 24-SEP-20
BP Key: 71 Status: AVAILABLE Compressed: NO Tag: TAG20200924T174150
Piece Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983710_hprtgzm1_.bkp
SPFILE Included: Modification time: 24-SEP-20
SPFILE db_unique_name: ORCL12C
Control File Included: Ckp SCN: 3286161 Ckp time: 24-SEP-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
72 Full 18.22M DISK 00:00:00 24-SEP-20
BP Key: 72 Status: AVAILABLE Compressed: NO Tag: TAG20200924T174333
Piece Name: /u01/app/Backup_from_Source/ORCL12C/autobackup/2020_09_24/o1_mf_s_1051983813_hprtl5pc_.bkp
SPFILE Included: Modification time: 24-SEP-20
SPFILE db_unique_name: ORCL12C
Control File Included: Ckp SCN: 3286305 Ckp time: 24-SEP-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
73 Full 161.83M DISK 00:00:19 24-SEP-20
BP Key: 73 Status: AVAILABLE Compressed: YES Tag: TAG20200924T173928
Piece Name: /u01/app/Backup_from_Source/ORCL12C/A84987FDF4C51164E0530100007FEB9C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprth013_.bkp
List of Datafiles in backup set 73
Container ID: 4, PDB Name: NEWPDB
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
41 Full 3286164 24-SEP-20 NO /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_system_hgnbd696_.dbf
42 Full 3286164 24-SEP-20 NO /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_sysaux_hgnbd6c1_.dbf
43 Full 3286164 24-SEP-20 NO /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_undotbs1_hgnbd6c2_.dbf
44 Full 3286164 24-SEP-20 NO /u01/app/oracle/oradata/ORCL12C/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_my_user__hgnbjwg7_.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
74 23.55M DISK 00:00:03 24-SEP-20
BP Key: 74 Status: AVAILABLE Compressed: YES Tag: TAG20200924T174142
Piece Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_annnn_TAG20200924T174142_hprtgqn6_.bkp

List of Archived Logs in backup set 74
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 73 3030419 17-JUN-20 3033316 17-JUN-20
1 74 3033316 17-JUN-20 3033319 17-JUN-20
1 75 3033319 17-JUN-20 3033326 17-JUN-20
1 76 3033326 17-JUN-20 3033329 17-JUN-20
1 77 3033329 17-JUN-20 3033340 17-JUN-20
1 78 3033340 17-JUN-20 3033343 17-JUN-20
1 79 3033343 17-JUN-20 3033358 17-JUN-20
1 80 3033358 17-JUN-20 3035646 17-JUN-20
1 81 3035646 17-JUN-20 3035675 17-JUN-20
1 82 3035675 17-JUN-20 3036658 17-JUN-20
1 83 3036658 17-JUN-20 3038913 09-JUL-20
1 84 3038913 09-JUL-20 3057240 13-JUL-20
1 85 3057240 13-JUL-20 3163574 23-SEP-20
1 86 3163574 23-SEP-20 3165215 23-SEP-20
1 87 3165215 23-SEP-20 3165221 23-SEP-20
1 88 3165221 23-SEP-20 3165687 23-SEP-20
1 89 3165687 23-SEP-20 3165755 23-SEP-20
1 90 3165755 23-SEP-20 3165858 23-SEP-20
1 91 3165858 23-SEP-20 3167178 23-SEP-20
1 92 3167178 23-SEP-20 3168603 23-SEP-20
1 93 3168603 23-SEP-20 3284332 24-SEP-20
1 94 3284332 24-SEP-20 3285739 24-SEP-20
1 95 3285739 24-SEP-20 3285960 24-SEP-20
1 96 3285960 24-SEP-20 3286131 24-SEP-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
75 Full 327.08M DISK 00:00:31 24-SEP-20
BP Key: 75 Status: AVAILABLE Compressed: YES Tag: TAG20200924T173928
Piece Name: /u01/app/Backup_from_Source/ORCL12C/backupset/2020_09_24/o1_mf_nnndf_TAG20200924T173928_hprtfwxl_.bkp
List of Datafiles in backup set 75
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 3286067 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/system01.dbf
3 Full 3286067 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/sysaux01.dbf
7 Full 3286067 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/users01.dbf
15 Full 3286067 24-SEP-20 NO /u01/app/oracle/oradata/orcl12c/undotbs2.dbf

RMAN>


From "eyeballing" the output, I can see that :
a. the highest Checkpoint SCN for datafiles is 3286164 (for PDB "NEWPDB")
but
b. the highest ArchiveLog SCN is 3286130 (3286131-1) from Sequence#96.
Quite obviously, I do not have enough Redo Information in the ArchiveLogs to be able to RECOVER to a consistent SCN.
Of course, the RMAN LIST BACKUP listing is quite short here.  What if it was very long  ?  How would I "query" ?  Using SQL, of course.

$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 25 23:18:31 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter session set nls_date_format='DD-MON-RR HH24:MI';

Session altered.

SQL>
SQL> select df.con_id, max(df.checkpoint_change#)
2 from v$backup_datafile df, v$database d
3 where df.resetlogs_change#=d.resetlogs_change#
4 and df.con_id > 0
5 group by df.con_id
6 /

CON_ID MAX(DF.CHECKPOINT_CHANGE#)
---------- --------------------------
1 3286305
2 1443131
3 3285704
4 3286164

SQL>
SQL> select df.con_id, max(df.checkpoint_time)
2 from v$backup_datafile df, v$database d
3 where df.resetlogs_change#=d.resetlogs_change#
4 and df.con_id > 0
5 group by df.con_id
6 /

CON_ID MAX(DF.CHECKPOI
---------- ---------------
1 24-SEP-20 17:43
2 02-MAR-17 07:57
3 24-SEP-20 17:39
4 24-SEP-20 17:41

SQL>

SQL> select arc.thread#, max(arc.next_change#)-1
2 from v$backup_archivelog_details arc, v$database d
3 where arc.resetlogs_change#=d.resetlogs_change#
4 group by arc.thread#
5 /

THREAD# MAX(ARC.NEXT_CHANGE#)-1
---------- -----------------------
1 3286130

SQL>
SQL> select arc.thread#, max(arc.next_time)-1/1440
2 from v$backup_archivelog_details arc, v$database d
3 where arc.resetlogs_change#=d.resetlogs_change#
4 group by arc.thread#
5 /

THREAD# MAX(ARC.NEXT_TI
---------- ---------------
1 24-SEP-20 17:40

SQL>
SQL> select arc.thread#, max(arc.sequence#)
2 from v$backup_archivelog_details arc, v$database d
3 where arc.resetlogs_change#=d.resetlogs_change#
4 group by arc.thread#
5 /

THREAD# MAX(ARC.SEQUENCE#)
---------- ------------------
1 96

SQL>



The information I get is that CON_ID=4 (which is NEWPDB) has at least one datafile at a higher Checkpoint SCN and Time then the last ArchiveLog in the backup.
Therefore, I would not be able to do an OPEN RESETLOGS because Oracle will expect some more Redo to be applied (from at least Sequence #97).

Why do I query for CON_ID > 0 ?  Because CON_ID=0 is for the CDB, not the actual Root (which is CON_ID=1)

Why I do filter for RESETLOGS_CHANGE#?  Because I want to query for the current Incarnation of the database, as reflected in the Controlfile.  


Categories: DBA Blogs

Is SQL (Relational ?) difficult : A lesson from NoSQL

Tue, 2020-09-22 01:38

 An excellent article by Franck Pachot : 

A lesson from NoSQL (vs. RDBMS): listen to your users


Categories: DBA Blogs

Checking the new PDB as a no-data-clone

Fri, 2020-09-11 22:56

 In the previous post, I had created a NEWDB as a "NO DATA" clone (even also explicitly excluding all USER_TABLESPACES).

FYI, these are the entries in the Primary database alert log (entries in the Standby are shown in the previous post) :

2020-09-07T23:35:32.840917+08:00
create pluggable database newpdb
from orclpdb1
storage (maxsize 10G)
service_name_convert=('ORCLPDB1','NEWPDB')
user_tablespaces=NONE
standbys=ALL
no data
2020-09-07T23:35:35.215231+08:00
ORCLPDB1(3): MDSYS.SDO_COORD_OP_PARAM_VALS (PARAM_VALUE_FILE) - CLOB populated
2020-09-07T23:36:04.284729+08:00
NEWPDB(4):Endian type of dictionary set to little
****************************************************************
Pluggable Database NEWPDB with pdb id - 4 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
local undo-1, localundoscn-0x0000000000000118
****************************************************************
NEWPDB(4):Autotune of undo retention is turned on.
NEWPDB(4):Undo initialization recovery: err:0 start: 786885 end: 786904 diff: 19 ms (0.0 seconds)
NEWPDB(4):[2298] Successfully onlined Undo Tablespace 2.
NEWPDB(4):Undo initialization online undo segments: err:0 start: 786905 end: 786994 diff: 89 ms (0.1 seconds)
NEWPDB(4):Undo initialization finished serial:0 start:786885 end:786996 diff:111 ms (0.1 seconds)
NEWPDB(4):Database Characterset for NEWPDB is AL32UTF8
NEWPDB(4):JIT: pid 2298 requesting stop
2020-09-07T23:36:05.350701+08:00
NEWPDB(4):Buffer Cache flush started: 4
NEWPDB(4):Buffer Cache flush finished: 4
Completed: create pluggable database newpdb
from orclpdb1
storage (maxsize 10G)
service_name_convert=('ORCLPDB1','NEWPDB')
user_tablespaces=NONE
standbys=ALL
no data
2020-09-07T23:40:15.148214+08:00
alter pluggable database orclpdb1 close
2020-09-07T23:40:15.165239+08:00
ORCLPDB1(3):JIT: pid 2298 requesting stop
ORCLPDB1(3):Buffer Cache flush started: 3
ORCLPDB1(3):Buffer Cache flush finished: 3
Pluggable database ORCLPDB1 closed
Completed: alter pluggable database orclpdb1 close
2020-09-07T23:40:22.786209+08:00
alter pluggable database orclpdb1 open read write
ORCLPDB1(3):Autotune of undo retention is turned on.
2020-09-07T23:40:23.211240+08:00
ORCLPDB1(3):Endian type of dictionary set to little
ORCLPDB1(3):Undo initialization recovery: err:0 start: 1045210 end: 1045246 diff: 36 ms (0.0 seconds)
ORCLPDB1(3):[2298] Successfully onlined Undo Tablespace 2.
ORCLPDB1(3):Undo initialization online undo segments: err:0 start: 1045246 end: 1045562 diff: 316 ms (0.3 seconds)
ORCLPDB1(3):Undo initialization finished serial:0 start:1045210 end:1045596 diff:386 ms (0.4 seconds)
2020-09-07T23:40:23.793691+08:00
ORCLPDB1(3):Database Characterset for ORCLPDB1 is AL32UTF8
2020-09-07T23:40:26.180462+08:00
ORCLPDB1(3):Opening pdb with no Resource Manager plan active
ORCLPDB1(3):joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 2298 cid 3
Pluggable database ORCLPDB1 opened read write
Completed: alter pluggable database orclpdb1 open read write
2020-09-07T23:40:34.702216+08:00
alter pluggable database newpdb close
ORA-65020 signalled during: alter pluggable database newpdb close...
2020-09-07T23:40:44.050260+08:00
alter pluggable database newpdb open read write
NEWPDB(4):Autotune of undo retention is turned on.
2020-09-07T23:40:44.313418+08:00
NEWPDB(4):Endian type of dictionary set to little
NEWPDB(4):Undo initialization recovery: err:0 start: 1066307 end: 1066330 diff: 23 ms (0.0 seconds)
NEWPDB(4):[2298] Successfully onlined Undo Tablespace 2.
NEWPDB(4):Undo initialization online undo segments: err:0 start: 1066330 end: 1066564 diff: 234 ms (0.2 seconds)
NEWPDB(4):Undo initialization finished serial:0 start:1066307 end:1066592 diff:285 ms (0.3 seconds)
NEWPDB(4):Deleting old file#9 from file$
NEWPDB(4):Deleting old file#10 from file$
NEWPDB(4):Deleting old file#11 from file$
NEWPDB(4):Deleting old file#12 from file$
NEWPDB(4):Adding new file#13 to file$(old file#9). fopr-1, newblks-35840, oldblks-19200
NEWPDB(4):Adding new file#14 to file$(old file#10). fopr-1, newblks-47360, oldblks-15360
NEWPDB(4):Adding new file#15 to file$(old file#11). fopr-1, newblks-33280, oldblks-12800
NEWPDB(4):Marking tablespace #5 offline since it has been requested to be skipped as part of the USER_TABLESPACES clause. The tablespace cannot be brought online and needs to be dropped and recreated if it needs to brought online.
2020-09-07T23:40:45.094463+08:00
NEWPDB(4):Successfully created internal service NEWPDB at open
****************************************************************
Post plug operations are now complete.
Pluggable database NEWPDB with pdb id - 4 is now marked as NEW.
****************************************************************
2020-09-07T23:40:45.313606+08:00
NEWPDB(4):Pluggable database NEWPDB dictionary check beginning
NEWPDB(4):Pluggable Database NEWPDB Dictionary check complete
NEWPDB(4):Database Characterset for NEWPDB is AL32UTF8
2020-09-07T23:40:50.931146+08:00
NEWPDB(4):Opening pdb with no Resource Manager plan active
NEWPDB(4):joxcsys_required_dirobj_exists: directory object exists with required path /opt/oracle/product/19c/dbhome_1/javavm/admin/, pid 2298 cid 4
Pluggable database NEWPDB opened read write
Completed: alter pluggable database newpdb open read write
2020-09-07T23:44:00.682159+08:00
Control autobackup written to DISK device

handle '/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_09_07/o1_mf_s_1050536639_hodob0c4_.bkp'



But let's now check if NEWPDB has any left-over objects from the Data Dictionary of ORCLPDB1.

SQL> select con_id, name, open_mode
2 from v$pdbs
3 order by 1
4 /

CON_ID NAME OPEN_MODE
---------- ---------------- ----------
2 PDB$SEED READ ONLY
3 ORCLPDB1 READ WRITE
4 NEWPDB MOUNTED

SQL> alter pluggable database newpdb open read write;

Pluggable database altered.

SQL> select con_id, name, open_mode
2 from v$pdbs
3 order by 1
4 /

CON_ID NAME OPEN_MODE
---------- ---------------- ----------
2 PDB$SEED READ ONLY
3 ORCLPDB1 READ WRITE
4 NEWPDB READ WRITE

SQL>
SQL> select con_id, name, creation_time
2 from v$pdbs
3 order by 1
4 /

CON_ID NAME CREATION_
---------- ---------------- ---------
2 PDB$SEED 04-MAY-19
3 ORCLPDB1 04-MAY-19
4 NEWPDB 07-SEP-20

SQL>


First get the list from ORCLPDB1 :
SQL> alter session set container=ORCLPDB1;

Session altered.

SQL>
SQL> select username, created, oracle_maintained
2 from dba_users
3 where username = 'HEMANT'
4 /

USERNAME CREATED O
---------------- --------- -
HEMANT 07-MAY-19 N

SQL>
SQL> l
1 select object_type, trunc(created), status, count(*)
2 from dba_objects
3 where owner = 'HEMANT'
4 group by object_type, trunc(created), status
5* order by 1,2
SQL> /

OBJECT_TYPE TRUNC(CRE STATUS COUNT(*)
-------------------- --------- ------- ----------
INDEX 18-AUG-19 VALID 3
INDEX 16-SEP-19 VALID 1
INDEX 29-SEP-19 VALID 3
INDEX 12-OCT-19 VALID 2
INDEX 19-OCT-19 VALID 2
INDEX 27-OCT-19 VALID 3
INDEX 28-OCT-19 VALID 2
INDEX 12-NOV-19 VALID 4
INDEX 12-JUL-20 VALID 2
JOB 12-OCT-19 VALID 1
MATERIALIZED VIEW 18-AUG-19 VALID 1
MATERIALIZED VIEW 16-SEP-19 VALID 1
MATERIALIZED VIEW 25-SEP-19 VALID 1
MATERIALIZED VIEW 29-SEP-19 VALID 2
MATERIALIZED VIEW 12-OCT-19 VALID 2
MATERIALIZED VIEW 27-OCT-19 VALID 2
MATERIALIZED VIEW 12-NOV-19 VALID 1
TABLE 07-MAY-19 VALID 1
TABLE 18-AUG-19 VALID 4
TABLE 16-SEP-19 VALID 1
TABLE 25-SEP-19 VALID 1
TABLE 29-SEP-19 VALID 3
TABLE 12-OCT-19 VALID 2
TABLE 19-OCT-19 VALID 3
TABLE 27-OCT-19 VALID 4
TABLE 28-OCT-19 VALID 3
TABLE 12-NOV-19 VALID 4
TABLE 26-MAR-20 VALID 1
TABLE 20-MAY-20 VALID 1
TABLE 12-JUL-20 VALID 1
TABLE 10-AUG-20 VALID 1
TABLE 12-AUG-20 VALID 2
TABLE 14-AUG-20 VALID 4
TABLE 18-AUG-20 VALID 3
TABLE PARTITION 07-MAY-19 VALID 4

35 rows selected.

SQL>


Now get the list from NEWPDB :

SQL> alter session set container=NEWPDB;

Session altered.

SQL> select username, created, oracle_maintained
2 from dba_users
3 where username = 'HEMANT'
4 /

USERNAME CREATED O
---------------- --------- -
HEMANT 07-MAY-19 N

SQL>
SQL> l
1 select object_type, trunc(created), status, count(*)
2 from dba_objects
3 where owner = 'HEMANT'
4 group by object_type, trunc(created), status
5* order by 1,2
SQL> /

OBJECT_TYPE TRUNC(CRE STATUS COUNT(*)
-------------------- --------- ------- ----------
INDEX 18-AUG-19 VALID 3
INDEX 16-SEP-19 VALID 1
INDEX 29-SEP-19 VALID 3
INDEX 12-OCT-19 VALID 2
INDEX 19-OCT-19 VALID 2
INDEX 27-OCT-19 VALID 3
INDEX 28-OCT-19 VALID 2
INDEX 12-NOV-19 VALID 4
INDEX 12-JUL-20 VALID 2
JOB 12-OCT-19 VALID 1
MATERIALIZED VIEW 18-AUG-19 VALID 1
MATERIALIZED VIEW 16-SEP-19 VALID 1
MATERIALIZED VIEW 25-SEP-19 VALID 1
MATERIALIZED VIEW 29-SEP-19 VALID 2
MATERIALIZED VIEW 12-OCT-19 VALID 2
MATERIALIZED VIEW 27-OCT-19 VALID 2
MATERIALIZED VIEW 12-NOV-19 VALID 1
TABLE 07-MAY-19 VALID 1
TABLE 18-AUG-19 VALID 4
TABLE 16-SEP-19 VALID 1
TABLE 25-SEP-19 VALID 1
TABLE 29-SEP-19 VALID 3
TABLE 12-OCT-19 VALID 2
TABLE 19-OCT-19 VALID 3
TABLE 27-OCT-19 VALID 4
TABLE 28-OCT-19 VALID 3
TABLE 12-NOV-19 VALID 4
TABLE 26-MAR-20 VALID 1
TABLE 20-MAY-20 VALID 1
TABLE 12-JUL-20 VALID 1
TABLE 10-AUG-20 VALID 1
TABLE 12-AUG-20 VALID 2
TABLE 14-AUG-20 VALID 4
TABLE 18-AUG-20 VALID 3
TABLE PARTITION 07-MAY-19 VALID 4

35 rows selected.

SQL>


That's interesting. Let's explicitly connect to each PDB and verify. 
 ORCLPDB1 first

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> select table_name
2 from user_tables
3 order by 1
4 /

TABLE_NAME
------------------------------
EMPLOYEES
HEMANT_SOURCE_TBL
MLOG$_HEMANT_SOURCE_TBL
MLOG$_MY_LARGE_SOURCE
MLOG$_NEW_SOURCE_TABLE
MLOG$_SOURCE_TABLE
MLOG$_SOURCE_TABLE_1
MV_1
MV_2
MV_3
MV_DEPT
MV_EMP
MV_FAST_NOT_POSSIBLE
MV_OF_SOURCE
MY_LARGE_SOURCE
MY_TARGET
MY_TRANSACTIONS
NEW_MV
NEW_MV_2_1
NEW_MV_2_2
NEW_SOURCE_TABLE
NOROWDEP
ROWDEPENDENCIES
RUPD$_HEMANT_SOURCE_TBL
RUPD$_MY_LARGE_SOURCE
RUPD$_SOURCE_TABLE
RUPD$_SOURCE_TABLE_1
SOURCE_TABLE
SOURCE_TABLE_1
SOURCE_TABLE_2
TABLE_A
TABLE_B
T_1
T_2
T_3
T_4
WIDGETS_LIST
X
XYZ

39 rows selected.

SQL>
SQL> select count(*) from employees
2 /

COUNT(*)
----------
10000

SQL> select count(*) from my_target
2 /

COUNT(*)
----------
997

SQL>


Now, NEWPDB :

SQL> connect hemant/hemant@newpdb
Connected.
SQL> select table_name
2 from user_tables
3 order by 1
4 /

TABLE_NAME
------------------------------
EMPLOYEES
HEMANT_SOURCE_TBL
MLOG$_HEMANT_SOURCE_TBL
MLOG$_MY_LARGE_SOURCE
MLOG$_NEW_SOURCE_TABLE
MLOG$_SOURCE_TABLE
MLOG$_SOURCE_TABLE_1
MV_1
MV_2
MV_3
MV_DEPT
MV_EMP
MV_FAST_NOT_POSSIBLE
MV_OF_SOURCE
MY_LARGE_SOURCE
MY_TARGET
MY_TRANSACTIONS
NEW_MV
NEW_MV_2_1
NEW_MV_2_2
NEW_SOURCE_TABLE
NOROWDEP
ROWDEPENDENCIES
RUPD$_HEMANT_SOURCE_TBL
RUPD$_MY_LARGE_SOURCE
RUPD$_SOURCE_TABLE
RUPD$_SOURCE_TABLE_1
SOURCE_TABLE
SOURCE_TABLE_1
SOURCE_TABLE_2
TABLE_A
TABLE_B
T_1
T_2
T_3
T_4
WIDGETS_LIST
X
XYZ

39 rows selected.

SQL>
SQL> select count(*) from employees;

COUNT(*)
----------
0

SQL> select count(*) from my_target;

COUNT(*)
----------
0

SQL>


So, NEWPDB has the object definitions but really has no data.


Categories: DBA Blogs

Creating a PDB as a Clone in a DataGuard environment

Mon, 2020-09-07 10:55

 In the 19c Primary database, where I want to create NEWPDB as a no-data clone of ORCLPDB1 :



SQL> select con_id, name, open_mode from v$pdbs;

CON_ID
----------
NAME
--------------------------------------------------------------------------------------------------------------------------------
OPEN_MODE
----------
2
PDB$SEED
READ ONLY

3
ORCLPDB1
READ WRITE


SQL> alter pluggable database ORCLPDB1 close;

Pluggable database altered.

SQL> alter pluggable database ORCLPDB1 open read only;

Pluggable database altered.

SQL> show parameter db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /opt/oracle/oradata
SQL>
SQL> create pluggable database newpdb
2 from orclpdb1
3 storage (maxsize 10G)
4 service_name_convert=('ORCLPDB1','NEWPDB')
5 user_tablespaces=NONE
6 standbys=ALL
7 no data
8 /

Pluggable database created.

SQL>
SQL> select name
2 from v$datafile
3 where con_id =
4 (select con_id
5 from v$pdbs
6 where name = 'NEWPDB')
7 order by file#
8 /

NAME
------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_system_hodnt79n_.dbf
/opt/oracle/oradata/ORCLCDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_sysaux_hodnt7bk_.dbf
/opt/oracle/oradata/ORCLCDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_undotbs1_hodnt7bm_.dbf

SQL>
SQL> select name
2 from v$datafile
3 where con_id =
4 (select con_id
5 from v$pdbs
6 where name = 'ORCLPDB1')
7 order by file#
8 /

NAME
------------------------------------------------------------------------------------------------------------------------------------
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf

SQL>


Notice how the NEWPDB does NOT have a USERS datafile as I specified "user_tablespaces=NONE".
Also note that while the ORCLPDB1 had a different naming format, NEWPDB relies on what I have specified as "db_create_file_dest" to create Oracle Managed Files as datafiles  (incuding the Undo Tablespace datafile)
Also, the "no data" ensures that no user data is copied, only the database template(data dictionary) is copied. User-created tables and indexes are excluded.  (should we test to see which types of user-created objects are included/excluded ?)

SQL> alter pluggable database orclpdb1 close;

Pluggable database altered.

SQL> alter pluggable database orclpdb1 open read write;

Pluggable database altered.

SQL> alter pluggable database newpdb close;
alter pluggable database newpdb close
*
ERROR at line 1:
ORA-65020: pluggable database NEWPDB already closed


SQL> alter pluggable database newpdb open read write;

Pluggable database altered.

SQL>


The source PDB had to be Read Only but the cloned NEWDB is not OPEN when created.

SQL> select service_id, name, network_name, creation_date, pdb, con_id
2 from v$services
3 order by 1
4 /

SERVICE_ID NAME NETWORK_NAME CREATION_ PDB CON_ID
---------- ---------------------------------------------------------------- ---------------- --------- -------- ----------
1 SYS$BACKGROUND 17-APR-19 CDB$ROOT 1
2 SYS$USERS 17-APR-19 CDB$ROOT 1
5 ORCLCDBXDB ORCLCDBXDB 04-MAY-19 CDB$ROOT 1
6 ORCLCDB ORCLCDB 04-MAY-19 CDB$ROOT 1
8 orclpdb1 orclpdb1 04-MAY-19 ORCLPDB1 3
10 newpdb newpdb 07-SEP-20 NEWPDB 4

6 rows selected.

SQL>
SQL> select service_id, name, con_id
2 from v$active_services
3 order by service_id
4 /

SERVICE_ID NAME CON_ID
---------- ---------------------------------------------------------------- ----------
1 SYS$BACKGROUND 1
2 SYS$USERS 1
5 ORCLCDBXDB 1
6 ORCLCDB 1
8 orclpdb1 3
10 newpdb 4

6 rows selected.

SQL>


I have a Service called "newpdb" created for the new PDB.

How and when does the PDB propagate to the Standby ?

After I issue 

SQL> alter system archive log current;

System altered.

SQL>


The Standby alert log shows :

2020-09-07T23:46:09.815590+08:00
Recovery created pluggable database NEWPDB
2020-09-07T23:46:16.818755+08:00
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file /opt/oracle/oradata/STDBYDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_system_hodnt79n_.dbf from /opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf
NEWPDB(4):Successfully added datafile 13 to media recovery
NEWPDB(4):Datafile #13: '/opt/oracle/oradata/STDBYDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_system_hodnt79n_.dbf'
2020-09-07T23:46:27.355523+08:00
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file /opt/oracle/oradata/STDBYDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_sysaux_hodnt7bk_.dbf from /opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf
NEWPDB(4):Successfully added datafile 14 to media recovery
NEWPDB(4):Datafile #14: '/opt/oracle/oradata/STDBYDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_sysaux_hodnt7bk_.dbf'
2020-09-07T23:46:35.160960+08:00
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file /opt/oracle/oradata/STDBYDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_undotbs1_hodnt7bm_.dbf from /opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf
NEWPDB(4):Successfully added datafile 15 to media recovery
NEWPDB(4):Datafile #15: '/opt/oracle/oradata/STDBYDB/AEBBE124BDA608FAE0530100007F00D7/datafile/o1_mf_undotbs1_hodnt7bm_.dbf'
2020-09-07T23:46:37.523453+08:00
PR00 (PID:2275): Media Recovery Waiting for T-1.S-80 (in transit)
2020-09-07T23:46:37.557413+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 80 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log


The datafiles at the Standby are also created as as Oracle Managed Files, relying on "db_create_file_dest".  But the alert log messages clearly show that it did a *local* copy of the datafiles from ORCLPDB1  instead of copying the new datafiles across the network.



Categories: DBA Blogs

Using SQL Developer to Copy Data to the Oracle Cloud

Thu, 2020-08-27 01:41

 

I have created a short video demonstrating a copy of selected objects from a 19c database accessible from my desktop to an Autonomous (ATP) Database in the Oracle Cloud using SQL Developer 20.2






Categories: DBA Blogs

ORA_ROWSCN and RowDependencies

Tue, 2020-08-18 09:58

 The last three posts in this blog have been on SCNs and the ORA_ROWSCN function with brief mentions about the RowDependencies extended attribute of a table defnition.

Here is a quick demonstration of how RowDependencies works.

I setup two tables, without and with RowDepedencies, with 5 rows each (ensuring that all 5 fit into 1 datablock) and read the SCN values returned by ORA_ROWSCN 

This is the first table where I don't specify RowDependencies :


22:39:35 SQL> create table norowdep (id number, inserted_scn number, inserted_timestamp timestamp);

Table created.

22:40:09 SQL> insert into nororwdep
22:40:21 2 select 1, current_scn, systimestamp from v$database;
insert into nororwdep
*
ERROR at line 1:
ORA-00942: table or view does not exist


22:40:37 SQL> insert into norowdep
22:40:45 2 select 1, current_scn, systimestamp from v$database;

1 row created.

22:40:52 SQL> commit;

Commit complete.

22:40:54 SQL> !sleep 5

22:41:02 SQL>
22:41:09 SQL> insert into norowdep
22:41:10 2 select 2, current_scn, systimestamp from v$database;

1 row created.

22:41:19 SQL> commit;

Commit complete.

22:41:22 SQL> !sleep 5

22:41:30 SQL> insert into norowdep
22:41:34 2 select 3, current_scn, systimestamp from v$database;

1 row created.

22:41:42 SQL> commit;

Commit complete.

22:41:47 SQL> !sleep 5

22:41:54 SQL> insert into norowdep
22:41:56 2 select 4, current_scn, systimestamp from v$database;

1 row created.

22:42:05 SQL> commit;

Commit complete.

22:42:10 SQL> !sleep 5

22:42:18 SQL> insert into norowdep
22:42:19 2 select 5, current_scn, systimestamp from v$database;

1 row created.

22:42:29 SQL> commit;

Commit complete.

22:42:31 SQL> !sleep 5

22:42:41 SQL> select id, inserted_scn, inserted_timestamp, ora_rowscn
22:43:09 2 from norowdep
22:43:16 3 order by 1
22:43:19 4 /

ID INSERTED_SCN INSERTED_TIMESTAMP ORA_ROWSCN
---------- ------------ --------------------------------------------------------------------------- ----------
1 6780419 18-AUG-20 10.40.52.802908 PM 6785773
2 6782540 18-AUG-20 10.41.19.887569 PM 6785773
3 6783619 18-AUG-20 10.41.42.647586 PM 6785773
4 6784694 18-AUG-20 10.42.05.374764 PM 6785773
5 6785769 18-AUG-20 10.42.29.422846 PM 6785773

22:43:20 SQL>


Here Oracle returns the same SCN value  as ORA_ROWSCN for all 5 rows -- it has read this from the Block, instead of reading for each row.

This is the second table with RowDependencies :


22:47:31 SQL> create table rowdependencies (id number, inserted_scn number, inserted_timestamp timestamp) rowdependencies;

Table created.

22:47:56 SQL>
22:48:07 SQL> insert into rowdependencies
22:48:13 2 select 1, current_scn, systimestamp from v$database;

1 row created.

22:48:17 SQL> commit;

Commit complete.

22:48:19 SQL> !sleep 5
insert into rowdependencies
22:48:27 SQL>
22:48:30 2
22:48:31 SQL> insert into rowdependencies
22:48:33 2 select 2, current_scn, systimestamp from v$database;

1 row created.

22:48:42 SQL> commit;

Commit complete.

22:48:43 SQL> !sleep 5

22:48:51 SQL> insert into rowdependencies
22:48:53 2 select 3,, current_scn, systimestamp from v$database;
select 3,, current_scn, systimestamp from v$database
*
ERROR at line 2:
ORA-00936: missing expression


22:49:00 SQL> insert into rowdependencies
22:49:06 2 select 3, current_scn, systimestamp from v$database;

1 row created.

22:49:12 SQL> commit;

Commit complete.

22:49:14 SQL> !sleep 5

22:49:21 SQL> insert into rowdependencies
22:49:22 2 select 4, current_scn, systimestamp from v$database;

1 row created.

22:49:35 SQL> commit;

Commit complete.

22:49:37 SQL> insert into rowdependencies
22:49:47 2 select 5, current_scn, systimestamp from v$database;

1 row created.

22:49:56 SQL> commit;

Commit complete.

22:49:57 SQL> !sleep 5

22:50:06 SQL> select id, inserted_scn, inserted_timestamp, ora_rowscn
22:50:17 2 from rowdependencies
22:50:23 3 order by id
22:50:26 4 /

ID INSERTED_SCN INSERTED_TIMESTAMP ORA_ROWSCN
---------- ------------ --------------------------------------------------------------------------- ----------
1 6804210 18-AUG-20 10.48.17.485841 PM 6804228
2 6805303 18-AUG-20 10.48.42.081454 PM 6805308
3 6806387 18-AUG-20 10.49.12.242874 PM 6806394
4 6807475 18-AUG-20 10.49.35.500547 PM 6807478
5 6808541 18-AUG-20 10.49.56.098645 PM 6808546

22:50:26 SQL>


For this table, eachrow has a different ORA_ROWSCN, although it is not the actual database SCN at the time of the INSERT.  (I have already explained the reason for this in my previous posts here and here).

ORA_ROWSCN does *not* return the actual SCN of the database as of the time of the INSERT or UPDATE DML  but only as of the time of the COMMIT.  However, if you don't specify RowDependencies at the table level, it will return the block level SCN.

Note further :  Caveat : ORA_ROWSCN does not necessarily return the *exact* SCN as of the time of the COMMIT. Because of the complexities of the SCN, the actual value returned might still be slightly different.

Categories: DBA Blogs

ORA_ROWSCN -- 2 : Multi Table, Multiple Rows

Fri, 2020-08-14 09:58

 Instead of the single-table, single-row demonstration in my previous blog post, this demonstrates how ORA_ROWSCN is presented when a single transaction (i.e. one COMMIT at the end of multiple DML statements) presents the same SCN for all the rows in all the tables that were involved.



oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 14 22:39:25 2020
Version 19.3.0.0.0

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

Last Successful login time: Fri Aug 14 2020 22:39:20 +08:00

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

22:39:25 SQL> create table t_1 (txn_scn number) rowdependencies;

Table created.

22:39:48 SQL> create table t_2(txn_scn number) rowdependencies;

Table created.

22:40:00 SQL> create table t_3 (txn_scn number) rowdependencies;

Table created.

22:40:07 SQL> create table t_4 (txn_scn number) rowdependencies;

Table created.

22:40:12 SQL>
22:40:12 SQL> insert into t_1
22:40:27 2 select current_scn
22:40:31 3 from v$database, dual
22:40:36 4 connect by level < 11
22:40:40 5 /

10 rows created.

22:40:40 SQL> insert into t_2
22:40:43 2 select current_scn
22:40:50 3 from v$database
22:40:53 4 /

1 row created.

22:40:53 SQL> !sleep 10

22:41:06 SQL> insert into t_3
22:41:14 2 select current_scn
22:41:18 3 from v$database, dual
22:41:22 4 connect by level < 6
22:41:28 5 /

5 rows created.

22:41:29 SQL> !sleep 30

22:42:01 SQL> select distinct (current_scn)
22:42:12 2
22:42:21 SQL>
22:42:21 SQL> select distinct (txn_scn)
22:42:25 2 from t_1
22:42:28 3 /

TXN_SCN
----------
6664390

22:42:28 SQL> select distinct (txn_scn)
22:42:36 2 from t_2
22:42:38 3 /

TXN_SCN
----------
6664419

22:42:40 SQL> select distinct (txn_scn)
22:42:44 2 from t_3
22:42:46 3 /

TXN_SCN
----------
6665530

22:42:46 SQL> insert into t_4
22:42:51 2 select * from t_1
22:42:53 3 /

10 rows created.

22:42:54 SQL> select distinct (txn_scn)
22:42:58 2 from t_4
22:43:00 3 /

TXN_SCN
----------
6664390

22:43:01 SQL> !sleep 60

22:44:05 SQL> update t_2
22:44:21 2 set txn_scn = (select current_scn from v$database)
22:44:36 3 /

1 row updated.

22:44:37 SQL> select txn_scn
22:44:42 2 from t_2
22:44:45 3 /

TXN_SCN
----------
6672139

22:44:46 SQL>
22:44:46 SQL>
22:45:07 SQL> select distinct(txn_scn) from t_1;

TXN_SCN
----------
6664390

22:45:27 SQL> select distinct(txn_scn) from t_2;

TXN_SCN
----------
6672139

22:45:33 SQL> select distinct(txn_scn) from t_3;

TXN_SCN
----------
6665530

22:45:41 SQL> select distinct(txn_scn) from t_4;

TXN_SCN
----------
6664390

22:45:45 SQL> insert into t_1
22:45:54 2 select current_scn from v$database;

1 row created.

22:46:04 SQL> update t_1
22:46:07 2 where txn_Scn != 6664390
22:46:16 3
22:46:20 SQL>
22:46:27 SQL> update t_1
22:46:30 2 st txn_scn = (select current_scn from v$database)
22:46:40 3 where txn_scn != 6664390
22:46:51 4 /
st txn_scn = (select current_scn from v$database)
*
ERROR at line 2:
ORA-00971: missing SET keyword


22:46:52 SQL> update t_1
22:46:58 2 set txn_scn = (select current_scn from v$database)
22:47:03 3 where txn_scn != 6664390
22:47:09 4 /

1 row updated.

22:47:10 SQL> select txn_scn, count(*)
22:47:16 2 from t_1
22:47:18 3 group by txn_scn
22:47:22 4 /

TXN_SCN COUNT(*)
---------- ----------
6683784 1
6664390 10

22:47:23 SQL> !sleep 15
22:47:42 SQL>
22:47:54 SQL> commit;

Commit complete.

22:47:59 SQL>


The CURRENT_SCN that is inserted into each of the 4 tables is different (I had multiple other transactions running from other sessions to forcefully increment the SCNs).

What ORA_ROWSCN values do we see after the COMMIT ?


22:48:57 SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 14 22:49:05 2020
Version 19.3.0.0.0

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

Last Successful login time: Fri Aug 14 2020 22:48:57 +08:00

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

22:49:05 SQL> select txn_scn, ora_rowscn
22:49:27 2 from t_1
22:49:29 3 /

TXN_SCN ORA_ROWSCN
---------- ----------
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6683784 6686983

11 rows selected.

22:49:30 SQL> select txn_scn, ora_rowscn
22:49:40 2 from t_1
22:49:44 3 order by 1,2
22:49:48 4 /

TXN_SCN ORA_ROWSCN
---------- ----------
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6683784 6686983

11 rows selected.

22:49:48 SQL>
22:49:48 SQL> select txn_scn, ora_rowscn
22:50:09 2 from t_2
22:50:11 3 order by 1,2
22:50:13 4 /

TXN_SCN ORA_ROWSCN
---------- ----------
6672139 6686983

22:50:14 SQL> select txn_scn, ora_rowscn
22:50:19 2 from t_3
22:50:21 3 order by 1,2
22:50:22 4 /

TXN_SCN ORA_ROWSCN
---------- ----------
6665530 6686983
6665530 6686983
6665530 6686983
6665530 6686983
6665530 6686983

22:50:23 SQL> select txn_scn, ora_rowscn
22:50:29 2 from t_4
22:50:32 3 order by 1,
22:50:34 4 2
22:50:36 5
22:50:37 SQL> /

TXN_SCN ORA_ROWSCN
---------- ----------
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983
6664390 6686983

10 rows selected.

22:50:37 SQL>


Every single row in all 4 tables has the same ORA_ROWSCN that was set when the COMMIT was issued.  So, for any DML which spans more than 1 row and/or more than 1 table, *all* the rows affected will have the same ORA_ROWSCN, irrespective of the actual CURRENT_SCN at the beginning of the first DML statement and the CURRENT_SCN at the end of the last DML statement.


However, remember that DDL statement (CREATE, ALTER, DROP etc) cause a COMMIT to be issued immediately.  So, if I had a DDL statement in the middle of the session, the ORA_ROWSCN for rows impacted after the DDL would be different from those for rows impacted before the DDL.
Note, however, if the client program had set AUTOCOMMIT ON, every statement would result in a COMMIT and fresh ORA_ROWSCN.  So, the ORA_ROWSCN values for these same SQL operations could have varied if I were using a client program (.e.g a JDBC connection) that had AUTOCOMMIT ON.  Also, session exit/disconnect/reconnect may issue a COMMIT depending on whether the client program issues a COMMIT on closing the session.

What other operations can you think that would implicit commits, resulting in varying ORA_ROWSCNs ?
Categories: DBA Blogs

ORA_ROWSCN

Wed, 2020-08-12 05:28

 As a follow up to my previous post on SCN_TO_TIMESTAMP, here is a demo of the ORA_ROWSCN function.

I have two different sessions and two different tables where I insert one row each.  I then delay the COMMIT in each session.


This is the first session :

oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 12 18:02:47 2020
Version 19.3.0.0.0

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

Last Successful login time: Wed Aug 12 2020 18:02:31 +08:00

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

18:02:47 SQL> create table table_a
18:02:51 2 (table_name varchar2(32), insert_scn number, insert_timestamp timestamp);

Table created.

18:03:10 SQL> insert into table_a
18:03:14 2 select 'TABLE_A', current_scn, systimestamp from v$database
18:03:32 3 /

1 row created.

18:03:33 SQL> select * from table_a
18:03:37 2 /

TABLE_NAME INSERT_SCN INSERT_TIMESTAMP
-------------------------------- ---------- ---------------------------------------------------------------------------
TABLE_A 6580147 12-AUG-20 06.03.33.263180 PM

18:03:38 SQL>
18:05:16 SQL> !sleep 120

18:07:21 SQL>
18:07:26 SQL> commit;

Commit complete.

18:07:28 SQL>


And this is the second session :

oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 12 18:04:27 2020
Version 19.3.0.0.0

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

Last Successful login time: Wed Aug 12 2020 18:03:32 +08:00

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

18:04:27 SQL> create table table_b
18:04:36 2 (table_name varchar2(32), insert_scn number, insert_timestamp timestamp);

Table created.

18:04:46 SQL> insert into table_b
18:04:51 2 select 'TABLE_B',current_scn, systimestamp from v$database;

1 row created.

18:05:03 SQL> select * from table_b
18:05:09 2 /

TABLE_NAME INSERT_SCN INSERT_TIMESTAMP
-------------------------------- ---------- ---------------------------------------------------------------------------
TABLE_B 6581390 12-AUG-20 06.05.03.813011 PM

18:05:10 SQL>
18:05:24 SQL> !sleep 30

18:06:00 SQL>
18:06:07 SQL>
18:06:13 SQL> commit;

Commit complete.

18:06:16 SQL>


So, the second session, against TABLE_B did the INSERT after the first session but issued a COMMIT before the first session.  (TABLE_B has a higher INSERT_SCN and INSERT_TIMESTAMP than TABLE_A).

Let's see what ORA_ROWSCN shows :
< br />
SQL> select table_name, insert_scn, insert_timestamp, scn_to_timestamp(ora_rowscn)
2 from table_a
3 /

TABLE_NAME INSERT_SCN INSERT_TIMESTAMP
-------------------------------- ---------- ---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
TABLE_A 6580147 12-AUG-20 06.03.33.263180 PM
12-AUG-20 06.07.26.000000000 PM


SQL> select table_name, insert_scn, insert_timestamp, scn_to_timestamp(ora_rowscn)
2 from table_b
3 /

TABLE_NAME INSERT_SCN INSERT_TIMESTAMP
-------------------------------- ---------- ---------------------------------------------------------------------------
SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
TABLE_B 6581390 12-AUG-20 06.05.03.813011 PM
12-AUG-20 06.06.14.000000000 PM


SQL>


The actual INSERT into TABLE_B was after that in TABLE_A  (higher INSERT_SCN and INSERT_TIMESTAMP)  but SCN_TO_TIMESTAMP of the ORA_ROWSCN implies that the row in TABLE_B is earlier than that in TABLE_A !

SQL> select table_name, insert_scn, insert_timestamp, ora_rowscn
2 from table_a
3 /

TABLE_NAME INSERT_SCN INSERT_TIMESTAMP ORA_ROWSCN
-------------------------------- ---------- --------------------------------------------------------------------------- ----------
TABLE_A 6580147 12-AUG-20 06.03.33.263180 PM 6586905

SQL> select table_name, insert_scn, insert_timestamp, ora_rowscn
2 from table_b
3 /

TABLE_NAME INSERT_SCN INSERT_TIMESTAMP ORA_ROWSCN
-------------------------------- ---------- --------------------------------------------------------------------------- ----------
TABLE_B 6581390 12-AUG-20 06.05.03.813011 PM 6584680

SQL>


The actual SCN recorded is that of the COMMIT time, *not* the INSERT time.

A database session gets an SCN for the Transaction it does when it COMMITs.
So, even though the INSERT into TABLE_A was earlier, it has a higher SCN simply because the COMMIT was issued later.


Does it matter if I use the ROWDEPENDENCIES extended attribute for the table  ? Without ROWDEPENDENCIES, ORA_ROWSCN actually uses the SCN in the block header -- irrespective of when each row in the block was inserted / updated.
In my scenario, I had a new table with only 1 row, so there would be no difference.

Nevertheless, I repeat the experiment with ROWDEPENDENCIES.


oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 12 18:17:57 2020
Version 19.3.0.0.0

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

Last Successful login time: Wed Aug 12 2020 18:17:47 +08:00

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

18:17:57 SQL> create table table_a
18:18:10 2
18:18:10 SQL> create table table_a
18:18:14 2 (table_name varchar2(32), insert_scn number, insert_timestamp timestamp) rowdependencies;

Table created.

18:18:31 SQL> insert into table_a
18:18:40 2 select 'TABLE_A', current_scn, systimestamp from v$database
18:18:50 3 /

1 row created.

18:18:51 SQL>
18:20:11 SQL> !sleep 60

18:21:13 SQL>
18:21:15 SQL> commit;

Commit complete.

18:21:16 SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>



and


oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Aug 12 18:19:30 2020
Version 19.3.0.0.0

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

Last Successful login time: Wed Aug 12 2020 18:19:04 +08:00

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

18:19:30 SQL> create table table_b
18:19:33 2 (table_name varchar2(32), insert_scn number, insert_timestamp timestamp) rowdependencies;

Table created.

18:19:40 SQL> insert into table_b
18:19:52 2 select 'TABLE_B',current_scn, systimestamp from v$database;

1 row created.

18:20:00 SQL>
18:20:16 SQL> !sleep 30

18:20:49 SQL>
18:20:51 SQL> commit;

Commit complete.

18:20:52 SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>



resulting in :


SQL> select table_name, insert_scn, insert_timestamp, ora_rowscn
2 from table_a
3 /

TABLE_NAME INSERT_SCN INSERT_TIMESTAMP ORA_ROWSCN
-------------------------------- ---------- --------------------------------------------------------------------------- ----------
TABLE_A 6612380 12-AUG-20 06.18.51.562927 PM 6618886

SQL> select table_name, insert_scn, insert_timestamp, ora_rowscn
2 from table_b
3 /

TABLE_NAME INSERT_SCN INSERT_TIMESTAMP ORA_ROWSCN
-------------------------------- ---------- --------------------------------------------------------------------------- ----------
TABLE_B 6614592 12-AUG-20 06.20.00.141122 PM 6617807

SQL>


Bottom line : A row that is inserted (or updated) earlier can still have a higher SCN (and, therefore, show a higher SCN_TO_TIMESTAMP)  simply because the user or the application program issued the COMMIT later.   Even an application or batch job may run multiple queries or DMLs before finally issuing a COMMIT.


Categories: DBA Blogs

SCN_TO_TIMESTAMP

Tue, 2020-08-11 09:23
A quick demo of SCN_TO_TIMESTAMP in 19c

 
oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 11 21:59:56 2020
Version 19.3.0.0.0

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

Last Successful login time: Mon Aug 10 2020 16:08:38 +08:00

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

SQL> select scn_to_timestamp(5389994) from dual;

SCN_TO_TIMESTAMP(5389994)
---------------------------------------------------------------------------
11-AUG-20 09.53.44.000000000 PM

SQL>
SQL> select scn_to_timestamp(5389994-100000) from dual;

SCN_TO_TIMESTAMP(5389994-100000)
---------------------------------------------------------------------------
12-JUL-20 11.19.13.000000000 PM

SQL>
SQL> select scn_to_timestamp(32720) from dual;
select scn_to_timestamp(32720) from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1


SQL>


If you query for an older SCN, you would get an ORA-08181 error.  What is an "older SCN" ?  

Technically, Oracle frequently inserts new rows into SYS.SMON_SCN_TIME and deletes older rows.  This is the table that is queried by the SCN_TO_TIMESTAMP function.  So, if you query for an SCN no longer present in the table, you get an ORA-08181 error.

Does Oracle insert every SCN into this table ? Of course not !  Else there would have been more 5million rows in the table in my database.  It periodically inserts rows.  When you run the SCN_TO_TIMESTAMP function, you get an approximate timestamp  -- an estimate that Oracle derives from reading "nearby" rows.  

Do not ever assume that SCN_TO_TIMETAMP returns an Exact Timestamp for that SCN.

For a range of potential SCNs, you can query V$ARCHIVED_LOG for FIRST_TIME (which is still in DATE format, not TIMESTAMP) and FIRST_CHANGE# (which is the first SCN recorded for that ArchiveLog).


Categories: DBA Blogs

Real Time SQL Monitor using SQL Developer 20.2

Mon, 2020-08-10 03:27
Here are a few screenshots of using the Real Time SQL Monitor in SQL Developer 20.2 against a 19c database.  I use the MONITOR hint explicitly in the SQL statements to force them to be visible in the SQL Monitor.

Note : The "B" after the "18" and "20" for I/O requests in the first two screenshots is *not* "Billion"




This is an INSERT statement

This shows the Execution Plan of the INSERT statement 

Here is  more complicated query with Parallel Execution  (all 3 panes : Plan Statistics, Plan and Parallel Execution)


 

Categories: DBA Blogs

Re-Register ArchiveLogs and Backups

Thu, 2020-08-06 05:34
If you as the DBA or someone else as the SysAdmin has had to delete and restore or relocate ArchiveLogs or RMAN Backups to another mount point, you'd find that Oracle can no longer identify them.

This would also happen if you run a CREATE CONTROLFILE -- all information about RMAN Backups and ArchiveLogs that was formerly in the controlfile is "lost" because your database starts with a controlfile that has information only about DataFiles and Online RedoLogs.

How do you re-register ArchiveLogs and RMAN Backups ?

I begin this demo with 3 ArchiveLogs (Sequence#50 to #52) and one BackupSet created today.


SQL> l
1 select sequence#, name from v$archived_log
2 where dest_id=1
3 and (completion_time > sysdate-1 OR first_time > sysdate-1)
4* order by sequence#
SQL> /

SEQUENCE# NAME
---------- --------------------------------------------------
50 /opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf
51 /opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf
52 /opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf

SQL>
RMAN> list archivelog all completed after "sysdate-1";

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
144 1 50 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf

145 1 51 A 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf

146 1 52 A 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf


RMAN>
RMAN> list backup completed after "sysdate-1";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 790.52M DISK 00:00:23 06-AUG-20
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20200806T173252
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 5356172 06-AUG-20 NO /opt/oracle/oradata/ORCLCDB/system01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 18.02M DISK 00:00:01 06-AUG-20
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20200806T173317
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_08_06/o1_mf_s_1047749597_hlqmly7z_.bkp
SPFILE Included: Modification time: 06-AUG-20
SPFILE db_unique_name: ORCLCDB
Control File Included: Ckp SCN: 5356230 Ckp time: 06-AUG-20

RMAN>



Sometime later, these get deleted and  I can no longer find them.




RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
validation succeeded for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_48_1036108814.dbf RECID=139 STAMP=1047211353
validation succeeded for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_49_1036108814.dbf RECID=142 STAMP=1047211650
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf RECID=144 STAMP=1047749103
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf RECID=145 STAMP=1047749332
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf RECID=146 STAMP=1047749334
Crosschecked 5 objects


RMAN>
RMAN> crosscheck backup completed after "sysdate-1";

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp RECID=17 STAMP=1047749572
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_08_06/o1_mf_s_1047749597_hlqmly7z_.bkp RECID=18 STAMP=1047749598
Crosschecked 2 objects


RMAN>

oracle19c>pwd
/opt/oracle/archivelog/ORCLCDB
oracle19c>ls -ltr |tail -2
-rw-r-----. 1 oracle oinstall 6656 Jul 31 12:02 1_48_1036108814.dbf
-rw-r-----. 1 oracle oinstall 203776 Jul 31 12:07 1_49_1036108814.dbf
oracle19c>
oracle19c>pwd
/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06
oracle19c>ls -ltr
total 0
oracle19c>

RMAN> list expired backup;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Full 790.52M DISK 00:00:23 06-AUG-20
BP Key: 17 Status: EXPIRED Compressed: NO Tag: TAG20200806T173252
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 5356172 06-AUG-20 NO /opt/oracle/oradata/ORCLCDB/system01.dbf

RMAN> delete expired backup;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
17 17 1 1 EXPIRED DISK /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp RECID=17 STAMP=1047749572
Deleted 1 EXPIRED objects


RMAN>



So, ArchiveLogs from Sequence#50 to Sequence#52 have been deleted and so has today's backupset (although the controlfile autobackup is still present).

I ask the SysAdmin to restore the misssing files.  He restores them to a different mountpoint  -- under /NEWFS.
I then re-register them.



oracle19c>pwd
/NEWFS/archivelog/ORCLCDB
oracle19c>ls -l
total 4432
-rw-r-----. 1 oracle oinstall 1720832 Aug 6 17:25 1_50_1036108814.dbf
-rw-r-----. 1 oracle oinstall 2808320 Aug 6 17:28 1_51_1036108814.dbf
-rw-r-----. 1 oracle oinstall 1536 Aug 6 17:28 1_52_1036108814.dbf
oracle19c>

oracle19c>pwd
/NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06
oracle19c>ls -l
total 809504
-rw-r-----. 1 oracle oinstall 828932096 Aug 6 17:33 o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp
oracle19c>

SQL> alter database register physical logfile '/NEWFS/archivelog/ORCLCDB/1_50_1036108814.dbf';

Database altered.

SQL> alter database register physical logfile '/NEWFS/archivelog/ORCLCDB/1_51_1036108814.dbf';

Database altered.

SQL> alter database register physical logfile '/NEWFS/archivelog/ORCLCDB/1_52_1036108814.dbf';

Database altered.

SQL>
RMAN> list archivelog all completed after "sysdate-1";

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
148 1 50 A 31-JUL-20
Name: /NEWFS/archivelog/ORCLCDB/1_50_1036108814.dbf

144 1 50 X 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf

149 1 51 A 06-AUG-20
Name: /NEWFS/archivelog/ORCLCDB/1_51_1036108814.dbf

145 1 51 X 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf

150 1 52 A 06-AUG-20
Name: /NEWFS/archivelog/ORCLCDB/1_52_1036108814.dbf

146 1 52 X 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf

147 1 53 A 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_53_1036108814.dbf


RMAN>
RMAN> crosscheck archivelog all completed after "sysdate-1";

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=285 device type=DISK
validation succeeded for archived log
archived log file name=/NEWFS/archivelog/ORCLCDB/1_50_1036108814.dbf RECID=148 STAMP=1047752869
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf RECID=144 STAMP=1047749103
validation succeeded for archived log
archived log file name=/NEWFS/archivelog/ORCLCDB/1_51_1036108814.dbf RECID=149 STAMP=1047752894
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf RECID=145 STAMP=1047749332
validation succeeded for archived log
archived log file name=/NEWFS/archivelog/ORCLCDB/1_52_1036108814.dbf RECID=150 STAMP=1047752905
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf RECID=146 STAMP=1047749334
validation succeeded for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_53_1036108814.dbf RECID=147 STAMP=1047751145
Crosschecked 7 objects


RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=285 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
144 1 50 X 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf

145 1 51 X 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf

146 1 52 X 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_50_1036108814.dbf RECID=144 STAMP=1047749103
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_51_1036108814.dbf RECID=145 STAMP=1047749332
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_52_1036108814.dbf RECID=146 STAMP=1047749334
Deleted 3 EXPIRED objects


RMAN>
RMAN> list archivelog all completed after "sysdate-1";

List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
148 1 50 A 31-JUL-20
Name: /NEWFS/archivelog/ORCLCDB/1_50_1036108814.dbf

149 1 51 A 06-AUG-20
Name: /NEWFS/archivelog/ORCLCDB/1_51_1036108814.dbf

150 1 52 A 06-AUG-20
Name: /NEWFS/archivelog/ORCLCDB/1_52_1036108814.dbf

147 1 53 A 06-AUG-20
Name: /opt/oracle/archivelog/ORCLCDB/1_53_1036108814.dbf


RMAN>
--- note that ArchiveLog 53 is a new one that has been generated recently, in the default location

RMAN> catalog start with '/NEWFS/FRA';

searching for all files that match the pattern /NEWFS/FRA

List of Files Unknown to the Database
=====================================
File Name: /NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp

RMAN> list backup completed after "sysdate-1";


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18 Full 18.02M DISK 00:00:01 06-AUG-20
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20200806T173317
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_08_06/o1_mf_s_1047749597_hlqmly7z_.bkp
SPFILE Included: Modification time: 06-AUG-20
SPFILE db_unique_name: ORCLCDB
Control File Included: Ckp SCN: 5356230 Ckp time: 06-AUG-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 790.52M DISK 00:00:23 06-AUG-20
BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20200806T173252
Piece Name: /NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 5356172 06-AUG-20 NO /opt/oracle/oradata/ORCLCDB/system01.dbf

RMAN> crosscheck backup completed after "sysdate-1";

using channel ORA_DISK_1
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_08_06/o1_mf_s_1047749597_hlqmly7z_.bkp RECID=18 STAMP=1047749598
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/NEWFS/FRA/ORCLCDB/ORCLCDB/backupset/2020_08_06/o1_mf_nnndf_TAG20200806T173252_hlqml4dv_.bkp RECID=19 STAMP=1047753112
Crosschecked 2 objects


RMAN>



To re-register ArchiveLogs, I used the SQL command "ALTER DATABASE REGISTER PHYSICAL LOGFILE'.  RMAN is then able to identify these ArchiveLogs as well.  I then use CROSSCHECK and DELETE EXPIRED in RMAN to delete information about the old location of the same ArchiveLogs.

To re-register RMAN Backups, I used the RMAN command "CATALOG START WITH".  And then used "CROSSCHECK" to confirm that the BackupPiece(s) is/are available.




Categories: DBA Blogs

Oracle Cloud Shell

Tue, 2020-08-04 10:28
The Oracle Cloud (accessible even with a free account) now includes a Cloud Shell feature with 5GB of storage.

Here is a quick demo of this feature.


Categories: DBA Blogs

Archive Log Deletion Policy with a Standby Database

Thu, 2020-07-30 23:15
Previously, I have blogged about Archive Log Deletion Policy even when a Standby database is not present.

Here's a 19c example with a Standby Database

I first set it to "APPLIED ON ALL STANDBY" meaning that an ArchiveLog can be deleted only if the Primary has confirmed that the ArchiveLog (i.e. all the Redo in that ArchiveLog) has been applied on every Standby database configured for this Primary.

I then attempt to use the DELETE command to delete all recent ArchiveLogs.


oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jul 31 11:45:40 2020
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCLCDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # 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_ORCLCDB.f'; # default

RMAN> configure archivelog deletion policy to applied on all standby;

new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters are successfully stored

RMAN>
RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> list archivelog all completed after "sysdate-1/24";

List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
126 1 41 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_41_1036108814.dbf

128 1 42 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_42_1036108814.dbf

130 1 43 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf

131 1 44 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf


RMAN>
RMAN> delete archivelog all;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf thread=1 sequence=43
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf thread=1 sequence=44
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
118 1 37 A 12-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_37_1036108814.dbf

119 1 38 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_38_1036108814.dbf

120 1 39 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_39_1036108814.dbf

125 1 40 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_40_1036108814.dbf

126 1 41 A 13-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_41_1036108814.dbf

128 1 42 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_42_1036108814.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_37_1036108814.dbf RECID=118 STAMP=1045695910
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_38_1036108814.dbf RECID=119 STAMP=1045696247
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_39_1036108814.dbf RECID=120 STAMP=1045696315
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_40_1036108814.dbf RECID=125 STAMP=1045696378
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_41_1036108814.dbf RECID=126 STAMP=1047209331
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_42_1036108814.dbf RECID=128 STAMP=1047209610
Deleted 6 objects


RMAN>
RMAN> list archivelog all completed after "sysdate-1/24";

List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
130 1 43 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf

131 1 44 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf


RMAN>


RMAN refused to delete ArchiveLogs 43 and 44 that were recently generated because they haven't been applied to the Standby database(s).

Once the Standby confirms that it has received and applied all the Redo in 43 and 44 :


2020-07-31T11:51:53.314269+08:00
PR00 (PID:3718): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_43_1036108814.dbf
2020-07-31T11:51:53.676981+08:00
PR00 (PID:3718): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_44_1036108814.dbf
PR00 (PID:3718): Media Recovery Waiting for T-1.S-45 (in transit)
2020-07-31T11:51:53.868134+08:00


The Primary can now Delete these ArchiveLogs (even though they haven't been backed up).


RMAN> delete archivelog all completed after "sysdate-1/24";

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
130 1 43 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf

131 1 44 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_43_1036108814.dbf RECID=130 STAMP=1047210443
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_44_1036108814.dbf RECID=131 STAMP=1047210452
Deleted 2 objects


RMAN> list archivelog all;

specification does not match any archived log in the repository

RMAN>


If you want to prevent RMAN from Deleting ArchiveLogs that have not been backed up, you can either add the "backed up 'n' times to disk' clause to the DELETE command :


RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
134 1 45 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf

136 1 46 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf


RMAN> delete archivelog all completed after "sysdate-1/24" backed up 1 times to disk;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf thread=1 sequence=45
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf thread=1 sequence=46

RMAN>


OR use a combination Archive Log Deletion Policy :


RMAN> configure archivelog deletion policy to applied on all standby backed up 1 times to disk;

old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters are successfully stored

RMAN>
RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> sql 'alter system switch logfile';

sql statement: alter system switch logfile

RMAN> delete archivelog all completed after "sysdate-1/24";

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf thread=1 sequence=45
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf thread=1 sequence=46
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_47_1036108814.dbf thread=1 sequence=47
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_48_1036108814.dbf thread=1 sequence=48

RMAN>


After I have confirmed that the Standby has applied the recent ArchiveLogs


PR00 (PID:4569): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_47_1036108814.dbf
2020-07-31T12:04:40.251269+08:00
PR00 (PID:4569): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_48_1036108814.dbf
PR00 (PID:4569): Media Recovery Waiting for T-1.S-49 (in transit)
2020-07-31T12:04:40.609327+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 49 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log


I try the DELETE again on the Primary


RMAN> delete archivelog all completed after "sysdate-1/24";

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf thread=1 sequence=45
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf thread=1 sequence=46
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_47_1036108814.dbf thread=1 sequence=47
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_48_1036108814.dbf thread=1 sequence=48

RMAN>
RMAN> backup as compressed backupset archivelog all;

Starting backup at 31-JUL-20
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=45 RECID=134 STAMP=1047210945
input archived log thread=1 sequence=46 RECID=136 STAMP=1047210952
input archived log thread=1 sequence=47 RECID=138 STAMP=1047211344
input archived log thread=1 sequence=48 RECID=139 STAMP=1047211353
input archived log thread=1 sequence=49 RECID=142 STAMP=1047211650
channel ORA_DISK_1: starting piece 1 at 31-JUL-20
channel ORA_DISK_1: finished piece 1 at 31-JUL-20
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_07_31/o1_mf_annnn_TAG20200731T120730_hl7682or_.bkp tag=TAG20200731T120730 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-JUL-20

Starting Control File and SPFILE Autobackup at 31-JUL-20
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_07_31/o1_mf_s_1047211651_hl768490_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 31-JUL-20

RMAN> delete archivelog all completed after "sysdate-1/24";

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=275 device type=DISK
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_48_1036108814.dbf thread=1 sequence=48
RMAN-08120: warning: archived log not deleted, not yet applied by standby
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_49_1036108814.dbf thread=1 sequence=49
List of Archived Log Copies for database with db_unique_name ORCLCDB
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
134 1 45 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf

136 1 46 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf

138 1 47 A 31-JUL-20
Name: /opt/oracle/archivelog/ORCLCDB/1_47_1036108814.dbf


Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_45_1036108814.dbf RECID=134 STAMP=1047210945
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_46_1036108814.dbf RECID=136 STAMP=1047210952
deleted archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_47_1036108814.dbf RECID=138 STAMP=1047211344
Deleted 3 objects


RMAN>


At first, I cannot delete any of the ArchiveLogs.  Then, after I backup 45 to 49, I  am able to delete 45 to 47 but not 48 and 49 as they have not yet been applied to the Standby (although they have been backed up locally on the Primary server) 

Thus the Archive Log Deletion Policy protects from accidental deletion with the DELETE ARCHIVELOG command.

HOWEVER, the DELETE OBSOLETE command ignores this Policy.


Notes :
1.  Archive Log Deletion Policy has been available since 10g.  However, if you are on 10g or 11g, see Oracle Support Document ID 728053.1
2.  Instead of the "APPLIED ON [ALL] STANDBY" you can also use "SHIPPED TO [ALL] STANDBY" subclause.  This is useful if you have Standby databases that are [deliberately] lagging the Primary  -- .e.g a Standby that does the Apply 4 hours after the Redo is generated on the Primary.
Categories: DBA Blogs

SQL Developer 20.2 connected to Oracle ATP on the Cloud

Thu, 2020-07-23 02:39

Screenshot from SQL Developer on my desktop :





Screenshot from ATP Overview page :