Life of DBA

 

 It's been a long time since I had put something here, I will try and start over again. 

Btw enjoy reading a poem which almost every dba can relate himself/herself to.

Life of DBA
A Small Poem which is describing the life of a DBA in a good way

Source : Steven Karam

Late at night at the office, Im the only one there
Many new tasks to get done, lots of things to repair.
While setting up some table grants, to keep security tight
My head grew heavy and my sight grew dim
I want to stop for the night
There it was on the display;
A piece of query hell
And I was thinking to myself
Lets give up Oracle and just go Excel
But I made a new index, and I started to pray
For an ending to the query war,
That it would end today

Welcome to the life of DB Admins
Fix the database
At an urgent pace
Always at work in the life of DB Admins
Any time of year, you will find us here.

Their queries dont use indexes, and the, where clause never ends (uh)
They got a lot of inefficient joins no DBA recommends.
How they fill up the ORDER BY, disk sorts galore
And then for no reason, they add even more.

So I grow the PGA
Hoping sorts will decline
But then I have to free up some more RAM from my well planned design
And now theres more full table scans, tons more each day.
With every new line of code that they write
I feel more dismay.

Welcome to the life of DB Admins
Fix the database
At an urgent pace
Living to work in the life of DB Admins
What a nice surprise, loads are on the rise.

CBO is reeling,
Gathered stats once or twice
And I thought, maybe I should just query for some DB_CACHE_ADVICE,
Or Ill add a new MView,
Hints are last but not least
Ill do anything I can do
Until disk reads have all ceased.

Last thing I remember, I was
Looking to explore
A way to get the whole server
Off the rack to smash to the floor
Relax, said my manager,
Though the code does make you grieve.
You still have a lot of work to do,
Get it done or youll never leave!

How to know your Oracle database is 32bit or 64bit?

Method 1:
oracle >cd $ORACLE_HOME/bin
oracle >file oracle
oracle: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped

Method 2:

oracle >sqlplus "/ as sysdba"

SQL*Plus: Release 9.0.1.4.0 - Production on Tue Apr 13 09:21:49 2010

(c) Copyright 2001 Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.0.1.5.0 - Production
With the Partitioning option
JServer Release 9.0.1.4.0 - Production

SQL> select
length(addr)*4 || '-bits' word_length
from
v$process
where
ROWNUM =1; 2 3 4 5 6

WORD_LENGTH
---------------------------------------------
32-bits

The third and the simplest one is while you connect to your database if you find any mentioning of the Platform it is 64bit and if not its 32 bits ;)


64 BITS:

oracle >sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 13 05:35:30 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

32BITS:

oracle >sqlplus "/ as sysdba"

SQL*Plus: Release 9.0.1.4.0 - Production on Tue Apr 13 09:21:49 2010
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.5.0 - Production
With the Partitioning option
JServer Release 9.0.1.4.0 - Production

TNS-00516: Permission denied, Solaris Error: 13: Permission denied

Last week our Unix Adminstrators has moved the data related to our Oracle database from a UFS filesystem to a ZFS filesystem on Solaris.
Once the move is completed, when we tried to start the databases and listeners on the server, the databases started normally but we got problems starting the Listeners.


The Problem:
DBA TEST ZONE> lsnrctl start LISTENER_ORA8

LSNRCTL for Solaris: Version 8.0.6.0.0 - Production on 07-SEP-09 10:52:35

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Starting /usr/app/oracle/product/8.0.6/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 8.0.6.0.0 - Production
System parameter file is /usr/app/oracle/product/8.0.6/network/admin/listener.ora
Log messages written to /usr/app/oracle/product/8.0.6/network/log/listener_ora8.log
Attempted to listen on: (DESCRIPTION=(CONNECT_TIMEOUT=10)(ADDRESS=(PROTOCOL=IPC)(KEY=test8)))
TNS-12546: TNS:permission denied
TNS-12560: TNS:protocol adapter error
TNS-00516: Permission denied
Solaris Error: 13: Permission denied


The Fix:
The Unix permissions for the hidden directory /tmp/.oracle should be:
drwxrwxrwx

Change the permissions on the .oracle directory:


DBA TEST ZONE> ls -lad /tmp/.oracle /var/tmp/.oracle
/tmp/.oracle: No such file or directory
drwxr-xr-x 2 root root 2 Aug 21 02:05 /var/tmp/.oracle

give full permisions to this file:

DBA TEST ZONE> ls -lad /var/tmp/.oracle
drwxrwxrwx 2 root root 2 Aug 21 02:05 /var/tmp/.oracle


Now again retried to start the Listener:

DBA TEST ZONE> lsnrctl start LISTENER_ORA8

LSNRCTL for Solaris: Version 8.0.6.0.0 - Production on 07-SEP-09 10:57:49

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Starting /usr/app/oracle/product/8.0.6/bin/tnslsnr: please wait...

TNSLSNR for Solaris: Version 8.0.6.0.0 - Production
System parameter file is /usr/app/oracle/product/8.0.6/network/admin/listener.ora
Log messages written to /usr/app/oracle/product/8.0.6/network/log/listener_ora8.log
Listening on: (ADDRESS=(PROTOCOL=ipc)(DEV=8)(KEY=test8))
Listening on: (ADDRESS=(PROTOCOL=tcp)(DEV=13)(HOST=10.52.51.61)(PORT=1526))

Connecting to (ADDRESS=(PROTOCOL=IPC)(KEY=test8))
STATUS of the LISTENER
------------------------
Alias LISTENER_ORA8
Version TNSLSNR for Solaris: Version 8.0.6.0.0 - Production
Start Date 07-SEP-09 10:57:52
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security OFF
SNMP OFF
Listener Parameter File /usr/app/oracle/product/8.0.6/network/admin/listener.ora
Listener Log File /usr/app/oracle/product/8.0.6/network/log/listener_ora8.log
Services Summary...
test8 has 1 service handler(s)
The command completed successfully

Delete files of a particular date.

Quick and efficient.


Remove -> ls -ltr | grep "May 23" | awk '{print "rm "$9" /disk1/oradata/arch/"}' | more

above command will list all the files that are having May 23 date as below:

rm orcl_R676045126_T1_S29396.arc.gz
rm orcl_R676045126_T1_S29397.arc.gz
rm orcl_R676045126_T1_S29398.arc.gz
rm orcl_R676045126_T1_S29399.arc.gz
rm orcl_R676045126_T1_S29400.arc.gz
rm orcl_R676045126_T1_S29401.arc.gz
rm orcl_R676045126_T1_S29402.arc.gz
rm orcl_R676045126_T1_S29403.arc.gz
rm orcl_R676045126_T1_S29404.arc.gz

Command for actually doing the job i.e delete files:

Remove -> ls -ltr | grep "May 23" | awk '{print "rm "$9" /disk1/oradata/arch/"}' | sh –x

Use this carefully. Check twice before issuing the command, because sh –x will execute the output. i.e delete all files without a prompt.

Changing the archivelog destination online.

Couple of days back received a high priority ticket developers saying not able to connect to database

hard working people they work on saturdays and sundays and make us also do the hard work :(

they are getting an error ORA-00257: archiver error. Connect internal only, until freed.

Found the following error in the alertlog file:

Sat Apr 11 05:20:00 2009
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 2029 (4)

When I checked for details from the database I can see the following:

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 devdb2
sun-boxdb1
10.2.0.2.0 10-APR-09 OPEN NO 1 FAILED ARCHIVE LOG
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /mounts/devdb2_arch/devdb2
Oldest online log sequence 2029
Next log sequence to archive 2029
Current log sequence 2031

Thought may be the archive destination mount is full but shocked coz I cannot see the mount point.
Then I realized the mistake that I not 'I' "they" made the earlier day when we moved the database to the new Sun box.

Our Storage guys forgot to mount the archive Mount point.
I actually prefer seperate mount points for Data,Arch and Temp :)

Dont ask me why I did not checked when I was making the database up..its a friday evening guys...
But did not know how the database was up without the archive mount point destination specified.

So for immediate resolution had to change the archive destination "the day is SATURDAY and storage guys available on saturday??? no way!!! don't look for them"

Did the following:

SQL> alter system set log_archive_dest_1='LOCATION=/mounts/devdb2_temp/devdb2/arch/';

System altered.

SQL> alter system archive log all;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /mounts/devdb2_temp/devdb2/arch/
Oldest online log sequence 2031
Next log sequence to archive 2033
Current log sequence 2033
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 devdb2
sun-boxdb1
10.2.0.2.0 10-APR-09 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL>

And you can see that all the errors are cleared in the alertlog file:

Sat Apr 11 05:20:03 2009
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 2029 (4)
Sat Apr 11 05:20:06 2009
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/mounts/devdb2_temp/devdb2/arch/' SCOPE=BOTH;
Sat Apr 11 05:20:07 2009
Archiver process freed from errors. No longer stopped
Sat Apr 11 05:20:09 2009
Thread 1 advanced to log sequence 2032
Current log# 3 seq# 2032 mem# 0: /mounts/devdb2_data/devdb2/redo03.log
Sat Apr 11 05:20:29 2009
Thread 1 advanced to log sequence 2033
Current log# 1 seq# 2033 mem# 0: /mounts/devdb2_data/devdb2/redo01.log
~


Happies endingss

Monitoring Temp Tablespace Use

This query shows the current usage of the temporary tablespace.

This is very useful to monitor if you are getting ORA-1652: unable to extend temp segment errors, as well as for monitoring the use of space in your temp tablespace.

select sess.USERNAME, sess.sid, sess.OSUSER, su.segtype,
su.segfile#, su.segblk#, su.extents, su.blocks
from v$sort_usage su, v$session sess
where sess.sql_address=su.sqladdr and sess.sql_hash_value=su.sqlhash ;

You can compare the EXTENTS and BLOCKS columns above to the total available segments and blocks by querying V$TEMP_EXTENT_POOL.

select * from v$temp_extent_pool;

The EXTENTS_CACHED are the total number of extents cached -- following an ora-1652, this will be the max number of extents available. The EXTENTS_USED column lets you know the total number of extents currently in use.

In a TEMPORARY temporary tablespace (ie. one with a TEMPORARY datafile), all the space in the datafile is reserved for temporary sort segments. (In a well-managed database, this is of course true for non-TEMPORARY temp tablespaces as well).

Extent management should be set to LOCAL UNIFORM with a size corresponding to SORT_AREA_SIZE. In this case, it's easy to see how many temp extents will fit in the temporary tablespace:

SELECT dtf.file_id, dtf.bytes/dt.INITIAL_EXTENT max_extents_allowed
from dba_temp_files dtf, dba_tablespaces dt
where dtf.tablespace_name='TEMPORARY_DATA' and
dtf.tablespace_name=dt.TABLESPACE_NAME;

To know the corresponding Temp tablespace - datafile name and Total bytes allocated:

select name,bytes/1024/1024 from v$tempfile;

To know the exact Temp tablespace details like name, total blocks allocated,used blocks and free blocks:

select tablespace_name,(total_blocks*8)/1024,(used_blocks*8)/1024,(free_blocks*8)/1024 from v$sort_segment;

Resizing Logfiles

The best way to resize logfiles is by creating new logfile groups in the new size, then dropping the old logfile groups.


Example:

SYS> select group#,member from v$logfile;

GROUP# MEMBER
---------- ----------------------------------------

1 D:\DATABASE\TRN3\LOGTRN3_1A.LGF
1 F:\DATABASE\TRN3\LOGTRN3_1A.LGF
2 D:\DATABASE\TRN3\LOGTRN3_2A.LGF
2 F:\DATABASE\TRN3\LOGTRN3_2A.LGF
3 D:\DATABASE\TRN3\LOGTRN3_3A.LGF
3 F:\DATABASE\TRN3\LOGTRN3_3A.LGF

6 rows selected.

SYS> alter database add logfile group 4
2 ('d:\database\trn3\logtrn3_4a.lgf','f:\database\trn3\logtrn3_4b.lgf')
3* size 10240K
SYS> /

Database altered.

SYS> alter database add logfile group 5
2 ('d:\database\trn3\logtrn3_5a.lgf','f:\database\trn3\logtrn3_5b.lgf')
3* size 10240K
SYS> /

Database altered.

SYS> alter database add logfile group 6
2 ('d:\database\trn3\logtrn3_6a.lgf','f:\database\trn3\logtrn3_6b.lgf')
3* size 10240K
SYS> /

Database altered.

SYS> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED

6 rows selected.

SYS> alter database drop logfile group 2;

Database altered.

SYS> alter database drop logfile group 3;

Database altered.

SYS> alter system switch logfile;

System altered.

SYS> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 ACTIVE
4 CURRENT
5 UNUSED
6 UNUSED

SYS> alter system switch logfile;

System altered.

SYS> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
4 ACTIVE
5 CURRENT
6 UNUSED

SYS> alter database drop logfile group 1;

Database altered.

SYS> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
4 INACTIVE
5 CURRENT
6 UNUSED

Tarballs

To collect a bunch of files and directories together, use tar.

For example, to tar up your entire home directory and put the tarball into /tmp, do this

$ cd ~srees
$ cd .. # go one dir above dir you want to tar
$ tar cvf /tmp/srees.backup.tar srees

By convention, use .tar as the extension. To untar this file use

$ cd /tmp
$ tar xvf srees.backup.tar

tar untars things in the current directory!

After running the untar, you will find a new directory, /tmp/srees, that is a copy of your home directory.
Note that the way you tar things up dictates the directory structure when untarred.
The fact that I mentioned srees in the tar creation means that I'll have that dir when untarred.

In contrast, the following will also make a copy of my home directory, but without having a srees root dir:

$ cd ~srees
$ tar cvf /tmp/srees.backup.tar *

It is a good idea to tar things up with a root directory so that
when you untar you don't generate a million files in the current directly.

To see what's in a tarball, use

$ tar tvf /tmp/srees.backup.tar

Most of the time you can save space by using the z argument.

The tarball will then be gzip'd and you should use file extension .tar.gz:

$ cd ~srees
$ cd .. # go one dir above dir you want to tar
$ tar cvfz /tmp/srees.backup.tar.gz srees

Unzipping requires the z argument also:

$ cd /tmp
$ tar xvfz srees.backup.tar.gz

If you have a big file to compress, use gzip:

$ gzip bigfile

After execution, your file will have been renamed bigfile.gz.

To uncompress, use

$ gzip -d bigfile.gz


To display a text file that is currently gzip'd, use zcat:

$ zcat bigfile.gz

nohup Execute Commands After You Exit From a Shell Prompt

Most of the time you login into remote server via ssh. If you start a shell script or command and you exit (abort remote connection), the process / command will get killed. Sometime job or command takes a long time. If you are not sure when the job will finish, then it is better to leave job running in background. However, if you logout the system, the job will be stopped. What do you do?

nohup command

Answer is simple, use nohup utility which allows to run command./process or shell script that can continue running in the background after you log out from a shell:


nohup Syntax:
nohup command-name &

Where,

command-name : is name of shell script or command name. You can pass argument to command or a shell script.

& : nohup does not automatically put the command it runs in the background; you must do that explicitly, by ending the command line with an & symbol.

nohup command example

Eg.
# nohup exp system/password@dbname file=exp_dbname_date.dmp log=exp_dbname_date.log full=y &

or

# vi exp_script.sh
exp system/password@dbname file=exp_dbname_date.dmp log=exp_dbname_date.log full=y
:wq

# ls –l
Check the permissions if that has execute permissions
#chomod 755 exp_script.sh

# nohup ./exp_script.sh &

this ensures of the completion of the Job even if the session terminates... :-)

Checking the Job progress cab be done using:

tail -f nohup.out
ctrl c -- to end the checking.

Some more good options for completing jobs without or when session terminations occur:

Option 1:

You can use the below script to keep your session alive with a simple while loop to run infinite loop.

#while true
>do
>echo " Exp going on"
>sleep 200
>done

Here you will not get prompt till you press Ctl + c (^c). This process will keep your session alive till you press enter.


Option 2:

You can run exp_script.sh script to queue (one minute) later execution:

$ echo "exp_script.sh" | at now + 1 minute


Hope this helps.

To calculate the export dump file size without actually exporting in Oracle 10G and above

Many times DBAs' may need to know the size of the export dump file size before starting the actual export of the database. This has become easy with the datapump option from Oracle version 10G.

Just give the following command:

expdp system full=y ESTIMATE_ONLY=Y NOLOGFILE=Y

for more options see

expdp help=y -- from your command prompt.

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.