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: 17 hours 33 min ago

Tracking the Standby Lag from the Primary

Sun, 2021-05-09 10:38

 Here is a quick way of tracking the Standby Lag from the Primary.

This relies on the information in V$ARCHIVE_DEST on the Primary.

Note that this query will not work if the lag is so great that the SCN_TO_TIMESTAMP mapping fails (because the underlying table holds only a limited number of records) OR if the Standby instance is shutdown and the Primary cannot communicate with it.


Note : The lag based on "SCN_TO_TIMESTAMP" is always an approximation.  

SQL> l
1 select scn_to_timestamp(current_scn) - scn_to_timestamp(applied_scn) Time_Diff
2 from v$database d,
3* (select applied_scn from v$archive_dest a where target = 'STANDBY')
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 00:41:09.000000000

SQL>
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:07:22.000000000

SQL>
SQL> l
1 select scn_to_timestamp(current_scn) - scn_to_timestamp(applied_scn) Time_Diff
2 from v$database d,
3* (select applied_scn from v$archive_dest a where target = 'STANDBY')
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:07:58.000000000

SQL>
SQL> l
1 select scn_to_timestamp(current_scn) - scn_to_timestamp(applied_scn) Time_Diff
2 from v$database d,
3* (select applied_scn from v$archive_dest a where target = 'STANDBY')
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:13:16.000000000

SQL>
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000004 01:13:37.000000000

SQL>
SQL> /

TIME_DIFF
---------------------------------------------------------------------------
+000000000 00:00:00.000000000

SQL>


Here, the lag was 4 days and it took some time for the Standby to catchup with the Primary.
(this is my Lab environment, not a real production environment at my work place, so don't ask how I managed to create a lag of 4 days or how long it took for the Standby to catch-up with the Pirmary)

Note : If the Standby database is down and/or the lag is very high, you will get error :
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

for the "applied_scn" from v$archive_dest.  (If the Standby is down, the value for "applied_scn" in v$archive_dest on the Primary is "0").


If you have access to the Standby you can run this query :

select name, value from v$dataguard_stats where name like '%lag'


The demo above is only a quick away by querying the Primary without accessing the Standby
Categories: DBA Blogs

RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Wed, 2021-05-05 09:40

 You are attempting to restore a database to another server.  

So, you have verified that you have controlfile and datafile backups on the source server  :



RMAN> list backup of controlfile;

using target database control file instead of recovery catalog

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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 11.52M DISK 00:00:01 20-FEB-21
BP Key: 51 Status: AVAILABLE Compressed: NO Tag: TAG20210220T114245
Piece Name: /opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
Control File Included: Ckp SCN: 1093419 Ckp time: 20-FEB-21

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
55 Full 11.52M DISK 00:00:02 04-MAY-21
BP Key: 55 Status: AVAILABLE Compressed: NO Tag: TAG20210504T232054
Piece Name: /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
Control File Included: Ckp SCN: 1126526 Ckp time: 04-MAY-21

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
56 Full 11.48M DISK 00:00:01 04-MAY-21
BP Key: 56 Status: AVAILABLE Compressed: NO Tag: TAG20210504T232851
Piece Name: /home/oracle/controlfile.bak
Control File Included: Ckp SCN: 1126757 Ckp time: 04-MAY-21

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
57 Full 11.52M DISK 00:00:02 04-MAY-21
BP Key: 57 Status: AVAILABLE Compressed: NO Tag: TAG20210504T232853
Piece Name: /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
Control File Included: Ckp SCN: 1126766 Ckp time: 04-MAY-21

RMAN>


You have copied the backups to the target, new, server and attempt to restore :

oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 5 22:27:26 2021
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 1207958960 bytes

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

RMAN> restore controlfile from '/home/oracle/controlfile.bak';

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/05/2021 22:27:47
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN>
RMAN> quit


Recovery Manager complete.
oracle19c>ls /home/oracle/controlfile.bak
/home/oracle/controlfile.bak
oracle19c>ls /opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
/opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
oracle19c>ls /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
/opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
oracle19c>ls /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
/opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
oracle19c>


So, why do you get the RMAN-06172 error ?  All the controlfile backups, including the manual backup to /home/oracle/controlfile.bak and the three autobackups, one from February 2021 and two from 04-May-2021 are available.

oracle19c>oerr rman 6172
6172, 1, "no AUTOBACKUP found or specified handle is not a valid copy or piece"
// *Cause: A restore could not proceed because no AUTOBACKUP was found or
// specified handle is not a valid copy or backup piece.
// In case of restore from AUTOBACKUP, it may be the case that a
// backup exists, but it does not satisfy the criteria specified in
// the user's restore operands.
// In case of restore from handle, it may be the handle is not a
// backup piece or control file copy. In may be that it does not
// exist.
// *Action: Modify AUTOBACKUP search criteria or verify the handle.
oracle19c>
oracle19c>ls -l /home/oracle/controlfile.bak
-rw-r-----. 1 root root 12058624 May 4 23:28 /home/oracle/controlfile.bak
oracle19c>ls -l /opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
-rw-r-----. 1 root root 12091392 Feb 20 11:42 /opt/oracle/FRA/HEMANT/autobackup/2021_02_20/o1_mf_s_1065008565_j3119p5t_.bkp
oracle19c>ls -l /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
-rw-r-----. 1 root root 12091392 May 4 23:20 /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703254_j92slr2m_.bkp
oracle19c>ls -l /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
-rw-r-----. 1 root root 12091392 May 4 23:28 /opt/oracle/FRA/HEMANT/autobackup/2021_05_04/o1_mf_s_1071703733_j92t1pow_.bkp
oracle19c>


You get the "error" message that there are no AUTOBACKUPs because the "oracle19c" account is unable to actually *read* those pieces.  It can list them using "ls" because it has permission to read the OS folders containing them, but it does no have permission to read the files owned by root without having granted read permission.

So, before you start wondering about your AUTOBACKUP configuration or search criteria specification like "RESTORE CONTROLFILE FROM AUTOBACKUP MAXDAYS 30",  check if the backup pieces are readable.


Categories: DBA Blogs

My Posts on RMAN

Sat, 2021-05-01 23:05

 My series of posts on RMAN :

1. 1 : Backup Job Details

2. 2 : ArchiveLog Deletion Policy

3. 3 : The DB_UNIQUE_NAME in Backups to the FRA

4. 4 : Recovering from an Incomplete Restore

5. 4b : Recovering from an Incomplete Restore with OMF Files

6. 5 : Useful KEYWORDs and SubClauses

7. 5b : (More) Useful KEYWORDs and SubClauses

8. 5c : (Some More) Useful KEYWORDs and SubClauses

9. 6 : RETENTION POLICY and CONTROL_FILE_RECORD_KEEP_TIME

10. 7 : Recovery Through RESETLOGS -- how are the ArchiveLogs identified ?

11. 8 : Using a Recovery Catalog Schema

12. 9 : Querying the RMAN Views / Catalog

13. 10 : VALIDATE


An older series of "tips" :

14. Tips -- 1

15. Tips -- 2

16. Tips -- 3

17. Tips -- 4


Other RMAN posts not in the  above series : (not in any particular order)

18. RMAN's CATALOG command

19. RESTORE and RECOVER a NOARCHIVELOG Database, with Incremental Backups

20. RESTORE and RECOVER a NOARCHIVELOG Database, with Incremental Backups -- 2nd Post

21. Primary and Standby in the same RMAN Catalog

22. Understanding Obsolescence of RMAN Backups

23. "SET TIME ON" in RMAN

24. RMAN Backup of a Standby Database

25. RMAN Image Copy File Names

26. Verifying an RMAN Backup

27. Verifying an RMAN Backup - Part 2

28. Misinterpreting RESTORE DATABASE VALIDATE

29. RMAN Backup and Recovery for Loss of ALL Files

30. CONTROLFILE AUTOBACKUPs are OBSOLETE[d]

31.RMAN Consistent ("COLD" ?) Backup and Restore

32. Archive Log Deletion Policy with a Standby Database

33. Datafiles not Restored -- using V$DATAFILE and V$DATAFILE_HEADER

34. Read Only Tablespaces and BACKUP OPTIMIZATION


Categories: DBA Blogs

Pro*C in Oracle

Sat, 2021-05-01 05:48

 Oracle also ships a Pro*C Precompiler that can convert a Pro*C source file to a C source file which can then be compiled  using a C Compiler (e.g  using "gcc").  Of course, you need the Pro*C Developer Licence to use this product.

Here is a quick demo with the command line display and then the actual code below.



oracle19c>ls -ltr
total 12
-rw-r--r--. 1 oracle oinstall 2255 May 1 18:07 instancedbinfo.pc
-rwxr--r--. 1 oracle oinstall 786 May 1 18:14 Compile_my_ProC.SH
-rwxr--r--. 1 oracle oinstall 356 May 1 18:15 Run_my_ProC.SH
oracle19c>./Compile_my_ProC.SH
*****Set LD_LIBRARY_PATH
*****Set C_INCLUDE_PATH
*****PreCompile Pro*C program file

Pro*C/C++: Release 19.0.0.0.0 - Production on Sat May 1 18:15:17 2021
Version 19.3.0.0.0

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

System default option values taken from: /opt/oracle/product/19c/dbhome_1/precomp/admin/pcscfg.cfg

*****Compile using C Compiler and specifying Oracle Client library file libclntsh.so
*****Compiled files:
-rw-r--r--. 1 oracle oinstall 2255 May 1 18:07 instancedbinfo.pc
-rw-r--r--. 1 oracle oinstall 0 May 1 18:15 instancedbinfo.lis
-rw-r--r--. 1 oracle oinstall 11875 May 1 18:15 instancedbinfo.c
-rwxr-xr-x. 1 oracle oinstall 14424 May 1 18:15 instancedbinfo
oracle19c>
oracle19c>
oracle19c>
oracle19c>./Run_my_ProC.SH
*****Set LD_LIBRARY_PATH
*****Set Connection String
*****Execute the program
Connected to ORACLE
At ORCLCDB which is on oracle-19c-vagrant running 19.0.0.0.0 and is OPEN, started at 01-MAY-21 17:54:52
This is ORCLPDB1 database running in READ WRITE mode since 01-MAY-21 05.55.21.573 PM +08:00

oracle19c>


The file "instancedbinfo.pc" is my Pro*C source code.
I Precompile it using the "proc" precompiler into "instancedbinfo.c".  Any compilation errors would have been logged into "instancedbinfo.lis"
Then, the same script "Compile_my_ProC.SH" compiles the C program source code into an executable "instancedbinfo" using "gcc"

Finally, I use "Run_my_ProC.SH" to execute the file "instancedbinfo"  (which is now an executable) and the execution displays information about the Pluggable database it is connected to.


Here is the code for the two shell scripts :


oracle19c>cat Compile_my_ProC.SH

echo "*****Set LD_LIBRARY_PATH"
LD_LIBRARY_PATH=/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include:/usr/include/linux:/opt/oracle/product/19c/dbhome_1/precom/lib:/opt/oracle/product/19c/dbhome_1/lib
export LD_LIBRARY_PATH


echo "*****Set C_INCLUDE_PATH"
C_INCLUDE_PATH=/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include:/usr/include/linux:/opt/oracle/product/19c/dbhome_1/precom/lib:/opt/oracle/product/19c/dbhome_1/lib:/opt/oracle/product/19c/dbhome_1/precomp/public
export C_INCLUDE_PATH

echo "*****PreCompile Pro*C program file"
proc instancedbinfo.pc

echo "*****Compile using C Compiler and specifying Oracle Client library file libclntsh.so"
gcc instancedbinfo.c -o instancedbinfo -L /opt/oracle/product/19c/dbhome_1/lib -l clntsh

echo "*****Compiled files:"
ls -ltr instancedbinfo*
oracle19c>


oracle19c>cat Run_my_ProC.SH

echo "*****Set LD_LIBRARY_PATH"
LD_LIBRARY_PATH=/usr/lib/gcc/x86_64-redhat-linux/4.8.2/include:/usr/include/linux:/opt/oracle/product/19c/dbhome_1/precom/lib:/opt/oracle/product/19c/dbhome_1/lib
export LD_LIBRARY_PATH

echo "*****Set Connection String"
CNCTSTRING=hemant/hemant@orclpdb1
export CNCTSTRING

echo "*****Execute the program"
./instancedbinfo
oracle19c>


The Compilation script specifies the LD_LIBRARY_PATH and the Paths to the Include (.h Header) files.  
It then executes "proc"  (which is in $ORACLE_HOME/bin) to precompile the "instancedbinfo.pc" source file.
Finally, it calls "gcc" to compile the c-language source code file (generated by the Precomipler), also specifiying the client shared library file libclntsh.so  in $ORACLE_HOME/lib  (only "-l clntsh" is sufficient to identify the file name).  The compiled executable is called "instancedbinfo" with Execute Permission.

The Run script specifies the Connect-String that the executable will be reading from the environment and executes it.


Here is the code of the source Pro*C file :


oracle19c>cat instancedbinfo.pc

/* standard C includes */
#include << stdio.h >>
#include << stdlib.h >>
#include << string.h >>



/* Oracle Pro*C includes from $ORACLE_HOME/precomp/public */
#include << sqlca.h >>
#include << sqlda.h >>
#include << sqlcpr.h >>




/* my variables */
varchar MYinstanceName[16];
varchar MYhostName[64];
varchar MYversion[17];
varchar MYstatus[12];
varchar MYinstanceStartupTime[18];
varchar MYdbName[128];
varchar MYdbOpenMode[10];
varchar MYdbOpenTime[32];



/* function for error handling */
void sql_error(msg)
char msg[200];
{
char err_msg[128];
size_t buf_len, msg_len;

EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("\n%s\n", msg);
buf_len = sizeof (err_msg);
sqlglm(err_msg, &buf_len, &msg_len);
printf("%.*s\n", msg_len, err_msg);

EXEC SQL ROLLBACK RELEASE;
exit(EXIT_FAILURE);
}


/* MAIIN program */
int main(argc,argv)
int argc;
char *argv[];
{

/* read Connection String from environment -- or, it could have been hardcoded here */
const char *conn = getenv("CNCTSTRING");
if (!conn) {
printf("! require CNCTSTRING env variable\n");
return (1);
}

EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");

/* connect to targe database */
EXEC SQL CONNECT :conn ;
printf("Connected to ORACLE \n");


/* execute query and populate variables */
/* NOTE : This expects to connect to a PDB ! */
/* If the target is a Non-PDB, change references from v$pdbs to V$database */
EXEC SQL SELECT instance_name,host_name, version,
to_char(startup_time,'DD-MON-RR HH24:MI:SS'), status,
name, open_mode, to_char(open_time)
INTO :MYinstanceName, :MYhostName, :MYversion,
:MYinstanceStartupTime, :MYstatus,
:MYdbName, :MYdbOpenMode, :MYdbOpenTime
FROM v$instance, v$pdbs ;


/* display query results */
printf("At %s which is on %s running %s and is %s, started at %s \n",
MYinstanceName.arr, MYhostName.arr, MYversion.arr, MYstatus.arr, MYinstanceStartupTime.arr);
printf("This is %s database running in %s mode since %s \n",
MYdbName.arr, MYdbOpenMode.arr, MYdbOpenTime.arr);
printf("\n");

/* end of MAIN */
}
oracle19c>


(Note :  I have put doube angle brackets for the #includes so as to preserve them in HTML)
Pro*C allows embedding of SQL calls into a C program be including the Proc include files and then running the source code through a Precompiler.
My Pro*C source code file is 2,255 bytes and the C source code is 11,875 bytes.

Note that the variables defined as varchar in my Pro*C source file are actually become C structures :

/* my variables */
/* varchar MYinstanceName[16]; */
struct { unsigned short len; unsigned char arr[16]; } MYinstanceName;

/* varchar MYhostName[64]; */
struct { unsigned short len; unsigned char arr[64]; } MYhostName;

/* varchar MYversion[17]; */
struct { unsigned short len; unsigned char arr[17]; } MYversion;

/* varchar MYstatus[12]; */
struct { unsigned short len; unsigned char arr[12]; } MYstatus;

/* varchar MYinstanceStartupTime[18]; */
struct { unsigned short len; unsigned char arr[18]; } MYinstanceStartupTime;

/* varchar MYdbName[128]; */
struct { unsigned short len; unsigned char arr[128]; } MYdbName;

/* varchar MYdbOpenMode[10]; */
struct { unsigned short len; unsigned char arr[10]; } MYdbOpenMode;

/* varchar MYdbOpenTime[32]; */
struct { unsigned short len; unsigned char arr[32]; } MYdbOpenTime;


Similarly, my EXEC SQL query also gets re-written :
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 13;
sqlstm.arrsiz = 8;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = "select instance_name ,host_name ,version ,to_char(startup\
_time,'DD-MON-RR HH24:MI:SS') ,status ,name ,open_mode ,to_char(open_time) int\
o :b0,:b1,:b2,:b3,:b4,:b5,:b6,:b7 from v$instance ,v$pdbs ";
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )51;
sqlstm.selerr = (unsigned short)1;
sqlstm.sqlpfmem = (unsigned int )0;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = (unsigned char *)&MYinstanceName;
sqlstm.sqhstl[0] = (unsigned long )18;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( short *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned long )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = (unsigned char *)&MYhostName;
and so on .....


Pro*C is a very good way of combining C programming with SQL and creating an executable binary instead of an interpreted file (like a Java or Python program outside the database).



Categories: DBA Blogs

OJVM : Loading Java Code and Running it from a Database Session

Sun, 2021-04-25 06:08

 Oracle's JVM in the database, available since 8i and also known as "OJVM" allows you (Developer or DBA) to load Java code into the database and execute it from a database session.  Note that any OS calls that it makes will be from the server process, not the client process. Thus, if the code tries to read or write to a filesystem or to connect to an external website, the call will be executed by the database server process, not your client (sqlplus or any other program).


Here is a simple example :



SQL> -- grant Java Code permission to only READ only /home/oracle/tmp
SQL> -- this should have been provided by the DBA
SQL>
SQL> begin
2 dbms_java.grant_permission('HEMANT',
3 'SYS:java.io.FilePermission',
4 '/home/oracle/tmp', 'read');
5
6 dbms_java.grant_permission( 'HEMANT',
7 'SYS:java.io.FilePermission',
8 '/home/oracle/tmp/*',
9 'read' );
10
11 dbms_java.grant_permission( 'HEMANT',
12 'SYS:java.lang.RuntimePermission',
13 'getFileSystemAttributes',
14 '' );
15 end;
16 /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL>
SQL> -- here is where I load the Java code into my schema
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL>
SQL> -- load the java code into the database
SQL> create or replace and compile java source named "readOSDirectory"
2 as
3 /* using java.io */
4 import java.io.*;
5
6 /* create the main class */
7 public class readOSDirectory
8 {
9 /* create the class to be executed from a procedure */
10 public static void getList(String directory)
11 {
12 /* use File class from java.io */
13 File directoryPath = new File( directory );
14 File filesList[] = directoryPath.listFiles();
15
16 /* read till end of list */
17 for (File file : filesList)
18 {
19 System.out.println("File Name: "+file.getName()
20 + " File Size: "+file.length());
21 }
22 }
23 }
24 /

Java created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> /* create a PL/SQL procedure to call the getList class */
SQL> create or replace
2 procedure Read_Directory( p_directory in varchar2 )
3 as language java
4 name 'readOSDirectory.getList( java.lang.String )';
5 /

Procedure created.

SQL>
SQL> /* enable output to screen-- serveroutput is for my sqlplus session */
SQL> SET SERVEROUTPUT ON SIZE 100000
SQL> CALL dbms_java.set_output (100000);

Call completed.

SQL>
SQL> begin
2 Read_Directory('/home/oracle/tmp');
3 end;
4 /
File Name: File_1.txt File Size: 13
File Name: File_2.txt File Size: 25
File Name: Run_Sequence_Loop.sql File Size: 1047
File Name: List_LongOps.sql File Size: 303
File Name: create_db_HEMANT.sql File Size: 1530
File Name: create_db_HEMANT.lst File Size: 47

PL/SQL procedure successfully completed.

SQL>
SQL> begin
2 Read_Directory('/home/oracle');
3 end;
4 /
Exception in thread "Root Thread" java.security.AccessControlException: the Permission ("java.io.FilePermission" "/home/oracle"
"read") has not been granted to HEMANT. The PL/SQL to grant this is dbms_java.grant_permission( 'HEMANT',
'SYS:java.io.FilePermission', '/home/oracle', 'read' )
at java.security.AccessControlContext.checkPermission(AccessControlContext.java)
at java.security.AccessController.checkPermission(AccessController.java:926)
at java.lang.SecurityManager.checkPermission(SecurityManager.java:551)
at oracle.aurora.rdbms.SecurityManagerImpl.checkPermission(SecurityManagerImpl.java:210)
at java.lang.SecurityManager.checkRead(SecurityManager.java:890)
at java.io.File.list(File.java:1117)
at java.io.File.listFiles(File.java:1207)
at readOSDirectory.getList(readOSDirectory:11)
begin
*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission
("java.io.FilePermission" "/home/oracle" "read") has not been granted to HEMANT. The PL/SQL to grant this is
dbms_java.grant_permission( 'HEMANT', 'SYS:java.io.FilePermission', '/home/oracle', 'read' )
ORA-06512: at "HEMANT.READ_DIRECTORY", line 1
ORA-06512: at line 2


SQL>
SQL> !ls -l /home/oracle/tmp
total 24
-rw-r--r--. 1 oracle oinstall 47 Apr 25 18:46 create_db_HEMANT.lst
-rw-r--r--. 1 oracle oinstall 1530 Apr 25 18:46 create_db_HEMANT.sql
-rw-r--r--. 1 oracle oinstall 13 Apr 25 18:07 File_1.txt
-rw-r--r--. 1 oracle oinstall 25 Apr 25 18:34 File_2.txt
-rw-r--r--. 1 oracle oinstall 303 Apr 25 18:46 List_LongOps.sql
-rw-r--r--. 1 oracle oinstall 1047 Apr 25 18:46 Run_Sequence_Loop.sql

SQL>


(Note : The Java call reads from the OS's directory object "as is" returned by the directory.  The "ls -l" command sorts the output, so you may see a difference in the sorted output)

Since OJVM runs in a database server process, strong permission controls are enforced.  So, the first set of calls show permissions being granted by the DBA to the "HEMANT" schema to only read from /home/oracle/tmp

Then, the user (HEMANT) connects and loads his Java code into the database.  (Note : The first time you run this, it may take some time to load the java classes).  Then he creates a PL/SQL procedure as a "wrapper" that can execute the Java code.

Finally, he executes the java code to read the target directory /home/oracle/tmp

I also demonstrate how his attempt to read /home/oracle fails as he has not been granted explicit permission on this directory

Categories: DBA Blogs

Primary and Standby in the same RMAN Catalog

Wed, 2021-04-21 10:23

 A quick demo of an RMAN Catalog shared by both Primary and Standby databases

For this demo, the RMAN Catalog is in the "rmanschema" account in the "rmancat" database, accessible from both Primary and Standby

At the Primary :



oracle19c>echo $ORACLE_SID
ORCLCDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:22:02 2021
Version 19.3.0.0.0

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


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

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
ORCLCDB READ WRITE

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ORCLCDB
SQL>
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:22:54 2021
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)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> quit


Recovery Manager complete.
oracle19c>


Now, at the Standby



oracle19c>echo $ORACLE_SID
STDBYDB
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:25:13 2021
Version 19.3.0.0.0

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


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

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
ORCLCDB MOUNTED

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string STDBYDB
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:26:03 2021
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, not open)
connected to recovery catalog database

RMAN> register database;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 04/21/2021 22:26:19
RMAN-01005: Mounted control file type must be CURRENT to register the database

RMAN> quit


Recovery Manager complete.
oracle19c>


(Note that the Standby has the same NAME and DBID, but a distinct DB_UNIQUE_NAME.)

It is NOT necessary to register the Standby as long as the DB_UNIQUE_NAME is different and is not currently "known to the Catalog" (i.e. is not already registered for some other database).

So, how do I run RMAN Backups and have them saved in the Catalog ?

On the Primary, I run a FULL Backup.


oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:31:08 2021
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)
connected to recovery catalog database

RMAN> backup as compressed backupset database;

Starting backup at 21-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=280 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/ORCLCDB/system01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 21-APR-21
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_2: starting piece 1 at 21-APR-21
channel ORA_DISK_2: finished piece 1 at 21-APR-21
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7v9_.bkp tag=TAG20210421T223134 comment=NONE

...
...
...
Starting Control File and SPFILE Autobackup at 21-APR-21
piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2021_04_21/o1_mf_s_1070490815_j80fy0hv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-21

RMAN> quit


Recovery Manager complete.
oracle19c>


Then, I run a separate backup at the Standby :


oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:36:37 2021
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, not open)
connected to recovery catalog database

RMAN> backup as compressed backupset database;

Starting backup at 21-APR-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=253 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=249 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/STDBYDB/system01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 21-APR-21
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_2: starting piece 1 at 21-APR-21
channel ORA_DISK_2: finished piece 1 at 21-APR-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4j55_.bkp tag=TAG20210421T223703 comment=NONE

...
...
...
Starting Control File and SPFILE Autobackup at 21-APR-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_04_21/o1_mf_s_1070490998_j80g8k1q_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 21-APR-21

RMAN> quit


Recovery Manager complete.
oracle19c>


So, I now have two different database backups of the same NAME and DBID registered in one catalog.

How do I distinguish them ?  From the RMAN command lime query  I can verify in this manner (querying only for datafile 1 for the purpose of this demo)

At the Primary :
oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:43:42 2021
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)
connected to recovery catalog database

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


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1245 Full 231.82M DISK 00:00:38 21-APR-21
BP Key: 1255 Status: AVAILABLE Compressed: YES Tag: TAG20210421T223134
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7pb_.bkp
List of Datafiles in backup set 1245
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 11072202 21-APR-21 NO /opt/oracle/oradata/ORCLCDB/system01.dbf

RMAN> quit


Recovery Manager complete.
oracle19c>


And at the Standby :

oracle19c>rman target / catalog rmanschema/rmanschema@rmancat

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Apr 21 22:45:03 2021
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, not open)
connected to recovery catalog database

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


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1300 Full 241.08M DISK 00:00:40 21-APR-21
BP Key: 1308 Status: AVAILABLE Compressed: YES Tag: TAG20210421T223703
Piece Name: /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp
List of Datafiles in backup set 1300
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 Full 11061162 21-APR-21 11072277 NO /opt/oracle/oradata/STDBYDB/system01.dbf

RMAN> quit


Recovery Manager complete.
oracle19c>


RMAN doesn't show the database hostname (and a RAC database could be on multiple hosts, with rman backup channels running from multiple hosts concurrently, backups could be written to tape instead of disk).

But the listing does show that 

the Primary database datafile /opt/oracle/oradata/ORCLCDB/system01.dbf  is in the backup- piece /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223134_j80ft7pb_.bkp 

while  the Standby database datafile /opt/oracle/oradata/STDBYDB/system01.dbf is in the backup-piece /opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2021_04_21/o1_mf_nnndf_TAG20210421T223703_j80g4hv6_.bkp.

You might also notice that the Checkpoint SCNs are different.



Can I run an SQL query in the RMAN Catalog schema itself ?

oracle19c>sqlplus rmanschema/rmanschema@rmancat

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 21 22:49:02 2021
Version 19.3.0.0.0

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

Last Successful login time: Wed Apr 21 2021 22:45:04 +08:00

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

SQL> select db_key, dbid, name from rc_database;

DB_KEY DBID NAME
---------- ---------- --------
1 2778483057 ORCLCDB

SQL>
SQL> col db_unique_name format a16
SQL> select site_key, db_key, database_role, db_unique_name from rc_site;

SITE_KEY DB_KEY DATABAS DB_UNIQUE_NAME
---------- ---------- ------- ----------------
3 1 PRIMARY ORCLCDB
804 1 STANDBY STDBYDB

SQL>
SQL> l
1 select site.site_key, site.database_role, site.db_unique_name,
2 bs.bs_key, bs.backup_type
3 from rc_site site, rc_backup_set bs
4 where site.site_key=bs.site_key
5 and site.db_key=bs.db_key
6 and bs.start_time > sysdate-1
7* and bs.bs_key in (1245,1300)
SQL> /

SITE_KEY DATABAS DB_UNIQUE_NAME BS_KEY B
---------- ------- ---------------- ---------- -
3 PRIMARY ORCLCDB 1245 D
804 STANDBY STDBYDB 1300 D

SQL>
SQL> l
1 select site.site_key, site.database_role, site.db_unique_name,
2 df.bs_key, df.file#, df.checkpoint_change#
3 from rc_site site, rc_backup_set bs, rc_backup_datafile df
4 where site.site_key=bs.site_key
5 and site.db_key=bs.db_key
6 and site.db_key=df.db_key
7 and bs.bs_key=df.bs_key
8 and bs.start_time > sysdate-1
9 and bs.bs_key in (1245,1300)
10* and df.file#=1
SQL> /

SITE_KEY DATABAS DB_UNIQUE_NAME BS_KEY FILE# CHECKPOINT_CHANGE#
---------- ------- ---------------- ---------- ---------- ------------------
3 PRIMARY ORCLCDB 1245 1 11072202
804 STANDBY STDBYDB 1300 1 11061162


Here we can set that RC_DATABASE has only 1 record for the actual database.  But we can distinguish the Primary and Standby from RC_SITE. SITE_KEY is 3 is for the Primary and 804 is for the Standby.

BS_KEY is the "BS Key" in the "LIST BACKUP" command output from RMAN.  Today's backup of Datafile#1 at the Primary was in Backup Set  1245 while that of the same datafile at the Standby was in Backup Set 1300.  (Yes, the Primary and Standby can have different Backup Set numbers and the Standby may well have higher Backup Set numbers if I have run more frequent Backups at the Standby !)
You can also compare the CHECKPOINT_CHANGE# as the "Ckp SCN" in the LIST BACKUP output. The SYSTEM datafile#1 was at SCN 11072202 in the Primary Backup and SCN 11061162 in the Standby Backup.

There are many more RC_%  Tables and Views in the RMAN Schema that you can query.


Categories: DBA Blogs

Python with Oracle using sqlalchemy and cx_oracle

Sun, 2021-04-11 09:46

 Here is a simple demo of using Python and the sqlalchemy and cx_oracle libraries


This is the code :

#import required libraries
import pandas as pd
import cx_Oracle
import sqlalchemy
from sqlalchemy.exc import SQLAlchemyError

#setup connection
try:
oracle_pdb = sqlalchemy.create_engine("oracle+cx_oracle://hemant:hemant@localhost/?service_name=orclpdb1", arraysize=100)
except SQLAlchemyError as e:
print(e)

#setup query and pandas dataframe for results
try:
employees_query = """SELECT * FROM hr.employees order by employee_id""";
df_employees = pd.read_sql(employees_query, oracle_pdb)
except SQLAlchemyError as e:
print(e)

#Info on the dataframe
print(df_employees.info())
#the first five rows
print(df_employees.head())

#create a new dataframe with a subset of columns
df_emp_selected_cols=df_employees
df_emp_selected_cols.drop(['email','phone_number','salary','commission_pct','manager_id','department_id'],axis=1, inplace=True)
print(df_emp_selected_cols.head())


And here is the output from my database :

Info on the dataframe

RangeIndex: 108 entries, 0 to 107
Data columns (total 11 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 employee_id 108 non-null int64
1 first_name 108 non-null object
2 last_name 108 non-null object
3 email 108 non-null object
4 phone_number 107 non-null object
5 hire_date 108 non-null datetime64[ns]
6 job_id 108 non-null object
7 salary 107 non-null float64
8 commission_pct 35 non-null float64
9 manager_id 106 non-null float64
10 department_id 107 non-null float64
dtypes: datetime64[ns](1), float64(4), int64(1), object(5)
memory usage: 9.4+ KB
None
The first 5 rows
employee_id first_name last_name ... commission_pct manager_id department_id
0 100 Steven King ... NaN NaN 90.0
1 101 Neena Kochhar ... NaN 100.0 90.0
2 102 Lex De Haan ... NaN 100.0 90.0
3 103 Alexander Hunold ... NaN 102.0 60.0
4 104 Bruce Ernst ... NaN 103.0 60.0

[5 rows x 11 columns]
With selected columns only
employee_id first_name last_name hire_date job_id
0 100 Steven King 2003-06-17 AD_PRES
1 101 Neena Kochhar 2005-09-21 AD_VP
2 102 Lex De Haan 2001-01-13 AD_VP
3 103 Alexander Hunold 2006-01-03 IT_PROG
4 104 Bruce Ernst 2007-05-21 IT_PROG


Once you are familiar with this method, you can use numpy, matplotlib and a host of other python libraries with the dataset.

This article by Yuli Vasiliev is a good starter.


Categories: DBA Blogs

Using the SESSION_LONGOPS view with DBMS_APPLICATION_INFO

Sat, 2021-04-03 10:38

 I have, in the past, demonstrated DBMS_APPLICATION_INFO   here    and   here.

Also, I have demonstrated how V$SESSION_LONGOPS can be used (and misunderstood !!) 


Here is a demo of how you can use both features to enable monitoring of a (batch ?) job run.

The job is modified to include calls to DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS.

The DBA can then monitor the job from V$SESSION_LONGOPS.


The job is to update a PRODUCTS table with new PRODUCT_IDs, after some validation.  Since, this can take some time to run, the DBA wants to monitor it via V$SESSION_LONGOPS.


Here is the PL/SQL code for the batch (job) run :(look up the documentaion on DBMS_APPLICATION_INFO in the PL/SQL Developers Guide for our version -- I know that is available since at least 10.2, although this demonstation below is in 19c)



DECLARE
rindex BINARY_INTEGER;
slno BINARY_INTEGER;
target_count number;
updated_count number;

product_row products%rowtype;
check_flag varchar2(8);

BEGIN
rindex := dbms_application_info.set_session_longops_nohint;
updated_count := 0;
select num_rows into target_count --- this is an approximation based on last updated statistics on the table
from user_tables
where table_name = 'PRODUCTS';

-- begin the updates here
for product_row in (select product_id, product_name from products)
loop

-- validate_for_update is a function that checks if this product should get a new PRODUCT_ID
select validate_for_update(product_row.product_id, product_row.product_name) into check_flag from dual ;

if check_flag='OK' then
update products set product_id = product_id+10000 where product_id=product_row.product_id;
insert into update_run_log values (product_row.product_id,systimestamp);
updated_count := updated_count+1;
dbms_application_info.set_session_longops(rindex,
slno,
op_name=>'New_Product_IDs',
target=>0, -- default, not used by me
context=>0, -- default, not used by me
sofar=>updated_count,
totalwork=>target_count,
target_desc=>'Table : PRODUCTS',
units=>'rows');
else
null;
end if;

end loop;
END;
/


commit;



And here is the DBA monitoring the job as it is running :



23:22:15 SQL> l
1 select sid, opname, target_desc, sofar, totalwork, units, start_time, elapsed_seconds, time_remaining
2 from v$session_longops
3* where username = 'HEMANT'
23:22:15 SQL> /

no rows selected

23:22:16 SQL>
23:22:21 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 3 500 rows 03-APR-21 23:22:18 2 331

23:22:22 SQL>
23:22:30 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 12 500 rows 03-APR-21 23:22:18 12 488

23:22:31 SQL>
23:22:44 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 26 500 rows 03-APR-21 23:22:18 26 474

23:22:45 SQL>
23:23:26 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 67 500 rows 03-APR-21 23:22:18 69 446

23:23:27 SQL>
23:26:28 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 245 500 rows 03-APR-21 23:22:18 251 261

23:26:30 SQL>
23:28:31 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 364 500 rows 03-APR-21 23:22:18 373 139

23:28:32 SQL>
23:29:24 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 416 500 rows 03-APR-21 23:22:18 426 86

23:29:25 SQL>
23:30:16 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 467 500 rows 03-APR-21 23:22:18 478 34

23:30:17 SQL>
23:30:43 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 493 500 rows 03-APR-21 23:22:18 505 7

23:30:44 SQL>
23:30:48 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 497 500 rows 03-APR-21 23:22:18 509 3

23:30:49 SQL>
23:30:50 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 500 500 rows 03-APR-21 23:22:18 512 0

23:30:51 SQL>
23:31:04 SQL> /

SID OPNAME TARGET_DESC SOFAR TOTALWORK UNITS START_TIME ELAPSED_SECONDS TIME_REMAINING
----- ------------------ ---------------- ---------- ---------- ------ ------------------ --------------- --------------
18 New_Product_IDs Table : PRODUCTS 500 500 rows 03-APR-21 23:22:18 512 0

23:31:05 SQL>


(In this demo, all 500 rows actually do pass the "VALIDATE_FOR_UPDATE" check) 
 Normally, we expect V$SESSION_LONGOPS to be populated by Long Running Operations (I specify Operations because of the "misinterpretation" that I demonstrate here).  Operations that are Full Table Scans  or Parallel Execution or RMAN Operations --- all of these are cases where the view is populated "automatically" by Oracle.

However, as I have demonstrated above, you can use your own code to populate this view so that it can be used to monitor Long Running "Operations" that you have defined (or, rather, worked with the Developers to define if you are the DBA).



Categories: DBA Blogs

Patching -- opatch and datapatch in Oracle vs a single executable in SQL Server

Fri, 2021-03-19 09:21

 In the Oracle universe, when applying a Release Update patch, the DBA has to run "opatch" to patch the binaries and library files but also has to run a separate "datapatch" to update the data dictionary with SQL "Apply" Actions in each database.

In what seems to be a contrast, SQL Server patching requires only execution of the Patch exe file.  Does that mean that no SQL "Apply" Actions are required ?  See how it is done in SQL Server in my other blog post.



Categories: DBA Blogs

RESTORE and RECOVER a NOARCHIVELOG Database, with Incremental Backups -- 2nd Post

Thu, 2021-02-18 08:10

 As a follow up on a question in the previous blog post,  I demonstrate it again without restoring the Controlfile


The current SCN and available backups (Level-0 and Level-1) :



SQL> select  current_scn from v$database;

CURRENT_SCN
-----------
1084836

SQL>
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /opt/oracle/archivelog/HEMANT
Oldest online log sequence 6
Current log sequence 8
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 18 21:50:02 2021
Version 19.3.0.0.0

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

connected to target database: HEMANT (DBID=432411782)

RMAN> list 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
------- ---- -- ---------- ----------- ------------ ---------------
44 Incr 0 67.09M DISK 00:00:19 18-FEB-21
BP Key: 44 Status: AVAILABLE Compressed: YES Tag: TAG20210218T212223
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/19vnh8kg_1_1
List of Datafiles in backup set 44
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/system.dbf
2 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/sysaux.dbf
3 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/undotbs.dbf
4 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
5 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/indx01.dbf
6 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/HR_DATA.dbf
10 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/indx02.dbf
11 0 Incr 974397 18-FEB-21 NO /opt/oracle/oradata/HEMANT/indx03.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
45 Full 11.52M DISK 00:00:02 18-FEB-21
BP Key: 45 Status: AVAILABLE Compressed: NO Tag: TAG20210218T212249
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210218-00
SPFILE Included: Modification time: 18-FEB-21
SPFILE db_unique_name: HEMANT
Control File Included: Ckp SCN: 974397 Ckp time: 18-FEB-21

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
46 Incr 1 1.84M DISK 00:00:16 18-FEB-21
BP Key: 46 Status: AVAILABLE Compressed: YES Tag: TAG20210218T212541
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/1bvnh8ql_1_1
List of Datafiles in backup set 46
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/system.dbf
2 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/sysaux.dbf
3 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/undotbs.dbf
4 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
5 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/indx01.dbf
6 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/HR_DATA.dbf
10 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/indx02.dbf
11 1 Incr 975490 18-FEB-21 NO /opt/oracle/oradata/HEMANT/indx03.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
47 Full 11.52M DISK 00:00:01 18-FEB-21
BP Key: 47 Status: AVAILABLE Compressed: NO Tag: TAG20210218T212606
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210218-01
SPFILE Included: Modification time: 18-FEB-21
SPFILE db_unique_name: HEMANT
Control File Included: Ckp SCN: 975490 Ckp time: 18-FEB-21

RMAN>


Now my current SCN is 1084826 .
My Level-0 Backup (BackupSet 44) was at 974397 (and so was the Controlfile backup in BackupSet 45)
My Level-1 Backup (BackupSet 46) was at 975490 (and so was the Controlfile backup in BackupSet 47).

So, all of those backups are older than the current SCN.

Can I restore and recover the database without restoring the Controlfile ?


RMAN> quit


Recovery Manager complete.
oracle19c>rm /opt/oracle/oradata/HEMANT/system.dbf
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 18 21:53:38 2021
Version 19.3.0.0.0

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


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

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1084978

SQL>
SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/opt/oracle/oradata/HEMANT/system.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
toracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 18 21:57:23 2021
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 mount;

Oracle instance started
database mounted

Total System Global Area 1207958960 bytes

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

RMAN>
RMAN> restore database;

Starting restore at 18-FEB-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 device type=DISK

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 00002 to /opt/oracle/oradata/HEMANT/sysaux.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/HEMANT/undotbs.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/HEMANT/indx01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/oracle/oradata/HEMANT/HR_DATA.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/HEMANT/indx02.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/19c/dbhome_1/dbs/19vnh8kg_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/19c/dbhome_1/dbs/19vnh8kg_1_1 tag=TAG20210218T212223
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 18-FEB-21

RMAN> recover database noredo;

Starting recover at 18-FEB-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /opt/oracle/oradata/HEMANT/system.dbf
destination for restore of datafile 00002: /opt/oracle/oradata/HEMANT/sysaux.dbf
destination for restore of datafile 00003: /opt/oracle/oradata/HEMANT/undotbs.dbf
destination for restore of datafile 00004: /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
destination for restore of datafile 00005: /opt/oracle/oradata/HEMANT/indx01.dbf
destination for restore of datafile 00006: /opt/oracle/oradata/HEMANT/HR_DATA.dbf
destination for restore of datafile 00010: /opt/oracle/oradata/HEMANT/indx02.dbf
destination for restore of datafile 00011: /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/19c/dbhome_1/dbs/1bvnh8ql_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/19c/dbhome_1/dbs/1bvnh8ql_1_1 tag=TAG20210218T212541
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished recover at 18-FEB-21

RMAN>
RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 02/18/2021 21:59:38
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

RMAN>
RMAN> exit


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

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 18 22:00:12 2021
Version 19.3.0.0.0

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


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

SQL> recover datbase using backup controlfile until cancel;
ORA-00905: missing keyword


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 975490 generated at 02/18/2021 21:25:11 needed for thread 1
ORA-00289: suggestion : /opt/oracle/archivelog/HEMANT/1_4_1063318051.dbf
ORA-00280: change 975490 for thread 1 is in sequence #4


Specify log: {=suggested | filename | AUTO | CANCEL} -- commented the RET as it was being treated as an HTML Tag
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
975656

SQL>


I can't exactly use the same method as I did in the previous blog post.  This is because RMAN doesn't properly recognise this as an Incomplete Recovery if I have used the current Controlfile.
What is the workaround ?  Use sqlplus !  I can use the SQL command "recover database using backup controlfile until cancel" and then CANCEL to simulate an Incomplete Recovery that allows me to "open resetlogs" !
This is similar to the "OPEN RESETLOGS without really doing a Recovery" demo that I had presented earlier.

Note : The CURRENT_SCN is now 975656.  This is because I have restored and recovered from "older" database backups (BackupSet 45 at SCN 975490) and ignored any transactions after those backups.  The OPEN RESETLOGS recreates the Online Redo Logs and resynchronizes the Controlfile but, in the process some SCNs are incremented so it is higher than 975490 and lower than 1084978.
This doesn't mean that User Transactions after 975490 have been recovered. They have, actually been discarded.


Categories: DBA Blogs

Checking for Active Transactions

Thu, 2021-02-11 03:18

 Oracle 11.2 introduced the WAIT_ON_PENDING_DML function in the DBMS_UTILITY Package.

Here is a demonstration of how to use it :



17:05:21 SQL> @Check_for_Transactions
17:05:22 SQL> declare
17:05:22 2 check_for_transactions boolean;
17:05:22 3 scnvalue number;
17:05:22 4 begin
17:05:22 5 check_for_transactions := dbms_utility.wait_on_pending_dml
17:05:22 6 (tables=>'HEMANT.MY_TXN_TABLE',
17:05:22 7 timeout=>60,
17:05:22 8 scn=>scnvalue);
17:05:22 9 if check_for_transactions then
17:05:22 10 dbms_output.put_line('No Transaction(s) at start time OR Transactions present at start have committed before Timeout');
17:05:22 11 dbms_output.put_line('Other Transactions *may* have begun after the start of this Check');
17:05:22 12 else
17:05:22 13 dbms_output.put_line('One or More Active Transaction(s) present until Timeout');
17:05:22 14 end if;
17:05:22 15 end;
17:05:22 16 /
No Transaction(s) at start time OR Transactions present at start have committed before Timeout
Other Transactions *may* have begun after the start of this Check

PL/SQL procedure successfully completed.

17:05:22 SQL>


17:05:43 SQL> @Check_for_Transactions
17:05:44 SQL> declare
17:05:44 2 check_for_transactions boolean;
17:05:44 3 scnvalue number;
17:05:44 4 begin
17:05:44 5 check_for_transactions := dbms_utility.wait_on_pending_dml
17:05:44 6 (tables=>'HEMANT.MY_TXN_TABLE',
17:05:44 7 timeout=>60,
17:05:44 8 scn=>scnvalue);
17:05:44 9 if check_for_transactions then
17:05:44 10 dbms_output.put_line('No Transaction(s) at start time OR Transactions present at start have committed before Timeout');
17:05:44 11 dbms_output.put_line('Other Transactions *may* have begun after the start of this Check');
17:05:44 12 else
17:05:44 13 dbms_output.put_line('One or More Active Transaction(s) present until Timeout');
17:05:44 14 end if;
17:05:44 15 end;
17:05:44 16 /
One or More Active Transaction(s) present until Timeout

PL/SQL procedure successfully completed.

17:06:44 SQL>


17:07:08 SQL> @Check_for_Transactions
17:07:09 SQL> declare
17:07:09 2 check_for_transactions boolean;
17:07:09 3 scnvalue number;
17:07:09 4 begin
17:07:09 5 check_for_transactions := dbms_utility.wait_on_pending_dml
17:07:09 6 (tables=>'HEMANT.MY_TXN_TABLE',
17:07:09 7 timeout=>60,
17:07:09 8 scn=>scnvalue);
17:07:09 9 if check_for_transactions then
17:07:09 10 dbms_output.put_line('No Transaction(s) at start time OR Transactions present at start have committed before Timeout');
17:07:09 11 dbms_output.put_line('Other Transactions *may* have begun after the start of this Check');
17:07:09 12 else
17:07:09 13 dbms_output.put_line('One or More Active Transaction(s) present until Timeout');
17:07:09 14 end if;
17:07:09 15 end;
17:07:09 16 /
No Transaction(s) at start time OR Transactions present at start have committed before Timeout
Other Transactions *may* have begun after the start of this Check

PL/SQL procedure successfully completed.

17:07:23 SQL>


When I ran the Check code at17:05:22, there were no active transaction against the target table "HEMANT.MY_TXN_TABLE", so the Check completed immediately (the returned BOOLEAN is TRUE)

When I re-ran the Check code at 17:05:44, there were one or more transactions (uncommitted) present.  The Check code ran for 60 seconds until the specified timeout and returned the message "One or More Active Transaction(s) present until Timeout"

When I ran the Check code again at 17:07:09 there were one or more transactions present.  However, they committed within the 60seconds timeout so the Check ended at 17:07:23  (i.e. the function returned TRUE at 17:07:23)

However, this Check only checks for transactions present as at the time it began running.  If a third or fourth session begins a transaction after this start and yet does not commit, it would not be identified by this Check.  

With the caveat that the Check doesn't check for *new* transactions, this is useful when you are monitoring for the presence of transactions at a specific time --- .e.g you expected an ETL job to complete by 17:30 and know that no other session would have any transaction against the target table.

The "TABLES" parameter can actually take a comma-separated list of tables.
The "SCN" parameter is an IN OUT in that you can put in a specific SCN prior to which transactions may begun.  If a NULL or invalid value is passed, the function takes the current SCN.

Categories: DBA Blogs

RESTORE and RECOVER a NOARCHIVELOG Database, with Incremental Backups

Sun, 2021-01-31 08:25

 Oracle does support all Incremental (as well as Full) Backups of Databases running in NOARCHIVELOG mode.  Such backups can be made when the database is in MOUNT (not OPEN) mode.

There are 2 "downsides" to Backups in NOARCHIVELOG mode :

1.  The database is unavailable (as it is not OPEN) for the duration of the BACKUP DATABASE run.  So, it would be a good idea to make frequent Incremental Level-1 backups as they could be faster (shorter duration) than the Level-0 backups (which could be scheduled during longer maintenance weekend hours)

2. If you lose any datafile(s) (one or more) you have to RESTORE and RECOVER the *whole* database.  You cannot restore and recover individual datafiles for a database in NOARCHIVELOG mode as you would be able to do with backups with ArchiveLogs.


oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 31 20:01:50 2021
Version 19.3.0.0.0

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

connected to target database: HEMANT (DBID=432411782)

RMAN> backup as compressed backupset incremental level 0 database ;

Starting backup at 31-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 01/31/2021 20:02:07
RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

RMAN>
RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 1207958960 bytes

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

RMAN> backup as compressed backupset incremental level 0 database ;

Starting backup at 31-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/HEMANT/system.dbf
input datafile file number=00002 name=/opt/oracle/oradata/HEMANT/sysaux.dbf
input datafile file number=00003 name=/opt/oracle/oradata/HEMANT/undotbs.dbf
input datafile file number=00004 name=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
input datafile file number=00005 name=/opt/oracle/oradata/HEMANT/indx01.dbf
input datafile file number=00010 name=/opt/oracle/oradata/HEMANT/indx02.dbf
input datafile file number=00011 name=/opt/oracle/oradata/HEMANT/indx03.dbf
input datafile file number=00006 name=/opt/oracle/oradata/HEMANT/HR_DATA.dbf
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1 tag=TAG20210131T200317 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 31-JAN-21

Starting Control File and SPFILE Autobackup at 31-JAN-21
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210131-00 comment=NONE
Finished Control File and SPFILE Autobackup at 31-JAN-21

RMAN>
RMAN> exit


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

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 20:05:44 2021
Version 19.3.0.0.0

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


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

SQL> select max(sequence#), current_scn from v$log, v$database group by current_scn;

MAX(SEQUENCE#) CURRENT_SCN
-------------- -----------
865 864084

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /opt/oracle/archivelog/HEMANT
Oldest online log sequence 863
Current log sequence 865
SQL>


At approximately 20:05 on 31-Jan-2021, the database is in NOARCHIVELOG mode. So, an RMAN BACKUP DATABASE command fails when the Database is OPEN.  I must restart the Database Instance in MOUNT (no OPEN) state to run an RMAN Backup.  I am particular to make this backup explicitly a Level-0 backup so that I can later take a Level-1 backup.

The highest Log Sequence# is 865 (the CURRENT Redo Log file) and the SCN is 864084.

Later ...


oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 21:40:27 2021
Version 19.3.0.0.0

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


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

SQL> select max(sequence#), current_scn from v$log, v$database group by current_scn;

MAX(SEQUENCE#) CURRENT_SCN
-------------- -----------
872 869174

SQL> select count(*) from hemant.my_test_table;

COUNT(*)
----------
24554

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 31 21:43:17 2021
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 mount

Oracle instance started
database mounted

Total System Global Area 1207958960 bytes

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

RMAN> backup as compressed backupset incremental level 1 database;

Starting backup at 31-JAN-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=257 device type=DISK
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/HEMANT/system.dbf
input datafile file number=00002 name=/opt/oracle/oradata/HEMANT/sysaux.dbf
input datafile file number=00003 name=/opt/oracle/oradata/HEMANT/undotbs.dbf
input datafile file number=00004 name=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
input datafile file number=00005 name=/opt/oracle/oradata/HEMANT/indx01.dbf
input datafile file number=00010 name=/opt/oracle/oradata/HEMANT/indx02.dbf
input datafile file number=00011 name=/opt/oracle/oradata/HEMANT/indx03.dbf
input datafile file number=00006 name=/opt/oracle/oradata/HEMANT/HR_DATA.dbf
channel ORA_DISK_1: starting piece 1 at 31-JAN-21
channel ORA_DISK_1: finished piece 1 at 31-JAN-21
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1 tag=TAG20210131T214349 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 31-JAN-21

Starting Control File and SPFILE Autobackup at 31-JAN-21
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210131-01 comment=NONE
Finished Control File and SPFILE Autobackup at 31-JAN-21

RMAN>
RMAN> alter database open;

Statement processed

RMAN> exit


Recovery Manager complete.
oracle19c>


So, with further transactions between 20:05 and 21:43, the highest Log Sequence# has gone from 865 to 872 (none of which are Archived) and the Database SCN has gone from 864084 to 869174.
I use the table "HEMANT.MY_TEST_TABLE" as the reference table at this point.

After some time :
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 21:57:21 2021
Version 19.3.0.0.0

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


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

SQL> select max(sequence#), current_scn from v$log, v$database group by current_scn;

MAX(SEQUENCE#) CURRENT_SCN
-------------- -----------
877 870492

SQL> select count(*) from hemant.my_test_table;

COUNT(*)
----------
27554

SQL>


There have been more transactions (incremented Log Sequence#, SCN and Row Count). However, I do not have a fresh backup of the database (and the database does not generate ArchiveLogs).

If I lose some or all of the Datafiles (and, possibly, even the Redo Log and Control Files) :


SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/opt/oracle/oradata/HEMANT/system.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>

RMAN> shutdown abort;

using target database control file instead of recovery catalog
Oracle instance shut down

RMAN> startup nomount;

connected to target database (not started)
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 '/opt/oracle/product/19c/dbhome_1/dbs/c-432411782-20210131-01';

Starting restore at 31-JAN-21
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 31-JAN-21

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

RMAN>
RMAN> restore database;

Starting restore at 31-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

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 00002 to /opt/oracle/oradata/HEMANT/sysaux.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/HEMANT/undotbs.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/HEMANT/indx01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /opt/oracle/oradata/HEMANT/HR_DATA.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/HEMANT/indx02.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1 tag=TAG20210131T200317
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 31-JAN-21

RMAN> recover database noredo;

Starting recover at 31-JAN-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /opt/oracle/oradata/HEMANT/system.dbf
destination for restore of datafile 00002: /opt/oracle/oradata/HEMANT/sysaux.dbf
destination for restore of datafile 00003: /opt/oracle/oradata/HEMANT/undotbs.dbf
destination for restore of datafile 00004: /opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf
destination for restore of datafile 00005: /opt/oracle/oradata/HEMANT/indx01.dbf
destination for restore of datafile 00006: /opt/oracle/oradata/HEMANT/HR_DATA.dbf
destination for restore of datafile 00010: /opt/oracle/oradata/HEMANT/indx02.dbf
destination for restore of datafile 00011: /opt/oracle/oradata/HEMANT/indx03.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1 tag=TAG20210131T214349
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

Finished recover at 31-JAN-21

RMAN>
RMAN> alter database open resetlogs;

Statement processed

RMAN> exit


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

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 31 22:07:53 2021
Version 19.3.0.0.0

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


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

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
870157

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

MAX(SEQUENCE#)
--------------
1

SQL>
SQL> select count(*) from hemant.my_test_table;

COUNT(*)
----------
24554

SQL>


Note that I was able to use the controlfile autobackup. Then, the RESTORE DATABASE command restored datafiles from the Backup Piece(s) /opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1 that contained the Level-0 backup.  The RECOVER DATABASE NOREDO actually copied datafile incremental changes from the Level-1 backup piece /opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1.  

I have to OPEN RESETLOGS because I must discard the Online Redo Logs as they are not consistent with what been restored (the Online Redo Logs, even if still present on disk, are in the "future" of the Recover and I do not have ArchiveLogs to bring the datafiles in sync).  The Redo Logs get reset to Sequence#=1.  The CURRENT_SCN would be slightly higher than the SCN recorded at the time of the backup -- it should not be lower than that at the time of the Incremental Backup.

All new rows inserted in the MY_TEST_TABLE are lost, as the Row Count reverts to 24,554 that was present when the Level-1 backup was taken.  All other transactions (and SCN increments) since the Level-1 Backup are also lost.

Note : It does not matter that I don't use an FRA.  The method is the same whether an FRA is used or not.

This shows the Level-0 and Level-1 backps (I am listing for only 1 datafile).  
oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jan 31 22:20:04 2021
Version 19.3.0.0.0

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

connected to target database: HEMANT (DBID=432411782)

RMAN> list backup of datafile 1;

using target database control file instead of recovery catalog

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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
41 Incr 0 73.66M DISK 00:00:12 31-JAN-21
BP Key: 41 Status: AVAILABLE Compressed: YES Tag: TAG20210131T200317
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/14vm1l86_1_1
List of Datafiles in backup set 41
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 0 Incr 863156 31-JAN-21 NO /opt/oracle/oradata/HEMANT/system.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
43 Incr 1 2.83M DISK 00:00:10 31-JAN-21
BP Key: 43 Status: AVAILABLE Compressed: YES Tag: TAG20210131T214349
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/16vm1r4l_1_1
List of Datafiles in backup set 43
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 869282 31-JAN-21 NO /opt/oracle/oradata/HEMANT/system.dbf

RMAN>


For the Level-0 Backup, the datafile Checkpoint SCN is lower than that I had from the SQL Query because the SQL query was when the database was opened *after* the Backup.
For the Level-1 Backup, the datafile Checkpoint SCN is higher than that had from the SQL Query because the SQL query was before the Backup was taken.
Similarly, the Restored database has a higher SCN because the act of Restore+Recover+Open also increments the Database SCN.


Categories: DBA Blogs

RMAN's CATALOG command

Mon, 2021-01-25 09:14

The CATALOG START WITH command allows you to update the RMAN Repository with information about backup pieces (or archivelogs) in the specified location.  

For example, if older backups have already been purged from RMAN but are now restored from tape, they can be made visible to RMAN with the CATALOG START WITH command.

Another case would be if you relocate backups  to an alternate filesystem or diskgroup and the RMAN repository needs to updated to identify the new location.

If you copy a backup to another server and then restore the controlfile from a different backup, you can have the controlfile updated with information about the copied backups using this command.

You can also take a backup from a Primary database and catalog it to a Standby (e.g. when you want to update the Standby which is significantly lagging). Oracle also allows you to catalog a backup from a Standby into the Primary server if the backup / backups is/are not available on the Primary.


 A few demonstrations :



Demonstration 1 : Relocated Backup Set / BackupPiece for Datafile Backup(s)

SQL> select file#, name, checkpoint_change#
2 from v$datafile
3 where name = '/opt/oracle/oradata/ORCLCDB/users01.dbf'
4 /

FILE# NAME CHECKPOINT_CHANGE#
---------- ------------------------------------------------ ------------------
7 /opt/oracle/oradata/ORCLCDB/users01.dbf 7583758

SQL>
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 22:18:20 2021
Version 19.3.0.0.0

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


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

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

Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jan 25 22:18:26 2021
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> list backup of datafile 7;

using target database control file instead of recovery catalog

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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42 Full 229.31M DISK 00:00:26 14-NOV-20
BP Key: 42 Status: AVAILABLE Compressed: YES Tag: TAG20201114T162700
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp
List of Datafiles in backup set 42
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
7 Full 7343626 14-NOV-20 NO /opt/oracle/oradata/ORCLCDB/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53 Full 229.31M DISK 00:00:26 25-JAN-21
BP Key: 53 Status: AVAILABLE Compressed: YES Tag: TAG20210125T221421
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
List of Datafiles in backup set 53
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
7 Full 7583529 25-JAN-21 NO /opt/oracle/oradata/ORCLCDB/users01.dbf

RMAN>
-- Datafile 7 is currently at a higher SCN (7583758) then the latest backup as of 25-Jan-21





RMAN> crosscheck backup of datafile 7;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=288 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp RECID=53 STAMP=1062800062
Crosschecked 1 objects

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp RECID=42 STAMP=1056472020
Crosschecked 1 objects


RMAN>
----- both backups are no longer available on disk





oracle19c>pwd
/var/tmp/For_Restore
oracle19c>ls -l
total 318016
-rw-r-----. 1 oracle oinstall 9194496 Jan 25 22:14 o1_mf_annnn_TAG20210125T221418_j0xnkv4w_.bkp
-rw-r-----. 1 oracle oinstall 4457984 Jan 25 22:14 o1_mf_annnn_TAG20210125T221418_j0xnkvdk_.bkp
-rw-r-----. 1 oracle oinstall 2251776 Jan 25 22:14 o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp
-rw-r-----. 1 oracle oinstall 62976 Jan 25 22:15 o1_mf_annnn_TAG20210125T221517_j0xnmoj0_.bkp
-rw-r-----. 1 oracle oinstall 240459776 Jan 25 22:14 o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
-rw-r-----. 1 oracle oinstall 69206016 Jan 25 22:14 o1_mf_nnndf_TAG20210125T221421_j0xnkym5_.bkp
oracle19c>
----- backups of 25-Jan have been restored from Tape to /var/tmp/For_Restore





RMAN> catalog start with '/var/tmp/For_Restore';

searching for all files that match the pattern /var/tmp/For_Restore

List of Files Unknown to the Database
=====================================
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkv4w_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkvdk_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221517_j0xnmoj0_.bkp
File Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
File Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnkym5_.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: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkv4w_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkvdk_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp
File Name: /var/tmp/For_Restore/o1_mf_annnn_TAG20210125T221517_j0xnmoj0_.bkp
File Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
File Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnkym5_.bkp

RMAN>
RMAN> list backup of datafile 7;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42 Full 229.31M DISK 00:00:26 14-NOV-20
BP Key: 42 Status: EXPIRED Compressed: YES Tag: TAG20201114T162700
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp
List of Datafiles in backup set 42
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
7 Full 7343626 14-NOV-20 NO /opt/oracle/oradata/ORCLCDB/users01.dbf

BS Key Type LV Size
------- ---- -- ----------
53 Full 229.31M
List of Datafiles in backup set 53
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
7 Full 7583529 25-JAN-21 NO /opt/oracle/oradata/ORCLCDB/users01.dbf

Backup Set Copy #2 of backup set 53
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:26 25-JAN-21 YES TAG20210125T221421

List of Backup Pieces for backup set 53 Copy #2
BP Key Pc# Status Piece Name
------- --- ----------- ----------
64 1 AVAILABLE /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp

Backup Set Copy #1 of backup set 53
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:26 25-JAN-21 YES TAG20210125T221421

List of Backup Pieces for backup set 53 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
53 1 EXPIRED /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp

RMAN>
----- Now RMAN finds that there is one more backup in /var/tmp/For_Restore
----- RMAN also identifies that Backup Set 53 actually has 2 copies -- Copy#2 being in /var/tmp/For_Restore
----- The BackupSet is 53 but the BackupPiece is 53 at the FRA location and 64 for the Copy at /var/tmp/For_Restore
----- So, the CATALOG command has added this copy is a new BackupPiece in the Repository





RMAN> crosscheck backup of datafile 7;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp RECID=42 STAMP=1056472020
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp RECID=64 STAMP=1062800572
Crosschecked 1 objects

crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp RECID=53 STAMP=1062800062
Crosschecked 2 objects


RMAN> delete expired backup of datafile 7;

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
42 42 1 1 EXPIRED DISK /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp
53 53 1 1 EXPIRED DISK /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.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/2021_01_25/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp RECID=53 STAMP=1062800062
Deleted 1 EXPIRED objects

deleted backup piece
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2020_11_14/o1_mf_nnndf_TAG20201114T162700_htz56nnc_.bkp RECID=42 STAMP=1056472020
Deleted 1 EXPIRED objects


RMAN> list backup of datafile 7;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53 Full 229.31M DISK 00:00:26 25-JAN-21
BP Key: 64 Status: AVAILABLE Compressed: YES Tag: TAG20210125T221421
Piece Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
List of Datafiles in backup set 53
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
7 Full 7583529 25-JAN-21 NO /opt/oracle/oradata/ORCLCDB/users01.dbf

RMAN>
----- after running CROSSCHECK and DELETE EXPIRED, RMAN now identifies that Backupset 53 has only one BackupPiece at /var/tmp/For_Restore
----- Any attempt to RESTORE DATAFILE 7 would now use this BackupPiece




Demonstration 2 : Relocated ArchiveLog and Backup of ArchiveLog

RMAN> list archivelog from  sequence 119 until sequence 119;

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

Key Thrd Seq S Low Time
------- ---- ------- - ---------
286 1 119 A 25-JAN-21
Name: /opt/oracle/archivelog/ORCLCDB/1_119_1036108814.dbf


RMAN> list backup of archivelog from sequence 119 until sequence 119;


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


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
51 2.15M DISK 00:00:01 25-JAN-21
BP Key: 51 Status: AVAILABLE Compressed: YES Tag: TAG20210125T221418
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp

List of Archived Logs in backup set 51
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 119 7582383 25-JAN-21 7583492 25-JAN-21

RMAN>
RMAN> crosscheck archivelog from sequence 119 until sequence 119;

released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=288 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK
validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_119_1036108814.dbf RECID=286 STAMP=1062800057
Crosschecked 1 objects


RMAN> crosscheck backup of archivelog from sequence 119 until sequence 119;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=51 STAMP=1062800060
Crosschecked 1 objects


RMAN>
----- The CROSSCHECK command finds that both the ArchiveLog and it's backup are missing





RMAN> catalog start with '/var/tmp/ArchLogs_Restore/';

searching for all files that match the pattern /var/tmp/ArchLogs_Restore/

List of Files Unknown to the Database
=====================================
File Name: /var/tmp/ArchLogs_Restore/1_119_1036108814.dbf
File Name: /var/tmp/ArchLogs_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.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: /var/tmp/ArchLogs_Restore/1_119_1036108814.dbf
File Name: /var/tmp/ArchLogs_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp

RMAN> crosscheck archivelog from sequence 119 until sequence 119;

released channel: ORA_DISK_1
released channel: ORA_DISK_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=288 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=46 device type=DISK
validation succeeded for archived log
archived log file name=/var/tmp/ArchLogs_Restore/1_119_1036108814.dbf RECID=299 STAMP=1062801628
Crosschecked 1 objects

validation failed for archived log
archived log file name=/opt/oracle/archivelog/ORCLCDB/1_119_1036108814.dbf RECID=286 STAMP=1062800057
Crosschecked 1 objects


RMAN> crosscheck backup of archivelog from sequence 119 until sequence 119;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=51 STAMP=1062800060
Crosschecked 1 objects

crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/var/tmp/ArchLogs_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=66 STAMP=1062801628
Crosschecked 1 objects


RMAN>
RMAN> delete expired backup of archivelog from sequence 119 until sequence 119;

using channel ORA_DISK_1
using channel ORA_DISK_2

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
51 51 1 1 EXPIRED DISK /opt/oracle/FRA/ORCLCDB/ORCLCDB/backupset/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.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/2021_01_25/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=51 STAMP=1062800060
Deleted 1 EXPIRED objects


RMAN> crosscheck backup of archivelog from sequence 119 until sequence 119;

using channel ORA_DISK_1
using channel ORA_DISK_2
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/var/tmp/ArchLogs_Restore/o1_mf_annnn_TAG20210125T221418_j0xnkwy7_.bkp RECID=66 STAMP=1062801628
Crosschecked 1 objects


RMAN>
----- After I CROSSCHECK in the new (restored) location, RMAN finds the ArchiveLog and it's backup
----- I can DELETE the EXPIRED backup
----- (note that the missing ArchiveLog /opt/oracle/archivelog/ORCLCDB/1_119_1036108814.dbf is no longer listed as the CROSSCHECK had already marked it as "validation failed")



Demonstration 3 : Datafile Backup from Standby available at Primary

----- Backup of Datafile 7 taken at the Standby
RMAN> backup as compressed backupset datafile 7 format '/var/tmp/For_Primary/datafile_7.bkp';

Starting backup at 25-JAN-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=264 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-JAN-21
channel ORA_DISK_1: finished piece 1 at 25-JAN-21
piece handle=/var/tmp/For_Primary/datafile_7.bkp tag=TAG20210125T225828 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-JAN-21

Starting Control File and SPFILE Autobackup at 25-JAN-21
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2021_01_25/o1_mf_s_1062802630_j0xq4pmm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-JAN-21

RMAN>
----- The backup is then copied over to the Primary Server





RMAN> catalog start with '/var/tmp/From_Standby/';
RMAN> catalog start with '/var/tmp/From_Standby/';

searching for all files that match the pattern /var/tmp/From_Standby/

List of Files Unknown to the Database
=====================================
File Name: /var/tmp/From_Standby/datafile_7.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: /var/tmp/From_Standby/datafile_7.bkp

RMAN> list backup of datafile 7;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
60 Full 229.31M DISK 00:00:26 25-JAN-21
BP Key: 70 Status: AVAILABLE Compressed: YES Tag: TAG20210125T221421
Piece Name: /var/tmp/For_Restore/o1_mf_nnndf_TAG20210125T221421_j0xnky0z_.bkp
List of Datafiles in backup set 60
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
7 Full 7583529 25-JAN-21 NO /opt/oracle/oradata/ORCLCDB/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
62 Full 1.18M DISK 00:00:00 25-JAN-21
BP Key: 73 Status: AVAILABLE Compressed: YES Tag: TAG20210125T225828
Piece Name: /var/tmp/From_Standby/datafile_7.bkp
List of Datafiles in backup set 62
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
7 Full 7591636 25-JAN-21 NO /opt/oracle/oradata/ORCLCDB/users01.dbf

RMAN>
----- The Primary now recognises that there are 2 distinct backups of datafile 7
----- That in /var/tmp/For_Restore is as of CheckPoint SCN 7583529 (it has a new BS Key and BackupPiece as I have deleted and re-cataloged it for this, third, demo)
----- The one from the Standby at /var/tmp/From_Standby is at CheckPoint SCN 7591636 -- which is a higher SCN as it is a more recent backup
----- I can actualy use the backup from th Standby and Restore to the Primary





RMAN> sql 'alter database datafile 7 offline';

sql statement: alter database datafile 7 offline

RMAN> restore datafile 7;

Starting restore at 25-JAN-21
using channel ORA_DISK_1
using channel ORA_DISK_2

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 00007 to /opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /var/tmp/From_Standby/datafile_7.bkp
channel ORA_DISK_1: piece handle=/var/tmp/From_Standby/datafile_7.bkp tag=TAG20210125T225828
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-JAN-21

RMAN> sql 'alter database datafile 7 online';

sql statement: alter database datafile 7 online
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 01/25/2021 23:02:55
RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 7 online
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/opt/oracle/oradata/ORCLCDB/users01.dbf'

RMAN> recover datafile 7;

Starting recover at 25-JAN-21
using channel ORA_DISK_1
using channel ORA_DISK_2

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

Finished recover at 25-JAN-21

RMAN> sql 'alter database datafile 7 online';

sql statement: alter database datafile 7 online

RMAN>
----- So, when datafile 7 is corrupt at the Primary, I take it OFFLINE and then issue a RESTORE command
----- RMAN automatically identifies that, of the two backups, the "From_Standby/datafile_7.bkp' is more recent
----- So, the Backup from the Standby can be Restored to the Primary and the datafile brought ONLINE
----- RECOVERy is still required because the Primary database is currently at a higher SCN than the backup of that datafile from the Standby
----- So, the RECOVER command applies all Redo that is for SCN higher than 7591636 that needs to be applied to Datafile 7
----- For the duration when I had datafile 7 OFFLINE I had stopped Database Recovery at the Standby


Thus, there are different uses for the CATALOG START WITH command in RMAN
(what I haven't demonstrated here is restoring a Full Database -- either on the same server or to another server, when the BackupPieces are at an alternate locatoin).


Categories: DBA Blogs

Datapump in Oracle ADB using SQL Developer Web

Thu, 2021-01-21 04:20

 If you have a small schema in the Oracle Cloud Autonomous Database, you can actually run DataPump from SQL Developer Web.  DATA_PUMP_DIR maps to a DBFS mount inside the Oracle Database.


Logged in to my Oracle ADB as "ADMIN"

I check if DATA_PUMP_DIR exists  and I find that it is in dbfs  :

Query


I run a PLSQL Block to export the HEMANT schema using the DBMS_DATAPUMP API :

PLSQL Block


After I drop the two tables in the schema, I run the import using the DBMS_DATAPUMP API and then refresh the list of Tables owned by "HEMANT" :

PL/SQL Block


This method is a quick way of using the Autonomous Database itself when you don't have an external Object Store to hold the Datapump file.  So, I'd use this only for very small schemas as the dump is itself loaded into DBFS.


The PLSQL Code is :



REM  Based on Script posted by Dick Goulet, posted to oracle-l@freelists.org
REM With modifications by me.
REM Hemant K Chitale

REM Export schema "HEMANT"
declare
h1 NUMBER := 0;
h2 varchar2(1000);
ex boolean := TRUE;
fl number := 0;
schema_exp varchar2(1000) := 'in(''HEMANT'')';
f_name varchar2(50) := 'My_DataPump';
dp_mode varchar2(100) := 'export';
blksz number := 0;
SUCCESS_WITH_INFO exception;
begin
utl_file.fgetattr('DATA_PUMP_DIR', dp_mode||'.log', ex, fl, blksz);
if(ex = TRUE) then utl_file.fremove('DATA_PUMP_DIR',dp_mode||'.log');
end if;
h1 := dbms_datapump.open (operation => 'EXPORT', job_mode => 'SCHEMA', job_name => upper(dp_mode)||'_EXP', version => 'COMPATIBLE');
dbms_datapump.set_parallel(handle => h1, degree => 2);
dbms_datapump.add_file(handle => h1, filename => f_name||'.dmp%U', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
dbms_datapump.add_file(handle => h1, filename => f_name||'.log', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
dbms_datapump.set_parameter(handle => h1, name => 'INCLUDE_METADATA', value => 1);
dbms_datapump.metadata_filter(handle=>h1, name=>'SCHEMA_EXPR',value=>schema_exp);
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
dbms_datapump.wait_for_job(handle=>h1, job_state=>h2);
exception
when SUCCESS_WITH_INFO THEN NULL;
when others then
h2 := sqlerrm;
if(h1 != 0) then dbms_datapump.stop_job(h1,1,0,0);
end if;
dbms_output.put_line(h2);
end;





REM Import schema "HEMANT"
declare
h1 NUMBER := 0;
h2 varchar2(1000);
ex boolean := TRUE;
fl number := 0;
schema_exp varchar2(1000) := 'in(''HEMANT'')';
f_name varchar2(50) := 'My_DataPump';
dp_mode varchar2(100) := 'import';
blksz number := 0;
SUCCESS_WITH_INFO exception;
begin
utl_file.fgetattr('DATA_PUMP_DIR', dp_mode||'.log', ex, fl, blksz);
if(ex = TRUE) then utl_file.fremove('DATA_PUMP_DIR',dp_mode||'.log');
end if;
h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA', job_name => upper(dp_mode)||'_EXP');
dbms_datapump.set_parallel(handle => h1, degree => 2);
dbms_datapump.add_file(handle => h1, filename => f_name||'.dmp%U', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
dbms_datapump.add_file(handle => h1, filename => f_name||'.log', directory => 'DATA_PUMP_DIR', filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
dbms_datapump.set_parameter(handle => h1, name => 'TABLE_EXISTS_ACTION', value=>'SKIP');
dbms_datapump.metadata_filter(handle=>h1, name=>'SCHEMA_EXPR',value=>schema_exp);
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
dbms_datapump.wait_for_job(handle=>h1, job_state=>h2);
exception
when SUCCESS_WITH_INFO THEN NULL;
when others then
h2 := sqlerrm;
if(h1 != 0) then dbms_datapump.stop_job(h1,1,0,0);
end if;
dbms_output.put_line(h2);
end;



Again, I emphasise that this is only for small dumps.  


Categories: DBA Blogs

Configuring Transparent Data Encryption -- 2 : For Columns

Thu, 2021-01-14 05:15
The previous demo of TDE in 19c was for a full Tablespace (converting an existing, non-TDE, Tablespace to an Encrypted Tablespace).

Pre-creating a Table with an Encrypted column would be straightforward :

CREATE TABLE employees (
emp_id number primary key,
first_name varchar2(128),
last_name varchar2(128),
national_id_no varchar2(18) encrypt,
salary number(6) )
tablespace hr_data
/


This encrypts the column with the AES encryption algorithm with a 192-bit key length ("AES192").

But what if you want to encrypt an existing, non-encrypted column ? You can use the MODIFY clause.

ALTER TABLE employees (
MODIFY (national_id_no encrypt)
/


A quick demo :

SQL> create tablespace hr_data datafile '/opt/oracle/oradata/HEMANT/HR_DATA.dbf' size 5M;

Tablespace created.

SQL> CREATE TABLE employees (
2 emp_id number primary key,
3 first_name varchar2(128),
4 last_name varchar2(128),
5 national_id_no varchar2(18),
6 salary number(6) )
7 tablespace hr_data;

Table created.

SQL> ^C

SQL> insert into employees
2 select rownum, 'Hemant', 'Hemant' || to_char(rownum), dbms_random.string('X',12), 1000
3 from dual
4 connect by level "less than" 21 --- "less than" symbol replaced by string to preserve HTML formatting
5 /

20 rows created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> !sync ; sync

SQL>
SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf | more
}|{z
HEMANT
3J?5
HR_DATA
H4J?
AAAAAAAA
Hemant
Hemant1
LH6RUZRISE11
Hemant
Hemant2
DFIN8FZ7B6J0
Hemant
Hemant3
PLJ1R2QYRG2C
Hemant
Hemant4
UT3HB9ALF3B5
Hemant
Hemant5
LQMDUTFB2PTM
Hemant
Hemant6
1IGKV4E78M5J
Hemant
Hemant7
P9TQAV5BC5EM
Hemant
Hemant8
V69U6VZWCK26
Hemant
Hemant9
EOTOQHOB0F45
Hemant
Hemant10
OKMEV89XOQE1
Hemant
Hemant11
0D4L77P3YNF0
Hemant
Hemant12
CTMCLJSKQW82
Hemant
Hemant13
49T0AG7E2Y9X
Hemant
Hemant14
ODEY2J51D8RH
Hemant
Hemant15
R1HFMN34MYLH
Hemant
Hemant16
OXI0LOX161BO
Hemant
Hemant17
2XL44ZJVABGW
Hemant
Hemant18
4BIPWVECBWYO
Hemant
Hemant19
732KA25TZ3KR
Hemant
Hemant20
NN0X92ES90PH
AAAAAAAA

SQL> alter table employees
2 MODIFY (national_id_no encrypt)
3 /

Table altered.

SQL> alter system checkpoint;

System altered.

SQL> !sync ; sync

SQL>

SQL> select emp_id, national_id_no
2 from employees
3 order by 1
4 /

EMP_ID NATIONAL_ID_NO
---------- ------------------
1 LH6RUZRISE11
2 DFIN8FZ7B6J0
3 PLJ1R2QYRG2C
4 UT3HB9ALF3B5
5 LQMDUTFB2PTM
6 1IGKV4E78M5J
7 P9TQAV5BC5EM
8 V69U6VZWCK26
9 EOTOQHOB0F45
10 OKMEV89XOQE1
11 0D4L77P3YNF0
12 CTMCLJSKQW82
13 49T0AG7E2Y9X
14 ODEY2J51D8RH
15 R1HFMN34MYLH
16 OXI0LOX161BO
17 2XL44ZJVABGW
18 4BIPWVECBWYO
19 732KA25TZ3KR
20 NN0X92ES90PH

20 rows selected.

SQL>
SQL> select emp_id, dump(national_id_no) col_dump
2 from employees
3 order by emp_id
4 /

EMP_ID COL_DUMP
---------- ------------------------------------------------------
1 Typ=1 Len=12: 76,72,54,82,85,90,82,73,83,69,49,49
2 Typ=1 Len=12: 68,70,73,78,56,70,90,55,66,54,74,48
3 Typ=1 Len=12: 80,76,74,49,82,50,81,89,82,71,50,67
4 Typ=1 Len=12: 85,84,51,72,66,57,65,76,70,51,66,53
5 Typ=1 Len=12: 76,81,77,68,85,84,70,66,50,80,84,77
6 Typ=1 Len=12: 49,73,71,75,86,52,69,55,56,77,53,74
7 Typ=1 Len=12: 80,57,84,81,65,86,53,66,67,53,69,77
8 Typ=1 Len=12: 86,54,57,85,54,86,90,87,67,75,50,54
9 Typ=1 Len=12: 69,79,84,79,81,72,79,66,48,70,52,53
10 Typ=1 Len=12: 79,75,77,69,86,56,57,88,79,81,69,49
11 Typ=1 Len=12: 48,68,52,76,55,55,80,51,89,78,70,48
12 Typ=1 Len=12: 67,84,77,67,76,74,83,75,81,87,56,50
13 Typ=1 Len=12: 52,57,84,48,65,71,55,69,50,89,57,88
14 Typ=1 Len=12: 79,68,69,89,50,74,53,49,68,56,82,72
15 Typ=1 Len=12: 82,49,72,70,77,78,51,52,77,89,76,72
16 Typ=1 Len=12: 79,88,73,48,76,79,88,49,54,49,66,79
17 Typ=1 Len=12: 50,88,76,52,52,90,74,86,65,66,71,87
18 Typ=1 Len=12: 52,66,73,80,87,86,69,67,66,87,89,79
19 Typ=1 Len=12: 55,51,50,75,65,50,53,84,90,51,75,82
20 Typ=1 Len=12: 78,78,48,88,57,50,69,83,57,48,80,72

20 rows selected.

SQL>

SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf | more
}|{z
HEMANT
3J?5
HR_DATA
AAAAAAAA
Hemant
Hemant204
Hemant
Hemant194
Hemant
Hemant184
Hemant
Hemant174[Q#
Hemant
Hemant164
Hemant
Hemant154
$^?[
Hemant
Hemant1448
Hemant
Hemant134
Hemant
Hemant124
Hemant
Hemant114
Hemant
Hemant104J
Hemant
Hemant94
Hemant
Hemant84M
zCAGp
Q(ru
Hemant
Hemant74
$o7tN
Hemant
Hemant6418
( i+W
Hemant
Hemant54
f(cCL
Hemant
Hemant44
Hemant
Hemant34
Hemant
Hemant24
e{_
Hemant
Hemant14
Hemant
Hemant1
LH6RUZRISE11
Hemant
Hemant2
DFIN8FZ7B6J0
Hemant
Hemant3
PLJ1R2QYRG2C
Hemant
Hemant4
UT3HB9ALF3B5
Hemant
Hemant5
LQMDUTFB2PTM
Hemant
Hemant6
1IGKV4E78M5J
Hemant
Hemant7
P9TQAV5BC5EM
Hemant
Hemant8
V69U6VZWCK26
Hemant
Hemant9
EOTOQHOB0F45
Hemant
Hemant10
OKMEV89XOQE1
Hemant
Hemant11
0D4L77P3YNF0
Hemant
Hemant12
CTMCLJSKQW82
Hemant
Hemant13
49T0AG7E2Y9X
Hemant
Hemant14
ODEY2J51D8RH
Hemant
Hemant15
R1HFMN34MYLH
Hemant
Hemant16
OXI0LOX161BO
Hemant
Hemant17
2XL44ZJVABGW
Hemant
Hemant18
4BIPWVECBWYO
Hemant
Hemant19
732KA25TZ3KR
Hemant
Hemant20
NN0X92ES90PH
AAAAAAAA


SQL> select version, version_full from v$instance;

VERSION VERSION_FULL
----------------- -----------------
19.0.0.0.0 19.3.0.0.0

SQL>


When I insert a new row, the plain-text for this is not present. But the old (20) rows plain-text is still present.

SQL> insert into employees
2 values (21,'HemantNew','HemantNew21','ABCDEFGHIJ88',2000);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> !sync;sync

SQL> !strings -a /opt/oracle/oradata/HEMANT/HR_DATA.dbf
}|{z
HEMANT
3J?5
SJ?
HR_DATA
UTJ?
AAAAAAAA
HemantNew
HemantNew214S
Hemant
Hemant204
Hemant
Hemant194
Hemant
Hemant184
Hemant
Hemant174[Q#
Hemant
Hemant164
Hemant
Hemant154
$^?[
Hemant
Hemant1448
Hemant
Hemant134
Hemant
Hemant124
Hemant
Hemant114
Hemant
Hemant104J
Hemant
Hemant94
Hemant
Hemant84M
zCAGp
Q(ru
Hemant
Hemant74
$o7tN
Hemant
Hemant6418
( i+W
Hemant
Hemant54
f(cCL
Hemant
Hemant44
Hemant
Hemant34
Hemant
Hemant24
e{_
Hemant
Hemant14
Hemant
Hemant1
LH6RUZRISE11
Hemant
Hemant2
DFIN8FZ7B6J0
Hemant
Hemant3
PLJ1R2QYRG2C
Hemant
Hemant4
UT3HB9ALF3B5
Hemant
Hemant5
LQMDUTFB2PTM
Hemant
Hemant6
1IGKV4E78M5J
Hemant
Hemant7
P9TQAV5BC5EM
Hemant
Hemant8
V69U6VZWCK26
Hemant
Hemant9
EOTOQHOB0F45
Hemant
Hemant10
OKMEV89XOQE1
Hemant
Hemant11
0D4L77P3YNF0
Hemant
Hemant12
CTMCLJSKQW82
Hemant
Hemant13
49T0AG7E2Y9X
Hemant
Hemant14
ODEY2J51D8RH
Hemant
Hemant15
R1HFMN34MYLH
Hemant
Hemant16
OXI0LOX161BO
Hemant
Hemant17
2XL44ZJVABGW
Hemant
Hemant18
4BIPWVECBWYO
Hemant
Hemant19
732KA25TZ3KR
Hemant
Hemant20
NN0X92ES90PH
AAAAAAAA

SQL>



So, it seems that after I ran the MODIFY to encrypt a column, Oracle created new copies of the 20 rows with encrypted values.  However, the old plain-text (non-encrypted) values are still present in the datafile.

Apparently, those "still present" plain-text representations of the "NATIONAL_ID_NO" column in the datafile are explained in the documentation as :

"Column values encrypted using TDE are stored in the data files in encrypted form. However, these data files may still contain some plaintext fragments, called ghost copies, left over by past data operations on the table. This is similar to finding data on the disk after a file was deleted by the operating system."

You should remove old plaintext fragments that can appear over time.

Old plaintext fragments may be present for some time until the database overwrites the blocks containing such values. If privileged operating system users bypass the access controls of the database, then they might be able to directly access these values in the data file holding the tablespace.

To minimize this risk:

  1. Create a new tablespace in a new data file.

    You can use the CREATE TABLESPACE statement to create this tablespace.

  2. Move the table containing encrypted columns to the new tablespace. You can use the ALTER TABLE.....MOVE statement.

    Repeat this step for all of the objects in the original tablespace.

  3. Drop the original tablespace.

    You can use the DROP TABLESPACE tablespace INCLUDING CONTENTS KEEP DATAFILES statement. Oracle recommends that you securely delete data files using platform-specific utilities.

  4. Use platform-specific and file system-specific utilities to securely delete the old data file. Examples of such utilities include shred (on Linux) and sdelete (on Windows).

Categories: DBA Blogs

Oracle Database 21c

Tue, 2020-12-08 16:05
Oracle Database 21c is now available in the Oracle Cloud.

21c is an Innovation Release so it can be used for testing of the new features.  For Long Term Support, customers still now upgrading should be upgrading to 19c.
Categories: DBA Blogs

How the World Wide Web was created

Thu, 2020-12-03 21:52
Categories: DBA Blogs

Configuring Transparent Data Encryption -- 1 : For a Tablespace

Sat, 2020-11-21 05:49

 Oracle allows TDE (Transparent Data Encryption) for specific (i.e. selected) columns or a full Tablespace.

Here is a quick demo of TDE for a Tablespace.

First, I setup a target tablespace with some data



SQL> connect hemant/hemant
Connected.
SQL> create tablespace TDE_TARGET_TBS datafile '/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf' size 100M;

Tablespace created.

SQL>
SQL> create table TDE_TARGET_TABLE
2 (id_col number,
3 data_col varchar2(50))
4 tablespace TDE_TARGET_TBS
5 /

Table created.

SQL> insert into TDE_TARGET_TABLE
2 select rownum,
3 'MY DATA CONTENT : ' || rownum
4 from dual
5 connect by level "less than" 1001 -- the "less than" symbol replaced by string to preserve formatting for HTML
6 /

1000 rows created.

SQL> commit;

Commit complete.

SQL>


oracle19c>dd if=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf ibs=8192 skip=128 > /tmp/dump_of_TDE_TARGET_TBS.TXT
12673+0 records in
202768+0 records out
103817216 bytes (104 MB) copied, 1.10239 s, 94.2 MB/s
oracle19c>
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS.TXT |grep 'CONTENT' |head -5
MY DATA CONTENT : 944,
MY DATA CONTENT : 945,
MY DATA CONTENT : 946,
MY DATA CONTENT : 947,
MY DATA CONTENT : 948,
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS.TXT |grep 'CONTENT' | wc -l
1000
oracle19c>


I inserted 1000 rows with the text "MY DATA CONTENT" and it is visible as plain-text when I dump the datafile.   

Note how not all the inserted rows appear to be in physical order -- the "last" 57 "records" (i.e. rows) seem to appear before the first "record" (row) as I show in this video recording of a viewing of the dump.  Never assume physical ordering of data in a datafile or when retrieving output (for ordering the results of a SELECT statement, *always* use the ORDER BY clause)






So, I now intend to encrypt the tablespace.

Step 1 : Specify the ENCRYPTION WALLET LOCATION
In earlier releases, this is specified in the sqlnet.ora file like this :

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=/home/oracle/wallet))) -- or this could be any other folder, or defaulting to $ORACLE_BASE/admin/db_unique_name/wallet


However, in 19c, Oracle recommends using the KEYSTORE_CONFIGURATION attribute of the TDE_CONFIGURATION initialization parameter after setting the WALLET_ROOT.


SQL> show parameter tde

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string
SQL> show parameter wallet

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string
SQL>
SQL> alter system set wallet_root='/opt/oracle/product/19c/dbhome_1/TDE_WALLETS' scope=SPFILE;

System altered.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
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
Database mounted.
Database opened.
SQL>
SQL> show parameter wallet

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string /opt/oracle/product/19c/dbhome
_1/TDE_WALLETS
SQL>
SQL> alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=SPFILE;

System altered.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
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
Database mounted.
Database opened.
SQL> show parameter wallet

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string /opt/oracle/product/19c/dbhome
_1/TDE_WALLETS
SQL> show parameter tde

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string KEYSTORE_CONFIGURATION=FILE
SQL>


In the case of this database, since I had not earlier configured these parameters, I had to do a manual restart for them to take effect (Note : "WALLET_ROOT" must have been configured before "TDE_CONFIGURATION" can be set, that is why I had to do an additional restart between setting the two parameters).
I have deliberately configured WALLET_ROOT to a non-default/standard location.


Step 2 : Create the KEYSTORE (under WALLET_ROOT)

This is where I actually  create the Wallet.  The syntax specifies KEYSTORE location, but can default to WALLET_ROOT as I have already defined it. I can also create an Auto-Login Keystore

SQL> administer key management create keystore identified by mysecretpassword;

keystore altered.

SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde
total 4
-rw-------. 1 oracle oinstall 2555 Nov 21 18:38 ewallet.p12

SQL>
SQL> administer key management create LOCAL auto_login keystore
2 from keystore '/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde'
3 identified by mysecretpassword
4 /

keystore altered.

SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde
total 8
-rw-------. 1 oracle oinstall 2600 Nov 21 18:49 cwallet.sso
-rw-------. 1 oracle oinstall 2555 Nov 21 18:38 ewallet.p12

SQL>
SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
------------------------------ -------------------- --------- -------- --------- ----------
FILE
/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/
OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE NONE UNDEFINED 0


SQL>


In this case, "ewallet.p12" is the Password Protected Keystore and "cwallet.sso" is the Auto-Login Keystore (created LOCALly only, not for remote servers/clients).


Step 3 : OPEN the Keystore  (only if it is NOT already OPEN) 

I can see that the Keystore is already OPEN (from the query on v$encryption_wallet) but I could attempt OPENing it with :


SQL> administer key management set keystore open
2 identified by mysecretpassword
3 /
administer key management set keystore open
*
ERROR at line 1:
ORA-28354: Encryption wallet, auto login wallet, or HSM is already open


SQL>
SQL> !oerr ora 28354
28354, 0000, "Encryption wallet, auto login wallet, or HSM is already open"
// *Cause: Encryption wallet, auto login wallet, or HSM was already opened.
// *Action: None.
//

SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
------------------------------ -------------------- --------- -------- --------- ----------
FILE
/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/
OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE NONE UNDEFINED 0


SQL>


Step 4 : Setup the Master Encryption Key

Now, I setup the Master Key (and also backup the existing key file)


SQL> administer key management set key
2 using tag 'For_Tablespace_TDE'
3 force keystore -- because I am using LOCAL_AUTOLOGIN
4 identified by mysecretpassword
5 with backup using 'tde_key_backup'
6 /

keystore altered.

SQL>
SQL> !ls -l /opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde
total 20
-rw-------. 1 oracle oinstall 4232 Nov 21 19:04 cwallet.sso
-rw-------. 1 oracle oinstall 2555 Nov 21 19:04 ewallet_2020112111043216_tde_key_backup.p12
-rw-------. 1 oracle oinstall 4171 Nov 21 19:04 ewallet.p12

SQL>
SQL> select * from v$encryption_wallet;

WRL_TYPE
--------------------
WRL_PARAMETER
------------------------------------------------------------------------------------------------------------------------------------
STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
------------------------------ -------------------- --------- -------- --------- ----------
FILE
/opt/oracle/product/19c/dbhome_1/TDE_WALLETS/tde/
OPEN LOCAL_AUTOLOGIN SINGLE NONE NO 0


SQL>
SQL> select key_id, creation_time, keystore_type, tag from v$encryption_keys;

KEY_ID
------------------------------------------------------------------------------
CREATION_TIME KEYSTORE_TYPE
--------------------------------------------------------------------------- -----------------
TAG
------------------------------------------------------------------------------------------------------------------------------------
AaHRyuP8yE+ivzI/hZHcOdoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
21-NOV-20 07.04.32.347090 PM +08:00 SOFTWARE KEYSTORE
For_Tablespace_TDE


SQL>


Now I am ready the encrypt my Tablespace.


Step 5 :  Online Encryption of Tablespace
This is a 19c feature, earlier versions required Offline Encryption.
This method creates a new datafile with encrypted data

SQL> alter tablespace TDE_TARGET_TBS
2 encryption online
3 using 'AES192'
4 encrypt file_name_convert = ('/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS.dbf','/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf')
5 /

Tablespace altered.

SQL>

oracle19c>pwd
/opt/oracle/oradata/HEMANT
oracle19c>ls -l TDE*
-rw-r-----. 1 oracle oinstall 104865792 Nov 21 19:42 TDE_TARGET_TBS_encrypted.dbf
oracle19c>
oracle19c>dd if=/opt/oracle/oradata/HEMANT/TDE_TARGET_TBS_encrypted.dbf ibs=8192 skip=128 > /tmp/dump_of_TDE_TARGET_TBS_encrypted.TXT
12673+0 records in
202768+0 records out
103817216 bytes (104 MB) copied, 0.414517 s, 250 MB/s
oracle19c>
oracle19c>strings -a /tmp/dump_of_TDE_TARGET_TBS_encrypted.TXT |grep 'CONTENT' |head -5
oracle19c>

SQL> l
1 select t.name, e.ts#, e.encryptionalg, e.encryptedts, e.key_version, e.status
2 from v$tablespace t, v$encrypted_tablespaces e
3* where t.ts#=e.ts#
SQL> /

NAME TS# ENCRYPT ENC KEY_VERSION STATUS
------------------------------ ---------- ------- --- ----------- ----------
TDE_TARGET_TBS 8 AES192 YES 1 NORMAL

SQL>



Oracle has replaced the TDE_TARGET_TBS.dbf file with TDE_TARGET_TBS_encrypted.dbf file. The new file does NOT have any plain-text values "CONTENT"

For subsequent Tablespaces, Steps 1 to 4 would not be required.



Categories: DBA Blogs

Active Data Guard and DML Redirection

Sat, 2020-11-14 03:15

 Active Data Guard (also known as "ADG") allows you to open a Standby Database in Read Only mode and query it while Media Recovery (i.e. Redo Apply from the Primary) is concurrently running.

Caveat : ADG requires purchase of additional licences to use this feature on a Standby Database.

If you issue the commands from sqlplus and not from dgmrl, you must first stop Media Recovery before you OPEN the database and then re-enable it on the Standby :


SQL> select FORCE_LOGGING, FLASHBACK_ON,DATABASE_ROLE,PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE
2 from v$database;

FORCE_LOGGING FLASHBACK_ON DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
--------------------------------------- ------------------ ---------------- -------------------- --------------------
OPEN_MODE
--------------------
YES NO PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
MOUNTED


SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select FORCE_LOGGING, FLASHBACK_ON,DATABASE_ROLE,PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE
2 from v$database;

FORCE_LOGGING FLASHBACK_ON DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
--------------------------------------- ------------------ ---------------- -------------------- --------------------
OPEN_MODE
--------------------
YES NO PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
READ ONLY


SQL>
SQL> alter pluggable database orclpdb1 open read only;

Pluggable database altered.

SQL>
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>


Thereafter, you would be able to query the Standby Database.  DML (INSERT, UPDATE, DELETE) and DDL (CREATE, DROP, ALTER etc) are *not* allowed by default.  

19c does have a DML Redirection feature ADG_REDIRECT_DML which I demonstrate below :

SQL> connect hemant/hemant@stdbypdb1
Connected.
SQL> select * from x;

D
-
X

SQL> delete x;
delete x
*
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access


SQL> ALTER SESSION ENABLE ADG_REDIRECT_DML;

Session altered.

SQL> delete x;

1 row deleted.

SQL> commit;

Commit complete.

SQL>


The actual execution of the DML will take some time as Oracle has to actually push it to the Primary database for execution and then wait for the Redo Apply to replicate back to the Standby.

PLSQL also can be executed with ADG_REDIRECT_PLSQL     which I am not demonstrating here.

Note :  According to Oracle's documentation the DML Redirect feature is for "read-mostly applications, which occasionally execute DMLs, on the standby database."    .  I wouldn't advise trying this for all sessions at the Instance ("alter system") level but only to be used occasionally at session level.

Categories: DBA Blogs

Compressing VARCHAR2 and CHAR column data

Sun, 2020-11-08 09:21

 Most applications that I see use VARCHAR2 columns.  However, there are some (including many built by "in house database designers" that use CHAR columns).  CHAR columns are for Fixed-Length character strings.  Any inserted values that are shorter than the defined length are right-padded with blanks.  Comparison semantics are also different in that VARCHAR2 uses non-padded comparison while CHAR uses blank-padding for comparison.

In this demonstration, I'd like to focus on compressibility of columns defined as VARCHAR2 and CHAR.

These scripts are executed in 19c on Linux.


First with VARCHAR2


SQL> create tablespace VC2_TBS datafile '/opt/oracle/oradata/HEMANT/VC2_TBS.dbf' size 2G;

Tablespace created.

SQL>
SQL> create table VC2_TABLE
2 (id_col number(8), data_col_1 varchar2(50), data_col_2 varchar2(50), data_col_3 varchar2(50))
3 pctfree 0
4 tablespace VC2_TBS
5 /

Table created.

SQL>
SQL> declare
2 l_c number;
3 begin
4 for l_c in 1 .. 100
5 loop
6 insert into VC2_TABLE
7 select rownum, dbms_random.string('X',25), dbms_random.string('X',15), dbms_random.string('X',10)
8 from dual
9 connect by level "less than sign" 100001; -- "less than sign" replaced by words to preserve HTML formatting
10 commit;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
2 from user_tables
3 where table_name = 'VC2_TABLE'
4 /

COMPRESS COMPRESS_FOR NUM_ROWS BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
DISABLED 10,000,000 78,437 58

SQL>
SQL> select bytes/1048576
2 from user_segments
3 where segment_name = 'VC2_TABLE'
4 /

BYTES/1048576
-------------
616

SQL>
SQL> pause For RMAN BACKUP AS COMPRESSED BACKUPSET
For RMAN BACKUP AS COMPRESSED BACKUPSET

RMAN> backup as compressed backupset tablespace VC2_TBS;

Starting backup at 08-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=45 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/opt/oracle/oradata/HEMANT/VC2_TBS.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-20
channel ORA_DISK_1: finished piece 1 at 08-NOV-20
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0svf1p8h_1_1 tag=TAG20201108T221753 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 08-NOV-20


oracle19c>ls -l /opt/oracle/product/19c/dbhome_1/dbs/0svf1p8h_1_1
-rw-r-----. 1 oracle oinstall 386490368 Nov 8 22:18 /opt/oracle/product/19c/dbhome_1/dbs/0svf1p8h_1_1
oracle19c>
-- approx 369MB RMAN Compressed Backup

SQL>
SQL> alter table VC2_TABLE
2 move compress
3 /

Table altered.

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

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
2 from user_tables
3 where table_name = 'VC2_TABLE'
4 /

COMPRESS COMPRESS_FOR NUM_ROWS BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
ENABLED BASIC 10,000,000 79,027 58

SQL>
SQL> select bytes/1048576
2 from user_segments
3 where segment_name = 'VC2_TABLE'
4 /

BYTES/1048576
-------------
624

SQL>


So these are the facts for the table with 3 VARCHAR2(50) columns with random 25, 15 and 10 character-strings. 
Note : I deliberately chose dbms_random.string to ensure that I'd get very few (if any) repeatable values that are compressible themselves.

10million rows of Average Row Length of 58bytes
616MB Segment (78,437 blocks equal 613MB approx).
RMAN Compressed Size of the Datafile 369MB (approx 60% of the Segment Size)
BASIC Compression Size of the Table :  624MB  (i.e. no compression achieved because I used random strings)



Next with CHAR


SQL> create tablespace CHAR_TBS datafile '/opt/oracle/oradata/HEMANT/CHAR_TBS.dbf' size 2G;  -- subsequently resized to 4000M

Tablespace created.

SQL>
SQL> create table CHAR_TABLE
2 (id_col number(8), data_col_1 char(50), data_col_2 char(50), data_col_3 char(50))
3 pctfree 0
4 tablespace CHAR_TBS
5 /

Table created.

SQL>
SQL> declare
2 l_c number;
3 begin
4 for l_c in 1 .. 100
5 loop
6 insert into CHAR_TABLE
7 select rownum, dbms_random.string('X',25), dbms_random.string('X',15), dbms_random.string('X',10)
8 from dual
9 connect by level "less than" 100001; -- "less than sign" replaced by words to preserve HTML formatting
10 commit;
11 end loop;
12 end;
13 /

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
2 from user_tables
3 where table_name = 'CHAR_TABLE'
4 /

COMPRESS COMPRESS_FOR NUM_ROWS BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
DISABLED 10,000,000 204,116 158

SQL>
SQL> select bytes/1048576
2 from user_segments
3 where segment_name = 'CHAR_TABLE'
4 /

BYTES/1048576
-------------
1600

SQL>
SQL> pause For RMAN BACKUP AS COMPRESSED BACKUPSET
For RMAN BACKUP AS COMPRESSED BACKUPSET


RMAN> backup as compressed backupset tablespace CHAR_TBS;

Starting backup at 08-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/opt/oracle/oradata/HEMANT/CHAR_TBS.dbf
channel ORA_DISK_1: starting piece 1 at 08-NOV-20
channel ORA_DISK_1: finished piece 1 at 08-NOV-20
piece handle=/opt/oracle/product/19c/dbhome_1/dbs/0vvf1r7f_1_1 tag=TAG20201108T225127 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 08-NOV-20


oracle19c>ls -l /opt/oracle/product/19c/dbhome_1/dbs/0vvf1r7f_1_1
-rw-r-----. 1 oracle oinstall 441876480 Nov 8 22:51 /opt/oracle/product/19c/dbhome_1/dbs/0vvf1r7f_1_1
oracle19c>
-- appox 421MB RMAN Compressed Backup

SQL>
SQL> alter table CHAR_TABLE
2 move compress
3 /

Table altered.

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

PL/SQL procedure successfully completed.

SQL>
SQL> select compression, compress_for, num_rows, blocks, avg_row_len
2 from user_tables
3 where table_name = 'CHAR_TABLE'
4 /

COMPRESS COMPRESS_FOR NUM_ROWS BLOCKS AVG_ROW_LEN
-------- ------------------------------ ------------ ------------ -----------
ENABLED BASIC 10,000,000 204,766 158

SQL>
SQL> select bytes/1048576
2 from user_segments
3 where segment_name = 'CHAR_TABLE'
4 /

BYTES/1048576
-------------
1600

SQL>

So these are the facts for the table with 3 CHAR(50) columns with random 25, 15 and 10 character-strings. 
 Note : I deliberately chose dbms_random.string to ensure that I'd get very few (if any) repeatable values that are compressible themselves. 

10million rows of Average Row Length of 158bytes (up from 58 bytes for the VARCHAR2 columns) 
1600MB Segment (204,116 blocks equal 1595MB approx). 
RMAN Compressed Size of the Datafile 421MB  (approx 26% of the Segment Size)
BASIC Compression Size of the Table : 1600MB (i.e. no compression achieved, because I used random strings ? -- shouldn't the right-padded blanks be compressed ?

 So, in this test comparing random strings in VARCHAR2 and CHAR, quite obviously the table with CHAR columns took up much more space.
BASIC Compression didn't achieve anything (again : because I used random strings?)

RMAN default compression ("CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ") achieved better compression with CHAR

Of course, since I used random strings and you might have actual data that is compressible and the length of the actual strings inserted into the CHAR columns may be different in your production / test table, you would see different levels of compression achieved.

The point is that compression success depends not just on the type of data but can be different whether you use BASIC compression in the tablespace or the default BASIC in RMAN.

Note :  Given RMAN's default behaviour of "unused block compression" do not expect RMAN to have to backup all the blocks in entire 2GB or 4000MB datafile  -- particularly because I specifically create the tablespace just before the test and it doesn't have any other objects (segments).


Categories: DBA Blogs

Pages