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;
Wednesday, February 25, 2009
Linux command line reference
This is a Linux command line reference for common operations. Examples marked with • are valid/safe to paste without modification into a terminal, so you may want to keep a terminal window open while reading this so you can cut & paste.
Commands
•apropos whatis
Show commands pertinent to string. See also threadsafe
•man -t man | ps2pdf - > man.pdf
make a pdf of a manual page
which command
Show full path name of command
time command
See how long a command takes
•time cat
Start stopwatch. Ctrl-d to stop. See also sw
•nice info
Run a low priority command (The "info" reader in this case)
•renice 19 -p $$
Make shell (script) low priority. Use for non interactive tasks
dir navigation
•cd -
Go to previous directory
•cd
Go to $HOME directory
(cd dir && command)
Go to dir, execute command and return to current dir
•pushd .
Put current dir on stack so you can popd back to it
file searching
•alias l='ls -l --color=auto'
quick dir listing
•ls -lrt
List files by date. See also newest and find_mm_yyyy
•ls /usr/bin | pr -T9 -W$COLUMNS
Print in 9 columns to width of terminal
find -name '*.[ch]' | xargs grep -E 'expr'
Search 'expr' in this dir and below. See also findrepo
find -type f -print0 | xargs -r0 grep -F 'example'
Search all regular files for 'example' in this dir and below
find -maxdepth 1 -type f | xargs grep -F 'example'
Search all regular files for 'example' in this dir
find -maxdepth 1 -type d | while read dir; do echo $dir; echo cmd2; done
Process each item with multiple commands (in while loop)
•find -type f ! -perm -444
Find files not readable by all (useful for web site)
•find -type d ! -perm -111
Find dirs not accessible by all (useful for web site)
•locate -r 'file[^/]*\.txt'
Search cached index for names. This re is like glob *file*.txt
•look reference
Quickly search (sorted) dictionary for prefix
•grep --color reference /usr/share/dict/words
Highlight occurances of regular expression in dictionary
archives and compression
gpg -c file
Encrypt file
gpg file.gpg
Decrypt file
tar -c dir/ | bzip2 > dir.tar.bz2
Make compressed archive of dir/
bzip2 -dc dir.tar.bz2 | tar -x
Extract archive (use gzip instead of bzip2 for tar.gz files)
tar -c dir/ | gzip | gpg -c | ssh user@remote 'dd of=dir.tar.gz.gpg'
Make encrypted archive of dir/ on remote machine
find dir/ -name '*.txt' | tar -c --files-from=- | bzip2 > dir_txt.tar.bz2
Make archive of subset of dir/ and below
find dir/ -name '*.txt' | xargs cp -a --target-directory=dir_txt/ --parents
Make copy of subset of dir/ and below
( tar -c /dir/to/copy ) | ( cd /where/to/ && tar -x -p )
Copy (with permissions) copy/ dir to /where/to/ dir
( cd /dir/to/copy && tar -c . ) | ( cd /where/to/ && tar -x -p )
Copy (with permissions) contents of copy/ dir to /where/to/
( tar -c /dir/to/copy ) | ssh -C user@remote 'cd /where/to/ && tar -x -p'
Copy (with permissions) copy/ dir to remote:/where/to/ dir
dd bs=1M if=/dev/sda | gzip | ssh user@remote 'dd of=sda.gz'
Backup harddisk to remote machine
rsync (Network efficient file copier: Use the --dry-run option for testing)
rsync -P rsync://rsync.server.com/path/to/file file
Only get diffs. Do multiple times for troublesome downloads
rsync --bwlimit=1000 fromfile tofile
Locally copy with rate limit. It's like nice for I/O
rsync -az -e ssh --delete ~/public_html/ remote.com:'~/public_html'
Mirror web site (using compression and encryption)
rsync -auz -e ssh remote:/dir/ . && rsync -auz -e ssh . remote:/dir/
Synchronize current directory with remote one
ssh (Secure SHell)
ssh $USER@$HOST command
Run command on $HOST as $USER (default command=shell)
•ssh -f -Y $USER@$HOSTNAME xeyes
Run GUI command on $HOSTNAME as $USER
scp -p -r $USER@$HOST: file dir/
Copy with permissions to $USER's home directory on $HOST
ssh -g -L 8080:localhost:80 root@$HOST
Forward connections to $HOSTNAME:8080 out to $HOST:80
ssh -R 1434:imap:143 root@$HOST
Forward connections from $HOST:1434 in to imap:143
wget (multi purpose download tool)
•(cd cli && wget -nd -pHEKk http://www.pixelbeat.org/cmdline.html)
Store local browsable version of a page to the current dir
wget -c http://www.example.com/large.file
Continue downloading a partially downloaded file
wget -r -nd -np -l1 -A '*.jpg' http://www.example.com/dir/
Download a set of files to the current directory
wget ftp://remote/file[1-9].iso/
FTP supports globbing directly
•wget -q -O- http://www.pixelbeat.org/timeline.html | grep 'a href' | head
Process output directly
echo 'wget url' | at 01:00
Download url at 1AM to current dir
wget --limit-rate=20k url
Do a low priority download (limit to 20KB/s in this case)
wget -nv --spider --force-html -i bookmarks.html
Check links in a file
wget --mirror http://www.example.com/
Efficiently update a local copy of a site (handy from cron)
networking (Note ifconfig, route, mii-tool, nslookup commands are obsolete)
ethtool eth0
Show status of ethernet interface eth0
ethtool --change eth0 autoneg off speed 100 duplex full
Manually set ethernet interface speed
iwconfig eth1
Show status of wireless interface eth1
iwconfig eth1 rate 1Mb/s fixed
Manually set wireless interface speed
•iwlist scan
List wireless networks in range
•ip link show
List network interfaces
ip link set dev eth0 name wan
Rename interface eth0 to wan
ip link set dev eth0 up
Bring interface eth0 up (or down)
•ip addr show
List addresses for interfaces
ip addr add 1.2.3.4/24 brd + dev eth0
Add (or del) ip and mask (255.255.255.0)
•ip route show
List routing table
ip route add default via 1.2.3.254
Set default gateway to 1.2.3.254
•tc qdisc add dev lo root handle 1:0 netem delay 20msec
Add 20ms latency to loopback device (for testing)
•tc qdisc del dev lo root
Remove latency added above
•host pixelbeat.org
Lookup DNS ip address for name or vice versa
•hostname -i
Lookup local ip address (equivalent to host `hostname`)
•whois pixelbeat.org
Lookup whois info for hostname or ip address
•netstat -tupl
List internet services on a system
•netstat -tup
List active connections to/from system
windows networking (Note samba is the package that provides all this windows specific networking support)
•smbtree
Find windows machines. See also findsmb
nmblookup -A 1.2.3.4
Find the windows (netbios) name associated with ip address
smbclient -L windows_box
List shares on windows machine or samba server
mount -t smbfs -o fmask=666,guest //windows_box/share /mnt/share
Mount a windows share
echo 'message' | smbclient -M windows_box
Send popup to windows machine (off by default in XP sp2)
text manipulation (Note sed uses stdin and stdout. Newer versions support inplace editing with the -i option)
sed 's/string1/string2/g'
Replace string1 with string2
sed 's/\(.*\)1/\12/g'
Modify anystring1 to anystring2
sed '/ *#/d; /^ *$/d'
Remove comments and blank lines
sed ':a; /\\$/N; s/\\\n//; ta'
Concatenate lines with trailing \
sed 's/[ \t]*$//'
Remove trailing spaces from lines
sed 's/\([\\`\\"$\\\\]\)/\\\1/g'
Escape shell metacharacters active within double quotes
•seq 10 | sed "s/^/ /; s/ *\(.\{7,\}\)/\1/"
Right align numbers
sed -n '1000p;1000q'
Print 1000th line
sed -n '10,20p;20q'
Print lines 10 to 20
sed -n 's/.*<title>\(.*\)<\/title>.*/\1/ip;T;q'
Extract title from HTML web page
sed -i 42d ~/.ssh/known_hosts
Delete a particular line
sort -t. -k1,1n -k2,2n -k3,3n -k4,4n
Sort IPV4 ip addresses
•echo 'Test' | tr '[:lower:]' '[:upper:]'
Case conversion
•tr -dc '[:print:]' < /dev/urandom
Filter non printable characters
•history | wc -l
Count lines
set operations (Note you can export LANG=C for speed. Also these assume no duplicate lines within a file)
sort file1 file2 | uniq
Union of unsorted files
sort file1 file2 | uniq -d
Intersection of unsorted files
sort file1 file1 file2 | uniq -u
Difference of unsorted files
sort file1 file2 | uniq -u
Symmetric Difference of unsorted files
join -a1 -a2 file1 file2
Union of sorted files
join file1 file2
Intersection of sorted files
join -v2 file1 file2
Difference of sorted files
join -v1 -v2 file1 file2
Symmetric Difference of sorted files
math
•echo '(1 + sqrt(5))/2' | bc -l
Quick math (Calculate φ). See also bc
•echo 'pad=20; min=64; (100*10^6)/((pad+min)*8)' | bc
More complex (int) e.g. This shows max FastE packet rate
•echo 'pad=20; min=64; print (100E6)/((pad+min)*8)' | python
Python handles scientific notation
•echo 'pad=20; plot [64:1518] (100*10**6)/((pad+x)*8)' | gnuplot -persist
Plot FastE packet rate vs packet size
•echo 'obase=16; ibase=10; 64206' | bc
Base conversion (decimal to hexadecimal)
•echo $((0x2dec))
Base conversion (hex to dec) ((shell arithmetic expansion))
•units -t '100m/9.69s' 'miles/hour'
Unit conversion (metric to imperial)
•units -t '500GB' 'GiB'
Unit conversion (SI to IEC prefixes)
•units -t '1 googol'
Definition lookup
•seq 100 | (tr '\n' +; echo 0) | bc
Add a column of numbers. See also add and funcpy
calendar
•cal -3
Display a calendar
•cal 9 1752
Display a calendar for a particular month year
•date -d fri
What date is it this friday. See also day
•[ $(date -d "tomorrow" +%d) = "01" ] || exit
exit a script unless it's the last day of the month
•date --date='25 Dec' +%A
What day does xmas fall on, this year
•date --date='@2147483647'
Convert seconds since the epoch (1970-01-01 UTC) to date
•TZ=':America/Los_Angeles' date
What time is it on West coast of US (use tzselect to find TZ)
echo "mail -s 'get the train' P@draigBrady.com < /dev/null" | at 17:45
Email reminder
•echo "DISPLAY=$DISPLAY xmessage cooker" | at "NOW + 30 minutes"
Popup reminder
locales
•printf "%'d\n" 1234
Print number with thousands grouping appropriate to locale
•BLOCK_SIZE=\'1 ls -l
get ls to do thousands grouping appropriate to locale
•echo "I live in `locale territory`"
Extract info from locale database
•LANG=en_IE.utf8 locale int_prefix
Lookup locale info for specific country. See also ccodes
•locale | cut -d= -f1 | xargs locale -kc | less
List fields available in locale database
recode (Obsoletes iconv, dos2unix, unix2dos)
•recode -l | less
Show available conversions (aliases on each line)
recode windows-1252.. file_to_change.txt
Windows "ansi" to local charset (auto does CRLF conversion)
recode utf-8/CRLF.. file_to_change.txt
Windows utf8 to local charset
recode iso-8859-15..utf8 file_to_change.txt
Latin9 (western europe) to utf8
recode ../b64 < file.txt > file.b64
Base64 encode
recode /qp.. < file.txt > file.qp
Quoted printable decode
recode ..HTML < file.txt > file.html
Text to HTML
•recode -lf windows-1252 | grep euro
Lookup table of characters
•echo -n 0x80 | recode latin-9/x1..dump
Show what a code represents in latin-9 charmap
•echo -n 0x20AC | recode ucs-2/x2..latin-9/x
Show latin-9 encoding
•echo -n 0x20AC | recode ucs-2/x2..utf-8/x
Show utf-8 encoding
CDs
gzip < /dev/cdrom > cdrom.iso.gz
Save copy of data cdrom
mkisofs -V LABEL -r dir | gzip > cdrom.iso.gz
Create cdrom image from contents of dir
mount -o loop cdrom.iso /mnt/dir
Mount the cdrom image at /mnt/dir (read only)
cdrecord -v dev=/dev/cdrom blank=fast
Clear a CDRW
gzip -dc cdrom.iso.gz | cdrecord -v dev=/dev/cdrom -
Burn cdrom image (use dev=ATAPI -scanbus to confirm dev)
cdparanoia -B
Rip audio tracks from CD to wav files in current dir
cdrecord -v dev=/dev/cdrom -audio *.wav
Make audio CD from all wavs in current dir (see also cdrdao)
oggenc --tracknum='track' track.cdda.wav -o 'track.ogg'
Make ogg file from wav file
disk space (See also FSlint)
•ls -lSr
Show files by size, biggest last
•du -s * | sort -k1,1rn | head
Show top disk users in current dir. See also dutop
•df -h
Show free space on mounted filesystems
•df -i
Show free inodes on mounted filesystems
•fdisk -l
Show disks partitions sizes and types (run as root)
•rpm -q -a --qf '%10{SIZE}\t%{NAME}\n' | sort -k1,1n
List all packages by installed size (Bytes) on rpm distros
•dpkg-query -W -f='${Installed-Size;10}\t${Package}\n' | sort -k1,1n
List all packages by installed size (KBytes) on deb distros
•dd bs=1 seek=2TB if=/dev/null of=ext3.test
Create a large test file (taking no space). See also truncate
monitoring/debugging
•tail -f /var/log/messages
Monitor messages in a log file
•strace -c ls >/dev/null
Summarise/profile system calls made by command
•strace -f -e open ls >/dev/null
List system calls made by command
•ltrace -f -e getenv ls >/dev/null
List library calls made by command
•lsof -p $$
List paths that process id has open
•lsof ~
List processes that have specified path open
•tcpdump not port 22
Show network traffic except ssh. See also tcpdump_not_me
•ps -e -o pid,args --forest
List processes in a hierarchy
•ps -e -o pcpu,cpu,nice,state,cputime,args --sort pcpu | sed '/^ 0.0 /d'
List processes by % cpu usage
•ps -e -orss=,args= | sort -b -k1,1n | pr -TW$COLUMNS
List processes by mem usage. See also ps_mem.py
•ps -C firefox-bin -L -o pid,tid,pcpu,state
List all threads for a particular process
•ps -p 1,2
List info for particular process IDs
•last reboot
Show system reboot history
•free -m
Show amount of (remaining) RAM (-m displays in MB)
•watch -n.1 'cat /proc/interrupts'
Watch changeable data continuously
system information (see also sysinfo) ('#' means root access is required)
•uname -a
Show kernel version and system architecture
•head -n1 /etc/issue
Show name and version of distribution
•cat /proc/partitions
Show all partitions registered on the system
•grep MemTotal /proc/meminfo
Show RAM total seen by the system
•grep "model name" /proc/cpuinfo
Show CPU(s) info
•lspci -tv
Show PCI info
•lsusb -tv
Show USB info
•mount | column -t
List mounted filesystems on the system (and align output)
•grep -F capacity: /proc/acpi/battery/BAT0/info
Show state of cells in laptop battery
#dmidecode -q | less
Display SMBIOS/DMI information
#smartctl -A /dev/sda | grep Power_On_Hours
How long has this disk (system) been powered on in total
#hdparm -i /dev/sda
Show info about disk sda
#hdparm -tT /dev/sda
Do a read speed test on disk sda
#badblocks -s /dev/sda
Test for unreadable blocks on disk sda
interactive (see also linux keyboard shortcuts)
•readline
Line editor used by bash, python, bc, gnuplot, ...
•screen
Virtual terminals with detach capability, ...
•mc
Powerful file manager that can browse rpm, tar, ftp, ssh, ...
•gnuplot
Interactive/scriptable graphing
•links
Web browser
•xdg-open http://www.pixelbeat.org/
open a file or url with the registered desktop application
miscellaneous
•alias hd='od -Ax -tx1z -v'
Handy hexdump. (usage e.g.: • hd /proc/self/cmdline | less)
•alias realpath='readlink -f'
Canonicalize path. (usage e.g.: • realpath ~/../$USER)
•set | grep $USER
Search current environment
touch -c -t 0304050607 file
Set file timestamp (YYMMDDhhmm)
•python -m SimpleHTTPServer
Serve current directory tree at http://$HOSTNAME:8000/
Super fast Database Copying/Cloning
A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.
This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.
STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 2: Shutdown the old database
STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:
Old:
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
New:
CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS
STEP 5: Remove the “recover database” and “alter database open” syntax
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 6: Re-names of the data files names that have changed.
Save as db_create_controlfile.sql.
Old:
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
New:
DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'
STEP 7: Create the bdump, udump and cdump directories
cd $DBA/admin
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile
STEP 8: Copy-over the old init.ora file
rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile
STEP 9: Start the new database
@db_create_controlfile.sql
STEP 10: Place the new database in archivelog mode
SDLC Model – Overview of Phases:
1. Phase I - Project Initiation:
a. Request for service – project justification
i. Maintenance/enhancements/upgrade projects (minor in nature): Departments/agencies request directly to DoT representative using e-mail or memorandum.
ii. New projects or larger system enhancements: Departments/agencies request to the Information Technology Committee (ITC) for IT staff resources and/or funding.
iii. Feasibility Study: request sent to ITC for approval.
b. Feasibility study and risk analysis
c. Define Project Charter and identify project team and stakeholders
d. Define scope and business objectives to achieve
e. Assign IT personnel
f. Develop project plan
g. Assign project tracking number through DoT Request Tracking system
2. Phase II – Analysis and Requirements Definition
a. Identify existing business processes and system flow
b. Identify desired business processes and functional specifications
c. Interview users (dept/agencies involved in project)
d. Document requirements
e. Identify interfaces with other systems (existing and new), internal and external to the organization
f. Identify data conversion requirements
g. Develop business rules and process flows
h. Identify technical architecture
i. Finalize project management plan
j. Assign resources and schedule high-level tasks
k. Obtain project sponsor(s) sign-off
3. Phase III – Request for Proposal (RFP) optional
a. Develop RFP document
b. Identify potential vendors & deliver to Purchasing for processing
c. Develop evaluation checklist
d. Create vendor list and distribute responses to selection team for review
e. Rank vendor responses; select top responders
f. Schedule vendor demo’s
g. Schedule site visits
h. Contract negotiations
i. Issue notice of intent to award
j. Review and finalize vendor’s project plan and statement of work
k. Develop final system cost including future maintenance agreement cost
l. Final vendor selection with recommendation to ITC
m. Contract signed by vendor and authorized city personnel
n. Prepare purchase order (Service Contract)
4. Phase IV – Design
a. Design logical business model
b. Design application components
c. Design data dictionary
d. Design security
e. Design reports
f. Design interfaces with external systems
g. Design physical database/table structure
h. Design technical architecture
i. Obtain quotes and order hardware and software
j. Develop user prototype and business case scenarios
k. Design review/walk-through
l. Obtain user sign-off
5. Phase V – Development
a. Create development and test environments
b. Create development databases
c. Develop graphical design and report layouts
d. Define modules for coding and assign resources
e. Create database roles and user security levels
f. Create stored procedures and define database triggers
g. Developer coding & documentation
h. Quality Assurance of code
i. Unit testing
6. Phase VI – Testing
a. Develop system test plans
b. Train users involved in system testing
c. System and user testing
d. Obtain user sign-off
e. Purchase and install production technical hardware/software
f. Set up production security access
g. Obtain user sign-off
7. Phase VII – Deployment
a. Create implementation plan
b. Schedule IT Implementation Planning Session (all divisions of DoT)
c. Complete documentation (system/technical) and training manuals
d. Schedule and conduct end user training
e. Move application components to production environment
f. Deploy/move data to production environment (data conversion, import, or load data)
g. User verification of data
h. Review security
i. Turnover operational processing to DoT Operations Center/user
j. Final testing and verification of production environment
k. Obtain user sign-off
l. Go live with system
8. Phase VIII – Post Implementation Evaluation and Support
a. Conduct post implementation review
b. Evaluate overall project delivery with DoT management
c. Utilize DoT change management procedures for enhancements and incidents
d. Monitor, support, and enhance system
Documents created in SDLC:
The following are documents which are created in entire SDLC…
1. User Requirements Specification
2. System Requirement Specification
3. System Design Document
4. Test Plans
5. Test Cases
6. Unified Implementation Plan
Using Fast Incremental Backups with Block Change Tracking in Oracle 10g
In Oracle 9i we could create incremental backups with level 0 to 4. A level 0 backup is a full backup.
In Oracle 10g there are still these levels but we only use incremental level 0 and 1 backups with Oracle’s suggested backup strategy.
Starting with Oracle 10g RMAN can take incremental backups without having to read the entire datafiles in order to find out which blocks have changed since the last backup.
This new feature is called FAST INCREMENTAL BACKUP.
The new technology used for this feature is called BLOCK CHANGE TRACKING.
You enbale block change tracking by making an entry in the controlfile:
SQL> alter database enable block change tracking;
alter database enable block change tracking
*
ERROR at line 1:
ORA-19773: must specify change tracking file name
If you do not have DB_CREATE_FILE_DEST specified (used for OMF => oracle managed files)
you will encounter an error if you do not specify a name fot the change tracking file
SQL> show parameter create
NAME TYPE VALUE
———————————— ———– ——————————
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
So I try to alter this parameter:
SQL> alter system set db_create_file_dest=’/home/oracle/file_create’;
alter system set db_create_file_dest=’/home/oracle/file_create’
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01261: Parameter db_create_file_dest destination string cannot be
translated
ORA-01262: Stat failed on a file destination directory
Linux Error: 2: No such file or directory
Oracle checks if the location existes before it allows us to change this parameter!
SQL> ! mkdir -p /home/oracle/file_create
SQL> alter system set db_create_file_dest=’/home/oracle/file_create’;
System altered.
Now we can enable block change tracking
SQL> alter database enable block change tracking;
Database altered.
As an alternative you can specify a user managed filename if you do not want to use the parameter:
alter database enable block change tracking using file ‘/home/oracle/change.trc’;
Let’s take a look at the alert_log now:
alter database enable block change tracking
Thu May 18 06:39:14 2006
ORA-19773 signalled during: alter database enable block change tracking…
Thu May 18 06:39:46 2006
alter database enable block change tracking using file
‘/home/oracle/change.trc’
Thu May 18 06:39:46 2006
Block change tracking file is current.
Starting background process CTWR
CTWR started with pid=21, OS id=26871
Block change tracking service is active.
Thu May 18 06:39:47 2006
Completed: alter database enable block change tracking using file
‘/home/oracle/change.trc’
This is what oerr returns for this error:
[oracle@kr12-KL2 bdump]$ oerr ora 19773
19773, 00000, “must specify change tracking file name”
// *Cause: No file name was specified with the ALTER DATABASE ENABLE
// CHANGE TRACKING command, and the DB_CREATE_FILE_DEST parameter
// was not set.
// *Action: Either specify a file name, or set the DB_CREATE_FILE_DEST
// parameter.
Let’s take a look at the change tracking file:
[oracle@edchr3p8 oracle]$ cd file_create/
[oracle@edchr3p8 file_create]$ ll
total 4
drwxr-x— 3 oracle oinstall 4096 May 3 15:55 ORCL
[oracle@edchr3p8 file_create]$ cd ORCL/
[oracle@edchr3p8 ORCL]$ ll
total 4
drwxr-x— 2 oracle oinstall 4096 May 3 15:55 changetracking
[oracle@edchr3p8 ORCL]$ cd changetracking/
[oracle@edchr3p8 changetracking]$ ll
total 11348
-rw-r—– 1 oracle oinstall 11600384 May 3 15:55 o1_mf_25l66r5c_.chg
# this is an OMF change tracking file!
The change tracking file has an initial size of 10Mb.
How to monitor the change treacking file from inside Oracle:
SQL> desc v$block_change_tracking
Name Null? Type
—————————————– ——– —————————-
STATUS VARCHAR2(10)
FILENAME VARCHAR2(513)
BYTES NUMBER
SQL> select * from v$block_change_tracking;
STATUS FILENAME BYTES
———- ————————————————– ———-
ENABLED /home/oracle/change.trc 11599872
Now that we have enabled block change tracking a new background process called CTWR (change track writer) is started and it will automatically be started for every new instance.
[oracle@kr12-KL2 ~]$ ps -ef grep orcl
oracle 9531 1 0 May16 ? 00:00:00 ora_pmon_orcl
oracle 9533 1 0 May16 ? 00:00:02 ora_psp0_orcl
oracle 9535 1 0 May16 ? 00:00:00 ora_mman_orcl
oracle 9537 1 0 May16 ? 00:00:06 ora_dbw0_orcl
oracle 9539 1 0 May16 ? 00:00:16 ora_lgwr_orcl
oracle 9541 1 0 May16 ? 00:00:17 ora_ckpt_orcl
oracle 9543 1 0 May16 ? 00:00:25 ora_smon_orcl
oracle 9545 1 0 May16 ? 00:00:00 ora_reco_orcl
oracle 9547 1 0 May16 ? 00:00:03 ora_cjq0_orcl
oracle 9549 1 0 May16 ? 00:00:18 ora_mmon_orcl
oracle 9551 1 0 May16 ? 00:00:02 ora_mmnl_orcl
oracle 9554 1 0 May16 ? 00:00:00 ora_d000_orcl
oracle 9556 1 0 May16 ? 00:00:00 ora_s000_orcl
oracle 9560 1 0 May16 ? 00:00:00 ora_qmnc_orcl
oracle 9574 1 0 May16 ? 00:00:06 ora_q000_orcl
oracle 10746 1 0 May16 ? 00:00:00 ora_q001_orcl
oracle 26854 26850 0 06:38 ? 00:00:00 oracleorcl (DESCRIPTION=
(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 26871 1 0 06:39 ? 00:00:00 ora_ctwr_orcl
oracle 27778 1 0 06:50 ? 00:00:00 ora_j000_orcl
oracle 27780 27153 0 06:50 pts/0 00:00:00 grep orcl
CTWR will track addresses of blocks which have changed since the last backup in the change tracking file from now on.
RMAN can use this information for the next incremental backup. It will be able to find out which block must be written to the backupset by just reading the change tracking file.
RMAN will not have to read the entire datafiles into the SGA in order to find out which blocks must be backed up as it had to do before 10g.
This methode is much faster.
Now what happens if we lose the change tracking file or if it gets corrupted?
Let’s corrupt is:
[oracle@edchr3p8 changetracking]$ echo ‘hallo’ > o1_mf_25l66r5c_.chg
And now let us startup the database with a corrupt change tracking file!
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file:
‘/home/oracle/file_create/ORCL/changetracking/o1_mf_25l66r5c_.chg’
ORA-27038: created file already exists
Additional information: 1
ORA-27047: unable to read the header block of file
Linux Error: 2: No such file or directory
Additional information: 1
Wed May 3 16:03:30 2006
CHANGE TRACKING is enabled for this database, but the
change tracking file can not be found. Recreating the file.
Oracle tries to automaqtically recreate the missing file but it cannot overwrite an existing on!
Let us create some changed blocks now so Oracle cannot track them in the change tracking file!
SQL> update hr.employees set salary=salary*1.1;
107 rows updated.
SQL> commit;
Commit complete.
Now let us take a look at the ALERT_LOG:
CHANGE TRACKING ERROR 19756, disabling change tracking
Wed May 3 16:07:24 2006
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_ctwr_6628.trc:
ORA-19756: corrupt block number 18 found in change tracking file
ORA-19750: change tracking file: ‘/home/oracle/file_create/ORCL/changetracking/o1_mf_25l6qckh_.chg’
Block change tracking service stopping.
Deleted Oracle managed file /home/oracle/file_create/ORCL/
changetracking/o1_mf_25l6qckh_.chg
Oracle automatically deletes a currupted change tracking file and stops block change tracking.
Let us now take an incremental backup with RMAN:
RMAN> backup incremental level 1 for recover of copy database;
Starting backup at 03-MAY-06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-MAY-06
channel ORA_DISK_1: finished piece 1 at 03-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/
2006_05_03/ o1_mf_nnnd1_TAG20060503
T160745_25l6×2s1_.bkp tag= TAG20060503T160745
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 03-MAY-06
channel ORA_DISK_1: finished piece 1 at 03-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/
backupset/2006_05_03/o1_mf_ncsn1_TAG20060503
T160745_25l6z4mv_.bkp tag= TAG20060503T160745
comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 03-MAY-06
RMAN was able to take an incremental backup but now it had to use the old methode => read all the blocks in order to find out which ones have changed since the last backup;
Next I replace the change tracking file with an old version in order to see what happens when I try to take an incremental backup:
SYS @10gR2 SQL > ! mv /home/oracle/change.old /home/oracle/change.trc
RMAN> backup incremental level 1 for recover of copy database;
Starting backup at 19-MAY-06
using channel ORA_DISK_1
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 6 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-06
channel ORA_DISK_1: finished piece 1 at 19-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2006_05_19/
o1_mf_nnnd1_TAG20060519T123227_26v7owgo_.bk p tag=
TAG20060519T123227 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:32:30
ORA-19694: some changed blocks were not found in the change tracking file
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/
o1_mf_example_26v7oyjx_.dbf tag=TAG20060519T123227
recid=3 stamp=590848373
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/sales1.dbf
output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/
o1_mf_sales_26v7pr8j_.dbf tag=TAG20060519T123227 rec id=4 stamp=590848386
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:33:13
ORA-19694: some changed blocks were not found in the change tracking file
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/
o1_mf_users_26v7qb8t_.dbf tag=TAG20060519T123227 rec id=5 stamp=590848394
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 19-MAY-06
channel ORA_DISK_1: finished piece 1 at 19-MAY-06
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2006_05_19/
o1_mf_ncsn1_TAG20060519T123227_26v7qfb5_.bk p tag=TAG20060519T123227 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:33:13
ORA-19694: some changed blocks were not found in the change tracking file
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/19/2006 12:32:30
ORA-19694: some changed blocks were not found in the change tracking file
RMAN> list backup of tablespace sysaux;
There is no backup!!!
RMAN has found out that I tried to cheat and that it was not able to create an incremental backup for all files by using the change tracking file.
This is what oerr says about ORA-19694:
[oracle@lutzasm ~]$ oerr ora 19694
19694, 00000, “some changed blocks were not found in the change tracking file”
// *Cause: A backup or copy found that some changed blocks had not been
// recorded in the change tracking file. The details of which files
// and blocks are affected will be in an Oracle trace file.
// *Action: This indicates that there is a problem with the change tracking
// feature. Disable change tracking and re-start the backup.
connect lutz hartmann as sysdba
Subscribe to:
Posts (Atom)
