Hemant K Chitale

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

Updated: 3 hours 8 min ago

ORA_ROWSCN

11 hours 6 min ago

 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 :





Performance Metrics for a specific SQL Statement, captured by the Real Time SQL Monitor in SQL Developer :



Categories: DBA Blogs

A Function Based Index : 2

Sun, 2020-07-12 10:16
Here's another Function Based Index example.   This is in 19.3  (not to demonstrate any difference between  12.2 and 19.3 here)

SQL> create table employees
2 (employee_id number primary key,
3 first_name varchar2(30),
4 last_name varchar2(30))
5 pctfree 0
6 /

Table created.

SQL>SQL> insert into employees
...
...

10000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES');

PL/SQL procedure successfully completed.

SQL>
SQL> select sample_size, blocks, empty_blocks, avg_row_len
2 from user_tab_statistics
3 where table_name = 'EMPLOYEES'
4 /

SAMPLE_SIZE BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
----------- ---------- ------------ -----------
10000 65 0 45

SQL>
SQL> select 45*10000/8192 from dual;

45*10000/8192
-------------
54.9316406

SQL>
SQL> select column_name, avg_col_len
2 from user_tab_cols
3 where table_name = 'EMPLOYEES'
4 order by column_id
5 /

COLUMN_NAME AVG_COL_LEN
------------------------------ -----------
EMPLOYEE_ID 4
FIRST_NAME 19
LAST_NAME 22

SQL>
SQL> create index employees_full_name_ndx
2 on employees(first_name||last_name)
3 /

Index created.

SQL>
SQL> select column_name, avg_col_len
2 from user_tab_cols
3 where table_name = 'EMPLOYEES'
4 order by column_id
5 /

COLUMN_NAME AVG_COL_LEN
------------------------------ -----------
EMPLOYEE_ID 4
FIRST_NAME 19
LAST_NAME 22
SYS_NC00004$

SQL>
SQL> -- no statistics on the new virtual column yet
SQL> exec dbms_stats.gather_table_stats('','EMPLOYEES');

PL/SQL procedure successfully completed.

SQL> select column_name, avg_col_len
2 from user_tab_cols
3 where table_name = 'EMPLOYEES'
4 order by column_id
5 /

COLUMN_NAME AVG_COL_LEN
------------------------------ -----------
EMPLOYEE_ID 4
FIRST_NAME 19
LAST_NAME 22
SYS_NC00004$ 40

SQL>
SQL> select num_rows, sample_size, blocks, empty_blocks, avg_row_len
2 from user_tab_statistics
3 where table_name = 'EMPLOYEES'
4 /

NUM_ROWS SAMPLE_SIZE BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- ----------- ---------- ------------ -----------
10000 10000 65 0 85

SQL>
SQL> select 85*10000/8192 from dual;

85*10000/8192
-------------
103.759766

SQL>
SQL> select leaf_blocks
2 from user_ind_statistics
3 where index_name = 'EMPLOYEES_FULL_NAME_NDX'
4 /

LEAF_BLOCKS
-----------
72

SQL>



After creation of the Function Based Index, the new virtual column in the table has an average length of 40 bytes.  Surely, the table should now have taken up more blocks with the average row length increasing from 45 to 85 ?  The number of table blocks should have increased by about 50 or more blocks ? And, yet, the table still consumes only 65 blocks.  It is the Index that reports 72 leaf blocks.

So, adding a Function Based Index increases the reported "AVG_ROW_LEN" for the Table without actually increasing the space consumed by the table.  The computed values are not stored in the Table blocks but are actually only in the Index leaf blocks.

Conversely, this can also mean that when you come across a table with a low BLOCKS versus AVG_ROW_LEN*NUM_ROWS, you might want to look for a Function Based Index :


SQL> select index_name, index_type, uniqueness
2 from user_indexes
3 where table_name = 'EMPLOYEES'
4 /

INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ --------------------------- ---------
SYS_C007674 NORMAL UNIQUE
EMPLOYEES_FULL_NAME_NDX FUNCTION-BASED NORMAL NONUNIQUE

SQL> select column_position, column_expression
2 from user_ind_expressions
3 where table_name = 'EMPLOYEES'
4 and index_name = 'EMPLOYEES_FULL_NAME_NDX'
5 /

COLUMN_POSITION COLUMN_EXPRESSION
--------------- ------------------------------
1 "FIRST_NAME"||"LAST_NAME"

SQL>


(SYS_C007674 is the Primary Key Index on EMPLOYEE_ID)


Categories: DBA Blogs

Adding a Function-Based Index

Thu, 2020-07-09 10:51
What happens when you create a Function Based Index ?

{This demo is in 12.2}

I start with my "large" table.

SQL> create table my_data_table 
2 as select * from dba_objects
3 union all select * from dba_objects;

Table created.

SQL> select count(*) from my_data_table;

COUNT(*)
----------
157408

SQL> exec dbms_stats.gather_table_stats('','MY_DATA_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL> select num_rows, avg_row_len
2 from user_tables
3 where table_name = 'MY_DATA_TABLE'
4 /

NUM_ROWS AVG_ROW_LEN
---------- -----------
157408 132

SQL>


I then add a Function Based Index on it.

SQL> create index my_data_fbi   
2 on my_data_table(lower(OBJECT_NAME))
3 /

Index created.

SQL>


I now look at Table and Column statistics

SQL> exec dbms_stats.gather_table_stats('','MY_DATA_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> desc my_data_table
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(18)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
APPLICATION VARCHAR2(1)
DEFAULT_COLLATION VARCHAR2(100)
DUPLICATED VARCHAR2(1)
SHARDED VARCHAR2(1)
CREATED_APPID NUMBER
CREATED_VSNID NUMBER
MODIFIED_APPID NUMBER
MODIFIED_VSNID NUMBER

SQL>
SQL> l
1 select column_name, num_nulls,to_char(last_analyzed,'DD-MON HH24:MI')
2 from user_tab_columns
3 where table_name = 'MY_DATA_TABLE'
4* order by column_id
SQL> /

COLUMN_NAME NUM_NULLS TO_CHAR(LAST_ANALYZED
------------------------------ ---------- ---------------------
OWNER 0 09-JUL 22:43
OBJECT_NAME 0 09-JUL 22:43
SUBOBJECT_NAME 155954 09-JUL 22:43
OBJECT_ID 2 09-JUL 22:43
DATA_OBJECT_ID 139120 09-JUL 22:43
OBJECT_TYPE 0 09-JUL 22:43
CREATED 0 09-JUL 22:43
LAST_DDL_TIME 2 09-JUL 22:43
TIMESTAMP 2 09-JUL 22:43
STATUS 0 09-JUL 22:43
TEMPORARY 0 09-JUL 22:43
GENERATED 0 09-JUL 22:43
SECONDARY 0 09-JUL 22:43
NAMESPACE 2 09-JUL 22:43
EDITION_NAME 157408 09-JUL 22:43
SHARING 0 09-JUL 22:43
EDITIONABLE 101970 09-JUL 22:43
ORACLE_MAINTAINED 0 09-JUL 22:43
APPLICATION 0 09-JUL 22:43
DEFAULT_COLLATION 125494 09-JUL 22:43
DUPLICATED 0 09-JUL 22:43
SHARDED 0 09-JUL 22:43
CREATED_APPID 157408 09-JUL 22:43
CREATED_VSNID 157408 09-JUL 22:43
MODIFIED_APPID 157408 09-JUL 22:43
MODIFIED_VSNID 157408 09-JUL 22:43

26 rows selected.

SQL>
SQL> l
1 select column_name, num_nulls,to_char(last_analyzed,'DD-MON HH24:MI')
2 from user_tab_cols
3 where table_name = 'MY_DATA_TABLE'
4* order by column_id
SQL> /

COLUMN_NAME NUM_NULLS TO_CHAR(LAST_ANALYZED
------------------------------ ---------- ---------------------
OWNER 0 09-JUL 22:43
OBJECT_NAME 0 09-JUL 22:43
SUBOBJECT_NAME 155954 09-JUL 22:43
OBJECT_ID 2 09-JUL 22:43
DATA_OBJECT_ID 139120 09-JUL 22:43
OBJECT_TYPE 0 09-JUL 22:43
CREATED 0 09-JUL 22:43
LAST_DDL_TIME 2 09-JUL 22:43
TIMESTAMP 2 09-JUL 22:43
STATUS 0 09-JUL 22:43
TEMPORARY 0 09-JUL 22:43
GENERATED 0 09-JUL 22:43
SECONDARY 0 09-JUL 22:43
NAMESPACE 2 09-JUL 22:43
EDITION_NAME 157408 09-JUL 22:43
SHARING 0 09-JUL 22:43
EDITIONABLE 101970 09-JUL 22:43
ORACLE_MAINTAINED 0 09-JUL 22:43
APPLICATION 0 09-JUL 22:43
DEFAULT_COLLATION 125494 09-JUL 22:43
DUPLICATED 0 09-JUL 22:43
SHARDED 0 09-JUL 22:43
CREATED_APPID 157408 09-JUL 22:43
CREATED_VSNID 157408 09-JUL 22:43
MODIFIED_APPID 157408 09-JUL 22:43
MODIFIED_VSNID 157408 09-JUL 22:43
SYS_NC00027$ 0 09-JUL 22:43

27 rows selected.

SQL>

SQL> l
1 select column_name, avg_col_len
2 from user_tab_cols
3 where table_name = 'MY_DATA_TABLE'
4* order by column_id
SQL> /

COLUMN_NAME AVG_COL_LEN
------------------------------ -----------
OWNER 6
OBJECT_NAME 34
SUBOBJECT_NAME 2
OBJECT_ID 5
DATA_OBJECT_ID 2
OBJECT_TYPE 10
CREATED 8
LAST_DDL_TIME 8
TIMESTAMP 20
STATUS 7
TEMPORARY 2
GENERATED 2
SECONDARY 2
NAMESPACE 3
EDITION_NAME 0
SHARING 13
EDITIONABLE 2
ORACLE_MAINTAINED 2
APPLICATION 2
DEFAULT_COLLATION 4
DUPLICATED 2
SHARDED 2
CREATED_APPID 0
CREATED_VSNID 0
MODIFIED_APPID 0
MODIFIED_VSNID 0
SYS_NC00027$ 34

27 rows selected.

SQL>
SQL> select num_rows, avg_row_len
2 from user_tables
3 where table_name = 'MY_DATA_TABLE'
4 /

NUM_ROWS AVG_ROW_LEN
---------- -----------
157408 166

SQL>


A new column "SYS_NC00027$" appears in USER_TAB_COLS but not in USER_TAB_COLUMNS.  The new column is not also not visible when I run a "DESCRIBE" command.

Also, the AVG_ROW_LEN has increased by 34  (seeing as I do not have any objects with long names) to reflect the addition of the new virtual column.
But did all the blocks actually get re-written ?  Are the "lower(OBJECT_NAME)" values written into each table block as well ?

SQL> exec dbms_stats.gather_table_stats('','MY_DATA_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> l
1 select obj#, savtime, rowcnt, blkcnt, avgrln,samplesize,to_char(analyzetime,'DD-MON-RR HH24:MI:SS')
2 from sys.WRI$_OPTSTAT_TAB_HISTORY
3 where obj# =
4 (select object_id
5 from dba_objects
6 where owner = 'HEMANT'
7 and object_type = 'TABLE'
8 and object_name = 'MY_DATA_TABLE')
9* order by 1,2
SQL> /

OBJ# SAVTIME ROWCNT BLKCNT AVGRLN SAMPLESIZE TO_CHAR(ANALYZETIME,'DD-MON
---------- ---------------------------- ---------- ---------- ---------- ---------- ---------------------------
79843 09-JUL-20 10.39.03.789392 PM 157408 3106 132 157408 09-JUL-20 22:37:53
79843 09-JUL-20 10.43.59.424420 PM 157408 3106 132 157408 09-JUL-20 22:39:03
79843 09-JUL-20 11.02.35.088733 PM 157408 3106 166 157408 09-JUL-20 22:43:59

SQL>


Apparently, the actual number of blocks did not increase.

SQL> l
1 select segment_name, segment_type, bytes/1024
2 from user_segments
3 where segment_name like 'MY_DATA%'
4* order by 1,2
SQL> /

SEGMENT_NAME SEGMENT_TYPE BYTES/1024
------------------------------ ------------------ ----------
MY_DATA_FBI INDEX 8192
MY_DATA_TABLE TABLE 25600

SQL>
SQL>
SQL> l
1 select index_name, column_name
2 from user_ind_columns
3* where index_name = 'MY_DATA_FBI'
SQL> /

INDEX_NAME COLUMN_NAME
------------------------------ ------------------------------
MY_DATA_FBI SYS_NC00027$

SQL>


So, the Index does take up space as an Index segment but the Virtual Column ("lower(OBJECT_NAME)")  created on the Table does not grow the table.  What seems to happen is that the "computed" value ("lower(OBJECT_NAME)") is stored in the Index segment (leaf blocks) but not in the Table segment (blocks)


What happens if I drop the Function Based Index ?

SQL> drop index my_data_fbi;

Index dropped.

SQL> exec dbms_stats.gather_table_stats('','MY_DATA_TABLE',degree=>4);

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*)
2 from user_tab_columns
3 where table_name = 'MY_DATA_TABLE'
4 /

COUNT(*)
----------
26

SQL> select count(*)
2 from user_tab_cols
3 where table_name = 'MY_DATA_TABLE'
4 /

COUNT(*)
----------
26

SQL> select avg_row_len
2 from user_tables
3 where table_name = 'MY_DATA_TABLE'
4 /

AVG_ROW_LEN
-----------
132

SQL>


The additional column and the 34 bytes it represents has disappeared from the Table and Column statistics.



Categories: DBA Blogs

A Correlated SubQuery

Tue, 2020-07-07 10:16
I take this simple Correlated Subquery that identifies all employees whose Salary is greater than the department's average Salary and is also greater than $9,000

SQL> l
1 select emp.department_id, emp.employee_id, emp.salary
2 from hr.employees emp
3 where emp.salary >
4 (select avg(salary)
5 from hr.employees emp_inner
6 where emp.department_id = emp_inner.department_id)
7 and emp.salary > 9000
8* order by 1,2
SQL> /

DEPARTMENT_ID EMPLOYEE_ID SALARY
------------- ----------- ----------
20 201 13000
30 114 11000
80 145 14000
80 146 13500
80 147 12000
80 148 11000
80 149 10500
80 150 10000
80 151 9500
80 156 10000
80 157 9500
80 162 10500
80 163 9500
80 168 11500
80 169 10000
80 170 9600
80 174 11000
90 100 24000
100 108 12008
110 205 12008

20 rows selected.

SQL>


The Row Source Statistics for the actual query execution are :

select emp.department_id, emp.employee_id, emp.salary
from hr.employees emp
where emp.salary >
(select avg(salary)
from hr.employees emp_inner
where emp.department_id = emp_inner.department_id)
and emp.salary > 9000
order by 1,2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 12 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 12 0 20

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
20 20 20 FILTER (cr=12 pr=0 pw=0 time=1027 us starts=1)
23 23 23 SORT GROUP BY (cr=12 pr=0 pw=0 time=1020 us starts=1 cost=7 size=3552 card=111)
536 536 536 HASH JOIN (cr=12 pr=0 pw=0 time=917 us starts=1 cost=6 size=71648 card=2239)
23 23 23 TABLE ACCESS FULL EMPLOYEES (cr=6 pr=0 pw=0 time=30 us starts=1 cost=3 size=1752 card=73)
108 108 108 TABLE ACCESS FULL EMPLOYEES (cr=6 pr=0 pw=0 time=7 us starts=1 cost=3 size=864 card=108)

********************************************************************************


The Execution Plan is

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 111 | 3552 | 7 (15)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | SORT GROUP BY | | 111 | 3552 | 7 (15)| 00:00:01 |
|* 3 | HASH JOIN | | 2239 | 71648 | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMPLOYEES | 73 | 1752 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMPLOYEES | 108 | 864 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("EMP"."SALARY">SUM("SALARY")/COUNT("SALARY"))
3 - access("EMP"."DEPARTMENT_ID"="EMP_INNER"."DEPARTMENT_ID")
4 - filter("EMP"."SALARY">9000)


(Since I had only 1 execution of the query when tracing is enabled, "Rows (1st"), "Rows (avg)" and "Rows (max)" report on the same, single, execution)

The EMPLOYEES table underwent two Full Table Scans, each consisting of 6 "consistent reads"  ("cr=6"). 

The execution at line Id=4 expected to return 73 rows ("card=73") but actually returned 23 rows.  This query filtered for SALARY greater than 9000.

The execution at line Id=5 expected to return 108 rows ("card=108") and did actually return 108 rows.  There is no predicate information, meaning that all 108 rows of the table were returned, none filtered.

The join of the outer query and the inner query on DEPARTMENT_ID was the Hash Join at operation Id=3.

Do you note how the FILTER at operation ID=1 appears ?  It is filtering for SALARY greater than AVERAGE SALARY  (where AVERAGE is computed as SUM of SALARY  values divided by the number of not-null SALARY values)



Categories: DBA Blogs

"Long Term Support Release" vs "Innovation Release"

Fri, 2020-06-19 00:31
Too often, IT departments implement and then get stuck on software releases that do not get long term support.  There is no harm upgrading to a new release that is an intermediate if you plan to subsequently upgrade to the proper release version that has long term support.

Mike Dietrich has explained this in his blog post here.




Categories: DBA Blogs

Full Recovery of Standby Database over the network

Wed, 2020-06-17 10:10
Say that your Standby database is lagging behind the Primary database.

You could
a.  Fetch and apply all the ArchiveLogs required to cover the lag
b.  Take an Incremental Backup from the Primary and apply it to the Standby (the syntax being "backup as compressed backupset incremental from SCN=xxxx format '......' ) and then restore it on the Standby
c.  Since 12c, do a complete Refresh of the Standby over the network


My Primary reports :

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
79

SQL>


While my Standby reports :

SQL> select high_Sequence# from v$archive_gap;

HIGH_SEQUENCE#
--------------
67

SQL>


So, I attempt to refresh the Standby with :

$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jun 17 22:55:22 2020

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 419430400 bytes

Fixed Size 8793496 bytes
Variable Size 167772776 bytes
Database Buffers 234881024 bytes
Redo Buffers 7983104 bytes

RMAN> restore database from service ORCL12C;

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

skipping datafile 5; already restored to SCN 1443131
skipping datafile 6; already restored to SCN 1443131
skipping datafile 8; already restored to SCN 1443131
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /STANDBY/database/STDB/datafile/o1_mf_system_2gude3k1_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /STANDBY/database/STDB/datafile/o1_mf_sysaux_2hude3l5_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /STANDBY/database/STDB/datafile/o1_mf_users_2qude3pc_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_system_2jude3nb_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_sysaux_2fude3iu_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_undotbs1_2iude3mi_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_users_2mude3op_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00013 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_apex_199_2pude3pb_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00014 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_apex_199_2rude3pe_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00015 to /STANDBY/database/STDB/datafile/o1_mf_undotbs2_2oude3p4_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 17-JUN-20

RMAN>
RMAN> exit


Recovery Manager complete.
$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 17 22:58:55 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 database recover managed standby database disconnect from session;

Database altered.

SQL>


The alert log on the Standby even shows the new PDB that I had created on the Primary while the Standby was down.  (See my previous BlogPost where I had created NEWPDB)

2020-06-17T22:59:13.347236+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_67_hgncbh2m_.arc
2020-06-17T22:59:13.497650+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_68_hgnbvsy2_.arc
2020-06-17T22:59:13.566410+08:00
Completed: alter database recover managed standby database disconnect from session
2020-06-17T22:59:13.659512+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_69_hgncbj0v_.arc
2020-06-17T22:59:13.759502+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_70_hgncbj5z_.arc
2020-06-17T22:59:13.981225+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_71_hgncbjf6_.arc
Recovery created pluggable database NEWPDB
2020-06-17T22:59:21.246470+08:00
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file /STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_system_hgnbd696_.dbf from /STANDBY/database/STDB/49BFE9E2D73E2038E0530100007F846C/datafile/o1_mf_system_2lude3oa_.dbf
NEWPDB(4):Successfully added datafile 41 to media recovery
NEWPDB(4):Datafile #41: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_system_hgnbd696_.dbf'
2020-06-17T22:59:30.184914+08:00
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file /STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_sysaux_hgnbd6c1_.dbf from /STANDBY/database/STDB/49BFE9E2D73E2038E0530100007F846C/datafile/o1_mf_sysaux_2kude3nr_.dbf
NEWPDB(4):Successfully added datafile 42 to media recovery
NEWPDB(4):Datafile #42: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_sysaux_hgnbd6c1_.dbf'
2020-06-17T22:59:32.983486+08:00
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file /STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_undotbs1_hgnbd6c2_.dbf from /STANDBY/database/STDB/49BFE9E2D73E2038E0530100007F846C/datafile/o1_mf_undotbs1_2nude3p1_.dbf
NEWPDB(4):Successfully added datafile 43 to media recovery
NEWPDB(4):Datafile #43: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_undotbs1_hgnbd6c2_.dbf'
2020-06-17T22:59:36.310405+08:00
(4):Successfully added datafile 44 to media recovery
(4):Datafile #44: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_my_user__hgncypmz_.dbf'
(4):Resize operation completed for file# 42, old size 337920K, new size 348160K
(4):Resize operation completed for file# 42, old size 348160K, new size 368640K
2020-06-17T22:59:39.083144+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_72_hgncbjf0_.arc
2020-06-17T22:59:39.235507+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_73_hgnc90l0_.arc
2020-06-17T22:59:39.547295+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_74_hgncbdhw_.arc
2020-06-17T22:59:39.734181+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_75_hgncbdn2_.arc
2020-06-17T22:59:39.856485+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_76_hgncbgvb_.arc
2020-06-17T22:59:40.007157+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_77_hgncbgr5_.arc
2020-06-17T22:59:40.129214+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_78_hgncbmo4_.arc
2020-06-17T22:59:40.266298+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_79_hgncbmmz_.arc
Media Recovery Waiting for thread 1 sequence 80 (in transit)
2020-06-17T23:02:19.174414+08:00
Archived Log entry 18 added for thread 1 sequence 80 rlc 937554761 ID 0x2dc76487 LAD2 :
2020-06-17T23:02:19.575536+08:00
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process (PID:6137)
RFS[2]: No standby redo logfiles created
2020-06-17T23:02:19.597702+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_80_hgncrmr3_.arc
RFS[2]: Opened log for T-1.S-81 dbid 768045447 branch 937554761
2020-06-17T23:02:20.948511+08:00
Media Recovery Waiting for thread 1 sequence 81 (in transit)
2020-06-17T23:02:31.317814+08:00
Archived Log entry 19 added for thread 1 sequence 81 rlc 937554761 ID 0x2dc76487 LAD2 :
RFS[2]: No standby redo logfiles created
RFS[2]: Opened log for T-1.S-82 dbid 768045447 branch 937554761
2020-06-17T23:02:31.710951+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_81_hgnd3vlo_.arc
Media Recovery Waiting for thread 1 sequence 82 (in transit)


And I can check whether the datafiles are being updated at the Standby

SQL> select current_scn, database_role from v$database;

CURRENT_SCN DATABASE_ROLE
----------- ----------------
3035674 PHYSICAL STANDBY

SQL> select file#, checkpoint_change# from v$datafile order by 1;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3035675
3 3035675
5 1443131
6 1443131
7 3035675
8 1443131
9 3035675
10 3035675
11 3035675
12 3035675
13 3035675
14 3035675
15 3035675
41 3035675
42 3035675
43 3035675
44 3035675

17 rows selected.

SQL>


What are datafiles 5, 6 and 8 ? Even the RMAN RESTORE command had shown them to "already restored to SCN 1443131"  -- which is a much lower SCN ?

SQL> select p.con_id, p.name, p.open_mode, d.file#, d.checkpoint_change#
2 from v$pdbs p, v$datafile d
3 where p.con_id=d.con_id
4 order by d.file#
5 /

CON_ID NAME OPEN_MODE FILE# CHECKPOINT_CHANGE#
---------- ------------ ---------- ---------- ------------------
2 PDB$SEED MOUNTED 5 1443131
2 PDB$SEED MOUNTED 6 1443131
2 PDB$SEED MOUNTED 8 1443131
3 ORCL MOUNTED 9 3035675
3 ORCL MOUNTED 10 3035675
3 ORCL MOUNTED 11 3035675
3 ORCL MOUNTED 12 3035675
3 ORCL MOUNTED 13 3035675
3 ORCL MOUNTED 14 3035675
4 NEWPDB MOUNTED 41 3035675
4 NEWPDB MOUNTED 42 3035675
4 NEWPDB MOUNTED 43 3035675
4 NEWPDB MOUNTED 44 3035675

13 rows selected.

SQL>


Those 3 datafiles are of the SEED PDB which does not get updated as it is not opened READ-WRITE.



Categories: DBA Blogs

Datafile in a PDB added but not backed up

Mon, 2020-06-15 09:43
(I think I've covered this with a pre-12c non-PDB example earlier, but this demo is with a 12c PDB)

What happens if you add a datafile to a PDB and then lose it without having a backup of the datafile ?

Here is a quick demo

$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:10:19 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> show parameter db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL> alter session set db_create_file_dest='/u01/app/oracle/oradata';

Session altered.

SQL> create pluggable database newpdb admin user newpdb_adm identified by newpdb_adm;

Pluggable database created.

SQL>
SQL> alter pluggable database newpdb open;

Pluggable database altered.

SQL> alter session set container=newpdb;

Session altered.

SQL> create user hemant_newpdb identified by hemant_newpdb;

User created.

SQL> grant dba to hemant_newpdb;

Grant succeeded.

SQL>

SQL> !vi $ORACLE_HOME/network/admin/tnsnames.ora --- added the entry for newpdb

SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$sqlplus hemant_newpdb/hemant_newpdb@newpdb

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:16:29 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>
SQL> alter session set db_create_file_dest='/u01/app/oracle/oradata';

Session altered.

SQL> create tablespace my_user_data;

Tablespace created.

SQL> select file_name from dba_data_files
2 where tablespace_name = 'MY_USER_DATA'
3 /

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf

SQL> select file_id, file_name
2 from dba_data_files
3 order by 1
4 /

FILE_ID
----------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
41
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_system_hgh0ddlg_.dbf

42
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_sysaux_hgh0ddlw_.dbf

43
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_undotbs1_hgh0ddlw_.dbf

44
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf


SQL> create table my_user_data_tbl tablespace my_user_data as select * from dba_objects;

Table created.

SQL> select count(*) from my_user_data_tbl;

COUNT(*)
----------
72623

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


So, I now have a new PDB, a custom tablespace in the PDB and a table with data in that Tablespace.

I have NOT yet taken a backup of the PDB.

For the purpose of this demo, I will corrupt the datafile.

$cat  >/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
abcdef
junk data
overwriting the datafile
$ls -l /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
-rw-r----- 1 oracle oinstall 43 Jun 15 22:22 /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
$cat /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
abcdef
junk data
overwriting the datafile
$


Any attempt to read or write the Datafile will fail.

$sync;sync
$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:23:38 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 system flush buffer_cache;

System altered.

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$sqlplus hemant_newpdb/hemant_newpdb@newpdb

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:23:59 2020

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

Last Successful login time: Mon Jun 15 2020 22:16:29 +08:00

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

SQL> select count(*) from my_user_data_tbl;
select count(*) from my_user_data_tbl
*
ERROR at line 1:
ORA-01115: IO error reading block from file (block # )
ORA-01110: data file 44: '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 130


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$


The sync and flush buffer_cache commands ensure that Oracle does not try to read the table blocks from memory but actually has to attempt to read the Datafile.

Now it reports that the Datafile has an error.

However, I have not taken a backup of the Datafile.

I go ahead to use RMAN to "restore" and "recover" the Datafile.

$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jun 15 22:30:32 2020

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

connected to target database: ORCL12C (DBID=768045447)

RMAN> list failure;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
171 HIGH OPEN 15-JUN-20 One or more non-system datafiles are corrupt

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
171 HIGH OPEN 15-JUN-20 One or more non-system datafiles are corrupt

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=267 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. Automatic repairs may be available if you shutdown the database and restart it in mount mode

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 44
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/hm/reco_3798852352.hm

RMAN>
RMAN> quit


Recovery Manager complete.
$cat /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/hm/reco_3798852352.hm
# restore and recover datafile
sql 'NEWPDB' 'alter database datafile 44 offline';
restore ( datafile 44 );
recover datafile 44;
sql 'NEWPDB' 'alter database datafile 44 online';
$
$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jun 15 22:31:44 2020

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

connected to target database: ORCL12C (DBID=768045447)

RMAN> sql 'NEWPDB' 'alter database datafile 44 offline';

using target database control file instead of recovery catalog
sql statement: alter database datafile 44 offline

RMAN> restore ( datafile 44 );

Starting restore at 15-JUN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=289 device type=DISK

creating datafile file number=44 name=/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 15-JUN-20

RMAN> recover datafile 44;

Starting recover at 15-JUN-20
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 15-JUN-20

RMAN> sql 'NEWPDB' 'alter database datafile 44 online';

sql statement: alter database datafile 44 online

RMAN> exit


Recovery Manager complete.
$


Note that the message "restore not done; all files read only, offline, excluded, or already restored" can be misleading.  This occurs when RMAN actually *creates* a Datafile because there is no backup of the Datafile.

I can now query the data.

$sqlplus hemant_newpdb/hemant_newpdb@newpdb

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:34:40 2020

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

Last Successful login time: Mon Jun 15 2020 22:24:00 +08:00

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

SQL> select count(*) from my_user_data_tbl;

COUNT(*)
----------
72623

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$
$cd /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile
$ls -l
total 829540
-rw-r----- 1 oracle oinstall 104865792 Jun 15 22:32 o1_mf_my_user__hgh0qyh7_.dbf
-rw-r----- 1 oracle oinstall 377495552 Jun 15 22:23 o1_mf_sysaux_hgh0ddlw_.dbf
-rw-r----- 1 oracle oinstall 262152192 Jun 15 22:40 o1_mf_system_hgh0ddlg_.dbf
-rw-r----- 1 oracle oinstall 67117056 Jun 15 22:14 o1_mf_temp_hgh0ddlw_.dbf
-rw-r----- 1 oracle oinstall 104865792 Jun 15 22:40 o1_mf_undotbs1_hgh0ddlw_.dbf


Here are the interesting messages from the alert log file :

2020-06-15T22:31:53.166607+08:00
NEWPDB(4):alter database datafile 44 offline
NEWPDB(4):Completed: alter database datafile 44 offline
2020-06-15T22:32:01.797043+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_m000_5900.trc:
ORA-01110: data file 44: '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-01