Now is time to play with some more advanced RMAN scenarios.
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';
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;
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;
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;
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';
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;
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;
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;