Archive for

RMAN : Freeing FRA Space in an Emergency

The flashback recovery area has run out of space. You see a message in the alert log similar to the following :

Can not open flashback thread because there is no more space in flash recovery area.

sql>alter database open;
alter database open
*
ERROR at line 1:
ORA-38760 : This database instance failed to turn on flashback database.

Solution :

1.Increase space:-

You can increase the size of the flashback area.

sql>alter system set db_recovery_file_dest_size=10G;

2.Remove Restore Points

The alternative to increasing the size of the flashback area is to remove some of the older restore points that you no longer need.

sql>col name format a25
sql>select name , storage_size from v$restore_points;
Name                              Storage_size
RP0                                207028224
RP1                                                0
RP2                                915701760
PRE_TEST1                                    0
POST_TEST1                                  0
GOOD_ONE                                   0
QA_GOLD                                      0
BRANCH_1                                    0
AFTER_BRANCH_2                        0
AFTER_BRANCH_3                        0

10 rows selected.

restore points RP0 and RP2 have torage associated with them.this is because guaranteed restore points. You should remove them to make some room in the flash recovery area.

sql>drop restore point rp2;

Restore point dropped.

sql>drop restore point rp0;

Restore poing rp0;

Restore point dropped.

you may be able to start the database.
3.Disable Flashback

If solutions 1 and 3 fail or not applicable, you may want to disable flashback in the database temporarily.

sql>shutdown immediate

Oracle instance shutdown.

sql>startup mount

Database mounted.

sql>alter database flashback off;
Database altered.

This will stop the flashback operations and will stop generating flashback logs. To free up some space , you may want to delete some more files such as archived redo logs, unneeded backups and so on.

$ rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Wed Dec 23 14:20:52 2009

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

connected to target database: RIYADH (DBID=3624306176)

RMAN> delete noprompt archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
——- —- ——- – ——— —-
1       1    2       A 28-OCT-09 D:\ORACLE\ARCHIVE_01\ARC00002_0701448453.001
2       1    2       A 28-OCT-09 D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RI
YADH\ARCHIVELOG\2009_12_19\O1_MF_1_2_5LRWHYC7_.ARC
3       1    3       A 19-DEC-09 D:\ORACLE\ARCHIVE_01\ARC00003_0701448453.001
4       1    3       A 19-DEC-09 D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RI
YADH\ARCHIVELOG\2009_12_20\O1_MF_1_3_5LVJOJ5C_.ARC
5       1    4       A 20-DEC-09 D:\ORACLE\ARCHIVE_01\ARC00004_0701448453.001
6       1    4       A 20-DEC-09 D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RI
YADH\ARCHIVELOG\2009_12_23\O1_MF_1_4_5M3FN1N0_.ARC
7       1    5       A 23-DEC-09 D:\ORACLE\ARCHIVE_01\ARC00005_0701448453.001
deleted archive log
archive log filename=D:\ORACLE\ARCHIVE_01\ARC00002_0701448453.001 recid=1 stamp=
706008375
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RIYADH\ARCHIVE
LOG\2009_12_19\O1_MF_1_2_5LRWHYC7_.ARC recid=2 stamp=706008375
deleted archive log
archive log filename=D:\ORACLE\ARCHIVE_01\ARC00003_0701448453.001 recid=3 stamp=
706094569
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RIYADH\ARCHIVE
LOG\2009_12_20\O1_MF_1_3_5LVJOJ5C_.ARC recid=4 stamp=706094569
deleted archive log
archive log filename=D:\ORACLE\ARCHIVE_01\ARC00004_0701448453.001 recid=5 stamp=
706353596
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RIYADH\ARCHIVE
LOG\2009_12_23\O1_MF_1_4_5M3FN1N0_.ARC recid=6 stamp=706353596
deleted archive log
archive log filename=D:\ORACLE\ARCHIVE_01\ARC00005_0701448453.001 recid=7 stamp=
706370138
Deleted 7 objects.

Similarly…

RMAN>delete noprompt backup of database;
RMAN>delete noprompt copy of database;

sql>alter database open;
Database altered.

The database is now fully functional , but without the flashback ability. if you want to reenable flashback later ,you can do. because you’ve cleared unneeded files, the flash recovery area is fully usable whenever you choose to againg enable flashback.

I asked Mr.Surachart about Restore Point…

Mohamed Says : What is Restore point?
Surachart Says : A restore point is a user defined name that can be substituted for an SCN or clock time when used in conjuction with flashback database, flashback table and RMAN.

Mohamed Says : If i am go to delete restore point means m what’s happen?

Surachart Says : If you delete restore point , flashback not guarantee to that time or scn.

RMAN 2.2 Connecting to RMAN and BASIC Backup

To connect to RMAN, you need to establish the following

• OS Environement Variables.
• Access to a privileged OS account or schema with sysdba privilege.

You can connect to RMAN either the OS command line interface or through EM.

The following examples assumes you have on to a UNIX and Windows server using the Oracle account .

$ rman target / on Unix.

cmd>rman target /   on Windows.

You must connect to RMAN with either a user that is OS authenticated or a username /pasword that is in the password file.

 While connected as  RMAN, you can startup and shutdown your target database.

C:\Documents and Settings\AZAR>set oracle_sid=riyadh

C:\Documents and Settings\AZAR>rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Dec 20 11:56:33 2009

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

connected to target database (not started)

RMAN> startup

Oracle instance started
database mounted
database opened

Total System Global Area     247463936 bytes

Fixed Size                     1248332 bytes
Variable Size                 79692724 bytes
Database Buffers             159383552 bytes
Redo Buffers                   7139328 bytes

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN>exit

Backup & Restore Database

$rman target /
RMAN messages displaying information about which files are being backed up and wo which file and location.

RMAN> backup database;

Starting backup at 20-DEC-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\SYSTEM01.D
BF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\SYSAUX01.D
BF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\UNDOTBS01.
DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\USERS01.DB
F
channel ORA_DISK_1: starting piece 1 at 20-DEC-09
channel ORA_DISK_1: finished piece 1 at 20-DEC-09
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RIYADH\BACKUPSET\2009_
12_20\O1_MF_NNNDF_TAG20091220T123746_5LVW3H3F_.BKP tag=TAG20091220T123746 commen
t=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:18
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 20-DEC-09
channel ORA_DISK_1: finished piece 1 at 20-DEC-09
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RIYADH\BACKUPSET\2009_
12_20\O1_MF_NCSNF_TAG20091220T123746_5LVW5VJ3_.BKP tag=TAG20091220T123746 commen
t=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 20-DEC-09

To display information about your backup, use the list backup commands.

RMAN> list backup;
List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
1       Full    517.96M    DISK        00:01:09     20-DEC-09
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20091220T123746
        Piece Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RIYADH\BACKUPSE
T\2009_12_20\O1_MF_NNNDF_TAG20091220T123746_5LVW3H3F_.BKP
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  —- — —- ———- ——— —-
  1       Full 629209     20-DEC-09 D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\SYST
EM01.DBF
  2       Full 629209     20-DEC-09 D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\UNDO
TBS01.DBF
  3       Full 629209     20-DEC-09 D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\SYSA
UX01.DBF
  4       Full 629209     20-DEC-09 D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\USER
S01.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
2       Full    6.80M      DISK        00:00:03     20-DEC-09
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20091220T123746
        Piece Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RIYADH\BACKUPSE
T\2009_12_20\O1_MF_NCSNF_TAG20091220T123746_5LVW5VJ3_.BKP
  Control File Included: Ckp SCN: 629239       Ckp time: 20-DEC-09
  SPFILE Included: Modification time: 20-DEC-09

RMAN>

 Oracle recommand that you set the NLS_DATE_FORMAT-DD-MON-RRRR HH24:MI:SS AT os LEVEL.

RMAN will allocate a  channel and backup to a default location on disk.

Simulating a Media Failure and  Restore and Recover database :

To simulate media failure, you can rename a datafile at the OS level on your target database server, when starts up, it reads the control file and compares the information to all the datafiles.

C:\Documents and Settings\AZAR>move D:\oracle\product\10.2.0\oradata\riyadh\US
ERS01.DBF D:\oracle\product\10.2.0\oradata\riyadh\USERS01.bk

or

Unix –> $ mv  /oracle/RIYADH/users01.dbf      /oracle/RIYADH/users.bk

C:\Documents and Settings\AZAR>rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Dec 20 12:57:15 2009

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

connected to target database (not started)

RMAN> startup

Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 12/20/2009 12:57:33
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\USERS01.DBF’

RMAN> exit
Recovery Manager complete.

Restore and Recover :

• Restore is the process of copying backup datafiles from the backup files.
• Recovery is the process of applying transaction information to the datafiles to recover them to the state were in just before the failure occured.

 

C:\Documents and Settings\AZAR>rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Dec 20 12:58:03 2009

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

connected to target database: RIYADH (DBID=3624306176, not open)

RMAN> restore database;

Starting restore at 20-DEC-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\SYSTEM01.DBF

restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\UNDOTBS01.DB
F
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\SYSAUX01.DBF

restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\RIYADH\USERS01.DBF
channel ORA_DISK_1: reading from backup piece D:\ORACLE\PRODUCT\10.2.0\FLASH_REC
OVERY_AREA\RIYADH\BACKUPSET\2009_12_20\O1_MF_NNNDF_TAG20091220T123746_5LVW3H3F_.
BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\RIYADH\BACKUPSET\2009_
12_20\O1_MF_NNNDF_TAG20091220T123746_5LVW3H3F_.BKP tag=TAG20091220T123746
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 20-DEC-09

RMAN> recover database;

Starting recover at 20-DEC-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:06

Finished recover at 20-DEC-09

RMAN> alter database open;

database opened.

 

 

RMAN 2.1 Toggling Archivelog Mode

Problem

You attempted to use RMAN to backup your database and  received this error message.

RMAN – 03009 : failure of backup command on ORA_DISK_1 channel.
ORA- 19602 : cannot backup or copy active file in NOARCHIVELOG mode.

This message indicates that before you can create an RMAN online backup, you need to place your database into archivelog mode.

Solution:

Enabling Archivelog Mode

sql>connect / as sysdba
sql>shutdown immediate
sql>startup mount
sql>alter database archivelog;
sql>alter database open;

Displaying Archive Inforamtion

sql> select log_mode from v$database;

log_mode

Archivelog

Disabling Archivelogmode :

sql>shutdown immediate
sql>startup mount
sql>alter database noarchivelog;
sql>alter database open;
sql>select log_mode from v$database;
log_mode
Noarchivelog

Change archive log destination

sql>alter system set log_archive_dest=’c:\oracle\archive’ scope=spfile;

sql>archive log list
sql>archive log list
database log mode archive
automatic archival enable
Location ‘\orace\archive\’

How It works :

• Online backup — Archivelogmode should be enabled.
• Its allows you to recover all committed transactions.
• This mode protects your database from disk failure because your transaction information can be restored and recovered from the archive log files.
• Every online redo log switch that the contents of the logs are successfully copied to archived redo log files.
• Oracle will not allow an online redo log file to be overwritten until it is copied to an archived redo log file.
• If oracle cannot copy an online redo log file to an archived redo log file, then your database will stop processing and hang. so check your free space on directory.
• No need for log_archive_start when your changing noarchive to archive in 10g.

RMAN 1.4 RMAN Architecture

RMAN Architecture

You can start performing backups with RMAN executable from the $ORACLE_HOME/bin directory.

Recovery Catalog

1. The target database control file will always store the RMAN repository, which is the set of RMAN related backup and recovery information —> RMAN METADATA.
2. It’s smarter to use a dedicated database to store the RAMN repository then create a special schema called the recovery catalog.
3. Thus avoiding the risk of the critical metadata being overwritten when the control files runs out of space.
4. Flash Recovery Area :
5. This is a location on disk where the database will store the backup and recovery related files.
6. MML -Media Management Layer
7. RMAN can directly interact only with disk drives.
8. if you want to use tape drives to store your backups, you’ll need a MML in addition to RMAN.
9. RMAN can’t directly inteact with the tape drives.
10. Oracle also provides Oracle Secure backup , most well integrated media management layer for RMAN backup.

Benefits of using RMAN

1. The powerful Data Recovery Advisor Feature which enables you to easily diagonse and repair data failures and corruption.
2. It automatically manages the backup files without DBA intervention.
3. It automatically deletes unnecessary backup datafiles and archived redo log files both from disk and tape.
4. Reporting of backup actions.
5. Duplicating a database or creating a standby database.
6. Verify available backups are useable for recovery
7. Incremental backup
8. I lets you perform database duplication without backups by using the network enabled database duplication feature also known as active duplication.
9. It automatically detects corrupt data blocks during backups V$DATABASE_BLOCK_CORRUPTION view.
10. when only a few data blocks are corrupted, you can recover at the data block level.
11. Unused block complession feature, wherein RMAN skips unused data blocks during a backup.
12. RMAN provides the ability to perform encrypted backups.
13. You can use a powerful scrpting language which lets you write custom backup and recovery scripts quickly.

 http://download.oracle.com/docs/cd/B10500_01/server.920/a96566/rcmarchi.htm

RMAN 1.3 Recovery Types

Database Recovery and Consistent VS Inconsistent Backups

Consistent Backups	Inconsistent Backups
If you shutdown your database using shutdown norml, shutdown immediate and shutdown transactional, you’ll have a consistent backup.	Shutdown abort or shutdown force or if there is an instance failure , you’ll end up with an inconsistent database.
All uncommitted changes are rolled back	Any committed changes are not rolled back automatticaly
The contents of the database buffer cache written to the datafiles on disk.	Changes made to the database buffer cache aren’t written to the datafiles on disk.
All resources such as lock and latches are released. No need for instance recovery.	All resources such as locks and latches are still held and aren’t released

Crash Recovery :

1. The Oracle server will perform an automatic crash recovery when you restart the instance.
2. Don’t need to restore or recovery tasks.
3. The server will use the information in the undo tablespace to perform automatic instance recovery by rolling back uncommitted transactions  in  the database.

Rolling Forward	Rolling Back
The Oracle server will update all data files with the information from the redo log file. The log files are always written to before the data is recorded in the data file. thus ,an instance recovery may usually leave the online log files “ahead” of the data files.	Uncommitted changes that were added to the data files during the roll forward operation are rolled  back. Undo tablespace contents uncommitted changes to their Original states. At the end of rollback stage, only committed data at the time of the instance failure is retained in the data files.

FAST_START_MTTR_TARGET

1. The database server must apply all transactions b/w the last checkpoint and end of the redo log to the data files.
2.   Number of seconds you want the crash recovery to take.
3.   Oracle will try to recover the instance as close as possible to the time that you specify for the FAST_START_MTTR_TARGET
4.   MAX value is 3,600 seconds (1 hour).

Media Recovery

1.   The server won’t be able to automatically recover from such  a catastrophe
2.   Must provide the lost datafiles from backup.
3.   If error occured, Find V$RECOVER_FILE view, which lists all files that need media recovery
4.  You use two basic commands RERTORE OR RECOVER

You must do the following as part of  a Media Recovery operation

1.   Restore the necessary data files from backup ,either to the old or to an alternative location
2.   Rename the data files , if necessary, so the database will know about their new location
3.   Recover the data files (bring them up to date), if necessary, by applying redo information to them

To open the database after a Successful Restore and Recovery

1.   You must have synchronized copies of all the control files
2.   you must have synchronized online data files.
3.   you must have at least one member of each redo log group

Deciding on the Appropriate Recovery Technique

1.   If you run into logical errors, perform a TSPITR or consider using an appropriate flashback technique to make a point in time recovery.
2.   If you have data corruption in a few blocks in a data files or a set of data files, use block media recovery.
3. If a user error affects a large set of tables or the entire database ,use the flashback feature to revert the database to a previous “good” time by undoing all the changes since that point in time.

RMAN 1.2 Backup Types

Physical and Logical Backups

1. Copy of Datafile using an OS utility such as cp command.
2. You can use this file to restore the database contents if you lose the disk file.
3. Copy of the files like Datafiles, control files and redo log files.
4. logical backups by using Oracle Data pump Export tool wherein you copy the definitions and contents all of the database’s logical componets such as table and so on.
5. Its not complete backup and recovery solution.

Whole and Partial backups

1. Backup of the entire database.
2. Partial backup –> backup of a tablespace or datafile in a database.
3. Datafile backup —> only a single OS file.
4. Tablespace backup –> all the datafiles that are part of the tablespace.

Online and Offline Backups

1. RMAN support both online ( HOT ) and offline ( COLD ) backups.
2. An online backup is always inconsistent during a recovery , the application of the necessary archived log file will make the backup consistent.

Full and Incremental Backups

1. It will contain complete backups of all the datafiles.
2. It contain only the changed data blocks in the datafiles.
3. You can make incremental backups only with the help of RMAN.

Consistent and Inconsistent Backups

What is SCN?

1. SCN is an Oracle assigned number that indicates a committed version of a database.
2. Its quite possible that different datafiles in the database might have a different SCN at any given point in time.
3. If the SCN across all the datafiles are synchronized, it means that the data across the datafiles comes from a single point of time and thus, is consistent.
4. During each checkpoint, the server makes all the datafiles SCN consistent w.r.t an identical SCN.
5. It updates control file  with that SCN information.
6. This synchronization of the SCN gives you a consistent backup of your database.

Inconsistent Backup

1. If you backup your database while it’s running, you may end up with backups of the various data files at various time points and different SCN.
2. This means your database are inconsistent, since the SCN aren’t identical across all the datafiles.
3. If you’re using inconsistent backups, you must use the archive redo log files to make the data current and synchronize the SCN across the datafiles.
4. If you’re using the RMAN offline backup, the database must be mounted before you can actually perform the RMAN backup. RMAN needs to update the target database control file.

RMAN 1.1 Types of Failure

Statement Failures

1. When a program attempts to enter invalid data into an Oracle Table.
2. Long data insertion job or data import job to fail midway between there is no more room to put the data in.
3. Proper privileges to perform a task.

User process Failure

1. abnormal disconnect or performing a terminal program error and losing the session connection.
2. DBA not much work to do here.
3. BG process rollback the uncommitted transaction  changes to the data and releases the locks.

Instance Failure

1. Your database comes down such as Hardware , a power failure and an emergency shutdown procedure.
2. An instance shutdown when the key Oracle bg process such as PMON shutdown because of an error condition.
3. Check Alert log and trace files.
4. Just restarting the Database instance using by STARTUP Command.
5. The database was clearly shutdown and the database files aren’t synchronized.
6. Oracle will perform an automatic instance or crash recovery at this point.
7. Automatically perform a rollback uncommitted transactions by using the data from undo segments and roll forward committed changes it in the online redo logs.
8. Don’t need to any sort of backup when restarting the database.

Network Failure

1. Net listener, NIC and network connection has failed.
2. DBA must configure the multiple network cards.

User Error

1. Wrongly deleting  data from table and dropping a table you can use FLASHBACK feature.
2. If the transactions not completed yet, rollback statement.
3. Oracle LOGMINER also comes on handy situation like this.

Media Failure

1. It occur when you lose a disk or a disk  controller fails.
2. Examples of media failure  i) Head crash II) File corruption III)Overwriting or deleting of a datafile.
3. Any one of the multiplexed control files are deleted or lost because of disk failure you must restore the missing control file from an existing control file.
4. Datafiles, undo table space is deleted or lost because of a disk failure.If you lose one of these files, the instance may shutdown or may not sutdown in such case

         sql> shutdown abort;           Then

         sql>startup mount

          Restore the datafiles and recover it.

1.         An entire redo log group lost. if you have atleast one member of the redo log group , your database instance can continue to operating normally.
2. Restore the log file by copying one of  the other members of the same group.