Tag Archives: command

dbca, utility

Remove Oracle Database with DBCA from command line

Leave a comment

The following procedure shows how to remove an Oracle database with dbca utility from the command line.

1. Logon as the oracle software owner.

[root@mylinux2 root]# su – oracle
[oracle@mylinux2 ~]$

2. Change directories to $ORACLE_HOME/bin

[oracle@mylinux2 ~]$ cd $ORACLE_HOME/bin
[oracle@mylinux2 bin]$

3. Obtain the ORACLE_SID of the database you wish to delete.

[oracle@mylinux2 bin]$ ps -ef|grep smon
oracle 4746 1 0 Feb09 ? 00:00:00 ora_smon_orcl
oracle 7839 7796 0 00:39 pts/1 00:00:00 grep smon
[oracle@mylinux2 bin]$

4. Obtain the userid and password of a user with sysdba privileges, normally this would be sys.

5. Logon to the database if open with the immediate option and exit.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@mylinux2 bin]$

6. Ensure that no oracle database processes are running for the ORACLE_SID you wish to delete


[oracle@mylinux2 bin]$ ps -ef|grep orcl
oracle 11485 7796 0 01:46 pts/1 00:00:00 grep orcl
[oracle@mylinux2 bin]$

7. Execute the command dbca -deleteDatabase -sourceDB -sysDBAUserName -sysDBAPassword


[oracle@mylinux2 bin]$ ./dbca -silent -deleteDatabase -sourceDB orcl – sysDBAUserName larry -sysDBAPassword larry
Connecting to database
4% complete
9% complete
14% complete
19% complete
23% complete
28% complete
47% complete
Updating network configuration files
48% complete
52% complete
Deleting instance and datafiles
76% complete
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/orcl.log” for
further details.
[oracle@mylinux2 bin]$

This completes deleting an oracle database from command line with dbca.

Larry J. Catt, OCP
oracle@allcompute.com
www.allcompute.com

11g, hp-ux, itanium, linux, Oracle, Oracle 10g, oracle 9i, SQL, unix

Execution of Oracle SQL commands from within UNIX or LINUX shell scripts:

Leave a comment

In the management of Oracle RDBMS, we frequently have the need to develop shell scripts to manage our databases. This article demonstrates the use of BASH shell script to connect to an Oracle database and execute PL/SQL and SQL statements from within the Oracle database. The following procedure will work on UNIX and LINUX machines.

1. Logon to your Oracle database server as the Oracle software owner.

2. Use vi to create a new file with the name sql_shell_test.sh

mylinux:> vi sql_shell_test.sh
“sql_shell_test.sh” [New file]

~
~

3. Press the ‘a’ key once (for append operations) and past the following text into your file.

sqlplus ‘/ as sysdba’ <

4. Press the keys : –> w –> q and hit the return key to save and exit vi.

5. Execute the chmod command to change you file status to read_write_execute for owner and group

mylinux:> chmod 770 sql_shell_test.sh
mylinux:>

6. As the Oracle software owner, execute the shell script sql_shell_test.sh with the following command.

mylinux:>./sql_shell_test.sh

7. Perform an ls command and you can see that the script has generated the output file test_shell_script_output.lst.

mylinux:>ls -lrt
-rwxrwxrwx 1 oracle dba 148 May 13 03:30 sql_shell_test.sql
-rw-r–r– 1 oracle dba 1391 May 13 03:32 test_shell_script_output.lst
mylinux:>

8. Perform a cat command to see the output of file test_shell_script_output.lst.

mylinux:>cat test_shell_script_output.lst
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 orcl
frankie
10.2.0.4.0 13-MAY-10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

SQL> select name, open_mode from v$database;

NAME OPEN_MODE
——— ———-
ORCL READ WRITE

SQL> spool off
mylinux:>

This concludes the execution of Oracle SQL commands from within UNIX or LINUX shell scripts.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com