Archive

Archive for the ‘Administration’ Category

ORA-00031: session marked for kill

January 28, 2014 Leave a comment

1. Kill a session, returned the message below.

SQL>  alter system kill session ‘1022,851’;
alter system kill session ‘1022,851’
*
ERROR at line 1:
ORA-00031: session marked for kill

2. Find out the OS process id

SQL> select a.spid,b.sid,b.serial#,b.username from v$process a,v$session b where a.addr=b.paddr and b.status=’KILLED’;

SPID         SID           SERIAL# USERNAME
———— ———- ———- ——————————
15198        ##########        851 EHEALTH

3. Go to OS to find the server process

[oracle@yplodb02 ~]$ ps -ef|grep 15198
oracle   15198     1  1 Jan20 ?        03:41:07 oracleehealthp1 (LOCAL=NO)

oracle    9140 13829  0 15:38 pts/4    00:00:00 grep 15198

4. Kill it by OS command

[oracle@yplodb02 ~]$ kill -9 15198

[oracle@yplodb02 ~]$ ps -ef|grep 15198
oracle   12522 13829  0 15:38 pts/4    00:00:00 grep 15198

 

 

 

 

Categories: Administration, ORA-XXX

Purge dba_scheduler_job_log

January 28, 2014 Leave a comment

 

SQL> exec DBMS_SCHEDULER.PURGE_LOG();

Categories: Administration

ORA-02085: database link DB2PCTVUAT connects to PCTVUAT

July 18, 2012 Leave a comment

I create a database link from a development db to uat db. Then I test the connection as below and got the error ORA-02085

SQL> create database link db2pctvuat connect to strmadmin identified by strmadmin using ‘pctvuat’;

Database link created.

SQL> select tname from tab@db2pctvuat
2  ;
select tname from tab@db2pctvuat
*
ERROR at line 1:
ORA-02085: database link DB2PCTVUAT connects to PCTVUAT

Once I see the initial parameter global_name=false, the connection test is OK

SQL> select tname from tab@db2pctvuat;

no rows selected

Then, found a Metalink article (DocID=1024124.6), it said that …

  – GLOBAL_NAMES is set to TRUE, the name of the database link should match the global name of the target database
 –  GLOBAL_NAMES is set to FALSE, you can name the dblink anything you desire.

Since my uat database name is pctvuat, I test the database with global_names is set to true

SQL>  create database link pctvuat  connect to strmadmin identified by strmadmin using ‘pctvuat’;

Database link created.

SQL> select tname from tab@pctvuat;

no rows selected

SQL> drop database link DB2PCTVUAT;

Database link dropped.

SQL> show parameter global

NAME                                 TYPE        VALUE
———————————— ———– ——————————
global_context_pool_size             string
global_names                         boolean     TRUE

Categories: Administration, ORA-XXX

Temporarily change user password

June 20, 2012 Leave a comment

1. Get the password string of user

SQL> select name, password from sys.user$ where name=’ROAMSAVE’;

NAME                           PASSWORD
—————————— ——————————
ROAMSAVE                       90EEB9F64DD6C1BF

2. Change the password of user
SQL> alter  user roamsave identified by roamsave;

 

3. Restore the password of the user

SQL> alter  user roamsave identified by values ’90EEB9F64DD6C1BF’;

User altered.

SQL> select name, password from sys.user$ where name=’ROAMSAVE’;

NAME                           PASSWORD
—————————— ——————————
ROAMSAVE                       90EEB9F64DD6C1BF

 

Categories: Administration

Temporary Tablesapce (TEMP) Full

May 31, 2012 Leave a comment

Fixing Procedure.

Turn it off and on. The temporary space are released.

1. Check current free space

SVRMGR>  select ‘FILE’,tablespace_name,sum(bytes) from dba_free_space
2> group by tablespace_name;
‘FIL TABLESPACE_NAME                SUM(BYTES)
—- —————————— ———-
FILE INDX                            167636992
FILE NETPG_DATA                     1012871168
FILE NETPG_INDEX                     497352704
FILE RBS                             801107968
FILE SYSTEM                           90705920
FILE TEMP                            117174272
FILE TOOLS                             8384512
FILE USERS                           514584576
FILE VIDEO                          5940887552
9 rows selected.

2. Turn it on/off

SVRMGR> alter tablespace temp offline;
Statement processed.
SVRMGR> alter tablespace temp online;
Statement processed.

3. Check free space now

SVRMGR> select ‘FILE’,tablespace_name,sum(bytes) from dba_free_space
2>  group by tablespace_name;
‘FIL TABLESPACE_NAME                SUM(BYTES)
—- —————————— ———-
FILE INDX                            167636992
FILE NETPG_DATA                     1012871168
FILE NETPG_INDEX                     497352704
FILE RBS                             801107968
FILE SYSTEM                           90705920
FILE TEMP                           1594617856 <–
FILE TOOLS                             8384512
FILE USERS                           514584576
FILE VIDEO                          5940887552
9 rows selected.
SVRMGR>

Categories: Administration

RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed ORA-12720: operation requires database is in EXCLUSIVE mode

March 7, 2011 Leave a comment

Today,

I duplicate new database from existing database on the same RAC servers. At the end, I got the following messages

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/07/2011 11:29:05
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

Finally, I found that I foget setting the target database ( the new one) initial parameter cluster_database to false. After changing the parameter to false, and restart instance in nomount and then reduplicate the database again. It worked fine.

 

 

 

 

Categories: Administration

Add service to single instance database

February 15, 2011 Leave a comment

1. Login database as sysdba

2.Add ha_gsqs database service name to service_names parameter

SQL> alter system set service_names=’gsqsqa,ha_gsqs’ scope=both;

3. Register the database service to listener

SQL> alter system register;

4. Testing the service name

$ sqlplus gsqs/gsqs@gsqsvmdb:1521/ha_gsqs

SQL*Plus: Release 11.1.0.7.0 – Production on Tue Feb 15 11:51:36 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

 

Categories: Administration