Drop Oracle DB by sqlplus (RAC)

Just write about drop Oracle Database by using SQL*Plus. If we use Oracle Database RAC. we should stop Instances, except one node.

$ env | grep SID
ORACLE_SID=DB1

$ sqlplus / as sysdba

SQL> alter system set cluster_database=FALSE scope=spfile;

System altered.

SQL> shutdown immediate;

SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 2089472 bytes
Variable Size 671092224 bytes
Database Buffers 3607101440 bytes
Redo Buffers 14684160 bytes
Database mounted.

SQL> alter system enable restricted session;

System altered.

SQL> select name from v$database;

NAME
---------
DB

SQL> drop database;

Database dropped.

done...

SQL*Plus 10.2.0.1 Hangs, When System Uptime Is Long Period of Time

Today, my colleague told me, Why I can't use "sqlplus" (Oracle client) on my application server connect your database, But I can use "tnsping"... And I'd ever connected!

I remoted on this server and found "sqlplus" hung ... and sqlplus used more CPU %

$ ps aux

USER       PID %CPU %MEM   VSZ  RSS TTY      STAT START   TIME COMMAND

oracle   12722 96.4  0.2 19560 4600 pts/5    R    15:36   0:06 sqlplus

So, found out on metalink... (338461.1)

SQL*Plus 10.2.0.1 Hangs, When System Uptime Is Long Period of Time (Linux x86)

Used "strace" :

$ strace sqlplus -V 2>&1 |less

execve("/oracle/10.2.0/client/bin/sqlplus", ["sqlplus", "-V"], [/* 31 vars */]) = 0

uname({sys="Linux", node="host01", ...})  = 0

brk(0)                                  = 0x804a000

access("/etc/ld.so.preload", R_OK)      = -1 ENOENT (No such file or directory)

.

.

.

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

times(NULL)                             = -2138395754

It is looping on the times() function.

There have been cases where problem occurs when uptime reaches 60 days and others as long as 248 days.

In addition to sqlplus, it has been reported that the netca and dbca tools also hang.

Solution from metalink...

Select one of the following two solutions:

1) Apply one-off patch available for 10.2.0.1.

   a. Download one-off patch off Metalink:

       Patch 4612267

       Description OCI CLIENT IS IN AN INFINITE LOOP WHEN MACHINE UPTIME HITS 248 DAYS

       Product CORE

       Release Oracle 10.2.0.1

   b. To apply patch on Instant Client install, please follow instructions documented in the OCI manual.

        You can find this in:

        http://www.oracle.com/pls/db102/db102.show_toc?which=main&partno=b14250&maxlevel=2&section=&expand=3216

        under "Patching Instant Client Shared Libraries on Linux or UNIX".

2)  Apply Patchset 10.2.0.2  or higher. 

      According to Bug 4612267, this bug is fixed in version 11, and backported to 10.2.0.2 patchset.

Relax Time! and Work... again

I was on vacation at Phu Kradung (wiki). That's a great location...and great time. No Oracle, No Internet... that made me Enjoy...

It's time to work and online again...

I thought... if i want to find what oracle process make more CPU... and trace ...it!

Maybe I should begin with OS process (unix/linux command), check any OS process make more percentage CPU.

$ ps -e -o pcpu,user,pid,args | sort -k 1 | grep oracle | tail

.

.

.

3.2 oracle 32657 oracle[ORACLE_SID](LOCAL=NO)

3.9 oracle 3834 ora_j000_[ORACLE_SID]

15.3 oracle 16463 oracle[ORACLE_SID](LOCAL=NO)

Or

$ top

.

.

.

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

16463 oracle 16 0 7824m 1.6g 1.6g S 34 10.2 1:06.41 oracle

28150 oracle -2 0 7834m 6.6g 6.6g S 9 42.4 3914:51 oracle

I knew os process... and I want to trace it, So sqlplus command with "oradebug" trace 10046 event on OS process can help! (Example: oradebug trace oracle process)

$ sqlplus / as sysdba

SQL> oradebug setospid 16463

SQL> oradebug TRACEFILE_NAME

$ORACLE_BASE/admin/[ORACLE_SID]/udump/[ORACLE_SID]_ora_16463.trcc

SQL> oradebug unlimit

SQL> oradebug event 10046 trace name context forever, level 12

I should find trace file.

$ cd $ORACLE_BASE

$ cd admin/[ORACLE_SID]/udump

$ ls *16463*

[ORACLE_SID]_ora_16463.trc

and then use tkprof command-line .

$ tkprof [ORACLE_SID]_ora_16463.trc /tmp/file.out sys=no

After that I can investigate a problem on /tmp/file.out file, Check SQL statement and resolve...

Example:

$ less /tmp/file.out

.

.

.

SELECT COUNT(*)

FROM

"DTABLE" "A1" WHERE "A1"."A"=:1 AND "A1"."DSTART"<=TO_DATE(:2,

'dd/mm/yyyy hh24:mi:ss') AND "A1"."DSTOP">=TO_DATE(:3,'dd/mm/yyyy

hh24:mi:ss')

call count cpu elapsed disk query current rows

------------- ----------------------------------------------------------

Parse 2 0.00 0.00 0 0 0 0

Execute 2 0.00 0.00 0 0 0 0

Fetch 4 1.85 3.41 1 93080 0 2

------- ------ -------- --------------------------------------------------

total 8 1.86 3.41 1 93080 0 2

.

.

.

Oh! I forgo: disable 10046 event

$ sqlplus / as sysdba

SQL> oradebug setospid 16463

SQL> oradebug event 10046 trace name context off

That's easy to check and trace...

Start Database by sqlplus, can not by srvctl on Solaris 10 (Oracle RAC 10G)

After I changed project on Solaris 10.

projmod -s -K "project.max-shm-memory=(privileged,13832385536,deny)" oracle

something wrong, i can start instance by sqlplus, but can not by srvctl

srvctl  start instance -d db  -i  db2

PRKP-1001 : Error starting instance db2 on node node02

CRS-0215: Could not start resource 'ora.db.db2.inst'.

When checked log files on ORACLE_HOME/log/{hostname}/racg/imon*log

SQL> ORA-27102: out of memory

SVR4 Error: 22: Invalid argument

SQL> Disconnected

So, found out some informations from matalink.

Cause

Problem is not the oracle project.

This occurs because when we start an instance using srvctl it will inherit the system project, since the crsd.bin is running as root, our racg infrastructure is starting the resource as oracle however racgwrap is initially started as root. It calls  setuid/setgid to change the user & group IDs but unfortunately when we do this we dont call the project ID.

Solution

Easiest fix for this problem is to define a project for system.

% projmod -s -K "project.max-shm-memory=(privileged,2.00TB,deny)" system

% projmod -s -K "process.max-sem-nsems=(privileged,2048,deny)" system

Above assigns root user the system project in order for the CRSD daemon (run under root's user id) to pick up the changes:

% vi /etc/user_attr, add root to project=system:

Finally restart CRS stack

And then I modified project (for system project)

projmod -s -K "project.max-shm-memory=(privileged,13832385536,deny)" oracle

projmod -s -K "project.max-shm-memory=(privileged,15132385536,deny)" system

that helped me solve this case.

srvctl  start instance -d db  -i  db2

.

.

.

Drop Oracle DB by sqlplus

- Shutdown Database and start Database on mount Mode.

$ export ORACLE_SID=testdb
$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 3 13:47:16 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1703624704 bytes
Fixed Size 2145064 bytes
Variable Size 1291846872 bytes
Database Buffers 402653184 bytes
Redo Buffers 6979584 bytes
Database mounted.

- Alter database to restricted session Mode and drop Database.

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Enjoy!