Archive files are deleted mistakenly form my Database and RMAN show ORA-19625 error identifying file string

Archive files are deleted mistakenly form my Database and RMAN show ORA-19625 error identifying file string
====================================================================================================


Suddenly one of my DBA member deleted some Archive log files mistakenly. and he don't told me.but when he run RMAN backup script. he found ORA-19625 error. then he told me that.


Solution of this problem:-
==========================


Run in RMAN command line,

RMAN> CROSSCHECK ARCHIVELOG ALL;

RMAN> DELETE EXPIRED ARCHIVELOG ALL;



The first command verifies the list of archivelogs in the
controlfile with those actually present on disk. Files that
are not found on disk (those that were deleted
by mistake) will be marked as "EXPIRED".


The second command then identifies all the "EXPIRED" files,
presents the list to you and asks you to confirm if they
should be deleted -- meaning that they will be deleted from
the controlfile. RMAN, at the next backup run (or a backup that
you initiate immediately thereafter) will not look for
these files and will not error.

How to Recover oracle Database until a Previous position by RMAN

How to back Database until a Previous position by RMAN

Database Point-in-time recovery (Time based incomplete recovery)

# This scenario assumes that all initialization files,RMAN backup of database and the current
# controlfile are in place and you want to recover to a point in time '03-07-2010:14:20:00' .
#

RMAN> shutdown immediate

RMAN>STARTUP MOUNT FORCE;

RMAN> RUN
{
SET UNTIL TIME "TO_DATE('03-07-2010:14:20:00','dd-mm-yyyy:hh24:mi:ss')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

Oracle Block change Tracking

Oracle Block change Tracking
============================
============================

Oracle Block change Tracking improves the incremental Backup performance and
is used to record changed blocks in each datafile in a change tracking file.
If change Tracking is enabled, RMAN uses block change tracking file to identify
changed blocks for incremental backups and avoids full datafile scans during the backup.Block change tracking feature introduce in Oracle 10g R1. By default,
the Block change tracking file is created as Oracle managed file in
DB_CREATE_FILE_DEST.By default, Oracle will not record block change information.
You can Enable or disable the change tracking when the database is open or mounted.

Enable and Disable Block Change Tracking:
===============================================

SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Run the below command to create change tracking file in a specified location.
===========================================================================

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE 'D:\oradata\orcl\block_change_track.ora' REUSE;

Run the below command to disable block change tracking
=======================================================

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Run the below Query to monitor the status of block change tracking
==================================================================

SQL> SELECT FILE,STATUS,BYTES FROM V$BLOCK_CHANGE_TRACKING;

RMAN Backup policy

RMAN Backup policy
---------------------

requirement is as follows.

1)Database running is noarchivelog mode. As I wish to take backup online so database need to run in archivelog mode.

2)I will use flash recovery area to take backup for better management of backup data.

3)Take full database backup or level 0 incremental backup in a week and each day will take incremental backup cumulative.

4)As a repository I will use recovery catalog in order to store backup information.

5)I need to setup rman configuration like,
-autobackup on(in order to back up the control file and spfile whenever the database structure metadata in the control file changes and whenever a backup record is added) ,

-backup optimization on (in order to skip logs that it has already backed up to the specified device). and,

-recovery window to 7 days. (to ensure that you can recover the database to any point within the last week)

6)recovery catalog database name is test, listener port 1522 and serice name test1

Below is the step by step solution.


01: Connect to source database as sysdba.
=============================================
oracle@neptune ~$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 15 23:22:04 2008


Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

02: See the log mode of database.
====================================

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

03: Shutdown the source database.
================================
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

04: Start the database in mount state.
======================================
SQL> startup mount
ORACLE instance started.

Total System Global Area 234881024 bytes
Fixed Size 2019800 bytes
Variable Size 109055528 bytes
Database Buffers 117440512 bytes
Redo Buffers 6365184 bytes
Database mounted.

05: Enable archive log.
=========================
SQL> alter database archivelog;
Database altered.

06: Open the database.
=========================

SQL> alter database open;
Database altered.

07: Check the archived destination and flash recovery area size and location.
=============================================
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 83
Next log sequence to archive 85
Current log sequence 85

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE
------------------------------------ ----------- -------
db_recovery_file_dest string /oracle/app/oracle/product/10.
2.0/db_1/flash_recovery_area
db_recovery_file_dest_size big integer 50G
If you see after giving archive log list Archive destination is not shown as USE_DB_RECOVERY_FILE_DEST then set it by,
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

08: Connect to rman of source database.
=========================================
SQL> host rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:24:16 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: host (DBID=259530685)

09: Change some configuration settings of source database.
======================================================
RMAN> configure controlfile autobackup on;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> configure backup optimization on;

new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored

10: Connect to the recovery catalog database.
====================================================
oracle@neptune ~$ sqlplus sys/sistem@test:1522/test1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Oct 15 23:35:21 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

11: Create recovery catalog user and tablespace and assign permission.
====================================================
SQL> create user rman identified by rman;

User created.

SQL> create tablespace catalog_spc datafile '/oradata1/catalog.dbf' size 10M autoextend on maxsize unlimited;

Tablespace created.

SQL> alter user rman default tablespace catalog_spc temporary tablespace temp quota unlimited on catalog_spc;

User altered.

SQL> grant recovery_catalog_owner to rman;

Grant succeeded.

12: Connect to recovery catalog database.
=================================================

bash-3.00$ rman catalog rman/rman@test:1522/test1

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:39:43 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to recovery catalog database

13: Create recovery catalog schema objects.
============================================

RMAN> create catalog tablespace catalog_spc;

recovery catalog created

14: Be sure whether recovery catalog objects successfully created.
===================================================

SQL> select table_name from tabs;

15: Connect to rman on source and recovery catalog database.
==========================================================

bash-3.00$ rman target / catalog rman/rman@test:1522/test1

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Oct 15 23:41:53 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: host (DBID=259530685)
connected to recovery catalog database

16: Register database with recovery catalog.
============================================

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete


17: Check whether registration was successful.
==============================================

RMAN> report schema;

If this return database structure then registration is successful.

18: Create scripts for incremental database backups.
=========================================================

This script is for full database backup level 0. This one will be run on weekly basis.
--------------------------

CREATE GLOBAL SCRIPT global_full_backup
{
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}
This script is for incremental cumulative database backup level 0. This one will be run on daily basis.
CREATE GLOBAL SCRIPT global_incr_backup
{
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}


This one is for RMAN incrementally updated backups. I just made this and will not use it. For huge database we may consider it.
--------------------------------

CREATE GLOBAL SCRIPT global_incr_updated_backup
{
RECOVER COPY OF DATABASE WITH TAG 'inc_update';
BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'inc_update'
DATABASE;
}

A sample output after creating global_full_backup,
RMAN> CREATE GLOBAL SCRIPT global_full_backup;
{
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
DELETE OBSOLETE;
}

starting full resync of recovery catalog
full resync complete
created global script global_full_backup

19: Run these scripts and take backups.
======================================

Weekly,

RMAN>RUN{
EXECUTE GLOBAL SCRIPT global_full_backup;
}

Daily,

RMAN>RUN{
EXECUTE GLOBAL SCRIPT global_incr_backup;
}


You may consider also inside script like,

RMAN> backup database plus archivelog delete all input;

in order to delete archived log from all locations that already taken backup.

Or, you may issue,

RMAN> delete archivelog all backed up 1 times to disk;


in order to delete archive log that have at least 1 times backed up to disk.

Database duplication using Recovery Manager

Introduction
This document provides you with a brief description on how to do refresh a Database (duplicate a database) from the Production Database backup taken using RMAN to tapes to any other environments.

I. Introduction
---------------
This document describes the process of refreshing the Test Databases from the RMAN Production database backups taken to the Tapes / Disks.

II. Initial Preparation Steps
-----------------------------
We first need to make sure that the database is not running within Fail Safe environment, and that the disk space used by the old database is released, so we can fit the new database. Here are a few steps that need to be taken cars of before starting the Database Refresh:

1. If there is requirement to preserve some data or accounts (Schema’s and other necessary things) from the old environment, export that data first before starting the refreshing from Production.

2. If the databases are running in Fail Safe environment, shut them down through Fail Safe Manager. Also shut down the Listener and the Intelligent Agent that is running for those particular databases.

3. Modify the TNSNAMES.ORA, the INIT.ORA (and listener.ora if required) files to make sure the database can be started independently, using the local listener. Try this out by starting the listener service and database service manually through the Services screen on the Win 2000 machine, and starting database through SQL*Plus.

4. Shut down the database using the FAILSAFE Manager and remove all database files except ones from the Admin directories (e.g. init.ora). This is required to clean space on disk to fit the new database. If we have enough disk space for the restore to happen, then we move the existing files to a different directory or mount point.

III. Preparing RMAN Duplication Script
--------------------------------------
Once we are done with the above steps, we can proceed with the next step of creating the scripts for restoring the Database. An example of this script is given below:
connect catalog rman/password@;
connect target sys/password@;
connect auxiliary sys/password@;
run {
allocate auxiliary channel ch1 type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=c:\cluster\tdpo.opt)';
set until scn or ;

set newname for datafile 1 to ‘new path for restore’;
. . . . . .
. . . . . .
...
duplicate target database to
logfile group 1 (‘’,
(‘’) SIZE 100M,
group 2 ((‘’,
(‘’) SIZE 100M,
group 3 ((‘’,
(‘’) SIZE 100M;
}

The description of the above script is as follows :

The first part deals with connecting to required databases:

1. catalog database where RMAN catalog is stored,
2. target database which is the database we want to clone, and
3. auxiliary database which is the one that we are attempting to create.
4. Note that when running this script later on, both catalog and target databases need to be open during the process, while auxiliary database is normally in NOMOUNT state.

Next in the script is allocating channel used to access file system through TSM. Note that to do that we will need to change TSM configuration (dsm.opt file, nodename parameter) in order for the node to appear as the production node.
Next in the script is set until SCN / DATE command that specifies until which point the database will be duplicated. If the UNTIL SCN / DATE is not mentioned, RMAN will attempt to recover until the last archived log, which can cause failure if that log is not available on the tape drive (e.g. it is still on the production server disk).
Next is the list of set newname for datafile commands, which are required when new disk structure is different from production disk structure (which is case on all our systems). All database files should be specified in this list (nothing is required for tempfiles). The list of datafile’s can be obtained by querying the DBA_DATA_FILES data dictionary view.
Finally, the duplicate command is there to do the actual database duplication.

IV. Running Database Duplication
--------------------------------
To run database duplication we can prepare a batch script, or run a command to start it up. It would look something like this:

rman cmdfile (the rman script) msglog (a file name for the logs)

Before starting the RMAN script, the following things need to be taken care of :
1. Verify that the RMAN catalog database is open. Make sure this database will be open during complete duplication process, e.g. if it normally goes down for backup turn off the backup procedures. If the connection to the database is lost during the duplication, the process will fail and will need recovery.

2. Verify that the target database is open. Make sure this database will be open during complete duplication process, same as for RMAN database.

3. Verify that the Oracle services for auxiliary database are running and the database is in nomount state.

If the RMAN script is successful, it will get all the files from the file system, place them in appropriate locations as specified in the script, and recover the database. It will also change the Database ID, and start the database. This is the best case scenario, however, if duplication script fails you might need to try and recover from failure.

V. Recovering from Failure
--------------------------
If the RMAN duplication process fails, We might need to recover the database using the RMAN backup. The Database supplication or the restore can fail because of some reasons like :

1. RMAN catalog database going down for backup
2. Archived logs not available on the file system (when set until SCN was not specified in the script). In those cases you might try following steps to recover, first run the switch clone command through RMAN (After CONNECTING to the TARGET, CATALOG and AUXILIARY Databases) :
run{
switch clone datafile all;
}
Afterwards, try recreating the control file. RMAN first creates a control file but does not have all data files specified in there (it creates that one later). Best way is to backup control file to trace on the target database, and modify that script to run in auxiliary database. Changes to the script are typically: use new filenames as the location might have changed, set new database name, and use RESETLOGS clause.
Once control file is created and executed, complete recovery of the database until specified SCN, the RMAN script can look something like this :
run {
allocate auxiliary channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=c:\cluster\tdpo.opt)';
set until scn 6899135273;
recover
clone database
check readonly;
release channel ch1;
}
This step will obtain all required archived logs from the file system and apply them to the database. After recovery is completed you can open the database:
alter database open resetlogs;
That would complete the recovery. Note that when recovered this way, the database Id is still the same as for production, thus you cannot use RMAN to backup that database (unless you are using different catalog). Consequently, one should always strive to have the database duplicated properly through RMAN without failures.

VI. Post Refresh Steps
----------------------
After the database is duplicated, there are few steps that might be required:
1. In some environments , it may be required to change the Mode to noarchivelog mode as the Production is mainly run in Archivelog mode.

2. Add files to temporary tablespaces. When the database is restored all files and tablespaces will exist, however, none of the temp files will be created. One needs to add tempfiles to temporary tablespaces.

3. Drop all database links, and recreate them to point to proper environment. After duplication, new database will have same database links as the production, thus pointing to the production database. All the database links should therefore be dropped, and new ones created to point to the new environment.

4. If the new database is running in the Fail Safe environment, one will need to rebuild the password file on the other node (the one that was not used when duplicating the database). If this is not done, the database will not start on that node and the whole Fail Safe group will be moved to other node.

5. Revert back changes to tnsnames.ora (and listener.ora if applicable) to make sure the database can start within Fail Safe.

6. Revert back changes done to the TSM configuration files (dsm.opt).

7. Shut the database, stop local listener and database services, and start the listener and database within Fail Safe.

8. Make sure the database can fall-over correctly to another node, by moving Fail Safe group manually.