Advanced RMAN

Now is time to play with some more advanced RMAN scenarios.

Information about fully-completed backups

Have a look at the following command:

SQL> ALTER SESSION SET nls_date_format= 'DD-MON-YYYY:HH24:MI:SS';
SQL> SELECT /*+ RULE */ session_key, session_recid,
start_time, end_time, output_bytes, elapsed_seconds, optimized
 2   FROM   v$rman_backup_job_details
 3   WHERE  start_time >= sysdate-180
 4   AND    status='COMPLETED'
 5   AND    input_type='DB FULL';

Summary of the active session history

A summary of the active session history might help (make sure you are licensed to use it by acquiring the Oracle Diagnostic Pack!). Have a look at the following command:

SQL> SELECT sid, serial#, program 
 2   FROM   v$session 
 3   WHERE  lower(program) like '%rman%';


SQL> SET LINES 132 
SQL> COLUMN session_id FORMAT 999 HEADING "SESS|ID"
SQL> COLUMN session_serial# FORMAT 9999 HEADING "SESS|SER|#"
SQL> COLUMN event FORMAT a40
SQL> COLUMN total_waits FORMAT 9,999,999,999 HEADING "TOTAL|TIME|WAITED|MICRO"
SQL> SELECT session_id, session_serial#, Event, sum(time_waited) total_waits
 2   FROM   v$active_session_history
 3   WHERE  session_id||session_serial# in (403, 476, 4831)
 4   AND    sample_time > sysdate -1
 5   AND    program like '%rman%'
 6   AND    session_state='WAITING' And time_waited > 0
 7   GROUP BY session_id, session_serial#, Event
 8   ORDER BY session_id, session_serial#, total_waits desc;

How long does it take?

Have a look at the following command:

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Open a new terminal and execute:

$ sqlplus / as sysdba

SQL> ALTER SESSION SET CONTAINER=pdborcl;

SQL> SELECT sid, serial#, program 
 2   FROM   v$session 
 3   WHERE  lower(program) like '%rman%';

SQL> SELECT sid, serial#, opname, time_remaining
 2   FROM   v$session_longops
 3   WHERE  sid||serial# in (<XXX>, <XXX>, <XXX>)
 4   AND    time_remaining > 0;

Note

Remember to replace the values <XXX> with the sid and serial# from the first query. For example, sid=4 and serial#=20 enter 420.

V$BACKUP_ASYNC_IO

Have a look at the following command:

SQL> SELECT sid, serial#, program 
 2   FROM   v$session 
 3   WHERE  lower(program) like '%rman%';

SQL> COLUMN filename FORMAT a60
SQL> SELECT sid, serial, effective_bytes_per_second, filename 
 2   FROM   V$BACKUP_ASYNC_IO
 3   WHERE  sid||serial in (<XXX>, <XXX>, <XXX>);

SQL> SELECT LONG_WAITS/IO_COUNT, FILENAME
 2   FROM   V$BACKUP_ASYNC_IO 
 3   WHERE LONG_WAITS/IO_COUNT > 0 
 4   AND   sid||serial in (<XXX>, <XXX>, <XXX>)
 5   ORDER BY LONG_WAITS/IO_COUNT DESC; 

Note

Remember to replace the values <XXX> with the sid and serial# from the first query. For example, sid=4 and serial#=20 enter 420.

Tablespace Point-in-time Recovery (TSPITR)

Firstly, we will check if the tablespace TEST is fully self-contained.

Note

Remember that you should have the EXECUTE_CATALOG_ROLE granted to be able to execute the TRANSPORT_SET_CHECK procedure.

SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('test', TRUE);

SQL> SELECT * FROM transport_set_violations;

Look for object that will be lost during the recovery using the following command:

SQL> SELECT OWNER, NAME, TABLESPACE_NAME,
 2   TO_CHAR(CREATION_TIME, 'YYYY-MM-DD:HH24:MI:SS')
 3   FROM  TS_PITR_OBJECTS_TO_BE_DROPPED
 4   WHERE TABLESPACE_NAME IN ('TEST')
 5   AND CREATION_TIME >
 6   TO_DATE('07-OCT-08:22:35:30','YY-MON-DD:HH24:MI:SS')
 7   ORDER BY TABLESPACE_NAME, CREATION_TIME;

Execute the TSPITR.

RMAN>RECOVER TABLESPACE pdborcl:test UNTIL SCN <XXXX> AUXILIARY DESTINATION '/tmp';

Note

Replace <XXXX> for any previous SCN of the database.

Reporting from a catalog

Here is a script that displays databases that are registered in the catalog and the last date they were backed up (full backup or level 0):

SQL> SELECT a.db_key, a.dbid, a.name db_name,
b.backup_type, b.incremental_level,
b.completion_time, max(b.completion_time) 
over (partition by a.name, a.dbid) max_completion_time
 2 FROM   catalog_bck.rc_database a, catalog_bck.rc_backup_set b
 3 WHERE  b.status = 'A'
 4 AND    b.backup_type = 'D'
 5 AND    b.db_key = a.db_key;

Duplex backup

Have a look at the following command:

RMAN> CONFIGURE DATAFILE BACKUPCOPIES FOR DEVICE TYPE DISK TO 2;
RMAN> CONFIGURE ARCHIVELOG BACKUp COPIES FOR DEVICE TYPE DISK TO 2;
RMAN> BACKUP DATAFILE 1 FORMAT '/DB/u02/backups/bck_orcl_%U','/Data/backup/bck_orcl_%U' PLUS ARCHIVELOG;

Check if the database is recoverable

Have a look at the following command:

RMAN> RESTORE DATABASE PREVIEW;

Recover advisor

Firstly, let's create a tablespace and a user for this scenario:

SQL> CREATE TABLESPACE test3_tbs DATAFILE '/data/pdborcl /test3_01.dbf' SIZE 100m;
SQL> CREATE USER test3 IDENTIFIED BY test3 DEFAULT TABLESPACE test3_tbs QUOTA UNLIMITED ON test3_tbs;
SQL> GRANT CONNECT, RESOURCE to test3;
SQL> CREATE TABLE test3.EMPLOYEE  
( EMP_ID   NUMBER(10) NOT NULL,
  EMP_NAME VARCHAR2(30),
  EMP_SSN  VARCHAR2(9),
  EMP_DOB  DATE
);
 
SQL> INSERT INTO test.employee VALUES (101,'Francisco Munoz',123456789,'30-JUN-73');

SQL> INSERT INTO test.employee VALUES (102,'Gonzalo Munoz',234567890,'02-OCT-96');

SQL> INSERT INTO test.employee VALUES (103,'Evelyn Aghemio',659812831,'02-OCT-79');
$ Cd /data/pdborcl
$ echo > test3_01_dbf.dbf
$ ls -lrt

RMAN> VALIDATE DATABASE;
RMAN> LIST FAILURE;
RMAN> ADVISE FAILURE;
RMAN> REPAIR FAILURE PREVIEW;
RMAN> REPAIR FAILURE;