dNFS and ASM/Filesystem

An Oracle ASM disk group can be created from NFS files, including Oracle Direct NFS (dNFS), as well as whole disks, partitions, and LUNs. The NFS files that are provisioned to a disk group may be from multiple NFS servers to provide better load balancing and flexible capacity planning.

Direct NFS can be used to store data files, but is not supported for Oracle Clusterware files. To install Oracle Real Application Clusters (Oracle RAC) on Windows using Direct NFS, you must also have access to a shared storage method other than NFS for Oracle Clusterware file


Advantages of 'HAVING' ASM with dNFS --

1. If we remove ASM then DBA has no or very less control over storage. He can't even see how much is the free space left as physical level.
2. Stripping option is there to gain performance benefits
3. Multiplexing has benefits over mirroring when it comes to recovery.

(e.g, suppose a database is created with only 1 controlfile as external mirroring is in place at storage level , and another database is created with 2 copies (multiplexed within Oracle level), and an rm command was issued to remove that file then definitely there will be a time difference between restoring the file back.)


"Please also note that ASM is not required for using Direct NFS and NAS. ASM can be used if customers feel that ASM functionality is a value-add in their environment. " ------How to configure ASM on top of dNFS disks in 11gR2 (Doc ID 1570073.1) 

Oracle Direct NFS (dNFS) is an optimized NFS (Network File System) client that provides faster and more scalable access to NFS storage located on NAS storage devices (accessible over TCP/IP). Direct NFS is built directly into the database kernel.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The advantages of ASM are well known --

1. Easier administration for DBA, as using this 'layer', we know the storage very well.
2. automatic re-balancing and dynamic reconfiguration.
3. Stripping and mirroring (though we are not using this option in our env, external redundancy is provided at storage level).
4. Less (or no) dependency on storage admin for DB file related tasks.
5. Oracle also recommends to use ASM rather than file system storage.

 Advantages of DNFS(Direct Network File System) ---

1. Oracle bypasses the OS layer, directly connects to storage.
2. Better performance as user's data need not to be loaded in OS's kernel.
3. It load balances across multiple network interfaces in a similar fashion to how ASM operates in SAN environments.

Now if we combine these 2 options , how will be that configuration in terms of administration/manageability/performance/downtime in future in case of migration.

I have collected some points.

In favor of 'NOT' HAVING ASM--

1. ASM is an extra layer on top of storage so if using dNFS ,this layer should be removed as there are no performance benefits.
2. store the data in file system rather than ASM.
3. Stripping will be provided  at storage level (not very much sure about this).
4. External redundancy is being used at storage level so its better to remove ASM. 


How To Setup DNFS (Direct NFS) On Oracle Release 11.2 (Doc ID 1452614.1) 

Troubleshooting tfa issues

Check the tfa process on both the nodes
ps -ef | grep tfa

Verify TFA runtime status
# /u01/app/grid/tfa/bin/tfactl print status

Check for errors:
# /u01/app/grid/tfa/bin/tfactl print errors

Check for database startups:
# /u01/app/grid/tfa/bin/tfactl print startups

Restart TFA
# /etc/init.d/init.tfa  restart

Stop TFAMain process and removes related inittab entries
# /etc/init.d/init.tfa shutdown
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

To list hosts
tfactl print hosts

To add the host successfully, try the following steps:
1. Get the cookie in node1 using:
./tfa_home/bin/tfactl print cookie
2. Set the cookie from Step 1 in node2 using:
  ./tfa_home/bin/tfactl set cookie=
3. After Step 2, add host again:
 ./tfa_home/bin/tfactl host add node2

Verify TFA runtime status
tfactl print status

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

By default TFA will try to use ports 5000 to 5005 for secure root communications however if that port range is not
available on your system this can be changed.

The file TFA_HOME/internal/usuableports.txt when installed looks like this.

#cat /opt/oracle/tfa/tfa_home/internal/usableports.txt
5000
5001
5002
5003
5004
5005

To change the ports TFA will try to use you need to:-
1) Stop TFA on all nodes
2) Edit usableports.txt to replace the ports you wish tfa to use.
3) Remove the file tfa_home/internal/port.txt on all nodes.
4) Start TFA on all nodes.

Note: The usableports.txt file must be identical on all nodes.

TFA will also use one of ports 5006 to 5011 on the loopback interface for non root communications however
if that port range is not available on your system then this can also be changed.

The file TFA_HOME/internal/NonRootusuableports.txt when installed looks like this.

#cat /opt/oracle/tfa/tfa_home/internal/usableNonRootports.txt
5006
5007
5008
5009
5010
5011

To change the ports TFA will try to use you need to:-
1) Stop TFA on all nodes
2) Edit usableports.txt to replace the ports you wish tfa to use.
3) Remove the file tfa_home/internal/NonRootports.txt on all nodes.
4) Start TFA on all nodes.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
re-installation with -local will resolve the adding the hosts.

Small Notes on Clusterware resource ora.cvu

ora.cvu is a new resource introduced with Grid Infrastructure 11.2.0.2. The purpose of this resource is to invoke clusterware health checks at regular intervals. It is a singleton resource with cardinality of 1 and invokes the cluster verification utility. It executes the following command in the background.
cluvfy comp health -_format
Attribute '_format' sets the format which is redirected to the log file of the ora.cvu
Logs:
The logs related to ora.cvu are located under /log/cvu/.
There are two directories underneath: one for log file s(cvulog) and one for trace files (cvutrc).

Monitoring/Managing cvu:
-- status of the resource ora.cvu
crsctl stat res ora.cvu -t

-- Stopping cvu
srvctl stop cvu
srvctl status cvu

--Starting cvu
srvctl start cvu
srvctl status cvu

-- enable/disable ora.cvu
srvctl enable cvu

-- check the status
crsctl stat res ora.cvu -p | grep ENABLED

-- disable ora.cvu
srvctl disable cvu

-- change execution frequency of ora.cvu
The frequency of ora.cvu execution can be changed by changing the value of the attribute "CHECK_INTERVAL"

crsctl modify resource ora.cvu -attr CHECK_INTERVAL=21600
crsctl stat res ora.cvu -p

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
cvu res dir will have .xml and .txt files (it will create up to 5 .txt and/or .xml files in that directory), generated at every 6hrs normally by ora.CVU resource. Due to code bug, these files are not getting purged; you might be hitting the bug 19703199  with a workaround to delete the files (or apply the patch)

To purge WRH$_SQL_PLAN table (old awr reports)

From the diag0 trace logs, WRH$_SQL_PLAN table may cause the database hangs. Please purge the table after taking a backup of any good sql plans (if any).

1> Please keep a backup of good sql plans so that you can restore it after purging WRH$_SQL_PLAN table

2> Please use one of the option to Purge the WRH$_SQL_PLAN table (Doc ID 1478615.1).

OPTION 1
========

Try executing the dbms_workload_repository.purge_sql_details() as per below:

First count the number in WRH$_SQL_PLAN table.

SQL> select count(*) from sys.wrh$_sql_plan;

SQL>SELECT dbid FROM v$database;

SQL> exec dbms_workload_repository.purge_sql_details(1000, &dbid);               --------------------------- Enter the dbid from above query.

SQL> select count(*) from sys.wrh$_sql_plan;


See if this deletes any rows from the WRH$_SQL_PLAN table. It is needed that you shrink the table and check the table size in the end to reclaim the space.

OPTION 2
========


Another option is to remove the old wrh$_sql_plan data by running simple "delete from wrh$_sql_plan where snap_id between x and y" manually (delete little by little). After deletion of old records from wrh$_sql_plan table, the table needs to be shrinked.  Then check the table size in the end.

Below SQLs will help in identifying the range of snapshots that can deleted from WRH$_SQL_PLAN.

SQL> select snap_id ,timestamp from  wrh$_sql_plan where snap_id=(select min(snap_id) from  wrh$_sql_plan);

The above query gives the oldest available data in table WRH$_SQL_PLAN.

This data could be older than the last available snapshot in the AWR repository.

SQL> select dbid,SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where SNAP_ID=&snap_id;The above query gives the oldest available AWR data from dba_hist_snapshot view.

Example for the delete process:

SQL> select min(TIMESTAMP) from wrh$_sql_plan;
MIN(TIMESTAMP)
------------------
06-JUN-08

1 row selected.

SQL> select min(BEGIN_INTERVAL_TIME) from dba_hist_snapshot;
MIN(BEGIN_INTERVAL_TIME)
---------------------------------------------------------------------------
31-JUL-12 12.00.24.903 AM

SQL> select count(*) from sys.wrh$_sql_plan;
COUNT(*)
----------
27147

SQL> delete from wrh$_sql_plan where trunc(TIMESTAMP) < (select min(BEGIN_INTERVAL_TIME) from dba_hist_snapshot);
7449 rows deleted.

SQL> commit;
Commit complete.

SQL> select min(TIMESTAMP) from wrh$_sql_plan;
MIN(TIMESTAMP)
------------------
01-AUG-12

1 row selected.

SQL> select min(BEGIN_INTERVAL_TIME) from dba_hist_snapshot;
MIN(BEGIN_INTERVAL_TIME)
---------------------------------------------------------------------------
31-JUL-12 12.00.24.903 AM

1 row selected.

Delete the older snap_ids from the table to reclaim space in the table. Then shrink the table after delete to reclaim the space.

Also, set the purging policy as per the Database usage.

UNDO TUNING : ORA-01555

ORA-01555 caused by SQL statement below (SQL ID: fdxcyoin67ty8t, Query Duration=380128 sec, SCN: 0x0229.ff00afd0):
 
Following are the existing settings

Code:

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     96000
undo_tablespace                      string          undo


SQL> select RETENTION from dba_tablespaces where TABLESPACE_NAME='UNDO';

RETENTION
-----------
NOGUARANTEE

SQL> select distinct AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME like 'UNDO%';

AUT
---
NO

SQL> select sum(bytes)/1024/1024  "size_in_mb" from dba_data_files where tablespace_name='UNDO';

SIZE_IN_MB
--------------------
  216511


Following are the details from v$undostat

Code:

select begin_time, end_time, undotsn, undoblks, maxquerylen, maxqueryid, activeblks, unexpiredblks, expiredblks, tuned_undoretention from v$undostat
where trunc(begin_time)=trunc(sysdate)-1 order by begin_time;

BEGIN_TIME     END_TIME          UNDOTSN   UNDOBLKS MAXQUERYLEN MAXQUERYID    ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
-------------- -------------- ---------- ---------- ----------- ------------- ---------- ------------- ----------- -------------------
21-04-13 00:08 21-04-13 00:18          1      12733      378446 duqnawh32hp4u      91152       7068448      225440              345600
21-04-13 00:18 21-04-13 00:28          1       8951      379047 duqnawh32hp4u      99344       7072800      225440              345600
21-04-13 00:28 21-04-13 00:38          1      14073      379650 duqnawh32hp4u      90128       7075872      234656              345600
21-04-13 00:38 21-04-13 00:48          1       6109        2651 2hgnkv6xxncmq      90128       6919328      399520              345600
21-04-13 00:48 21-04-13 00:58          1      10088        3253 2hgnkv6xxncmq      89104       6768928      525472              345600
21-04-13 00:58 21-04-13 01:08          1      69593        3854 2hgnkv6xxncmq      90128       6664736      564384              345600
21-04-13 01:08 21-04-13 01:18          1     163304        4457 2hgnkv6xxncmq     122896       6753824      541856              345600
21-04-13 01:18 21-04-13 01:28          1     172593        5058 2hgnkv6xxncmq     121872       6909472      482464              345600
21-04-13 01:28 21-04-13 01:38          1     171075        5660 2hgnkv6xxncmq     119824       7080480      480416              345600
21-04-13 01:38 21-04-13 01:48          1      84882        6262 2hgnkv6xxncmq      98320       7270048      478368              345600
21-04-13 01:48 21-04-13 01:58          1       1622        6863 2hgnkv6xxncmq      72720       7330464      476320              345600
21-04-13 01:58 21-04-13 02:08          1       2075        6335 2hgnkv6xxncmq      64528       7337632      477344              345600
21-04-13 02:08 21-04-13 02:18          1       1491         236 a4mhnydhzsv0y      64528       7336608      478368              345600
21-04-13 02:18 21-04-13 02:28          1        884         795 62yh0pbb4p9f2      63504       7325472      490656              345600


Following are the details in AWR report (00:00 til 01:00 of 21-Apr-2013) .... not thet the error was produced at 00:42

Code:

Undo Segment Summary                DB/Inst: DBCPY/dbcpy01  Snaps: 18853-18854
- Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
- STO - Snapshot Too Old count,  OOS - Out of Space count
- Undo segment block stats:
- uS - unexpired Stolen,   your - unexpired Released,   uU - unexpired reUsed
- eS - expired   Stolen,   eR - expired   Released,   eU - expired   reUsed

Undo   Num Undo       Number of  Max Qry   Max Tx Min/Max   STO/     uS/uR/uU/
 TS# Blocks (K)    Transactions  Len (s) Concurcy TR (mins) OOS      eS/eR/eU
---- ---------- --------------- -------- -------- --------- ----- --------------
   1      156.1          41,962  379,650       38 5760/5760 1/0   0/0/0/0/0/0
          -------------------------------------------------------------

    Undo Segment Stats                   DB/Inst: DBCPY/dbcpy01  Snaps: 18853-18854
- Most recent 35 Undostat rows, ordered by Time desc

                Num Undo    Number of Max Qry  Max Tx Tun Ret STO/    uS/uR/uU/
End Time          Blocks Transactions Len (s)   Concy  (mins) OOS     eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- ------------
21-Apr 01:08      69,593        4,871   3,854      37   5,760 0/0   0/0/0/0/0/0
21-Apr 00:58      10,088        7,261   3,253      37   5,760 0/0   0/0/0/0/0/0
21-Apr 00:48       6,109        4,110   2,651      35   5,760 1/0   0/0/0/0/0/0
21-Apr 00:38      14,073        4,168 #######      36   5,760 0/0   0/0/0/0/0/0
21-Apr 00:28       8,951        4,031 #######      36   5,760 0/0   0/0/0/0/0/0
21-Apr 00:18      12,733        5,979 #######      36   5,760 0/0   0/0/0/0/0/0
21-Apr 00:08      34,602       11,542 #######      38   5,760 0/0   0/0/0/0/0/0
          -------------------------------------------------------------
   


Undo Advisor information taken 'now' is as following

Code: [Select all] [Show/ hide]

SQL> select dbms_undo_adv.longest_query(sysdate-2,sysdate) from dual;

DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-2,SYSDATE)
----------------------------------------------
                                        379650

SQL> select dbms_undo_adv.required_retention from dual;

REQUIRED_RETENTION
------------------
            379650

SQL> select dbms_undo_adv.best_possible_retention from dual;

BEST_POSSIBLE_RETENTION
-----------------------
                1169169

SQL> select dbms_undo_adv.required_undo_size(379650) from dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(379650)
----------------------------------------
                                   80655

SQL> select dbms_undo_adv.required_undo_size(379650,sysdate-3,sysdate) from dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(379650,SYSDATE-3,SYSDATE)
----------------------------------------------------------
                                                     56028

In above situation what should be my first choice (assuming increasing space is not an issue) - increase undo tablespace or increase undo retention?

If latter is the choice then what should be the value?
Because as I understand present 96000 value is taken as lower limit and because of auto tuning the actual value (TUNED_UNDORETENTION) being used was 345600
In that case shall I set it to something > max(maxquerylen) i.e 379,650 + X?
Or I shall increase the undo tablespace size?

From Undo Advisor output it looks to me that even if I increase the undo retention to 379650 current undo size will be able to support it (may be at the expense of DMLs)
Is that right?


~~~~~

General calculation :-

TUNED_UNDORETENTION = MAXQUERYLEN + 300 Sec.

If UNDO_RETENTION is set > 0, the value specified acts as a minimum setting.

If UNDO_MANAGEMENT=AUTO automatic undo tuning will be used.
MMON will calculate the MAXQUERYLEN every 30 sec interval. Based upon the MAXQUERYLEN, MMON decides the
TUNED_UNDORETENTION. That means the new UNDO RETENTION will be set to TUNED_UNDORETENTION.

For optimal undo management, rather than
tuning based on 100% of the tablespace size, the database tunes the undo retention period based
on 85% of the tablespace size.

Automatic tuning will help to avoid ORA-01555, but if your UNDO tablespace has autoextend off, then you might get into a situation
where active DML needs more space--not reusing expired UNDO segments. The database will be under space pressure and Oracle will give
higher priority to finishing the DML, and not queries.

So Better set Auto

~~~~~~~~~~
_____________________________________________________________________________________________

Data Concurrency and Read Consistency

ROLLBACK or UNDO is the backbone of the READ CONSISTENCY mechanism provided by Oracle. Multi-User Data Concurrency and Read Consistency mechanism make Oracle stand tall in Relational Database Management Systems (RDBMS) world.

Starting in Oracle9i, rollback segments are re-named undo logs. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was made available for overlaying.

Best of all, automatic undo management allows the DBA to specify how long undo information should be retained after commit, preventing “snapshot too old” errors on long running queries.

This is done by setting the UNDO_RETENTION parameter.  The default is 900 seconds (5 minutes), and you can set this parameter to guarantee that Oracle keeps undo logs for extended periods of time.

Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle take care of the rest. Turning on automatic undo management is easy.  All you need to do is create an undo tablespace and set UNDO_MANAGEMENT = AUTO.

UNDO_RETENTION is a parameter in the init.ora initialization parameters file that specifies the time period in seconds for which a system retains undo data for committed transactions. The flashback query can go upto the point of time specified as a value in the UNDO_RETENTION parameter.

The properties of the UNDO_RETENTION parameter are mentioned below:

    Parameter type – Integer
    Default value – 900
    Range of values – 0 to 232 – 1
    Syntax – ALTER SYSTEM SET UNDO_RETENTION = < seconds >;

However it is worth to tune the following important parameters

1. The size of the UNDO tablespace
2. The UNDO_RETENTION parameter

Calculate UNDO_RETENTION for given UNDO Tabespace

You can choose to allocate a specific size for the UNDO tablespace and then set theUNDO_RETENTION parameter to an optimal value according to the UNDO size and the database activity. If your disk space is limited and you do not want to allocate more space than necessary to the UNDO tablespace, this is the way to proceed. The following query will help you to optimize the UNDO_RETENTION parameter:

Optimal Undo Retention =
           Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time.
Actual Undo Size

SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
       v$tablespace b,
       dba_tablespaces c
WHERE c.contents = 'UNDO'
   AND c.status = 'ONLINE'
   AND b.name = c.tablespace_name
   AND a.ts# = b.ts#;

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) "UNDO_BLOCK_PER_SEC"
FROM v$undostat;

DB Block Size

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';

Optimal Undo Retention Calculation

Formula:
Optimal Undo Retention =
           Actual Undo Size / (DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC)

Using Inline Views, you can do all calculation in one query

SQL Code:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
    SUBSTR(e.value,1,25)    "UNDO RETENTION [Sec]",
    ROUND((d.undo_size / (to_number(f.value) *
    g.undo_block_per_sec)))"OPTIMAL UNDO RETENTION [Sec]"
  FROM (
       SELECT SUM(a.bytes) undo_size
          FROM v$datafile a,
               v$tablespace b,
               dba_tablespaces c
         WHERE c.contents = 'UNDO'
           AND c.status = 'ONLINE'
           AND b.name = c.tablespace_name
           AND a.ts# = b.ts#
       ) d,
       v$parameter e,
       v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))undo_block_per_sec
       FROM v$undostat
       ) g
WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'

Calculate Needed UNDO Size for given Database Activity

If you are not limited by disk space, then it would be better to choose theUNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Formula:
Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC

Using Inline Views, you can do all calculation in one query

SQL Code:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
       g.undo_block_per_sec) / (1024*1024)
      "NEEDED UNDO SIZE [MByte]"
  FROM (
       SELECT SUM(a.bytes) undo_size
         FROM v$datafile a,
              v$tablespace b,
              dba_tablespaces c
        WHERE c.contents = 'UNDO'
          AND c.status = 'ONLINE'
          AND b.name = c.tablespace_name
          AND a.ts# = b.ts#
       ) d,
      v$parameter e,
      v$parameter f,
       (
       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
         undo_block_per_sec
         FROM v$undostat
       ) g
 WHERE e.name = 'undo_retention'
  AND f.name = 'db_block_size'

Automatic Undo Retention Tuning

Oracle 10g automatically tunes undo retention to reduce the chances of “snapshot too old”errors during long-running queries. The UNDO_RETENTION parameter is used to set a low retention time threshold which the system will attempt to achieve. In the event of any undo space constraints the system will prioritize DML operations over undo retention meaning the low threshold may not be achieved. If the undo retention threshold must be guaranteed, even at the expense of DML operations, the RETENTION GUARANTEE clause can be set against the undo tablespace during or after creation:
Reset the undo low threshold.

ALTER SYSTEM SET UNDO_RETENTION = 2400;

Guarantee the minimum threshold is maintained.

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

SELECT tablespace_name, retention FROM dba_tablespaces;

TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       GUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY

5 rows selected.

Switch back to the default mode.

ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

TABLESPACE_NAME                RETENTION
------------------------------ -----------
SYSTEM                         NOT APPLY
UNDOTBS1                       NOGUARANTEE
SYSAUX                         NOT APPLY
TEMP                           NOT APPLY
USERS                          NOT APPLY

5 rows selected.

The Undo Advisor PL/SQL Interface

Oracle Database provides an Undo Advisor that provides advice on and helps automate the establishment of your undo environment. You activate the Undo Advisor by creating an undo advisor task through the advisor framework. The following example creates an undo advisor task to evaluate the undo tablespace. The name of the advisor is ‘Undo Advisor‘. The analysis is based on Automatic Workload Repository snapshots, which you must specify by setting parameters START_SNAPSHOT and END_SNAPSHOT. In the following example, the START_SNAPSHOT is “1” and END_SNAPSHOT is “2”.

DECLARE
   tid    NUMBER;
   tname  VARCHAR2(30);
   oid    NUMBER;
   BEGIN
   DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
   DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 1);
   DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 2);
   DBMS_ADVISOR.execute_task(tname);
end;

_____________________________________________________________________________________________

To check the optimum size, please excute this query:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.STATUS = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';

Cleanup orphaned datapump jobs from DBA_DATAPUMP_JOBS:

Step 01: Check the orphaned datapump jobs.

sqlplus / as sysdba
SET lines 140
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE owner_name
COL job_mode LIKE owner_name
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
RANA SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA NOT RUNNING 0
RANA SYS_EXPORT_SCHEMA_02 EXPORT SCHEMA NOT RUNNING 0

Step 02: Check the state field. For orphaned jobs the state will be NOT RUNNING. So from the output we can say both are orphaned jobs.

Step 03: Drop the master table.

DROP TABLE RANA.SYS_EXPORT_SCHEMA_01;
DROP TABLE RANA.SYS_EXPORT_SCHEMA_02;

Step 04: Check for existing data pump jobs by query issued in step 01. If objects are in recyclebin bin then purge the objects from the recyclebin.

SQL> SELECT owner_name, job_name, operation, job_mode,
 state, attached_sessions
 FROM dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ---------- ---------- ------------ -----------------
RANA BIN$xMNQdACzQ6yl22kj EXPORT SCHEMA NOT RUNNING 0
 9U0B8A==$0
RANA BIN$BmUy4r5MSX+ojxFk EXPORT SCHEMA NOT RUNNING 0
 sw8ocg==$0

SQL> PURGE TABLE RANA.SYS_EXPORT_SCHEMA_01;
Table purged.

SQL> PURGE TABLE RANA.SYS_EXPORT_SCHEMA_02;
Table purged.

Check if there is any orphaned jobs again.

SQL> SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;
no rows selected

Step 05: In this stage you did not get any orphaned jobs if the jobs have a master table. If there are still jobs listed in dba_datapump_jobs do cleanup process like below.

SET serveroutput on
SET lines 100
DECLARE
 job1 NUMBER;
BEGIN
 job1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_01','RANA');
 DBMS_DATAPUMP.STOP_JOB (job1);
END;
/
DECLARE
 job2 NUMBER;
BEGIN
 job2 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_SCHEMA_02','RANA');
 DBMS_DATAPUMP.STOP_JOB (job2);
END;
/

Rhel6 Vs oel6

The differences between Oracle Linux and Red Hat Enterprise Linux are mostly differences in licensing and support.

Both companies use a different mechanism to count the number of installed instances.

Secondly Oracle Linux offers the choice of several types of kernel. you can have the same kernel Red Hat ships or you can have one of two types of kernels modified by Oracle.

Oracle's Unbreakable kernel includes some additional drivers (e.g. for SSD disks) that are not available in Red Hat's kernel. Because of these additions Oracle claims significant performance gains.

Off course you need to consider how much value you attach to such marketing claims and whether or not the situation applies to your environment. I.e. do you use SSD disks and infiband messaging?

On the other hand Oracle does IMHO not provide a real alternative for Red Hat Network Satellite server. That was a big influence in our decission to run our new Oracle Databases on standard RHEL servers. But again how important this is depends on your environment. Oracle Enterprise Manager has been extended with some management features.

With Oracle Linux, there are some bonus features that differentiate it from RHEL:

1.  Unbreakable Kernel (Optimized OLTP, InfiniBand, and SSD disk access,  NUMA-optimizations, Reliable Datagram Sockets (RDS), async I/O, OCFS2,  and networking)
2. OCFS2 (Clustered file system)
3. Ksplice (Hot Kernel Patching without rebooting)
4. Dtrace (Ported from Solaris)
6. Cheaper support costs than RHEL

Files for dns and dhcp

# Public
192.9.201.183    rac1.shilpa.com        rac1
192.9.201.187    rac2.shilpa.com        rac2

#Private
192.9.201.173    rac1-priv.shilpa.com        rac1-priv
192.9.201.187    rac2-priv.shilpa.com        rac2-priv

#Virtual
192.9.201.173    rac1-vip.shilpa.com        rac1-vip
192.9.201.177    rac2-vip.shilpa.com        rac2-vip

#Scan
192.9.201.163    rac-scan.shilpa.com        rac-scan
192.9.201.167    rac-scan.shilpa.com        rac-scan
192.9.201.169    rac-scan.shilpa.com        rac-scan

Netmask:255.255.255.0
Default gateway IP : 192.9.201.2
Primary DNS Server : 192.9.201.59
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[root@server1 Desktop]# cat /var/named/forward.zone
$TTL 1D
@    IN SOA    server1.shilpa.com. root.server1.shilpa.com. (
                    0    ; serial
                    1D    ; refresh
                    1H    ; retry
                    1W    ; expire
                    3H )    ; minimum
        NS    server1.shilpa.com.
server1        A    192.9.201.59
rac1        A    192.9.201.183
rac2        A    192.9.201.187
rac3        A    192.9.201.189
host01        A    192.9.201.23
host02        A    192.9.201.27
rac1-priv    A    10.0.0.1
rac2-priv    A    10.0.0.2
rac3-priv    A    10.0.0.3
cluster01-gns    A    192.9.201.180
    AAAA    ::1
$ORIGIN        cluster01.shilpa.com.
@    IN      NS    cluster01-gns.shilpa.com.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[root@server1 Desktop]# cat /var/named/reverse.zone
$TTL 1D
@    IN SOA    server1.shilpa.com. root.server1.shilpa.com. (
                    0    ; serial
                    1D    ; refresh
                    1H    ; retry
                    1W    ; expire
                    3H )    ; minimum
    NS    server1.shilpa.com.
    A    192.9.201.59
    AAAA    ::1
59    PTR    server1.shilpa.com.
183    PTR    rac1.shilpa.com.
187    PTR    rac2.shilpa.com.
189    PTR    rac3.shilpa.com.
23    PTR    host01.shilpa.com.
27    PTR    host02.shilpa.com.
180    PTR    cluster01-gns.shilpa.com.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[root@server1 Desktop]# cat /var/named/reverse1.zone
$TTL 1D
@    IN SOA    server1.shilpa.com. root.server1.shilpa.com. (
                    0    ; serial
                    1D    ; refresh
                    1H    ; retry
                    1W    ; expire
                    3H )    ; minimum
    NS    server1.shilpa.com.
    A    192.9.201.59
    AAAA    ::1
1    PTR    rac1-priv.shilpa.com.
2    PTR    rac2-priv.shilpa.com.
3    PTR    rac3-priv.shilpa.com.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[root@server1 Desktop]# cat /var/named/chroot/etc/named.conf
//
// named.conf
//
// Provided by Red Hat bind package to configure the ISC BIND named(8) DNS
// server as a caching only nameserver (as a localhost DNS resolver only).
//
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//

options {
    listen-on port 53 { 192.9.201.59; };
#    listen-on-v6 port 53 { ::1; };
    directory     "/var/named";
    dump-file     "/var/named/data/cache_dump.db";
        statistics-file "/var/named/data/named_stats.txt";
        memstatistics-file "/var/named/data/named_mem_stats.txt";
    allow-query     { any; };
    recursion yes;

    dnssec-enable yes;
    dnssec-validation no;
    dnssec-lookaside auto;

    /* Path to ISC DLV key */
    bindkeys-file "/etc/named.iscdlv.key";

    managed-keys-directory "/var/named/dynamic";
};

logging {
        channel default_debug {
                file "data/named.run";
                severity dynamic;
        };
};

zone "." IN {
    type hint;
    file "named.ca";
};

include "/etc/named.rfc1912.zones";
include "/etc/named.root.key";
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[root@server1 Desktop]# cat /etc/named.rfc1912.zones
// named.rfc1912.zones:
//
// Provided by Red Hat caching-nameserver package
//
// ISC BIND named zone configuration for zones recommended by
// RFC 1912 section 4.1 : localhost TLDs and address zones
// and http://www.ietf.org/internet-drafts/draft-ietf-dnsop-default-local-zones-02.txt
// (c)2007 R W Franks
//
// See /usr/share/doc/bind*/sample/ for example named configuration files.
//

zone "shilpa.com" IN {
    type master;
    file "forward.zone";
    allow-transfer { 192.9.201.59; };
};

zone "localhost" IN {
    type master;
    file "named.localhost";
    allow-update { none; };
};

zone "1.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.ip6.arpa" IN {
    type master;
    file "named.loopback";
    allow-update { none; };
};

zone "201.9.192.in-addr.arpa" IN {
    type master;
    file "reverse.zone";
};

zone "0.0.10.in-addr.arpa" IN {
    type master;
    file "reverse1.zone";
};
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[root@server1 Desktop]# cat /etc/dhcp/dhcpd.conf
# dhcpd.conf
#
# Subnet Declaration

subnet 192.9.201.0 netmask 255.255.255.0 {
        option routers                  192.9.201.1;
        option subnet-mask              255.255.255.0;
        option domain-search              "shilpa.com";
        option domain-name-servers       192.9.201.59;
        option time-offset              -18000;     # Eastern Standard Time
#    range 192.9.201.190 192.9.201.254;
}

# Use this to enble / disable dynamic dns updates globally.
ddns-update-style interim;

# If this DHCP server is the official DHCP server for the local
# network, the authoritative directive should be uncommented.
authoritative;

# Use this to send dhcp log messages to a different log file (you also
# have to hack syslog.conf to complete the redirection).
log-facility local6;

# Range Parameter

default-lease-time 1600;
max-lease-time 7200;
option subnet-mask 255.255.255.0;
option routers 192.9.201.1;
option domain-name-servers 192.9.201.59;
option domain-search "shilpa.com";
subnet 192.9.201.0 netmask 255.255.255.0 {
   range 192.9.201.190 192.9.201.254;
}

# Static IP Address Using DHCP

host ns {
   next-server marvin.redhat.com;
   hardware ethernet 12:34:56:78:AB:CD;
   fixed-address 207.175.42.254;
}
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DNS-oel6

Note: Select Network Infrastructure Server while OS Install:
Disable selinux:
/etc/sysconfig/selinux (disabled)
Disable firefox
vi /etc/hosts
setup
service network restart
chkconfig network on
mkdir -p /var/ftp/oel6/
cp /media/O*/Packages /var/ftp/oel6/
cp /media/O*/Server/repodata/* /var/ftp/oel6/Packages/
cp /media/O*/Server/repodata/*comps* /var/ftp/
cp /media/O*/Server/Tr* /var/ftp/oel6/
cp /media/O*/Server/list* /var/ftp/oel6/

vi /etc/sysconfig/ntpd
(-x)
cd /media/O*/Packages/
rpm -iUh vsftpd
rpm -iUh deltarpm*
rpm -iUh createrepo*
service vsftpd restart
chkconfig vsftpd on
service ntpd restart
chkconfig ntpd on
createrepo -vg /var/ftp/*comps* /var/ftp/oel6/Packages
mv /etc/yum.repos.d/pubic-yum-ol6.repo /etc/yum.repos.d/public-yum-ol6.repo.original
vi /etc/yum.repos.d/public-yum-ol6.repo

[Packages]
name=file:///var/ftp/oel6/Packages
baseurl=file:///var/ftp/oel6/Packages/
enabled=1
gpgcheck=0


1) Install Package
yum install bind*

2) Configure IP
#DNS&DHCP Server
eth0 192.9.201.59    server1.shilpa.com
netmask 255.255.255.0

~~~~~~~~~~~~~~~
setup (eth0 + dns)
service network restart
ifconfig
vi /etc/hosts
vi /etc/resolv.conf

vi /etc/named.conf
vi /etc/named.rfc1912.zones
vi /var/named/forward.zone -->localhost
vi /var/named/reverse.zone -->loopback
vi /var/named/reverse1.zone -->loopback
chgrp named forward.zone
chgrp named reverse.zone
chgrp named reverse1.zone
chgrp named named.conf
chgrp named.rfc1912.zones

service named start
chkconfig named on

For errors: /var/log/messages
Other Commands:
vim /etc/sysconfig/network-scripts/ifcfg-eth0
vim /etc/sysconfig/network
dig host.domain.com
dig -x
ifconfig
route
netstat -r -n

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DHCP
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Sep 2 19:43:16 cpanel named[22767]: network unreachable resolving 'ns.isc.afilias-nst.info/A/IN': 2001:500:7::79#53

To fix this you have to start the bind damon with "-4" (IPv4 only). You can add the line "OPTIONS="-4"" to /etc/sysconfig/named to do so.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ps ax | grep dhcp*

kill -9

Notes:

yum install dhcp*

vim /etc/dhcp/dhcpd.conf
 service dhcpd start
chkconfig dhcpd on

Yum Install on OEL6

 *How to Configure yum on server* (with ftp server)

Before configure what will be the output of "yum list" command

Just have a look

# yum list

ommit output...

It will show only the installed packages
Now lets start to configure
before configure the yum first we will install the ftp server

Step 0

Disable selinux:
/etc/sysconfig/selinux (disabled)
Disable firefox
vi /etc/hosts
setup
service network restart
chkconfig network on
mkdir -p /var/ftp/oel6/


Step 1

*install ftp package*
-------------
(here i am asuming the cd/dvd is mounted under /media/oel6)

use following command to install ftp server

# cd /media/O*/Packages/
# rpm -iUh vsftpd*

ommit output...

# service vsftpd start ; chkconfig vsftpd on

ommit output...

Step 2

*install createrepo package*
-----------------------------
use following command to install createrepo package

# cd /media/O*/Packages/
# rpm -iUh deltarpm*
# rpm -iUh createrepo*

ommit output...

Step 3

*copy RHEL DVD into /var/ftp/rhel5/ location*
----------------------------------------------

# cp /media/O*/Packages /var/ftp/oel6/
# cp /media/O*/Server/repodata/* /var/ftp/oel6/Packages/
# cp /media/O*/Server/repodata/*comps* /var/ftp/
# cp /media/O*/Server/Tr* /var/ftp/oel6/
# cp /media/O*/Server/list* /var/ftp/oel6/

ommit output...

Step 4

*Create repository database*
----------------------------

# createrepo -vg /var/ftp/*comps* /var/ftp/oel6/Packages

output ommit...


Step 5:

*Create repository file*
-------------------------

# mv /etc/yum.repos.d/pubic-yum-ol6.repo /etc/yum.repos.d/public-yum-ol6.repo.original

Download Oracle public YUM server configuration file:

# pwd
/etc/yum.repos.d

# wget https://public-yum.oracle.com/public-yum-ol6.repo

# vim /etc/yum.repos.d/public-yum-ol6.repo

[Packages]
name=file:///var/ftp/oel6/Packages
baseurl=file:///var/ftp/oel6/Packages/
enabled=1
gpgcheck=0

[ol6_u3_base]
name=Oracle Linux $releasever Update 3 installation media copy ($basearch)
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL6/3/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
gpgcheck=1
enabled=1

:wq

Now you have done it lets check is it working?

#yum list

output ommit...

it will show list of installed [installed] and not installed [Server] packages.

Have fun!!
--------------------------------

To release session locking database objects

select a.sid, a.serial# from v$session a, v$locked_object b, dba_objects c where b.object_id = c.object_id and a.sid = b.session_id and OBJECT_NAME='REPORTER_STATUS';

alter system kill session 'sid,serial#';



 

RMAN Refresh (POINT IN TIME RECOVERY)

1. The variables that will be used in the document and their explanation are as follows:         

FROM_SERVER        = Where production database is up and running.

TO_SERVER              = Where the duplicate database will be up and running.

FROM_DATABASE  = Production database that has been backed up using RMAN

TO_DATABASE        = The database to be refreshed on

Make sure the filesystems on Production (FROM_SERVER) exactly match the filesystems on the Dev/UAT (TO_SERVER) server.

In case there is a difference in the filesystems layout or directory structure of the source and the target server; contact server team

Make sure there is enough space in /data/oracle//backup to store the backup pieces from .

          Log in to

Make directory to keep the backup pieces of and scripts

           $mkdir –p /data/oracle//backup//script

A.      TO REFRESH FROM CURRENT BACKUP ON

Copy the following files from FROM_SERVER to TO_SERVER using the “scp –p” command.

          Log in to

                - All RMAN backup pieces including archive logs pieces.

                        $ scp –p /data/oracle//backup/database/*rmn  

                            :/data/oracle//backup/           

                             

            - A backup controlfile (*ctl*Z or *ctl*gz) or the file generated from controlfile autobackup (c-*).

                        $ scp –p /data/oracle//backup/database/*ctl*Z

                             :/data/oracle//backup/

                        OR

                        $ scp –p /data/oracle//backup/database/*ctl*gz

                             :/data/oracle//backup/

                       OR

       

                       $scp –p /data/oracle//backup/database/c-*

                         :/data/oracle//backup/

                - A duplicate script (duplicate*.ksh) that dbbackup generates a template to clone database.

                        $ scp –p /data/oracle//backup/database/duplicate*.ksh

                             :/data/oracle//backup/

                - A backup init.ora file.

                        $ scp –p /data/oracle//backup/database/init*

                             :/data/oracle//backup/

               

              - A controlfile creation script generated from dbbackup. It is used to add temporary files after

                open resetlogs.

                   $ scp –p /data/oracle//backup/database/create_control*sql

                             :/data/oracle//backup/

    

   It is important to use "scp -p" to preserve the original file timestamp, when the above files are transferred to the host where RMAN refresh will be implemented.

B.      TO REFRESH FROM PREVIOUS RMAN BACKUP

Restore required RMAN backup files to the from tape

After restore to the “touch” one of the controlfiles on as:

$touch /data/oracle//backup//

2. Four refresh scripts need to be copied to the refresh host from

                                                                      US golden::/export/applications/oracle/rman/refresh

                - refresh.ksh                      - is main script that will be executing RMAN refresh.

                - refresh.begin                  - is one of templates to generate a running script start_refresh.ksh

                - refresh.end                     - is one of templates to generate a running script start_refresh.ksh

               - rman_refresh.config    - is a configuration file needed to be customized

              Log in to US golden and :

               $ scp –p /export/applications/oracle/rman/refresh/refresh* 

                          :/data/oracle//backup//script

               $ scp –p /export/applications/oracle/rman/refresh/ rman_refresh.config

                          :/data/oracle//backup//script

   No modification is required for refresh.ksh, refresh.begin  and refresh.end.

3. Log in to

-          Change directory to /data/oracle//backup//script

-          Update the parameters in rman_refresh.config

               In most cases, we only need to update TO_ORACLE_SID, BACKUP_DIR, BASE_DATA_DIR and

               INIT_ORA_OVERWRITE

               

               TO_ORACLE_SID               - is the database to be refreshed (SID name at TO

                                                               location)

                BACKUP_DIR                      - / data/oracle//backup/

                                                            is the directory where source database backup pieces, init.ora,

                                                             backup controlfile, duplicate script and controlfile creation script

                BASE_DATA_DIR               - is the base directory where the database is to be refreshed.

                INIT_ORA_OVERWRITE  - determines if init.ora is generated from the source

                                                             init.ora (Y if init.ora is created from the backup one.

                                                                   N if existing init.ora is reused, but ensure 

                                                                   UNDO_TABLESPACE uses same tablespace as the source database.

              

   The following parameters need to be modified ONLY IF a special filename or script is used:

              DUPLICATE_SCRIPT                   -  is a duplicate script created by dbbackup.

              CONTROLFILE_SQL                    - is a source database controlfile creation sql script

              BACKUP_CONTROLFILE      - is either a backup controlfile or a file generated from autobackup

   We can take default value for the following parameters:

             CLEANUP                                   - is a flag for housekeeping for refresh process. Turn off CLEANUP for

                                                                debugging.

            TRACE                            - determines whether "set -vx" is enabled or not. Useful for debugging.

              ARCHIVING                             -   determines if the database to be refreshed runs in ARCHIVE/NOARCHIVE

                                                         mode.

            RMAN_TARGET_LOGIN  -  determines how to connect to database instance.

            ABORT_ON_ERROR        - determines whether the refresh script aborts or not when an error occurs.

4.  To perform refresh to the time of backup, go to step 6.
5. To perform point-to-time recovery

               Setting RECOVER_UNTIL_TIME parameter in rman_refresh.config will enable point-to-time

               Recovery.

a.       If the point in time is a time before the backup was taken then:

                            In the rman_refresh.config update the RECOVER_UNTIL_TIME as "MM-DD-YYYY:HH24:MI:SS".

                            Go to step 5. And run the refresh.ksh

b.      If the time to recover is (far) ahead of the previous RMAN backup, it means the backup controlfile or autobackup controlfile from dbbackup may be obsolete. 

The following list steps to make point-to-time recovery work:

                           - Make sure dbbackup version should be 4.09 or higher (can be seen on the second line of

                              backup.log)

                           - Make sure controlfile autobackup is enabled for the . To see the

                             conguration do the following:

                                  Log in to

                                  Log in or sudo – su to

                                  $ rman target /

                                  RMAN> show all;

                                  If not already configured or the value is OFF, give the following command:

                                  RMAN> configure CONTROLFILE AUTOBACKUP ON;

                                  RMAN> exit;

                                    

                                  Run "arc2adsm.ksh -f $ORACLE_SID" forces to perform rman backup for archive logs and

                                  generates new  autobackup controlfile for the source database.

                                 $/data/oracle//admin/DBBACKUP/scripts/arc2adsm.sh –f $ORACLE_SID

                         

                          - Copy latest rman backup pieces and autobackup controlfile to the backup directory on the

                             server using the commands in step 1.

6. Run the refresh.ksh script on the to get the database refreshed on the

-          Log in to

-          Change directory to /data/oracle//backup//script

               $ cd /data/oracle//backup//script

-          Run the script refresh.ksh

               $ nohup refresh.ksh &

-          Monitor the refresh process

               $ tail –f nohup.out

7. Please ensure the log file for the refresh is stored in the following location normally /data/oracle//admin/logs;

            $ORA_ADMIN/logs/refresh_${ORACLE_SID}_$(date +%Y%m%d).log

  

  The potential issues:

        - point-to-time recovery may fail if a tablespace name or data file is dropped because the duplicate

          script from dbbackup becomes obsolete.