Archive for February, 2012

Point in Time Recovery Using RMAN

February 27, 2012 Leave a comment

Point in time recovery using RMAN – untill a log sequence number

1.Recovery Objective

SQL> conn scott/tiger
 SQL> select count(*) from myobjects;

2.Switch a logfile

SQL> conn / as sysdba
 SQL> alter system switch logfile;
System altered.

3.Note the current log sequence number (13)

SQL> archive log list
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination /u02/ORACLE/opsdba/arch
 Oldest online log sequence 12
 Next log sequence to archive 14
 Current log sequence 14

4.Simulate an application failure – WRONG Delete!!

SQL> conn scott/tiger
 SQL> delete myobjects;
249410 rows deleted.
SQL> commit;
Commit complete.

The developer states that the wrong DML statement was made AFTER 8.15 AM and is positive about the same.

We need to determine the log sequence we need to recover until

select sequence#,first_change#, to_char(first_time,'HH24:MI:SS')
from v$log order by 3
 SQL> /
 ---------- ------------- --------
 13 2760463 07:49:36
 14 2761178 08:12:47
 15 2766622 08:18:49

Log sequence 14 was first written to at 8:12 AM so we should recover to a log sequence before this – i.e sequence# 13

5. Shutdown and mount the database

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

 SQL> startup mount;
 ORACLE instance started.
Total System Global Area 264241152 bytes
 Fixed Size 2070416 bytes
 Variable Size 163580016 bytes
 Database Buffers 92274688 bytes
 Redo Buffers 6316032 bytes
 Database mounted.

 RMAN> run {
 2> set until sequence=14; >>> add one to the sequence number we have to recover until
 3> restore database;
 4> recover database;
 5> }
executing command: SET until clause
Starting restore at 29-JAN-07
 allocated channel: ORA_DISK_1
 channel ORA_DISK_1: sid=154 devtype=DISK
 allocated channel: ORA_SBT_TAPE_1
 channel ORA_SBT_TAPE_1: sid=158 devtype=SBT_TAPE
 channel ORA_SBT_TAPE_1: Data Protection for Oracle: version
channel ORA_DISK_1: starting datafile backupset restore
 channel ORA_DISK_1: specifying datafile(s) to restore from backup set
 restoring datafile 00001 to /u02/ORACLE/opsdba/system01.dbf
 restoring datafile 00002 to /u02/ORACLE/opsdba/undotbs01.dbf
 restoring datafile 00003 to /u02/ORACLE/opsdba/sysaux01.dbf
 restoring datafile 00004 to /u02/ORACLE/opsdba/users01.dbf
 restoring datafile 00005 to /u02/ORACLE/opsdba/users02.dbf
 restoring datafile 00006 to /u02/ORACLE/opsdba/users03.dbf
 restoring datafile 00007 to /u02/ORACLE/opsdba/users05.dbf
 restoring datafile 00008 to /u02/ORACLE/opsdba/users06.dbf
 restoring datafile 00009 to /u02/ORACLE/opsdba/users07.dbf
 restoring datafile 00010 to /u02/ORACLE/opsdba/users04.dbf
 restoring datafile 00011 to /u02/ORACLE/opsdba/drtbs1.dbf
 restoring datafile 00012 to /u02/ORACLE/opsdba/drtbs2.dbf

 channel ORA_DISK_1: reading from backup piece
 channel ORA_DISK_1: 
 restored backup piece 1
 piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070129. 
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
 Finished restore at 29-JAN-07
Starting recover at 29-JAN-07
 using channel ORA_DISK_1
 using channel ORA_SBT_TAPE_1
starting media recovery
archive log thread 1 sequence 13 is already on disk as file 
 channel ORA_DISK_1: starting archive log restore to default destination
 channel ORA_DISK_1: restoring archive log
 archive log thread=1 sequence=12
 channel ORA_DISK_1: reading from backup piece 
 channel ORA_DISK_1: restored backup piece 1
 piece handle=/opt/oracle/backup/opsdba/OPSDBA.20070129. 
 channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
 archive log filename=/u02/ORACLE/opsdba/arch/arch_1_12_613052894.dbf thread=1 sequence=12
 archive log filename=/u02/ORACLE/opsdba/arch/arch_1_13_613052894.dbf thread=1 sequence=13
 media recovery complete, elapsed time: 00:00:01
 Finished recover at 29-JAN-07
RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs

6.Confirm that the recovery has worked

 opsdba:/opt/tivoli/tsm/client/oracle/bin64>sqlplus scott/tiger
SQL*Plus: Release - Production on Mon Jan 29 09:43:14 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
 Connected to:
 Oracle Database 10g Enterprise Edition Release - 64bit Production
 With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL> select count(*) from myobjects;


Reference links:


Oracle Database Running Out of Space ?

February 22, 2012 Leave a comment

Are you running out of space in the file system where database files (data files) are installed?

Try out this tip in order to move some of your data files from one drive to another and update the datafile location in your database.

The operating system: Oracle Enterprise Linux
The Database: Oracle Database 10g Enterprise Edition Release

In our environment we have installed our database’s files (data files) in /oracle/oradata/mydb location. The drive is getting up to 99% of utilization. Now we will move the system01.dbf from the above mentioned location to a new location in /oracle/hdb1/oradata/mydb location. /oracle/hdb1 is mounted from /dev/hdb1. The drive is nearly empty, that’s why I chose it.
Now for the real moving part, we will perform the following steps:
Login to SQL* Plus and shutdown the database
Logout from SQL* Plus and move the files from the source directory to destination directory.
Login to SQL* Plus as /nolog
Connect as SYSDBA to the database to an idle instance.
Issue startup mount command to bring up the database.
Issue ALTER DATABASE RENAME command to rename the data files from the source directory to destination directory.
Now finally open the database.
The above mentioned are the brief steps by which we can achieve our goal. Now to demonstrate see the following commands as well so that it serves as a live example:

Step 1
oracle@astrn10: sqlplus /nolog
SQL> conn /as sysdba
SQL> shutdown immediate;
Troubleshooting: If you are not able to get the SQL prompt, check your ORACLE_HOME, and ORACLE_SID.
Step 2
SQL> exit;
oracle@astrn10: mv /oracle/oradata/mydb/system01.dbf /oracle/hdb1/oradata/mydb/
Now check whether the file have been moved or not by issuing the following command:
oracle@astrn10: ls /oracle/hdb1/oradata/mydb/
total 429924352
-rw-r----- 1 oracle oinstall 429924352 Feb 12 11:00 system01.dbf
Now we are ready for the next step.
Step 3
oracle@astrn10: sqlplus /nolog
Step 4
SQL> conn /as sysdba
Connected to idle instance.
Step 5
SQL> startup mount;
Database mounted.
Step 6
SQL> alter database rename file '/oracle/oradata/mydb/system01.dbf' to '/oracle/hdb1/oradata/mydb/system01.dbf';
Database altered.
Step 7
SQL> alter database open;
Database opened.

That’s all. We are done with our agenda for moving data files from one drive to another. If this where Windows/any other operating system, then copy files as per your operating system commands/interface in Step 2.
In order to copy more files (in this demonstration we have moved only one file), then repeat Step #2 and Step # 6 for each file.
#End of tip


Running out of space? Want to move Oracle Datafiles? – Ask Anantha


Oracle Locks

February 21, 2012 Leave a comment


Query to identify the owner, object, object type, sid, serial number, status, OS user and machine to track locks:

select c.owner, c.object_name, c.object_type,b.sid, b.serial#, b.status, b.osuser, b.machine, a.session_id
 from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;

More Queries:

s1.username || '@' || s1.machine 
|| ' ( SID,S#=' || s1.sid || ',' || s1.serial# || ' ) is blocking '
|| s2.username || '@' || s2.machine
|| ' ( SID,S#=' || s2.sid || ',' || s2.serial# || ' )'
AS blocking_status
v$lock l1,
v$session s1,
v$lock l2,
v$session s2
s1.sid = l1.sid
and s2.sid = l2.sid
and l1.BLOCK = 1
and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;
 v$locked_object a,dba_objects b
 a.object_id = b.object_id

Kill the session causing lock:

alter system kill session 'SID,SERIAL';
Eg: alter system kill session '323,1609';

Source / Reference Links:

Automatic Shared Memory Management – ASMM

February 19, 2012 Leave a comment

The Oracle Automatic Shared Memory Management is a feature that automatically readjusts the sizes of the main pools (db_cache_size, shared_pool_size, large_pool_size, java_pool_size) based on existing workloads for optimal performance.

About Automatic Shared Memory Management:

Automatic Shared Memory Management simplifies SGA memory management. You specify the total amount of SGA memory available to an instance using the SGA_TARGET initialization parameter and Oracle Database automatically distributes this memory among the various SGA components to ensure the most effective memory utilization. When automatic shared memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of a workload without requiring any additional configuration. The database automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory. Oracle Database remembers the sizes of the automatically tuned components across instance shutdowns if you are using a server parameter file (SPFILE). As a result, the system does need to learn the characteristics of the workload again each time an instance is started. It can begin with information from the past instance and continue evaluating workload where it left off at the last shutdown.

The benefits of ASMM are:

  • Reduces the change of running out of shared pool memory
  • Uses available memory optimally
  • Improves database performance by constantly matching memory allocations and instance memory needs

Based on workload information, automatic shared memory tuning:

  • Captures statistics periodically in the background
  • Uses the different memory advisories
  • Performs ?what-if? analyses to determine best distribution of memory
  • Moves memory to where it is most needed
  • Has no need to configure parameters for the worst-case scenario
  • Resurrects component sizes from last shutdown if SPFILE is used

Oracle Automatic Shared Memory Management is enabled by setting:

  • a spfile used to specify init.ora values
  • sga_target parameter is set to a non-zero value
  • statistics_level parameter set to to TYPICAL (the default) or ALL (The other value of this parameter is BASIC, which will not allow changing the memory pools automatically.)
  • shared_pool_size must be set to a non-zero value

Oracle10g has introduced special double underscore hidden parameter to control ASMM:

  • __db_cache_size
  • __shared_pool_size
  • __large_pool_size

Once enabled, Oracle ASMM will morph the pool areas within the confines of sga_max_size.

When ASMM is enabled, then the following memory pools are automatically sized:

1 .Buffer cache (DB_CACHE_SIZE)
2. Shared pool (SHARED_POOL_SIZE)
3. Large pool (LARGE_POOL_SIZE)
4. Java pool (JAVA_POOL_SIZE)

The following pools are manually sized components and are not affected by ASMM.

3. DB_nK_CACHE_SIZE (non-default block size)

Reference Links:

How to Find Sessions Generating Lots of Redo

February 14, 2012 Leave a comment

When a transaction generates undo, it will automatically generate redo as well.

Methods to find sessions generating lots of redo:

1) Query V$SESS_IO.

This view contains the column BLOCK_CHANGES which indicates how much blocks have been changed by the session. High values indicate a session generating lots of redo.

SQL> SELECT s.sid, s.serial#, s.username, s.program,
 FROM v$session s, v$sess_io i
 WHERE s.sid = i.sid
 ORDER BY 5 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of BLOCK_CHANGES. Large deltas indicate high redo generation by the session.


These view contains information about the amount of undo blocks and undo records accessed by the transaction (as found in the USED_UBLK and USED_UREC columns).

SQL> SELECT s.sid, s.serial#, s.username, s.program,
 t.used_ublk, t.used_urec
 FROM v$session s, v$transaction t
 WHERE s.taddr = t.addr
 ORDER BY 5 desc, 6 desc, 1, 2, 3, 4;

Run the query multiple times and examine the delta between each occurrence of USED_UBLK and USED_UREC. Large deltas indicate high redo generation by the session.

You use the first query when you need to check for programs generating lots of redo when these programs activate more than one transaction. The latter query can be used to find out which particular transactions are generating redo.


Materialized View

February 14, 2012 Leave a comment
Categories: Materialized View

Materialized View Refresh

February 13, 2012 Leave a comment

Check refresh mode, refresh method and last refresh time from USER_MVIEWS:

SELECT mview_name, refresh_mode, refresh_method,
 last_refresh_type, last_refresh_date
 FROM user_mviews;

Change of refresh behaviour in 10g

Upgrading from Oracle 9i to Oracle 10g will change the MV refresh behaviour. Oracle 10g will use the DELETE command to remove rows and a normal INSERT to repopulate it. In Oracle 9i and earlier releases, Oracle did a TRUNCATE and INSERT /*+APPEND*/, which is more efficient, but had the side effect that users will see no rows while the refresh is taking place.

If you prefer the older truncate/append behaviour, change the refresh method to set atomic_refresh = false. Here is an example:

 -- use this with 10g/11g to return to truncate/append behavior
 dbms_mview.refresh('MY_TEST_MV', method=>'C', atomic_refresh=>false);


Atomic_refresh parameter is to allow either all of the tasks of a transaction are performed or none of them (Atomicity). If set to TRUE, then all refreshes are done in one transaction. If set to FALSE, then the refresh of each specified materialized view is done in a separate transaction.If set to FALSE, Oracle can optimize refresh by using parallel DML and truncate DDL on a materialized views.

If you set that to FALSE, it’ll do a truncate + insert /*+ append */ on a FULL refresh.

When atomic_refresh=>true, Oracle performs deleting from MView table.
When atomic_refresh=>false, Oracle truncates the MView table.

Atomic refresh does a

a) truncate (data disappears right away, poof)
b) insert /*+ APPEND */ – direct path load, which maintains indexes
c) commits (data reappears)

The indexes won’t/don’t go unusable, but the materialized view “disappears” for the duration of the refresh.

Source / Reference Links: