Oracle – Page 3 – Fahad Pulikkal

Performance Tuning Areas

Just picking those area that require tuning! Source – OraFAQ

Database Design (if it’s not too late):

Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the “data access path” in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.

Application Tuning:

Experience shows that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.

Memory Tuning:

Properly size your database buffers (shared_pool, buffer cache, log buffer, etc) by looking at your wait events, buffer hit ratios, system swapping and paging, etc. You may also want to pin large objects into memory to prevent frequent reloads.

Disk I/O Tuning:

Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc.

Eliminate Database Contention:

Study database locks, latches and wait events carefully and eliminate where possible.

Tune the Operating System:

Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.

Oracle Cloning

About Cloning
(From Oracle.com)
Cloning is the process of copying an existing Oracle installation to a different location and updating the copied bits to work in the new environment. The changes made by applying one-off patches on the source Oracle home are also present after the clone operation. The source and the destination path (host to be cloned) need not be the same. During cloning, OUI replays the actions that were run to install the home. Cloning is similar to installation, except that OUI runs the actions in a special mode referred to as clone mode. Some situations in which cloning is useful are:

Creating an installation that is a copy of a production, test, or development installation. Cloning enables you to create a new installation with all patches applied to it in a single step. This contrasts with going through the installation process by performing separate steps to install, configure, and patch the installation.

Rapidly deploying an instance and the applications that it hosts.

Preparing an Oracle home and deploying it to many hosts.

The cloned installation behaves the same as the source installation. For example, the cloned Oracle home can be removed using OUI or patched using OPatch. You can also use the cloned Oracle home as the source for another cloning operation. You can create a cloned copy of a test, development, or production installation by using the command-line cloning scripts. The default cloning procedure is adequate for most usage cases. However, you can also customize various aspects of cloning, for example, to specify custom port assignments, or to preserve custom settings.

The cloning process copies all of the files from the source Oracle home to the destination Oracle home. Thus, any files used by the source instance located outside the source Oracle home’s directory structure are not copied to the destination location.

The size of the binaries at the source and the destination may differ because these are relinked as part of the clone operation, and the operating system patch levels may also differ between these two locations. Additionally, the number of files in the cloned home would increase because several files copied from the source, specifically those being instantiated, are backed up as part of the clone operation.

OUI cloning is more beneficial than using the tarball approach, because cloning configures the Central Inventory and the Oracle home inventory in the cloned home. Cloning also makes the home manageable and allows the paths in the cloned home and the target home to be different.

1. Sign in as SYSDBA on the source system through SQL Plus and issue:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Create database syntax will be now placed in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

Trace file:

— The following are current System-scope REDO Log Archival related
— parameters and can be included in the database initialization file.
—
— LOG_ARCHIVE_DEST=”
— LOG_ARCHIVE_DUPLEX_DEST=”
—
— LOG_ARCHIVE_FORMAT=ARC%S_%R.%T
—
— DB_UNIQUE_NAME=”TEST”
—
— LOG_ARCHIVE_CONFIG=’SEND, RECEIVE, NODG_CONFIG’
— LOG_ARCHIVE_MAX_PROCESSES=2
— STANDBY_FILE_MANAGEMENT=MANUAL
— STANDBY_ARCHIVE_DEST=%ORACLE_HOME%RDBMS
— FAL_CLIENT=”
— FAL_SERVER=”
—
— LOG_ARCHIVE_DEST_10=’LOCATION=USE_DB_RECOVERY_FILE_DEST’
— LOG_ARCHIVE_DEST_10=’OPTIONAL REOPEN=300 NODELAY’
— LOG_ARCHIVE_DEST_10=’ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC’
— LOG_ARCHIVE_DEST_10=’REGISTER NOALTERNATE NODEPENDENCY’
— LOG_ARCHIVE_DEST_10=’NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME’
— LOG_ARCHIVE_DEST_10=’VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)’
— LOG_ARCHIVE_DEST_STATE_10=ENABLE

—
— Below are two sets of SQL statements, each of which creates a new
— control file and uses it to open the database. The first set opens
— the database with the NORESETLOGS option and should be used only if
— the current versions of all online logs are available. The second
— set opens the database with the RESETLOGS option and should be used
— if online logs are unavailable.
— The appropriate set of statements can be copied from the trace into
— a script file, edited as necessary, and executed when there is a
— need to re-create the control file.
—
— Set #1. NORESETLOGS case
—
— The following commands will create a new control file and use it
— to open the database.
— Data used by Recovery Manager will be lost.
— Additional logs may be required for media recovery of offline
— Use this only if the current versions of all online logs are
— available.

— After mounting the created controlfile, the following SQL
— statement will place the database in the appropriate
— protection mode:
— ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “TEST” NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘C:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG’ SIZE 50M,
GROUP 2 ‘C:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG’ SIZE 50M,
GROUP 3 ‘C:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG’ SIZE 50M
— STANDBY LOGFILE

DATAFILE
‘C:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTEXAMPLE01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTTEST.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTUSERS02.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTEXAMPLE2.DBF’
CHARACTER SET WE8MSWIN1252
;

— Commands to re-create incarnation table
— Below log names MUST be changed to existing filenames on
— disk. Any one log file from each branch can be used to
— re-create incarnation records.
— ALTER DATABASE REGISTER LOGFILE ‘C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATESTARCHIVELOG2011_10_17O1_MF_1_1_%U_.ARC’;
— ALTER DATABASE REGISTER LOGFILE ‘C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATESTARCHIVELOG2011_10_17O1_MF_1_1_%U_.ARC’;
— Recovery is required if any of the datafiles are restored backups,
— or if the last shutdown was not normal or immediate.
RECOVER DATABASE

— All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

— Database can now be opened normally.
ALTER DATABASE OPEN;

— Commands to add tempfiles to temporary tablespaces.
— Online tempfiles have complete space information.
— Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘C:ORACLEPRODUCT10.2.0ORADATATESTTEMP01.DBF’
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
— End of tempfile additions.
—
— Set #2. RESETLOGS case
—
— The following commands will create a new control file and use it
— to open the database.
— Data used by Recovery Manager will be lost.
— The contents of online logs will be lost and all backups will
— be invalidated. Use this only if online logs are damaged.

— After mounting the created controlfile, the following SQL
— statement will place the database in the appropriate
— protection mode:
— ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “TEST” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘C:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG’ SIZE 50M,
GROUP 2 ‘C:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG’ SIZE 50M,
GROUP 3 ‘C:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG’ SIZE 50M
— STANDBY LOGFILE

DATAFILE
‘C:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTEXAMPLE01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTTEST.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTUSERS02.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTEXAMPLE2.DBF’
CHARACTER SET WE8MSWIN1252
;

— Commands to re-create incarnation table
— Below log names MUST be changed to existing filenames on
— disk. Any one log file from each branch can be used to
— re-create incarnation records.
— ALTER DATABASE REGISTER LOGFILE ‘C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATESTARCHIVELOG2011_10_17O1_MF_1_1_%U_.ARC’;
— ALTER DATABASE REGISTER LOGFILE ‘C:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATESTARCHIVELOG2011_10_17O1_MF_1_1_%U_.ARC’;
— Recovery is required if any of the datafiles are restored backups,
— or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

— Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

— Commands to add tempfiles to temporary tablespaces.
— Online tempfiles have complete space information.
— Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE ‘C:ORACLEPRODUCT10.2.0ORADATATESTTEMP01.DBF’
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
— End of tempfile additions.

2. Shutdown source database.

3. Install Oracle server on new machine. Create directories ( bdump, udump and cdump and other directories as in source database system ) for the copy database on target system.

4. Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

5. Add database name to tnsnames.ora, listener.ora on target system.

6. Create database service with ORADIM ( If OS is Windows )

oradim -new -sid TEST -INTPWD mypassword -STARTMODE AUTO

7. On target system, set SID and use sqlplus to connect as SYSDBA

8. Delete the control files if any already copied over using OS commands

9. Rename trace file to ‘ DB_CREATE_CONTROLFILE.SQL ‘, edited, contents are as follows:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “TEST” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘C:ORACLEPRODUCT10.2.0ORADATATESTREDO01.LOG’ SIZE 50M,
GROUP 2 ‘C:ORACLEPRODUCT10.2.0ORADATATESTREDO02.LOG’ SIZE 50M,
GROUP 3 ‘C:ORACLEPRODUCT10.2.0ORADATATESTREDO03.LOG’ SIZE 50M

DATAFILE
‘C:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTEXAMPLE01.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTTEST.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTUSERS02.DBF’,
‘C:ORACLEPRODUCT10.2.0ORADATATESTEXAMPLE2.DBF’
CHARACTER SET WE8MSWIN1252
;

RECOVER DATABASE USING BACKUP CONTROLFILE

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE ‘C:ORACLEPRODUCT10.2.0ORADATATESTTEMP01.DBF’
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

10. Run the create control file script issuing:

@DB_CREATE_CONTROLFILE.SQL

If everything went OK and you see no errors, then execute:

SHUTDOWN IMMEDIATE;

11. Startup new database and verify it work fine.

ORA-01552

Oracle Error :: ORA-01552: cannot use system rollback segment for non-system tablespace ‘CRM’

Possible Reason and Action:
Undo tablespace is missing – Recreate the undo tablespace
Undo tablespace size is full – Create new undo tablespace and make it default
Undo tablespace datafile offline – Make it online

SQL> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES;

TABLESPACE_NAME STATUS
———————————- ————-
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS ONLINE
TEMP ONLINE
CRM ONLINE

Check V$DATAFILE:

SQL> SELECT FILE#, STATUS FROM V$DATAFILE;

FILE# STATUS
——— ————
1 SYSTEM
2 ONLINE
3 ONLINE
4 ONLINE

If any datafile is OFFLINE, bring it ONLINE. Eg:

SQL> ALTER DATABASE DATAFILE ‘D:CRMDATAUNDOTBS.DBF’ ONLINE;

Database altered.

SQL> commit;

Commit complete.

SQL> SHOW PARAMETER UNDO

NAME TYPE VALUE
——————————– ———– ——————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS

If undo_management parameter isn’t auto, set it to auto in parameter file and restart database.

ORA-00257

Error:

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

Reason:

File recovery area is above specified size.

Action:

Without increasing DB_RECOVERY_FILE_DEST_SIZE :

1. Check whether the database is in archive log mode and automatic archiving is enabled.

 SQL> archive log list;
 Database log mode              Archive Mode
 Automatic archival             Enabled
 Archive destination            USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence     29
 Next log sequence to archive   31
 Current log sequence           31

2. If archive destination is defined by USE_DB_RECOVERY_FILE_DEST, find the archive destination by:

SQL> show parameter db_recovery_file_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:oracleproduct10.2.0/flash_recovery_area
db_recovery_file_dest_size           big integer 2G

Check what the value for db_recovery_file_dest_size.

3. Find the space used in flash recovery area by using following SQL:

col ROUND(SPACE_LIMIT/1048576) heading “Space Allocated (MB)” format 999999
col round(space_used/1048576) heading “Space Used (MB)” format 99999
col name format a40
select name, round(space_limit/1048576) As space_limit,round(space_used/1048576) As space_used
from v$RECOVERY_FILE_DEST;

4. If SPACE_USED is equal to SPACE_LIMIT of db_recovery_file_dest, move the archive logs to different destination.

5. Archive all the log files

SQL> alter system archive log all;

6. Just switch the logs to verify

 SQL> alter system switch logfile;

7. DB_RECOVERY_FILE_DEST_SIZE is to delete (archive log) files from DB_RECOVERY_FILE_DEST if you are sure you have backups and the archived logs are no longer necessary.

$rman target / RMAN>delete archivelog until time ‘SYSDATE-1’; or, RMAN>delete archivelog all;

By increasing DB_RECOVERY_FILE_DEST_SIZE :

SQL> show parameter db_recovery_file_dest;
NAME TYPE VALUE
———————————— ———– ——————————
db_recovery_file_dest string C:oracleproduct10.2.0/flash_recovery_area
db_recovery_file_dest_size big integer 2G

Check what the value for db_recovery_file_dest_size.

Increase the Flash Recovery Area

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=’10G’ SCOPE=BOTH;
Sytem Altered.

http://itnewscast.com/database/error-ora-00257-archiver-error-connect-internal-only-until-freed

http://oraworks.wordpress.com/2011/08/08/ora-00257-archiver-error-connect-internal-only-until-freed/

ORA-00271

Error:

Oracle Error :: ORA-00271
there are no logs that need archiving

Reason:

An attempt was made to archive the unarchived redo log files manually, but there are no files that need to be archived.

Action:

No action required.

Database Tools

dbpost favorites:

SQLveloper
MyOra
empower!

* draft page

ORA-371

Error:

ORA-00371 (ORA-371)
Text: not enough shared pool memory, should be at least %s bytes

Reason:

Init.ora parameter shared_pool_size is too small

Action:

Increase the parameter value

Recycle Bin

Display all the contents of the recycle bin

show recyclebin

Purge the recycle bin

purge recyclebin

Drop an object with out placing it in the recycle bin

drop <object_type> <object_name> purge;

Database Session Stats

Session

1. Connected users list

set lines 100 pages 999
col ID format a15
select username
,      sid || ',' || serial# "ID"
,      status
,      last_call_et "Last Activity"
from   v$session
where  username is not null
order by status desc
,        last_call_et desc
/

2. Time since last user activity

set lines 100 pages 999
select username
,      floor(last_call_et / 60) "Minutes"
,      status
from   v$session
where  username is not null
order by last_call_et
/

3. Sessions sorted by logon time

set lines 100 pages 999
col ID		format a15
col osuser	format a15
col login_time	format a14
select 	username
,	osuser
,	sid || ',' || serial# "ID"
,	status
,	to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
,	last_call_et
from	v$session
where	username is not null
order	by login_time
/

4.User information including OS process ID

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select	s.sid || ',' || s.serial# "SID/SERIAL"
,	s.username
,	s.osuser
,	p.spid "OS PID"
,	s.program
from	v$session s
,	v$process p
Where	s.paddr = p.addr
order 	by to_number(p.spid)
/

5. Show users’ current SQL

Select sql_text
from   v$sqlarea
where  (address, hash_value) in
(select sql_address, sql_hash_value
        from v$session
        where username like '&username')
/

6. Session status associated with the specified OS process ID

select	s.username
,	s.sid
,	s.serial#
,	p.spid
,	last_call_et
,	status
from 	V$SESSION s
,	V$PROCESS p
where	s.PADDR = p.ADDR
and	p.spid='&pid'
/

7. Show all active SQL

set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
  for x in
   (select username||'('||sid||','||serial#||') ospid = '|| process ||
    ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') current_time,
    sql_address,
    sql_hash_value
   from v$session
   where status = 'ACTIVE'
   and rawtohex(sql_address) <> '00'
   and username is not null ) loop
   for y in (select sql_text
   from v$sqlarea
   where address = x.sql_address ) loop
   if ( y.sql_text not like '%listener.get_cmd%' and
    y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
    dbms_output.put_line( '--------------------' );
    dbms_output.put_line( x.username );
    dbms_output.put_line( x.logon_time || ' ' || x.current_time ||
 ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
   end if;
  end loop;
 end loop;
end;
/

8. List long operations

set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select	username
,	to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
,	time_remaining remaining
,	message
from	v$session_longops
where	time_remaining = 0
order by time_remaining desc
/

9. List open cursors per user

a, 
set pages 999
select 	sess.username
,	sess.sid
,	sess.serial#
,	stat.value cursors
from	v$sesstat stat
,	v$statname sn
,	v$session sess
where	sess.username is not null
and	sess.sid = stat.sid
and	stat.statistic# = sn.statistic#
and	sn.name = 'opened cursors current'
order by value
/
b,
set lines 100 pages 999
select count(hash_value) cursors
,      sid
,      user_name
from   v$open_cursor
group by
       sid
,      user_name
order by
       cursors
/