Oracle RAC Apps DBA: July 2014

Tablespace Management in Oracle

Tablespace : A tablespace is a storage location, where the objects are stored like tables, indexes, functions, procedures... Tablespace is intermediate between logical and physical components of oracle. A tablespace is made up of one or more datafiles.

Types of Tablespaces:

1. User data tablespaces

2. Temp Tablespaces

3. Undo Tablespaces

Segment: Segment is a database object , which is allocated to some space to it. Segments are consists one or more extents. A segment can span to one tablespace.

Extents: Extent is a contiguous oracle data bocks. Extents are allocated to segments.

Blocks: Block is a smallest unit of storage in oracle. By default oracle block size is 8k.

How to Kill sniped or inactive sessions

Inactive sessions or sniped sessions, sniped sessions are where database session idle_time is set to 30 minutes all inactive sessions which are running more than 30 minutes they are moved to sniped sessions. Sniped sessions are consumed resources unnecessarily . We have to kill them using script in periodically.

How to set idle_time:

1.SQL> select * from dba_profiles;

PROFILE RESOURCE_NAME RESOURCE LIMIT

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

DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED

DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED

DEFAULT CPU_PER_SESSION KERNEL UNLIMITED

DEFAULT CPU_PER_CALL KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED

DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED

DEFAULT IDLE_TIME KERNEL UNLIMITED

DEFAULT CONNECT_TIME KERNEL UNLIMITED

DEFAULT PRIVATE_SGA KERNEL UNLIMITED

DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10

DEFAULT PASSWORD_LIFE_TIME PASSWORD 180

DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED

DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED

DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL

DEFAULT PASSWORD_LOCK_TIME PASSWORD 1

DEFAULT PASSWORD_GRACE_TIME PASSWORD 7

16 rows selected.

2. SQL>alter profile default limit idle_time 30;

3. SQL>show parameter resource_limit

NAME TYPE VALUE

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

resource_limit boolean FALSE

SQL> alter system set resource_limit=true;

System altered.

Now inactive sessions running more than 30 minutes converted to snipped, Kill these sniped sessions .

How to stop listener.log file logging in oracle

In oracle listener log file has been increasing day by day it will effect on the performance of database.
We can stop listener logging , follow the below steps

$lsnrctl
lsnrctl> set log_status off

The above command will stop the logging temporarly upto listener restart, when you restart listener then it will start logging, for permenantly stop listener logging

place LISTENER_LOGGING = OFF in listener.ora file

listener logfile location in oracle 10g : $ORACLE_HOME/network/log/listener.log
listener logfile location in oracle 11g : /u01/app/oracle/diag/tnslsnr//listener/trace/listener.log

Move Oracle Database from one machine to another machine using RMAN

1. Take full backup of database on target , create pfile from spfile.

2. Move backup pieces and pfile to restoration machine.

3.Make necessary changes in pfile (paths according to new server like controlfiles,archivelog locations ) & place it on $ORACLE_HOME/dbs location.

4.startup nomount with pfile & create spfile from pfile shut down DB and startup nomount with spfile.

5.connect to rman

rman target /

6.create controlfile from backup

rman>restore controlfile from '/location/.bkp';

7. start the database in mount state.

sql> startup mount

8. Restore and recover the database from backup
rman> run {
catalog start with '/backup location/';
set newname for database to '/new database files location/%b';
restore database;
switch datafile all;
recover database;
}

9.Rename the all redo logs.

sql>select member from v$logfile;
sql>alter database rename file '/old location/redo03.log' to '/new location/redo03.log';

10. Open database in resetlogs.

sql>alter database open resetlogs;

11. Create temporary tablespace and make it default to database.

sql>create temporary tablespace temp1 tempfile '/location/temp02.dbf' size 100m autoextend on next 100m maxsize unlimited;

sql>alter database default temporary tablespace temp1;

ORA-00257 archiver error Connect internal only, until freed

Issue: ORA-00257 archiver error Connect internal only, until freed

This could be caused by due to archiver background process unable to write logs in archivelog destination because of archive destination is full. Until free make free size on archive destination database will not allow any transactions.

Using rman tool we can free the archive destination is the best way.

connect to the target machine
rman target /

rman>crosscheck archivelog all;
rman> delete noprompt obsolete;
rman> delete noprompt expired archivelog all; or
rman> delete archivelog all before 'sysdate-1'; or
rman> delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-1';

It will delete all the obsolate archivelogs from the archivelog location before present day.

Writing audit records to Windows Event log failed ORA-28056

Issue: Writing audit records to Windows Event log failed ORA-28056

I have faced this error while installation of Oracle 11.2.0.3 in Window Server 2008.

To Solve this issue

Click start>control panel>administrative tools> Double click on Event Viewer.

Clear all the event logs. Then continue the installation process.

This issue might get oracle startup time also,

SQL> conn /as sysdba;

ERROR: ORA-28056: Writing audit records to Windows Event Log failed
ORA-01031: insufficient privileges

At that time also follow the above steps.

Pages