Tutorial on manually deleting a database in Oracle

Tutorial on manually deleting a database in Oracle

1. manually delete a file system database

1. Stop listening and OEM

The Code is as follows:

$ Lsnrctl stop listener_name

$ Emctl stop dbconsole

2. Obtain information about data files, log files, and control files, including archiving

The Code is as follows:

$ Sqlplus/as sysdba

SQL> select status, name from v $ controlfile; -- get the location information of the Control File

SQL> select * from v $ dbfile; -- obtains the location information of the data file.

SQL> select * from v $ logfile; -- get the location information of the log file

SQL> archive log list; -- view the archived sequence and location information

SQL> shutdown abort; -- directly shutdown abort because data is no longer needed

3. Start to mount (use exclusive restart)

The Code is as follows:

SQL> startup mount exclusive restrict;

4. Modify the parameter to allow restricted session mode.

The Code is as follows:

SQL> alter system enable restricted session;

5. Run the drop database Command to clear the database.

The Code is as follows:

SQL> drop database;

SQL> exit

6. manually clear Related Files

The Code is as follows:

$ Echo $ ORACLE_SID -- confirm the current ORACLE_SID

$ Rm-rf $ ORACLE_BASE/admin/$ ORACLE_SID -- delete all content of $ ORACLE_SID in the oracle base Directory

$ Rm-rf $ ORACLE_BASE/oradata/$ ORACLE_SID -- data files under oradata. Based on the preceding query, delete data files in different paths.

$ Rm-rf $ ORACLE_BASE/arch/$ ORACLE_SID -- clear archiving logs and ensure that the archiving path is correct.

$ Rm-rf $ ORACLE_BASE/flash_recovery_area/$ ORACLE_SID -- clear the content of the flash back area

$ Rm-rf $ ORACLE_HOME/dbs/* $ ORACLE_SID * -- clear the parameter file */

7. Clear listener-related files (recommended)

The Code is as follows:

$ Rm $ TNS_ADMIN/*. ora -- if the $ TNS_ADMIN variable is set, delete the listener file in the default path */

$ Rm $ ORACLE_HOME/network/admin/*. ora */

8. Modify the oratab file and. bash_profile

The Code is as follows:

$ Vi/etc/oratab -- remove instance-related settings

$ Vi ~ /. Bash_profile -- remove instance-related settings

Ii. Clear the ASM Database

The difference between the deletion of the ASM database and the file system database is that the data files, control files, log files, and parameter files are all stored in the ASM system. Therefore, you need to make the appropriate settings in the ASM instance.

To completely clear the database.

1. Execute Step 3-4 to clear the file system database (shutdown first)

2. Run the drop database Command to clear the database (this command will clear data files, log files, temp files)

The Code is as follows:

SQL> drop database;

SQL> exit

3. Connect to the ASM instance

The Code is as follows:

$ Export ORACLE_SID = + ASM

$ Sqlplus/as sysdba

4. Clear residual files

The Code is as follows:

SQL> select name, file_number, group_number, file_incarnation from v $ asm_alias; -- view the file. Note that group_number indicates that it is located in different disk groups.

SQL> alter diskgroup DG1 drop directory '+ DG1/asmdb/file_name'; -- you can use this method to delete files to be deleted.

SQL> alter diskgroup REV drop directory '+ REV/asmdb/file_name ';

SQL> select name, file_number, group_number, file_incarnation from v $ asm_alias; -- verify the deleted result

You can also use the following method (asmcmd command) to delete the residual files.

The Code is as follows:

$ Export ORACLE_SID = + ASM

$ Asmcmd

ASMCMD> ls-l

State Type Rebal Unbal Name

Mounted extern n DG1/

Mounted extern n rev/

ASMCMD> ls-s

Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name

512 4096 1048576 3067 2974 0 2974 0 DG1/

512 4096 1048576 2047 1996 0 1996 0 REV/

ASMCMD> cd + DG1

ASMCMD> ls

ASMDB/

ASMCMD> rm-rf ASMDB

ASMCMD> ls

ASMCMD> cd + REV

ASMCMD> pwd

+ REV

ASMCMD> ls

ASMCMD> cd +

ASMCMD> ls-s

Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name

512 4096 1048576 3067 3016 0 3016 0 DG1/

512 4096 1048576 2047 1996 0 1996 0 REV/

You can also use the rm command to directly delete files directly located in a disk group.

5. Clear other files

Although data files, archived logs, online logs, temporary files, and parameter files are stored on the ASM disk, the dump files and alarm logs are still on the disk and need to be cleared manually

The Code is as follows:

$ Echo $ ORACLE_SID -- confirm the current ORACLE_SID

$ Rm-rf $ ORACLE_BASE/admin/$ ORACLE_SID -- delete all content of $ ORACLE_SID in the oracle base Directory

$ Rm-rf $ ORACLE_HOME/dbs/* $ ORACLE_SID * -- clear the parameter file */

6. Execute steps 7-8 to clear the file system database