Uninstalling Oracle 10g Manually from Windows XP/Windows 2003 Server
I have uninstalled Oracle 10g manually for my Windows XP and Windows 2003 server. Here are the steps bellow:
Step 1:
Use Oracle Universal installer to deinstall Oracle.
Follow the steps and select correct oracle home to remove it.
Step 2:
After running the supplied Oracle uninstallation utility (which may or may not do some or all of the following):
• Stop any Oracle services that have been left running.
Start->Settings->Control Panel->Services
Look for any services with names starting with 'Oracle' and stop them.
Step 3:
Take the backup of your registry in case you accidentally delete other thing (wrongly delete any entry in registry might let to reinstall whole server.)
In DOS command prompt type regedit
Move the icon in my computer then file-export
Step 4:
• Run regedit and delete the following keys (some may have slightly different names in your registry):
HKEY_CURRENT_USER\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application\Oracle.oracle
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleDBConsole
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle10g_home
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OraclService
Step 5:
Reboot the system.
• Delete the Oracle home directory
C:\Oracle
• Delete the Oracle Program Files directory:
C:\Program Files\Oracle
• Delete the Oracle Start Menu shortcuts directory:
C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*
Where * indicates the name of your install. Look for and remove all Oracle directories from that location.
Step 6:
• Remove Oracle refereces from the path. To edit your path go to:
Start->Settings->Control Panel->System->Advanced->Environment Variables
Edit both of the environment variables user PATH and system PATH. Remove any Oracle references in them.
• Remove Oracle.DataAccess and any Polic.Oracle files from the GAC which is at:
C:\Windows\assembly\
Now you can install Oracle 10g.
Tuesday, September 30, 2008
Monday, September 29, 2008
How to release dead lock in Oracle
1. Find out who is locking in Deadlock scenario
SQL> select (select username from v$session where sid=a.sid) blocker,
a.sid, ' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1 and b.request > 0
and a.id1 = b.id1 and a.id2 = b.id2
/BLOCKER SID 'ISBLOCKING' BLOCKEE SID------------------------------ ----------ORAUSER 94 is blocking OPIS 1032.
2. Find out SID and Serial#
SQL> SELECT s.sid, s.serial#, s.osuser,FROM v$session swhere s.sid=94SQL> SID SERIAL# OSUSER PROGRAM---------- ---------- -------------------------------94 39897 mross3.
Kill the session:
SQL> alter system kill session '94,39897';
System altered.
SQL> select (select username from v$session where sid=a.sid) blocker,
a.sid, ' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1 and b.request > 0
and a.id1 = b.id1 and a.id2 = b.id2
/BLOCKER SID 'ISBLOCKING' BLOCKEE SID------------------------------ ----------ORAUSER 94 is blocking OPIS 1032.
2. Find out SID and Serial#
SQL> SELECT s.sid, s.serial#, s.osuser,FROM v$session swhere s.sid=94SQL> SID SERIAL# OSUSER PROGRAM---------- ---------- -------------------------------94 39897 mross3.
Kill the session:
SQL> alter system kill session '94,39897';
System altered.
Friday, September 19, 2008
Upgrading Oracle Database Server 10g Release 10.2.0.1 to Oracle Database 10g Release 10.2.0.3 on Windows 2003
Upgrading Oracle Database Server 10g Release 10.2.0.1 to Oracle Database 10g Release 10.2.0.3 on Windows 2003
We had recently upgraded one of our test databases from 10.2.0.1 to 10.2.0.3 on Windows 2003.Details:
Operating Version Details Windows 2003 Standard EditionOracle Version Details (Current) : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionOracle
Instance Name: tom
Patchset (to be applied on) : p5337014_10203_WINNT.zip (Downloaded from http://www.metalink.oracle.com/)
Oracle Universal Installer Version RequirementsThe Oracle Universal Installer release must be 10.1.0.3 (This includes in the Patchset).
System Requirements* Oracle Database 10g (10.2.0.1) or laterPreinstallation TasksTo download and extract the patch set installation software:* Download the p5337014_10203_WINNT.zip patch set.
Set the ORACLE_HOME and ORACLE_SID Environment Variables
C:\> set ORACLE_HOME= D:\oracle\product\10.2.0\db_1
c:\> set ORACLE_SID=TOMThe oracle_home is the Oracle home directory where the
Oracle10g installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.
Stopping All Processes for a Single Instance Installation
**To stop Listener ServiceC:\lsnrctlLSNRCTL> stop
** To stop Enterprise Manager ServiceC:\ emctl stop dbconsole
** To stop iSQLPlus ServiceC:\ isqlplusctl stop**
To Shut Down Oracle DatabasesC:\ sqlplus /nolog
C:\ connect /as sysdbaSQL> SHUTDOWN IMMEDIATE;
Back Up the SystemOracle recommends that you create a backup of the Oracle10g installation before you install the patch set.We took the backup of Oracle home and all the database files.
Installation TasksInstalling the Oracle Database 10g Patch Set Interactively
** Log in as AdministratorGo to unzipped location of p5337014_10203_WINNT.zip file** To start Oracle Universal Installer, where patchset_directory is the directory where you unzipped the patch set softwareD:\ patchset_directory\patch\Disk1
Dubble click setup.exe
** On the Welcome screen, click Next.
** In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.** On the Summary screen, click Install.This screen lists all of the patches available for installation.** On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.Postinstallation TasksUpgrading of Oracle 10g Software from Release 10.2.0.1 to 10.2.0.3 is now successfully done. Now, earlier Oracle Database needs to be upgraded from 10.2.0.1 to 10.2.0.3. Oracle recommends two ways to upgrade the database, one is using DBUA (Database Upgrade Assistance), and another is Manual database upgrade. Here we have followed manual method for upgrade.
Upgrading a Release 10.2 Database using DBUA (Oracle Database Upgrade Assistant)
After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:
Note:
If you do not run the Oracle Database Upgrade Assistant script as described in this section, then the following errors are displayed:
ORA-01092: ORACLE instance terminated.
ORA-39700: database must be opened with UPGRADE option.
Run Oracle Database Upgrade Assistant either in the interactive or noninteractive mode:
Interactive mode:
1. Start the Oracle Database Upgrade Assistant:
From the Start menu, select Programs, then Oracle - HOME_NAME,
then Configuration and Migration Tools and then Database Upgrade Assistant to start the Oracle Database Upgrade Assistant.
Complete the following steps displayed in the Oracle Database Upgrade Assistant screen:
2. On the Welcome screen, click Next.
3. On the Databases screen, select the name of the Oracle Database that you want to update, then click Next.
4. On the Recompile invalid objects screen, select the Recompile the invalid objects at the end of upgrade option, then click Next.
5. If you have not taken the back up of the database earlier, on the Backup screen, select the I would like to take this tool to backup the database option, mention the Path, then click Next.
6. On the Summary screen, check the summary, then click Finish.
7. On the End of upgradation screen, click Exit, then click Yes to exit from Oracle Database Upgrade Assistant.
## Start All Oracle Related Services
## To Check Current Oracle Version
SQL> select * from version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Manually Upgrading a Release 10.2 Database
* Required Preupgrade Checks**
Check the SYSTEM Tablespace Size
SQL> SELECT sum(bytes/1024/1024) "Size in MB"2 FROM dba_free_space3 WHERE tablespace_name='SYSTEM';
Size in MB----------97.125
We have enough free space in SYSTEM tablespace.I
f not enough free space in SYSTEM tablespace, then add/resize the datafile as below.
## TO add a datafileALTER TABLESPACE SYSTEMADD ' D:\oracle\product\10.2.0\oradata\TOM\ SYSTEM01.DBF 'SIZE 100M;
## To resize a existing datafile.ALTER DATABASE DATAFILE ' D:\oracle\product\10.2.0\oradata\TOM\ SYSTEM01.DBF' RESIZE 500M;
* Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
##Start the database with the NOMOUNT option:
SQL> STARTUP NOMOUNT
## To check initialization parameter file (initsid.ora or spfilesid.ora)
SQL> SHOW PARAMETER PFILE;
## To determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
##If the system is using a server parameter file:If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;
If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;
##If the system uses an initialization parameter fileIf necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
Shut down the database:
SQL> SHUTDOWN
Upgrading a Release 10.2 Database
##Login as Windows Administrator
##Start the Listener
C:\lsnrctlLSNRCTL> START
##Connect to sqlplus as sysdba and start the database in upgrade mode
C:\sqlplus /nologSQL> connect /as sysdba
SQL> STARTUP UPGRADE;
##Run the catupgrd.sql scrips
SQL> SPOOL patch.log
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catupgrd.sql
SQL> SPOOL OFF;
Review the patch.log file for errors and inspect the list of components that is displayed at the
end of catupgrd.sql script.
## Restart the database:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql
Start All Oracle Related Services
To Check Current Oracle Version
SQL> select * from version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Common Errors:
** If you attempt to install this patch set in an Oracle home directory that does not contain an Oracle Database 10g release 10.2.0.1 or 10.2.0.2 installation, Oracle Universal Installer displays a warning dialog with the following error:
OUI-10091: There are no patches that need to be applied from the patch set Oracle Database 10g Release 2 Patch Set 210.2.0.3
The Oracle Universal Installer does not allow the installation to proceed. Click OK, then click Cancel to end the installation.
** If you do not run the Oracle Database Upgrade Assistant as described in this section, then the following errors are displayed:ORA-01092: ORACLE instance terminated.ORA-39700: database must be opened with UPGRADE option.
C:\sqlplus /nologSQL> connect /as sysdbaSQL> STARTUP UPGRADE;
We had recently upgraded one of our test databases from 10.2.0.1 to 10.2.0.3 on Windows 2003.Details:
Operating Version Details Windows 2003 Standard EditionOracle Version Details (Current) : Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionOracle
Instance Name: tom
Patchset (to be applied on) : p5337014_10203_WINNT.zip (Downloaded from http://www.metalink.oracle.com/)
Oracle Universal Installer Version RequirementsThe Oracle Universal Installer release must be 10.1.0.3 (This includes in the Patchset).
System Requirements* Oracle Database 10g (10.2.0.1) or laterPreinstallation TasksTo download and extract the patch set installation software:* Download the p5337014_10203_WINNT.zip patch set.
Set the ORACLE_HOME and ORACLE_SID Environment Variables
C:\> set ORACLE_HOME= D:\oracle\product\10.2.0\db_1
c:\> set ORACLE_SID=TOMThe oracle_home is the Oracle home directory where the
Oracle10g installation that you want to upgrade is installed, and sid is the SID of the database that you want to upgrade.
Stopping All Processes for a Single Instance Installation
**To stop Listener ServiceC:\lsnrctlLSNRCTL> stop
** To stop Enterprise Manager ServiceC:\ emctl stop dbconsole
** To stop iSQLPlus ServiceC:\ isqlplusctl stop**
To Shut Down Oracle DatabasesC:\ sqlplus /nolog
C:\ connect /as sysdbaSQL> SHUTDOWN IMMEDIATE;
Back Up the SystemOracle recommends that you create a backup of the Oracle10g installation before you install the patch set.We took the backup of Oracle home and all the database files.
Installation TasksInstalling the Oracle Database 10g Patch Set Interactively
** Log in as AdministratorGo to unzipped location of p5337014_10203_WINNT.zip file** To start Oracle Universal Installer, where patchset_directory is the directory where you unzipped the patch set softwareD:\ patchset_directory\patch\Disk1
Dubble click setup.exe
** On the Welcome screen, click Next.
** In the Specify Home Details screen, select the name of the Oracle home that you want to update, or select the Path that you want to update from the list, then click Next.** On the Summary screen, click Install.This screen lists all of the patches available for installation.** On the End of Installation screen, click Exit, then click Yes to exit from Oracle Universal Installer.Postinstallation TasksUpgrading of Oracle 10g Software from Release 10.2.0.1 to 10.2.0.3 is now successfully done. Now, earlier Oracle Database needs to be upgraded from 10.2.0.1 to 10.2.0.3. Oracle recommends two ways to upgrade the database, one is using DBUA (Database Upgrade Assistance), and another is Manual database upgrade. Here we have followed manual method for upgrade.
Upgrading a Release 10.2 Database using DBUA (Oracle Database Upgrade Assistant)
After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:
Note:
If you do not run the Oracle Database Upgrade Assistant script as described in this section, then the following errors are displayed:
ORA-01092: ORACLE instance terminated.
ORA-39700: database must be opened with UPGRADE option.
Run Oracle Database Upgrade Assistant either in the interactive or noninteractive mode:
Interactive mode:
1. Start the Oracle Database Upgrade Assistant:
From the Start menu, select Programs, then Oracle - HOME_NAME,
then Configuration and Migration Tools and then Database Upgrade Assistant to start the Oracle Database Upgrade Assistant.
Complete the following steps displayed in the Oracle Database Upgrade Assistant screen:
2. On the Welcome screen, click Next.
3. On the Databases screen, select the name of the Oracle Database that you want to update, then click Next.
4. On the Recompile invalid objects screen, select the Recompile the invalid objects at the end of upgrade option, then click Next.
5. If you have not taken the back up of the database earlier, on the Backup screen, select the I would like to take this tool to backup the database option, mention the Path, then click Next.
6. On the Summary screen, check the summary, then click Finish.
7. On the End of upgradation screen, click Exit, then click Yes to exit from Oracle Database Upgrade Assistant.
## Start All Oracle Related Services
## To Check Current Oracle Version
SQL> select * from version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Manually Upgrading a Release 10.2 Database
* Required Preupgrade Checks**
Check the SYSTEM Tablespace Size
SQL> SELECT sum(bytes/1024/1024) "Size in MB"2 FROM dba_free_space3 WHERE tablespace_name='SYSTEM';
Size in MB----------97.125
We have enough free space in SYSTEM tablespace.I
f not enough free space in SYSTEM tablespace, then add/resize the datafile as below.
## TO add a datafileALTER TABLESPACE SYSTEMADD ' D:\oracle\product\10.2.0\oradata\TOM\ SYSTEM01.DBF 'SIZE 100M;
## To resize a existing datafile.ALTER DATABASE DATAFILE ' D:\oracle\product\10.2.0\oradata\TOM\ SYSTEM01.DBF' RESIZE 500M;
* Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters
##Start the database with the NOMOUNT option:
SQL> STARTUP NOMOUNT
## To check initialization parameter file (initsid.ora or spfilesid.ora)
SQL> SHOW PARAMETER PFILE;
## To determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
##If the system is using a server parameter file:If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='150M' SCOPE=spfile;
If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 150 MB:
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='150M' SCOPE=spfile;
##If the system uses an initialization parameter fileIf necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
Shut down the database:
SQL> SHUTDOWN
Upgrading a Release 10.2 Database
##Login as Windows Administrator
##Start the Listener
C:\lsnrctlLSNRCTL> START
##Connect to sqlplus as sysdba and start the database in upgrade mode
C:\sqlplus /nologSQL> connect /as sysdba
SQL> STARTUP UPGRADE;
##Run the catupgrd.sql scrips
SQL> SPOOL patch.log
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catupgrd.sql
SQL> SPOOL OFF;
Review the patch.log file for errors and inspect the list of components that is displayed at the
end of catupgrd.sql script.
## Restart the database:
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.
SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql
Start All Oracle Related Services
To Check Current Oracle Version
SQL> select * from version;BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Common Errors:
** If you attempt to install this patch set in an Oracle home directory that does not contain an Oracle Database 10g release 10.2.0.1 or 10.2.0.2 installation, Oracle Universal Installer displays a warning dialog with the following error:
OUI-10091: There are no patches that need to be applied from the patch set Oracle Database 10g Release 2 Patch Set 210.2.0.3
The Oracle Universal Installer does not allow the installation to proceed. Click OK, then click Cancel to end the installation.
** If you do not run the Oracle Database Upgrade Assistant as described in this section, then the following errors are displayed:ORA-01092: ORACLE instance terminated.ORA-39700: database must be opened with UPGRADE option.
C:\sqlplus /nologSQL> connect /as sysdbaSQL> STARTUP UPGRADE;
Saturday, June 14, 2008
How to Set Up a Recovery Catalog
How to Set Up a Recovery Catalog
Create the database to be used as the recovery catalog. Alternatively, identify an already
existing database and use that. Because a single recovery catalog can serve more than
one database, you may choose to create a single recovery catalog that services all your
databases that are being backed up.
1. Create table space RMAN catalog if it’s not there
SQL>
1 create tablespace rman_cat
2 datafile 'D:\oracle\product\10.2.0\oradata\pom\rman.DBF'
3 size 10m autoextend on next 10m maxsize 2000m
4* extent management local autoallocate segment space management auto
SQL> /
Tablespace created.
2. In the recovery catalog database, create the user that will own the recovery catalog data.
For example, assume that catdb is the name of the catalog database, and rman_cat is
the name of a tablespace you have created where the catalog data is to be stored.
SQL>
1 create user rman identified by cat
2 temporary tablespace temp
3 default tablespace rman_cat
4* quota unlimited on rman_cat
SQL> /
User created.
2. Grant the RECOVERY_CATALOG_OWNER role to the catalog owner. This role provides
the user with all privileges required to maintain and query the recovery catalog.
SQL> GRANT RECOVERY_CATALOG_OWNER TO RMAN;
Grant succeeded.
4. Start RMAN and log in as the catalog owner.
d:\opis2005\ADMIN\Scripts\COM ( POM )RMAN CATALOG RMAN/CAT
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 10 14:56:43 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> CREATE CATALOG TABLESPACE RMAN_CAT;
recovery catalog created
RMAN> EXIT
Recovery Manager complete.
5. Connect to the target database by using RMAN, and register it in the newly created
recovery catalog.
d:\opis2005\ADMIN\Scripts\COM ( POM )RMAN TARGET / CATALOG RMAN/CAT
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 10 14:58:52 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: POM (DBID=1104548899)
connected to recovery catalog database
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Create the database to be used as the recovery catalog. Alternatively, identify an already
existing database and use that. Because a single recovery catalog can serve more than
one database, you may choose to create a single recovery catalog that services all your
databases that are being backed up.
1. Create table space RMAN catalog if it’s not there
SQL>
1 create tablespace rman_cat
2 datafile 'D:\oracle\product\10.2.0\oradata\pom\rman.DBF'
3 size 10m autoextend on next 10m maxsize 2000m
4* extent management local autoallocate segment space management auto
SQL> /
Tablespace created.
2. In the recovery catalog database, create the user that will own the recovery catalog data.
For example, assume that catdb is the name of the catalog database, and rman_cat is
the name of a tablespace you have created where the catalog data is to be stored.
SQL>
1 create user rman identified by cat
2 temporary tablespace temp
3 default tablespace rman_cat
4* quota unlimited on rman_cat
SQL> /
User created.
2. Grant the RECOVERY_CATALOG_OWNER role to the catalog owner. This role provides
the user with all privileges required to maintain and query the recovery catalog.
SQL> GRANT RECOVERY_CATALOG_OWNER TO RMAN;
Grant succeeded.
4. Start RMAN and log in as the catalog owner.
d:\opis2005\ADMIN\Scripts\COM ( POM )RMAN CATALOG RMAN/CAT
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 10 14:56:43 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> CREATE CATALOG TABLESPACE RMAN_CAT;
recovery catalog created
RMAN> EXIT
Recovery Manager complete.
5. Connect to the target database by using RMAN, and register it in the newly created
recovery catalog.
d:\opis2005\ADMIN\Scripts\COM ( POM )RMAN TARGET / CATALOG RMAN/CAT
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jun 10 14:58:52 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: POM (DBID=1104548899)
connected to recovery catalog database
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
How to see what is committed in table (in past)
Here is the query to see the result of 2 days before. Interval might be day, hour, minute, second
etc.
SELECT * FROM Table1
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' day)
WHERE site_code = 'ITACS';
etc.
SELECT * FROM Table1
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '2' day)
WHERE site_code = 'ITACS';
Saturday, May 24, 2008
How to recover Database by SCN number (incomplete recovery) -RMAN
• Log sequence recovery
1. Mount the database.
2. Restore all data files.
3. Recover the database by using UNTIL SCN
4. Open the database by using RESETLOGS.
Know last SCN no.
SQL> select first_change#,status,group# from v$log;
FIRST_CHANGE# STATUS GROUP#
------------- ---------------- ----------
8793770 CURRENT 1
8785734 ACTIVE 2
8793669 ACTIVE 3
The FIRST_CHANGE# is the first SCN stamped in the missing log. This implies that the last
SCN stamped in the previous log is 8793769 (FIRST_CHANGE#-1). This is the highest SCN
that we can recover to. In order to do the recovery we must first restore ALL datafiles to this
SCN, followed by recovery (also up to this SCN).
RMAN> shutdown immediate;
using target database control file instead of re
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 683671552 bytes
Fixed Size 1250836 bytes
Variable Size 125831660 bytes
Database Buffers 549453824 bytes
Redo Buffers 7135232 bytes
RMAN> restore database until scn 8793769;
……
channel ORA_DISK_1: restore complete, elapsed time: 00:02:17
Finished restore at 23-MAY-08
RMAN> recover database until scn 8793769;
……
media recovery complete, elapsed time: 00:00:16
Finished recover at 23-MAY-08
RMAN> alter database open resetlogs;
database opened
1. Mount the database.
2. Restore all data files.
3. Recover the database by using UNTIL SCN
4. Open the database by using RESETLOGS.
Know last SCN no.
SQL> select first_change#,status,group# from v$log;
FIRST_CHANGE# STATUS GROUP#
------------- ---------------- ----------
8793770 CURRENT 1
8785734 ACTIVE 2
8793669 ACTIVE 3
The FIRST_CHANGE# is the first SCN stamped in the missing log. This implies that the last
SCN stamped in the previous log is 8793769 (FIRST_CHANGE#-1). This is the highest SCN
that we can recover to. In order to do the recovery we must first restore ALL datafiles to this
SCN, followed by recovery (also up to this SCN).
RMAN> shutdown immediate;
using target database control file instead of re
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 683671552 bytes
Fixed Size 1250836 bytes
Variable Size 125831660 bytes
Database Buffers 549453824 bytes
Redo Buffers 7135232 bytes
RMAN> restore database until scn 8793769;
……
channel ORA_DISK_1: restore complete, elapsed time: 00:02:17
Finished restore at 23-MAY-08
RMAN> recover database until scn 8793769;
……
media recovery complete, elapsed time: 00:00:16
Finished recover at 23-MAY-08
RMAN> alter database open resetlogs;
database opened
Saturday, May 17, 2008
Limit exceeded for recovery files.
SQL> startup;
ORACLE instance started.
Total System Global Area 364904448 bytes
Fixed Size 778880 bytes
Variable Size 116137344 bytes
Database Buffers 247463936 bytes
Redo Buffers 524288 bytes
Database mounted.
ORA-16038: log 3 sequence# 693 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/u01/oradata/erprod/erprod/redo03.log'
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 10g;
...
SQL> shutdown immediate;
...
SQL> startup;
...no error message this time...
ORACLE instance started.
Total System Global Area 364904448 bytes
Fixed Size 778880 bytes
Variable Size 116137344 bytes
Database Buffers 247463936 bytes
Redo Buffers 524288 bytes
Database mounted.
ORA-16038: log 3 sequence# 693 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/u01/oradata/erprod/erprod/redo03.log'
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 10g;
...
SQL> shutdown immediate;
...
SQL> startup;
...no error message this time...
How to change Archive log Mode
SQL> startup mount;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 226495364 bytes
Database Buffers 377487360 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 226495364 bytes
Database Buffers 377487360 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
How to recover a dropped table from Recycle bin
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE 2004-02-16:21:13:31
This shows the original name of the table, RECYCLETEST, as well as the new name in the recycle bin, which has the same name as the new table we saw created after the drop. (Note: the exact name may differ by platform.) To reinstate the table, all you have to do is use the FLASHBACK TABLE command:
SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;
FLASHBACK COMPLETE.
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE 2004-02-16:21:13:31
This shows the original name of the table, RECYCLETEST, as well as the new name in the recycle bin, which has the same name as the new table we saw created after the drop. (Note: the exact name may differ by platform.) To reinstate the table, all you have to do is use the FLASHBACK TABLE command:
SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;
FLASHBACK COMPLETE.
Subscribe to:
Posts (Atom)