Automtic and Manually sized Components of SGA

DB_CACHE_SIZE,
SHARED_POOL_SIZE,
LARGE_POOL_SIZE and
JAVA_POOL_SIZE are automatic sized components.

The parameter SGA_TARGET specifies the total size of all SGA components.
If SGA_TARGET is set to a value greater than zero then above components are automatically sized.

On the other hand,

LOG_BUFFER,
BUFFER_POOL_KEEP,
BUFFER_POOL_RECYCLE,
DB_nK_CACHE_SIZE other than DB_CACHE_SIZE,
STREAMS_POOL_SIZE ,
Fixed SGA and
other internal components are manually sized components.

However setting the value to manually sized components automatically reduce the value from SGA_TARGET if SGA_TARGET is set to value greater than zero.

When SGA_TARGET>0 then automatic sized components of SGA are automatically allocated and this is called Automatic Shared Memory Management.

Both the automatic and manually sized components of SGA are dynamic components because they dynamically can be changed.

You can see them from V$SGA_DYNAMIC_COMPONENTS.

Simple Restore and Recovery process using RMAN on a Windows box.

C:\>set oracle_sid=SDR

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Dec 17 05:16:07 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn sys/sys as sysdba
Connected to an idle instance
SQL> startup pfile=c:\oracle\database\initSDR.ora nomount
ORACLE instance started.

Total System Global Area 202448548 bytes
Fixed Size 454308 bytes
Variable Size 159383552 bytes
Database Buffers 41943040 bytes
Redo Buffers 667648 bytes
SQL>


C:\oracle\bin>rman

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

RMAN> connect target sys/sys

connected to target database: SDR (not mounted)

RMAN>


RUN
{
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\Backup\RMAN\SDR\%F';
RESTORE CONTROLFILE FROM 'D:\Backup\RMAN\SDR\C-3180286864-20081215-00';
ALTER DATABASE MOUNT;
}


RMAN> RUN
2> {
3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\Backup\RMAN\SDR\%F';
4> RESTORE CONTROLFILE FROM 'D:\Backup\RMAN\SDR\C-3180286864-20081215-00';
5> ALTER DATABASE MOUNT;
6> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database controlfile instead of recovery catalog

Starting restore at 17-DEC-08

allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:\ORACLE\ORADATA\SDR\CONTROL01.CTL
output filename=D:\ORACLE\ORADATA\SDR\CONTROL02.CTL
output filename=C:\ORACLE\ORADATA\SDR\CONTROL03.CTL
Finished restore at 17-DEC-08

database mounted


RMAN> restore archivelog from time '14-DEC-08';

Starting restore at 17-DEC-08

using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=209
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\SDR\92K28SQ3_1_1 tag=ORACLESRVR - SDR_12140803
0002 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=210
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\SDR\95K2BH5F_1_1 tag=ORACLESRVR - SDR_12150803
0005 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 17-DEC-08

RMAN> restore database;

Starting restore at 17-DEC-08

using channel ORA_DISK_1
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\ORADATA\SDR\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\ORADATA\SDR\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\ORADATA\SDR\CWMLITE01.DBF
restoring datafile 00004 to D:\ORACLE\ORADATA\SDR\DRSYS01.DBF
restoring datafile 00005 to D:\ORACLE\ORADATA\SDR\EXAMPLE01.DBF
restoring datafile 00006 to D:\ORACLE\ORADATA\SDR\INDX01.DBF
restoring datafile 00007 to D:\ORACLE\ORADATA\SDR\ODM01.DBF
restoring datafile 00008 to D:\ORACLE\ORADATA\SDR\TOOLS01.DBF
restoring datafile 00009 to D:\ORACLE\ORADATA\SDR\USERS01.DBF
restoring datafile 00010 to D:\ORACLE\ORADATA\SDR\XDB01.DBF
restoring datafile 00011 to D:\ORACLE\ORADATA\SDR\SDRD.ORA
restoring datafile 00012 to D:\ORACLE\ORADATA\SDR\SDRI.ORA
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\RMAN\SDR\94K2BGV8_1_1 tag=ORACLESRVR - SDR_12150803
0005 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 17-DEC-08

RMAN> recover database until logseq 0 thread 1;

Starting recover at 17-DEC-08
using channel ORA_DISK_1

starting media recovery
media recovery complete

Finished recover at 17-DEC-08


RMAN> open resetlogs database;

database opened

RMAN>exit

Restore and Recover a database using a HOT Backup and lost archivelog files.

You can say it as a Dummy Recovery.

$ pwd
/uo1/oracle/app/product/9.2.0.1.0/oradata

$ mkdir edr_arch edr_data edr_temp
$ pwd
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp
$ ls
$ mkdir adump bdump cdump create email tmpfiles udump
$
$ pwd
/uo1/oracle/app/product/9.2.0.1.0/admin/edr

ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/adump
ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/bdump
ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/cdump
ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/create
ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/email
ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/tmpfiles
ln -s /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/udump

$ pwd
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data
Restore or copy all your backup files accordingly.
--------------------------------------------------------------------------------------------------------
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--------------------------------------------------------------------------------------------------------
My Controlfile Script:

CREATE CONTROLFILE SET DATABASE "edr" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 2268
LOGFILE
GROUP 1 '/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/logedr1.ora' SIZE 200M,
GROUP 2 '/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/logedr2.ora' SIZE 200M,
GROUP 3 '/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/logedr3.ora' SIZE 200M,
GROUP 4 '/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/logedr4.ora' SIZE 200M,
GROUP 5 '/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/logedr5.ora' SIZE 200M
DATAFILE
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_system.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_users.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_tools.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/RBS.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrDATA_D.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrDATA_I.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrMETA_D.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrMETA_.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrUSERS.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/CUSTOM_INDEX.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/DBA.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/GCMDB.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/PREFSTAT.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrcode.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrcode_I.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/RCX_OTHER.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/EXPORT_D.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrDATA_D2.dbf.dbf',
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edrDATA_D3.dbf'
CHARACTER SET WE8MSWIN1252
;

--------------------------------------------------------------------------------------------------------
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--------------------------------------------------------------------------------------------------------
My Pfile:

# Instance self registration parameters
instance_name = edr
service_names = edrserver
db_name = "edr"
db_domain = oracle.com
#db_files = 1000
db_block_size = 8192
db_file_multiblock_read_count = 8

compatible = "9.2.0.1.0"

# db_cache_size replaces the earlier db_block_buffers

db_cache_size = 8m
#db_cache_size = 10485760000


# Optional additional block sizes available in Oracle 9i
# You cannot set db_Nk_cache_size for N = DB_BLOCK_SIZE
# db_2k_cache_size = m
# db_4k_cache_size = m
# db_16k_cache_size = m
# db_32k_cache_size = m

shared_pool_size = 20M
#shared_pool_size = 419430400
#log_buffer = 163840
log_buffer = 20070400

# Optional settings for dynamic SGA and PGA's
# sga_max_size =
sga_max_size = 2728640000

pga_aggregate_target = 380M
#pga_aggregate_target = 419430400

# Eliminates the need for the _area_size parameters.

# To eliminate the need for Rollback segments we set the following Automatic UNDO
# parameters, and we don't set ROLLBACK_SEGMENTS
#UNDO_RETENTION = 90 # Measured in Seconds controls undo segment size.
#UNDO_MANAGEMENT = AUTO
#UNDO_TABLESPACE = UNDOTBS
#UNDO_SUPPRESS_ERRORS = TRUE # Kills error messages for rollback segment operations

control_files = ("/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/control01.ctl",
"/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/control02.ctl")
log_archive_start = false
log_archive_dest_1 = "location=/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_arch/"
log_archive_format = edr_arch%S.arc
background_dump_dest = /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/bdump
core_dump_dest = /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/cdump
user_dump_dest = /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/udump
audit_file_dest = /uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/adump

#remote_os_authent = true
#os_authent_prefix = "OPS$"

open_cursors = 3000
max_enabled_roles = 148

#optimizer_mode = choose
#nls_language = american
#nls_date_format = DD-MON-RRRR
#nls_date_language = american

timed_statistics=TRUE
#hash_join_enabled=TRUE
#optimizer_features_enable = 9.2.0.1.0
#query_rewrite_enabled=FALSE
#star_transformation_enabled=FALSE
#java_pool_size=20M
#large_pool_size=200M
#large_pool_size= 209715200
#processes=500
#fast_start_mttr_target=300
#audit_trail=TRUE
#max_rollback_segments=50
#sort_area_size=67108864
#workarea_size_policy = AUTo
#remote_login_passwordfile=exclusive
#log_archive_start=TRUE

--------------------------------------------------------------------------------------------------------
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--------------------------------------------------------------------------------------------------------

ORACLE_SID set to: edr.
ORACLE_HOME set to: /uo1/oracle/app/product/9.2.0.1.0.

startup nomount pfile=/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/pfile/initedr.ora

--------------------------------------------------------------------------------------------------------
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--------------------------------------------------------------------------------------------------------

ORACLE_SID set to: edr.
ORACLE_HOME set to: /uo1/oracle/app/product/9.2.0.1.0.

$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 23 08:20:07 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/pfile/initedr.ora
ORACLE instance started.

Total System Global Area 2738868712 bytes
Fixed Size 733672 bytes
Variable Size 2701131776 bytes
Database Buffers 16777216 bytes
Redo Buffers 20226048 bytes
SQL> @cr8ctl.sql

Control file created.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_system.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 8469459547375 generated at 12/03/2008 22:00:03 needed for
thread 1
ORA-00289: suggestion :
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_arch/edr_arch0000006653.arc
ORA-00280: change 8469459547375 for thread 1 is in sequence #6653

Here Here.. I lost my archivelog files..remember...so trying my luck with the online redolog files:

Specify log: {=suggested | filename | AUTO | CANCEL}
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/log1aedr.rdo
ORA-00310: archived log contains sequence 2370; sequence 6653 required
ORA-00334: archived log:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/log1aedr.rdo'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_system.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 8469459547375 generated at 12/03/2008 22:00:03 needed for
thread 1
ORA-00289: suggestion :
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_arch/edr_arch0000006653.arc
ORA-00280: change 8469459547375 for thread 1 is in sequence #6653


Specify log: {=suggested | filename | AUTO | CANCEL}
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/log2aedr.rdo
ORA-00310: archived log contains sequence 2371; sequence 6653 required
ORA-00334: archived log:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/log2aedr.rdo'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_system.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 8469459547375 generated at 12/03/2008 22:00:03 needed for
thread 1
ORA-00289: suggestion :
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_arch/edr_arch0000006653.arc
ORA-00280: change 8469459547375 for thread 1 is in sequence #6653


Specify log: {=suggested | filename | AUTO | CANCEL}
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/log3aedr.rdo
ORA-00310: archived log contains sequence 2369; sequence 6653 required
ORA-00334: archived log:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/log3aedr.rdo'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/edr_system.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 8469459547375 generated at 12/03/2008 22:00:03 needed for
thread 1
ORA-00289: suggestion :
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_arch/edr_arch0000006653.arc
ORA-00280: change 8469459547375 for thread 1 is in sequence #6653


Specify log: {=suggested | filename | AUTO | CANCEL}
/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_data/logedr1.ora
Log applied.
Media recovery complete.

Hurrah..done. This redolog has the change it required.

**The redologs are also backedup along with the other files.
SQL> alter database open resetlogs;

Database altered.

And the database is up.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 edr
edrserver
9.2.0.1.0 23-DEC-08 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL


SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
JServer Release 9.2.0.1.0 - Production
--------------------------------------------------------------------------------------------------------
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
--------------------------------------------------------------------------------------------------------
Then some more things to make my restore activity complete:

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
sQL> alter database open;

create spfile from pfile;

SQL> shutdown immediate;
SQL> startup
ORACLE instance started.

Total System Global Area 2738868712 bytes
Fixed Size 733672 bytes
Variable Size 2701131776 bytes
Database Buffers 16777216 bytes
Redo Buffers 20226048 bytes
Database mounted.
Database opened.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 edr
edrserver
9.2.0.1.0 23-DEC-08 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL


My controlfile script does not contain the temp file..so I have to add it:

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/uo1/oracle/app/product/9.2.0.1.0/oradata/edr_temp/tmpfiles/temp01.dbf'SIZE 4000M;

And one more thing needed is that I had to create a password file
#pwd
/uo1/oracle/app/product/9.2.0.1.0/dbs
orapwd file=orapwedr password=**********

Rename datafile

Process to rename the datafile:

alter tablespace master offline;


!mv masterPROD03.dbf childprod03.dbf
!mv masterPROD02.dbf childprod02.dbf
!mv masterPROD01.dbf childprod01.dbf


alter tablespace master rename datafile '/u01/prod_data/oradata/prod/dbfiles/masterPROD03.dbf' to
'/u01/prod_data/oradata/prod/dbfiles/childprod03.dbf';

alter tablespace master rename datafile '/u01/prod_data/oradata/prod/dbfiles/masterPROD02.dbf' to
'/u01/prod_data/oradata/prod/dbfiles/childprod02.dbf';

alter tablespace master rename datafile '/u01/prod_data/oradata/prod/dbfiles/masterPROD01.dbf' to
'/u01/prod_data/oradata/prod/dbfiles/childprod01.dbf';


select file_name from dba_data_files where tablespace_name='master';

alter tablespace master online;

Hot backup script

Use this script to prepare the required hot backup scripts

prompt alter system switch logfile;;
DECLARE
CURSOR cur_tablespace IS
SELECT tablespace_name
FROM dba_tablespaces;
CURSOR cur_datafile (tn VARCHAR) IS
SELECT file_name
FROM dba_data_files
WHERE tablespace_name = tn;
BEGIN
FOR ct IN cur_tablespace LOOP
dbms_output.put_line ('alter tablespace '||ct.tablespace_name||
' begin backup;');
FOR cd IN cur_datafile (ct.tablespace_name) LOOP
dbms_output.put_line ('host cp '||cd.file_name||' &dir');
END LOOP;
dbms_output.put_line ('alter tablespace '||ct.tablespace_name||
' end backup;');
END LOOP;
END;
/

Simple vi Editor Command

General Startup

To use vi: vi filename
To exit vi and save changes: ZZ or :wq
To exit vi without saving changes: :q!
To enter vi command mode: [esc]

Counts : A number preceding any vi command tells vi to repeat that command that many times.

Cursor Movement

h move left (backspace)
j move down
k move up
l move right (spacebar) [return] move to the beginning of the next line
$ last column on the current line
0 move cursor to the first column on the current line
^ move cursor to first nonblank column on the current line
w move to the beginning of the next word or punctuation mark
W move past the next space
b move to the beginning of the previous word or punctuation mark
B move to the beginning of the previous word, ignores punctuation
e end of next word or punctuation mark
E end of next word, ignoring punctuation
H move cursor to the top of the screen
M move cursor to the middle of the screen
L move cursor to the bottom of the screen

Screen Movement

G move to the last line in the file
xG move to line x
z+ move current line to top of screen
z move current line to the middle of screen
z- move current line to the bottom of screen
^F move forward one screen
^B move backward one line
^D move forward one half screen
^U move backward one half screen
^R redraw screen ( does not work with VT100 type terminals )
^L redraw screen ( does not work with Televideo terminals )

Inserting

r replace character under cursor with next character typed
R keep replacing character until [esc] is hit
i insert before cursor a append after cursor
A append at end of line
O open line above cursor and enter append mode

Deleting

x delete character under cursor
dd delete line under cursor
dw delete word under cursor
db delete word before cursor

Copying Code yy (yank)'copies' line which may then be put by the p(put) command. Precede with a count for multiple lines.

Put Command brings back previous deletion or yank of lines, words, or characters P bring back before cursor p bring back after cursor

Find Commands

? finds a word going backwards
/ finds a word going forwards
f finds a character on the line under the cursor going forward
F finds a character on the line under the cursor going backwards
t find a character on the current line going forward and stop one character before it
T find a character on the current line going backward and stop one character before it
; repeat last f, F, t, T

Miscellaneous Commands

. repeat last command
u undoes last command issued
U undoes all commands on one line
xp deletes first character and inserts after second (swap)
J join current line with the next line
^G display current line number
% if at one parenthesis, will jump to its mate
mx mark current line with character x
'x find line marked with character x

NOTE: Marks are internal and not written to the file.

Clone an Oracle database using an online/hot backup

This procedure will clone a database using a online copy of the source database files. Before beginning though, there are a few things that are worth noting about online/hot backups:

* When a tablespace is put into backup mode, Oracle will write entire blocks to redo rather than the usual change vectors. For this reason, do not perform a hot backup during periods of heavy database activity - it could lead to a lot of archive logs being created.
* This procedure will put all tablespaces into backup mode at the same time. If the source database is quite large and you think that it might take a long time to copy, consider copying the tablespaces one at a time, or in groups.
* While the backup is in progress, it will not be possible to take the tablespaces offline normally or shut down the instance.

Ok, lets get started...

* 1. Make a note of the current archive log change number
Because the restored files will require recovery, some archive logs will be needed. This applies even if you are not intending to put the cloned database into archive log mode. Work out which will be the first required log by running the following query on the source database. Make a note of the change number that is returned:

select max(first_change#) chng
from v$archived_log
/

* 2. Prepare the begin/end backup scripts
The following sql will produce two scripts; begin_backup.sql and end_backup.sql. When executed, these scripts will either put the tablespaces into backup mode or take them out of it:

=====================================
cr_hot_backup.sql

set lines 999 pages 999
set verify off
set feedback off
set heading off

spool begin_backup.sql

select 'alter tablespace ' || tablespace_name || ' begin backup;' tsbb
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name
/
spool off

spool end_backup.sql

select 'alter tablespace ' || tablespace_name || ' end backup;' tseb
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name
/
spool off

=======================================

#


# 3. Put the source database into backup mode
From sqlplus, run the begin backup script created in the last step:

@begin_backup

This will put all of the databases tablespaces into backup mode.

# 4. Copy the files to the new location
Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.

# 5. Take the source database out of backup mode
Once the file copy has been completed, take the source database out of backup mode. Run the end backup script created in step 2. From sqlplus:

@end_backup

# 6. Copy archive logs
It is only necessary to copy archive logs created during the time the source database was in backup mode. Begin by archiving the current redo:

alter system archive log current;
# Then, identify which archive log files are required. When run, the following query will ask for a change number. This is the number noted in step 1.

select name
from v$archived_log
where first_change# >= &change_no
order by name
/

Create an archive directory in the clone database.s file system and copy all of the identified logs into it.

# 7. Produce a pfile for the new database
This step assumes that you are using a spfile. If you are not, just copy the existing pfile.

From sqlplus:

create pfile='init.ora' from spfile;

This will create a new pfile in the $ORACLE_HOME/dbs directory.

Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.

Ensure that the archive log destination is pointing to the directory created in step 6.

# 8. Create the clone controlfile
Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:

alter database backup controlfile to trace as '/home/oracle/cr_.sql'
/

The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:

* Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).

* Remove any lines that start with --

* Remove any lines that start with a #

* Remove any blank lines in the 'CREATE CONTROLFILE' section.

* Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'

* Remove the line 'ALTER DATABASE OPEN RESETLOGS;'

* Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file. Make sure that you hang onto the command, it will be used later.

* Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.

* If the file paths are being changed, alter the file to reflect the changes.


Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:

====================================================
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DG9A" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/u03/oradata/dg9a/redo01.log' SIZE 100M,
GROUP 2 '/u03/oradata/dg9a/redo02.log' SIZE 100M,
GROUP 3 '/u03/oradata/dg9a/redo03.log' SIZE 100M
DATAFILE
'/u03/oradata/dg9a/system01.dbf',
'/u03/oradata/dg9a/undotbs01.dbf',
'/u03/oradata/dg9a/cwmlite01.dbf',
'/u03/oradata/dg9a/drsys01.dbf',
'/u03/oradata/dg9a/example01.dbf',
'/u03/oradata/dg9a/indx01.dbf',
'/u03/oradata/dg9a/odm01.dbf',
'/u03/oradata/dg9a/tools01.dbf',
'/u03/oradata/dg9a/users01.dbf',
'/u03/oradata/dg9a/xdb01.dbf',
'/u03/oradata/dg9a/andy01.dbf',
'/u03/oradata/dg9a/psstats01.dbf',
'/u03/oradata/dg9a/planner01.dbf'
CHARACTER SET WE8ISO8859P1
;
=======================================================

# 9. Add a new entry to oratab and source the environment
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:

echo $ORACLE_SID

If this doesn't output the new database sid go back and investigate.

# 10. Create the a password file
Use the following command to create a password file (add an appropriate password to the end of it):

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=

# 11. Create the new control file(s)
Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:

sqlplus "/ as sysdba"

@/home/oracle/cr_

If all goes to plan you will see the instance start and then the message 'Control file created'.

# 12. Recover and open the database
The archive logs that were identified and copied in step 6 must now be applied to the database. Issue the following command from sqlplus:

recover database using backup controlfile until cancel

When prompted to 'Specify log' enter 'auto'. Oracle will then apply all the available logs, and then error with ORA-00308. This is normal, it simply means that all available logs have been applied. Open the database with reset logs:

alter database open resetlogs;


# 13. Create temp files
Using the 'ALTER TABLESPACE TEMP...' command from step 8, create the temp files. Make sure the paths to the file(s) are correct, then run it from sqlplus.

# 14. Perform a few checks
If the last couple of steps went smoothly, the database should be open. It is advisable to perform a few checks at this point:

* Check that the database has opened with:

select status from v$instance;

The status should be 'OPEN'

* Make sure that the datafiles are all ok:

select distinct status from v$datafile;

It should return only ONLINE and SYSTEM.

* Take a quick look at the alert log too.


# 15. Set the databases global name
The new database will still have the source databases global name. Run the following to reset it:

alter database rename global_name to
/

Note. no quotes!

# 16. Create a spfile
From sqlplus:

create spfile from pfile;

# 17. Change the database ID
If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.

From sqlplus:

shutdown immediate
startup mount
exit

From unix:

nid target=/

NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:

shutdown immediate
startup mount
alter database open resetlogs
/

# 18. Configure TNS
Add entries for new database in the listener.ora and tnsnames.ora as necessary.

# 19. Finished
That's it!

Reference:
http://www.shutdownabort.com/quickguides/clone_hot.php

Tom Kytes Challenge

I Came across a wonderful Challenge Tom(Thomas Kyte) gave to people out there some time back, while going through his asktom site.

The challenge is simple(not as it seems) : Have to correctly provide ALL of the versions the following features were added to Oracle.

Thought interesting so blogged it here.

Happy reading, fun along with knowledge.....just go on

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ops$tkyte%ORA10GR2> select distinct version from features order by version;

VERSION
--------------------
10.1
10.2
11.1
2
3
4
5
6
7.0
7.1
7.2
7.3
8.0
8.1.5
8.1.6
8.1.7
9.0
9.2

18 rows selected.

So, here are the features:

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select rownum, txt
2 from (select txt from features order by rnd)
3 /

ROWNUM TXT
---------- ----------------------------------------------------------------------
1 Real Application Testing
2 Read only Replication
3 Distributed Query
4 Drop column
5 Client-Server (where the client could be elsewhere in the network)
6 Object Relational Features
7 Ability to return result sets from stored procedures (ref cursors)
8 Commit and Rollback (transactions)
9 Triggers
10 Function based indexes
11 Materialized Views
12 Rman
13 Audit SYSDBA/SYSOPER activity
14 Automatic Undo Management
15 Resumable Operations
16 Automatic Storage Management (ASM)
17 Streams
18 Bitmap Indexes
19 csscan - Character Set Scanner utility
20 Flashback Query
21 Case statement (IN SQL, instead of decode)
22 Parallel Query
23 Transparent column level encryption
24 Tablespace encryption
25 PL/SQL
26 Partitioning
27 Row Level Locking
28 Read Consistency (my favorite feature!)
29 2 Phase Commit
30 Sorted Hash Clusters
31 Conditional compilation for PL/SQL
32 Connect By Queries (select ename, level from emp connect by prior....)
33 Update anywhere Replication

33 rows selected.

=======================================
Check your answers:


select rownum, version, txt
2 from (select version, txt from features order by rnd)
3 /

or

col txt for a40
select * from features order by
to_number(substr(version,1,decode(instr(version,'.'),0,1,instr(version,'.'))))
/



ROWNUM VERSION TXT
---------- -------------------- ----------------------------------------
1 8.1.5 Materialized Views
2 10.1 Sorted Hash Clusters
3 8.0 Rman
4 8.1.6 Case statement
5 7.0 2 Phase Commit
6 11.1 Real Application Testing
7 2 Connect By Queries (select ename, level
from emp connect by prior....)

8 9.0 Automatic Undo Management
9 3 Commit and Rollback (transactions)
10 7.2 Ability to return result sets from store
d procedures (ref cursors)

11 8.1.5 Function based indexes
12 11.1 Tablespace encryption
13 8.1.6 csscan - Character Set Scanner utility
14 4 Read Consistency (my favorite feature!)
15 7.0 Triggers
16 10.1 Automatic Storage Management (ASM)
17 7.1 Update anywhere Replication
18 5 Distributed Query
19 7.1 Parallel Query
20 5 Client-Server (where the client could be
elsewhere in the network)

21 10.2 Transparent column level encryption
22 10.2 Conditional compilation for PL/SQL
23 8.1.5 Drop column
24 9.2 Streams
25 8.0 Object Relational Features
26 9.0 Flashback Query
27 9.2 Audit SYSDBA/SYSOPER activity
28 7.3 Bitmap Indexes
29 6 PL/SQL
30 7.0 Read only Replication
31 6 Row Level Locking
32 8.0 Partitioning
33 9.0 Resumable Operations
================================================================================

Want to know how Tom exactly, created this table :

here is the script,

/*

drop table features;
create table features( rnd number, version varchar2(20), txt varchar2(100) );

insert into features values ( dbms_random.random, '2', 'Connect By Queries (select ename, level
from emp connect by prior....)');
insert into features values ( dbms_random.random, '3', 'Commit and Rollback (transactions)');
insert into features values ( dbms_random.random, '4', 'Read Consistency (my favorite feature!)');
insert into features values ( dbms_random.random, '5', 'Client-Server (where the client could be
elsewhere in the network)');
insert into features values ( dbms_random.random, '5', 'Distributed Query');
insert into features values ( dbms_random.random, '6', 'Row Level Locking');
insert into features values ( dbms_random.random, '6', 'PL/SQL');
insert into features values ( dbms_random.random, '7.0', '2 Phase Commit');
insert into features values ( dbms_random.random, '7.0', 'Triggers');
insert into features values ( dbms_random.random, '7.0', 'Read only Replication');
insert into features values ( dbms_random.random, '7.1', 'Update anywhere Replication');
insert into features values ( dbms_random.random, '7.1', 'Parallel Query');
insert into features values ( dbms_random.random, '7.2', 'Ability to return result sets from stored
procedures (ref cursors)');
insert into features values ( dbms_random.random, '7.3', 'Bitmap Indexes');
insert into features values ( dbms_random.random, '8.0', 'Object Relational Features');
insert into features values ( dbms_random.random, '8.0', 'Partitioning');
insert into features values ( dbms_random.random, '8.0', 'Rman');
insert into features values ( dbms_random.random, '8.1.5', 'Materialized Views');
insert into features values ( dbms_random.random, '8.1.5', 'Function based indexes');
insert into features values ( dbms_random.random, '8.1.5', 'Drop column');
insert into features values ( dbms_random.random, '8.1.6', 'Case statement');
insert into features values ( dbms_random.random, '8.1.6', 'csscan - Character Set Scanner
utility');
insert into features values ( dbms_random.random, '9.0', 'Automatic Undo Management');
insert into features values ( dbms_random.random, '9.0', 'Resumable Operations');
insert into features values ( dbms_random.random, '9.0', 'Flashback Query');
insert into features values ( dbms_random.random, '9.2', 'Streams');
insert into features values ( dbms_random.random, '9.2', 'Audit SYSDBA/SYSOPER activity');
insert into features values ( dbms_random.random, '10.1', 'Automatic Storage Management (ASM)');
insert into features values ( dbms_random.random, '10.1', 'Sorted Hash Clusters');
insert into features values ( dbms_random.random, '10.2', 'Conditional compilation for PL/SQL');
insert into features values ( dbms_random.random, '10.2', 'Transparent column level encryption');
insert into features values ( dbms_random.random, '11.1', 'Tablespace encryption');
insert into features values ( dbms_random.random, '11.1', 'Real Application Testing');

select distinct version from features order by version;
*/

===================================================================
Tom Kytes web sites...

http://tkyte.blogspot.com/
http://asktom.oracle.com/

General process for Query Tuning

Create a plan table

SQL>@?/rdbms/admin/utlxplan.sql

Autotrace

To switch it on:

SQL>column plan_plus_exp format a100

SQL>set autotrace on explain
# Displays the execution plan only.
SQL>set autotrace traceonly explain
# dont run the query
SQL>set autotrace on
# Shows the execution plan as well as statistics of the statement.
SQL>set autotrace on statistics
# Displays the statistics only.
SQL>set autotrace traceonly
# Displays the execution plan and the statistics


To switch it off:

SQL>set autotrace off

Explain plan

SQL>explain plan for
select ...

or...
SQL>explain plan set statement_id = 'bad1' for
select...

Then to see the output...
SQL>set lines 100 pages 999
SQL>@?/rdbms/admin/utlxpls

Put something unique in the like clause

SQL>select hash_value, sql_text
from v$sqlarea
where sql_text like '%TIMINGLINKS%FOLDERREF%'
/

Grab the sql associated with a hash

SQL>select sql_text
from v$sqlarea
where hash_value = '&hash'
/


Look at a query's stats in the sql area

SQL>select executions
, cpu_time
, disk_reads
, buffer_gets
, rows_processed
, buffer_gets / executions
from v$sqlarea
where hash_value = '&hash'
/

Cool Scripts

Cool script to know the details of a Particular user.

+++++++++++++++++++++++++++++++++++++++++++++++
-- user_conf.sql

set lines 100 pages 999
set verify off
set feedback off

undefine user

accept userid prompt 'Enter username:'

select username
, default_tablespace
, temporary_tablespace
from dba_users
where username = '&userid'
/

select tablespace_name
, decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from dba_ts_quotas
where username = upper('&&userid')
/

select granted_role || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option')

"ROLE"
from dba_role_privs
where grantee = upper('&&userid')
/

select privilege || ' ' || decode(admin_option, 'NO', '', 'YES', 'with admin option')

"PRIV"
from dba_sys_privs
where grantee = upper('&&userid')
/

undefine user
set verify on
set feedback on

+++++++++++++++++++++++++++++++++++++++++++++++

Cool script to CLONE a Particular user.

+++++++++++++++++++++++++++++++++++++++++++++++
-- user_clone.sql

set lines 999 pages 999
set verify off
set feedback off
set heading off

select username
from dba_users
order by username
/

undefine user

accept userid prompt 'Enter user to clone: '
accept newuser prompt 'Enter new username: '
accept passwd prompt 'Enter new password: '

select username
, created
from dba_users
where lower(username) = lower('&newuser')
/

accept poo prompt 'Continue? (ctrl-c to exit)'

--spool /tmp/user_clone_tmp.sql
spool D:\temp\testing\user_clone_tmp.sql

select 'create user ' || '&newuser' ||
' identified by ' || '&passwd' ||
' default tablespace ' || default_tablespace ||
' temporary tablespace ' || temporary_tablespace || ';' "user"
from dba_users
where username = '&userid'
/

select 'alter user &newuser quota '||
decode(max_bytes, -1, 'unlimited'
, ceil(max_bytes / 1024 / 1024) || 'M') ||
' on ' || tablespace_name || ';'
from dba_ts_quotas
where username = '&&userid'
/

select 'grant ' ||granted_role || ' to &newuser' ||
decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
from dba_role_privs
where grantee = '&&userid'
/

select 'grant ' || privilege || ' to &newuser' ||
decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
from dba_sys_privs
where grantee = '&&userid'
/

spool off

undefine user

set verify on
set feedback on
set heading on

@D:\temp\testing\user_clone_tmp.sql

host del D:\temp\testing\user_clone_tmp.sql

--@/tmp/user_clone_tmp.sql

--!rm /tmp/user_clone_tmp.sql

NOTE: Username should be Entered in Captitals.

+++++++++++++++++++++++++++++++++++++++++++++++

Save the scripts as .sql and run at the command prompt.

How to Create and Use OMF

OMF indicates Oracle Managed Files. With the use of Oracle-managed files the administration of an Oracle Database can be simplified. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle Database. You specify operations in terms of database objects rather than filenames.

Enable the Creation of OMFs
--------------------------------
The following initialization parameters allow the database server to use the Oracle-managed files feature.

1)DB_CREATE_FILE_DEST: Defines the location of the default file system directory where the database creates datafiles or tempfiles when no file specification is given in the creation operation. It is also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

2)DB_CREATE_ONLINE_LOG_DEST_n:Defines the location of the default file system directory for redo log files and control file creation when no file specification is given in the creation operation. You can use this initialization parameter multiple times, where n specifies a multiplexed copy of the redo log or control file. You can specify up to five multiplexed copies.

3)DB_RECOVERY_FILE_DEST:Defines the location of the default file system directory where the database creates RMAN backups when no format option is used, archived logs when no other local destination is configured, and flashback logs. Also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

Both of these initialization parameters are dynamic, and can be set using the ALTER SYSTEM or ALTER SESSION statement.

An Example of using OMF :
---------------------------
1)Setting the parameter for the session:

SQL> alter session set db_create_file_dest='/oradata';
Session altered.

2)Create Tablespace using OMF:

SQL> create tablespace omf_tbs;
Tablespace created.

3)Check the data file Location:

SQL> select file_name from dba_data_files where tablespace_name='OMF_TBS';
FILE_NAME
--------------------------------------------------------------------------------
/oradata/EDR/datafile/o1_mf_omf_tbs_4067w4op_.dbf

Here EDR is the Database Name.

The dafault location for datafile is Your settings for parameter/Database Name/datafile/Unique Name.dbf

Oracle11g: Where’s My Alert Log?

Just a short blog entry about Oracle 11g.
One of the first things that caught me by surprise with 11g,
when I first started in the beta program, was that the default
location for the alert log has moved. It is still placed
under the traditional OFA structure, but not /u01/app/oracle/admin.
There is a new directory called diag that resides
in /u01/app/oracle as seen on one of my systems:


[oracle@oracle11gr1 ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl
[oracle@oracle11gr1 orcl]$ ll
total 64
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 alert
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 cdump
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 hm
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 incident
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 incpkg
drwxr-x--- 2 oracle oinstall 4096 Jan 24 01:38 ir
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 lck
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 metadata
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 stage
drwxr-x--- 2 oracle oinstall 4096 Jan 9 13:23 sweep
drwxr-x--- 2 oracle oinstall 24576 Apr 19 15:33 trace

[oracle@oracle11gr1 orcl]$ cd trace
[oracle@oracle11gr1 trace]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

[oracle@oracle11gr1 trace]$ ll alert_orcl.log
-rw-r----- 1 oracle oinstall 392 Apr 19 15:33 alert_orcl.log


In this case, my database is called orcl and the instance is orcl.

View : v$diag_info

If Alert log file is lost

One of my friend asked me whether if in any case the
alert log file is lost what happens?

It is automatically created whenever there needs to
be a new entry into the alert log.

Lets see a practical explanation

[oracle@oracle11gr1 ~]$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Apr 19 15:35:05 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn /as sysdba
Connected.
SQL>
SQL>!

[oracle@oracle11gr1]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$ ll alert_orcl.log
-rw-r----- 1 oracle oinstall 144278 Apr 19 15:26 alert_orcl.log

[oracle@oracle11gr1 trace]$ pwd
/u01/app/oracle/diag/rdbms/orcl/orcl/trace

[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$ mv alert_orcl.log alert_orcl_bak.log

[oracle@oracle11gr1 trace]$ll alert_orcl.log
ls: alert_orcl.log: No such file or directory

[oracle@oracle11gr1 trace]$exit

SQL>
SQL> CREATE TABLESPACE EDR_VECTOR_SPIND DATAFILE
'/u01/app/oracle/oradata/orcl/user.dbf' SIZE 4M AUTOEXTEND ON
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO; 2 3

Tablespace created.

SQL>
SQL>
SQL> !
[oracle@oracle11gr1 ~]$
[oracle@oracle11gr1 ~]$
[oracle@oracle11gr1 ~]$ cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace
[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$ ll alert_orcl.log
-rw-r----- 1 oracle oinstall 392 Apr 19 15:33 alert_orcl.log

[oracle@oracle11gr1 trace]$
[oracle@oracle11gr1 trace]$ more alert_orcl.log
Sat Apr 19 15:33:15 2008
CREATE TABLESPACE EDR_VECTOR_SPIND DATAFILE
'/u01/app/oracle/oradata/orcl/user.dbf' SIZE 4M AUTOEXTEND ON
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
Completed: CREATE TABLESPACE EDR_VECTOR_SPIND DATAFILE
'/u01/app/oracle/oradata/orcl/user.dbf' SIZE 4M AUTOEXTEND ON
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

[oracle@oracle11gr1 trace]$

Capture all SQL run between two points in time

There are situations where it is useful to capture the SQL
that a particular user is running in the database. Usually
you would simply enable session tracing for that user, but
there are two potential problems with that approach.

The first is that many web based applications maintain a
pool of persistent database connections which are shared
amongst multiple users. The second is that some applications
connect, run some SQL and disconnect very quickly, making it
tricky to enable session tracing at all (you could of course
use a logon trigger to enable session tracing in this case).

A quick and dirty solution to the problem is to capture all
SQL statements that are run between two points in time.

The following procedure will create two tables, each containing
a snapshot of the database at a particular point. The tables
will then be queried to produce a list of all SQL run during
that period.

If possible, you should do this on a quiet development system -
otherwise you risk getting way too much data back.

1. Take the first snapshot
Run the following sql to create the first snapshot:

create table sql_exec_before as
select executions
, hash_value
from v$sqlarea
/

2. Get the user to perform their task within the application

3. Take the second snapshot

select aft.hash_value
from sql_exec_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
/

4. Check the results
Now that you have captured the SQL it is time to query the results.

This first query will list all query hashes that have been executed:
select aft.hash_value
from sql_exec_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
/

This one will display the hash and the SQL itself:

set pages 999 lines 100
break on hash_value
select hash_value
, sql_text
from v$sqltext
where hash_value in (
select aft.hash_value
from sql_exec_before bef
, sql_exec_after aft
where aft.executions > bef.executions
and aft.hash_value = bef.hash_value (+)
)
order by
hash_value
, piece
/

5. Tidy up

Don't forget to remove the snapshot tables once you've finished:

drop table sql_exec_before
/

drop table sql_exec_after
/


Source : www.shutdownabort.com

How to find Client IP address by Quering Oracle Database ?

We can use PLSQL package UTL_INADDR.

There are two procedures'

1. GET_HOST_NAME or
2. GET_HOST_ADDRESS

for finding out local machine HOST name or IP address.

UTL_INADDR

We can use below query to findout CLIENT IP ADDRESS.

SQL> col machine for a20
SQL> col UTL_INADDR.GET_HOST_ADDRESS(MACHINE) for a40

SQL> select sid,machine,UTL_INADDR.GET_HOST_ADDRESS (machine)
from v$session
where type = 'USER' and username is not null
order by sid;

SID MACHINE UTL_INADDR.GET_HOST_ADDRESS(MACHINE)
---------- -------------------- ----------------------------------------
152 edrserver 10.21.1.249

=====================================================
If you are getting below error message then modify above code little bit.

ERROR at line 1:ORA-29257: host xxxxxxxxxxxxxxxxxx unknown

ORA-06512: at "SYS.UTL_INADDR", line 19

ORA-06512: at "SYS.UTL_INADDR", line 40

ORA-06512: at line 1

==================Modified code======================

SQL> select sid, machine,
UTL_INADDR.get_host_address (substr(machine,instr(machine,'\')+1)) ip
from v$session
where type='USER' and username is not null
order by sid;

SID MACHINE IP
---------- -------------------- ------------------------------
152 edrserver 10.21.1.249

Rman Recovery Scenarios

Rman recovery scenarios require that the database
is in archive log mode, and that backups of datafiles,
control files and archived redolog files are made using
Rman. Incremental Rman backups may be used also.

Rman can be used with the repository installed on the
archivelog, or with a recovery catalog that may be
installed in the same or other database.

Configuration and operation recommendations:

Set the parameter controlfile autobackup to ON
to have with each backup a controlfile backup also:

rman>configure controlfile autobackup on;

Set the parameter retention policy to the recovery
window you want to have, i.e redundancy 2 will keep
the last two backups available, after executing
delete obsolete commands:

rman>configure retention policy to redundancy 2;

Execute your full backups with the option
'plus archivelogs' to include your
archivelogs with every backup:

rman>backup database plus archivelog;

Perform daily maintenance routines to maintain on
your backup directory the number of backups you need only:

rman>crosscheck backup;
rman>crosscheck archivelog all;
rman>delete noprompt obsolete backup;

To work with Rman and a database based
catalog follow these steps:
1. sqlplus /
2. create tablespace repcat;
3. create user rcuser identified by rcuser default tablespace repcat
temporary tablespace temp;
4. grant connect, resource, recovery_catalog_owner to rcuser;
5. exit
6. rman catalog rcuser/rcuser # connect to rman catalog as the rcuser
7. create catalog # create the catalog
8. connect target / #
=============================================
Complete Closed Database Recovery. System tablespace is missing
---------------------------------------------
In this case complete recovery is performed, only the
system tablespace is missing, so the database can be
opened without reseting the redologs.
1. rman target /
2. startup mount;
3. restore database;
4. recover database;
5. alter database open;
=============================================
Complete Open Database Recovery. Non system tablespace
is missing,database is up
---------------------------------------------
1. rman target /
2. sql 'alter tablespace offline immediate';
3. restore datafile 3;
4. recover datafile 3;
5. sql 'alter tablespace online';
=============================================
Complete Open Database Recovery (when the database
is initially closed). Non system tablespace is missing
---------------------------------------------
A user datafile is reported missing when trying to
startup the database. The datafile can be turned offline
and the database started up. Restore and recovery are
performed using Rman. After recovery is performed
the datafile can be turned online again.
1. sqlplus /nolog
2. connect / as sysdba
3. startup mount
4. alter database datafile '' offline;
5. alter database open;
6. exit;
7. rman target /
8. restore datafile '';
9. recover datafile '';
10. sql 'alter tablespace online';
=============================================
Recovery of a Datafile that has no backups (database is up).
---------------------------------------------
If a non system datafile that was not backed up since
the last backup is missing, recovery can be performed
if all archived logs since the creation of the missing
datafile exist. Since the database is up you can check
the tablespace name and put it offline. The option offline
immediate is used to avoid that the update of the datafile header.

Pre requisites: All relevant archived logs.
1. sqlplus '/ as sysdba'
2. alter tablespace offline immediate;
3. alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf;
4. exit
5. rman target /
6. recover tablespace ;
7. sql 'alter tablespace online';
If the create datafile command needs to be executed to place
the datafile on a location different than the original use:
alter database create datafile '/user/oradata/u01/dbtst/newdata01.dbf' as
'/user/oradata/u02/dbtst/newdata01.dbf'
=============================================
Restore and Recovery of a Datafile to a different location. Database is up.
---------------------------------------------
If a non system datafile is missing and its original
location not available, restore can be made to a different
location and recovery performed.

Pre requisites: All relevant archived logs, complete cold or hot backup.
1. Use OS commands to restore the missing or corrupted datafile to the new
location, ie:
cp -p /user/backup/uman/user01.dbf /user/oradata/u02/dbtst/user01.dbf
2. alter tablespace offline immediate;
3. alter tablespace rename datafile
'/user/oradata/u01/dbtst/user01.dbf' to '/user/oradata/u02/dbtst/user01.dbf';
4. rman target /
5. recover tablespace ;
6. sql 'alter tablespace online';
=============================================
Control File Recovery
---------------------------------------------
Always multiplex your controlfiles. If you loose only one
controlfile you can replace it with the one you have in place,
and startup the Database. If both controlfiles are missing,
the database will crash.

Pre requisites: A backup of your controlfile and all relevant
archived logs. When using Rman alway set configuration parameter
autobackup of controlfile to ON. You will need the dbid to
restore the controlfile, get it from the name of the backed up
controlfile. It is the number following the 'c-' at the start of the name.
1. rman target /
2. set dbid
3. startup nomount;
4. restore controlfile from autobackup;
5. alter database mount;
6. recover database;
7. alter database open resetlogs;
8. make a new complete backup, as the database is open in a new incarnation and
previous archived log are not relevant.
=============================================
Incomplete Recovery, Until Time/Sequence/Cancel
---------------------------------------------
Incomplete recovery may be necessaire when the database
crash and needs to be recovered, and in the recovery process
you find that an archived log is missing. In this case recovery
can only be made until the sequence before the one that is missing.

Another scenario for incomplete recovery occurs when an important
object was dropped or incorrect data was committed on it.
In this case recovery needs to be performed until before the
object was dropped.

Pre requisites: A full closed or open database backup and archived
logs, the time orsequence that the 'until' recovery
needs to be performed.
1. If the database is open, shutdown it to perform full restore.
2. rman target \
3. startup mount;
4. restore database;
5. recover database until sequence 8 thread 1; # you must pass the thread, if a
single instance will always be 1.
6. alter database open resetlogs;
7. make a new complete backup, as the database is open in a new incarnation and
previous archived log are not relevant.
Alternatively you may use instead of until sequence, until time, ie: '2008-03-
16:01:01:10'.

How to change the root password of a linux server.

Use this information at your own risk,

Please note that you cannot perform this remotely,
you must be physically at the machine.

1. The first step is to reboot server.

2. Since the server I inherit has GRUB, wait until
the grub splash screen comes on and hit the letter 'e'
on your keyboard. this will stop grub.

3. hit 'e' again and this will take you to another screen

4. you should see one of the choices has "kernel"
select the one with "kernel

5. hit 'e' on your keyboard

6. type ' single' or 1 (make sure to include a space before "single/1")
and hit enter. then enter 'b' to boot.

7. your system should boot up in single mode as root

8. now that you are logged in single mode you can
change the root password by sending this command:
-->> passwd
Give the new password

9. after you have changed the root password, reboot server
and you should be able to login with the new password.

DONE

LILO ----------------------------

1. If you have LILO, its different, first reboot server and when you
see the LILO: prompt type "linux single" and hit enter.
this will log you in as root in single user mode.

2. Once booting is complete, you can change your password with this command:
-->> passwd
Give the new password
and reboot server.

DONE

Usage of Split command in Linux.

Split, have a large file that you need to split into smaller chucks? A Oracle dump maybe? split is your command. Below I split a 110MB file into 30 megabyte chunks.

Split works just fine on both text, and binary (even compressed) files. An example is worth a thousand words of man page (that don't have examples). Here I have TBL_LOSANGELES_CA.dmp, a 109MB Oracle export dump file.

[root@oracle11gr1 ~]# mkdir split
[root@oracle11gr1 ~]# mv /share/TBL_LOSANGELES_CA.dmp split/
[root@oracle11gr1 ~]# cd split/
[root@oracle11gr1 split]# ll
total 111284
-rwxrw-r-- 1 vshare vshare 113836032 Oct 11 22:43 TBL_LOSANGELES_CA.dmp

[root@oracle11gr1 split]#split -b 30m TBL_LOSANGELES_CA.dmp TBL_LOSANGELES_CA_part_
[root@oracle11gr1 split]# ll
total 222584
-rwxrw-r-- 1 vshare vshare 113836032 Oct 11 22:43 TBL_LOSANGELES_CA.dmp
-rw-r--r-- 1 root root 31457280 Mar 14 14:07 TBL_LOSANGELES_CA_part_aa
-rw-r--r-- 1 root root 31457280 Mar 14 14:07 TBL_LOSANGELES_CA_part_ab
-rw-r--r-- 1 root root 31457280 Mar 14 14:07 TBL_LOSANGELES_CA_part_ac
-rw-r--r-- 1 root root 19464192 Mar 14 14:07 TBL_LOSANGELES_CA_part_ad

Cat command is used to join the files split by above split command.

[root@oracle11gr1 split]#cat TBL_LOSANGELES_CA_part_aa TBL_LOSANGELES_CA_part_ab TBL_LOSANGELES_CA_part_ac TBL_LOSANGELES_CA_part_ad >TBL_LOSANGELES

[root@oracle11gr1 split]# ls -lh
total 327M
-rw-r--r-- 1 root root 109M Mar 14 14:15 TBL_LOSANGELES
-rwxrw-r-- 1 vshare vshare 109M Oct 11 22:43 TBL_LOSANGELES_CA.dmp
-rw-r--r-- 1 root root 30M Mar 14 14:07 TBL_LOSANGELES_CA_part_aa
-rw-r--r-- 1 root root 30M Mar 14 14:07 TBL_LOSANGELES_CA_part_ab
-rw-r--r-- 1 root root 30M Mar 14 14:07 TBL_LOSANGELES_CA_part_ac
-rw-r--r-- 1 root root 19M Mar 14 14:07 TBL_LOSANGELES_CA_part_ad

You can check whether the files are identical are not by using the DIFF command.

[root@oracle11gr1 split]# diff -s TBL_LOSANGELES TBL_LOSANGELES_CA.dmp
Files TBL_LOSANGELES and TBL_LOSANGELES_CA.dmp are identical
[root@oracle11gr1 split]#

The cat command can be broken into parts (this is useful if each part is on a separate disk, like one each on CDs). Take note that the first time, a single > is used to make sure that the data from this first part overwrites the destination file (if it already exists), but that each time after that, a double > is used to append to the destination file.

cat TBL_LOSANGELES_CA_part_aa > TBL_LOSANGELES
cat TBL_LOSANGELES_CA_part_ab >> TBL_LOSANGELES
cat TBL_LOSANGELES_CA_part_ac >> TBL_LOSANGELES
cat TBL_LOSANGELES_CA_part_ad >> TBL_LOSANGELES

RMAN - Recovery Manager for Oracle Database 10g

WHAT IS RMAN ?

Recovery Manager is a tool that: manages the process of creating backups and also manages the process of restoring and recovering from them.

WHY USE RMAN ?

No extra costs …Its available for free
RMAN introduced in Oracle 8 it has become simpler with
newer versions and easier than user managed backups Proper security
You are 100% sure your database has been backed up.
Its contains detail of the backups taken etc in its central repository
Facility for testing validity of backups also commands like crosscheck to Check the status of backup.
Faster backups and restores compared to backups without RMAN
RMAN is the only backup tool which supports incremental backups.
Oracle 10g has got further optimized incremental backup which has
resulted in improvement of performance during backup and recovery time
Parallel operations are supported
Better querying facility for knowing different details of backup
No extra redo generated when backup is taken..compared to online backup
without RMAN which results in saving of space in hard disk
RMAN is an intelligent tool
Maintains repository of backup metadata
Remembers backup set location
Knows what need to backed up
Knows what is required for recovery
Knows what backup are redundant

UNDERSTANDING THE RMAN ARCHITECTURE

An oracle RMAN comprises of RMAN EXECUTABLE
This could be present and fired even through
client side TARGET DATABASE.
(Target)This is the database which needs to be backed up
RECOVERY CATALOG is optional otherwise backup
details are stored in target database controlfile
It is a repository of information queried and updated by Recovery Manager
It is a schema or user stored in Oracle database
One schema can support many databases
It contains information about physical schema of target database datafile and archive log, backup sets and pieces

Recovery catalog is a must in following scenarios
In order to store scripts
For tablespace point in time recovery

Media Management Software

Media Management software is a must if you are using
RMAN for storing backup in tape drive directly.

Backups in RMAN

Oracle backups in RMAN are of the following type

RMAN complete backup OR RMAN incremental backup

These backups are of RMAN proprietary nature

IMAGE COPY

Its again a kind of backup. The advantage of uing Image copy is its not in RMAN proprietary format.

Backup Format

RMAN backup is not in oracle format but in RMAN format.
Oracle backup comprises of backup sets and it consists
of backup pieces. Backup sets are logical entity.
In oracle 9i it gets stored in a default location.

There are two type of backup sets
Datafile backup sets,
Archivelog backup sets

One more important point of data file backup sets
is it do not include empty blocks. A backup set
would contain many backup pieces. A single backup piece
consists of physical files which are in RMAN proprietary format.

You can go to RMAN prompt by just typing rman.
RMAN executable is present in ORACLE_HOME/bin location.

bash-2.05$ rman

Recovery Manager: Release 10.1.0.5.0 - Production

Copyright (c) 1995, 2004, Oracle. All rights reserved.


RMAN>

You can use target connect to connect to database.
The database it will connect to depends on the environment variable ORACLE_HOME.

RMAN> connect target

connected to target database: ORCL (DBID=1176834246)

Alternatively you can use “rman TARGET SYS/sys@test NOCATALOG”
to connect to the RMAN of “test” instance.

Here we will be using target database control file
to store all the information required for RMAN,
like backupsets and backup image information etc.

Backup Database:

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 1660944384 bytes

Fixed Size 1322112 bytes
Variable Size 686019456 bytes
Database Buffers 973078528 bytes
Redo Buffers 524288 bytes

RMAN> backup database;

Starting backup at 13-MAR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=166 devtype=DISK
channel ORA_DISK_1: starting full 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 13-MAR-08
channel ORA_DISK_1: finished piece 1 at 13-MAR-08
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2008_03_13/

o1_mf_nnndf_TAG20080313T031455_3b8zv57d_.bkp tag=TAG20080313T031455 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full 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 13-MAR-08
channel ORA_DISK_1: finished piece 1 at 13-MAR-08
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2008_03_13/

o1_mf_ncsnf_TAG20080313T031455_3b8zy7xr_.bkp tag=TAG20080313T031455 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 13-MAR-08
Creating RMAN Catalog

You can also create a repository for RMAN to store all this
information. Repository will be just another small
database which can store the catalog information.
Creating a catalog is a 3 step process

1) Create database which will hold the catalog.
Else you can use the existing database also.
All you need is to create a seperate tablespace
for holding the information about RMAN catalog.

2) Create RMAN tablespace and RMAN user

SQL> create tablespace rman_tbs datafile ‘/u01/app/oracle/oradata/TEST/rman01.dbf’ size 240M EXTENT MANAGEMENT LOCAL segment SPACE MANAGEMENT AUTO ;

Tablespace created.

SQL> create user rcat identified by rcat
default tablespace rman_tbs
TEMPORARY TABLESPACE TEMPTS1
QUOTA UNLIMITED ON rman_tbs account unlock;

User created.

After creating user, you need to grant RECOVERY_CATALOG_OWNER role to that user.

3) Create RMAN catalog.

bash-2.05$ rman catalog rcat/rcat@test

Recovery Manager: Release 10.1.0.5.0 - Production on Thu Mar 13 02:47:16 2008

Copyright (c) 1995, 2004, Oracle. All rights reserved.

connected to recovery catalog database

RMAN> create catalog

recovery catalog created

For registering the database, you need to get connected
to database as well as catalog at the same time. Here is how you can do.

RMAN> connect target

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

The above error is because the database we connected to is not found in the catalog database. We can register the database in catalog.

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Registering database will also resynch the information present
in the target database control file and catalog database.
Since we have taken 1 backup early, it will synchup that
information with RMAN catalog. We can check the same using
LIST BACKUP command at RMAN prompt.

RMAN> list backup;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
18 Full 573.48M DISK 00:01:26 13-MAR-08
BP Key: 20 Status: AVAILABLE Compressed: NO Tag: TAG20080313T031355
Piece Name: /u01/app/oracle/oradata/ORCL/backupset/2008_03_13/
o1_mf_nnndf_TAG20080313T031355_3b8zv57d_.bkp

List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/system01.dbf
2 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/undotbs01.dbf
3 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/sysaux01.dbf
4 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/users01.dbf
5 Full 562487 13-MAR-08 /u01/app/oracle/oradata/ORCL/example01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
19 Full 6.80M DISK 00:00:05 13-MAR-08
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20080313T031355
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2008_03_13/
o1_mf_ncsnf_TAG20080313T031355_3b8zy7xr_.bkp

Control File Included: Ckp SCN: 562487 Ckp time: 13-MAR-08
SPFILE Included: Modification time: 13-MAR-08

RMAN Command line

Backing up the Controlfile and Spfile

The control file can be automatically backed up after each
RMAN backup and database structure change as a way to protect
the RMAN repository (when we are not using a seperate catalog for RMAN).

RMAN> configure controlfile autobackup on;

Backing up control file

RMAN> backup current controlfile;

Creating Image copy of all datafiles in database

These are the image copies and are stored in ORACLE format
and not in RMAN format. Backupsets and backuppieces are
stored in internal RMAN format. Hence these image copies can be
used for manual restore and recovery as well.

RMAN> backup as copy database;

Creating backupsets of all datafiles in database

If you specify BACKUP AS BACKUPSET, then RMAN stores its backups
in backup sets. A backup set, consisting of one or more backup pieces,
contains the physical file data being backed up. This backupset
is written in a format that only RMAN can access. Only RMAN can
create and restore backup sets. Backup sets can be written to disk
or tape, and they are the only type of backup which RMAN can use
to write backups to tape.

RMAN> backup as backupset database;

Backup individual tablespace as backupsets

RMAN> backup tablespace system, HTMLDB;

Backup individual tablespace as image copies

RMAN> backup as copy tablespace system;

Backup individual files as image copies

RMAN> backup as copy datafile ‘/u01/app/oracle/oradata/test/system01.dbf’;

Backup individual files as Backupsets

RMAN> backup datafile ‘/u01/app/oracle/oradata/test/system01.dbf’;

Backup archivelogs

We can backup the archive logs according to the output of some
search condition. Example we want to backup only those
archivelogs which starts with “ARCH_616814159_”.

RMAN> backup acrchivelog like '%ARCH_616814159_%';

Copy archivelogs from some time stamp.

Suppose we want to copy the archivelogs of last 2 days,
then we can use the following commands.

RMAN> BACKUP ARCHIVELOG from time 'sysdate-2';

Backup all archivelog files

RMAN> Backup archivelog all;

Backup archivelog between some time.

RMAN> BACKUP ARCHIVELOG FROM TIME 'SYSDATE-30' UNTIL TIME 'SYSDATE-7';

Specifying copies while backing up.

RMAN> backup copies 2 datafile '/u01/app/oracle/oradata/test/cs_tbs01.dbf';

Remember that copies option cannot be used with image copies.
It can be used only with backupsets.

Giving tags to backups

RMAN> BACKUP TAG ‘weekly_full_db_bkup’ DATABASE MAXSETSIZE 100M;

Backing up backupsets
RMAN> BACKUP BACKUPSET ALL;

Backup imagecopies

RMAN> Backup as copy backupset all;

List Imagecopies

RMAN> list copy;

List Backupsets

RMAN> list backup;

Restoring and Recovering the database

Use the RESTORE and RECOVER commands for RMAN restore and
recovery of physical database files.

RMAN> STARTUP FORCE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;

Recovering Current Tablespaces

RMAN> SQL ‘ALTER TABLESPACE users OFFLINE’;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
SQL ‘ALTER TABLESPACE users ONLINE;

Recovering Current Datafiles

RMAN> SQL ‘ALTER DATABASE DATAFILE 7 OFFLINE’;
RESTORE DATAFILE 7;
RECOVER DATAFILE 7;
SQL ‘ALTER DATABASE DATAFILE 7 ONLINE’;

Recovering Individual Data Blocks

RMAN can recover individual corrupted datafile blocks.
When RMAN performs a complete scan of a file for a backup,
any corrupted blocks are listed in V$DATABASE_BLOCK_CORRUPTION.
Corruption is usually reported in alert logs, trace files or
results of SQL queries. Use BLOCKRECOVER to repair all corrupted blocks:

RMAN> BLOCKRECOVER CORRUPTION LIST;

You can also recover individual blocks, as shown in this example:
RMAN> BLOCKRECOVER DATAFILE 7 BLOCK 233, 235 DATAFILE 4 BLOCK 101;

Managing RMAN repository

As you know that if you do not use a recovery catalog, then
control file of the target database is used as RMAN repository
and eventually after some time the control file records
for RMAN information will get overwritten.Set this initialization
parameter in the parameter file of the target database to
determine how long records are kept:

CONTROL_FILE_RECORD_KEEP_TIME =

You can configure a retention policy to be used by RMAN
to determine which backups are considered obsolete.
This allows you to remove files from the repository that
are no longer needed to meet your retention requirements.
This policy can be based on a recovery window (the maximum number
of days into the past for which you can recover) or redundancy
(how many copies of each backed-up file to keep).

Two Parameters are used to set retention policies.

You can specify the days days between the current time and the
earliest point of recoverability, this is called RECOVERY WINDOW.
RMAN does not consider any full or level 0 incremental backup
as obsolete if it falls within the recovery window.
Alternatively the REDUNDANCY parameter will instruct to store the
number of copies of backup in RMAN repository. If the number of
backups for a specific datafile or control file exceeds the REDUNDANCY
setting considers the extra backups as obsolete.

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

This will make the REDUNDANCY setting to 3. Meaning that it will
at max store 3 copies of backups and ikmages of datafile.
Any more images or backups are consider obsolete.

When we run the below command

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

it will configure new retention policy based on the RECOVERY WINDOW
of 7 days. This means that all the backups which falls outside this
window will be considered obsolute. So in this case you need to have
backup scheduled every week to have atleast 1 valid backup.

Remember that at any point of time, only one policy can be active.
It can be either REDUNDANCY or RECOVERY WINDOW.

When you change the retention policy to another one, it will suspend
the previous policy as shown below.

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Cross checking the available backups

RMAN> CROSSCHECK BACKUP;

Crosscheck is needed when an archivelog file or backup is manually
removed, i.e., not deleted by RMAN. This command ensures that data
about backups in the recovery catalog or control file is synchronized
with corresponding data on disk or in the media management catalog.
The CROSSCHECK command operates only on files that are recorded in
the recovery catalog or the control file.

The CROSSCHECK command does not delete any files that it is
unable to find, but updates their repository records to EXPIRED.
Then, you can run DELETE EXPIRED to remove the repository records
for all expired files as well as any existing physical files whose
records show the status EXPIRED.

If some backup pieces or copies were erroneously marked as EXPIRED,
for example, because the media manager was misconfigured, then after
ensuring that the files really do exist in the media manager, run
the CROSSCHECK BACKUP command again to restore those files to AVAILABLE
status.

RMAN> CROSSCHECK COPY;

This will validate the image copies.

Deleting the backups

The DELETE command removes RMAN backups and copies from DISK marks
the records in control file as DELETED or removes the records from the
recovery catalog (if you use a catalog).

RMAN> DELETE BACKUPSET 101, 102, 103;

RMAN> delete controlfilecopy ‘%ctl’; # Pattern search is allowed.

RMAN> delete archivelog until sequence 20;

This will delete all the archives from the oldest one till the
sequence we have specified.

RMAN> delete backup of tablespace system;

RMAN> DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE DISK;

List commands

RMAN> list backup;

RMAN> list copy;

RMAN> list incarnation of database;

RMAN> list expired backupset;

RMAN> list expired copy;

RMAN> list backup of tablespace sysaux;

RMAN> list copy of datafile 3;

Reporting in RMAN

RMAN> report need backup;

Reports which database files need to be backed up to meet a configured
or specified retention policy

RMAN> report unrecoverable;

Reports which database files require backup because they have been
affected by some NOLOGGING operation such as a direct-path insert

RMAN> report need backup recovery window of 7 days;

Displays objects requiring backup to satisfy a recovery window-based
retention policy.

Suppose in the above command we want to skip a perticular tablespace
like perfstat then we can use the below command.

RMAN> report need backup recovery window of 2 days database skip tablespace perfstat;

RMAN> report need backup redundancy 3;

Displays objects requiring backup to satisfy a redundancy-based
retention policy.

RMAN> report need backup days 7;

Displays files that require more than n days’ worth of archived
redo log files for recovery.

RMAN> report need backup incremental 7;

Displays files that require application of more than n incremental
backups for recovery.

RMAN> report need backup redundancy 2 datafile 3;

Gives report of files with less than 2 redundant backups.

RMAN> report need backup tablespace htmldb;

Report of files that must be backed up to satisfy current
retention policy for this tablespace (htmldb)

RMAN> report need backup incremental 2;

Report of files that need more than 2 incrementals during recovery

RMAN> report need backup device type disk;

Report of files that must be backed up to satisfy current
retention policy for database

RMAN> report obsolete;
Shows backups that are obsolete according to the current
retention policy.

You can add the options RECOVERY WINDOW and REDUNDANCY with
this command as given below.

RMAN> report obsolete recovery window of 3 days;

RMAN> report obsolete redundancy 2;

RMAN> report schema;

This command lists and displays information about the database files.

RMAN> report schema at time 'sysdate - 14';

This command gives report on schema 14 days ago.

RMAN> report schema at SCN 10000;

This gives report on schema at scn 10000.

RMAN> report schema at sequence 55 thread 1;

Gives report of schema at sequence 55.

RMAN configuration

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/oradata/test/dbs/snapcf_test.f’; # default

We can change each of these parameters as per our requirements.

You can return any setting to its default value by using CONFIGURE… CLEAR

RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;

RMAN> CONFIGURE RETENTION POLICY CLEAR;

By default, RMAN sends all backups to an operating system
specific directory on disk. So default setting for DEVICE
TYPE is DISK. You can configure to make backups by default
on tape or any other device as given below.

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;

You can configure backup sets or image copies as the default
for a particular device type, using either of the following commands:

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; # Default becomes image copies
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; # Default becomes uncompressed

You can configure RMAN to use compressed backupsets by
default on a particular device type, by using the CONFIGURE
DEVICE TYPE command with the BACKUP TYPE TO COMPRESSED BACKUPSET
option, as shown in the following examples.

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;

To disable compression you can use below command

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;

RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO BACKUPSET;

You can configure the parallelism for a device type.

RMAM> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;

You can turn on/off controlfile autobackup using

RMAN> configure controlfile autobackup off;

RMAN> configure controlfile autobackup on;

We can configure RMAN channel to write backups and images in a specific format.

The following command configures RMAN to write disk backups to the /backup directory

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/ora_df%t_s%s_s%p’;

%t is replaced with a four byte time stamp,
%s with the backup set number, and
%p with the backup piece number.

You can also configure format for controlfile autobackup as well.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE deviceSpecifier TO ’string’;

For example, you can run the following command:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘?/oradata/cf_%F’;

Here %F will be replaced by following

c-IIIIIIIIII-YYYYMMDD-QQ, where:

IIIIIIIIII stands for the DBID.
YYYYMMDD is a time stamp of the day the backup is generated
QQ is the hex sequence that starts with 00 and has a maximum of FF

You can clear the format using following command.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;



References:

Metalink Note ID: 360416.1

http://advait.wordpress.com/2007/08/10/rman-recovery-manager-for-oracle-database-10g/

http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14192/toc.htm