Monday, April 26, 2010
Sunday, April 25, 2010
Automating Database Refreshes using BCV
Steps:
1. Backup’s of the tables/database.
2. BCV snapshot of the SOURCE database
3. BRING DOWN THE LISTENERS
4. TAKE A TRACE OF THE CONTROL FILE
5. REUSE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE SOURCE DATABASE ON THE TEST SERVER
6. USE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE TARGET DATABASE ON THE TEST SERVER
7. RUN all THE POST DATABASE SCRIPTS
8. BRING UP THE LISTENERS
BACKUP of the tables/Databases
Please ensure that before you start the refresh process you take a backup of the tables which are needed to be backed up.
BCV snapshot of the database
You can do this step in coordination with unix team where in you can request unix team/System Administrator to take a BCV snap shot of the database server from which you needs your development/test database to be refreshed. For this methodology storage team use to set up some SRDF disks on the server on which the BCV needs to be set up. Here I am taking an assumption that production database name is SOURCE-DB and the database needs to be refreshed is TARGET-DB
Once BCV is completed by the SA’s these BCV disks are placed on the dev/test server. Once this is completed, The BCV disks will be looking as below:
/dev/vx/dsk/bldmx1dg/d1000n
10G 7.6G 2.3G 78% /d1000b
/dev/vx/dsk/bldmx1dg/d1001n
172G 156G 15G 92% /d1001b
/dev/vx/dsk/bldmx1dg/d1002n
182G 119G 59G 68% /d1002b
/dev/vx/dsk/bldmx1dg/d1003n
182G 122G 57G 69% /d1003b
/dev/vx/dsk/bldmx1dg/d1004n
182G 118G 60G 67% /d1004b
/dev/vx/dsk/bldmx1dg/d1005
182G 112G 66G 63% /d1005b
/dev/vx/dsk/bldmx1dg/d1006
182G 182G 92M 100% /d1006b
/dev/vx/dsk/bldmx1dg/d1007
182G 155G 25G 87% /d1007b
/dev/vx/dsk/bldmx1dg/d1008
182G 128G 51G 72% /d1008b
/dev/vx/dsk/bldmx1dg/d1009
182G 137G 42G 77% /d1009b
/dev/vx/dsk/bldmx1dg/d1010
182G 82G 94G 47% /d1010b
/dev/vx/dsk/bldmx1dg/d1011
182G 97G 80G 55% /d1011b
/dev/vx/dsk/bldmx1dg/d1012
182G 61G 114G 35% /d1012b
/dev/vx/dsk/bldmx1dg/d1013
182G 64G 111G 37% /d1013b
/dev/vx/dsk/bldmx1dg/d1014
182G 110G 67G 63% /d1014b
/dev/vx/dsk/bldmx1dg/d1015
182G 98G 79G 56% /d1015b
/dev/vx/dsk/bldmx1dg/d1016
364G 363G 945M 100% /d1016b
/dev/vx/dsk/bldmx1dg/d1017
364G 360G 3.5G 100% /d1017b
Above ‘b’ in /d****b represents the BCV disks.
BRING DOWN THE LISTENERS
echo "Stopping listener : `date`" >> ${BCV_LOG}
lsnrctl stop
TAKE A TRACE OF THE CONTROL FILE
Here we use to bring up the database as SOURCE-DB on the test server. This is done because while the database BCV was done there were already many transactions are running on the database. So you need to shut down the database cleanly.
Below is the script that we use to bring up SOURCE database on the test server and then shut it down cleanly to have its SMON do all the transaction recovery.
Step1: set up the oracle environments. Here in my case I am using script /var/opt/oracle/env/.env to set up the environment
/var/opt/oracle/env/.env
Set the ORACLE_SID correctly.
export ORACLE_SID=
Connect as sysdba to the database and redirecting this to a log file $BCV_LOG
sqlplus "/ as sysdba" <>${BCV_LOG}
Startup the database with the spfile/pfile of the source database. Here you can use of minimal parameters which are required to bring up the database
startup mount pfile=/var/opt/oracle/product/9.2.0.5.0/dbs/init.ora;
The above command will bring up the database SOURCE-DB on the target server using the control files of SOURCE-DB on the target server that is a copy of the CONTROL file of the SOURCE DATABASE of the source server.
Now take a trace of the control file. We are doing this so as to REUSE and recreate the control file of the SOURCE DATABASE on the test box.
alter database backup controlfile to trace as '/d1000/var/opt/oracle/admin//udump/control__$proc_date.trc'; --------------------------------------1
Now shut down this database
shutdown immediate
exit
eot
REUSE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE SOURCE DATABASE ON THE TEST SERVER
Below we are going to the location where the command 1 will create the trace file.
echo "Creating control file script for : `date`" >> ${BCV_LOG}
cd /d1000/var/opt/oracle/admin//udump
Now below is what we are doing:
i. Below is the sample trace file which we get on execting step1
ii. On this trace file we are running the below script which will give us a modified script where in below will be the changes
TARGET-DB to SOURCE-DB
RESETLOG to NORESETLOGS
ARCHIVELOG to NOARCHIVELOG
Also because the mount point name (in my case its /d***b)) on the target server is different as compared to that of the SOURCE SERVER(here name is /d****). If this is not the case in your case you can skip the below changes
d1001/d1001b
d1002/d1002b
d1003/d1003b
d1004/d1004b
d1005/d1005b
d1006/d1006b
d1007/d1007b
d1008/d1008b
d1009/d1009b
d1010/d1010b
d1011/d1011b
d1012/d1012b
d1013/d1013b
d1014/d1014b
d1015/d1015b
d1016/d1016b
d1017/d1017b
iii. Here basically there are two control file script in the trace file so we are taking one and passing all these to file control_.sql.
iv. Below is the script that we are using.
sed -e "1,/# be invalidated/d" -e "s///g" -e "s/RESETLOGS/NORESETLOGS/g" -e "s/ARCHIVELOG/NOARCHIVELOG/g" -e "/d200/d" -e "s/d1001/d1001b/g" -e "s/d1002/d1002b/g" -e "s/d1003/d1003b/g" -e "s/d1004/d1004b/g" -e "s/d1005/d1005b/g" -e "s/d1006/d1006b/g" -e "s/d1007/d1007b/g" -e "s/d1008/d1008b/g" -e "s/d1009/d1009b/g" -e "s/d1010/d1010b/g" -e "s/d1011/d1011b/g" -e "s/d1012/d1012b/g" -e "s/d1013/d1013b/g" -e "s/d1014/d1014b/g" -e "s/d1015/d1015b/g" -e "s/d1016/d1016b/g" -e "s/d1017/d1017b/g" -e "/^# Configure snapshot controlfile filename/,/^$/d" -e "s/STARTUP NOMOUNT//g" -e "/^$/d" -e "s/ALTER/---ALTER/g" control__$proc_date.trc > control_.sql
v. Run the generated control file for the database.
. /var/opt/oracle/env/.env
export ORACLE_SID=
sqlplus "/ as sysdba" <>${BCV_LOG}
startup nomount pfile=/var/opt/oracle/product/9.2.0.5.0/dbs/init.ora;
@control_.sql
This will create the control file for the SOURCE-DB. Above we are bringing up the instance as nomount using the pfile of the SOURCE-DB.
vi. Recovery
As ofcourse the database will ask for recovery and we will recover as below and once the media recovery completes we will be open the database.
recover database;
alter database open;
vii. Do a clean shut down of this database
DO a clean shut down of this database as this will disable the TX recovery process as well.
shutdown immediate;
eot
USE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE TARGET DATABASE ON THE TEST SERVER
viii. On this trace file we are running the below script which will give us a modified script where in below will be the changes
REUSE to SET
SOURCE-DB to TARGET-DB
NORESETLOG to RESETLOGS
NOARCHIVELOG to ARCHIVELOG
Also because the mount point name (in my case its /d***b)) on the target server is different as compared to that of the SOURCE SERVER(here name is /d****). If this is not the case in your case you can skip the below changes
d1001/d1001b
d1002/d1002b
d1003/d1003b
d1004/d1004b
d1005/d1005b
d1006/d1006b
d1007/d1007b
d1008/d1008b
d1009/d1009b
d1010/d1010b
d1011/d1011b
d1012/d1012b
d1013/d1013b
d1014/d1014b
d1015/d1015b
d1016/d1016b
d1017/d1017b
ix. Here basically there are two control file script in the trace file so we are taking one (here with the SET option) and passing all these to file control_.sql
x. Below is the script that we are using.
echo "Creating control file script for : `date`" >> ${BCV_LOG}
cd /d1000/var/opt/oracle/admin//udump
sed -e "1,/# be invalidated/d" -e "s/REUSE/SET/g" -e "s///g" -e "s/NORESETLOGS/RESETLOGS/g" -e "s/ARCHIVELOG/NOARCHIVELOG/g" -e "/d200/d" -e "s/d1001/d1001b/g" -e "s/d1002/d1002b/g" -e "s/d1003/d1003b/g" -e "s/d1004/d1004b/g" -e "s/d1005/d1005b/g" -e "s/d1006/d1006b/g" -e "s/d1007/d1007b/g" -e "s/d1008/d1008b/g" -e "s/d1009/d1009b/g" -e "s/d1010/d1010b/g" -e "s/d1011/d1011b/g" -e "s/d1012/d1012b/g" -e "s/d1013/d1013b/g" -e "s/d1014/d1014b/g" -e "s/d1015/d1015b/g" -e "s/d1016/d1016b/g" -e "s/d1017/d1017b/g" -e "/^# Configure snapshot controlfile filename/,/^$/d" -e "s/STARTUP NOMOUNT//g" -e "/^$/d" "s/ALTER/---ALTER/g" control__$proc_date.trc > control_.sql
xi. Run the generated control file for the database.
xii. Here before you run the control file script @control_.sql, you need to make sure that you are removing/moving the control files which are older one and are on the same location as per the pfile/spfile that is being used to bring up SOURCE-DB database.
mv /d1001b/oradata//control01.ctl /d1001b/oradata//control01.ctl.bkp
mv /d1002b/oradata//control02.ctl /d1002b/oradata//control02.ctl.bkp
. /var/opt/oracle/env/.env
export ORACLE_SID=
sqlplus "/ as sysdba" <>${BCV_LOG}
startup nomount
@control_.sql
This will create the control file for the SOURCE-DB. Above we are bringing up the instance as nomount using the pfile of the SOURCE-DB.
xiii. Reset all the logs
Here as we don’t need the redo logs of the SOURCE-DB reset all the logs
alter database open resetlogs;
xiv. shut down of this database
DO a clean shut down of this database as this will disable the TX recovery process as well.
shutdown immediate;
eot
xv. Create the password file
Before creating remove the older password file
cd $ORACLE_HOME/dbs
rm orapw
orapwd file=orapw password=spongeb entries=5
xvi. Start the database
export ORACLE_SID=
sqlplus "/ as sysdba" <>${BCV_LOG}
RUN all THE POST DATABASE SCRIPTS
You may need to run the past database refresh scripts like below and if anything else you may include in your script as well.
!sleep 10
set echo on
spool post_refresh__$proc_date.lst
@/var/opt/oracle/scripts/POST_change_passwords.sql
@/var/opt/oracle/scripts/post__sql.sql
@/var/opt/oracle/scripts//post__others.sql
spool off
exit
eof
BRING UP THE LISTENERS
echo "Starting listener : `date`" >> ${BCV_LOG}
lsnrctl start
1. Backup’s of the tables/database.
2. BCV snapshot of the SOURCE database
3. BRING DOWN THE LISTENERS
4. TAKE A TRACE OF THE CONTROL FILE
5. REUSE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE SOURCE DATABASE ON THE TEST SERVER
6. USE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE TARGET DATABASE ON THE TEST SERVER
7. RUN all THE POST DATABASE SCRIPTS
8. BRING UP THE LISTENERS
BACKUP of the tables/Databases
Please ensure that before you start the refresh process you take a backup of the tables which are needed to be backed up.
BCV snapshot of the database
You can do this step in coordination with unix team where in you can request unix team/System Administrator to take a BCV snap shot of the database server from which you needs your development/test database to be refreshed. For this methodology storage team use to set up some SRDF disks on the server on which the BCV needs to be set up. Here I am taking an assumption that production database name is SOURCE-DB and the database needs to be refreshed is TARGET-DB
Once BCV is completed by the SA’s these BCV disks are placed on the dev/test server. Once this is completed, The BCV disks will be looking as below:
/dev/vx/dsk/bldmx1dg/d1000n
10G 7.6G 2.3G 78% /d1000b
/dev/vx/dsk/bldmx1dg/d1001n
172G 156G 15G 92% /d1001b
/dev/vx/dsk/bldmx1dg/d1002n
182G 119G 59G 68% /d1002b
/dev/vx/dsk/bldmx1dg/d1003n
182G 122G 57G 69% /d1003b
/dev/vx/dsk/bldmx1dg/d1004n
182G 118G 60G 67% /d1004b
/dev/vx/dsk/bldmx1dg/d1005
182G 112G 66G 63% /d1005b
/dev/vx/dsk/bldmx1dg/d1006
182G 182G 92M 100% /d1006b
/dev/vx/dsk/bldmx1dg/d1007
182G 155G 25G 87% /d1007b
/dev/vx/dsk/bldmx1dg/d1008
182G 128G 51G 72% /d1008b
/dev/vx/dsk/bldmx1dg/d1009
182G 137G 42G 77% /d1009b
/dev/vx/dsk/bldmx1dg/d1010
182G 82G 94G 47% /d1010b
/dev/vx/dsk/bldmx1dg/d1011
182G 97G 80G 55% /d1011b
/dev/vx/dsk/bldmx1dg/d1012
182G 61G 114G 35% /d1012b
/dev/vx/dsk/bldmx1dg/d1013
182G 64G 111G 37% /d1013b
/dev/vx/dsk/bldmx1dg/d1014
182G 110G 67G 63% /d1014b
/dev/vx/dsk/bldmx1dg/d1015
182G 98G 79G 56% /d1015b
/dev/vx/dsk/bldmx1dg/d1016
364G 363G 945M 100% /d1016b
/dev/vx/dsk/bldmx1dg/d1017
364G 360G 3.5G 100% /d1017b
Above ‘b’ in /d****b represents the BCV disks.
BRING DOWN THE LISTENERS
echo "Stopping listener : `date`" >> ${BCV_LOG}
lsnrctl stop
TAKE A TRACE OF THE CONTROL FILE
Here we use to bring up the database as SOURCE-DB on the test server. This is done because while the database BCV was done there were already many transactions are running on the database. So you need to shut down the database cleanly.
Below is the script that we use to bring up SOURCE database on the test server and then shut it down cleanly to have its SMON do all the transaction recovery.
Step1: set up the oracle environments. Here in my case I am using script /var/opt/oracle/env/
/var/opt/oracle/env/
Set the ORACLE_SID correctly.
export ORACLE_SID=
Connect as sysdba to the database and redirecting this to a log file $BCV_LOG
sqlplus "/ as sysdba" <
Startup the database with the spfile/pfile of the source database. Here you can use of minimal parameters which are required to bring up the database
startup mount pfile=/var/opt/oracle/product/9.2.0.5.0/dbs/init
The above command will bring up the database SOURCE-DB on the target server using the control files of SOURCE-DB on the target server that is a copy of the CONTROL file of the SOURCE DATABASE of the source server.
Now take a trace of the control file. We are doing this so as to REUSE and recreate the control file of the SOURCE DATABASE on the test box.
alter database backup controlfile to trace as '/d1000/var/opt/oracle/admin/
Now shut down this database
shutdown immediate
exit
eot
REUSE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE SOURCE DATABASE ON THE TEST SERVER
Below we are going to the location where the command 1 will create the trace file.
echo "Creating control file script for
cd /d1000/var/opt/oracle/admin/
Now below is what we are doing:
i. Below is the sample trace file which we get on execting step1
ii. On this trace file we are running the below script which will give us a modified script where in below will be the changes
TARGET-DB to SOURCE-DB
RESETLOG to NORESETLOGS
ARCHIVELOG to NOARCHIVELOG
Also because the mount point name (in my case its /d***b)) on the target server is different as compared to that of the SOURCE SERVER(here name is /d****). If this is not the case in your case you can skip the below changes
d1001/d1001b
d1002/d1002b
d1003/d1003b
d1004/d1004b
d1005/d1005b
d1006/d1006b
d1007/d1007b
d1008/d1008b
d1009/d1009b
d1010/d1010b
d1011/d1011b
d1012/d1012b
d1013/d1013b
d1014/d1014b
d1015/d1015b
d1016/d1016b
d1017/d1017b
iii. Here basically there are two control file script in the trace file so we are taking one and passing all these to file control_
iv. Below is the script that we are using.
sed -e "1,/# be invalidated/d" -e "s/
v. Run the generated control file for the database.
. /var/opt/oracle/env/
export ORACLE_SID=
sqlplus "/ as sysdba" <
startup nomount pfile=/var/opt/oracle/product/9.2.0.5.0/dbs/init
@control_
This will create the control file for the SOURCE-DB. Above we are bringing up the instance as nomount using the pfile of the SOURCE-DB.
vi. Recovery
As ofcourse the database will ask for recovery and we will recover as below and once the media recovery completes we will be open the database.
recover database;
alter database open;
vii. Do a clean shut down of this database
DO a clean shut down of this database as this will disable the TX recovery process as well.
shutdown immediate;
eot
USE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE TARGET DATABASE ON THE TEST SERVER
viii. On this trace file we are running the below script which will give us a modified script where in below will be the changes
REUSE to SET
SOURCE-DB to TARGET-DB
NORESETLOG to RESETLOGS
NOARCHIVELOG to ARCHIVELOG
Also because the mount point name (in my case its /d***b)) on the target server is different as compared to that of the SOURCE SERVER(here name is /d****). If this is not the case in your case you can skip the below changes
d1001/d1001b
d1002/d1002b
d1003/d1003b
d1004/d1004b
d1005/d1005b
d1006/d1006b
d1007/d1007b
d1008/d1008b
d1009/d1009b
d1010/d1010b
d1011/d1011b
d1012/d1012b
d1013/d1013b
d1014/d1014b
d1015/d1015b
d1016/d1016b
d1017/d1017b
ix. Here basically there are two control file script in the trace file so we are taking one (here with the SET option) and passing all these to file control_
x. Below is the script that we are using.
echo "Creating control file script for
cd /d1000/var/opt/oracle/admin/
sed -e "1,/# be invalidated/d" -e "s/REUSE/SET/g" -e "s/
xi. Run the generated control file for the database.
xii. Here before you run the control file script @control_
mv /d1001b/oradata/
mv /d1002b/oradata/
. /var/opt/oracle/env/
export ORACLE_SID=
sqlplus "/ as sysdba" <
startup nomount
@control_
This will create the control file for the SOURCE-DB. Above we are bringing up the instance as nomount using the pfile of the SOURCE-DB.
xiii. Reset all the logs
Here as we don’t need the redo logs of the SOURCE-DB reset all the logs
alter database open resetlogs;
xiv. shut down of this database
DO a clean shut down of this database as this will disable the TX recovery process as well.
shutdown immediate;
eot
xv. Create the password file
Before creating remove the older password file
cd $ORACLE_HOME/dbs
rm orapw
orapwd file=orapw
xvi. Start the database
export ORACLE_SID=
sqlplus "/ as sysdba" <
RUN all THE POST DATABASE SCRIPTS
You may need to run the past database refresh scripts like below and if anything else you may include in your script as well.
!sleep 10
set echo on
spool post_refresh_
@/var/opt/oracle/scripts/POST_change_passwords.sql
@/var/opt/oracle/scripts/post_
@/var/opt/oracle/scripts//post_
spool off
exit
eof
BRING UP THE LISTENERS
echo "Starting listener : `date`" >> ${BCV_LOG}
lsnrctl start
Upgrading Oracle database from version 9205 to 9207
Steps followed
Download software and extract the oracle installation software
Set the oracle environment
Shut down the database
Stop all processes
Take a backup of Oracle installation
Installation
Post Installation Task(Database Upgrade)
STEP 1: Download and extract the latest patch
You can use the oracle website to download the latest oracle patch set. Here we have installed p4163445_92070_SOLARIS64.zip
Here we have placed the patch set dump file p4163445_92070_SOLARIS64.zip at /var/opt/oracle/product
Once the patch set dump has been placed on the server extract the patch set p4163445_92070_SOLARIS64.zip
unzip p4163445_92070_SOLARIS64.zip
STEP2: Set up the oracle environments variables:
This is one of the very important step that need to be followed religiously because if this is not followed properly then this can ruin all the effort.
Set up mainly the correct ORACLE_HOME and ORACLE_SID
Export ORACLE_SID=blfxen1
Export ORACLE_HOME=/var/opt/oracle/product/9.2.0.5.0
STEP 3: SHUT DOWN DATABASE:
Ensure that before the installation begin shut down the databases which the using binaries for which you are doing the upgrading activity.
SQl>Shutdown immediate;
STEP 4: Stop all processes
Ensure that before you begin this upgradation activity all the oracle processes should be stopped.
The main oracle processes would be the database listeners as well as any agent(if running.
Lsnrctl stop
Or
lsnrop.sh stop
Ensure that you run the above command for all the listeners for the databases(if more than one) running on the same server.
STEP 5: BACKUP
This is recommended that before installation activity starts we should take a backup of the binaries on which the database is running. This is to ensure to roll back in case of any corruption after the installation activity completes.
STEP 6: INSTALLING BINARIES
a. First of all we need to direct the X applications to display on the local system. So for that export the DISPLAY parameter a:
DISPLAY=export DISPLAY=:0.0
For e.g.
DISPLAY=export DISPLAY=:0.0
Where LOCAL_HOST is the address (ip or hostname) of the machine where you want to display runInstaller.
b. Check on your local machine where you are displaying the runInstaller for softwares like EXCEED/XSESSION (XSecurePro64) which supports display settings required for running the runInstaller.
c. Once the patch set dump is extracted
unzip p4163445_92070_SOLARIS64.zip
It will create a directory Disk1
Inside the directory below are the files that will be present.
() [SID: db_name]/var/opt/oracle/product>cd Disk1
() [SID: db_name]/var/opt/oracle/product/Disk1>ls -ltr
total 4280
-rwxr-xr-x 1 oracle dba 1477 Sep 1 2005 runInstaller
drwxr-xr-x 2 oracle dba 96 Sep 1 2005 response
drwxr-xr-x 4 oracle dba 1024 Sep 1 2005 install
-rwxr-xr-x 1 oracle dba 2087863 Sep 2 2005 9207_buglist.htm
-rwxr-xr-x 1 oracle dba 99327 Sep 2 2005 patch_note.htm
drwxr-xr-x 8 oracle dba 1024 Sep 2 2005 stage
d.Now,go to /var/opt/oracle/product/Disk1
Before running runInstaller ensure that file /var/opt/oracle/oraInst.loc contains the right oraInventory path
If any issue you face like the installer product are not being shown at the
GUI runInstaller window, you must give the correct path of the ORAINVENTORY in the orainst.loc file.
5.RUNINSTALLER
This will open a GUI window:
() [SID: blfxdev2]/var/opt/oracle/product/Disk1>./runInstaller
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be 5.6, 5.7, 5.8, 5.9 or 5.10. Actual 5.9
Passed
Checking Temp space: must be greater than 250 MB. Actual 27549 MB Passed
Checking swap space: must be greater than 500 MB. Actual 29116MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 256 Passed
All installer requirements met.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-08-20_11-15-44AM. Please wait ...() [SID: blfxdev2]/var/opt/oracle/product/Disk1>Oracle Universal Installer, Version 10.1.0.4.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.
Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct
Just ensure that installed product show the binaries installed
Here ensure that when you click on the Installed product (as shown above in the screen shot ) you find the Oracle products installed. If no check path of inventory_loc your file’s /var/opt/oracle/oraInst.loc and ensure that it is not corrupted. Other wise change this to path of $ORACLE_HOME/oraInventory
f11u30-06:sdas> more oraInst.loc
#Oracle Installer Location File Location
#Mon Jun 08 23:20:57 GMT 2009
inst_group=dba
inventory_loc=/var/opt/oracle/oraInventory
Also ensure that the Destination PATH taken while installation is your ORACLE_HOME and source PATH is file patchset_directory/Disk1/stage/products.xml(here my pathset_directory is /var/opt/oracle/product).
Here as shown in the above diagram, path will be the path of the product that needs to be installed. Here in this case it is /d2100/var/opt/oracle/product/stage/product.xml
At the destination side path will be the path of your old binaries. Here in this case this is ORACLE_HOME.
$ORACLE_HOME=/var/opt/oracle/product/9.2.0.5
Here once you click NEXT installer check the components present at the ORACLE_HOME location.
As shown above, the installer checks for the oracle components that are installed in the database. If the upgraded version is already installed then, an error will be popped up telling that the patchset is already applied.
Otherwise the product component will start loading on the server. Once this completes to 100% then we will get an overall statistics.
Next we will start the upgradation.
d. At last when the upgradation of binaries completes before which, you will be asked to run root.sh by the root user.
Run this file by root user otherwise contact unix SA’s
/d2100/var/opt/oracle/product/9.2.0.5.0/root.sh
Output of the root.sh
----------------------------
Running Oracle9 root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /var/opt/oracle/product/9.2.0.5.0
Enter the full pathname of the local bin directory: [/usr/local/bin]:
bash-2.05# /d2100/var/opt/oracle/product/9.2.0.5.0/root.sh
Running Oracle9 root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /var/opt/oracle/product/9.2.0.5.0
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying coraenv to /usr/local/bin ...
Adding entry to /var/opt/oracle/oratab file...
Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/dbs/initbillprd1.ora
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/dbs/orapwbillprd1
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/rdbms/filemap
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
STEP 7: DATABASE UPGRADE
a.Check SYSTEM Tablespace Size
Here as a pre-requisite we need to ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace. If not we need to add some space to system tablespace.
Next, check for SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
b.Check java_pool_size:
show parameter java_pool_size
Here if required increase the size of java_pool_size:
alter system set java_pool_size=’150M’ scope=spfile;
c.check shared_pool_size
show parameter shared_pool_size
Here if required increase the size of shared_pool_size
alter system set shared_pool_size =’150M’ scope=spfile;
d.If the system uses an initialization parameter file then change the value of these parameters in the pfile (initsid.ora)
Shut down the database: shut down immediate
Startup pfile=
Create spfile form pfile;
Shut immediate
Startup
Now ensure that the changes done in the pfile are getting reflected in the spfile
show parameter java_pool_size
show parameter shared_pool_size
e. Now we are ready for the upgradation as the pre check has completed.
First startup the data
Startup the database in migrate mode:
Startup migrate;
Once the database is started in migrate mode, we need to run catpatch.sql script.
Before running take a spool file
SQL> SPOOL patch_db_name.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF
I am attaching below log file that comes up on running the catpatch.sql script.
Once the catpatch finishes the last few lines will show you as given below:
Output file will show as given below
COMP_NAME STATUS VERSION
----------------------------------- ----------- ------------------------------
Oracle9i Catalog Views VALID 9.2.0.7.0
Oracle9i Packages and Types VALID 9.2.0.7.0
JServer JAVA Virtual Machine VALID 9.2.0.7.0
Oracle XDK for Java VALID 9.2.0.9.0
Oracle9i Java Packages VALID 9.2.0.7.0
f.INVALID OBJECTS
Check all the invalid objects in the database. We should always run the utlrp utility once the upgrade completes.
To check the invalid objects in the database:
SQL>select * from dba_objects where status='INVALID'
Compile all the invalid objects in the database.
SQL>@?/rdbms/admin/utlrp
This completes the upgradation of the database from 9.2.0.5.0 to 9.2.0.7.0
g.CONFIRMATION
TO confirm the version of the several components installed in the database, query dba_registry
SQL> select COMP_NAME,VERSION from dba_registry;
COMP_NAME VERSION
Oracle9i Catalog Views 9.2.0.7.0
Oracle9i Packages and Types 9.2.0.7.0
JServer JAVA Virtual Machine 9.2.0.7.0
Oracle XDK for Java 9.2.0.9.0
Oracle9i Java Packages 9.2.0.7.0
Download software and extract the oracle installation software
Set the oracle environment
Shut down the database
Stop all processes
Take a backup of Oracle installation
Installation
Post Installation Task(Database Upgrade)
STEP 1: Download and extract the latest patch
You can use the oracle website to download the latest oracle patch set. Here we have installed p4163445_92070_SOLARIS64.zip
Here we have placed the patch set dump file p4163445_92070_SOLARIS64.zip at /var/opt/oracle/product
Once the patch set dump has been placed on the server extract the patch set p4163445_92070_SOLARIS64.zip
unzip p4163445_92070_SOLARIS64.zip
STEP2: Set up the oracle environments variables:
This is one of the very important step that need to be followed religiously because if this is not followed properly then this can ruin all the effort.
Set up mainly the correct ORACLE_HOME and ORACLE_SID
Export ORACLE_SID=blfxen1
Export ORACLE_HOME=/var/opt/oracle/product/9.2.0.5.0
STEP 3: SHUT DOWN DATABASE:
Ensure that before the installation begin shut down the databases which the using binaries for which you are doing the upgrading activity.
SQl>Shutdown immediate;
STEP 4: Stop all processes
Ensure that before you begin this upgradation activity all the oracle processes should be stopped.
The main oracle processes would be the database listeners as well as any agent(if running.
Lsnrctl stop
Or
lsnrop.sh stop
Ensure that you run the above command for all the listeners for the databases(if more than one) running on the same server.
STEP 5: BACKUP
This is recommended that before installation activity starts we should take a backup of the binaries on which the database is running. This is to ensure to roll back in case of any corruption after the installation activity completes.
STEP 6: INSTALLING BINARIES
a. First of all we need to direct the X applications to display on the local system. So for that export the DISPLAY parameter a:
DISPLAY=export DISPLAY=
For e.g.
DISPLAY=export DISPLAY=
Where LOCAL_HOST is the address (ip or hostname) of the machine where you want to display runInstaller.
b. Check on your local machine where you are displaying the runInstaller for softwares like EXCEED/XSESSION (XSecurePro64) which supports display settings required for running the runInstaller.
c. Once the patch set dump is extracted
unzip p4163445_92070_SOLARIS64.zip
It will create a directory Disk1
Inside the directory below are the files that will be present.
(
(
total 4280
-rwxr-xr-x 1 oracle dba 1477 Sep 1 2005 runInstaller
drwxr-xr-x 2 oracle dba 96 Sep 1 2005 response
drwxr-xr-x 4 oracle dba 1024 Sep 1 2005 install
-rwxr-xr-x 1 oracle dba 2087863 Sep 2 2005 9207_buglist.htm
-rwxr-xr-x 1 oracle dba 99327 Sep 2 2005 patch_note.htm
drwxr-xr-x 8 oracle dba 1024 Sep 2 2005 stage
d.Now,go to /var/opt/oracle/product/Disk1
Before running runInstaller ensure that file /var/opt/oracle/oraInst.loc contains the right oraInventory path
If any issue you face like the installer product are not being shown at the
GUI runInstaller window, you must give the correct path of the ORAINVENTORY in the orainst.loc file.
5.RUNINSTALLER
This will open a GUI window:
(
Starting Oracle Universal Installer...
Checking installer requirements...
Checking operating system version: must be 5.6, 5.7, 5.8, 5.9 or 5.10. Actual 5.9
Passed
Checking Temp space: must be greater than 250 MB. Actual 27549 MB Passed
Checking swap space: must be greater than 500 MB. Actual 29116MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 256 Passed
All installer requirements met.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2009-08-20_11-15-44AM. Please wait ...(
Copyright (C) 1999, 2005, Oracle. All rights reserved.
Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct
Just ensure that installed product show the binaries installed
Here ensure that when you click on the Installed product (as shown above in the screen shot ) you find the Oracle products installed. If no check path of inventory_loc your file’s /var/opt/oracle/oraInst.loc and ensure that it is not corrupted. Other wise change this to path of $ORACLE_HOME/oraInventory
f11u30-06:sdas> more oraInst.loc
#Oracle Installer Location File Location
#Mon Jun 08 23:20:57 GMT 2009
inst_group=dba
inventory_loc=/var/opt/oracle/oraInventory
Also ensure that the Destination PATH taken while installation is your ORACLE_HOME and source PATH is file patchset_directory/Disk1/stage/products.xml(here my pathset_directory is /var/opt/oracle/product).
Here as shown in the above diagram, path will be the path of the product that needs to be installed. Here in this case it is /d2100/var/opt/oracle/product/stage/product.xml
At the destination side path will be the path of your old binaries. Here in this case this is ORACLE_HOME.
$ORACLE_HOME=/var/opt/oracle/product/9.2.0.5
Here once you click NEXT installer check the components present at the ORACLE_HOME location.
As shown above, the installer checks for the oracle components that are installed in the database. If the upgraded version is already installed then, an error will be popped up telling that the patchset is already applied.
Otherwise the product component will start loading on the server. Once this completes to 100% then we will get an overall statistics.
Next we will start the upgradation.
d. At last when the upgradation of binaries completes before which, you will be asked to run root.sh by the root user.
Run this file by root user otherwise contact unix SA’s
/d2100/var/opt/oracle/product/9.2.0.5.0/root.sh
Output of the root.sh
----------------------------
Running Oracle9 root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /var/opt/oracle/product/9.2.0.5.0
Enter the full pathname of the local bin directory: [/usr/local/bin]:
bash-2.05# /d2100/var/opt/oracle/product/9.2.0.5.0/root.sh
Running Oracle9 root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /var/opt/oracle/product/9.2.0.5.0
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying coraenv to /usr/local/bin ...
Adding entry to /var/opt/oracle/oratab file...
Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/dbs/initbillprd1.ora
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/dbs/orapwbillprd1
chmod: WARNING: can't access /var/opt/oracle/product/9.2.0.5.0/rdbms/filemap
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
STEP 7: DATABASE UPGRADE
a.Check SYSTEM Tablespace Size
Here as a pre-requisite we need to ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace. If not we need to add some space to system tablespace.
Next, check for SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
b.Check java_pool_size:
show parameter java_pool_size
Here if required increase the size of java_pool_size:
alter system set java_pool_size=’150M’ scope=spfile;
c.check shared_pool_size
show parameter shared_pool_size
Here if required increase the size of shared_pool_size
alter system set shared_pool_size =’150M’ scope=spfile;
d.If the system uses an initialization parameter file then change the value of these parameters in the pfile (initsid.ora)
Shut down the database: shut down immediate
Startup pfile=
Create spfile form pfile;
Shut immediate
Startup
Now ensure that the changes done in the pfile are getting reflected in the spfile
show parameter java_pool_size
show parameter shared_pool_size
e. Now we are ready for the upgradation as the pre check has completed.
First startup the data
Startup the database in migrate mode:
Startup migrate;
Once the database is started in migrate mode, we need to run catpatch.sql script.
Before running take a spool file
SQL> SPOOL patch_db_name.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> SPOOL OFF
I am attaching below log file that comes up on running the catpatch.sql script.
Once the catpatch finishes the last few lines will show you as given below:
Output file will show as given below
COMP_NAME STATUS VERSION
----------------------------------- ----------- ------------------------------
Oracle9i Catalog Views VALID 9.2.0.7.0
Oracle9i Packages and Types VALID 9.2.0.7.0
JServer JAVA Virtual Machine VALID 9.2.0.7.0
Oracle XDK for Java VALID 9.2.0.9.0
Oracle9i Java Packages VALID 9.2.0.7.0
f.INVALID OBJECTS
Check all the invalid objects in the database. We should always run the utlrp utility once the upgrade completes.
To check the invalid objects in the database:
SQL>select * from dba_objects where status='INVALID'
Compile all the invalid objects in the database.
SQL>@?/rdbms/admin/utlrp
This completes the upgradation of the database from 9.2.0.5.0 to 9.2.0.7.0
g.CONFIRMATION
TO confirm the version of the several components installed in the database, query dba_registry
SQL> select COMP_NAME,VERSION from dba_registry;
COMP_NAME VERSION
Oracle9i Catalog Views 9.2.0.7.0
Oracle9i Packages and Types 9.2.0.7.0
JServer JAVA Virtual Machine 9.2.0.7.0
Oracle XDK for Java 9.2.0.9.0
Oracle9i Java Packages 9.2.0.7.0
Recycle Bin in Oracle 10G
Recycle Bin in Oracle 10G
Introduction
In our day to day work we often come across scenario when a table gets deleted from database by mistake and we end up losing the data. The only way to recourse is to use tablespace point-in-time recovery in a different database and then recreate the table in the current database using export/import or some other method. This procedure demands significant DBA effort as well as precious time, not to mention the use of a different database for cloning.
In Oracle Database 10G Flash back Table feature is introduced. It makes the revival of dropped tables as simple as executing some sql commands. This feature works similar to windows recycle bin which we use in our daily work. Let’s see how it works:
How it works
First we create a test table.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
TABLE CREATED
SQL> INSERT INTO TEST VALUES (‘version 1’, sysdate);
1 ROW CREATED
SQL> SELECT * FROM TEST;
VERSION CHANGE_TI
------------- ----------------
version 1 21-AUG-07
Now let’s drop this table and see what happens.
SQL> DROP TABLE TEST;
TABLE DROPPED
Let’s check the Recycle bin.
SQL> SELECT * FROM RECYCLEBIN;
OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$Z/6jVsGtRHe46leTmSFf5g==$0 TEST DROP
TABLE USERS 2007-08-21:13:59:00
2007-08-21:14:02:42 9.0858E+12 YES YES
96232 96232 96232 32
This looks pretty weird. Let’s format the output.
SQL> SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE, CAN AS ‘UND’, CAN_PURGE AS ‘PUR’, DROPTIME FROM RECYCLEBIN;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR
------------------------------------------- ------------------------ ---------- ----- --------
DROPTIME
---------------
BIN$Z/6jVsGtRHe46leTmSFf5g==$0 TEST TABLE YES YES
2007-08-21:14:02:42
What happened here is table TEST got renamed and it’s still present in the database. It stays in the same tablespace, with the same structure as that of the original table. If there are indexes or triggers defined on the table, they are renamed too, using the same naming convention used by the table. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table BIN$Z/6jVsGtRHe46leTmSFf5g==$0, preserving the complete object structure of the dropped table.
Recycle Bin
Recycle bin is a logical structure that catalogs the dropped objects. To see the contents of recycle bin one can use following command on Sql*Plus prompt (version 10.1)
SQL> SHOW RECYCLEBIN;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$Z/6jVsGtRHe46leTmSFf5g==$0 TABLE 2007-08-21:14:02:42
It shows the original name and recycle bin name of the dropped object.
There are two recycle bin views: USER_RECYCLEBIN and DBA_RECYCLEBIN.
USER_RECYCLEBIN: Lets users see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.
DBA_RECYCLEBIN: Lets administrators see all dropped objects in the recycle bin.
Turning recycle bin ON and OFF
Recycle bin is enabled by default in Oracle 10G. You can turn it OFF or ON from RECYCLEBIN initialization parameter, at system or session level.
To turn it OFF
• ALTER SESSION SET recyclebin = OFF;
• ALTER SYSTEM SET recyclebin = OFF;
To turn it ON
• ALTER SESSION SET recyclebin = ON;
• ALTER SYSTEM SET recyclebin = ON;
Objects already in the recycle bin are not affected by enabling or disabling the recycle bin using ALTER SYSTEM or ALTER SESSION.
The initial value of the recyclebin parameter can be set in the text initialization files init.ora:
recyclebin=on
Reinstating the Table
To reinstate the table to its original state use the following command:
SQL> FLASHBACK TABLE TEST TO BEFORE DROP;
FLASHBACK COMPLETE.
Now the table is reinstated.
SQL> SELECT * FROM TEST;
VERSION CHANGE_TI
---------- ---------
version 1 21-AUG-07
The table is reinstated. If you check the recycle bin now it will be empty.
SQL> SELECT * FROM RECYCLEBIN;
NO ROWS SELECTED.
When we drop a table with RECYCLEBIN initialisation parameter set to ON, it does not frees up the space in original tablespace. We have to purge the recycle bin to free the space:
SQL> PURGE RECYCLEBIN;
Dropped table will be deleted permanently.
If you want to drop the table permanently at one go without using the Flashback feature, you can use the following command:
SQL> DROP TABLE TEST PURGE;
It will not rename the table and will delete it permanently.
Managing the Recycle Bin
So far we have learnt that once a table or database object is dropped, it doesn’t free the space and get renamed. So, what will happen if dropped objects take up all the space?
Actually, this situation never arrives. When a tablespace is completely filled with recycle bin data such that the data files have to extend to make room for more data, the tablespace is said to be under ‘Space Pressure’. In case of ‘Space Pressure’ recycle bin data is automatically purged in a first-in-first-out manner. The dependent objects e.g. indexes are removed before the table gets purged.
If user quotas are defined for a particular table space, Space pressure situation can occur for a user even if the table space is free. In this case also recycle bin data belonging to that user is purged in FIFO manner.
We can purge the table from recylce bin using following command:
SQL> PURGE TABLE TEST;
One can also use the recycle bin name to purge it.
SQL> PURGE TABLE ‘BIN$Z/6jVsGtRHe46leTmSFf5g==$0’;
This command will delete the table permanently along with its associated objects e.g. indexes, constraints etc. If you want to delete the dependent object from recyclebin, it can be done using following command:
SQL> PURGE INEDEX IND_TEST;
If the recycle bin for a particular user in table space needs to be purged it can be done using following command:
SQL> PURGE TABLESPACE USERS USER GAURAV;
You as a DBA can purge all the objects in any tablespace using
SQL> PURGE DBA_RECYCLEBIN;
So, recyclebin can be managed in several ways depending on the requirements.
Dropped Table Versions
Consider a scenario, where a user creates and drops a table several times with same name.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
Table created.
SQL> INSERT INTO TEST VALUES ('version 1', sysdate);
1 row created.
SQL> drop table test;
Table dropped.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
Table created.
SQL> INSERT INTO TEST VALUES ('version 2', sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
Table created.
SQL> INSERT INTO TEST VALUES ('version 3', sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
So, now if we reinstate the table ‘TEST’, which table will get reinstated? Let’s check it out.
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from test;
VERSION CHANGE_TI
---------- ---------
version 3 09-OCT-07
So, we see that the version which was dropped last was reinstated first. So, recycle bin follows Last In First Out (LIFO) algorithm to reinstate the tables with same name.
You can’t reinstate the other two tables unless you rename them.
SQL> flashback table test to before drop rename to test2;
SQL> flashback table test to before drop rename to test1;
Warning while reinstating the Tables
Be warned that when you undrop a table, it gets reinstated to its original name, but its associated objects e.g. indexes, triggers are left with the recycled names. Sources such as views and procedures defined on the table are not recompiled and remain in the invalid state. Original name has to be retrieved manually and then applied to the flashback objects.
This information is stored in view named user_recylcebin. Following query should be used to retrieve the old names, before flashing - back the table:
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'Table name ')
AND ORIGINAL_NAME! = 'Table name';
After the table is flashed-back, all the associated objects will be reinstated to their name shown in ‘Object_Name’ column.
Then you can rename them to their original names using Alter command.
Exceptions:
• Bitmap Indexes: Once they are dropped, they are not placed in recycle bin and hence not retrievable.
• Materialized View Logs: when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.
• Referential integrity constraints: that reference another table are lost when the table is put in the recyclebin and then restored.
• Constraint Names: are also not retrievable from the view.
Other uses of Flashback Tables
Recycle bin can also be used to reinstate the Table to a different point in time, replacing the current table to a different version in past. e.g. following example reinstates current table to a System Change Number (SCN) 2390874650.
Flashback table test to SCN 2390874650;
This feature uses Oracle Data Pump technology to create a different table, uses flashback to populate the table with the versions of the data at that SCN, and then replaces the original table with the new table.
Conclusion
In this document we have covered some useful features of Recylcebin in Oracle.
To Sum up:
• Recycle bin maintains the version of dropped objects and reinstate them in LIFO order.
• Flashing back a table reinstates the table to its original name, but all the associated objects have to be renamed to their original name.
• Even if the recyclebin parameter is turned off, objects already present in recyclebin can be reinstated.
References:
1. http://www.orafaq.com/node/968
2. http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html
Introduction
In our day to day work we often come across scenario when a table gets deleted from database by mistake and we end up losing the data. The only way to recourse is to use tablespace point-in-time recovery in a different database and then recreate the table in the current database using export/import or some other method. This procedure demands significant DBA effort as well as precious time, not to mention the use of a different database for cloning.
In Oracle Database 10G Flash back Table feature is introduced. It makes the revival of dropped tables as simple as executing some sql commands. This feature works similar to windows recycle bin which we use in our daily work. Let’s see how it works:
How it works
First we create a test table.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
TABLE CREATED
SQL> INSERT INTO TEST VALUES (‘version 1’, sysdate);
1 ROW CREATED
SQL> SELECT * FROM TEST;
VERSION CHANGE_TI
------------- ----------------
version 1 21-AUG-07
Now let’s drop this table and see what happens.
SQL> DROP TABLE TEST;
TABLE DROPPED
Let’s check the Recycle bin.
SQL> SELECT * FROM RECYCLEBIN;
OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$Z/6jVsGtRHe46leTmSFf5g==$0 TEST DROP
TABLE USERS 2007-08-21:13:59:00
2007-08-21:14:02:42 9.0858E+12 YES YES
96232 96232 96232 32
This looks pretty weird. Let’s format the output.
SQL> SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE, CAN AS ‘UND’, CAN_PURGE AS ‘PUR’, DROPTIME FROM RECYCLEBIN;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR
------------------------------------------- ------------------------ ---------- ----- --------
DROPTIME
---------------
BIN$Z/6jVsGtRHe46leTmSFf5g==$0 TEST TABLE YES YES
2007-08-21:14:02:42
What happened here is table TEST got renamed and it’s still present in the database. It stays in the same tablespace, with the same structure as that of the original table. If there are indexes or triggers defined on the table, they are renamed too, using the same naming convention used by the table. Any dependent sources such as procedures are invalidated; the triggers and indexes of the original table are instead placed on the renamed table BIN$Z/6jVsGtRHe46leTmSFf5g==$0, preserving the complete object structure of the dropped table.
Recycle Bin
Recycle bin is a logical structure that catalogs the dropped objects. To see the contents of recycle bin one can use following command on Sql*Plus prompt (version 10.1)
SQL> SHOW RECYCLEBIN;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$Z/6jVsGtRHe46leTmSFf5g==$0 TABLE 2007-08-21:14:02:42
It shows the original name and recycle bin name of the dropped object.
There are two recycle bin views: USER_RECYCLEBIN and DBA_RECYCLEBIN.
USER_RECYCLEBIN: Lets users see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.
DBA_RECYCLEBIN: Lets administrators see all dropped objects in the recycle bin.
Turning recycle bin ON and OFF
Recycle bin is enabled by default in Oracle 10G. You can turn it OFF or ON from RECYCLEBIN initialization parameter, at system or session level.
To turn it OFF
• ALTER SESSION SET recyclebin = OFF;
• ALTER SYSTEM SET recyclebin = OFF;
To turn it ON
• ALTER SESSION SET recyclebin = ON;
• ALTER SYSTEM SET recyclebin = ON;
Objects already in the recycle bin are not affected by enabling or disabling the recycle bin using ALTER SYSTEM or ALTER SESSION.
The initial value of the recyclebin parameter can be set in the text initialization files init.ora:
recyclebin=on
Reinstating the Table
To reinstate the table to its original state use the following command:
SQL> FLASHBACK TABLE TEST TO BEFORE DROP;
FLASHBACK COMPLETE.
Now the table is reinstated.
SQL> SELECT * FROM TEST;
VERSION CHANGE_TI
---------- ---------
version 1 21-AUG-07
The table is reinstated. If you check the recycle bin now it will be empty.
SQL> SELECT * FROM RECYCLEBIN;
NO ROWS SELECTED.
When we drop a table with RECYCLEBIN initialisation parameter set to ON, it does not frees up the space in original tablespace. We have to purge the recycle bin to free the space:
SQL> PURGE RECYCLEBIN;
Dropped table will be deleted permanently.
If you want to drop the table permanently at one go without using the Flashback feature, you can use the following command:
SQL> DROP TABLE TEST PURGE;
It will not rename the table and will delete it permanently.
Managing the Recycle Bin
So far we have learnt that once a table or database object is dropped, it doesn’t free the space and get renamed. So, what will happen if dropped objects take up all the space?
Actually, this situation never arrives. When a tablespace is completely filled with recycle bin data such that the data files have to extend to make room for more data, the tablespace is said to be under ‘Space Pressure’. In case of ‘Space Pressure’ recycle bin data is automatically purged in a first-in-first-out manner. The dependent objects e.g. indexes are removed before the table gets purged.
If user quotas are defined for a particular table space, Space pressure situation can occur for a user even if the table space is free. In this case also recycle bin data belonging to that user is purged in FIFO manner.
We can purge the table from recylce bin using following command:
SQL> PURGE TABLE TEST;
One can also use the recycle bin name to purge it.
SQL> PURGE TABLE ‘BIN$Z/6jVsGtRHe46leTmSFf5g==$0’;
This command will delete the table permanently along with its associated objects e.g. indexes, constraints etc. If you want to delete the dependent object from recyclebin, it can be done using following command:
SQL> PURGE INEDEX IND_TEST;
If the recycle bin for a particular user in table space needs to be purged it can be done using following command:
SQL> PURGE TABLESPACE USERS USER GAURAV;
You as a DBA can purge all the objects in any tablespace using
SQL> PURGE DBA_RECYCLEBIN;
So, recyclebin can be managed in several ways depending on the requirements.
Dropped Table Versions
Consider a scenario, where a user creates and drops a table several times with same name.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
Table created.
SQL> INSERT INTO TEST VALUES ('version 1', sysdate);
1 row created.
SQL> drop table test;
Table dropped.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
Table created.
SQL> INSERT INTO TEST VALUES ('version 2', sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> CREATE TABLE TEST (VERSION CHAR (10), CHANGE_TIME DATE);
Table created.
SQL> INSERT INTO TEST VALUES ('version 3', sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
So, now if we reinstate the table ‘TEST’, which table will get reinstated? Let’s check it out.
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from test;
VERSION CHANGE_TI
---------- ---------
version 3 09-OCT-07
So, we see that the version which was dropped last was reinstated first. So, recycle bin follows Last In First Out (LIFO) algorithm to reinstate the tables with same name.
You can’t reinstate the other two tables unless you rename them.
SQL> flashback table test to before drop rename to test2;
SQL> flashback table test to before drop rename to test1;
Warning while reinstating the Tables
Be warned that when you undrop a table, it gets reinstated to its original name, but its associated objects e.g. indexes, triggers are left with the recycled names. Sources such as views and procedures defined on the table are not recompiled and remain in the invalid state. Original name has to be retrieved manually and then applied to the flashback objects.
This information is stored in view named user_recylcebin. Following query should be used to retrieve the old names, before flashing - back the table:
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE
FROM USER_RECYCLEBIN
WHERE BASE_OBJECT = (SELECT BASE_OBJECT FROM USER_RECYCLEBIN
WHERE ORIGINAL_NAME = 'Table name ')
AND ORIGINAL_NAME! = 'Table name';
After the table is flashed-back, all the associated objects will be reinstated to their name shown in ‘Object_Name’ column.
Then you can rename them to their original names using Alter command.
Exceptions:
• Bitmap Indexes: Once they are dropped, they are not placed in recycle bin and hence not retrievable.
• Materialized View Logs: when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.
• Referential integrity constraints: that reference another table are lost when the table is put in the recyclebin and then restored.
• Constraint Names: are also not retrievable from the view.
Other uses of Flashback Tables
Recycle bin can also be used to reinstate the Table to a different point in time, replacing the current table to a different version in past. e.g. following example reinstates current table to a System Change Number (SCN) 2390874650.
Flashback table test to SCN 2390874650;
This feature uses Oracle Data Pump technology to create a different table, uses flashback to populate the table with the versions of the data at that SCN, and then replaces the original table with the new table.
Conclusion
In this document we have covered some useful features of Recylcebin in Oracle.
To Sum up:
• Recycle bin maintains the version of dropped objects and reinstate them in LIFO order.
• Flashing back a table reinstates the table to its original name, but all the associated objects have to be renamed to their original name.
• Even if the recyclebin parameter is turned off, objects already present in recyclebin can be reinstated.
References:
1. http://www.orafaq.com/node/968
2. http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html
Subscribe to:
Posts (Atom)