Murali Oracle DBA

DBPITR abbreviates to Database point in time recovery. When we want to rewind our database back to a previous time then we can use DBPITR to do that.

When we have performed some unwanted changes in our database and we want to undo that, we can perform DBPITR to undo that.

Only perform DBPITR when you are not able to undo the unwanted change with Flashback technology.

Disadvantages:-

1. It’s a time consuming process as whole database has to be restored from the backup and recovered to an earlier time using archive logs and incremental backups.

2. Unlike TSPITR in which only particular tablespace objects are inaccessible whole database is unavailable during this entire process.

3. All changes which are made after the TIME or SCN or log sequence which we used in DBPITR will be lost.

Requirements:-

1. Database has to be in archive log mode.
2. A valid backup of full database is needed and all archive logs or incremental backup after that backup will be required.

##NOTE: - When we have to undo those changes which are present in a different incarnation then DBPITR has to be done because we cannot rewind particular objects to a different incarnation, we have to rewind whole database.

Example:-

Consider we create a table named AMIT and AMIT_BCKUP and insert some records in that. Now accidentally we performed a drop command on table. Now we want to rewind our database to a time before drop operation. PFB steps:-

Recovery Manager11.2.0.1.0
RMAN> Backup database plus archivelog;
Starting backup at 17-MAR-15
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=146 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=59 STAMP=874609124
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
piece handle=D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_ANNNN_TAG20150317T185844_BJJC0F3C_.BKP tag=TAG20150317T185844 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-MAR-15

Starting backup at 17-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\CYBADMIN\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\CYBADMIN\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\CYBADMIN\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00004 name=D:\APP\CYBADMIN\ORADATA\ORCL\USERS01.DBF
input datafile file number=00005 name=D:\APP\CYBADMIN\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
piece handle=D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_NNNDF_TAG20150317T185846_BJJC0H48_.BKP tag=TAG20150317T185846 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
piece handle=D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_NCSNF_TAG20150317T185846_BJJC2VKF_.BKP tag=TAG20150317T185846 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-MAR-15

Starting backup at 17-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=60 STAMP=874609205
channel ORA_DISK_1: starting piece 1 at 17-MAR-15
channel ORA_DISK_1: finished piece 1 at 17-MAR-15
piece handle=D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_ANNNN_TAG20150317T190005_BJJC2XPC_.BKP tag=TAG20150317T190005 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-MAR-15
RMAN> EXIT
Recovery Manager complete.

D:\>sqlplus scott

Pasword:
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

DEPT TABLE

EMP TABLE

MURALI TABLE

TEST TABLE

SQL> drop table Murali purge;
Table dropped.

SQL> drop table test purge;

Table dropped.

SQL> drop table EMP purge;

Table dropped.

SQL> drop table DEPT purge;
Table dropped.

SQL> select * from tab;

no rows selected

SQL> conn sys
Password:
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size                  2176168 bytes
Variable Size            1073744728 bytes
Database Buffers          520093696 bytes
Redo Buffers                7397376 bytes
Database mounted.
SQL> EXIT

D:\app\Backup>rman target sys

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 17 19:04:48 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
target database Password:
connected to target database: ORCL (DBID=1380168819, not open)
RMAN> run
2> {
3> set until time "sysdate-1/24/60*5";
########### We rewind our database 5 mins before###########
#set until scn 10023;
#set until sequence 345 thread 2;
#set until restore point Murali_RESTORE;
#######Murali_RESTORE point already created##########
#####Any one of the above set line we can use###########
4> restore database;
5> recover database;
6> }
Recovery Manager11.2.0.1.0
RMAN> 2> 3> 4> 5> 6>
executing command: SET until clause
Starting restore at 17-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\APP\CYBADMIN\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\APP\CYBADMIN\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\CYBADMIN\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\CYBADMIN\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to D:\APP\CYBADMIN\ORADATA\ORCL\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_NNNDF_TAG20150317T185846_BJJC0H48_.BKP
channel ORA_DISK_1: piece handle=D:\APP\BACKUP\FLASHBACK\ORCL\BACKUPSET\2015_03_17\O1_MF_NNNDF_TAG20150317T185846_BJJC0H48_.BKP tag=TAG20150317T185846
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 17-MAR-15
Starting recover at 17-MAR-15
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-MAR-15
RMAN>
RMAN> alter database open resetlogs;
database opened
RMAN>Exit

D:\app\Backup>sqlplus scott

Password:

SQL> select * from tab;
TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
MURALI                         TABLE
TEST                           TABLE

SQL> select * from DEPT;
    DEPTNO DNAME      LOC
---------- ---------- ----------
        10 ACCOUNTING NEW YORK
        20 RESEARCH   DALLAS
        30 SALES      CHICAGO
        40 OPERATIONS BOSTON
SQL>

The Automatic Diagnostics Repository (ADR) is a hierarchical file-based repository for handling diagnostic information.

Organization

Setting the ADR Location

ADR Command Interpreter (ADRCI)

Viewing the Alert Log

Viewing Trace Files

Managing Diagnostic Information (Purging Trace Files)

Problems and Incidents

Creating Packages to Send to Oracle Support

Cloud Control Integration

The hierarchy of directories within the repository is shown below.

Organization

The Automatic Diagnostics Repository (ADR) is a hierarchical file-based repository for diagnostic information, made up of a directory structure like the following.

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/alert

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/cdump

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/hm

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/incident

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/trace

$ADR_BASE/diag/rdbms/{DB-name}/{SID}/{others}

Typical installations will have the ADR_BASE set to the ORACLE_BASE. For example, a database called "orcl" might have a structure like the following.

/u01/app/oracle/diag/rdbms/orcl/orcl/alert

/u01/app/oracle/diag/rdbms/orcl/orcl/cdump

/u01/app/oracle/diag/rdbms/orcl/orcl/hm

/u01/app/oracle/diag/rdbms/orcl/orcl/incident

/u01/app/oracle/diag/rdbms/orcl/orcl/trace

The locations of the various diagnostics directories can be displayed using the V$DIAG_INFO view.

COLUMN name FORMAT A25

COLUMN value FORMAT A65

SELECT name, value FROM v$diag_info;

NAME VALUE

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

Diag Enabled TRUE

ADR Base /u01/app/oracle

ADR Home /u01/app/oracle/diag/rdbms/orcl/orcl

Diag Trace /u01/app/oracle/diag/rdbms/orcl/orcl/trace

Diag Alert /u01/app/oracle/diag/rdbms/orcl/orcl/alert

Diag Incident /u01/app/oracle/diag/rdbms/orcl/orcl/incident

Diag Cdump /u01/app/oracle/diag/rdbms/orcl/orcl/cdump

Health Monitor /u01/app/oracle/diag/rdbms/orcl/orcl/hm

Default Trace File /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19975.trc

Active Problem Count 0

Active Incident Count 0

11 rows selected.

Setting the ADR Location

For the database, the ADR location is specified using the diagnostic_dest initialisation parameter.

SQL> ALTER SYSTEM SET diagnostic_dest='/u01/app/oracle';

System altered.

SQL> show parameter diagnostic_dest

NAME TYPE VALUE

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

diagnostic_dest string /u01/app/oracle/

SQL>

For the listener, the ADR location is set by editing the "$ORACLE_HOME/network/admin/listener.ora" file to include the following entry.

ADR_BASE_LISTENER = /u01/app/oracle

The listener must be restarted for the change to take effect.

$ lsnrctl stop

$ lsnrctl start

If the "diag" directory is not already present, it will be created when the listener or database are started with the new setting.

ADR Command Interpreter (ADRCI)

You can navigate the contents of ADR using operating systems command line or file browsing tools, but Oracle also provide the ADR Command Interpreter (ADRCI), which is probably a better solution for many tasks.

Assuming your environment is set correctly, you should be able to start ADRCI by issuing the "adrci" command.

$ adrci

ADRCI: Release 11.2.0.3.0 - Production on Mon Jun 9 13:41:29 2014

ADR base = "/u01/app/oracle"

adrci>

When started, you should see the "ADR base =" message. If instead you get a message saying no base is set, you can manually set it using the SET BASE command.

adrci> set base /u01/app/oracle

Typing the HELP command provides a list of help topics related to available commands.

adrci> help

HELP [topic]

Available Topics:

CREATE REPORT

ECHO

EXIT

HELP

HOST

IPS

PURGE

RUN

SET BASE

SET BROWSER

SET CONTROL

SET ECHO

SET EDITOR

SET HOMES | HOME | HOMEPATH

SET TERMOUT

SHOW ALERT

SHOW BASE

SHOW CONTROL

SHOW HM_RUN

SHOW HOMES | HOME | HOMEPATH

SHOW INCDIR

SHOW INCIDENT

SHOW PROBLEM

SHOW REPORT

SHOW TRACEFILE

SPOOL

There are other commands intended to be used directly by Oracle, type

"HELP EXTENDED" to see the list

adrci>

As the help suggests, you can drill down by asking for help on specific commands.

adrci> help show alert

Usage: SHOW ALERT [-p <predicate_string>] [-term]

[ [-tail [num] [-f]] | [-file <alert_file_name>] ]

Purpose: Show alert messages.

Options:

[-p <predicate_string>]: The predicate string must be double-quoted.

The fields in the predicate are the fields:

ORIGINATING_TIMESTAMP timestamp

NORMALIZED_TIMESTAMP timestamp

ORGANIZATION_ID text(65)

COMPONENT_ID text(65)

HOST_ID text(65)

HOST_ADDRESS text(17)

MESSAGE_TYPE number

MESSAGE_LEVEL number

MESSAGE_ID text(65)

MESSAGE_GROUP text(65)

CLIENT_ID text(65)

MODULE_ID text(65)

PROCESS_ID text(33)

THREAD_ID text(65)

USER_ID text(65)

INSTANCE_ID text(65)

DETAILED_LOCATION text(161)

UPSTREAM_COMP_ID text(101)

DOWNSTREAM_COMP_ID text(101)

EXECUTION_CONTEXT_ID text(101)

EXECUTION_CONTEXT_SEQUENCE number

ERROR_INSTANCE_ID number

ERROR_INSTANCE_SEQUENCE number

MESSAGE_TEXT text(2049)

MESSAGE_ARGUMENTS text(129)

SUPPLEMENTAL_ATTRIBUTES text(129)

SUPPLEMENTAL_DETAILS text(129)

PROBLEM_KEY text(65)

[-tail [num] [-f]]: Output last part of the alert messages and

output latest messages as the alert log grows. If num is not specified,

the last 10 messages are displayed. If "-f" is specified, new data

will append at the end as new alert messages are generated.

[-term]: Direct results to terminal. If this option is not specified,

the results will be open in an editor.

By default, it will open in emacs, but "set editor" can be used

to set other editors.

[-file <alert_file_name>]: Allow users to specify an alert file which

may not be in ADR. <alert_file_name> must be specified with full path.

Note that this option cannot be used with the -tail option

Examples:

show alert

show alert -p "message_text like '%incident%'"

show alert -tail 20

adrci>

Viewing the Alert Log

Depending on your setup, you may have multiple homes (Database, Listener, Grid Infrastructure etc.). It is important you point to the correct home before issuing any commands.

adrci> show home

ADR Homes:

diag/rdbms/orcl/orcl

diag/tnslsnr/ol6-112/listener

adrci> set home diag/rdbms/orcl/orcl

adrci>

With the home set, we can look at the contents of the alert log. The help text for the SHOW ALERT command provides some examples.

Examples:

show alert

show alert -p "message_text like '%incident%'"

show alert -tail 20

We might decide to look for errors in the alert log with a command like the following.

adrci> show alert -p "message_text like '%ORA-%'"

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:

*************************************************************************

Output the results to file: /tmp/alert_1781_13990_orcl_1.ado

adrci>

We could also do a UNIX-style "tail -f" of the alert log using the following command, even on Windows.

adrci> show alert -tail -f

Viewing Trace Files

The alert log will often make reference to trace files. These can be viewed directly using the SHOW TRACE command.

adrci> show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72697/orcl_ora_18310_i72697.trc

Managing Diagnostic Information (Purging Trace Files)

The PURGE command can be used to remove some or all of the diagnostic information from the repository. The HELP PURGE command describes the usage.

adrci> help purge

Usage: PURGE [[-i <id1> | <id1> <id2>] |

Purpose: Purge the diagnostic data in the current ADR home. If no

option is specified, the default purging policy will be used.

Options:

[-i id1 | id1 id2]: Users can input a single incident ID, or a

range of incidents to purge.

[-age <mins>]: Users can specify the purging policy either to all

the diagnostic data or the specified type. The data older than <mins>

ago will be purged

data to be purged.

Examples:

purge

purge -i 123 456

purge -age 60 -type incident

adrci>

So to purge all diagnostic information, including trace files, older than 1 month you would issue the following.

adrci> purge -age 43200

adrci>

Problems and Incidents

When a problem occurs on the database, it is logged in the alert log and an incident is created. Doing a search of the alert log with the following command will reveal some information about the incident.

adrci> show alert -p "message_text like '%incident%'"

2013-10-09 10:05:17.154000 +01:00

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_18310.trc (incident=72697):

ORA-07445: exception encountered: core dump [kghalo()] [SIGSEGV] [ADDR:0x9FFFFFFFBFFFF000]

[PC:0x40000000065AE680] [Address not mapped to object] []

Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72697/orcl_ora_18310_i72697.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

2013-10-09 10:05:36.501000 +01:00

Dumping diagnostic data in directory=[cdmp_20131009100536], requested by (instance=1, osid=18310), summary=[incident=72697].

A simpler solution is to display the problem from the command line directly using the SHOW PROBLEM command.

adrci> show problem

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:

*************************************************************************

PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME

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

1 ORA 3137 [3120] 71593 2013-08-09 10:16:43.714000 +01:00

2 ORA 7445 [kghalo()] 72697 2013-10-09 10:05:17.154000 +01:00

2 rows fetched

adrci>

The same problem can occur multiple times, so a single problem may result in multiple incidents. Incidents are displayed using the SHOW INCIDENT command.

adrci> show incident

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl:

*************************************************************************

INCIDENT_ID PROBLEM_KEY CREATE_TIME

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

71593 ORA 3137 [3120] 2013-08-09 10:16:43.714000 +01:00

72697 ORA 7445 [kghalo()] 2013-10-09 10:05:17.154000 +01:00

2 rows fetched

adrci>

Once you've identified an incident of interest, you can look at it in more detail by altering the mode in the SHOW INCIDENT command.

adrci> show incident -mode detail -p "incident_id=72697"

ADR Home = /u01/app/oracle/diag/rdbms/lstu/lstu:

*************************************************************************

**********************************************************

INCIDENT INFO RECORD 1

**********************************************************

INCIDENT_ID 72697

STATUS ready

CREATE_TIME 2013-10-09 10:05:17.154000 +01:00

PROBLEM_ID 2

CLOSE_TIME <NULL>

FLOOD_CONTROLLED none

ERROR_FACILITY ORA

ERROR_NUMBER 7445

ERROR_ARG1 kghalo()

ERROR_ARG2 SIGSEGV

ERROR_ARG3 ADDR:0x9FFFFFFFBFFFF000

ERROR_ARG4 PC:0x40000000065AE680

ERROR_ARG5 Address not mapped to object

ERROR_ARG6 <NULL>

ERROR_ARG7 <NULL>

ERROR_ARG8 <NULL>

ERROR_ARG9 <NULL>

ERROR_ARG10 <NULL>

ERROR_ARG11 <NULL>

ERROR_ARG12 <NULL>

SIGNALLING_COMPONENT <NULL>

SIGNALLING_SUBCOMPONENT <NULL>

SUSPECT_COMPONENT <NULL>

SUSPECT_SUBCOMPONENT <NULL>

ECID <NULL>

IMPACTS 0

PROBLEM_KEY ORA 7445 [kghalo()]

FIRST_INCIDENT 72697

FIRSTINC_TIME 2013-10-09 10:05:17.154000 +01:00

LAST_INCIDENT 72697

LASTINC_TIME 2013-10-09 10:05:17.154000 +01:00

IMPACT1 0

IMPACT2 0

IMPACT3 0

IMPACT4 0

KEY_NAME ProcId

KEY_VALUE 287.6

KEY_NAME Client ProcId

KEY_VALUE oracle@biro01 (TNS V1-V3).18310_1

KEY_NAME ECID

KEY_VALUE 55774dc6ecfa57a3:2a5146fe:13ffc25ce34:-8000-0000000000055601.1

KEY_NAME PQ

KEY_VALUE (0, 1381309514)

KEY_NAME SID

KEY_VALUE 1125.45841

OWNER_ID 1

INCIDENT_FILE /u01/app/oracle/diag/rdbms/orcl/lstu/trace/orcl_ora_18310.trc

OWNER_ID 1

INCIDENT_FILE /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_72697/orcl_ora_18310_i72697.trc

1 rows fetched

adrci>

Creating Packages to Send to Oracle Support

If you can't solve the problem yourself, you can use the Incident Packaging Service (IPS) to gather all pertinent information so it can be sent to Oracle Support. This should reduce the amount of time you waste trying to identify what information is necessary for them to identify and solve the problem.

First, create the package using the problem ID displayed by the SHOW PROBLEM command.

adrci> ips create package problem 2 correlate all

Created package 1 based on problem id 2, correlation level all

adrci>

Next, create a zip to send to Oracle Support by specifying the package number displayed by the above command.

adrci> ips generate package 1 in "/tmp"

Generated package 1 in file /tmp/IPSPKG_20140610100342_COM_1.zip, mode complete

adrci>

The package is now zipped and ready to upload to Oracle Support.

$ ls /tmp/IPSPKG*.zip

/tmp/IPSPKG_20140610100342_COM_1.zip

Cloud Control Integration

Incident management is integrated into Enterprise Manager Cloud Control. Navigate to the database of interest, the use the "Oracle Database > Diagnostics > Support Workbench" menu option. Incidents can be selected, packaged and uploaded directly to Oracle Support from Cloud Control

Oracle DBA Murali

Tabs

Tuesday, March 17, 2015

How to perform Database Point in Time Recovery(DBPITR)

Tuesday, March 10, 2015

Automatic Diagnostic Repository (ADR) in Oracle Database 11g Release 1 (ADRCI)

Cloud Control Integration

Translate

Live Feed

Country Count