Thursday, February 26, 2009
RMAN Backups - Configure/Backup/Report Commands
Persistant Configuration
CONFIGURE command enables you to configure various backup, restore and recover parameters
Retention Policy
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
Establishes the number of backups files and control files you want to retain
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
Establishes the number of days the backups are to be retained. The redundancy policy and the recovery window policy are mutually exclusive
Automatic Channel Configuration and Allocation
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'd:\backups\bkup_%d_%s_%p';
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEFAULT DEVICE TYPE CLEAR;
Automatic Backup of Control Files
BACKUP command INCLUDING CONTROL FILE;
Backup Optimization
CONFIGURE BACKUP OPTIMIZATION ON;
Will cause Oracle to skip backups of files that already have identical backups on the device being backed up to.
if it is ON, RMAN skips the files for which a backup already exists on the same device with the same header status. The same header implies that no modifications where made to the data in those files since the last backup. This feature is useful for archivelog backups; in this case the archivelog files that are already backed up can be skipped
Automated Backups of the Control File and the Database Parameter File
configure controlfile autobackup on;
configure controlfile autobackup off;
When autobackup of the control and parameter files is configured, the following rule apply:
> The control file and the server parameter file will be automatically backed up with each RMAN backup or copy command issued that is not included in a run block.
> If a run block is used, the control files and parameter files will be backed up at the end of the run block if the last command is not backup or copy.
RMAN> configure controlfile autobackup format for device type disk to 'd:\backup\contf\robt_%F'
When this option is used, the Oracle RDBMS will automatically back up the control file during database structure changes that impact the control file.
Number of channels
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'd:\backup\robt\backup_%U';
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'e:\backup\robt\backup_%U';
If you start an automated backup, two channels will be allocated to perform the backup in parallel.
Excluding Tablespaces
CONFIGURE EXCLUDE FOR TABLESPACE hr_tbsp;
CONFIGURE EXCLUDE FOR TABLESPACE hr_tbsp clear;
Size of backup set piece
CONFIGURE CHANNEL 1 DEVICE TYPE DISK MAXPIECESIZE 100m;
We are limiting channel 1 to create each individual backup piece at a maximum size of 100mb. Note that this command does not limit the overall size of the backup.
MaxSize for entire backup set
CONFIGURE MAXSETSIZE TO 7500K;
CONFIGURE MAXSETSIZE CLEAR;
Duplexed copies
configure datafile backup copies for device type disk to 2;
Snapshot control file
configure snapshot control file name to 'd:\oracle\backup\scontrolf';
This file is a point-in-time copy of the database control file that is taken during RMAN backup operations. This ensures that the backup is consistent to a given point in time.Thus, if you add a tablespace or datafile to a database after the backup has started(assuming an online backup, of course) that tablespace or datafile will not be included in the backup Backup Commands Options
Backup of Archive logs
BACKUP DATABASE PLUS ARCHIVELOG;
Restartable Backups
BACKUP DATABASE NOT BACKED UP SINCE TIME 'sysdate - 7';
Tags - Each backup in Oracle can be assigned a tag
RMAN> backup database tag = 'test backup'
Limiting the size of a Backup Set
RMAN> backup database maxsetsize=50m tag='test backup'
Using this parameter we have limited the maximum size of the backup set to 50MB. This is handy if your tape has a size limit or if your disks can only handle datafiles that are a certain size.
Modifying the Retention Policy for a Backup Set
backup database keep forever;
backup database keep until time ='sysdate+180';
Overriding the configure exclude command
backup database noexclude keep forever tag='test backup';
If you want to ensure that RMAN back up these datafiles, you can include the noexclude parameter in the backup command.
Checking the Database for Errors with Backup Command
backup validate database;
Scan the database for physical and logical errors without actually doing a
backup.
Skipping offline, Inaccessible or Read-Only Datafiles
backup database skip readonly;
backup database skip offline;
backup database skip inaccessible;
backup database skip readonly skip offline skip inaccessible;
Forcing a Backup of Read only Datafiles
backup database force;
Backing Up Datafiles Based on Their Last Backup Time
Backing up only newly added datafiles
backup database not backed up;
Backing up files not backed up in a specific time period
backup database not backed up sice time='sysdate -2';
Checking for logical corruption during a backup
backup check logical database;
backup validate check logical database;
If you wish the backup to continue through a given number of errors, you need to set the maxcorrupt parameter first. This requires using a run block, as shown in this example
run{
set maxcorrupt for datafile 1,2,3,4,5,6,7 to 10;
backup validate check logical database;
}
Making copies of Backups on our RMAN copier
backup database copies=2;
Capturing the Elusive Control File
backup database device type disk include current controlfile;
Set Command Options
To define settings that only apply to the current RMAN session.
> To Display RMAN commands inthe message log, use the set echo command
> To specify a database's database identified (DBID), use the set DBID command
Certain set commands can only be used within the confines of a run block.
- The set newname command is useful if you are performing TSPITR or database duplication The set newname command allows you to specify new database datafile names This is useful if you are moving the database to a new system and the file system names are different. You need to use the switch command in combination with the set newname command.
- Using the set maxcorrupt for datafile command enables you to define a maximum number of data block corruptions allowed before the RMAN operation will fail
- Using the set archivelog destination command allows you to modify the archive_log_Dest_1 destination for archived redo logs
- Using the set with the until clause of the set command enables you to define a specific poing in time, an SCN, or a log sequence number, to be used during database point-in-time recovery
- using the set backup copies command enables you to define how many copies of the backup files should be created for each backup piece in the backup set
- Using the set command id setting enables you to associate a given server session to a given channel
- Using the set controlfile autobackup format for device type command enables you to modify the default format for control file autobackups.
run
{
set maxcorrupt for datafile 3 to 10;
set backup copies = 3;
backup database;
}
If you wish to do a backup that creates two copies of each backup piece that is created, an you want to allow for ten corruptions in datafile 3.
Report Commands
REPORT OBSOLETE RECOVERY WINDOWS 7 DAYS;
REPORT NEED BACKUP RECOVERY WINDOW 7 DAYS;
LIST Commands
LIST BACKUP
LIST BACKUP BY FILE;
Other Commands
SHOW ALL;
SHOW RETENTION POLICY;
CROSSCHECK
command to check if backup sets or file copies exist
Recovery Scenarios using RMAN
Point in Time Recovery
run {
allocate channel d1 type disk;
set until time
"to_date('2007-03-31:20:00:00','yyyy-mm-dd:hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;
}
Recovery from missing or corrupted datafile(s):
This scenario deals with a situation where a datafile has gone missing, or is corrupted beyond repair. For concreteness, we look at a case where a datafile is missing. Below is a transcript of an SQL Plus session that attempts to open a database with a missing
datafile (typed commands in bold, lines in italics are my comments, all other lines are feedback from SQL Plus):
--open SQL Plus from the command line without logging on to database
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 25 14:52:41 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
--Connect to the idle Oracle process as a privileged user and start up instance
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
SQL>
The error message tells us that file# 4 is missing. Note that although the startup command has failed, the database is in the mount state. Thus, the database control file, which is also the RMAN repository can be accessed by the instance and by RMAN. We now recover the missing file using RMAN. The transcript of the recovery session is reproduced below (bold lines are typed commands, comments in italics, the rest is feedback from RMAN):
--logon to RMAN
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--restore missing datafile
RMAN> restore datafile 4;
Starting restore at 26/JAN/05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=15 devtype=DISK
channel ORA_DISK_1: starting datafile backupset
restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1 piece handle=D:\BACKUPQGB0UEC_1_1.BAK tag=TAG20050124T152708 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05
--recover restored datafile - RMAN applies all logs automatically
RMAN> recover datafile 4;
Starting recover at 26/JAN/05
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 4 is already on disk as file E:\ORACLE_ARCHIVE\ORCL_4.ARC
archive log thread 1 sequence 5 is already on disk as file C:\ORACLE_ARCHIVE\ORCL_5.ARC
archive log thread 1 sequence 6 is already on disk as file E:\ORACLE_ARCHIVE\ORCL_6.ARC
archive log thread 1 sequence 7 is already on disk as file E:\ORACLE_ARCHIVE\ORCL_7.ARC
archive log filename=E:\ORACLE_ARCHIVE\ORCL_4.ARC thread=1 sequence=4
archive log filename=C:\ORACLE_ARCHIVE\ORCL_5.ARC thread=1 sequence=5
media recovery complete
Finished recover at 26/JAN/05
--open database for general use
RMAN> alter database open;
database opened
RMAN>
In the above scenario, the database is already in the mount state before the RMAN session is initiated. If the database is not mounted, you should issue a "startup mount" command before attempting to restore the missing datafile. The database must be mounted before any datafile recovery can be done.
If the database is already open when datafile corruption is detected, you can recover the datafile without shutting down the database. The only additional step is to take the relevant tablespace offline before starting recovery. In this case you
would perform recovery at the tablespace level. The commands are:
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--offline affected tablespace
RMAN> sql 'alter tablespace USERS offline immediate';
using target database controlfile instead of recovery catalog
sql statement: alter tablespace USERS offline immediate
--recover offlined tablespace
RMAN> recover tablespace USERS;
Starting recover at 26/JAN/05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=12 devtype=DISK
starting media recovery
media recovery complete
Finished recover at 26/JAN/05
--online recovered tablespace
RMAN> sql 'alter tablespace USERS online';
sql statement: alter tablespace USERS online
RMAN>
Recovery from block corruption:
It is possible to recover corrupted blocks using RMAN backups. This is a somewhat exotic scenario, but it can be useful in certain circumstances, as illustrated by the following example. Here's the situation: a user connected to SQLPlus gets a data block corruption error when she queries a table. Here's a part of the session transcript:
SQL> connect testuser/testpassword
Connected.
SQL> select count(*) from test_table;
select count(*) from test_table
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4,
block # 2015)
ORA-01110: data file 4:
'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
Since we know the file and block number, we can perform block level recovery using RMAN. This is best illustrated by example:
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--restore AND recover specific block
RMAN> blockrecover datafile 4 block 2015;
Starting blockrecover at 26/JAN/05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=19 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=20 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=E:\BACKUPQGB0UEC_1_1.BAK
tag=TAG20050124T152708 params=NULL
channel ORA_DISK_1: block restore complete
starting media recovery
media recovery complete
Finished blockrecover at 26/JAN/05
RMAN>
Now our user should be able to query the table from her SQLPlus session. Here's her session transcript after block recovery.
SQL> select count(*) from test_table;
COUNT(*)
----------
217001
SQL>
A couple of important points regarding block recovery:
1. Block recovery can only be done using RMAN.
2. The entire database can be open while performing block recovery.
3. Check all database files for corruption. This is important - there could be other corrupted blocks.
Verification of database files can be done using RMAN or the dbverify utility. To verify using RMAN simply do a complete database backup with default settings.
If RMAN detects block corruption, it will exit with an error message pointing out the guilty file/block Recovery from missing or corrupted redo log group
Case 1: A multiplexed copy of the missing log is available.
If a redo log is missing, it should be restored from a multiplexed copy, if possible. This is the only way to recover without any losses. Here's an example, where I attempt to startup from SQLPlus when a redo log is missing:
SQL> startup
ORACLE instance started.
Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1:
'D:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG'
SQL>
To fix this we simply copy REDO03A.LOG from its multiplexed location on E: to the above location on E:. After copying the file, we issue an "alter database open" from the above SQLPlus session:
SQL> alter database open;
Database altered.
SQL>
That's it - the database is open for use.
Case 2: All members of a log group lost.
In this case an incomplete recovery is the best we can do. We will lose all transactions from the missing log and all subsequent logs. We illustrate using the same example as above. The error message indicates that members of log group 3 are missing. We don't have a copy of this file, so we know that an incomplete recovery is required. The first step is to determine how much can be recovered. In order to do this, we query the V$LOG view (when in the mount state) to find the system change number (SCN) that we can recover to (Reminder: the SCN is a monotonically increasing number that is incremented whenever a commit is
issued):
--The database should be in the mount state for v$log access
SQL> select first_change# from v$log where group#=3 ;
FIRST_CHANGE#
-------------
370255
SQL>
The FIRST_CHANGE# is the first SCN stamped in the missing log. This implies that the last SCN stamped in the previous log is 370254 (FIRST_CHANGE#-1). This is the highest SCN that we can recover to. In order to do the recovery we must first restore ALL datafiles to this SCN, followed by recovery (also up to this SCN). This is an incomplete recovery, so we must open the database resetlogs after we're done. Here's a transcript of the recovery session (typed commands in bold, comments in italics, all other lines are RMAN feedback):
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--Restore ENTIRE database to determined SCN
RMAN> restore database until scn 370254;
Starting restore at 26/JAN/05
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\SYSTEM01.DBF
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
channel ORA_DISK_2: starting datafile backupset
restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS01.DBF
channel ORA_DISK_2: restored backup piece 1
piece handle=E:\BACKUPGB14IB_1_1.BAK
tag=TAG20050124T171139 params=NUL
channel ORA_DISK_2: restore complete
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUPGB14IB_1_1.BAK
tag=TAG20050124T171139 params=NUL
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05
--Recover database
RMAN> recover database until scn 370254;
Starting recover at 26/JAN/05
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 9 is already on disk as file E:\ORACLE_ARCHIVE\ORCL_9.ARC
archive log thread 1 sequence 10 is already on disk as file E:\ORACLE_ARCHIVE\ORCL_10.ARC
archive log thread 1 sequence 11 is already on disk as file E:\ORACLE_ARCHIVE\ORCL_11.ARC
archive log thread 1 sequence 12 is already on disk as file E:\ORACLE_ARCHIVE\ORCL_12.ARC
archive log filename=E:\ORACLE_ARCHIVE\ORCL_9.ARC
thread=1 sequence=9
archive log filename=E:\ORACLE_ARCHIVE\ORCL_10.ARC
thread=1 sequence=10
media recovery complete
Finished recover at 26/JAN/05
--open database with RESETLOGS (see comments below)
RMAN> alter database open resetlogs;
database opened
RMAN>
The following points should be noted:
1. The entire database must be restored to the SCN that has been determined by querying v$log.
2. All changes beyond that SCN are lost. This method of recovery should be used only if you are sure that you cannot do better. Be sure to multiplex your redo logs, and (space permitting) your archived logs!
3. The database must be opened with RESETLOGS, as a required log has not been applied. This resets the log sequence to zero, thereby rendering all prior backups worthless. Therefore, the first step after opening a database RESETLOGS is to take a fresh backup. Note that the RESETLOGS option must be used for any incomplete recovery.
Recovery from missing or corrupted control file:
Case 1: A multiplexed copy of the control file is available.
On startup Oracle must read the control file in order to find out where the datafiles and online logs are located. Oracle expects to find control files at locations specified in the CONTROL_FILE initialization parameter. The instance will fail to mount the
database if any one of the control files are missing or corrupt. A brief error message will be displayed, with further details recorded in the alert log. The exact error message will vary depending on what has gone wrong. Here's an example:
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
SQL>
On checking the alert log, as suggested, we find the following:
ORA-00202: controlfile:
'e:\oracle_dup_dest\controlfile\ORCL\control02.ctl'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 5447783)
The above corruption was introduced by manually editing the control file when the database was closed. The solution is simple, provided you have at least one uncorrupted control file - replace the corrupted control file with a copy using operating system
commands. Remember to rename the copied file. The database should now start up without any problems Recover database by means of a backup from the old incarnation
1. Obtain the primary key of the prior incarnation with a LIST command:
# obtain primary key of old incarnation
LIST INCARNATION OF DATABASE trgt;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset
SCN Reset Time
------- ------- ------- ------ -------
---------- ----
1 2 TRGT 1224038686 PARENT 1 02-OCT-02
1 582 TRGT 1224038686 CURRENT 59727 10-OCT-02
2. Make sure the database is started but not mounted:
SHUTDOWN FORCE NOMOUNT
3. Reset the incarnation to the primary key that you just obtained:
# reset database to old incarnation
RESET DATABASE TO INCARNATION 2;
4. Recover the database, performing the following actions in the RUN command:
• Set the end time for recovery to the time just before the loss of the data.
• If automatic channels are not configured, then manually allocate one or more channels.
• Restore the control file and mount it.
• Restore and recover the database.
For example, run the following commands:
RUN
{
# set time to just before data was lost.
SET UNTIL TIME 'Oct 8 2002 07:55:00';
RESTORE CONTROLFILE; # FROM AUTOBACKUP not needed in catalog mode
ALTER DATABASE MOUNT; # mount database after restoring control file
RESTORE DATABASE;
RECOVER DATABASE;
}
5. If recovery is successful, then reset the online redo logs:
# this command automatically resets the database so that this incarnation is the new incarnation
ALTER DATABASE OPEN RESETLOGS;
Recovery
Restore from Cold Backup
A cold backup is a backup performed while the database is off-line and unavailable to its users. Procedure for restore from cold backup
1. Make sure source and destination databases are on the save version and patch (Very Important)
2. create control file script from trace file
SQLPLUS> ALTER DATABASE backup controlfile to trace;
SQLPLUS>SHOW PARAMETER UDUMP
3. Shutdown the Source Database
4. Copy all datafiles and trace file to the destination database (do not copy redo log files and control_files)
5. startup nomount pfile=init.ora
6. Create the script from the trace file. Here is the sample
CREATE CONTROLFILE SET DATABASE "DEV" RESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 2726
LOGFILE
GROUP 1 (
'/u02/oracle/oradata/DEV/redot1g1m2.log',
'/u01/oracle/oradata/DEV/redot1g1m1.log'
) SIZE 10M,
GROUP 2 (
'/u02/oracle/oradata/DEV/redot1g2m2.log',
'/u01/oracle/oradata/DEV/redot1g2m1.log'
) SIZE 10M,
GROUP 3 (
'/u01/oracle/oradata/DEV/redot1g3m1.log',
'/u02/oracle/oradata/DEV/redot1g3m2.log'
) SIZE 10M,
GROUP 4 (
'/u01/oracle/oradata/DEV/redot1g4m1.log',
'/u02/oracle/oradata/DEV/redot1g4m2.log'
) SIZE 10M
DATAFILE
'/u02/oracle/oradata/DEV/system01.dbf',
'/u02/oracle/oradata/DEV/undotbs01.dbf',
'/u02/oracle/oradata/DEV/sysaux01.dbf',
'/u02/oracle/oradata/DEV/users01.dbf',
'/u02/oracle/oradata/DEV/example01.dbf',
'/u02/oracle/oradata/DEV/data01.dbf',
'/u02/oracle/oradata/DEV/data02.dbf',
'/u02/oracle/oradata/DEV/data03.dbf',
'/u02/oracle/oradata/DEV/data04.dbf',
'/u02/oracle/oradata/DEV/index01.dbf',
'/u02/oracle/oradata/DEV/index02.dbf',
'/u02/oracle/oradata/DEV/index03.dbf',
'/u02/oracle/oradata/DEV/index04.dbf',
'/u02/oracle/oradata/DEV/lob01.dbf',
'/u02/oracle/oradata/DEV/bea01.dbf',
'/u02/oracle/oradata/DEV/beaidx01.dbf',
'/u02/oracle/oradata/DEV/bealob01.dbf'
CHARACTER SET WE8ISO8859P1 ;
7. Open the database with resetlogs
SQL> @control_file.sql;
Control file created.
8. Open the database with resetlogs
ALTER DATASASE OPEN RESETLOGS;
9. Create Temporary tablespace
ALTER TABLESPACE TEMP ADD TEMPFILE
‘/opt/oracle/schedwin/temp01.dbf’ size 150M;
Restore from Hot Backup
A hot backup is a backup performed while the database is on-line and available for read/write. Except for oracle exports, one can only do on-line backups when running in ARCHIVELOG mode.
CASE 1: Restore database using RMAN Full backup.
#1 Copy the following files to the target server
-The database backup piece
Controlfile backup piece
Parameter file. i.e pfile.
#2 Edit the parameter with new environment specific values
#3 Start the database in nomount mode, using RMAN.
$ rman target /
Recovery Manager: Release 9.2.0.6.0 - 64bit Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database (not started)
RMAN> startup nomount
Oracle instance started
Total System Global Area 773295264 bytes
Fixed Size 732320 bytes
Variable Size 134217728 bytes
Database Buffers 637534208 bytes
Redo Buffers 811008 bytes
#4 Restore control file from backup piece.
RMAN> restore controlfile from
'schedcd_633997804_109_1';
#5 Mount the database
RMAN>alter database mount;
#6 Catalog the backup piece
RMAN>catalog backuppiece ‘schedcd_633997804_109_1';
#7 Get to know the last sequence available in the archivelog backup using the following command.
RMAN> list backup of archivelog all;
#8 Rename the redologsfiles, so that they can be created in a new location when opened in resetlogs.
SQL>alter database rename file
‘/opt/oracle/oradata/schedcd/redo/redoa1.log’ to
‘/opt/oracle/oradata/schedwin/redo/redoa1.log’;
…
#9 Restore the datafiles to new locations and recover
RMAN> run
{ set until sequence
set newname for datafile 1 to
/opt/u01/oradata/schedwin/dbf/system01.dbf;
set newname for datafile 2 to
/opt/u01/oradata/schedwin/dbf/perfstat01.dbf;
set newname for datafile 3 to
/opt/u01/oradata/schedwin/dbf/cwmlite01.dbf;
set newname for datafile 4 to
/opt/u01/oradata/schedwin/dbf/drsys01.dbf;
set newname for datafile 5 to
/opt/u01/oradata/schedwin/dbf/indx01.dbf;
set newname for datafile 6 to
/opt/u01/oradata/schedwin/dbf/odm01.dbf;
set newname for datafile 7 to
/opt/u01/oradata/schedwin/dbf/tools01.dbf;
set newname for datafile 8 to
/opt/u01/oradata/schedwin/dbf/users01.dbf;
set newname for datafile 9 to
/opt/u01/oradata/schedwin/dbf/xdb01.dbf;
set newname for datafile 10 to
/opt/u02/oradata/schedwin/dbf/users02.dbf;
set newname for datafile 11 to
/opt/u03/oradata/schedwin/dbf/users03.dbf;
set newname for datafile 12 to
/opt/u01/oradata/schedwin/dbf/schedwin_indx01.dbf;
set newname for datafile 14 to
/opt/u02/oradata/schedwin/dbf/schedwin_indx02.dbf;
set newname for datafile 16 to
/opt/u01/oradata/schedwin/dbf/undotblspce01.dbf;
restore database validate; # This is optional
restore database;
switch datafile all;
recover database;
alter database open resetlogs;
}
Most Commonly used DBA Scripts:2
To check the database growth:
COLUMN month FORMAT a30 HEADING 'Month'
COLUMN growth FORMAT 999,999,999,999,999 HEADING 'Growth (bytes)'
SELECT
TO_CHAR(creation_time, 'RRRR-MM') month
, SUM(bytes) growth
FROM sys.v_$datafile
GROUP BY TO_CHAR(creation_time, 'RRRR-MM')
ORDER BY TO_CHAR(creation_time, 'RRRR-MM');
To count total number of sessions active:
select username,count(1) num_sessions from v$session group by Username order by 2 desc;
To check the total number of active sessions:
COLUMN username FORMAT A12
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
COLUMN OSUSER FORMAT A10
COLUMN MODULE FORMAT A40
SELECT s.sid,
s.serial#,
p.spid,
NVL(s.username, '(oracle)') AS username,
s.osuser,
s.status,
s.module,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time,
s.machine,
s.terminal
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
and s.status='ACTIVE'
--and s.username='FKHALID'
--and s.type<>'BACKGROUND'
ORDER BY s.username, s.osuser;
To get the sql text of all queries running under a user:
set linesize 1000
column osuser format a15
column status format a8
column user format a15
SELECT a.SID, a.serial#, a.username, a.osuser,a.status,
TO_CHAR (a.logon_time, 'dd-mm-yy hh24:mi:ss') Logon_Time, substr(b.sql_text,1,180) SQL_STATEMENT
FROM v$session a, v$sql b
WHERE a.sql_address = b.address AND a.username = UPPER ('&user')
and a.status='ACTIVE'
/
To get the Operating system ID of a user:
SELECT s.sid||','||s.serial# SIDs, s.osuser, s.status, s.schemaname
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND p.spid = '&osid'
/
To get the metadata of a object:
SELECT DBMS_METADATA.get_ddl
(UPPER('&OBJECT_TYPE'),UPPER('&OBJECT_NAME'),UPPER('&OWNER')) from dual;
To get the list of all tables which should be placed in memory for logical reads:
drop table keep_tables purge;
create table keep_tables as
select
o.owner owner,
o.object_name object_name,
o.subobject_name subobject_name,
o.object_type object_type,
count(distinct file# || block#) num_blocks
from
dba_objects o,
v$bh bh
where
o.data_object_id = bh.objd
and
o.owner not in ('SYS','SYSTEM')
and
bh.status != 'free'
group by
o.owner,
o.object_name,
o.subobject_name,
o.object_type
order by
count(distinct file# || block#) desc
;
select
'alter '||s.segment_type||' '||t1.owner||'.'||s.segment_name||' storage (buffer_pool keep);'
from
keep_tables t1,
dba_segments s
where
s.segment_name = t1.object_name
and
s.owner = t1.owner
and
s.segment_type = t1.object_type
and
nvl(s.partition_name,'-') = nvl(t1.subobject_name,'-')
and
buffer_pool <> 'KEEP'
and
object_type in ('TABLE','INDEX')
group by
s.segment_type,
t1.owner,
s.segment_name
having
(sum(num_blocks)/greatest(sum(blocks), .001))*100 > 80;
--Finally getting the list of objects in keep_pool
col OBJECT_NAME format a30
SELECT o.object_name, COUNT(1) number_of_blocks
FROM DBA_OBJECTS o, V$BH bh, dba_segments s
WHERE o.data_object_id = bh.objd
AND o.owner=s.owner and o.object_name=s.segment_name
AND s.buffer_pool='KEEP'
GROUP BY o.object_name
ORDER BY count(1) desc;
To get the list of all tables currently locked:
SELECT session_id SID, owner, SUBSTR (object_name, 1, 32) "OName",SUBSTR (oracle_username, 1, 12) "User",
object_type Object, process,
DECODE (locked_mode,
2, 'RS',
3, 'RE',
4, 'TS',
5, 'TE',
6, 'Exc',
'No Lock'
) "Mode"
FROM v$locked_object v, dba_objects d
WHERE v.object_id = d.object_id
ORDER BY oracle_username, object_name;
To get the list of all locks held on a object:
select * from V$ACCESS where upper(object)=upper('&OBJECT_NAME');
To get the list of rows locked:
select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# ) as rowid_locked
from v$session s, dba_objects do
where sid in
(select s2.sid
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2)
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;
To kill user sessions:
SELECT 'alter system kill session ' || '''' || SID || ',' || serial# || ''''
|| ';'
FROM v$session
WHERE username = upper('&user');
To check a table size:
col segment_name format a30
select SEGMENT_name,partition_name,bytes/1024/1024/1024 "GB" from dba_segments
where owner=UPPER('&OWNER')
and segment_type like 'TABLE%' and segment_name =UPPER('&SEGMENT_NAME')
order by 2
;
To check the temporary tablespace usage:
column sid_serial format A12 heading "Sid-Serial"
column tablespace format a15 heading 'TBS Name'
column spid format 9,999 heading 'Unix ID'
column segblk# format 999,999,999 heading 'Block ID'
column size_mb format 999,990 heading "MBytes Used"
column username format a10 heading "DB User"
column osuser format a20 heading "OS User"
column program format a35
select s.sid ||','|| s.serial# sid_serial,c.SPID spid, s.status,
s.username, s.osuser, s.program, su.tablespace, round(((su.blocks * p.value)/(1024*1024))) size_mb
from v$session s, v$sort_usage su, v$process c, v$parameter p where p.name='db_block_size'
and s.saddr = su.session_addr and s.paddr = c.addr
order by 8 desc;
To check the undo tablespace usage:
col NAME format a15
col TABLESPACE_NAME format a10
col Programme format a20
col machine format a30
SELECT
a.usn,
e.sid,
e.serial#,
e.username,
e.program,
e.machine,
e.osuser,
a.name,
b.status,
c.tablespace_name,
d.addr
FROM
v$rollname a,
v$rollstat b,
dba_rollback_segs c,
v$transaction d,
v$session e
WHERE
a.usn=b.usn AND
a.name=c.segment_name AND
a.usn=d.xidusn AND
d.addr=e.taddr;
To check the database cache hit percentage:
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/
To get the SGA statistics:
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
spsize number;
blkbuf number;
logbuf number;
BEGIN
select value into redlog from v$sysstat
where name = 'redo log space requests';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
select value into spsize from v$parameter where name = 'shared_pool_size';
select value into blkbuf from v$parameter where name = 'db_block_buffers';
select value into logbuf from v$parameter where name = 'log_buffer';
dbms_output.put_line(' SGA CACHE STATISTICS');
dbms_output.put_line(' *************************************');
dbms_output.put_line(' SQL Cache Hit rate = '||libcac);
dbms_output.put_line(' Dict Cache Hit rate = '||rowcac);
dbms_output.put_line(' Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line(' Redo Log space requests = '||redlog);
dbms_output.put_line('');
dbms_output.put_line(' INIT.ORA SETTING');
dbms_output.put_line(' *************************************');
dbms_output.put_line(' Shared Pool Size = '||spsize||' Bytes');
dbms_output.put_line(' DB Block Buffer = '||blkbuf||' Blocks');
dbms_output.put_line(' Log Buffer = '||logbuf||' Bytes');
dbms_output.put_line('');
dbms_output.put_line('');
dbms_output.put_line(' *************************************');
if
libcac < 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
END IF;
if
redlog > 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/
To check the waits stats:
column object_name format a20
column owner format a20
column object_type format a20
column sample_time format a27
column sql_text format a50
set ver off
prompt Histogram of Buffer Busy Wait
select WAIT_TIME_MILLI, WAIT_COUNT, round((WAIT_COUNT/tot) * 100, 2) pct
from v$event_histogram,
(select sum(WAIT_COUNT) tot
from v$event_histogram
where event = 'buffer busy waits'
)
where event = 'buffer busy waits'
/
prompt top 5 buffer busy waits
select sql_text, seconds_wait, waits, p1 file#, p2 block#
from v$sql ,
( select * from
(select p1 , p2 , count(*) waits, sum(time_waited)/1000000 seconds_wait, sql_id
from V$ACTIVE_SESSION_HISTORY
where event= 'buffer busy waits'
group by p1, p2, sql_id
order by 4 desc)
where rownum < 6
) a
where a.sql_id = v$sql.sql_id(+)
/
column file# new_value fv
column block# new_value bv
Prompt top waited on block information
select OBJECT_NAME, OBJECT_TYPE, OWNER,
seconds_wait, waits, p1 file#, p2 block#
from dba_objects ,
( select * from
(select p1 , p2 , count(*) waits, sum(time_waited)/1000000 seconds_wait,
CURRENT_OBJ#
from V$ACTIVE_SESSION_HISTORY
where event= 'buffer busy waits'
group by p1, p2, CURRENT_OBJ#
order by 4 desc)
where rownum < 2
) a
where current_obj# = object_id
/
prompt Times of Break Down Of top waited on Block
select sample_time, TIME_WAITED/1000000 seconds_wait, sql_text
from V$ACTIVE_SESSION_HISTORY outer left join v$sql using(sql_id)
where event= 'buffer busy waits'
and CURRENT_FILE# = nvl(to_number('&fv'), -1)
and CURRENT_BLOCK# = nvl(to_number('&bv'), -1)
order by 1
/
set ver on
To analyze a table:
begin
dbms_stats.gather_table_stats(OWNNAME=>'&SCHEMA_NAME',tabname=>'&TABLE_NAME',cascade=>TRUE,degree=>16,method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO,estimate_percent=>&ESTIMATE_PERCENTAGE,granularity=>'ALL');
end;
/
--or
begin
dbms_stats.gather_table_stats(OWNNAME=>'&SCHEMA_NAME',tabname=>'&TABLE_NAME',cascade=>TRUE,degree=>16,method_opt=>'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',estimate_percent=>&ESTIMATE_PERCENTAGE,granularity=>'ALL');
end;
/
To check if any datapump job is being executed:
SELECT
owner_name
,job_name
,operation
,job_mode
,state
,degree
,attached_sessions
FROM dba_datapump_jobs;
To get the constraints list:
#set linesize 1000
#set verify off
#set pagesize 40
#break on owner on table_name on constraint_name on r_constraint_name
column owner format a10
column r_owner format a10
column column_name format a15
column tt noprint
column position heading P format 9
column table_name format a20
column r_table_name format a15
column constraint_name format a15
column r_constraint_name format a15
select
a.tt,
a.owner,
b.table_name,
a.constraint_name,
b.column_name,
b.position,
a.r_constraint_name,
c.column_name,
c.position,
c.table_name r_table_name,
a.r_owner
from
(select
owner,
constraint_name,
r_constraint_name,
r_owner,1 tt
from
dba_constraints
where
owner=upper('&&owner')
and table_name=upper('&&table_name')
and constraint_type!='C'
union
select
owner,
constraint_name,
r_constraint_name,
r_owner,2
from
dba_constraints
where
(r_constraint_name,r_owner) in
(select
constraint_name,
owner
from
dba_constraints
where
owner=upper('&owner')
and table_name=upper('&table_name'))
) a,
dba_cons_columns b,
dba_cons_columns c
where
b.constraint_name=a.constraint_name
and b.owner=a.owner
and c.constraint_name=a.r_constraint_name
and c.owner=a.r_owner
and b.position=c.position
order by 1,2,3,4,5
/
set verify on
clear columns
clear breaks
undef owner
undef table_name
To get the tablespaces sizes:
set linesize 128 echo off feedback on heading on verify off pagesize 35
col megs_alloc format 999,999,999 heading "MB ALLOC"
col megs_free format 999,999,999 heading "MB FREE"
col megs_used format 999,999,999 heading "MB USED"
col pct_free format 999 heading "% FREE"
col pct_used format 999 heading "% USED"
break on report skip 1
compute sum label "TOTAL" of megs_alloc on report
compute sum of megs_free on report
compute sum of megs_used on report
select c.tablespace_name,
round(a.bytes/1048576,2) megs_alloc,
round(b.bytes/1048576,2) megs_free,
round((a.bytes-b.bytes)/1048576,2) megs_used,
round(b.bytes/a.bytes * 100,2) pct_free,
round((a.bytes-b.bytes)/a.bytes,2) * 100 pct_used
from (select tablespace_name,
sum(a.bytes) bytes,
min(a.bytes) minbytes,
max(a.bytes) maxbytes
from sys.dba_data_files a
group by tablespace_name) a,
(select a.tablespace_name,
nvl(sum(b.bytes),0) bytes
from sys.dba_data_files a,
sys.dba_free_space b
where a.tablespace_name = b.tablespace_name (+)
and a.file_id = b.file_id (+)
group by a.tablespace_name) b,
sys.dba_tablespaces c
where a.tablespace_name = b.tablespace_name(+)
and a.tablespace_name = c.tablespace_name
union
select tablespace_name, round((sum(bytes_used) + sum(bytes_free)) / 1048576,2) meg_alloc,
round(sum(bytes_free) / 1048576,2) megs_free,
round(sum(bytes_used) / 1048576,2) megs_used,
round(sum(bytes_free) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_free,
round(sum(bytes_used) / (sum(bytes_used) + sum(bytes_free)) * 100,2) pct_used
from v$temp_space_header
group by tablespace_name
order by tablespace_name;
Subscribe to:
Posts (Atom)
