RMAN TSPITR

Example on using RMAN TSPITR (Tablespace Point-in-Time Recovery):


Login into your RMAN Catalog by setting up your database environment.

rman catalog rman/password@catalogdb target /

RMAN> configure channel 1 device type sbt_tape parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=adm_p,OB2BARLIST=restore_job)';

RMAN> configure channel 2 device type sbt_tape parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=adm_p,OB2BARLIST=restore_job)';

I am configuring the above channels manually as I had issues RMAN allocating channels for AUXILIARY instance...

run {
SET NEWNAME FOR DATAFILE '/ora05/oradata/db1/abc_ts_01.dbf' TO '/ora03/oradata/db1/abc_ts_01.dbf'; --here I am changing the default location of a datafile to a new location
recover tablespace abc_ts until time "to_date('16-FEB-2013 06:41:00','DD-MON-YYYY HH24:MI:SS')" AUXILIARY DESTINATION '/ora05_1/auxdest'; --this is optional but I personally wanted to give a storage to RMAN for its dynamic Auxiliary instance.
}

The above will restore your tablespace until the time specified.

Once the above is successful, you will notice that tablespace is OFFLINE as Oracle wants you to take a backup of that tablespace and lets you bring that tablespace online.

So, take a backup:

RMAN> backup tablespace abc_ts; --or take a backup from your backup software...

and bring it online:

RMAN> sql 'alter tablespace abc_ts online';

RMAN-12012: multiple records for DISK parallelism found in catalog

While trying to restore (TSPITR) using RMAN Catalog I ran into this wired errors stating while allocating channels...


RMAN> recover tablespace abc123
until time "to_date('19-FEB-2013 00:00:00','DD-MON-YYYY HH24:MI:SS')"
AUXILIARY DESTINATION '/ora05/auxdest';

Starting recover at 20-FEB-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/20/2013 16:43:10
RMAN-12010: automatic channel allocation initialization failed
RMAN-12012: multiple records for DISK parallelism found in catalog

RMAN> exit

Doing some research, found the solution to reset config and try again.

SQL> exec dbms_backup_restore.resetconfig;

Now try again and you will have no issues allocating channels...

Finding Oracle allocated semaphores in Linux...

As I always say, there is always something to know/learn.

Having some issues with high usage of semaphores in our environment and digging more found this nice utility from Oracle (yeah its been there since 8.1 but today I came to know about it) called "sysresv".

If you have access to metalink then here is the ID#123322.1

BTW, you can still get all semaphores allocated by oracle with a simple command:
ipcs -s |grep oracle --This will list out all semaphores allocated for all instances on the server.

sysresv utility gives the list of semaphores only for the instance that you pointed to...

Once you get the semaphore id (semid) try below to get more info on that semid like process id (pid):
ipcs -si semid

This will give detailed info like below:


Semaphore Array semid=331382805
uid=80   gid=80  cuid=80         cgid=80
mode=0660, access_perms=0660
nsems = 128
otime = Tue Feb  5 16:45:46 2013
ctime = Tue Feb  5 16:45:46 2013
semnum     value      ncount     zcount     pid
0                 1          0             0          1315
1             18380      0             0          1315
2             18703      0             0          1315

From the above try to ps -ef |grep pid and see if this shows any OS process that is related to Oracle (in our case). If ps -ef does not return anything then that is a dead process and time clear that semaphore allocation using ipcrm -s semid.

Here is the info about this nice utility if you do not have metalink access:


SYSRESV Utility [ID 123322.1]

The sysresv utility included with Oracle 8.1.5 and above provides instance
status (and OS resources used) for specified ORACLE_SIDs.  This utility is
especially useful when multiple instances are running.  OS resources can be
removed using this utility if the specified instance is detected to be dead.

This utility may be useful when an instance has crashed or was aborted
and memory and semaphores related to this instance were not cleaned up
automatically.  This utility is also helpful in determining which instance
is running.

The sysresv utility, located in $O_H/bin, can be used from locations other
than $O_H/bin.

Point your environment to the instance of interest before using sysresv.

Usage:
------

sysresv:
usage   : sysresv [-if] [-d ] [-l sid1 ...]
          -i : Prompt before removing ipc resources for each sid
          -f : Remove ipc resources silently, oevrrides -i option
          -d : List ipc resources for each sid if on
          -l sid1 .. : apply sysresv to each sid
Default : sysresv -d on -l $ORACLE_SID
Note    : ipc resources are attempted to be deleted for a
          sid only if there is no currently running instance
          with that sid.

 
Examples:
---------

o  Instance is not running:

   /u02/app/oracle/product/8.1.7> sysresv

   IPC Resources for ORACLE_SID "R817" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "R817"


o  Instance is running:

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory:
   ID              KEY
   16437           0xe4efa8dc
   Semaphores:
   ID              KEY
   12320802        0x09d48346
   Oracle Instance alive for sid "X816"


o  Attempting to remove memory and semphores using sysresv when Oracle
   detects an instance is running:

   /u03/app/oracle/product/8.1.6> sysresv -f

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory:
   ID              KEY
   16437           0xe4efa8dc
   Semaphores:
   ID              KEY
   12320802        0x09d48346
   Oracle Instance alive for sid "X816"
   SYSRESV-005: Warning
           Instance maybe alive - aborting remove for sid "X816"


o  Removing IPC resources:

   [Sysresv shows memory and semaphores exist but Oracle determines the
    instance is not alive.  Cleanup is needed.]

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory:
   ID              KEY
   16837           0xe4efa8dc
   Semaphores:
   ID              KEY
   12714018        0x09d48346
   Oracle Instance not alive for sid "X816"


o  Removing IPC resources using sysresv:

   /u03/app/oracle/product/8.1.6> sysresv -i

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "X816"
   Remove ipc resources for sid "X816" (y/n)?y
   Done removing ipc resources for sid "X816"
   /u03/app/oracle/product/8.1.6


   Verify the resources were removed:

   /u03/app/oracle/product/8.1.6> sysresv

   IPC Resources for ORACLE_SID "X816" :
   Shared Memory
   ID              KEY
   No shared memory segments used
   Semaphores:
   ID              KEY
   No semaphore resources used
   Oracle Instance not alive for sid "X816"

   
o  If you need to remove memory segments, and Oracle detects the
   instance is alive through sysresv:

   % ipcrm -m

   Where is the memory id shown in the sysresv output.

   Example:
   % ipcrm -m 16437

   If you need to remove semaphores, and Oracle detects the
   instance is alive through sysresv:

   % ipcrm -s

   where is the semaphore id shown in the sysresv output.

   Example:
   % ipcrm -s 12320802

Faster way to get a row count on a huge table...

We all know how to get a row count of a table:
select count(1)from table_name;

What if the table is so huge!!!
Quick way we generally tend to go around to get the count is from user_tables data dictionary NUM_ROWS column value... wait... that wont give you the accurate number of rows!!! meaning that value is updated in that dictionary when the stats were generated...

Gathering stats to get that value populated is an option? depends on how big the table how long that stats gathering takes place.

So, is there any other way to get number of records faster in a table!!!

Yes...

There is a data dictionary named all_tab_modifications. You can read more about this from the link below (from where i learned this):

http://mwidlake.wordpress.com/2009/05/26/counting-the-cost-4-the-speedy-way/

Basically, once the insert opeartion is done you have to flush the statistics (which is what Oracle does every often) as below:

exec dbms_stats.flush_database_monitoring_info

Once the flush is done you can query all_tab_modifications table for the results:

select dbta.owner||'.'||dbta.table_name tab_name ,dbta.num_rows anlyzd_rows,
       to_char(dbta.last_analyzed,'yymmdd hh24:mi:ss')  last_anlzd,
  nvl(dbta.num_rows,0)+nvl(dtm.inserts,0) - nvl(dtm.deletes,0) tot_rows,
  nvl(dtm.inserts,0)+nvl(dtm.deletes,0)+nvl(dtm.updates,0) chngs,
  nvl(dtm.inserts,0)+nvl(dtm.deletes,0)+nvl(dtm.updates,0)) / greatest(nvl(dbta.num_rows,0),1) pct_c,
  dtm.truncated trn
  from dba_tables dbta
  -- replace below with all_tab_modifications if you need
       left outer join sys.dba_tab_modifications dtm
  on dbta.owner = dtm.table_owner
     and dbta.table_name = dtm.table_name
          and dtm.partition_name is null
 where dbta.table_name ='PERSON'
   and dbta.owner     ='TRAINER'
/