Oracle Reserved Words

During RMAN recovery i faced below problem.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: 'u01/app/oracle/oradata/testdb/test.dbf’

Note: My tablespace name is ‘TEST’

Datafile belong to ‘test tablespace ‘is lost so i have to perform recovery,

And I have valid database backup too.

RMAN>  restore tablespace TEST;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands

RMAN-01005: syntax error: found "test": expecting one of: "double-quoted-string, identifier, single-quoted-string"

RMAN-01007: at line 3 column 20 file: standard input

NOTE: I am getting above error because tablespace name "TEST" is reserved word for RMAN.

So i need to use double quotes with tablespace name.

In this post i will show you how we can check oracle reserved words.

In Oracle 9i

SQL> select count(*) from GV$RESERVED_WORDS;

COUNT(*)

----------

775

In Oracle 10g

SQL> select count(*) from GV$RESERVED_WORDS;

COUNT(*)

----------

1063

SQL> select * from GV$RESERVED_WORDS where keyword like 'TEST';

INST_ID KEYWORD LENGTH

---------- ------------------------------ ---------- 1 TEST 4

Oracle Block Change Tracking

RMAN block change tracking

With Oracle 10gr1 Enterprise Edition Oracle introduce Block change tracking feature which is used to Speed up RMAN Incremental Backup.

What is BLOCK CHANGE TRACKING?

RMAN's change tracking feature for incremental backups improves incremental backup performance by recording changed blocks in each datafile in a change tracking file. If change tracking is enabled, RMAN uses the change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan every block in the datafile.

After enabling change tracking, the first level 0 incremental backup still has to scan the entire datafile, as the change tracking file does not yet reflect the status of the blocks. Subsequent incremental backup that use this level 0 as parent will take advantage of the change tracking file.

Source: Oracle documentation

1) To  Enable Block Change tracking and set location

Before enabling we must to set/specify block change tracking location.There is two way to specify block change tracking location

1. using DB_CREATE_FILE_DEST parameter which use OMF file name.

SQL> alter system set db_create_file_dest='location' SCOPE=BOTH;

SQL> alter database enable block change tracking;

OR

2. Manually specify location for the block change tracking

SQL>alter database enable block change tracking using file 'location';

2) To Disable block change tracking

SQL> alter database disable block change tracking;

NOTE: BCT files deleted automatically by oracle when BCT is disabled.

3) To Reset the location of Block change tracking file

There are two options for this

1. shutdown the database and set the new location

SQL> shutdown database

--Now Move BCT file to new location using OS command ‘mv’

SQL> startup mount

SQL> alter database rename file 'old_location' TO 'new_location';

SQL> alter database open;

OR

Follow these step –

Disable the block change tracking , then set the new location, then enable BCT

SQL> alter database DISABLE block change tracking;

SQL> alter database enable block change tracking using FILE  'NEW_LOCATION';

After these,  RMAN use new location for the BCT.

4) To Check the information about block change tracking STATUS - enable or disabled

Check v$BLOCK_CHANGE_TRACKING view

5). Does BCT file is important for restore and recovery of database.. ?

Answer: No, it is doesn't require for Database Recovery of database

6). What happen if BCT file is lost or corrupted..?

Answer: That is very interesting case here, suppose oracle found if BCT file is corrupted or missing then oracle will automatically recreate new BCT file.

Let see some practical view...

Assume that, BCT file lost or missing :

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host rm /u01/block/bct.dbf

SQL> startup

ORACLE instance started.

Database mounted.

Database opened.

Now check the alertlog file for more information about how oracle create new BCT file when missing or lost.

Alertlog file :

Mon Feb 28 18:25:47 2016

ALTER DATABASE OPEN

CHANGE TRACKING is enabled for this database, but the

change tracking file can not be found. Recreating the file.

Change tracking file recreated.

Block change tracking file is current.

Redo-log File Status

Redo log file is very crucial for database recovery.

and most of time we confuse about "status" of v$log file for redo log files.

1. UNUSED

Whenever you create any redo log file ...that time v$log view showing status "UNUSED"

2. CURRENT

After Creation when redo log "LGWR" process use that file .

At that time v$log view showing status "CURRENT"

3. ACTIVE

If database running in archivelog mode then after log switch "ARCHIVER" process use log file and create archivelog file that time v$Log file view shows the  status  as "ACTIVE"

4. CLEARING

Whenever any log file is corrupted or missing ( which is not "active" or not "current") then we use statement  - -"alter database clear logfile"

to recreate corrupted log file.

At  that time v$log view showing status "CLEARING" after clearing it is showing "UNUSED"

5. INACTIVE

During archivelog file creation v$log view showing status "ACTIVE" and when archivelog file is created then it is showing "INACTIVE" for particular one redo log file at a time.

6. CLEARING_CURRENT

If any error occured during "alter database clear logfile" then v$log file view showing "CLEARING_CURRENT".

ORA-19815: WARNING: db_recovery_file_dest_size of 11646 bytes is 100.00% used, and has 0 remain

Problem:

You notice the following error in the alert.log of the Data Guard Standby database. Your Standby is configured to write its archive logs to the Fast Recovery Area.

ORA-19815: WARNING: db_recovery_file_dest_size of 11646 bytes is 100.00% used, and has 0 remain

Solution:

You have several of options in resolving this issue as suggested in the error message. Below we will detail the options available along with providing recommendations.

1)    Changing the RMAN RETENTION POLICY or Backup using BACKUP RECOVERY AREA

              2)    Adding space to DB_RECOVERY_FILE_DEST that can be reflected in DB_RECOVERY_FILE_DEST_SIZE

     

              3)    Delete archive logs that are no longer needed and set ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY

Delete archive logs that are no longer needed and set ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY

Removing archive logs that are no longer needed is a quick and easy way to free up space. Using RMAN is the recommended method and removes a lot of the guess work out of which logs to remove.

In RMAN you can use the delete archivelog all completed after|before|between ‘date’; command to remove archive logs that are no longer needed. Below is an example.

 
RMAN> delete archivelog all completed before 'sysdate';

Setting the ARCHIVE DELETION POLICTY TO APPLIED ON STANDBY is the best solution for ongoing archive log maintenance on the Data Guard Standby.


More :

ASM Real-Time Scenarios : Click Here


Performance Tuning Basics