How to move an Oracle database to another host WITHOUT the need to open it with RESETLOGS

Português/English

It’s well known the technique of moving an Oracle database to a new host, as explained for example in this documentation link. It’s also well known the technique to keep updating the target with all the changes in the source until the cutover window.

Basically, the steps are the following:

  1. Prepare the new host (install Oracle software, etc).
  2. Copy all Oracle configuration files (spfile, password file, listener.ora, tnsnames.ora, etc) and make the necessary adjustments.
  3. Perform a full backup of the source database.
  4. Copy the backup files to the target and restore the database from it.
  5. While you are waiting for the cutover window, perform periodic incremental backups in the source and update the target with them.
  6. In the cutover window, perform a last incremental backup of the source with the database mounted, then copy and apply it to the target.
  7. Open the database with RESETLOGS option.

The main problem with this approach is the fact that you end the process by opening the target database with RESETLOGS, which make it to be a new incarnation, almost as if it was a new database. In practice you need to start your backup history from scratch, and if something goes wrong in the target you don’t have the option to revert to the old database, unless you accept to loose all the changes made in the target after the database was open.

If you are using Oracle Enterprise Edition, you can configure a DataGuard between both databases, so you can switch to the new server almost without any downtime, and also revert to the source without any data loss at any moment if something goes wrong.

But, what if you don’t have Oracle EE license, or for any other reason you don’t want to use DataGuard? Is there a way to open the database without the RESETLOGS option and also revert to the source if necessary?

I found very little documentation about this, so this post is to explain why usually a RESETLOGS would be necessary, and how to overcome this constraint.

First of all, some information about my environment:

  • I am using two Oracle Linux virtual servers in AWS with Oracle 12.2 installed.
  • The servers are named ora12c-1 (the source) and ora12c-2 (the target). The Linux prompt identifies which server I am working on.
  • In the source server I have only the database orcldb that will be used here. In the target there is no database.
  • Both servers can communicate through the network, so I can easily transfer files between them.
  • All the environment variables are configured properly for the oracle OS user. So whenever I connect to a database it will always be the right one.
  • The database files, FRA, backups and everything else will be copied to the same paths in the target.

So let’s get started.

1. Prepare the source database

I just want to check if the database is in ARCHIVELOG mode so I can execute all the backups online:

[oracle@ora12c-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 11:33:13 2019

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

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

SQL> select name, log_mode from v$database;

NAME	  LOG_MODE
--------- ------------
ORCLCDB   ARCHIVELOG

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

2. Prepare the target server

Here I will just create the directories that will be used by the target database, the same way they exist in the source:

[oracle@ora12c-2 ~]$ mkdir -p $ORACLE_BASE/admin/orclcdb/adump
[oracle@ora12c-2 ~]$ mkdir -p $ORACLE_BASE/fast_recovery_area/orclcdb/ORCLCDB

3. Copy the SPFILE, password file and network configuration files to the target and adjust them

Let’s copy all the Oracle configuration files to the target:

[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem $ORACLE_HOME/dbs/spfileorclcdb.ora oracle@ora12c-2:$ORACLE_HOME/dbs/
spfileorclcdb.ora 100% 3584 4.9MB/s 00:00 
[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem $ORACLE_HOME/dbs/orapworclcdb oracle@ora12c-2:$ORACLE_HOME/dbs/
orapworclcdb 100% 3584 2.9MB/s 00:00 
[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem $ORACLE_HOME/network/admin/*ora oracle@ora12c-2:$ORACLE_HOME/network/admin/
listener.ora 100% 336 264.6KB/s 00:00 
sqlnet.ora 100% 202 364.2KB/s 00:00 
tnsnames.ora 100% 494 528.6KB/s 00:00

After copying these files, you should review the content of the network files and make the necessary adjustments considering the host server is different.

4. Perform a full backup of the source database

The first backup of the source database needs to be a full backup. Actually, it must be an incremental Level 0 backup in order to allow the subsequent Level 1 to consider it.

For the method we are going to use, there is no need to backup the Archived Logs, because the target database will be updated only with incremental backups.

[oracle@ora12c-1 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 12:17:15 2019

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

connected to target database: ORCLCDB (DBID=2774882222, not open)

RMAN> backup incremental level 0 as compressed backupset database;

Starting backup at 25-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 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=/u01/app/oracle/oradata/orclcdb/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orclcdb/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orclcdb/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-19
channel ORA_DISK_1: finished piece 1 at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzrqyb_.bkp tag=TAG20190325T121727 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
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=00010 name=/u01/app/oracle/oradata/orclcdb/pdb1/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/orclcdb/pdb1/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/orclcdb/pdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-19
channel ORA_DISK_1: finished piece 1 at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/84EA94D2E64025B8E053AB5C1FAC5F38/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzt54y_.bkp tag=TAG20190325T121727 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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=00006 name=/u01/app/oracle/oradata/orclcdb/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orclcdb/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-19
channel ORA_DISK_1: finished piece 1 at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/84EA3D138B311A47E053AB5C1FACC44E/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzty8l_.bkp tag=TAG20190325T121727 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 25-MAR-19

Starting Control File and SPFILE Autobackup at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003825815_g9kzvqgf_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-19

RMAN> exit

Recovery Manager complete.

I will take note of the controlfile autobackup to restore from it in the target: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003825815_g9kzvqgf_.bkp .

5. Copy the backup files to the target

As I will need to copy backup files from the source to the target several times, I will keep the FRA of both servers in sync by using rsync:

[oracle@ora12c-1 ~]$ rsync --progress -avz -e "ssh -i ~/EC2us-east-1.pem" /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/ oracle@ora12c-2:/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/
sending incremental file list
./
84EA3D138B311A47E053AB5C1FACC44E/
84EA3D138B311A47E053AB5C1FACC44E/backupset/
84EA3D138B311A47E053AB5C1FACC44E/backupset/2019_03_25/
84EA3D138B311A47E053AB5C1FACC44E/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzty8l_.bkp
    164,388,864 100%   20.01MB/s    0:00:07 (xfr#1, to-chk=8/18)
84EA94D2E64025B8E053AB5C1FAC5F38/
84EA94D2E64025B8E053AB5C1FAC5F38/backupset/
84EA94D2E64025B8E053AB5C1FAC5F38/backupset/2019_03_25/
84EA94D2E64025B8E053AB5C1FAC5F38/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzt54y_.bkp
    164,503,552 100%   18.03MB/s    0:00:08 (xfr#2, to-chk=5/18)
archivelog/
archivelog/2019_03_25/
autobackup/
autobackup/2019_03_25/
autobackup/2019_03_25/o1_mf_s_1003825815_g9kzvqgf_.bkp
     18,825,216 100%   20.31MB/s    0:00:00 (xfr#3, to-chk=2/18)
backupset/
backupset/2019_03_25/
backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzrqyb_.bkp
    327,761,920 100%   18.43MB/s    0:00:16 (xfr#4, to-chk=0/18)
onlinelog/

sent 627,374,884 bytes  received 163 bytes  19,303,847.60 bytes/sec
total size is 675,479,552  speedup is 1.08

6. Unset OMF in the target

If you are using Oracle Managed Files, I would recommend you to temporarily unset it in the target. If OMF is set, when you perform a restore RMAN will create the new data files with a different suffix. Then we would need to execute SET NEWNAME statements in order for the control file to recognise this new data files. As the control file will be recopied in the cutover, these statements would need to be reissued thus increasing the difficulty and the probability of errors.

[oracle@ora12c-2 ~]$ echo $ORACLE_SID
orclcdb
[oracle@ora12c-2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 12:45:37 2019

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size		    8793496 bytes
Variable Size		  297796200 bytes
Database Buffers	  109051904 bytes
Redo Buffers		    3788800 bytes
SQL> alter system set db_create_file_dest='' scope=both;

System altered.

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

7. Restore the control file and mount the database

Let’s restore the control file from the autobackup taken in the source, and then mount the database:

[oracle@ora12c-2 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 12:48:59 2019

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

connected to target database: ORCLCDB (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003825815_g9kzvqgf_.bkp';

Starting restore at 25-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/orclcdb/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orclcdb/control02.ctl
Finished restore at 25-MAR-19

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

9. Restore the database

Now it’s time to restore the database, i.e., the data files. Please note that, as the control file restored was backed up after the data files backup sets were created, it has the information about these backup sets and there is no need to catalog anything.

RMAN> restore database;

Starting restore at 25-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 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 /u01/app/oracle/oradata/orclcdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orclcdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orclcdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzrqyb_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzrqyb_.bkp tag=TAG20190325T121727
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
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 00009 to /u01/app/oracle/oradata/orclcdb/pdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/orclcdb/pdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/orclcdb/pdb1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/84EA94D2E64025B8E053AB5C1FAC5F38/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzt54y_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/84EA94D2E64025B8E053AB5C1FAC5F38/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzt54y_.bkp tag=TAG20190325T121727
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orclcdb/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orclcdb/pdbseed/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/84EA3D138B311A47E053AB5C1FACC44E/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzty8l_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/84EA3D138B311A47E053AB5C1FACC44E/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzty8l_.bkp tag=TAG20190325T121727
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 25-MAR-19

RMAN> exit

Recovery Manager complete.

At this point, we have the target database in mount state and updated with all the changes until the moment of the backup.

10. Keep updating the target database with incremental backups

As the source database is still being used and changed over time, it is necessary to apply all these changes to the target. We will do this with periodic incremental backups. They could be executed weekly, daily or even more often, depending on the volume of changes.

For this example, I will simulate two cycles of changes in the database and incremental backups that will contain that changes.

I will create a table TABLE1 to mimic changes in the source database.

[oracle@ora12c-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 13:06:35 2019

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


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

SQL> create table TABLE1 as select * from dba_objects;

Table created.

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

Then I create an incremental level 1 backup.

[oracle@ora12c-1 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 13:09:06 2019

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

connected to target database: ORCLCDB (DBID=2774882222)

RMAN> backup incremental level 1 as compressed backupset database;

Starting backup at 25-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 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=/u01/app/oracle/oradata/orclcdb/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orclcdb/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orclcdb/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-19
channel ORA_DISK_1: finished piece 1 at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T130915_g9l2sx2j_.bkp tag=TAG20190325T130915 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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=00010 name=/u01/app/oracle/oradata/orclcdb/pdb1/sysaux01.dbf
skipping datafile 00010 because it has not changed
input datafile file number=00009 name=/u01/app/oracle/oradata/orclcdb/pdb1/system01.dbf
skipping datafile 00009 because it has not changed
input datafile file number=00011 name=/u01/app/oracle/oradata/orclcdb/pdb1/users01.dbf
skipping datafile 00011 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
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=00006 name=/u01/app/oracle/oradata/orclcdb/pdbseed/sysaux01.dbf
skipping datafile 00006 because it has not changed
input datafile file number=00005 name=/u01/app/oracle/oradata/orclcdb/pdbseed/system01.dbf
skipping datafile 00005 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
Finished backup at 25-MAR-19

Starting Control File and SPFILE Autobackup at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003842582_g9l2tq67_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-19

RMAN> exit


Recovery Manager complete.

Again, more changes come to source database as the form of a new table TABLE2.

[oracle@ora12c-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 13:10:43 2019

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


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

SQL> create table TABLE2 as select * from dba_objects;

Table created.

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

And a new incremental level 1 backup will contain all these new changes.

[oracle@ora12c-1 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 13:11:32 2019

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

connected to target database: ORCLCDB (DBID=2774882222)

RMAN> backup incremental level 1 as compressed backupset database;

Starting backup at 25-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=70 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=/u01/app/oracle/oradata/orclcdb/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orclcdb/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orclcdb/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-19
channel ORA_DISK_1: finished piece 1 at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T131140_g9l2yddm_.bkp tag=TAG20190325T131140 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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=00010 name=/u01/app/oracle/oradata/orclcdb/pdb1/sysaux01.dbf
skipping datafile 00010 because it has not changed
input datafile file number=00009 name=/u01/app/oracle/oradata/orclcdb/pdb1/system01.dbf
skipping datafile 00009 because it has not changed
input datafile file number=00011 name=/u01/app/oracle/oradata/orclcdb/pdb1/users01.dbf
skipping datafile 00011 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
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=00006 name=/u01/app/oracle/oradata/orclcdb/pdbseed/sysaux01.dbf
skipping datafile 00006 because it has not changed
input datafile file number=00005 name=/u01/app/oracle/oradata/orclcdb/pdbseed/system01.dbf
skipping datafile 00005 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
Finished backup at 25-MAR-19

Starting Control File and SPFILE Autobackup at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003842726_g9l2z7b0_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-19

RMAN> exit


Recovery Manager complete.

These new incremental backups should be sent, cataloged and applied to the target database:

[oracle@ora12c-1 ~]$ rsync --progress -avz -e "ssh -i ~/EC2us-east-1.pem" /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/ oracle@ora12c-2:/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/
sending incremental file list
autobackup/2019_03_25/
autobackup/2019_03_25/o1_mf_s_1003842582_g9l2tq67_.bkp
18,825,216 100% 99.57MB/s 0:00:00 (xfr#1, to-chk=5/22)
autobackup/2019_03_25/o1_mf_s_1003842726_g9l2z7b0_.bkp
18,825,216 100% 50.01MB/s 0:00:00 (xfr#2, to-chk=4/22)
backupset/2019_03_25/
backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T130915_g9l2sx2j_.bkp
2,539,520 100% 5.28MB/s 0:00:00 (xfr#3, to-chk=1/22)
backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T131140_g9l2yddm_.bkp
2,211,840 100% 3.89MB/s 0:00:00 (xfr#4, to-chk=0/22)

sent 2,354,479 bytes received 115 bytes 1,569,729.33 bytes/sec
total size is 717,881,344 speedup is 304.89
[oracle@ora12c-2 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 13:18:45 2019

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

connected to target database: ORCLCDB (DBID=2774882222, not open)

RMAN> catalog start with '/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/' noprompt;

using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T131140_g9l2yddm_.bkp
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T130915_g9l2sx2j_.bkp
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003842582_g9l2tq67_.bkp
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003842726_g9l2z7b0_.bkp
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T131140_g9l2yddm_.bkp
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T130915_g9l2sx2j_.bkp
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003842582_g9l2tq67_.bkp
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003842726_g9l2z7b0_.bkp

RMAN> recover database;

Starting recover at 25-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
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: /u01/app/oracle/oradata/orclcdb/system01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/orclcdb/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/orclcdb/undotbs01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T130915_g9l2sx2j_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T130915_g9l2sx2j_.bkp tag=TAG20190325T130915
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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: /u01/app/oracle/oradata/orclcdb/system01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/orclcdb/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/orclcdb/undotbs01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T131140_g9l2yddm_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T131140_g9l2yddm_.bkp tag=TAG20190325T131140
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

unable to find archived log
archived log thread=1 sequence=2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/25/2019 13:19:15
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2 and starting SCN of 1448493

RMAN> exit

Recovery Manager complete.

Please note the RMAN error at the end of the RECOVER. It says there is an unknown archive log for the sequence 2. This is because the last changes in the source database are not yet archived, they are only in the redo log files.

So, this error is expected and we can ignore it now. At the end of the process we will apply all the changes and no data will be lost. I could avoid the error by issuing a RECOVER with UNTIL SEQUENCE.

11. Start the cutover window: shutdown the source database and perform the last incremental backup

Ok, so you target database have been updated for some days or weeks thru incremental backups, and now it’s time to perform the final steps to finally make our new database available to the users in replacement of the old one in the source.

First, let’s simulate the changes occurred in the source database by creating a new table TABLE3.

[oracle@ora12c-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 13:28:17 2019

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


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

SQL> create table TABLE3 as select * from dba_objects;

Table created.

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

Then, the cutover starts with us shutting down the source database and performing the last incremental backup:

[oracle@ora12c-1 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 13:29:55 2019

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

connected to target database: ORCLCDB (DBID=2774882222)

RMAN> shutdown immediate

using target database control file instead of recovery catalog
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 419430400 bytes

Fixed Size 8793496 bytes
Variable Size 297796200 bytes
Database Buffers 109051904 bytes
Redo Buffers 3788800 bytes

RMAN> backup incremental level 1 as compressed backupset database;

Starting backup at 25-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 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=/u01/app/oracle/oradata/orclcdb/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orclcdb/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orclcdb/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-19
channel ORA_DISK_1: finished piece 1 at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T133040_g9l420t6_.bkp tag=TAG20190325T133040 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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=00010 name=/u01/app/oracle/oradata/orclcdb/pdb1/sysaux01.dbf
skipping datafile 00010 because it has not changed
input datafile file number=00009 name=/u01/app/oracle/oradata/orclcdb/pdb1/system01.dbf
skipping datafile 00009 because it has not changed
input datafile file number=00011 name=/u01/app/oracle/oradata/orclcdb/pdb1/users01.dbf
skipping datafile 00011 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
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=00006 name=/u01/app/oracle/oradata/orclcdb/pdbseed/sysaux01.dbf
skipping datafile 00006 because it has not changed
input datafile file number=00005 name=/u01/app/oracle/oradata/orclcdb/pdbseed/system01.dbf
skipping datafile 00005 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
Finished backup at 25-MAR-19

Starting Control File and SPFILE Autobackup at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003843810_g9l42thh_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-19

RMAN> exit

Recovery Manager complete.

12. Copy the new backup files to the target

[oracle@ora12c-1 ~]$ rsync --progress -avz -e "ssh -i ~/EC2us-east-1.pem" /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/ oracle@ora12c-2:/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/
sending incremental file list
autobackup/2019_03_25/
autobackup/2019_03_25/o1_mf_s_1003843810_g9l42thh_.bkp
18,825,216 100% 107.32MB/s 0:00:00 (xfr#1, to-chk=5/24)
backupset/2019_03_25/
backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T133040_g9l420t6_.bkp
2,768,896 100% 9.60MB/s 0:00:00 (xfr#2, to-chk=0/24)

sent 1,580,107 bytes received 77 bytes 1,053,456.00 bytes/sec
total size is 739,475,456 speedup is 467.97

13. Copy the Redo Log Files and the up-to-date Control File to the target

Here is the trick that will allow us open the database without using RESETLOGS: we will copy the online redo log files and the up-to-date control file to the target.

Let me explain why it is necessary.

First of all, the control file is the file Oracle uses to control the last change (SCN) made to a database. So, if you have the current control file, i.e., not one restored from a backup, then surely the last SCN is written in the control file and so Oracle knows the last change.

On the other hand, if you restore a control file from the backup, then obviously the SCN information inside it is obsolete and cannot be used to know what was the last change. And, if the control file has not the last SCN, then it must be updated and it is done when you open the database with the RESETLOGS option. No matter if you were able to apply all the changes or not, if the control file came from a backup, your database must be opened with RESETLOGS.

Let’s take a look at the control file of the target database at this point:

[oracle@ora12c-2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 13:43:36 2019

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


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

SQL> select controlfile_type from v$database;

CONTROL
-------
BACKUP

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

As you can see, the target database has its control file marked as a backup. RMAN did it.

So, with this control file, the only way to open the target database is with RESETLOGS. But we want to avoid it, and the way to achieve this is by copying the last control file from the source to the target. This copy must be done manually, because any control file generated by RMAN will be marked as a backup.

We also need to copy the online redo log files because they could have some last changes that were not archived yet. Even if you are sure there is no change unarchived, the redos are still necessary because Oracle needs to check inside them to be sure it was able to apply all the changes and so the database is up to date.

Before I can copy the files, I will shutdown the target database because it will have its control files replaced.

[oracle@ora12c-2 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 13:54:17 2019

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

connected to target database: ORCLCDB (DBID=2774882222, not open)

RMAN> shutdown immediate

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

RMAN> exit

Recovery Manager complete.

So, let’s copy the files. First, let me see where are my control files and redo log files in the source:

[oracle@ora12c-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 13:56:01 2019

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


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

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orclcdb/control01.ctl
/u01/app/oracle/fast_recovery_area/orclcdb/control02.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orclcdb/redo03.log
/u01/app/oracle/oradata/orclcdb/redo02.log
/u01/app/oracle/oradata/orclcdb/redo01.log

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

Not, let’s copy the files to the target:

[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem /u01/app/oracle/oradata/orclcdb/control01.ctl oracle@ora12c-2:/u01/app/oracle/oradata/orclcdb/
control01.ctl 100% 18MB 117.7MB/s 00:00 
[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem /u01/app/oracle/fast_recovery_area/orclcdb/control02.ctl oracle@ora12c-2:/u01/app/oracle/fast_recovery_area/orclcdb/
control02.ctl 100% 18MB 117.8MB/s 00:00 
[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem /u01/app/oracle/oradata/orclcdb/redo01.log oracle@ora12c-2:/u01/app/oracle/oradata/orclcdb/
redo01.log 100% 200MB 87.7MB/s 00:02 
[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem /u01/app/oracle/oradata/orclcdb/redo02.log oracle@ora12c-2:/u01/app/oracle/oradata/orclcdb/
redo02.log 100% 200MB 67.1MB/s 00:02 
[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem /u01/app/oracle/oradata/orclcdb/redo03.log oracle@ora12c-2:/u01/app/oracle/oradata/orclcdb/
redo03.log 100% 200MB 66.9MB/s 00:02

14. Mount and recover the target database

Now we have the latest control files and redo log files. So this time the RECOVER statement must complete successfully by applying all the changes and confirming the media recovery is complete.

[oracle@ora12c-2 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 14:01:54 2019

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 419430400 bytes

Fixed Size 8793496 bytes
Variable Size 297796200 bytes
Database Buffers 109051904 bytes
Redo Buffers 3788800 bytes

RMAN> recover database;

Starting recover at 25-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
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: /u01/app/oracle/oradata/orclcdb/system01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/orclcdb/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/orclcdb/undotbs01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T133040_g9l420t6_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T133040_g9l420t6_.bkp tag=TAG20190325T133040
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

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

Finished recover at 25-MAR-19

Let’s check and confirm if our control file is not marked as a backup anymore:

RMAN> select controlfile_type from v$database;

CONTROL
-------
CURRENT

Now the control file is recognised as current, which means the database can be opened without RESETLOGS, as long as all the data files are correctly recovered. Let’s check if there is any file needing recovery:

RMAN> select * from v$recover_file;

no rows selected

Nothing to be recovered.

15. Open the target database

Everything is OK, so I can open the database in the target server without using RESETLOGS:

RMAN> alter database open;

Statement processed

RMAN> exit

Recovery Manager complete.

Just to confirm that we didn’t loose any information, let’s check if all the tables we created in the source to mimic data changes over time are available in the target:

[oracle@ora12c-2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 14:26:28 2019

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

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

SQL> select count(*) from table1;

COUNT(*)
----------
72633

SQL> select count(*) from table2;

COUNT(*)
----------
72634

SQL> select count(*) from table3;

COUNT(*)
----------
72635

Great, everything is OK.

16. Rollback to the source server

As long as the source database was not opened after the cutover steps, you could perform the reverse operation to rollback to the old server. For this, you should repeat all the steps beginning with the one named “Start the cutover”, but this time swapping roles between source and target databases.

That’s it. I hope you enjoy!

Como mover um banco de dados Oracle para outro servidor SEM precisar abrir com RESETLOGS

Português/English

É bem conhecida a técnica de mover um banco de dados Oracle para um novo servidor, como explicado por exemplo neste link da documentação. Também é bem conhecida a técnica para continuar atualizando o banco no destino até o momento da mudança.

Basicamente, os passos são os seguintes:

  1. Prepare o novo servidor (instale o software Oracle, etc).
  2. Copie todos os arquivos de configuração do Oracle (spfile, password file, listener.ora, tnsnames.ora, etc) e faça os ajustes necessários.
  3. Execute um backup completo do banco na origem.
  4. Copie os arquivos de backup para o destino a restaure o banco a partir deles.
  5. Enquanto espera o momento da mudança, execute backups incrementais periodicamente na origem e atualize o banco no destino.
  6. No momento da mudança, execute um último backup incremental do banco na origem em estado MOUNT, copie e aplique no destino.
  7. Abra o banco no destino com a opção RESETLOGS.

O principal problema deste processo é o fato de que o banco é aberto com RESETLOGS, o que o faz ser uma nova encarnação, quase como que um banco novo. Na prática, você terá que começar seu histórico de backups do zero, e se algo não for bem no servidor destino é impossível voltar a usar o banco na origem, a menos que você aceite perder todas as alterações feitas no destino depois que o banco foi aberto.

Se você está usando o Oracle Enterprise Edition, você pode configurar um DataGuard entre os bancos, e assim pode alternar para o novo servidor quase sem tempo de parada, e também reverter para a origem caso algo dê errado sem qualquer perda de dados.

Mas, e se você não tem a licença do Oracle EE, ou por qualquer motivo não quer configurar o DataGuard? Há uma maneira de abrir o banco no destino sem usar o RESETLOGS, e também de reverter para a origem caso necessário?

Eu encontrei muito pouca documentação sobre isso, e portanto este post é para explicar por que normalmente um RESETLOGS seria necessário nesta situação, e como superar esta limitação.

Em primeiro lugar, algumas informações sobre o meu ambiente:

  • Estou usando dois servidores virtuais Oracle Linux na AWS com Oracle 12.2 instalado.
  • Os servidores se chamam ora12c-1 (a origem) e ora12c-2 (o destino). O prompt do Linux identifica em qual dos servidores eu estou trabalhando.
  • Na origem eu tenho apenas o banco orcldb que será usado aqui. No destino não há nenhum banco de dados.
  • Ambos os servidores podem se comunicar através da rede, e portanto eu posso transferir arquivos facilmente entre eles.
  • Todas as variáveis de ambiente estão configuradas adequadamente para o usuário oracle. Portanto, sempre que eu me conectar a um banco de dados será o correto.
  • Os arquivos do banco de dados, FRA, backups e tudo o mais serão copiados para os mesmos caminhos no destino.

Então vamos começar.

1. Prepare o banco na origem

Eu apenas quero verificar se o banco está em nodo ARCHIVELOG, de maneira que eu possa executar todos os backups online:

[oracle@ora12c-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 11:33:13 2019

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

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

SQL> select name, log_mode from v$database;

NAME	  LOG_MODE
--------- ------------
ORCLCDB   ARCHIVELOG

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

2. Prepare o servidor destino

Aqui eu vou apenas criar os diretórios que serão usados pelo banco de dados, nos mesmos caminhos que existem na origem:

[oracle@ora12c-2 ~]$ mkdir -p $ORACLE_BASE/admin/orclcdb/adump
[oracle@ora12c-2 ~]$ mkdir -p $ORACLE_BASE/fast_recovery_area/orclcdb/ORCLCDB

3. Copie o SPFILE, password file e arquivos de configuração de rede para o destino e ajuste-os

Vamos copiar os arquivos de configuração para o destino:

[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem $ORACLE_HOME/dbs/spfileorclcdb.ora oracle@ora12c-2:$ORACLE_HOME/dbs/
spfileorclcdb.ora 100% 3584 4.9MB/s 00:00 
[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem $ORACLE_HOME/dbs/orapworclcdb oracle@ora12c-2:$ORACLE_HOME/dbs/
orapworclcdb 100% 3584 2.9MB/s 00:00 
[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem $ORACLE_HOME/network/admin/*ora oracle@ora12c-2:$ORACLE_HOME/network/admin/
listener.ora 100% 336 264.6KB/s 00:00 
sqlnet.ora 100% 202 364.2KB/s 00:00 
tnsnames.ora 100% 494 528.6KB/s 00:00

Depois de copiar estes arquivos, você deve revisar o conteúdo dos arquivos de rede e fazer os ajustes necessários, considerando que o servidor é diferente.

4. Execute um backup completo na origem

O primeiro backup na origem precisa ser completo. Na verdade, precisa ser um incremental de nível 0 para que os subsequentes nível 1 o considerem.

Para o método que vamos usar, não há necessidade de fazer backup dos Archived Logs, porque o banco no destino será atualizado somente com backups incrementais.

[oracle@ora12c-1 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 12:17:15 2019

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

connected to target database: ORCLCDB (DBID=2774882222, not open)

RMAN> backup incremental level 0 as compressed backupset database;

Starting backup at 25-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 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=/u01/app/oracle/oradata/orclcdb/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orclcdb/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orclcdb/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-19
channel ORA_DISK_1: finished piece 1 at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzrqyb_.bkp tag=TAG20190325T121727 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
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=00010 name=/u01/app/oracle/oradata/orclcdb/pdb1/sysaux01.dbf
input datafile file number=00009 name=/u01/app/oracle/oradata/orclcdb/pdb1/system01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/orclcdb/pdb1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-19
channel ORA_DISK_1: finished piece 1 at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/84EA94D2E64025B8E053AB5C1FAC5F38/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzt54y_.bkp tag=TAG20190325T121727 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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=00006 name=/u01/app/oracle/oradata/orclcdb/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orclcdb/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-19
channel ORA_DISK_1: finished piece 1 at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/84EA3D138B311A47E053AB5C1FACC44E/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzty8l_.bkp tag=TAG20190325T121727 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 25-MAR-19

Starting Control File and SPFILE Autobackup at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003825815_g9kzvqgf_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-19

RMAN> exit

Recovery Manager complete.

Vou anotar o backup automático do control file gerado, para restaurar no destino: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003825815_g9kzvqgf_.bkp .

5. Copie os arquivos de backup para o destino

Como eu vou ter que copiar os arquivos de backup para o destino várias vezes, vou manter a FRA sincronizada nos dois servidores com o rsync:

[oracle@ora12c-1 ~]$ rsync --progress -avz -e "ssh -i ~/EC2us-east-1.pem" /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/ oracle@ora12c-2:/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/
sending incremental file list
./
84EA3D138B311A47E053AB5C1FACC44E/
84EA3D138B311A47E053AB5C1FACC44E/backupset/
84EA3D138B311A47E053AB5C1FACC44E/backupset/2019_03_25/
84EA3D138B311A47E053AB5C1FACC44E/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzty8l_.bkp
    164,388,864 100%   20.01MB/s    0:00:07 (xfr#1, to-chk=8/18)
84EA94D2E64025B8E053AB5C1FAC5F38/
84EA94D2E64025B8E053AB5C1FAC5F38/backupset/
84EA94D2E64025B8E053AB5C1FAC5F38/backupset/2019_03_25/
84EA94D2E64025B8E053AB5C1FAC5F38/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzt54y_.bkp
    164,503,552 100%   18.03MB/s    0:00:08 (xfr#2, to-chk=5/18)
archivelog/
archivelog/2019_03_25/
autobackup/
autobackup/2019_03_25/
autobackup/2019_03_25/o1_mf_s_1003825815_g9kzvqgf_.bkp
     18,825,216 100%   20.31MB/s    0:00:00 (xfr#3, to-chk=2/18)
backupset/
backupset/2019_03_25/
backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzrqyb_.bkp
    327,761,920 100%   18.43MB/s    0:00:16 (xfr#4, to-chk=0/18)
onlinelog/

sent 627,374,884 bytes  received 163 bytes  19,303,847.60 bytes/sec
total size is 675,479,552  speedup is 1.08

6. Desligue a OMF no destino

Se você está utilizando Oracle Managed Files, eu recomendo que você desligue este recurso temporariamente no destino. Com OMF configurado, quando você faz um restore via RMAN os novos data files gerados têm um sufixo diferente no nome. Então teríamos que executar comandos SET NEWNAME para que o control file reconhecesse os novos arquivos. E como o control file será copiado novamente durante a mudança, os comandos teriam que ser reexecutados, aumentando a complexidade e a chance de erros.

[oracle@ora12c-2 ~]$ echo $ORACLE_SID
orclcdb
[oracle@ora12c-2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 12:45:37 2019

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size		    8793496 bytes
Variable Size		  297796200 bytes
Database Buffers	  109051904 bytes
Redo Buffers		    3788800 bytes
SQL> alter system set db_create_file_dest='' scope=both;

System altered.

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

7. Restaure o control file e monte o banco de dados

Vamos restaurar o control file do backup automático e montar o banco:

[oracle@ora12c-2 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 12:48:59 2019

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

connected to target database: ORCLCDB (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003825815_g9kzvqgf_.bkp';

Starting restore at 25-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/orclcdb/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orclcdb/control02.ctl
Finished restore at 25-MAR-19

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

9. Restaure o banco de dados

Agora é hora de restaurar o banco de dados, isto é, os data files. Perceba que, como o control file foi restaurado de um backup realizado depois do backup dos data files, ele já tem a informação sobre estes backups e nada portanto precisa ser catalogado.

RMAN> restore database;

Starting restore at 25-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 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 /u01/app/oracle/oradata/orclcdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orclcdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orclcdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzrqyb_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzrqyb_.bkp tag=TAG20190325T121727
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
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 00009 to /u01/app/oracle/oradata/orclcdb/pdb1/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/orclcdb/pdb1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/orclcdb/pdb1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/84EA94D2E64025B8E053AB5C1FAC5F38/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzt54y_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/84EA94D2E64025B8E053AB5C1FAC5F38/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzt54y_.bkp tag=TAG20190325T121727
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orclcdb/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/orclcdb/pdbseed/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/84EA3D138B311A47E053AB5C1FACC44E/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzty8l_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/84EA3D138B311A47E053AB5C1FACC44E/backupset/2019_03_25/o1_mf_nnnd0_TAG20190325T121727_g9kzty8l_.bkp tag=TAG20190325T121727
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 25-MAR-19

RMAN> exit

Recovery Manager complete.

Neste momento, nós temos o banco destino em estado MOUNT e atualizado até o momento do último backup.

10. Continue atualizando o destino com backups incrementais

Como o banco na origem está aberto e sendo atualizado ao longo do tempo, é necessário aplicar todas estas alterações no destino. Nós faremos isso com backups incrementais periódicos. Eles podem ser executados semanalmente, diariamente ou até mais frequentes, de acordo com o volume de alterações.

Por exemplo, vou simular dois ciclos de alterações no banco e backups incrementais que conterão estas alterações.

Vou criar a tabela TABLE1 para simular as mudanças no banco origem:

[oracle@ora12c-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 13:06:35 2019

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


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

SQL> create table TABLE1 as select * from dba_objects;

Table created.

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

Então eu crio um backup incremental de nível 1:

[oracle@ora12c-1 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 13:09:06 2019

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

connected to target database: ORCLCDB (DBID=2774882222)

RMAN> backup incremental level 1 as compressed backupset database;

Starting backup at 25-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 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=/u01/app/oracle/oradata/orclcdb/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orclcdb/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orclcdb/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-19
channel ORA_DISK_1: finished piece 1 at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T130915_g9l2sx2j_.bkp tag=TAG20190325T130915 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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=00010 name=/u01/app/oracle/oradata/orclcdb/pdb1/sysaux01.dbf
skipping datafile 00010 because it has not changed
input datafile file number=00009 name=/u01/app/oracle/oradata/orclcdb/pdb1/system01.dbf
skipping datafile 00009 because it has not changed
input datafile file number=00011 name=/u01/app/oracle/oradata/orclcdb/pdb1/users01.dbf
skipping datafile 00011 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
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=00006 name=/u01/app/oracle/oradata/orclcdb/pdbseed/sysaux01.dbf
skipping datafile 00006 because it has not changed
input datafile file number=00005 name=/u01/app/oracle/oradata/orclcdb/pdbseed/system01.dbf
skipping datafile 00005 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
Finished backup at 25-MAR-19

Starting Control File and SPFILE Autobackup at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003842582_g9l2tq67_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-19

RMAN> exit


Recovery Manager complete.

Novamente, mais alterações ocorrem na origem, na forma da nova tabela TABLE2:

[oracle@ora12c-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 13:10:43 2019

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


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

SQL> create table TABLE2 as select * from dba_objects;

Table created.

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

E um novo backup incremental de nível 1 conterá estas alterações:

[oracle@ora12c-1 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 13:11:32 2019

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

connected to target database: ORCLCDB (DBID=2774882222)

RMAN> backup incremental level 1 as compressed backupset database;

Starting backup at 25-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=70 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=/u01/app/oracle/oradata/orclcdb/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orclcdb/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orclcdb/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-19
channel ORA_DISK_1: finished piece 1 at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T131140_g9l2yddm_.bkp tag=TAG20190325T131140 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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=00010 name=/u01/app/oracle/oradata/orclcdb/pdb1/sysaux01.dbf
skipping datafile 00010 because it has not changed
input datafile file number=00009 name=/u01/app/oracle/oradata/orclcdb/pdb1/system01.dbf
skipping datafile 00009 because it has not changed
input datafile file number=00011 name=/u01/app/oracle/oradata/orclcdb/pdb1/users01.dbf
skipping datafile 00011 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
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=00006 name=/u01/app/oracle/oradata/orclcdb/pdbseed/sysaux01.dbf
skipping datafile 00006 because it has not changed
input datafile file number=00005 name=/u01/app/oracle/oradata/orclcdb/pdbseed/system01.dbf
skipping datafile 00005 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
Finished backup at 25-MAR-19

Starting Control File and SPFILE Autobackup at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003842726_g9l2z7b0_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-19

RMAN> exit


Recovery Manager complete.

Estes novos backups incrementais precisam ser enviados, catalogados e aplicados no destino:

[oracle@ora12c-1 ~]$ rsync --progress -avz -e "ssh -i ~/EC2us-east-1.pem" /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/ oracle@ora12c-2:/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/
sending incremental file list
autobackup/2019_03_25/
autobackup/2019_03_25/o1_mf_s_1003842582_g9l2tq67_.bkp
18,825,216 100% 99.57MB/s 0:00:00 (xfr#1, to-chk=5/22)
autobackup/2019_03_25/o1_mf_s_1003842726_g9l2z7b0_.bkp
18,825,216 100% 50.01MB/s 0:00:00 (xfr#2, to-chk=4/22)
backupset/2019_03_25/
backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T130915_g9l2sx2j_.bkp
2,539,520 100% 5.28MB/s 0:00:00 (xfr#3, to-chk=1/22)
backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T131140_g9l2yddm_.bkp
2,211,840 100% 3.89MB/s 0:00:00 (xfr#4, to-chk=0/22)

sent 2,354,479 bytes received 115 bytes 1,569,729.33 bytes/sec
total size is 717,881,344 speedup is 304.89
[oracle@ora12c-2 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 13:18:45 2019

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

connected to target database: ORCLCDB (DBID=2774882222, not open)

RMAN> catalog start with '/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/' noprompt;

using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T131140_g9l2yddm_.bkp
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T130915_g9l2sx2j_.bkp
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003842582_g9l2tq67_.bkp
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003842726_g9l2z7b0_.bkp
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T131140_g9l2yddm_.bkp
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T130915_g9l2sx2j_.bkp
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003842582_g9l2tq67_.bkp
File Name: /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003842726_g9l2z7b0_.bkp

RMAN> recover database;

Starting recover at 25-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
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: /u01/app/oracle/oradata/orclcdb/system01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/orclcdb/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/orclcdb/undotbs01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T130915_g9l2sx2j_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T130915_g9l2sx2j_.bkp tag=TAG20190325T130915
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
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: /u01/app/oracle/oradata/orclcdb/system01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/orclcdb/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/orclcdb/undotbs01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T131140_g9l2yddm_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T131140_g9l2yddm_.bkp tag=TAG20190325T131140
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

unable to find archived log
archived log thread=1 sequence=2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/25/2019 13:19:15
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2 and starting SCN of 1448493

RMAN> exit

Recovery Manager complete.

Veja o erro do RMAN no final do comando RECOVER. Ele diz que há um archive log desconhecido para a sequência 2. Isto ocorre porque as últimas alterações na origem não foram arquivadas ainda, estão apenas nos redo log files.

Logo, este erro é esperado e pode ser ignorado. Ao final do processo nós vamos aplicar todas as alterações e nenhum dado será perdido. Esse erro poderia ser evitado executando o comando RECOVER com UNTIL SEQUENCE.

11. Inicie a janela de mudança: baixe o banco na origem e execute o último backup incremental

Ok, então o banco de dados destino foi sendo atualizado por dias ou semanas através de backups incrementais, e agora é a hora de fazer os passos finais para que este novo banco esteja disponível para os usuários no novo servidor, substituindo o antigo.

Primeiro, vamos simular mais alterações ocorridas no banco origem criando uma nova tabela TABLE3.

[oracle@ora12c-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 13:28:17 2019

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


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

SQL> create table TABLE3 as select * from dba_objects;

Table created.

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

Então, a mudança começa baixando o banco na origem e fazendo o último backup incremental:

[oracle@ora12c-1 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 13:29:55 2019

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

connected to target database: ORCLCDB (DBID=2774882222)

RMAN> shutdown immediate

using target database control file instead of recovery catalog
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 419430400 bytes

Fixed Size 8793496 bytes
Variable Size 297796200 bytes
Database Buffers 109051904 bytes
Redo Buffers 3788800 bytes

RMAN> backup incremental level 1 as compressed backupset database;

Starting backup at 25-MAR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 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=/u01/app/oracle/oradata/orclcdb/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orclcdb/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orclcdb/undotbs01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAR-19
channel ORA_DISK_1: finished piece 1 at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T133040_g9l420t6_.bkp tag=TAG20190325T133040 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
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=00010 name=/u01/app/oracle/oradata/orclcdb/pdb1/sysaux01.dbf
skipping datafile 00010 because it has not changed
input datafile file number=00009 name=/u01/app/oracle/oradata/orclcdb/pdb1/system01.dbf
skipping datafile 00009 because it has not changed
input datafile file number=00011 name=/u01/app/oracle/oradata/orclcdb/pdb1/users01.dbf
skipping datafile 00011 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
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=00006 name=/u01/app/oracle/oradata/orclcdb/pdbseed/sysaux01.dbf
skipping datafile 00006 because it has not changed
input datafile file number=00005 name=/u01/app/oracle/oradata/orclcdb/pdbseed/system01.dbf
skipping datafile 00005 because it has not changed
channel ORA_DISK_1: backup cancelled because all files were skipped
Finished backup at 25-MAR-19

Starting Control File and SPFILE Autobackup at 25-MAR-19
piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/autobackup/2019_03_25/o1_mf_s_1003843810_g9l42thh_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAR-19

RMAN> exit

Recovery Manager complete.

12. Copie os novos arquivos de backup para o destino

[oracle@ora12c-1 ~]$ rsync --progress -avz -e "ssh -i ~/EC2us-east-1.pem" /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/ oracle@ora12c-2:/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/
sending incremental file list
autobackup/2019_03_25/
autobackup/2019_03_25/o1_mf_s_1003843810_g9l42thh_.bkp
18,825,216 100% 107.32MB/s 0:00:00 (xfr#1, to-chk=5/24)
backupset/2019_03_25/
backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T133040_g9l420t6_.bkp
2,768,896 100% 9.60MB/s 0:00:00 (xfr#2, to-chk=0/24)

sent 1,580,107 bytes received 77 bytes 1,053,456.00 bytes/sec
total size is 739,475,456 speedup is 467.97

13. Copie os Redo Log Files e o Control File final para o destino

Aqui está o “truque” que vai permitir abrir o banco sem RESETLOGS: nós vamos copiar os redo log files online e o control file mais atualizado para o destino.

Deixe-me explicar porque isto é necessário.

Inicialmente, o control file é o arquivo que o Oracle usa para controlar a última alteração (SCN) feita em um banco de dados. Então, se você tem o control file atual, isto é, não restaurado de um backup, certamente o último SCN está registrado nele e o Oracle conhece o número a última mudança.

Por outro lado, se você restaura um control file do backup, obviamente o SCN dentro dele é obsoleto e não server para identificar qual foi a última mudança. E, se o control file não tem a última mudança, ele precisa ser atualizado e isso é feito quando se abre o banco com RESETLOGS. Não importa se você conseguiu aplicar todas as alterações ou não, se o control file foi restaurado de um backup, o banco precisa ser aberto com RESETLOGS.

Vamos olhar o control file do destino neste momento:

[oracle@ora12c-2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 13:43:36 2019

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


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

SQL> select controlfile_type from v$database;

CONTROL
-------
BACKUP

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

Como pode ver, o banco destino tem seu control file marcado como de backup. O RMAN fez esta marcação.

Então, com este control file, o único jeito de abrir o banco é com RESETLOGS. Mas nós queremos evitar isso, e a maneira de conseguir é copiando o último control file da origem para o destino. Esta cópia precisa ser feita manualmente, porque todo control file gerado pelo RMAN será marcado como de backup.

Nós também precisamos copiar os redo log files porque eles contém as últimas alterações que ainda não foram arquivadas. Mesmo que você tenha certeza de que não há nenhuma alteração não arquivada, os redos são necessários porque o Oracle precisa verificar dentro deles e ter certeza de que todas as alterações foram aplicadas.

Antes de copiar os arquivos, vou baixar o banco no destino porque os control files serão substituídos.

[oracle@ora12c-2 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 13:54:17 2019

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

connected to target database: ORCLCDB (DBID=2774882222, not open)

RMAN> shutdown immediate

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

RMAN> exit

Recovery Manager complete.

Agora vamos copiar os arquivos. Primeiro, deixe-me verificar quais são os control files e redo log files na origem:

[oracle@ora12c-1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 13:56:01 2019

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


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

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orclcdb/control01.ctl
/u01/app/oracle/fast_recovery_area/orclcdb/control02.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orclcdb/redo03.log
/u01/app/oracle/oradata/orclcdb/redo02.log
/u01/app/oracle/oradata/orclcdb/redo01.log

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

Agora, vamos copiar os arquivos para o destino:

[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem /u01/app/oracle/oradata/orclcdb/control01.ctl oracle@ora12c-2:/u01/app/oracle/oradata/orclcdb/
control01.ctl 100% 18MB 117.7MB/s 00:00 
[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem /u01/app/oracle/fast_recovery_area/orclcdb/control02.ctl oracle@ora12c-2:/u01/app/oracle/fast_recovery_area/orclcdb/
control02.ctl 100% 18MB 117.8MB/s 00:00 
[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem /u01/app/oracle/oradata/orclcdb/redo01.log oracle@ora12c-2:/u01/app/oracle/oradata/orclcdb/
redo01.log 100% 200MB 87.7MB/s 00:02 
[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem /u01/app/oracle/oradata/orclcdb/redo02.log oracle@ora12c-2:/u01/app/oracle/oradata/orclcdb/
redo02.log 100% 200MB 67.1MB/s 00:02 
[oracle@ora12c-1 ~]$ scp -i ~/EC2us-east-1.pem /u01/app/oracle/oradata/orclcdb/redo03.log oracle@ora12c-2:/u01/app/oracle/oradata/orclcdb/
redo03.log 100% 200MB 66.9MB/s 00:02

14. Monte e recupere o banco no destino

Agora temos o último control file e os redo log files. Desta vez o comando RECOVER precisa completar com sucesso, aplicando todas as alterações e confirmando que a recuperação está completa:

[oracle@ora12c-2 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Mar 25 14:01:54 2019

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

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 419430400 bytes

Fixed Size 8793496 bytes
Variable Size 297796200 bytes
Database Buffers 109051904 bytes
Redo Buffers 3788800 bytes

RMAN> recover database;

Starting recover at 25-MAR-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
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: /u01/app/oracle/oradata/orclcdb/system01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/orclcdb/sysaux01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/orclcdb/undotbs01.dbf
destination for restore of datafile 00007: /u01/app/oracle/oradata/orclcdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T133040_g9l420t6_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orclcdb/ORCLCDB/backupset/2019_03_25/o1_mf_nnnd1_TAG20190325T133040_g9l420t6_.bkp tag=TAG20190325T133040
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

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

Finished recover at 25-MAR-19

Vamos confirmar que nosso control file não está mais marcado como backup:

RMAN> select controlfile_type from v$database;

CONTROL
-------
CURRENT

Agora o control file é reconhecido como atual, o que significa que o banco pode ser aberto SEM RESETLOGS, contanto que todos os data files estejam corretamente recuperados. Vamos verificar se há algum arquivo que precisa de recuperação:

RMAN> select * from v$recover_file;

no rows selected

Nada para ser recuperado.

15. Abra o banco no destino

Tudo está OK, então eu posso abrir o banco no servidor destino SEM USAR o RESETLOGS:

RMAN> alter database open;

Statement processed

RMAN> exit

Recovery Manager complete.

Apenas para confirmar que não perdemos nenhuma informação, vamos verificar se todas as tabelas criadas na origem para simular alterações existem no destino:

[oracle@ora12c-2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Mar 25 14:26:28 2019

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

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

SQL> select count(*) from table1;

COUNT(*)
----------
72633

SQL> select count(*) from table2;

COUNT(*)
----------
72634

SQL> select count(*) from table3;

COUNT(*)
----------
72635

Show, está tudo OK.

16. Rollback para a origem

Contanto que o banco origem não tenha sido aberto depois dos passos para a transferência, você poderá fazer a operação reversa e voltar a utilizar o banco na origem. Para isso, basta repetir todos os passos começando pelo “Inicie a janela de mudança”, mas desta vez invertendo os papéis entre os bancos origem e destino.

É isso. Espero que gostem!

Find the Datafile Checkpoint SCN in Linux

Português/English

Today I was asked how to find the datafile checkpoint SCN in Linux, reading the datafile directly. Of course, you have other ways to do it easier if you can use Oracle tools, either using RMAN ou querying V$ views. But, if you just have the datafile and for whatever reason you can’t use Oracle tools, here is a shell script to find the SCN:

SQL> select CHECKPOINT_CHANGE# from v$datafile_header where file#=1;

CHECKPOINT_CHANGE#
------------------
           2502608

1 row selected.

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

$ file="system01.dbf"
$ a=`od -x -N 10000 $file | grep ^0020740 | tr -s " " | cut -f5 -d" "`
$ b=`od -x -N 10000 $file | grep ^0020740 | tr -s " " | cut -f4 -d" "` 
$ printf "%d\n" 0x$a$b
2502608

I tested it against 11g and 12c datafiles. I have no idea if it works also in older versions, or will work in newer ones. It depends on Oracle not changing the absolute position of the SCN in the datafile header.

I also had no opportunity to test it in a Big Endian platform, like AIX. I guess you should just switch $a and $b variables in the code for this to work.

If you can test it please let me know the results, especially in a Big Endian platform.

Regards.


Encontre o Checkpoint SCN de um Datafile no Linux

Português/English

Hoje me perguntaram como encontrar o SCN do último checkpoint de um datafile no Linux, lendo o arquivo diretamente. Claro que há maneiras mais fáceis se você puder usar ferramentas Oracle, como o RMAN ou consultando as visões V$. Mas, se você só tem o datafile e por qualquer razão não pode usar as ferramentas Oracle no momento, aqui está um shell script para encontrar o SCN:

SQL> select CHECKPOINT_CHANGE# from v$datafile_header where file#=1;

CHECKPOINT_CHANGE#
------------------
           2502608

1 row selected.

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

$ file="system01.dbf"
$ a=`od -x -N 10000 $file | grep ^0020740 | tr -s " " | cut -f5 -d" "`
$ b=`od -x -N 10000 $file | grep ^0020740 | tr -s " " | cut -f4 -d" "` 
$ printf "%d\n" 0x$a$b
2502608

Eu testei em datafiles 11g e 12c. Não tenho ideia se funciona em versões mais antigas, ou se vai continuar funcionando nas mais novas. Depende de a Oracle não mudar a posição absoluta do SCN no cabeçalho do datafile.

Eu também não tive a oportunidade de testar em um ambiente Big Endian como AIX. Eu acredito que basta inverter os valores das variáveis $a e $b para funcionar.

Se você puder testar em seu banco por favor me diga se funcionou, especialmente em plataforma Big Endian.

Abraços.

A script to DELETE records recursively from all child tables

Português/English

In the last months I’ve been working for a client that has a very complex ER model, with about a thousand tables and several thousand of relationships, in many levels of depth. And there are a lot of relationships that cycles the model. For example, we have some situations like this: table A is parent of B, B is parent of C, C is parent of D, and D is parent of A. It means that table D, which is at the third level of dependency of table A, is at the same time parent of table A. I know it sounds weird, but the situation exists. By the way, the model is being reviewed.

Another issue they have is a lot of “trash” data that should be deleted. Old products for example, that came from another implementation of the application but were not cleaned properly.

So, for example, there is a need for deleting some products, but they have many child records in many tables, and each of these child records has its own child records in other tables, and so on.

No, the foreign keys are not “ON DELETE CASCADE”, and it could be a security risk to change all them just to delete some old products.

Then I was asked to develop a way to delete the unwanted data from a table, and all child records in all related tables recursively.

I browsed the Internet but couldn’t find any good solution. So I developed the procedure I present here.

It makes use of recursiveness to navigate throughout the model. For each child constraint it finds that has child records to delete, the procedure recursively calls itself passing the new found constraint, and so on until no more child data are found.

The statements created to delete records are built in the form ‘WHERE constraint_columns IN (SELECT … FROM parent table WHERE …)‘. So for a table in a deep level, you will see the final statement with several nested subqueries with this IN construction.

At first, the procedure does not delete any record. It just identifies the tables and records that should be deleted, and stores the statements to delete them in a table. After this step is done, it can then run the statements to really delete the data in the right order for not to have “parent records found” errors.

Below I present all the parameters that the procedure can accept. Please note that some of them are not intended to be passed in the execution, but are used instead for the recursive calls. Also note that only three parameters are mandatory and has no default values: p_owner, p_table and p_where:

  • p_owner: the owner of the main table to have records deleted.
  • p_table: the main table to have records deleted.
  • p_constraint: the PK/UK constraint to use as the first level. If NULL, all the PK/UK will be used. This parameter was created more to test the script. In the normal use, you should let it NULL.
  • p_where: the conditions that identifies the records to be deleted. For example ‘STATUS=1’.
  • p_commit: the commit interval (in rows of the main table). The default is 10000.
  • p_mode: the mode of execution (default is ‘GS’), with any combination of:
    • G – generate the statements.
    • S – show the generated statements (you must execute SET SERVEROUTPUT ON before).
    • X – execute the statements.
    • C – commit the deletes.
    • A – all in batch mode (the equivalent of ‘GXC’).
  • p_limit: use it to limit the depth of the search. Just for testing purposes.
  • p_level: (RECURSIVE PARAMETER) the current level of recursiveness.
  • p_parent_owner(RECURSIVE PARAMETER) the parent owner – will be used to build the recursive DELETE statement.
  • p_parent_table(RECURSIVE PARAMETER) the parent table – will be used to build the recursive DELETE statement.
  • p_parent_cols(RECURSIVE PARAMETER) the parent columns – will be used to build the recursive DELETE statement.
  • p_child_cols(RECURSIVE PARAMETER) the child columns – will be used to build the recursive DELETE statement.

In order for the procedure to work, you need first to create the table that will store the statements, and a sequence that will be used to define IDs for each statement (download it here):

create table tmp_delete_cascade_stmt ( id number primary key, lev number, owner varchar2(30), table_name varchar2(30), parent_constraint varchar2(30),  child_constraint varchar2(30), statement clob, rows_deleted number);
create sequence seq_delete_cascade;

If you call the procedure with the ‘G’ mode, then it first empties the table and then generates and store the new statements generated.

When using ‘S’ mode, the procedure shows all the statements generated. Please note you must issue SET SERVEROUTPUT ON in your SQL*Plus session in order to view the results. Alternatively, you could directly query the table TMP_DELETE_CASCADE_STMT to check the statements generated.

The ‘X’ mode executes all the statements, and stores in the table the number of rows each statement deleted. But it DOES NOT COMMIT any change. It is intentional, for you to have the opportunity to check if the deleted data is what you wanted, and only after confirming that you should issue the COMMIT.

If you use the ‘C’ mode (usually together with ‘X’) then the procedure commits the changes without asking for any confirmation. It makes partial commits for each 10000 rows of the main table, or the number you pass in the p_commit parameter.

You can execute the procedure step by step, first executing with ‘GS’ modes to see what is generated, then ‘X’ to execute and then ‘C’ to commit (or just issue the COMMIT by yourself), or you can do it all at once, by passing ‘GSXC’ or just ‘A’.

The procedure code can be downloaded from here.

CAUTION: please be very careful when using this script. As explained, it will DELETE all the dependant rows in all related tables. So, it is preferable to execute it first in mode ‘G’, then ‘X’ and only commit the changes after checking the results carefully. USE AT YOUR OWN RISK. It was not exhaustively tested in all possible situations and may have bugs!

If you find something wrong or have any comments, please comment the post and let me know.

So, let me build a sample model in order to show how the procedure works. My sample creates the main table T_PRODUCT and some others around that. It has the following child tables that could have records deleted when trying to delete from the main:

  • T_PRODUCT_WAREHOUSE: the warehouses that keep each product in stock.
  • T_INVOICE_ITEM: the items of each invoice.
  • T_INVOICE_ITEM_DELIVERY: the delivery of the items invoice (items could be delivered partially).
  • T_PRODUCT: it is a self-relationship to relate a product with other equivalent one.

You can download the sample from here.

-- Generate, execute and show (but don't COMMIT yet) the DELETE statements
begin
	P_DELETE_CASCADE(
		p_owner=>user,
		p_table=>'T_PRODUCT',
		p_where=>'product_id in (1,6)',
		p_mode => 'GX'
		);
end;
/

PL/SQL procedure successfully completed.
-- Verify the statements and the number of rows affected by each one
col statement for a80 word_wrap
select table_name, statement, rows_deleted from tmp_delete_cascade_stmt order by lev desc, id;

TABLE_NAME                          STATEMENT                                                                        ROWS_DELETED
----------------------------------- -------------------------------------------------------------------------------- ------------
T_INVOICE_ITEM                      DELETE FROM EDUARDO.T_INVOICE_ITEM WHERE (PRODUCT_ID) in (SELECT                            1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    (PRODUCT_EQUIVALENT) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_PRODUCT_WAREHOUSE                 DELETE FROM EDUARDO.T_PRODUCT_WAREHOUSE WHERE (PRODUCT_ID) in (SELECT                       1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    (PRODUCT_EQUIVALENT) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_INVOICE_ITEM_DELIVERY             DELETE FROM EDUARDO.T_INVOICE_ITEM_DELIVERY WHERE (INVOICE_ID, PRODUCT_ID) in               4
                                    (SELECT
                                    INVOICE_ID, PRODUCT_ID FROM EDUARDO.T_INVOICE_ITEM WHERE
                                    (PRODUCT_ID) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_PRODUCT                           DELETE FROM EDUARDO.T_PRODUCT WHERE (PRODUCT_EQUIVALENT) in (SELECT                         1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_INVOICE_ITEM                      DELETE FROM EDUARDO.T_INVOICE_ITEM WHERE (PRODUCT_ID) in (SELECT                            3
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_PRODUCT_WAREHOUSE                 DELETE FROM EDUARDO.T_PRODUCT_WAREHOUSE WHERE (PRODUCT_ID) in (SELECT                       4
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_PRODUCT                           DELETE FROM EDUARDO.T_PRODUCT WHERE product_id in (1,6)                                     2

7 rows selected.
-- Check which records remain
select * from T_PRODUCT;

PRODUCT_ID PRODUCT_NAME         PROVIDER_ID PROVIDER_PRODUCT_ID PRODUCT_EQUIVALENT
---------- -------------------- ----------- ------------------- ------------------
         3 Product 3                      1                9992
         4 Product 4                      1                9993
         5 Product 5                      2                9992

select * from T_PRODUCT_WAREHOUSE;

PRODUCT_ID WAREHOUSE_ID PIECES_IN_STOCK
---------- ------------ ---------------
         3            1              11
         4            2               3
         5            2               1

select * from T_INVOICE;

INVOICE_ID INVOICE_DATE
---------- -----------------
         1 26-MAR-2018 12:06
         2 26-MAR-2018 12:06
         3 26-MAR-2018 12:06
         4 26-MAR-2018 12:06

select * from T_INVOICE_ITEM;

INVOICE_ID PRODUCT_ID  QUANTITY
---------- ---------- ---------
         3          4         3
         4          3         5

select * from T_INVOICE_ITEM_DELIVERY;

INVOICE_ID PRODUCT_ID DELIVERY_DATE     QUANTITY_DELIVERED
---------- ---------- ----------------- ------------------
         3          4 26-MAR-2018 12:56                  3

Please note that the Invoices 1 and 2 still exist in the T_INVOICE table, despite there is no related child record in the T_INVOICE_ITEM. This is normal because this rule is not enforced by any constraint, so I must delete them manually.

Also note that the Product 2 was deleted because it was dependant of the Product 1. This is expected and shows the risks of using the script without the proper caution ;-).

After confirming everything is OK, you can COMMIT the changes.

Enjoy!!!!


Um script para apagar dados recursivamente de todas as tabelas filhas

Português/English

Nos últimos meses estou trabalhando em um cliente que tem um model ER bastante complexo, com cerca de mil tabelas e vários milhares de relacionamentos entre elas, em muitos níveis de profundidade. E há vários relacionamentos com referências cíclicas. Por exemplo, a tabela A é pai da B, a B é pai da C, a C é pai da D, e a D é pai da A. Isto significa que a tabela D, que por um relacionamento é bisneta da tabela A, é ao mesmo por outro relacionamento pai da tabela A. Eu sei que isso soa estranho, mas a situação existe. Para conhecimento, o modelo está sendo redesenhado.

Outro problema é que existem muitos dados “inúteis” que deveriam ser apagados. Produtos antigos por exemplo, que vieram de outra implementação da aplicação mas não foram excluídos propriamente.

Então, por exemplo, precisamos apagar alguns produtos, mas eles têm muitos registros filhos em muitas tabelas, e cada um destes filhos tem seus próprios registros filhos em outras tabelas, e assim por diante.

Não, as foreign keys não são “ON DELETE CASCADE”, e seria um risco de segurança alterar todas elas simplesmente para apagar registros antigos.

Então me pediram pra criar uma maneira de excluir os dados desnecessários de uma tabela, e todos os registros dependentes em todas as tabelas filhas, recursivamente.

Eu pesquisei na Internet e não encontrei nenhuma solução satisfatória. Então eu desenvolvi a procedure que apresento aqui.

Ela faz uso de recursividade para navegar através do modelo. Para cada constraint filha que tem registros filhos para apagar, a procedure recursivamente chama ela mesma passando a nova constraint encontrada, e assim por diante até que não encontre mais dados relacionados para apagar.

Os comandos criados para excluir os registros são construídos na forma ‘WHERE constraint_columns IN (SELECT … FROM parent table WHERE …)‘. Então para uma tabela em um nível profundo de dependência, você verá o comando final com vários subqueries aninhadas com esta construção IN.

Inicialmente, a procedure não apaga nenhum registro. Ela apenas identifica as tabelas e registros que devem ser apagados, e registra em uma tabela os comandos necessários para apagá-los. Em seguida os comandos podem ser executados para efetivamente excluir os dados na ordem correta para evitar erros de “registros pais encontrados”.

Abaixo eu apresento todos os parâmetros que a procedure pode aceitar. Note que alguns deles não são foram criados para serem passados na execução , mas nas chamadas recursicas que a própria procedure fará. Note também que somente três parâmetros são obrigatórios e não têm valores defaultp_owner, p_table e p_where:

  • p_owner: o owner da tabela principal que terá os registros excluídos.
  • p_table:a tabela principal que terá os registros excluídos.
  • p_constraint: a constraint PK/UK que será considerada como primeiro nível. Se NULL, todas as PK/UK serão usadas. Este parâmetros foi criado mais para testar o script. No uso normal, você deve deixar NULL.
  • p_where: as condições que identificam os registros a excluir. Por exemplo ‘STATUS=1’.
  • p_commit: o intervalo de commit (em linhas da tabela principal). O default é 10000.
  • p_mode: o modo de execução (default é ‘GS’), com qualquer combinação de:
    • G – gera os comandos.
    • S – mostra os comandos gerados (antes ligue o SET SERVEROUTPUT ON).
    • X – executa os comandos.
    • C – faz o COMMIT.
    • A – tudo em modo batch (o equivalente a ‘GXC’).
  • p_limit: use para limitar a profundidade da pesquisa. Apenas para testes.
  • p_level: (PARÂMETRO RECURSIVO) o nível atual de recursividade.
  • p_parent_owner(PARÂMETRO RECURSIVO) o owner da tabela pai – será usado para construir o DELETE recursivo.
  • p_parent_table(PARÂMETRO RECURSIVO) a tabela pai – será usado para construir o DELETE recursivo.
  • p_parent_cols(PARÂMETRO RECURSIVO) as colunas da tabela pai – será usado para construir o DELETE recursivo.
  • p_child_cols(PARÂMETRO RECURSIVO) as colunas da tabela filha – será usado para construir o DELETE recursivo.

Para que a procedure funcione, você precisa primeiro criar a tabela que vai guardar os comandos, e a sequence que será utilizada para gerar IDs para cada um deles (baixe aqui):

create table tmp_delete_cascade_stmt ( id number primary key, lev number, owner varchar2(30), table_name varchar2(30), parent_constraint varchar2(30),  child_constraint varchar2(30), statement clob, rows_deleted number);
create sequence seq_delete_cascade;

Se você chamar a procedure com o modo ‘G’, então ela primeiro limpa a tabela e depois gera e armazena os comandos gerados.

Ao usar o modo ‘S’, a procedure mostra todos os comandos gerados. Note que você precisa primeiro executar SET SERVEROUTPUT ON em sua sessão SQL*Plus para conseguir ver os resultados. Alternativamente, você pode consultar diretamente a tabela TMP_DELETE_CASCADE_STMT para ver os comandos gerados.

O modo ‘X’ executa os comandos, e salva na tabela o número de linhas que cada um excluiu. Mas ele NÃO FAZ O COMMIT. Isto é intencional, para que você tenha a oportunidade de checar se os dados apagados são os que você esperava, e só fazer o COMMIT depois de confirmar isto.

Se usar o modo ‘C’ mode (normalmente junto com o ‘X’) então a procedure faz o COMMIT das alterações sem pedir qualquer confirmação. Ela faz COMMIT parcial a cada 10000 linhas excluídas da tabela principal, ou o número passado no parâmetro p_commit.

Você pode executar a procedure passo a passo, primeiro com modos ‘GS’ para ver os comandos gerados, depois ‘X’ para executar e ‘C’ para confirmar (ou manualmente executar COMMIT), ou você pode fazer tudo de uma vez passando modos ‘GSXC’ ou apenas ‘A’.

A procedure pode ser baixada daqui.

CUIDADO: seja muito cuidados ao utilizar este script. Como explicado, ele vai APAGAR todas as linhas dependentes em todas as tabelas relacionadas. Então, é melhor executar primeiro com modo ‘G’, depois ‘X’ e só depois de ter certeza que tudo está correto confirmar as alterações. USE POR SUA CONTA E RISCO. Ele não foi testa exaustivamente em todas as situações possíveis e pode ter bugs!

Se você encontrar algo errado or tiver qualquer comentário, por favor comente o post.

Deixe-me então construir um modelo de exemplo para mostrar como a procedure funciona na prática. Meu exemplo cria a tabela principal T_PRODUCT e algumas outras ao redor dela. Ela tem as seguintes tabelas filhas que podem ter registros excluídos quando eu tentar excluir da principal:

  • T_PRODUCT_WAREHOUSE: os armazéns que mantém cada produto em estoque.
  • T_INVOICE_ITEM: os items dos pedidos.
  • T_INVOICE_ITEM_DELIVERY: as entregas dos items (eles podem ser entregues parcialmente).
  • T_PRODUCT: um auto-relacionamento que liga um produto a outro equivalente.

Você pode baixar o modelo exemplo aqui.

-- Generate, execute and show (but don't COMMIT yet) the DELETE statements
begin
	P_DELETE_CASCADE(
		p_owner=>user,
		p_table=>'T_PRODUCT',
		p_where=>'product_id in (1,6)',
		p_mode => 'GX'
		);
end;
/

PL/SQL procedure successfully completed.
-- Verify the statements and the number of rows affected by each one
col statement for a80 word_wrap
select table_name, statement, rows_deleted from tmp_delete_cascade_stmt order by lev desc, id;

TABLE_NAME                          STATEMENT                                                                        ROWS_DELETED
----------------------------------- -------------------------------------------------------------------------------- ------------
T_INVOICE_ITEM                      DELETE FROM EDUARDO.T_INVOICE_ITEM WHERE (PRODUCT_ID) in (SELECT                            1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    (PRODUCT_EQUIVALENT) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_PRODUCT_WAREHOUSE                 DELETE FROM EDUARDO.T_PRODUCT_WAREHOUSE WHERE (PRODUCT_ID) in (SELECT                       1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    (PRODUCT_EQUIVALENT) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_INVOICE_ITEM_DELIVERY             DELETE FROM EDUARDO.T_INVOICE_ITEM_DELIVERY WHERE (INVOICE_ID, PRODUCT_ID) in               4
                                    (SELECT
                                    INVOICE_ID, PRODUCT_ID FROM EDUARDO.T_INVOICE_ITEM WHERE
                                    (PRODUCT_ID) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_PRODUCT                           DELETE FROM EDUARDO.T_PRODUCT WHERE (PRODUCT_EQUIVALENT) in (SELECT                         1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_INVOICE_ITEM                      DELETE FROM EDUARDO.T_INVOICE_ITEM WHERE (PRODUCT_ID) in (SELECT                            3
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_PRODUCT_WAREHOUSE                 DELETE FROM EDUARDO.T_PRODUCT_WAREHOUSE WHERE (PRODUCT_ID) in (SELECT                       4
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_PRODUCT                           DELETE FROM EDUARDO.T_PRODUCT WHERE product_id in (1,6)                                     2

7 rows selected.
-- Check which records remain
select * from T_PRODUCT;

PRODUCT_ID PRODUCT_NAME         PROVIDER_ID PROVIDER_PRODUCT_ID PRODUCT_EQUIVALENT
---------- -------------------- ----------- ------------------- ------------------
         3 Product 3                      1                9992
         4 Product 4                      1                9993
         5 Product 5                      2                9992

select * from T_PRODUCT_WAREHOUSE;

PRODUCT_ID WAREHOUSE_ID PIECES_IN_STOCK
---------- ------------ ---------------
         3            1              11
         4            2               3
         5            2               1

select * from T_INVOICE;

INVOICE_ID INVOICE_DATE
---------- -----------------
         1 26-MAR-2018 12:06
         2 26-MAR-2018 12:06
         3 26-MAR-2018 12:06
         4 26-MAR-2018 12:06

select * from T_INVOICE_ITEM;

INVOICE_ID PRODUCT_ID  QUANTITY
---------- ---------- ---------
         3          4         3
         4          3         5

select * from T_INVOICE_ITEM_DELIVERY;

INVOICE_ID PRODUCT_ID DELIVERY_DATE     QUANTITY_DELIVERED
---------- ---------- ----------------- ------------------
         3          4 26-MAR-2018 12:56                  3

Perceba que os pedidos 1 e 2 ainda existem na tabela T_INVOICE, mesmo sem nenhum item relacionado na tabela T_INVOICE_ITEM. Isto é normal, já que esta regra não é validada por nenhuma constraint criada no banco de dados. Eu preciso apagar estes registros manualmente.

Perceba também que o Produto 2 foi apagado porque ele era dependente do Produto 1. Isto é um resultado esperado e mostra o risco de usar este script sem os cuidados necessários ;-).

Depois de confirmar que tudo está OK, você pode fazer o COMMIT para confirmar as alterações.

Espero que gostem!!!

An Enterprise Manager-like chart in SQL*Plus

Português/English

After showing how to use 256 colors and parsing parameters both in SQL*Plus, I now present a very interesting script (at least for me ;-)) I created that mimic the main Enterprise Manager chart in SQL*Plus.

The script builds a “chart” by using coloured blocks (or characters instead) to show active sessions broken down by Wait Class, or broken down by Wait Event for a provided Wait Class.

The script can receive a lot of parameters that control the period and other details as I present below.

Please note that the script gather information from ASH repository, so you should have Oracle Diagnostics Pack license to use it.

Here are the parameters you can provide:

  • class: specify the Wait Class (just the 3 first letters are enough) to show their events breakdown. If not specified, show the Wait Classes breakdown.
  • begin: begin date as a DATE expression, like sysdate-1/24.
  • end: end date as a DATE expression, like sysdate-1/24.
  • minutes: if a positive number is provided, the period is defined starting with &BEGIN and ending the number of minutes after that (&END parameter is ignored).
    if a negative number is provided, the period is defined starting the number of minutes before now (sysdate) and ending now (&BEGIN and &END are ignored).
  • view: the ASH view to query. Enter D to query DBA_ACTIVE_SESS_HISTORY. Any other value will use GV$ACTIVE_SESSION_HISTORY.
  • group: group (Y) or not (N) the rows by minute.
  • color: define if you want to see characters instead of colors (N), 8 colors (8) or 256 colors (256).
  • show: SHOW (Y) or not (N) the number of sessions for each Wait Class or Event.
  • mult: the multiplier to define how many boxes will be painted for each Average Active Session. If mult=2 and there are 3 sessions, 6 (3 * 2) boxes will be painted.
  • zeros: show (Y) or not (N) the samples that have no active session (especially useful when showing just one Wait Class
  • inst: specify an Instance to show (for RAC environments). NULL show all instances.

Here are some examples on how to call the script:

-- Execute the script with all the default parameters:
@em

-- Show the events break down for the APPLICATION wait class, from DBA_HIST ash view, don't show samples with ZERO active sessions
@em class=app view=D zeros=n

-- Show the last 120 minutes of data, don't group by minute
@em minutes=-120 group=N

-- Show the chart from yesterday, don't show the numbers of each wait class
@em begin=trunc(sysdate-1) end=trunc(sysdate) show=n

-- Show the chart with characters instead of colors, show 3 characters for each active session
@em color=n mult=3

And here are three sample outputs:

em main

The main chart showing some Application issues

em app

Drilling down to realize it is a row lock contention

em1

Another main chart, now showing User I/O issues

em2

User I/O events break down

em char

Using characters instead of colours

In the beginning of the script, right after the header, you will find the default values for every parameter. You can change the values as you like.

Please note that the main script can call some auxiliar ones: my parameters parser (read this) and one of three to define the colours (or characters) that will be used (read this).

Also note that I am using the PLAN_TABLE to save the ASH data that I will work with, just to avoid reading it many times in my subqueries. This solution is used in eDB360 and SQLd360 tools, from my fellow friends Carlos Sierra and Mauro Pagano.

So here you have the scripts. Please copy the content and save them all in the same directory. Then run em.sql as explained above. You can also download the scripts from github.

Any comments, suggestions, corrections, please let me know.

Enjoy!!!!

$cat em_nocolors.sql
define BG_ONCPU=""
define BG_USER =""
define BG_SYST =""
define BG_CONC =""
define BG_APPL =""
define BG_COMM =""
define BG_CONF =""
define BG_ADMI =""
define BG_SCHE =""
define BG_CLUS =""
define BG_QUEU =""
define BG_NETW =""
define BG_OTHE =""

define CH_ONCPU="."
define CH_USER ="U"
define CH_SYST ="S"
define CH_CONC ="C"
define CH_APPL ="A"
define CH_COMM ="M"
define CH_CONF ="F"
define CH_ADMI ="a"
define CH_SCHE ="s"
define CH_CLUS ="c"
define CH_QUEU ="q"
define CH_NETW ="n"
define CH_OTHE ="o"

define CH_EV1="1"
define CH_EV2="2"
define CH_EV3="3"
define CH_EV4="4"
define CH_EV5="5"
$cat em_8colors.sql
define BG_ONCPU="[42m"
define BG_USER ="[44m"
define BG_SYST ="[46m"
define BG_CONC ="[47m"
define BG_APPL ="[41m"
define BG_COMM ="[43m"
define BG_CONF ="[40m"
define BG_ADMI ="[34m"
define BG_SCHE ="[36m"
define BG_CLUS ="[31m"
define BG_QUEU ="[33m"
define BG_NETW ="[35m"
define BG_OTHE ="[45m"

define CH_ONCPU=" "
define CH_USER =" "
define CH_SYST =" "
define CH_CONC =" "
define CH_APPL =" "
define CH_COMM =" "
define CH_CONF =" "
define CH_ADMI ="a"
define CH_SCHE ="s"
define CH_CLUS ="c"
define CH_QUEU ="q"
define CH_NETW ="n"
define CH_OTHE ="o"

define CH_EV1=" "
define CH_EV2=" "
define CH_EV3=" "
define CH_EV4=" "
define CH_EV5=" "
$cat em_256colors.sql
define BG_ONCPU="[42m"
define BG_USER ="[44m"
define BG_SYST ="[46m"
define BG_CONC ="[48;5;1m"
define BG_APPL ="[48;5;9m"
define BG_COMM ="[48;5;202m"
define BG_CONF ="[48;5;52m"
define BG_ADMI ="[48;5;95m"
define BG_SCHE ="[48;5;157m"
define BG_CLUS ="[48;5;230m"
define BG_QUEU ="[48;5;144m"
define BG_NETW ="[48;5;131m"
define BG_OTHE ="[48;5;224m"

define CH_ONCPU=" "
define CH_USER =" "
define CH_SYST =" "
define CH_CONC =" "
define CH_APPL =" "
define CH_COMM =" "
define CH_CONF =" "
define CH_ADMI =" "
define CH_SCHE =" "
define CH_CLUS =" "
define CH_QUEU =" "
define CH_NETW =" "
define CH_OTHE =" "

define CH_EV1=" "
define CH_EV2=" "
define CH_EV3=" "
define CH_EV4=" "
define CH_EV5=" "
$cat em.sql
/*
This script emulates the EM performance chart
Created by Eduardo Claro, 2018-03-14
Last changes on 2018-03-20

WARNING: It uses ASH repository, so YOU NEED Diagnostics Pack

It can be used to display the Wait Class or the 5 main events of a specific Wait Class

Parameters (can be passed in any order in the form parameter=value)

	class    : specify the Wait Class (just the 3 first letters are enough) to show their events breakdown. If not specified, show the Wait Classes breakdown.
	begin    : begin date as a DATE expression, like sysdate-1/24.
	end      : end   date as a DATE expression, like sysdate-1/24.
	minutes  : if a positive number is provided, the period is defined starting with &BEGIN and ending the number of minutes after that (&END parameter is ignored).
	           if a negative number is provided, the period is defined starting the number of minutes before now (sysdate) and ending now (&BEGIN and &END are ignored).
	view     : the ASH view to query. Enter D to query DBA_ACTIVE_SESS_HISTORY. Any other value will use GV$ACTIVE_SESSION_HISTORY.
	group    : group (Y) or not (N) the rows by minute.
	color    : define if you want to see characters instead of colors (no), 8 colors (8) or 256 colors (256).
	show     : SHOW (Y) or not (N) the number of sessions for each Wait Class or Event.
	mult     : the multiplier to define how many boxes will be painted for each Average Active Session. If mult=2 and there are 3 sessions, 6 (3 * 2) boxes will be painted.
	zeros    : show (Y) or not (N) the samples that have no active session (especially useful when showing just one Wait Class
	inst     : specify an Instance to show (for RAC environments). NULL show all instances.

Example:

	@em
	@em class=app view=D
	@em group=N
	@em begin=sysdate-3/24 show=n

Attention:
	If the script seems to hang waiting for something, it is probably due to the SQL*Plus number variables (&1-&16) not be defined
	The SQL*Plus variables &1-&16 must be defined BEFORE running this script
	It is usually done by putting some statements in the login.sql, like this:
		set termout off
		COLUMN  1  NEW_VALUE  1    noprint
		COLUMN  2  NEW_VALUE  2    noprint
		COLUMN  3  NEW_VALUE  3    noprint
		...
		SELECT '' as "1",'' as "2",'' as "3" ... FROM dual;
		set termout on
*/

-- Default values for the parameters
define CLASS=""
define BEGIN="sysdate-1/24"
define END="sysdate"
define MINUTES=""
define VIEW=""
define GROUP=Y
define COLOR=256
define SHOW=N
define MULT=1
define ZEROS=Y
define INST=""

-- Parse the received parameters
@@parser

clear breaks
set echo off timing off ver off feed off

define RESET =[0m
alter session set nls_Date_format="dd-MON hh24:mi:ss";
set pages 500

set term off

col MULT new_value MULT
col xVIEW new_value VIEW
col xEND new_value END
col xBEGIN new_value BEGIN
col QUERY new_value QUERY
col INSTCOL new_value INSTCOL
col SCRIPT new_value SCRIPT
select
--	case when upper('&GROUP') = 'Y' then &MULT else 1 end MULT,
	case when upper('&VIEW') = 'D' then 'DBA_HIST_ACTIVE_SESS_HISTORY' else 'GV$ACTIVE_SESSION_HISTORY' end xVIEW,
	case when upper('&VIEW') = 'D' then 'INSTANCE_NUMBER' else 'INST_ID' end INSTCOL,
	case when to_number('&MINUTES') < 0 then q'[trunc(sysdate,'mi')+(&MINUTES./24/60)]' else q'[&BEGIN]' end xBEGIN,
	case when '&MINUTES' is null then q'[&END]' when to_number('&MINUTES') < 0 then 'sysdate' else q'[&BEGIN.+&MINUTES./24/60]' end xEND,
	case when '&CLASS' IS NULL then 'ash_class_final' else 'ash_event_final' end || case when upper('&SHOW') = 'N' then '2' end QUERY,
	case when '&COLOR' in ('8','256') then 'em_&COLOR.colors' else 'em_nocolors' end SCRIPT
from dual;

-- Set the colors
@@&SCRIPT

col MINUTE head "TIME"
col chart for a400 trunc
col ONCPU  for 99.9 head "OnCPU"
col USERIO for 99.9 head "UsrIO"
col SYSTIO for 99.9 head "SysIO"
col CONC   for 99.9 head "Conc"
col APPLIC for 99.9 head "Appl"
col COMM   for 99.9 head "Comm"
col CONF   for 99.9 head "Conf"
col ADMIN  for 99.9 head "Admin"
col SCHED  for 99.9 head "Sched"
col CLUST  for 99.9 head "Clust"
col QUEUE  for 99.9 head "Queue"
col NETW   for 99.9 head "Netwk"
col OTHER  for 99.9 head "Other"
col TOTAL  for 99.9 head "TOTAL"
col EV1  for 99.9 
col EV2  for 99.9 
col EV3  for 99.9 
col EV4  for 99.9 
col EV5  for 99.9 

-- Using DELETE instead of TRUNCATE just for not to end a transaction in course
delete from plan_table where STATEMENT_ID = 'EM-SQLPLUS'
;
insert into plan_table (STATEMENT_ID, TIMESTAMP, REMARKS)
select
	'EM-SQLPLUS',
	cast(sample_time as date) sample_time, --remove sub-seconds difference of the RAC instances
	'ALL SAMPLES' wait_class
from &VIEW
where sample_time between &begin and &end
and ('&INST' is null OR &INSTCOL = nvl('&INST','&INSTCOL'))
;
insert into plan_table (STATEMENT_ID, TIMESTAMP, REMARKS, COST)
select
	'EM-SQLPLUS',
	cast(sample_time as date) sample_time, --remove sub-seconds difference of the RAC instances
	nvl(wait_class,'On CPU') wait_class, count(*) sessions
from &VIEW
where '&class' IS NULL and sample_time between &begin and &end 
and ('&INST' is null OR &INSTCOL = nvl('&INST','&INSTCOL'))
group by cast(sample_time as date), wait_class
;
insert into plan_table (STATEMENT_ID, TIMESTAMP, REMARKS, PLAN_ID, COST)
select
	'EM-SQLPLUS',
	cast(sample_time as date) sample_time, --remove sub-seconds difference of the RAC instances
	event, event_id, count(*) sessions
from &VIEW
where '&class' IS NOT NULL and sample_time between &begin and &end 
	and wait_class = (select wait_class from v$event_name where upper(wait_class) like upper('&class%') and rownum = 1)
and ('&INST' is null OR &INSTCOL = nvl('&INST','&INSTCOL'))
group by cast(sample_time as date), event, event_id
;
set term on

WITH
ash_allsamples AS (
	select distinct
		TIMESTAMP sample_time
	from plan_table
	where STATEMENT_ID = 'EM-SQLPLUS' and REMARKS = 'ALL SAMPLES'
),
ash_class AS (
	select * from (
		select TIMESTAMP sample_time, REMARKS wait_class, COST sessions,
			count(distinct TIMESTAMP) over (partition by trunc(TIMESTAMP,'MI')) SAMPLES_PER_MIN
		from plan_table
		where STATEMENT_ID = 'EM-SQLPLUS'
	) where wait_class  'ALL SAMPLES'
),
ash_event AS (
	select * from (
		select TIMESTAMP sample_time, REMARKS event, PLAN_ID event_id, COST sessions,
			count(distinct TIMESTAMP) over (partition by trunc(TIMESTAMP,'MI')) SAMPLES_PER_MIN
		from plan_table
		where STATEMENT_ID = 'EM-SQLPLUS'
	) where event  'ALL SAMPLES'
),
ash_mainevents AS (
	select 
		event, position
	from (
		select 
			event,
			rank() over (order by sum(sessions) desc, event_id) position
		from ash_event
		group by event, event_id
		order by sum(sessions) desc
	)
	where position  0)
/

col CHART for a30
col xGROUP head "WAIT CLASS" for a20 trunc
SELECT * FROM (
	select 'On CPU'         xGROUP, '&BG_ONCPU' || lpad('&CH_ONCPU', 5 , '&CH_ONCPU') || '&RESET' chart from dual UNION ALL
	select 'User I/O'       xGROUP, '&BG_USER'  || lpad('&CH_USER' , 5 , '&CH_USER' ) || '&RESET' chart from dual UNION ALL
	select 'System I/O'     xGROUP, '&BG_SYST'  || lpad('&CH_SYST' , 5 , '&CH_SYST' ) || '&RESET' chart from dual UNION ALL
	select 'Application'    xGROUP, '&BG_APPL'  || lpad('&CH_APPL' , 5 , '&CH_APPL' ) || '&RESET' chart from dual UNION ALL
	select 'Concurrency'    xGROUP, '&BG_CONC'  || lpad('&CH_CONC' , 5 , '&CH_CONC' ) || '&RESET' chart from dual UNION ALL
	select 'Configuration'  xGROUP, '&BG_CONF'  || lpad('&CH_CONF' , 5 , '&CH_CONF' ) || '&RESET' chart from dual UNION ALL
	select 'Commit'         xGROUP, '&BG_COMM'  || lpad('&CH_COMM' , 5 , '&CH_COMM' ) || '&RESET' chart from dual UNION ALL
	select 'Administrative' xGROUP, '&BG_ADMI'  || lpad('&CH_ADMI' , 5 , '&CH_ADMI' ) || '&RESET' chart from dual UNION ALL
	select 'Scheduler'      xGROUP, '&BG_SCHE'  || lpad('&CH_SCHE' , 5 , '&CH_SCHE' ) || '&RESET' chart from dual UNION ALL
	select 'Cluster'        xGROUP, '&BG_CLUS'  || lpad('&CH_CLUS' , 5 , '&CH_CLUS' ) || '&RESET' chart from dual UNION ALL
	select 'Queueing'       xGROUP, '&BG_QUEU'  || lpad('&CH_QUEU' , 5 , '&CH_QUEU' ) || '&RESET' chart from dual UNION ALL
	select 'Network'        xGROUP, '&BG_NETW'  || lpad('&CH_NETW' , 5 , '&CH_NETW' ) || '&RESET' chart from dual UNION ALL
	select 'Other'          xGROUP, '&BG_OTHE'  || lpad('&CH_OTHE' , 5 , '&CH_OTHE' ) || '&RESET' chart from dual 
) WHERE '&class' IS NULL
;
col xGROUP head "EVENT" for a64 trunc
WITH events AS (
	select 
		event, position
	from (
		select 
			REMARKS event,
			rank() over (order by SUM(COST) desc, PLAN_ID) position
		from plan_table
		where STATEMENT_ID = 'EM-SQLPLUS' and REMARKS  'ALL SAMPLES'
		group by REMARKS, PLAN_ID
		order by SUM(COST) desc
	)
	where position <= 5
)
select * from (
	select event   xGROUP, '&BG_USER'  || lpad('&CH_EV1' , 5 , '&CH_EV1' ) || '&RESET' chart from events WHERE position = 1 UNION ALL
	select event   xGROUP, '&BG_SYST'  || lpad('&CH_EV2' , 5 , '&CH_EV2' ) || '&RESET' chart from events WHERE position = 2 UNION ALL
	select event   xGROUP, '&BG_APPL'  || lpad('&CH_EV3' , 5 , '&CH_EV3' ) || '&RESET' chart from events WHERE position = 3 UNION ALL
	select event   xGROUP, '&BG_CONC'  || lpad('&CH_EV4' , 5 , '&CH_EV4' ) || '&RESET' chart from events WHERE position = 4 UNION ALL
	select event   xGROUP, '&BG_CONF'  || lpad('&CH_EV5' , 5 , '&CH_EV5' ) || '&RESET' chart from events WHERE position = 5 UNION ALL
	select 'Other' xGROUP, '&BG_OTHE'  || lpad('&CH_OTHE' , 5 , '&CH_OTHE' ) || '&RESET' chart from dual 
) WHERE '&class' IS NOT NULL
;

define 1=""
define 2=""
define 3=""
define 4=""
define 5=""
define 6=""
define 7=""
define 8=""
define 9=""
define 10=""
define 11=""
define 12=""
define 13=""
define 14=""
define 15=""
define 16=""

Um gráfico do Enterprise Manager no SQL*Plus

Português/English

Depois de mostrar como usar 256 cores and como definir parâmetros no SQL*Plus, eu agora apresento um script bem interessante (pelo menos pra mim ;-)) que eu criei e imita o gráfico principal do Enterprise Manager no SQL*Plus.

O script constrói um “gráfico” usando blocos coloridos (ou caracteres) para mostrar sessões ativas por Wait Class, or por evento para uma Wait Class fornecida.

O script pode receber vários parâmetros que controlam o período e outros detalhes que eu explico abaixo.

Note que o script coleta informação do repositório do ASH, e portanto você deve ter licença do Oracle Diagnostics Pack para utilizá-lo.

Aqui estão os parâmetros que você pode fornecer:

  • class: define uma Wait Class (apenas as 3 primeiras letras são suficientes) para mostrar seus eventos. Se não especificado, mostra as sessões para cadaWait Class.
  • begin: data inicial em uma expressão tipo DATE, como em sysdate-1/24.
  • end: data final em uma expressão tipo DATE, como em sysdate-1/24.
  • minutes: se for um número positivo, o período é definido começando em &BEGIN e terminando após o número de minutos fornecido (o parâmetro &END é ignorado).
    se for um número negativo, o período é definido começando o número de minutos antes de agora (sysdate) e terminando agora (&BEGIN e &END são ignorados).
  • view: a view do ASH a ser consultada. Entre D para consultar a DBA_ACTIVE_SESS_HISTORY. Qualquer outro valor vai usar a view GV$ACTIVE_SESSION_HISTORY.
  • group: agrupa (Y) ou não (N) as linhas por minuto.
  • color: define se você quer ver characteres (N), 8 cores (8) ou 256 cores (256).
  • show: mostra (Y) ou não (N) a quantidade de sessões para cadaWait Class ou evento.
  • mult: o multiplicador que define quantos blocos coloridos serão mostrados para cada sessão ativa. Se mult=2 e há 3 sessões, 6 (3 * 2) blocos serão mostrados.
  • zeros: mostra (Y) ou não (N) os momentos em que não houve sessão ativa (especialmente útil quando mostrando os eventos de uma Wait Class).
  • inst: especifique uma instância do RAC para mostrar. NULL show all instances.

Aqui estão alguns exemplos de como executar o script:

-- Execute the script with all the default parameters:
@em

-- Show the events break down for the APPLICATION wait class, from DBA_HIST ash view, don't show samples with ZERO active sessions
@em class=app view=D zeros=n

-- Show the last 120 minutes of data, don't group by minute
@em minutes=-120 group=N

-- Show the chart from yesterday, don't show the numbers of each wait class
@em begin=trunc(sysdate-1) end=trunc(sysdate) show=n

-- Show the chart with characters instead of colors, show 3 characters for each active session
@em color=n mult=3

E aqui alguns exemplos de resultado:

em main

O gráfico principal mostrando alguns problemas de Aplicação

em app

Aprofundando para verificar que o problema era de lock de linhas

em1

Outro gráfico principal, agora mostrando predominância de User I/O

em2

Quebrando os eventos de User I/O

em char

Usando caracteres ao invés de cores

No início do script, logo depois do cabeçalho, você vai encontrar os valores default para cada parâmetro. Você pode mudar os valores como quiser.

Note que o script principal pode chamar alguns auxiliares: o meu parser (leia isto) e um dos três para definir as cores (ou caracteres) que serão usados (leia isto).

Note também que eu estou usando a PLAN_TABLE para salvar os dados do ASH que em seguida vou processar, pra evitar de ter que lê-los várias vezes do repositório. Essa solução é utilizada nas ferramentas eDB360 and SQLd360 tools, dos meus amigos Carlos Sierra e Mauro Pagano.

Aqui estão os scripts. Copie o conteúdo e salve-os no mesmo diretório. Então rode o em.sql como explicado acima. Você também pode baixar os scripts do github.

Qualquer comentário, sugestão, correção, por favor me avise.

Divirtam-se!!!!

$cat em_nocolors.sql
define BG_ONCPU=""
define BG_USER =""
define BG_SYST =""
define BG_CONC =""
define BG_APPL =""
define BG_COMM =""
define BG_CONF =""
define BG_ADMI =""
define BG_SCHE =""
define BG_CLUS =""
define BG_QUEU =""
define BG_NETW =""
define BG_OTHE =""

define CH_ONCPU="."
define CH_USER ="U"
define CH_SYST ="S"
define CH_CONC ="C"
define CH_APPL ="A"
define CH_COMM ="M"
define CH_CONF ="F"
define CH_ADMI ="a"
define CH_SCHE ="s"
define CH_CLUS ="c"
define CH_QUEU ="q"
define CH_NETW ="n"
define CH_OTHE ="o"

define CH_EV1="1"
define CH_EV2="2"
define CH_EV3="3"
define CH_EV4="4"
define CH_EV5="5"
$cat em_8colors.sql
define BG_ONCPU="[42m"
define BG_USER ="[44m"
define BG_SYST ="[46m"
define BG_CONC ="[47m"
define BG_APPL ="[41m"
define BG_COMM ="[43m"
define BG_CONF ="[40m"
define BG_ADMI ="[34m"
define BG_SCHE ="[36m"
define BG_CLUS ="[31m"
define BG_QUEU ="[33m"
define BG_NETW ="[35m"
define BG_OTHE ="[45m"

define CH_ONCPU=" "
define CH_USER =" "
define CH_SYST =" "
define CH_CONC =" "
define CH_APPL =" "
define CH_COMM =" "
define CH_CONF =" "
define CH_ADMI ="a"
define CH_SCHE ="s"
define CH_CLUS ="c"
define CH_QUEU ="q"
define CH_NETW ="n"
define CH_OTHE ="o"

define CH_EV1=" "
define CH_EV2=" "
define CH_EV3=" "
define CH_EV4=" "
define CH_EV5=" "
$cat em_256colors.sql
define BG_ONCPU="[42m"
define BG_USER ="[44m"
define BG_SYST ="[46m"
define BG_CONC ="[48;5;1m"
define BG_APPL ="[48;5;9m"
define BG_COMM ="[48;5;202m"
define BG_CONF ="[48;5;52m"
define BG_ADMI ="[48;5;95m"
define BG_SCHE ="[48;5;157m"
define BG_CLUS ="[48;5;230m"
define BG_QUEU ="[48;5;144m"
define BG_NETW ="[48;5;131m"
define BG_OTHE ="[48;5;224m"

define CH_ONCPU=" "
define CH_USER =" "
define CH_SYST =" "
define CH_CONC =" "
define CH_APPL =" "
define CH_COMM =" "
define CH_CONF =" "
define CH_ADMI =" "
define CH_SCHE =" "
define CH_CLUS =" "
define CH_QUEU =" "
define CH_NETW =" "
define CH_OTHE =" "

define CH_EV1=" "
define CH_EV2=" "
define CH_EV3=" "
define CH_EV4=" "
define CH_EV5=" "
$cat em.sql
/*
This script emulates the EM performance chart
Created by Eduardo Claro, 2018-03-14
Last changes on 2018-03-20

WARNING: It uses ASH repository, so YOU NEED Diagnostics Pack

It can be used to display the Wait Class or the 5 main events of a specific Wait Class

Parameters (can be passed in any order in the form parameter=value)

	class    : specify the Wait Class (just the 3 first letters are enough) to show their events breakdown. If not specified, show the Wait Classes breakdown.
	begin    : begin date as a DATE expression, like sysdate-1/24.
	end      : end   date as a DATE expression, like sysdate-1/24.
	minutes  : if a positive number is provided, the period is defined starting with &BEGIN and ending the number of minutes after that (&END parameter is ignored).
	           if a negative number is provided, the period is defined starting the number of minutes before now (sysdate) and ending now (&BEGIN and &END are ignored).
	view     : the ASH view to query. Enter D to query DBA_ACTIVE_SESS_HISTORY. Any other value will use GV$ACTIVE_SESSION_HISTORY.
	group    : group (Y) or not (N) the rows by minute.
	color    : define if you want to see characters instead of colors (no), 8 colors (8) or 256 colors (256).
	show     : SHOW (Y) or not (N) the number of sessions for each Wait Class or Event.
	mult     : the multiplier to define how many boxes will be painted for each Average Active Session. If mult=2 and there are 3 sessions, 6 (3 * 2) boxes will be painted.
	zeros    : show (Y) or not (N) the samples that have no active session (especially useful when showing just one Wait Class
	inst     : specify an Instance to show (for RAC environments). NULL show all instances.

Example:

	@em
	@em class=app view=D
	@em group=N
	@em begin=sysdate-3/24 show=n

Attention:
	If the script seems to hang waiting for something, it is probably due to the SQL*Plus number variables (&1-&16) not be defined
	The SQL*Plus variables &1-&16 must be defined BEFORE running this script
	It is usually done by putting some statements in the login.sql, like this:
		set termout off
		COLUMN  1  NEW_VALUE  1    noprint
		COLUMN  2  NEW_VALUE  2    noprint
		COLUMN  3  NEW_VALUE  3    noprint
		...
		SELECT '' as "1",'' as "2",'' as "3" ... FROM dual;
		set termout on
*/

-- Default values for the parameters
define CLASS=""
define BEGIN="sysdate-1/24"
define END="sysdate"
define MINUTES=""
define VIEW=""
define GROUP=Y
define COLOR=256
define SHOW=N
define MULT=1
define ZEROS=Y
define INST=""

-- Parse the received parameters
@@parser

clear breaks
set echo off timing off ver off feed off

define RESET =[0m
alter session set nls_Date_format="dd-MON hh24:mi:ss";
set pages 500

set term off

col MULT new_value MULT
col xVIEW new_value VIEW
col xEND new_value END
col xBEGIN new_value BEGIN
col QUERY new_value QUERY
col INSTCOL new_value INSTCOL
col SCRIPT new_value SCRIPT
select
--	case when upper('&GROUP') = 'Y' then &MULT else 1 end MULT,
	case when upper('&VIEW') = 'D' then 'DBA_HIST_ACTIVE_SESS_HISTORY' else 'GV$ACTIVE_SESSION_HISTORY' end xVIEW,
	case when upper('&VIEW') = 'D' then 'INSTANCE_NUMBER' else 'INST_ID' end INSTCOL,
	case when to_number('&MINUTES') < 0 then q'[trunc(sysdate,'mi')+(&MINUTES./24/60)]' else q'[&BEGIN]' end xBEGIN,
	case when '&MINUTES' is null then q'[&END]' when to_number('&MINUTES') < 0 then 'sysdate' else q'[&BEGIN.+&MINUTES./24/60]' end xEND,
	case when '&CLASS' IS NULL then 'ash_class_final' else 'ash_event_final' end || case when upper('&SHOW') = 'N' then '2' end QUERY,
	case when '&COLOR' in ('8','256') then 'em_&COLOR.colors' else 'em_nocolors' end SCRIPT
from dual;

-- Set the colors
@@&SCRIPT

col MINUTE head "TIME"
col chart for a400 trunc
col ONCPU  for 99.9 head "OnCPU"
col USERIO for 99.9 head "UsrIO"
col SYSTIO for 99.9 head "SysIO"
col CONC   for 99.9 head "Conc"
col APPLIC for 99.9 head "Appl"
col COMM   for 99.9 head "Comm"
col CONF   for 99.9 head "Conf"
col ADMIN  for 99.9 head "Admin"
col SCHED  for 99.9 head "Sched"
col CLUST  for 99.9 head "Clust"
col QUEUE  for 99.9 head "Queue"
col NETW   for 99.9 head "Netwk"
col OTHER  for 99.9 head "Other"
col TOTAL  for 99.9 head "TOTAL"
col EV1  for 99.9 
col EV2  for 99.9 
col EV3  for 99.9 
col EV4  for 99.9 
col EV5  for 99.9 

-- Using DELETE instead of TRUNCATE just for not to end a transaction in course
delete from plan_table where STATEMENT_ID = 'EM-SQLPLUS'
;
insert into plan_table (STATEMENT_ID, TIMESTAMP, REMARKS)
select
	'EM-SQLPLUS',
	cast(sample_time as date) sample_time, --remove sub-seconds difference of the RAC instances
	'ALL SAMPLES' wait_class
from &VIEW
where sample_time between &begin and &end
and ('&INST' is null OR &INSTCOL = nvl('&INST','&INSTCOL'))
;
insert into plan_table (STATEMENT_ID, TIMESTAMP, REMARKS, COST)
select
	'EM-SQLPLUS',
	cast(sample_time as date) sample_time, --remove sub-seconds difference of the RAC instances
	nvl(wait_class,'On CPU') wait_class, count(*) sessions
from &VIEW
where '&class' IS NULL and sample_time between &begin and &end 
and ('&INST' is null OR &INSTCOL = nvl('&INST','&INSTCOL'))
group by cast(sample_time as date), wait_class
;
insert into plan_table (STATEMENT_ID, TIMESTAMP, REMARKS, PLAN_ID, COST)
select
	'EM-SQLPLUS',
	cast(sample_time as date) sample_time, --remove sub-seconds difference of the RAC instances
	event, event_id, count(*) sessions
from &VIEW
where '&class' IS NOT NULL and sample_time between &begin and &end 
	and wait_class = (select wait_class from v$event_name where upper(wait_class) like upper('&class%') and rownum = 1)
and ('&INST' is null OR &INSTCOL = nvl('&INST','&INSTCOL'))
group by cast(sample_time as date), event, event_id
;
set term on

WITH
ash_allsamples AS (
	select distinct
		TIMESTAMP sample_time
	from plan_table
	where STATEMENT_ID = 'EM-SQLPLUS' and REMARKS = 'ALL SAMPLES'
),
ash_class AS (
	select * from (
		select TIMESTAMP sample_time, REMARKS wait_class, COST sessions,
			count(distinct TIMESTAMP) over (partition by trunc(TIMESTAMP,'MI')) SAMPLES_PER_MIN
		from plan_table
		where STATEMENT_ID = 'EM-SQLPLUS'
	) where wait_class  'ALL SAMPLES'
),
ash_event AS (
	select * from (
		select TIMESTAMP sample_time, REMARKS event, PLAN_ID event_id, COST sessions,
			count(distinct TIMESTAMP) over (partition by trunc(TIMESTAMP,'MI')) SAMPLES_PER_MIN
		from plan_table
		where STATEMENT_ID = 'EM-SQLPLUS'
	) where event  'ALL SAMPLES'
),
ash_mainevents AS (
	select 
		event, position
	from (
		select 
			event,
			rank() over (order by sum(sessions) desc, event_id) position
		from ash_event
		group by event, event_id
		order by sum(sessions) desc
	)
	where position  0)
/

col CHART for a30
col xGROUP head "WAIT CLASS" for a20 trunc
SELECT * FROM (
	select 'On CPU'         xGROUP, '&BG_ONCPU' || lpad('&CH_ONCPU', 5 , '&CH_ONCPU') || '&RESET' chart from dual UNION ALL
	select 'User I/O'       xGROUP, '&BG_USER'  || lpad('&CH_USER' , 5 , '&CH_USER' ) || '&RESET' chart from dual UNION ALL
	select 'System I/O'     xGROUP, '&BG_SYST'  || lpad('&CH_SYST' , 5 , '&CH_SYST' ) || '&RESET' chart from dual UNION ALL
	select 'Application'    xGROUP, '&BG_APPL'  || lpad('&CH_APPL' , 5 , '&CH_APPL' ) || '&RESET' chart from dual UNION ALL
	select 'Concurrency'    xGROUP, '&BG_CONC'  || lpad('&CH_CONC' , 5 , '&CH_CONC' ) || '&RESET' chart from dual UNION ALL
	select 'Configuration'  xGROUP, '&BG_CONF'  || lpad('&CH_CONF' , 5 , '&CH_CONF' ) || '&RESET' chart from dual UNION ALL
	select 'Commit'         xGROUP, '&BG_COMM'  || lpad('&CH_COMM' , 5 , '&CH_COMM' ) || '&RESET' chart from dual UNION ALL
	select 'Administrative' xGROUP, '&BG_ADMI'  || lpad('&CH_ADMI' , 5 , '&CH_ADMI' ) || '&RESET' chart from dual UNION ALL
	select 'Scheduler'      xGROUP, '&BG_SCHE'  || lpad('&CH_SCHE' , 5 , '&CH_SCHE' ) || '&RESET' chart from dual UNION ALL
	select 'Cluster'        xGROUP, '&BG_CLUS'  || lpad('&CH_CLUS' , 5 , '&CH_CLUS' ) || '&RESET' chart from dual UNION ALL
	select 'Queueing'       xGROUP, '&BG_QUEU'  || lpad('&CH_QUEU' , 5 , '&CH_QUEU' ) || '&RESET' chart from dual UNION ALL
	select 'Network'        xGROUP, '&BG_NETW'  || lpad('&CH_NETW' , 5 , '&CH_NETW' ) || '&RESET' chart from dual UNION ALL
	select 'Other'          xGROUP, '&BG_OTHE'  || lpad('&CH_OTHE' , 5 , '&CH_OTHE' ) || '&RESET' chart from dual 
) WHERE '&class' IS NULL
;
col xGROUP head "EVENT" for a64 trunc
WITH events AS (
	select 
		event, position
	from (
		select 
			REMARKS event,
			rank() over (order by SUM(COST) desc, PLAN_ID) position
		from plan_table
		where STATEMENT_ID = 'EM-SQLPLUS' and REMARKS  'ALL SAMPLES'
		group by REMARKS, PLAN_ID
		order by SUM(COST) desc
	)
	where position <= 5
)
select * from (
	select event   xGROUP, '&BG_USER'  || lpad('&CH_EV1' , 5 , '&CH_EV1' ) || '&RESET' chart from events WHERE position = 1 UNION ALL
	select event   xGROUP, '&BG_SYST'  || lpad('&CH_EV2' , 5 , '&CH_EV2' ) || '&RESET' chart from events WHERE position = 2 UNION ALL
	select event   xGROUP, '&BG_APPL'  || lpad('&CH_EV3' , 5 , '&CH_EV3' ) || '&RESET' chart from events WHERE position = 3 UNION ALL
	select event   xGROUP, '&BG_CONC'  || lpad('&CH_EV4' , 5 , '&CH_EV4' ) || '&RESET' chart from events WHERE position = 4 UNION ALL
	select event   xGROUP, '&BG_CONF'  || lpad('&CH_EV5' , 5 , '&CH_EV5' ) || '&RESET' chart from events WHERE position = 5 UNION ALL
	select 'Other' xGROUP, '&BG_OTHE'  || lpad('&CH_OTHE' , 5 , '&CH_OTHE' ) || '&RESET' chart from dual 
) WHERE '&class' IS NOT NULL
;

define 1=""
define 2=""
define 3=""
define 4=""
define 5=""
define 6=""
define 7=""
define 8=""
define 9=""
define 10=""
define 11=""
define 12=""
define 13=""
define 14=""
define 15=""
define 16=""

 

SQL*Plus 256 Colours in terminal

Português/English

 

As I use a lot SQL*Plus – and I like it 😉 – sometimes I try to use some interesting tips to make my outputs more readable and interesting.

If you use a terminal emulator (like putty in Windows or most terminal emulators in Unix/Linux/Mac environments), you know it has color capabilities. And maybe you probably know that SQL*Plus also can access this capabilities, as explained here and here.

I have been using colours is scripts for some time, but just the 8-color capability. But it happens that some terminals have instead the capability to show up to 256 different colours, which is cool!

The fish.sql script from Tanel Poder (second link above) exploits very well these capability. Stay tuned!

Here I just provide a script that shows all that 256 colours as background and foreground, to ease your job of picking which ones you want to use in your scripts.

$cat term_256colors.sql
/* Show all the 256 colors (if the terminal have this capability) */

col color for a200

prompt
prompt Background colors. USAGE: chr(27) || '[48;5;' || number
define x=48
select
	'id=' || to_char(4 * (level-1) + 1,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 1 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 2,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 2 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 3,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 3 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 4,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 4 ) || 'm' || ' COLOR ' || chr(27) || '[0m' color
from dual connect by level <= 256/4;

prompt
prompt Foreground colors USAGE: chr(27) || '[38;5;' || number
define x=38
select
	'id=' || to_char(4 * (level-1) + 1,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 1 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 2,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 2 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 3,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 3 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 4,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 4 ) || 'm' || ' COLOR ' || chr(27) || '[0m' color
from dual connect by level <= 256/4;

Of course it will only show the colours if you are using a terminal that has this capability.

Here you have an excerpt of the output:

Screen Shot 2018-03-20 at 14.39.18

Screen Shot 2018-03-20 at 14.39.42

Soon you will se a nice script that I created using such capability.

That’s it! I hope you enjoy 😉 .


256 Cores no SQL*Plus usando um terminal

Português/English

Como eu uso bastante o SQL*Plus – e gosto 😉 – às vezes tento usar algumas dicas interessantes para tornar meus resultados mais legíveis e interessantes.

Se você usa um emulador de terminal (como o putty no Windows ou a maioria dos emuladores de terminal em Unix/Linux/Mac), você sabe que ele pode mostrar cores. E você também provavelmente sabe que o SQL*Plus pode também usar essa capacidade, como demonstrado aqui e aqui.

Eu tenho usado há algum tempo cores em scripts, mas apenas a capacidade de 8 cores. Mas acontece que alguns terminais têm a capacidade de mostrar 256 cores ao invés de 8, o que é bem legal!

O script fish.sql do Tanel Poder (segundo link acima) explora muito bem esse recurso.

Aqui eu demonstro um script que mostra as 256 cores de fundo e de frente, para facilitar o seu trabalho de escolher as que deseja utilizar nos seus scripts.

$cat term_256colors.sql
/* Show all the 256 colors (if the terminal have this capability) */

col color for a200

prompt
prompt Background colors. USAGE: chr(27) || '[48;5;' || number
define x=48
select
	'id=' || to_char(4 * (level-1) + 1,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 1 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 2,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 2 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 3,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 3 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 4,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 4 ) || 'm' || ' COLOR ' || chr(27) || '[0m' color
from dual connect by level <= 256/4;

prompt
prompt Foreground colors USAGE: chr(27) || '[38;5;' || number
define x=38
select
	'id=' || to_char(4 * (level-1) + 1,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 1 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 2,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 2 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 3,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 3 ) || 'm' || ' COLOR ' || chr(27) || '[0m' || '    ' ||
	'id=' || to_char(4 * (level-1) + 4,'999') || 
	chr(27) || '[&x.;5;' || ( 4 * (level-1) + 4 ) || 'm' || ' COLOR ' || chr(27) || '[0m' color
from dual connect by level <= 256/4;

Claro que as cores só vão aparecer se você utilizar um terminal que tem essa capacidade.

Aqui você pode ver um pedaço do resultado:

Screen Shot 2018-03-20 at 14.39.18

Screen Shot 2018-03-20 at 14.39.42

Em breve você vai ver por aqui um script bem legal que utiliza estes recursos. Fique ligado!

É isso! Espero que gostem 😉 .

A simple SQL*Plus parameter parser

Português/English

One of the things I like to do as a DBA is to write scripts to facilitate my daily work. Any experienced DBA has his own scripts toolset that is being built over time. Writing scripts help you to automate part of your job and also allow you to learn a lot.

Usually the scripts require or allow to pass some parameter values. For example, if a script show information about tables, I could build it to accept an OWNER and a TABLE_NAME and then show only those tables from that specific owner and with that kind of name.

My old scripts used to accept the parameters by using the ACCEPT SQL*Plus statement, like this:

accept table prompt 'Table Name: '

But this forces me to answer something that not always I want to. What if, for example, I want to see all the tables? I would need to press ENTER to bypass the parameter.

Well, recently I am changing my scripts and building new ones with a different approach: using command line parameters, that could be informed or not.

For example, I could call the script that show tables with a first parameter being the owner, and the second being the table. It would be called this way:

SQL> @tables HR EMP%

In this example I am asking to show the tables that starts with EMP from owner HR.

But there is a caveat in this approach: I need to pass the parameters in the exact order. Moreover, what if I want to define just the second parameter without providing the first?

This type of issue inspired me to write a short and simple script to parse SQL*Plus parameters. This is what I am presenting in this post.

The script simply process all the parameters received in the form “parameter=value” and define SQL*Plus variables with these values, that could then be used in your script. For example, if you provide a parameter as “SHOW=Y”, after the parser script to be executed you will have a variable SHOW with the value “Y”.

This allows you to provide parameters in any arbitrary order for your scripts.

In our tables script, I could provide just the table by calling that this way:

SQL> @tables table=EMP%

Of course, the script should call the parser to perform the “magic”, and then filter the tables with the new “&table” variable.

No more speech, let me show you the parser script and a sample of how to use it.

$cat parser.sql
/*
Parameter parser

This script transform all the parameters (&1-&16) into SQL*Plus variables
It allows you to pass parameters in no specific order in the format PAR=val

Created by Eduardo Claro, 2018-8-15
Last changes on 2018-03-20

Example: if you run the script and the values of &1 is "VAR1='X'", it will define the SQL*Plus variable VAR1 to 'X'

For this to work properly, the number variables should be pre-defined as NULL in the login.sql
*/

store set set.tmp replace
set term off
set feed off ver off echo off head off timing off
spool parser.tmp REPLACE
select
	case when q'!&1!'  is not null then q'!define &1!'  end || chr(10) ||
	case when q'!&2!'  is not null then q'!define &2!'  end || chr(10) ||
	case when q'!&3!'  is not null then q'!define &3!'  end || chr(10) ||
	case when q'!&4!'  is not null then q'!define &4!'  end || chr(10) ||
	case when q'!&5!'  is not null then q'!define &5!'  end || chr(10) ||
	case when q'!&6!'  is not null then q'!define &6!'  end || chr(10) ||
	case when q'!&7!'  is not null then q'!define &7!'  end || chr(10) ||
	case when q'!&8!'  is not null then q'!define &8!'  end || chr(10) ||
	case when q'!&9!'  is not null then q'!define &9!'  end || chr(10) ||
	case when q'!&10!' is not null then q'!define &10!' end || chr(10) ||
	case when q'!&11!' is not null then q'!define &11!' end || chr(10) ||
	case when q'!&12!' is not null then q'!define &12!' end || chr(10) ||
	case when q'!&13!' is not null then q'!define &13!' end || chr(10) ||
	case when q'!&14!' is not null then q'!define &14!' end || chr(10) ||
	case when q'!&15!' is not null then q'!define &15!' end || chr(10) ||
	case when q'!&16!' is not null then q'!define &16!'end statement
from dual;
spool off
@parser.tmp
@set.tmp
set term on

As you can see, the script simply creates some “DEFINE” statements and then executes them.

One important thing: in order for this to work properly, all the SQL*Plus variables from &1 until &16 need to be initially defined. You can do this by including in your login.sql script the following rows (as in SQL*Plus tips. #1):

-- Reset parameters
set termout off
COLUMN 1 NEW_VALUE 1 noprint
COLUMN 2 NEW_VALUE 2 noprint
COLUMN 3 NEW_VALUE 3 noprint
COLUMN 4 NEW_VALUE 4 noprint
COLUMN 5 NEW_VALUE 5 noprint
COLUMN 6 NEW_VALUE 6 noprint
COLUMN 7 NEW_VALUE 7 noprint
COLUMN 8 NEW_VALUE 8 noprint
COLUMN 9 NEW_VALUE 9 noprint
COLUMN 10 NEW_VALUE 10 noprint
COLUMN 11 NEW_VALUE 11 noprint
COLUMN 12 NEW_VALUE 12 noprint
COLUMN 13 NEW_VALUE 13 noprint
COLUMN 14 NEW_VALUE 14 noprint
COLUMN 15 NEW_VALUE 15 noprint
COLUMN 16 NEW_VALUE 16 noprint
SELECT '' "1", '' "5", '' "9",  '' "13"
      ,'' "2", '' "6", '' "10", '' "14"
      ,'' "3", '' "7", '' "11", '' "15"
      ,'' "4", '' "8", '' "12", '' "16"
FROM dual;
set termout on;

Here is the tables script that shows how to use the parser:

$cat tables.sql
-- Define the default parameters values
define OWNER=""
define TABLE=""

-- Call the parser to set the parameters received
@@parser

select owner, table_name, tablespace_name, temporary
from dba_tables
where owner like nvl(upper('&OWNER'),owner)
and table_name like nvl(upper('&TABLE'),table_name)
/

In the upcoming days I will present some interesting scripts I created that use this parser.

That’s it! I hope you enjoy 😉 .


Definindo parametros para scripts SQL*Plus

Português/English

Uma das coisas que eu gosto de fazer como DBA é escrever scripts que facilitam meu trabalho diário. Qualquer DBA experiente tem seu próprio conjunto de scripts que vai sendo construído ao longo do tempo. Escrever scripts ajuda a automatizar parte do trabalho diário e também nos permite aprender bastante.

Normalmente os scripts exigem ou permitem que se passe alguns valores como parâmetros. Por exemplo, se um script mostra informações de tabelas, eu poderia construí-lo para aceitar um OWNER e um TABLE_NAME e então mostrar somente as tabelas daquele “owner” específico e com aquele tipo de nome.

Meus scripts antigos aceitam parâmetros usando o comando ACCEPT do SQL*Plus, assim:

accept table prompt 'Table Name: '

Mas isto me força a responder algo que nem sempre quero. Por exemplo, e se eu quiser ver todas as tabelas? Eu teria que pressionar ENTER para ignorar o parâmetro.

Ultimamente eu tenho alterado meus scripts e escrito novos com uma abordagem diferente: usando parâmetros na linha de comando, que podem ser informados ou não.

Por exemplo, eu poderia chamar o script que mostra as tabelas com um primeiro parâmetro sendo o owner, e o segundo sendo a tabela. Ele seria chamado assim:

SQL> @tables HR EMP%

Neste exemplo estou pedindo pra ver as tabelas que começam com EMP do owner HR.

Mas há um problema com esta abordagem: eu preciso passar os parâmetros na ordem certa. Mais, como fazer se eu quiser definir apenas o segundo parâmetro sem fornecer o primeiro?

Este tipo de problema me inspirou para escrever um script (parser) simples e curto que simplesmente define parâmetros no SQL*Plus. É isto que estou apresentando neste post.

O script simplesmente processa todos os parâmetros recebidos na forma “parâmetro=valor” e define variáveis SQL*Plus com estes valores, que podem em seguida ser usadas pelo seu script. Por exemplo, se você fornece um parâmetro “SHOW=Y”, você terá uma variável SHOW com o valor “Y”.

Isto permite que os parâmetros sejam passados em qualquer ordem para os seus scripts.

Em nosso script de tabelas, eu poderia fornecer simplesmente a tabela chamando-o assim:

SQL> @tables table=EMP%

Claro, o script precisa chamar o parser para fazer a “mágica”, e então filtrar as tabelas com a variável “&table”.

Sem mais conversa, deixe-me mostrar o script parser e um exemplo de como usá-lo.

$cat parser.sql
/*
Parameter parser

This script transform all the parameters (&1-&16) into SQL*Plus variables
It allows you to pass parameters in no specific order in the format PAR=val

Created by Eduardo Claro, 2018-8-15
Last changes on 2018-03-20

Example: if you run the script and the values of &1 is "VAR1='X'", it will define the SQL*Plus variable VAR1 to 'X'

For this to work properly, the number variables should be pre-defined as NULL in the login.sql
*/

store set set.tmp replace
set term off
set feed off ver off echo off head off timing off
spool parser.tmp REPLACE
select
	case when q'!&1!'  is not null then q'!define &1!'  end || chr(10) ||
	case when q'!&2!'  is not null then q'!define &2!'  end || chr(10) ||
	case when q'!&3!'  is not null then q'!define &3!'  end || chr(10) ||
	case when q'!&4!'  is not null then q'!define &4!'  end || chr(10) ||
	case when q'!&5!'  is not null then q'!define &5!'  end || chr(10) ||
	case when q'!&6!'  is not null then q'!define &6!'  end || chr(10) ||
	case when q'!&7!'  is not null then q'!define &7!'  end || chr(10) ||
	case when q'!&8!'  is not null then q'!define &8!'  end || chr(10) ||
	case when q'!&9!'  is not null then q'!define &9!'  end || chr(10) ||
	case when q'!&10!' is not null then q'!define &10!' end || chr(10) ||
	case when q'!&11!' is not null then q'!define &11!' end || chr(10) ||
	case when q'!&12!' is not null then q'!define &12!' end || chr(10) ||
	case when q'!&13!' is not null then q'!define &13!' end || chr(10) ||
	case when q'!&14!' is not null then q'!define &14!' end || chr(10) ||
	case when q'!&15!' is not null then q'!define &15!' end || chr(10) ||
	case when q'!&16!' is not null then q'!define &16!'end statement
from dual;
spool off
@parser.tmp
@set.tmp
set term on

Como você pode ver, o script simplesmente cria alguns comandos “DEFINE” e depois os executa.

Importante: pra que isso funcione corretamente, todas as variáveis SQL*Plus de &1 até &16 precisam ser definidas inicialmente. Você pode fazer isso incluindo no seu login.sql as seguintes linhas (como em SQL*Plus tips. #1):

-- Reset parameters
set termout off
COLUMN 1 NEW_VALUE 1 noprint
COLUMN 2 NEW_VALUE 2 noprint
COLUMN 3 NEW_VALUE 3 noprint
COLUMN 4 NEW_VALUE 4 noprint
COLUMN 5 NEW_VALUE 5 noprint
COLUMN 6 NEW_VALUE 6 noprint
COLUMN 7 NEW_VALUE 7 noprint
COLUMN 8 NEW_VALUE 8 noprint
COLUMN 9 NEW_VALUE 9 noprint
COLUMN 10 NEW_VALUE 10 noprint
COLUMN 11 NEW_VALUE 11 noprint
COLUMN 12 NEW_VALUE 12 noprint
COLUMN 13 NEW_VALUE 13 noprint
COLUMN 14 NEW_VALUE 14 noprint
COLUMN 15 NEW_VALUE 15 noprint
COLUMN 16 NEW_VALUE 16 noprint
SELECT '' "1", '' "5", '' "9",  '' "13"
      ,'' "2", '' "6", '' "10", '' "14"
      ,'' "3", '' "7", '' "11", '' "15"
      ,'' "4", '' "8", '' "12", '' "16"
FROM dual;
set termout on;

Aqui está o script tables que mostra como usar o parser:

$cat tables.sql
-- Define the default parameters values
define OWNER=""
define TABLE=""

-- Call the parser to set the parameters received
@@parser

select owner, table_name, tablespace_name, temporary
from dba_tables
where owner like nvl(upper('&OWNER'),owner)
and table_name like nvl(upper('&TABLE'),table_name)
/

Nos próximos dias eu vou apresentar alguns scripts interessantes que eu criei e usam o parser.

É isso! Espero que gostem 😉 .

Understanding CPU Metrics in a Linux environment

Português/English

The purpose of this blog post is just compile some information I have been around in the last weeks, while producing reports on some Oracle database environments for one of our clients.

When it comes to CPU metrics, there is always some confusion about the real understanding of each metric.

I am not an Operating System specialist, so my intention here is not produce a long article explaining all the details. I will just put together some notes that would be useful to me in the future, and hopefully for someone else that reads it. I will also point some links that explains better the metrics. They helped me a lot to understand them.

CPU Metrics

The CPU has 3 main states:

sy (Sys): time the CPUs spent running system (kernel-related, operating system) processes.
us (User): time the CPUs spent running user (i.e., not kernel-related) processes.
id (Idle): time the CPUs were doing nothing.

But they have other sub-types, to compose the seven metrics shown in the “top” command:

ni (Nice): time the CPUs spent running user processes that have been niced.
wa (I/O Wait): time the CPUs were doing nothing, while there were runnable processes waiting for I/O operations to complete.
hi (Hardware Interrupts): time the CPUs spent dealing with hardware interrupts.
si (Software Interrupts): time the CPUs spent dealing with software interrupts.
st (Steal Time): (only for Virtual Machines) time the virtual CPUs spent waiting to be served with a real CPU from the Hypervisor.

The sum of those seven metrics is 100%.

Load Average

Load Average is the average number of runnable processes during a period of time. This includes ALL runnable processes:

  • Those that are really running.
  • Those that are waiting for a CPU to become available.
  • Those that are waiting for an I/O operation to complete.

The number usually agreed as a maximum good number for Load Average is 70% of the CPU Threads. So, if you have a server with, say, 4 CPU cores with 2 threads each, the Load Average should not be higher than 5.6 (70% of 8).

My Summary and Conclusions

  • Beware of average values. A “good” average could hide huge and even long spikes.
  • If the Load Average is between 70% and 100% of the CPU Threads, then probably you have processes wasting part of their time waiting for CPU.
  • If the Load Average is over 100% of the CPU Threads, then you probably is having huge CPU bottlenecks.
  • If you have high Load Average and low CPU Idle, then you have a CPU bottleneck in that moment/period.
  • If you have high Load Average, high I/O Wait Time but also high Idle Time, then the processes in the run queue are really waiting for I/O, not CPU.
  • If you don’t have I/O Wait Time, it does not mean you are not having I/O bottlenecks.
    • The I/O Wait Time only show up in the CPU metrics when there are some CPU Idle.
    • If the CPUs are all loaded, then I/O Wait is ZERO even if there are processes waiting for I/O. In this case, these processes would show up in the Load Average.

Links

Understanding Linux CPU stats

Understanding Linux CPU Load – when should you be worried?

Linux performance metric myths (Load Average, % IO Wait)

That’s it. If you find something wrong here, or want to add something, let me know.

I will update this post whenever I find more important information related.

See you!


Entendendo as métricas de CPU em um ambiente Linux

O propósito deste post é apenas compilar algumas informações que eu estive pesquisando nas últimas semanas, enquanto estava produzindo um relatório sobre alguns ambientes Oracle de um de nossos clientes.

Quando se trata de métricas de CPU, sempre há alguma confusão sobre o real significado de cada métrica.

Eu não sou um especialista em Sistema Operacional, e portanto minha intenção não é escrever um artigo longo explicando todos os detalhes. Eu vou apenas juntar algumas notas que me podem ser úteis no futuro, e espero que também sejam úteis para alguém mais que leia isto. Colocarei alguns links que explicam melhor as métricas. Eles me ajudaram muito a entendê-las.

Métricas de CPU

A CPU tem 3 estados principais:

sy (Sys): tempo que as CPUs gastaram executando processos do sistema (relacionados com o kernel do Sistema Operacional).
us (User): tempo que as CPUs gastaram executando processos de usuários (isto é, não relacionados diretamente com o funcionamento do Sistema Operacional).
id (Idle): tempo em que as CPUs não estavam executando nada.

Mas eles têm outros sub-tipos, que compõe as sete métricas exibidas no comando “top”:

ni (Nice): tempo que as CPUs gastaram executando processos que receberam prioridade (nice).
wa (I/O Wait): tempo em que as CPUs não estavam executando nada, mas havia processos esperando por operações de I/O terminarem.
hi (Hardware Interrupts): tempo que as CPUs gastaram lidando com interrupções de hardware.
si (Software Interrupts): tempo que as CPUs gastaram lidando com interrupções de software.
st (Steal Time): (somente para Máquinas Virtuais) tempo que as CPUs virtuais perderam esperando que o Hypervisor disponibilizasse uma CPU real.

A soma destas sete métricas é 100%.

Load Average

O Load Average é a quantidade média de processos prontos para executar em um período de tempo. Isto inclui TODOS os processos considerados prontos para executar:

  • Aqueles realmente executando.
  • Aqueles que estavam esperando por uma CPU ficar disponível.
  • Aqueles que estavam esperando por operações de I/O.

O número normalmente tido como consenso como máximo aceitável para o Load Average é o equivalente a 70% do número de CPU Threads. Então, se você tem um servidor com, digamos, 4 CPU cores e 2 threads em cada CPU, o Load Average não deveria ser maior do que 5.6 (70% de 8).

Meu Resumo e Conclusões

  • Cuidado com valores médios. Uma média “boa” pode esconder picos enormes e longos.
  • Se o Load Average está entre 70% e 100% das CPU Threads, então provavelmente você já tem processos perdendo parte do seu tempo esperando por CPU.
  • Se o Load Average está acima de 100% das the CPU Threads, então você provavelmente tem um sério gargalo de CPU.
  • Se você tem um Load Average alto e baixo CPU Idle, então você tem um gargalo de CPU naquele período/momento.
  • Se você tem um Load Average alto, I/O Wait Time alto mas também Idle Time alto, então os processos na fila estão na verdade esperando por I/O, não por CPU.
  • Se você não tem I/O Wait Time, isso não significa que você não tenha gargalos de I/O.
    • O I/O Wait Time só aparece nas métricas de CPU quando há tempo de CPU Idle.
    • Se as CPUs estão todas sobrecarregadas, então o I/O Wait será ZERO mesmo que você tenha processos esperando por I/O. Neste caso, estes processos devem aparecer no Load Average.

Links

Understanding Linux CPU stats

Understanding Linux CPU Load – when should you be worried?

Linux performance metric myths (Load Average, % IO Wait)

É isso. Se você encontrar algo errado aqui, ou quiser adicionar alguma coisa, me avise.

Eu vou atualizar este post sempre que encontrar alguma informação importante relacionada.

Até a próxima!