You can do it at the OS level by deleting all the files of the database.
The files to be deleted can be found using:
1) select * from dba_data_files;
2) select * from v$logfile;
3) select * from v$controlfile;
4) archive log list
5) initSID.ora
6) In addition you can clean the UDUMP, BDUMP, scripts etc
Clean up the listener.ora and the tnsnames.ora.
Make sure that the oratab entry is also removed.
With dbca you can easily drop an oracle database.
However you may wish manually to delete a database. The following steps will help you to manually drop a database.
1)Set the Oracle SID of the database which you interested to drop.
On my system it is,
$export ORACLE_SID=dbase
2)Connect to SQL*Plus as SYSDBA privilege.
$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue May 13 02:41:24 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
3)Mount the database.
SQL> startup force mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 92274728 bytes
Database Buffers 67108864 bytes
Redo Buffers 6369280 bytes
Database mounted.
4)Enable restricted session.
SQL> alter system enable RESTRICTED session;
System altered.
5)Drop the database.
SQL> drop database;
Database dropped.
Showing posts with label Db Install. Show all posts
Showing posts with label Db Install. Show all posts
Friday, November 28, 2008
Monday, October 6, 2008
Upgrade Oracle Database 9.2.0.1 to Oracle Database 9.2.0.6 (patchset 3948480)
Yesterday I have Completed Platform migration on payroll database. While doing the migration, I have upgraded Oracle Database 9.2.0.1 to Oracle Database 9.2.0.6
1. Log in to the Oracle 9.2.0.1 Database server machine as the oracle user (administrator role in case of windows) on Unix.
2. Make sure that your environment contains the correct values for the ORACLE_HOME and ORACLE_SID variables.
3. Shut down any existing Oracle9i database instances with normal or immediate priority As the oracle user:
sqlplus /nolog
SQL> connect / as sysdba
SQL>shutdown immediate
4. Download the patchset & Extract the 9.2.0.6 patch set (3948480 into Stage directory.
5. Install the 9.2.0.6 patch set
cd /Stage/3948480/Disk1/
Run Setup.exe
Welcome
Next ->
Specify File Locations
Source Path: choose default
Destination Name: ORACLE_HOME (select from dropdown list, which databse you want to upgrade)
Destination Path: /oracle/products/payroll
Next ->
Summary
Install ->
End of Installation
Exit
Pre Installation Steps before Database Upgrade
1. If JServer is part of the installation, ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.
2. For RAC installations, ensure that there is at least 50 MB of free space allocated to the XDB tablespace.
3. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
1. Start the database:
2. SQL> STARTUP
3. If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):
SQL> SHOW PARAMETER PFILE;
This command displays the name and location of the server parameter file or the initialization parameter file.
Note:The UF installation uses an spfile (Oracle9i Database will automatically use the ORACLE_HOME/dbs/spfilewebct.ora file if it exists), but we want to make sure that the pfile and the file match up to reduce confusion.
4. Determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
5. If the system is using a server parameter file:
a. If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 250 MB:
b. SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='250M' SCOPE=spfile;
c.
d. If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 250 MB:
e. SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='250M' SCOPE=spfile;
6. If the system uses an initialization parameter file, if necessary, change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 250 MB in the initialization parameter file (initsid.ora).
7. Shut down the database:
SQL> SHUTDOWN
Upgrading the Database
Perform the following steps on every database associated with the upgraded Oracle home:
1. Log in as the oracle user:
sqlplus /nolog
SQL> connect / as sysdba
2. Enter the following SQL*Plus commands:
SQL> startup migrate
SQL> spool patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> spool off
3. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.
4.list provides the version and status of each SERVER component in the database. If necessary, rerun the catpatch.sql script after correcting any problems.
5. Restart the database:
SQL> shutdown immediate
SQL> startup
5. Execute the following script to recompile all invalid PL/SQL packages now. (This step is optional but recommended.)
SQL> @?/rdbms/admin/utlrp.sql
6. Restart the listener & other processes
1. Log in to the Oracle 9.2.0.1 Database server machine as the oracle user (administrator role in case of windows) on Unix.
2. Make sure that your environment contains the correct values for the ORACLE_HOME and ORACLE_SID variables.
3. Shut down any existing Oracle9i database instances with normal or immediate priority As the oracle user:
sqlplus /nolog
SQL> connect / as sysdba
SQL>shutdown immediate
4. Download the patchset & Extract the 9.2.0.6 patch set (3948480 into Stage directory.
5. Install the 9.2.0.6 patch set
cd /Stage/3948480/Disk1/
Run Setup.exe
Welcome
Next ->
Specify File Locations
Source Path: choose default
Destination Name: ORACLE_HOME (select from dropdown list, which databse you want to upgrade)
Destination Path: /oracle/products/payroll
Next ->
Summary
Install ->
End of Installation
Exit
Pre Installation Steps before Database Upgrade
1. If JServer is part of the installation, ensure that there is at least 10 MB of free space allocated to the SYSTEM tablespace.
2. For RAC installations, ensure that there is at least 50 MB of free space allocated to the XDB tablespace.
3. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
1. Start the database:
2. SQL> STARTUP
3. If necessary, enter the following command to determine whether the system uses an initialization parameter file (initsid.ora) or a server parameter file (spfiledbname.ora):
SQL> SHOW PARAMETER PFILE;
This command displays the name and location of the server parameter file or the initialization parameter file.
Note:The UF installation uses an spfile (Oracle9i Database will automatically use the ORACLE_HOME/dbs/spfilewebct.ora file if it exists), but we want to make sure that the pfile and the file match up to reduce confusion.
4. Determine the current values of these parameters:
SQL> SHOW PARAMETER SHARED_POOL_SIZE
SQL> SHOW PARAMETER JAVA_POOL_SIZE
5. If the system is using a server parameter file:
a. If necessary, set the value of the SHARED_POOL_SIZE initialization parameter to at least 250 MB:
b. SQL> ALTER SYSTEM SET SHARED_POOL_SIZE='250M' SCOPE=spfile;
c.
d. If necessary, set the value of the JAVA_POOL_SIZE initialization parameter to at least 250 MB:
e. SQL> ALTER SYSTEM SET JAVA_POOL_SIZE='250M' SCOPE=spfile;
6. If the system uses an initialization parameter file, if necessary, change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 250 MB in the initialization parameter file (initsid.ora).
7. Shut down the database:
SQL> SHUTDOWN
Upgrading the Database
Perform the following steps on every database associated with the upgraded Oracle home:
1. Log in as the oracle user:
sqlplus /nolog
SQL> connect / as sysdba
2. Enter the following SQL*Plus commands:
SQL> startup migrate
SQL> spool patch.log
SQL> @?/rdbms/admin/catpatch.sql
SQL> spool off
3. Review the patch.log file for errors and inspect the list of components that is displayed at the end of catpatch.sql script.
4.list provides the version and status of each SERVER component in the database. If necessary, rerun the catpatch.sql script after correcting any problems.
5. Restart the database:
SQL> shutdown immediate
SQL> startup
5. Execute the following script to recompile all invalid PL/SQL packages now. (This step is optional but recommended.)
SQL> @?/rdbms/admin/utlrp.sql
6. Restart the listener & other processes
Subscribe to:
Posts (Atom)