Oracle DBA Daily Experiences

To start the instance:
# check status of instance
$ $CRS_HOME/bin/crs_stat

# seen cases when CRS says OFFLINE but pmon is still running so double checking here
$ ps -ef | grep <instance_name> | grep pmon

# start one of the instance on one of the nodes if not running
$ srvctl start database -d <database name> -i <instance name>

To shutdown the instance:

# verify instance running
$ ps -ef | grep <instance_name> | grep pmon

# shutdown one of the instance on one of the nodes
$ srvctl stop database -d <database name> -i <instance name>

# check status of instance to make sure it’s down
$ $CRS_HOME/bin/crs_stat

# verify instance not running
$ ps -ef | grep <instance_name> | grep pmon

Received ORA-19571 error when running of archive log process, and found the the parameter control_file_record_keep_time was set to 0 so we changed to higher value which fixed the issue. Note this parameter can be changed without restart if using spfile, click on this link to find how to find if you are using spfile or not.

Log file:
archive log filename=/u01/oradata/TEST/arch/TEST_173673.arc recid=173475 stamp=668792442
Crosschecked 3146 objects

starting full resync of recovery catalog
full resync complete

sql statement: alter system archive log current

Starting backup at 22-SEP-08
channel t1: starting archive log backupset
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t1 channel at 09/22/2008
ORA-19571: archived-log recid 173477 stamp 668795179 not found in controlfile

One can use the following v$nls_parameters view to find the database LANGUAGE, TERRITORY and CHARACTER SET.

select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET',
'NLS_LANGUAGE', 'LANGUAGE',
'NLS_TERRITORY', 'TERRITORY') name,
value from v$nls_parameters
WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY')
/

NAME VALUE
————- —————–
LANGUAGE AMERICAN
TERRITORY AMERICA
CHARACTER SET WE8ISO8859P1

export NLS_LANG=<language>_<territory>.<character set>

To reduce the amount of space used when exporting one can compress the data as creating export file and also import data from a compressed file.

Exporting data directly to a compressed file
#!/bin/ksh

PIPE_FILE=/tmp/exp.pipe
EXP_FILE=exp.dmp.gz

# delete pipe file
rm -f $PIPE_FILE
# create a pipe file
$ mknod $PIPE_FILE p
$ gzip < $PIPE_FILE > $EXP_FILE &
$ exp file=$PIPE_FILE ….

Importing data directly from a compressed file
#!/bin/ksh

rm -f $PIPE_FILE
PIPE_FILE=/tmp/imp.pipe
EXP_FILE=exp.dmp.gz

$ mknod $PIPE_FILE p
$ gunzip < $EXP_FILE > $PIPE_FILE &
$ imp file=$PIPE_FILE ….

If you are using Oracle 10g datapump utility you can’t use the above step as the exdp checks if a file exists before writing and generates an error as the file exists.

When using datapump one can run the following query to monitor the progress by running the following SQL.

select sid, serial#, sofar, totalwork,
dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;

SID SERIAL# SOFAR TOTALWORK OWNER_NAME STATE JOB_MODE
———- ———- ———- ———- —————————— —————————— ——————————
122 64151 1703 2574 SYSTEM EXECUTING FULL

In certain cases you may want to lock statistics in a table in certain cases, for example if you want a table not be analyzed by automatic statistics job but analyze it later or in cases where you want prevent from analyzing statistics in cases where data in the table doesn’t change.

The following example shows how to lock table statistics and what happens when one tries to gather statistics on table that has statistics locked.

– create table
SQL> create table test ( x number );

Table created.

– create index
SQL> create index test_idx on test(x);

Index created.

– shows when stats is not locked the value of stattype_locked is NULL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
– lock statistics
SQL> exec dbms_stats.lock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

– shows when stats is locked the value of stattype_locked is ALL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
ALL

— try to gather statistics on locked table
SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');
BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

– try to gather statistics on the index using analyze
SQL> analyze index ajaffer.test_idx compute statistics;
analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

– unlock statistics
SQL> exec dbms_stats.unlock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

One of the features in 10g is flashback which allows to restores tables that were dropped. Note: If an index is dropped but not a table it can’t be restored from flashback but if a table is dropped that has an index you can restore the table and the index from flashback.

The example below shows a case where table is dropped that has an index.
– create table
SQL> create table test ( x number(1) constraint test not null);

Table created.

– create index on the table test
SQL> create index test_idx on test(x);

Index created.

– show recyclebin it’s empty
SQL> show recyclebin;

SQL> column object_name format a30

– show objects
SQL> select object_name from user_objects;

OBJECT_NAME
——————————
TEST_IDX
TEST

– drop table
SQL> drop table test;

Table dropped.

– show recyclebin which only shows the table
SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEST BIN$WLAh2JvbQDDgQ6wbCh1AMA==$0 TABLE 2008-10-07:13:58:17

– flashback the table
SQL> flashback table test to before drop;

Flashback complete.

– shows the object is restored and index too but it has name from the recycle-bin
SQL> select object_name from user_objects;

OBJECT_NAME
——————————
TEST
BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0

– show indexes it still has the name from the recyle-bin
SQL> select index_name from user_indexes;

INDEX_NAME
——————————
BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0

– shows the index is linked to the table
SQL> select table_name, index_name from user_indexes;

TABLE_NAME INDEX_NAME
—————————— ——————————
TEST BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0

– restore the index name back
SQL> ALTER INDEX “BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0″ RENAME TO test_idx;

Index altered.

– shows the updated index name
SQL> select table_name, index_name from user_indexes;

TABLE_NAME INDEX_NAME
—————————— ——————————
TEST TEST_IDX

Here is second example showing index is not part of recyclebin when dropped.
SQL> create table test ( x number(1) constraint test not null);

Table created.

SQL> create index test_idx on test(x);

Index created.

SQL> select object_name from user_objects;

OBJECT_NAME
——————————————————————————

TEST
TEST_IDX

SQL> show recyclebin;
SQL> select * FROM user_recyclebin;

no rows selected

SQL> drop index test_idx;

Index dropped.

SQL> select * FROM user_recyclebin;

no rows selected

SQL> show recyclebin;
SQL> select index_name from user_indexes;

no rows selected

Using the following command one can pass hostname, port, servicename and SID and connect to a database without having an entry in tnsnames.ora

$ sqlplus username/password@hostname:port/SERVICENAME

OR

$ sqlplus username
Enter password: password@//hostname:port/SERVICENAME

OR

$ sqlplus /nolog
SQL> connect username/password@hostname:port/SERVICENAME

By querying V$parameter one can find if the parameter can be modified in session and/or system.

If isses_modifiable is true then value can be modified in session, if FALSE then it can’t be modified using ALTER SESSION SET..

If issys_modifiable = DEFERRED, the value can be changed with scope=spfile specified (if spfile is used) and the change will take into effect into subsequent sessions.

If issys_modifiable =FALSE, the value can be changed using ALTER SYSTEM only if spfile is used and the change will take into effect after instance restart.

If issys_modifiable = IMMEDIATE, the value can be changed with scope=[memory|both|spfile]

To find out if the database is using spfile is being used refer to the following link http://oraclespin.wordpress.com/2008/06/22/how-to-tell-if-the-database-was-started-with-spfile-or-pfile/

Eg:

SELECT  isses_modifiable, issys_modifiable FROM V$parameter WHERE name = ‘shared_pool_size’;

ISSES   ISSYS_MOD

—– ———
FALSE  IMMEDIATE

The datafile for UNDO tablespace can’t be shrunk as we had issue where the datafile was set to unlimited and it kept on growing to fix the issue one can do the following steps.

– UNDO_RBS1 is new undo tablespace name
SQL> create undo tablespace UNDO_RBS1 datafile ‘/u03/oradata/TEST/undorbs1.dbf’ size 1000m;

– make the new tablespace to be the undo tablespace
SQL> alter system set undo_tablespace=undo_rbs1;

– get the filename of the old undo tablespace which will be dropped so you can remove the file
SQL> SELECT file_name FROM dba_data_files WHERE tablespace_name = ‘UNDO_RBS0′;

FILE_NAME
——————————————————————————–
/u03/oradata/TEST/undotbs0_rbs1.dbf

– drop the undo tablespace which has the unlimited datafile, if there is an active transaction in the undo tablespace then it will not be able to drop the tablespace so one can check and monitor for active transactions that are running
SQL> drop tablespace undo_rbs0;

– once the tablespace is dropped the file can be then be deleted
SQL> !rm /u03/oradata/TEST/undotbs0_rbs1.dbf