Rman Change Command in Oracle

Rman Change Comamnd : Change command update the status of a backup or copy in the RMAN repository.The purpose of the Change command is to change the status of backups, copies, and archived logs in the repository to AVAILABLE or UNAVAILABLE. This feature is useful when a previously unavailable file is made available again, or we do not want a specific backup or copy to be eligible to be restored but also do not want to delete it.

The CHANGE command can alter the repository status of usable backups and copies from prior incarnations.It  removes the catalog records for backups and copies, and update the corresponding records in the target control file to status DELETED. This feature is useful when we remove a file by using an operating system command rather than the RMAN CHANGE command, and want to remove its repository record as well.

Restrictions and Usage Notes :

• The target instance must be started.
• The KEEP FOREVER clause requires use of a recovery catalog.
• we cannot use CHANGE... UNAVAILABLE or KEEP attributes for files stored in the flash recovery area.

The only CHANGE command that requires either a manual or automatic maintenance channel is the CHANGE ... AVAILABLE command. However, a maintenance channel is not required when CHANGE ... AVAILABLE is used with a file that is disk only (that is, an archivelog, datafile copy, or controlfilecopy). Here are the option used with the Change Command :

AVAILABLE  :  Changes the status of a backup or copy to AVAILABLE in the repository. View the status in the LIST output or recovery catalog views.

keepOption   :  Changes the exemption status of a backup or copy in relation to the configured retention policy. For example, specify CHANGE ... NOKEEP to make a backup that is currently exempt from the retention policy eligible for OBSOLETE status.

Note: we cannot use this option with flash recovery area files.

UNAVAILABLE  :   Changes the status of a backup or copy to UNAVAILABLE in the repository. View the status in the LIST output or recovery catalog views. This option is provided for cases when the file cannot be found or has migrated offsite. RMAN does not use a file that is marked UNAVAILABLE in a RESTORE or RECOVER command. If the file is later found or returns to the main site, then use the AVAILABLE option to update its status.

UNCATALOG  :    Removes references to a datafile copy, backup piece, or archived redo log from the recovery catalog, and updates records in the target control file to status DELETED. The CHANGE ... UNCATALOG command does not touch physical backups and copies. Use this command to notify RMAN when a file is deleted by some means other than a DELETE command.

Caution: If we resynchronize from a backup control file, or upgrade the recovery catalog, then uncataloged records can sometimes reappear in the catalog metadata.

Here are few Examples : 

1.) UNAVAILABLE option   :  This example changes the status of backup set 100 as well as all backups of server parameter files created more than a day ago to UNAVAILABLE:

RMAN > CHANGE BACKUPSET 100 UNAVAILABLE;

RMAN > CHANGE BACKUP OF SPFILE COMPLETED BEFORE 'SYSDATE-1' UNAVAILABLE;

2.) Uncataloging and Cataloging Archived Logs  :  In this example, we move all archived logs to a new directory, uncatalog them, and then recatalog them in the new location:

RMAN > HOST move 'D:\archive\'  'D:\stand_arch\' ;

RMAN > CHANGE ARCHIVELOG ALL UNCATALOG;

RMAN > CATALOG START WITH 'D:\stand_arch\';

3.) Changing the Retention Status of a Backupset : This example, which requires a recovery catalog, changes an ordinary backup into a long-term backup:

RMAN > CHANGE BACKUP TAG 'consistent_db_bkup' KEEP FOREVER NOLOGS;

Enjoy      :-)

ORA-04043 , ORA-00942 : object does not exist

Today, i have face very usual error. I have imported a table from ms-excess into "scott" schemas. when i check the table in scott schemas,i found it was there.And when try to access the table it throws as error "ORA-00942 " . I get puzzled.

SQL> select * from tab;
TNAME                          TABTYPE              CLUSTERID
-------------------------      --------------                ----------
BONUS                          TABLE
DEPT                             TABLE
EMP                               TABLE
SALGRADE                   TABLE
Table11                          TABLE

SQL> select * from table11;
select * from table11
              *
ERROR at line 1:
ORA-00942: table or view does not exist

then, i have decided to describe the table, and got the error "ORA-04043"  .

SQL> desc Table11
ERROR:
ORA-04043: object Table11 does not exist

SQL> desc dept
 Name                                      Null?                           Type
 --------------------------            -------------             ---------------------
 DEPTNO                               NOT NULL           NUMBER(2)
 DNAME                                                               VARCHAR2(14)
 LOC                                                                    VARCHAR2(13)

While in case of table "dept"  it is working fine,then i have decide to rename the table, so that it may solved.

SQL> rename Table11 to emp1;
rename Table11 to emp1
*
ERROR at line 1:
ORA-04043: object TABLE11 does not exist

After some analysis i come to conclusion that the table are export from the ms-excess and ms-excess support the char datatype i.e, it is right padded . So to solve this issue, i  put the table in double quotes to excess the table. For detail click here 

SQL> desc "Table11"
 Name                                         Null?                           Type
 -----------------------------               --------             ------------------------
 ID                                                                          VARCHAR2(20)
 ACCOUNTNO                                                      BINARY_DOUBLE
 TEMPLATENO                                                     BINARY_DOUBLE
 DEFAULTTEMPLATE                                          BINARY_DOUBLE

ORA_04043 is an special error and cause due to various reason. Few Possible causes are :
- An attempt was made to rename an index or a cluster, or some other object that cannot be renamed.
- An invalid name for a table, view, sequence, procedure, function, package, or package body was entered.


Enjoy     :-) 

Difference Between Char,Varchar and Varchar2

The fact that a CHAR/NCHAR is really nothing more than a VARCHAR2/NVARCHAR2 in disguise makes me of  the opinion that there are really only two character string types to ever consider, namely VARCHAR2 and NVARCHAR2. I have never found a use for the CHAR type in any application. Since a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot “find” their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed .

Here is Demo which will clear our doubt about the char and varchar .

1.) CHAR   :   Char should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.

SQL> create table char_test (col1 CHAR(10));
Table created.

SQL> insert into  char_test  values ('qwerty');
1 row created.                                                                                                                                            
COL1             LENGTH(COL1)           ASCII Dump
------               -----------                     ----------------------------------------------------------
qwerty             10                              Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32

2.) VARCHAR  :   Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.

SQL> create table  varchar_test (col1 varchar2(10));
Table created.

SQL> insert into varchar_test  values  ('qwerty');
1 row created.

SQL> select  col1, length(col1), dump(col1) "ASCII Dump"  from varchar_test;
COL1           LENGTH(COL1)            ASCII Dump
-------          ------------                        --------------------------------------------------
qwerty            6                                   Typ=1 Len=6: 113,119,101,114,116,121

3.) VARCHAR2  :   Varchar2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.

SQL> create table  varchar2_test (col1 varchar2(10));
Table created.

SQL> insert into varchar2_test values ('qwerty');
1 row created.

SQL>  select  col1, length(col1), dump(col1) "ASCII Dump"  from  varchar2_test;
COL1                LENGTH(COL1)                ASCII Dump
----------           ------------------                     ------------------------------------------
qwerty                     6                                  Typ=1 Len=6: 113,119,101,114,116,121


Below is an another example of  "char"  which will help us to understand the concept  :

SQL> select * from char_test where col1 = 'qwerty';
COL1
----------
qwerty

SQL> variable y varchar2(25);
SQL> exec :y := 'qwerty'
PL/SQL procedure successfully completed.

SQL> select * from char_test where col1 = :y;
no rows selected

SQL> select * from char_test where col1 = rpad(:y,10);
COL1
----------
qwerty

Notice how when doing the search with a varchar2 variable (almost every tool in the world uses this type), we have to rpad() it to get a hit. If the field is in fact always 10 bytes long, using a char will not hurt -- However, it will not help either.

The only time I personally use a CHAR type is for CHAR(1).  And that is only because its faster to type char(1) then varchar2(1) --  it offers no advantages.(according to t kytes).


Enjoy     :-)

Myth of Commit Causing Buffer to be Flushed to the Disk

Today I have gone through a post of one of the famous Oracle Expert Anup Nanda. The post is very useful and invaluable.This Post is regarding the commit statements. There are very few person who have the knowledge of the oracle internals and Arup is one of them . There is a general confusion that commit means that the data are written to the disk but it is not always. Below is the link regarding the commits statements and hope this will help u  .  

http://arup.blogspot.com/2010/12/100-things-you-probably-didnt-know.html

Enjoy    :-) 

What is Checkpoint ?

A checkpoint is an operation that Oracle performs to ensure data file consistency. When a checkpoint occurs, Oracle ensures all modified buffers are written from the data buffer to disk files. Frequent checkpoints decrease the time necessary for recovery should the database crash, but may decrease overall database performance.                                                                                                                                                 

A checkpoint performs the following three operations:

1.) Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk.  It's the DBWR that writes all modified databaseblocks back to the datafiles.                                                           

2.) The latest SCN is written (updated) into the datafile header.

3.) The latest SCN is also written to the controlfiles.

The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Checkpoints occur in a variety of situations. For example, Oracle Database uses the following types of checkpoints:

1.) Thread checkpoints  :     The database writes to disk all buffers modified by redo in a specific thread before a certain target. The set of thread checkpoints on all instances in a database is a database checkpoint. Thread checkpoints occur in the following situations:

• Consistent database shutdown .
• ALTER SYSTEM CHECKPOINT statement . 
• Online redo log switch .
• ALTER DATABASE BEGIN BACKUP statement                                                                             

2.) Tablespace and data file checkpoints  :    The database writes to disk all buffers modified by redo before a specific target. A tablespace checkpoint is a set of data file checkpoints, one for each data file in the tablespace. These checkpoints occur in a variety of situations, including making a tablespace read-only or taking it offline normal, shrinking a data file, or executing ALTER TABLESPACE BEGIN BACKUP.

3.) Incremental checkpoints  :     An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every three seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers.

Other types of checkpoints include instance and media recovery checkpoints and checkpoints when schema objects are dropped or truncated.

Importance of Checkpoints for Instance Recovery  :                                                                               

Instance recovery uses checkpoints to determine which changes must be applied to the data files. The checkpoint position guarantees that every committed change with an SCN lower than the checkpoint SCN is saved to the data files.

Checkpoint Position in Online Redo Log File

During instance recovery, the database must apply the changes that occur between the checkpoint position and the end of the redo thread. As shown in Figure, some changes may already have been written to the data files. However, only changes with SCNs lower than the checkpoint position are guaranteed to be on disk.

Time and SCN of last checkpoint   :

The date and time of the last checkpoint can be retrieved through checkpoint_time in  v$datafile_header view

The SCN of the last checkpoint can be found in v$database.

Enjoy      J J J