How to enable trace in Oracle

1. Enable trace at instance level

Put the following line in init.ora. It will enable trace for all sessions and the background
processes

sql_trace = TRUE

to disable trace:

sql_trace = FALSE

- or -

to enable tracing without restarting database run the following command in sqlplus

SQLPLUS> ALTER SYSTEM SET trace_enabled = TRUE;

to stop trace run:

SQLPLUS> ALTER SYSTEM SET trace_enabled = FALSE;


2. Enable trace at session level

to start trace:

ALTER SESSION SET sql_trace = TRUE;

to stop trace:

ALTER SESSION SET sql_trace = FALSE;

- or -

EXECUTE dbms_session.set_sql_trace (TRUE);
EXECUTE dbms_session.set_sql_trace (FALSE);

- or -

EXECUTE dbms_support.start_trace;
EXECUTE dbms_support.stop_trace;


3. Enable trace in another session

Find out SID and SERIAL# from v$session. For example:

SELECT * FROM v$session WHERE osuser = OSUSER;

to start trace:

EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#);

to stop trace:

EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#);

- or -

EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE);
EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE);



SQL> select sid, serial# from v$session where username = 'USER';

SQL> alter system kill session 'SID,SERIAL#';

Backup & Recovery with RMAN

Agenda

• Types of failures and backups in Oracle
• RMAN Architecture
• Manual vs. RMAN backups
• On-tape backups with RMAN
• RMAN Configuration
• RMAN backup strategies
• RMAN backups Syntax
• Exemplary recovery scanarios



Types of failures

• Instance Failure
– Usually connected with an Oracle process failure
• Media Failure
– Disk failure, storage array controller failure etc.
• Human error
– In most cases accidentally deleted/updated data
– Database user or DBA
• Disaster
– Fire, flood, earthquake, plane crash etc.

Backup options in Oracle

• Physical backups
– Cold (off-line) backups
• Full database only
• Require downtime
– Hot (on-line) backups
• Different types of backups: full, incr. (cumulative, differential), archivelogs
• cumulative backup: which backs up all blocks changed after the most recent incremental backup at level 0
• differential backup: which backs up all blocks changed after the most recent incremental backup at level 1 or 0
• Different scopes: full database, tablespace(s) or datafile(s)
• Do not require database downtime
• Can be used to recover full database, single/multiple tablespace(s)/datafile(s) or a corrupted block
• Database can be recovered to any point in time within assumed backup retention period

RMAN architecture
Types of RMAN hot backups

• Copy or backupset
• Full database backup
• Incremental backups (in 10g 2 levels available: 0 and 1)
– Cumulative, differential
• Archivelog backups
• Tablespace(s), datafile(s) backups


Manual vs. RMAN backups

• RMAN advantages:
– Supports incremental backup strategies
– RMAN on-line backups are not so heavy for the system as manual on-line backups
– RMAN can detect corrupted blocks
– RMAN automatically track database structure changes
– Provides easy, automated backup, restore and recovery operations
– Keeps invenotory of taken backups
– Can seamlessly work with third party media managers

• Disadvantage: something new to learn
– RMAN concepts and command syntax sometimes are not intuitive

On-tape backups with RMAN
• RMAN allows to take on-disk backups out of the box
– Flash recovery area, if configured, further simplifies such backups
– On disk backups are interesting but usually not sufficient for a disaster recovery
• On-disk backups can be manually sent to tapes
– Recovery can be very troublesome
• RMAN can seamlessly work with third party Media Managers
– Media Manager Library (MML) is required
– Different configuration tasks for different MMLs
• Many vendors of Media Management software provide MMLs
• Most popular are:
– Tivoli Storage Manager
– Veritas NetBackup

RMAN Configuration


• RMAN can be preconfigured
– Configuration is stored in the control file and in the recovery catalog (if used)
– Can facilitate backup automation
• Most useful settings:

RMAN Configuration

• Example:

configure RETENTION POLICY TO RECOVERY WINDOW OF 31 DAYS;
configure DEFAULT DEVICE TYPE TO 'sbt';
configure DEVICE TYPE 'sbt' PARALLELISM 2;
configure CHANNEL DEVICE TYPE ‘sbt’ parms='ENV=
(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin/tdpo.opt)';
configure DEVICE TYPE DISK PARALLELISM 2;
configure MAXSETSIZE TO 200 G;
configure archivelog backup copies for device type 'sbt' to 1;
configure controlfile autobackup on;

• The SHOW ALL command lists all RMAN configuration settings
• To clear a given settings append CLEAR at the end of the CONFIGURE command

RMAN backup strategies


• RMAN allows many types of backups
• It possible to build own backup strategy that suits given database best
• Both Oracle-recommended strategies implemented for all production systems
• Incremental backup strategy:
– Backups go to tapes
– Weekly or biweekly level 0 backups (depending on the DB size)
– A level 1 cumulative backup inbetween
– Daily incremental level 1 differential backups
– Archivelog backup every 30 minutes
• Incrementally updated DB copy strategy:
– daily incremental differential backups applied with 2 days of delay
– Copies, incremental backups and archived redo logs stored in the Flash Recovery Area

Backup operations

RMAN> RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK/SBT
FORMAT '/u01/backups/%U';
BACKUP DATABASE PLUS ARCHIVELOG;
}
Backup operations
Backup operations


RMAN> backup as copy database;
RMAN> backup copy of database;
RMAN> backup database;


RMAN> configure device type disk backup type to compressed backupset;

RMAN> backup as compressed backupset full database plus archivelog;

Complete database recovery

• Needed when:
– All datafiles are lost or the SYSTEM tablespace datafiles are lost
– At least one member of each redo log group survived
• Requires:
– Control file recovery (if it’s lost)
– Datafile restore from a backup
– Database recovery using incremental backups and/or archived redo logs and online redo logs
Database point-in-time recovery

• Needed when:
– all datafiles are lost
– All copies of the current control file are lost
– Or all online redo log group members are lost
• If done after a disaster it has to be preceded by:
– Hardware configuration
– OS and Oracle software installation
– Re-creation or restore from non-RMAN backup of listener.ora, tnsnames.ora and other important configuration files
– ASM instance and diskgroup configuration (if needed)
– MML installation and configuration
• Requires
– Spfile restore
– Controlfile restore
– Datafiles restore and recovery

Recovery
Tablespace point-in-time recovery
• Needed
– Mainly to address a human error
• Oracle makes efforts to automate it
– Can be done with few clicks in OEM
• Requires
– Point in time recovery of the whole database Export/import of selected tablespaces schemas or objects


Block media recovery

• Needed when:
– Database reports either single or multi block corruption
• Can be done with an open database
• Database corruptions can be discovered with RMAN backup validate database command
• Corrupted blocks can be found in V$DATABASE_BLOCK_CORRUPTION

Single/multiple datafile/tablespace recovery

• Needed when
– Single/multiple tablespaces or datafiles have been lost
– SYSTEM tablespace is intact
– Controlfiles and online redo logs are intact
• Requires
– To put offline datafiles and tablespaces being recovered
– The database can be open and available to users

v$views
• V$BACKUP_ARCHIVELOG_DETAILS
• V$BACKUP_ARCHIVELOG_SUMMARY
• V$BACKUP_CONTROLFILE_DETAILS
• V$BACKUP_CONTROLFILE_SUMMARY
• V$BACKUP_COPY_DETAILS
• V$BACKUP_COPY_SUMMARY
• V$BACKUP_PIECE_DETAILS
• V$RMAN_BACKUP_JOB_DETAILS
• V$RMAN_BACKUP_TYPE
• V$FLASH_RECOVERY_AREA_USAGE

v$views
DBA_HIST_INSTANCE_RECOVERY
DBA_RECOVERABLE_SCRIPT
DBA_RECOVERABLE_SCRIPT_BLOCKS
DBA_RECOVERABLE_SCRIPT_ERRORS
DBA_RECOVERABLE_SCRIPT_PARAMS
GV_$INSTANCE_RECOVERY
GV_$RECOVER_FILE
GV_$RECOVERY_FILE_STATUS
GV_$RECOVERY_LOG
GV_$RECOVERY_PROGRESS
GV_$RECOVERY_STATUS
V_$FLASH_RECOVERY_AREA_USAGE
V_$INSTANCE_RECOVERY
V_$RECOVER_FILE
V_$RECOVERY_FILE_DEST
V_$RECOVERY_FILE_STATUS
V_$RECOVERY_LOG
V_$RECOVERY_PROGRESS
V_$RECOVERY_STATUS

Complete Restore / Recover Syntax

ORACLE_SID=TARGBD
Export ORACLE_SID

rman target rman/rman

RMAN> set dbid=1138590899

Executing command: SET DBID

RMAN> startup nomount

Oracle instance started


RMAN> set controlfile autobackup format for device type disk to 'c:\backup\%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup;



RMAN> mount database;

database mounted

RMAN> restore database;

Starting restore at …..


RMAN> recover database;

Starting recover at ….

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.


Block Media Recovery
Oracle introduced the ability to perform block level recovery in 9i. The following syntax can be used to perform block level recovery:

RMAN> blockrecover datafile 1 block 2;

OR

run {
allocate channel c1 device type disk|sbt;
blockrecover datafile 1 block 2;
}

Note these restrictions of block media recovery:

• You can only perform block media recovery with RMAN. No SQL*Plus recovery interface is available.
• You can only perform complete recovery of individual blocks. In other words, you cannot stop recovery before all redo has been applied to the block.
• You can only recover blocks marked media corrupt. The V$DATABASE_BLOCK_CORRUPTION view indicates which blocks in a file were marked corrupt since the most recent BACKUP or BACKUP ... VALIDATE command was run against the file.
• You must have a full RMAN backup. Incremental backups are not allowed. Note that Block media recovery is able to restore blocks from parent incarnation backups and recover the corrupted blocks through a RESETLOGS.
• Blocks that are marked media corrupt are not accessible to users until recovery is complete. Any attempt to use a block undergoing media recovery results in an error message indicating that the block is media corrupt.