RAM&KRISHNA BLOG

NOTE: Once enabled; this new 10g feature records the modified since last backup and stores the log of it in a block change tracking file. During backups RMAN uses the log file to identify the specific blocks that must be backed up. This improves RMAN's performance as it does not have to scan whole datafiles to detect changed blocks. Logging of changed blocks is performed by the CTWR process which is also responsible for writing data to the block change tracking file. When using Oracle block change tracking we see this procedure.  As data blocks change, the Change Tracking Writer (CTWR) background process tracks the changed blocks in a private area of memory. When a commit is issued against the data block, the block change tracking information is copied to a shared area in Large Pool called the CTWR buffer. During the checkpoint, the CTWR process writes the information from the CTWR RAM buffer to the change-tracking file. After enabling change tracking, the first level

Please see below ... We have encountered the below situation many times. The space is not getting released after dropping the data files in LINUX machine.   SQL> drop user TMP561 cascade; SQL> drop tablespace TMP561 including contents and datafiles; Note: The schema size is around 300Gig. And its having 11 datafiles each  data-file 30G. But after dropping the User/Tbs. Space is not reclaimed in the Server. SOLUTION: After dropping the datafiles still one process is holding these files. Check like below oracle@/oracle/tmp: /usr/sbin/lsof |grep deleted |grep TMP* oracle     13683    oracle   17u      REG             253,16 33286004736   40550413 /user/TMP/TMP561_01.dbf (deleted) oracle     13683    oracle   18u      REG             253,16 33286004736   40550414 /user/TMP/TMP561_02.dbf (deleted) oracle     13683    oracle   20u      REG             253,16 32212262912  206487553 /user/TMP/TMP561_03.dbf (deleted) oracle     13683    oracle   21u      R

RMAN - Setup of the Recovery Catalog: Setting up the recovery catalog RMAN is the first task the DBA completes when preparing to use RMAN. Lets look at the basic steps required to set up the recovery catalog. Creating The Recovery Catalog Creating the recovery catalog is not a terribly complicated process. It requires the DBA to set up a user in the database that will be used to store the recover catalog schema objects. Then the DBA needs to grant certain user rights to that userid, and then run a script to create the recovery catalog. The example below shows the creation of a user/schema in a database that will store the recovery catalog: CREATE USER MYRMAN IDENTIFIED BY MYRMAN DEFAULT TABLESPACE myrman_catalog_tbs TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON myrman_catalog_tbs; Once the user has been created, follow these steps to complete the setup of the schema and to create the recovery catalog in that schema: GRANT the userid (in this example, myrm

RMAN - Data Dictionary Views Several views exist to assist the DBA in interacting with RMAN. The views from the target database include: View Description V$ARCHIVED_LOG Displays information about archived redo logs from the control file. V$BACKUP_CORRPUTION Displays information about corrupted blocks discovered during database backups with RMAN. V$BACKUP_DATAFILE Provides information from the control file about backup datafiles and control files. V$BACKUP_DEVICE Provides information about various devices available from various third-party manufacturers. V$BACKUP_PIECE Displays information about all backup sets from the control file. V$BACKUP_REDOLOG Displays information about backed up redo logs in backup sets. V$BACKUP_SET Displays information about all backup sets from the control file. V$DATAFILE Lists datafile information from the database. V$DATAFILE_HEADER Lists information about database datafile headers. Selected views that are available f

Registering The Database to RMAN catalog database: Need to start RMAN as follows: RMAN target=sys/password@database_to_backup rcvcat=sys/password@recovery_catalog_database Another variation on the command, if the recovery catalog and the database were on the same server, might be as shown: oraenv ORACLE_SID = [KKUY] ? KKUY RMAN rcvcat=sys/password@recovery_catalog_database RMAN> connect target Recovery Manager: Release 8.0.5.1.0 - Production RMAN-06005: connected to target database: KKUY RMAN-06008: connected to recovery catalog database Use the below command to register the database. RMAN>register database; Want to verify if a database is registered in the recovery catalog. To do this, connect to RMAN and issue the command LIST INCARNATION OF DATABASE. RMAN> list incarnation of database; RMAN-03022: compiling command: list RMAN-06240: List of Database Incarnations RMAN-06241: DB Key Inc Key DB Name DB ID      CUR Reset SCN   Reset Time RMAN

RMAN - Backup File Optimization Feature Overall backup times of a database can be reduced when you choose not to back up your read-only or offline tablespaces all the time. It used be quite tedious to track when these tablespaces were last backed up. With Oracle9i, RMAN offers the backup file optimization (BFO) feature. With BFO, if RMAN is about to back up a file, and the file has already been backed up by the same device type, RMAN will skip the backup of that file. This includes both data files and archived redo logs backed up by RMAN. Oracle determines if the file has changed by comparing the datafile to the header of the backed up datafile. If they match, the file is not backed up. Before RMAN decides to skip a given datafile, it will determine if the datafile backup that is available meets the established retention policy. If the latest backup violates the retention policy, then RMAN will back up the datafile anyway. There may be cases when you want to back up all tabl

Alert Log: [oracle@ bdump]$ view alert_HDRTG12.log Thu May 28 22:05:42 PDT 2015 Process P021 died, see its trace file Thu May 28 22:05:47 PDT 2015 Process m000 died, see its trace file Thu May 28 22:05:47 PDT 2015 ksvcreate: Process(m000) creation failed Thu May 28 22:06:00 PDT 2015 Process P021 died, see its trace file Thu May 28 22:06:10 PDT 2015 Process P021 died, see its trace file Thu May 28 22:06:43 PDT 2015 [oracle@bdump]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Thu May 28 23:38:59 2015 Copyright (c) 1982, 2010, Oracle.  All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select name from v$database; NAME --------------------------- HDRTG12 SQL> select * from v$resource_limit where RESOURCE_NAME in ('sessions','processes','transactions'); RESOURCE

SQL> select name from v$database; NAME --------- UHTG1P SQL> drop tablespace TEST including contents and datafiles; drop tablespace TEST including contents and datafiles * ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys SOLUTION 1: SQL> drop tablespace TEST including contents and datafiles cascade constraints; Tablespace dropped. NOTE: The CASCADE CONSTRAINTS clause drops all referential integrity constraints that refer to the primary and unique keys defined on the dropped columns. SOLUTION 2: Other way we can drop the user first and then the tablespace. No need to specify cascade option here. Thank You!

RECOVER WINDOW AND REDUNDANCY The redundancy retention policy defines a fixed number of backups that need to be retained by RMAN. If there are a number of backups later than the number defined by the retention policy, then those backups can be deleted using the DELETE OBSOLETE command, which we will discuss in the next section. To set a redundancy retention policy, use the configure retention policy command, as shown here: RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 7; RMAN> show all; RMAN configuration parameters are: RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1; - NOTE: Here it will keep only 1 backup copy. Rest all previous backups marked as OBSOLETE RMAN> REPORT OBSOLETE; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of obsolete backups and copies Type                 Key    Completion Time    Filename/Handle -------------------- ------ ------------------ -----------

RMAN: Recovery Manager (RMAN) is an Oracle utility that can back up, restore, and recover database files. The product is a feature of the Oracle database server and does not require separate installation. Recovery Manager is a client/server application that uses database server sessions to perform backup and recovery. RMAN must include the following components: A TARGET DATABASE: An Oracle database, To which RMAN is connected with the TARGET keyword.  A target database is a database on which RMAN is performing backup and recovery operations. RMAN always maintains metadata about its operations on a database in the control file of the database. The RMAN metadata is known as the RMAN repository. RMAN CLIENT: The RMAN executable is automatically installed with the database and is typically located in the same directory as the other database executables. For example, the RMAN client on Linux is located in $ORACLE_HOME/bin. Some environments use the following opt