Feeds:
Posts
Comments

Posts Tagged ‘recovery’

Hi Friends,

Hope all of you doing well, here I have one more live scenario (Mainly for those who are new in DBA world and wanted to know what kind of issue came in PRD).

Yesterday I was doing one Database refresh activity and I came across this issue where while recovering database it was asking for archive file which was missing.

generally in this case, if you don’t have archive file and any how you want to open database i.e incomplete recovery then please follow the below steps.

While doing incomplete recovery we first find the which is current log file in DB.

Then try to apply those log file when it is asking for specific archive file.

In this scenario I try to apply the log file which was having status “CURRENT” but not luck.

Then I apply alternatively other log file as well to try luck and how wonder it took one of log file and recovery went successfully.

Luckily I copied those logs for all of you guys and , please find same one as below.

 

SQL> @control_CRP.sql.orig_noz ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started.

Total System Global Area 1.3095E+10 bytes Fixed Size                  2231848 bytes Variable Size            4362076632 bytes Database Buffers         8724152320 bytes Redo Buffers                6447104 bytes

Control file created.

SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140

Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogB/log_g18m1.dbf ORA-00310: archived log contains sequence 145139; sequence 145140 required ORA-00334: archived log: ‘/oracle/CRQ/origlogB/log_g18m1.dbf’

SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140

Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogA/log_g17m1.dbf ORA-00310: archived log contains sequence 145138; sequence 145140 required ORA-00334: archived log: ‘/oracle/CRQ/origlogA/log_g17m1.dbf’

SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140

Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogB/log_g16m1.dbf ORA-00310: archived log contains sequence 145137; sequence 145140 required ORA-00334: archived log: ‘/oracle/CRQ/origlogB/log_g16m1.dbf’

SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140

Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogA/log_g15m1.dbf ORA-00310: archived log contains sequence 145136; sequence 145140 required ORA-00334: archived log: ‘/oracle/CRQ/origlogA/log_g15m1.dbf’

SQL> recover database using backup controlfile; ORA-00279: change 1242045930 generated at 05/09/2013 06:42:28 needed for thread 1 ORA-00289: suggestion : /oracle/CRQ/oraarch/CRQarch1_145140_727276125.dbf ORA-00280: change 1242045930 for thread 1 is in sequence #145140

Specify log: {=suggested | filename | AUTO | CANCEL} /oracle/CRQ/origlogB/log_g14m1.dbf Log applied. Media recovery complete. SQL> alter database open resetlogs;

Database altered.

SQL> =============================================================================== Please find the log status during this recovery from other session…. FYI…:)

SQL> select GROUP#,BYTES/1024/1024,MEMBERS,STATUS from v$log;

    GROUP# BYTES/1024/1024    MEMBERS STATUS ———- ————— ———- —————-          1              50          2 INACTIVE          2              50          2 INACTIVE          3              50          2 INACTIVE          4              50          2 INACTIVE          5              50          2 INACTIVE          6              50          2 INACTIVE          7              50          2 INACTIVE          8              50          2 CURRENT

8 rows selected.

SQL> col MEMBER for a40 SQL> col TYPE for a12 SQL> select GROUP#,MEMBER,TYPE from v$logfile;

    GROUP# MEMBER                                   TYPE ———- —————————————- ————          8 /oracle/CRQ/origlogB/log_g18m1.dbf       ONLINE          8 /oracle/CRQ/mirrlogB/log_g18m2.dbf       ONLINE          7 /oracle/CRQ/origlogA/log_g17m1.dbf       ONLINE          7 /oracle/CRQ/mirrlogA/log_g17m2.dbf       ONLINE          6 /oracle/CRQ/origlogB/log_g16m1.dbf       ONLINE          6 /oracle/CRQ/mirrlogB/log_g16m2.dbf       ONLINE          5 /oracle/CRQ/origlogA/log_g15m1.dbf       ONLINE          5 /oracle/CRQ/mirrlogA/log_g15m2.dbf       ONLINE          4 /oracle/CRQ/origlogB/log_g14m1.dbf       ONLINE          4 /oracle/CRQ/mirrlogB/log_g14m2.dbf       ONLINE          3 /oracle/CRQ/origlogA/log_g13m1.dbf       ONLINE

    GROUP# MEMBER                                   TYPE ———- —————————————- ————          3 /oracle/CRQ/mirrlogA/log_g13m2.dbf       ONLINE          2 /oracle/CRQ/origlogB/log_g12m1.dbf       ONLINE          2 /oracle/CRQ/mirrlogB/log_g12m2.dbf       ONLINE          1 /oracle/CRQ/origlogA/log_g11m1.dbf       ONLINE          1 /oracle/CRQ/mirrlogA/log_g11m2.dbf       ONLINE

16 rows selected.

SQL>

 

Hope this article helped to you. I am expecting your suggestions/feedback.

It will help to motivate me to write more articles….!!!!

 

Thanks & Regards,

Samadhan

https://samadhandba.wordpress.com/

“Key for success, always fight even knowing your defeat is certain….!!!!

Read Full Post »

When implementing a backup and recovery strategy, you have the following solutions available:

– Recovery Manager (RMAN)

This tool integrates with sessions running on an Oracle database to perform a
range of backup and recovery activities, including maintaining an RMAN
repository of historical data about backups. You can access RMAN through the command line or through Oracle Enterprise Manager.

– User-managed backup and recovery

In this solution, you perform backup and recovery with a mixture of host
operating system commands and SQL*Plus recovery commands.
Both of the preceding solutions are supported by Oracle and are fully documented, but RMAN is the preferred solution for database backup and recovery.

RMAN performs the same types of backup and recovery available through user-managed techniques more easily, provides a common interface for backup tasks across different host operating systems, and offers a number of backup techniques not available through user-managed methods.
Most of this manual focuses on RMAN-based backup and recovery.

User-managed backup and recovery techniques are covered in Section VIII, “Performing User-Managed Backup and Recovery.” RMAN gives you access to several backup and recovery techniques and features not available with user-managed backup and recovery. The most noteworthy are the following:

– Incremental backups

An incremental backup stores only blocks changed since a previous backup.
Thus, they provide more compact backups and faster recovery, thereby reducing the need to apply redo during datafile media recovery. If you enable block change tracking, then you can improve performance by avoiding full scans of every input datafile. You use the BACKUP INCREMENTAL command to perform incremental backups.

– Block media recovery

You an repair a datafile with only a small number of corrupt data blocks without taking it offline or restoring it from backup. You use the RECOVER command to perform block media recovery.

– Unused block compression

In unused block compression, RMAN can skip data blocks that have never been used and, in some cases, used blocks that are currently unused.

– Binary compression

A binary compression mechanism integrated into Oracle Database reduces the size of backups.

– Encrypted backups

RMAN uses backup encryption capabilities integrated into Oracle Database to store backup sets in an encrypted format. To create encrypted backups on disk, the database must use the Advanced Security Option. To create encrypted backups directly on tape, RMAN must use the Oracle Secure Backup SBT interface, but does not require the Advanced Security Option.
Whether you use RMAN or user-managed methods, you can supplement physical backups with logical backups of schema objects made with Data Pump Export utility.

You can later use Data Pump Import to re-create data after restore and recovery. Logical backups are for the most part beyond the scope of the backup and recovery documentation.

– Oracle Flashback Technology

As explained in Oracle Database Concepts, Oracle Flashback Technology complements your physical backup and recovery strategy. This set of features provides an additional layer of data protection. Specifically, you can use flashback features to view past states of data and rewind your database without restoring backups or performing point-in-time recovery. In general, flashback features are more efficient and less disruptive than media recovery in most situations in which they apply.

– Logical Flashback Features

Most of the flashback features of Oracle operate at the logical level, enabling you to view and manipulate database objects. The logical-level flashback features of Oracle do not depend on RMAN and are available whether or not RMAN is part of your backup strategy. With the exception of Flashback Drop, the logical flashback features rely on undo data, which are records of the effects of each database update and the values overwritten in the update.

Oracle Database includes the following logical flashback features:

– Oracle Flashback Query

You can specify a target time and run queries against a database, viewing results as they would have appeared at the target time. To recover from an unwanted change like an update to a table, you could choose a target time before the error and run a query to retrieve the contents of the lost rows.

Oracle Database Advanced Application Developer’s Guide explains how to use this feature.

– Oracle Flashback Version Query

You can view all versions of all rows that ever existed in one or more tables in a specified time interval. You can also retrieve metadata about the differing versions of the rows, including start and end time, operation, and transaction ID of the transaction that created the version. You can use this feature to recover lost data values and to audit changes to the tables queried.

Oracle Database Advanced Aplication Developer’s Guide explains how to use this feature.

– Oracle Flashback Transaction Query

You can view changes made by a single transaction, or by all the transactions uring a period of time. Oracle Database Advanced Application Developer’s Guide xplains how to use this feature.

– Oracle Flashback Transaction

You can reverse a transaction. Oracle Database determines the dependencies between transactions and in effect creates a compensating transaction that reverses the unwanted changes. The database rewinds to a state as if the transaction, and any transactions that could be dependent on it, had never happened. Oracle
Database Advanced Application Developer’s Guide explains how to use this feature.

– Oracle Flashback Table

You can recover a table or set of tables to a specified point in time in the past
without taking any part of the database offline. In many cases, Flashback Table eliminates the need to perform more complicated point-in-time recovery operations. Flashback Table restores tables while automatically maintaining
associated attributes such as current indexes, triggers, and constraints, and in this way enabling you to avoid finding and restoring database-specific properties.

– Oracle Flashback Drop

You can reverse the effects of a DROP TABLE statement. “Rewinding a DROP
TABLE Operation with Flashback Drop” on page 16-7 explains how to use this
feature.

A flashback data archive enables you to use some of the logical flashback features to access data from far back in the past. A flashback data archive consists of one or more tablespaces or parts of tablespaces. When you create a flashback data archive, you specify the name, retention period, and tablespace. You can also specify a default flashback data archive. The database automatically purges old historical data the day after the retention period expires.

You can turn flashback archiving on and off for individual tables. By default, flashback archiving is turned off for every table.

– Flashback Database

At the physical level, Oracle Flashback Database provides a more efficient data protection alternative to database point-in-time recovery (DBPITR). If the current datafiles have unwanted changes, then you can use the RMAN command FLASHBACK DATABASE to revert the datafiles to their contents at a past time. The end product is much like the result of a DBPITR, but is generally much faster because it does not require restoring datafiles from backup and requires less redo than media recovery.

Flashback Database uses flashback logs to access past versions of data blocks and some information from archived redo logs. Flashback Database requires that you configure a flash recovery area for a database because the flashback logs can only be stored there. Flashback logging is not enabled by default. Space used for flashback logs is managed automatically by the database and balanced against space required for other files in the flash recovery area.

Oracle Database also supports restore points in conjunction with Flashback Database and backup and recovery. A restore point is an alias corresponding to a system change number (SCN). You can create a restore point at any time if you anticipate needing to return part or all of a database to its contents at that time. A guaranteed restore point ensures that you can use Flashback Database to return a database to the time of the restore point.

– Data Recovery Advisor

Oracle Database includes a Data Recovery Advisor tool that automatically diagnoses persistent data failures, presents appropriate repair options, and executes repairs at your request. Data Recovery Advisor provides a single point of entry for Oracle backup and recovery solutions. You can use Data Recovery Advisor through the Enterprise Manager Database Control or Grid Control console or through the RMAN command-line client.

A database failure usually manifests itself as a set of symptoms: error messages, alerts, trace files and dumps, and failed data integrity checks. Data Recovery Advisor automatically diagnoses and informs you of these failures. Within the context of Data Recovery Advisor, a failure is a persistent data corruption that can be directly mapped to a set of repair actions. Each failure has a status of open or closed. Each failure also has a priority of critical, high, or low.

Failures are detected by data integrity checks, which are diagnostic procedures executed to assess the health of the database or its components. If a data integrity check reveals a failure, then Data Recovery Advisor automatically assesses the effect of a set of failures and maps it to a set of repair options. In most cases, Data Recovery Advisor presents both automated and manual repair options.

Data Recovery Advisor determines the best automated repair option and its effect on the database. The repair option may include repairs such as datafile restore and recovery, media recovery, Flashback Database, and so on. Before presenting an automated repair option, Data Recovery Advisor validates it with respect to the specific environment and the availability of media components required to complete the proposed repair.

If you choose an automated repair option, then RMAN coordinates sessions on the Oracle database to perform the repair for you. The Data Recovery Advisor tool verifies the repair success and closes the appropriate failures.

Read Full Post »