youngprogrammersclub

[youngprogrammersclub]

RMAN QUICK LEARN– FOR THE BEGINNERS

Oracle Recovery Manager (RMAN) is Oracle’s preferred method or tools by which we are able to take backups and restore and recover our database. You must develop a proper backup strategy which provides maximum flexibility to Restore & Recover the DB from any kind of failure. To develop a proper backup strategy you must decide the type of requirement then after think the possible backup option. The recommended backup strategy must include the backup of all datafiles, Archivelog and spfile & controlfile autobackup. To take online or hot backups database must be in archivelog mode. You can however use RMAN to take an offline or cold backup.Note: Selecting the backup storage media is also important consideration. If you are storing your backup on disk then it is recommended to keep an extra copy of backup at any other server. CREATING RECOVERY CATALOG: Oracle recommended to use separate database for RMAN catalog. Consider in below steps the database is already created: 1. Create tablespace for RMAN: SQL> create tablespace RTBS datafile 'D:ORACLEORADATARTBS01.DBF' size 200M            extent management local uniform size 5M; 2. Create RMAN catalog user: SQL> create user CATALOG identified by CATALOG default tablespace RTBS quota unlimited on RTBS; 3. Grant some privileges to RMAN user: SQL> Grant connect, resource to CATALOG; SQL> grant recovery_catalog_owner to CATALOG; 4. Connect into catalog database and create the catalog: % rman catalog RMAN_USER/RMAN_PASSWORD@cat_db log=create_catalog.log RMAN> create catalog tablespace RTBS; RMAN> exit; 5. Connect into the target database and into the catalog database: % rman target sys/oracle@target_db RMAN> connect catalog RMAN_USER/RMAN_PASSWORD@cat_db 6. Connected into the both databases, register target database: RMAN> register database; The following list gives an overview of the commands and their uses in RMAN. For details description search the related topics of separate post on my blog: http://shahiddba.blogspot.com/INITIALIZATION PARAMETER: Some RMAN related database initialization parameters: control_file_record_keep_time: Time in days to retention records in the Control File. (default: 7 days) large_pool_size: Memory pool used for RMAN in backup/restore operations. shared_pool_size: Memory pool used for RMAN in backup/restore operations (only if large pool is not configured). CONNECTING RMANexport ORACLE_SID=  --Linux platformset ORACLE_SID==    --Windows platformTo connect on a target database execute RMAN.EXE then RMAN>connect target / RMAN>connect target username/password RMAN>connect target username/password@target_db To connect on a catalog database:RMAN>connect catalog username/password RMAN>connect catalog username/password@catalog_db To connect directly from the command prompt:C:>rman target /                 --target with nocatalog Recovery Manager: Release 9.2.0.1.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.connected to target database: RMAN (DBID=63198018)using target database controlfile instead of recovery catalogC:>rman target sys/oracle@orcl3 catalog catalog/catalog@rman  --with catalogRecovery Manager: Release 9.2.0.1.0 - ProductionCopyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.connected to target database: SADHAN (DBID=63198018)connected to recovery catalog databaseRMAN PARAMETERSRMAN parameters can be set to a specified value and remain persistent. This information is stored in the target database’s controlfile (By default). Alternatively you can store this backup information into recovery catalog. If you connect without catalog or only to the target database, your repository should be in the controlfile.SHOW/CONFIGURE – SHOW command will show current values for set parameters and CONFIGURE – Command to set new value for parameterRMAN> show all;using target database control file instead of recovery catalogRMAN configuration parameters are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # defaultCONFIGURE BACKUP OPTIMIZATION OFF; # defaultCONFIGURE DEFAULT DEVICE TYPE TO ; # defaultCONFIGURE CONTROLFILE AUTOBACKUP ON;RMAN>show datafile backup copies; RMAN>show default device type; RMAN>show device type; RMAN>show channel; RMAN>show retention policy;RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;old RMAN configuration parameters:CONFIGURE CONTROLFILE AUTOBACKUP OFF;new RMAN configuration parameters:CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored CONFIGURE channel device type disk format 'D:oraback%U'; You can set many parameters by configuring them first and making them persistent or you can override them (discard any persistent configuration) by specifying them explicitly in your RMAN backup command. Setting Default Recommended Controlfile autobackup off on Retention policy to redundancy 1 to recovery window of 30 days Device type disk parallelism 1 ... disk|sbt prallelism 2 ... Default device type to disk to disk Backup optimization off off Channel device type none disk parms=‘...’ Maxsetsize unlimited depends on your database size Appending CLEAR or NONE at the last of configuration parameter command will reset the configuration to default and none setting.CONFIGURE RETENTION POLICY CLEAR;CONFIGURE RETENTION POLICY NONE; Overriding the configured retention policy: change backupset 421 keep forever nologs; change datafilecopy 'D:oracleoradatausers01.dbf' keep until 'SYSDATE+30';RMAN BACKUP SCRIPTS:Backing up the database can be done with just a few commands or can be made with numerous options. RMAN> backup database;RMAN> backup as compressed backupset database;RMAN> Backup INCREMENTAL level=0 database;RMAN> Backup database TAG=Weekly_Sadhan;RMAN> Backup database MAXSETSIZE=2g;RMAN> backup TABLESPACE orafin;You may also combine options together in a single backup and for multi channel backup.RMAN> Backup INCREMENTAL level=1 as COMPRESSED backupset databaseFORMAT 'H:ORABACK%U' maxsetsize 2G; backup full datafile x,y,z incremental level x include current controlfile archivelog all  delete input copies x filesperset x maxsetsize xM diskratio x format = 'D:oraback%U';run {allocate channel d1 type disk FORMAT "H:orabackWeekly_%T_L0_%d-%s_%p.db";allocate channel d2 type disk FORMAT "H:orabackWeekly_%T_L0_%d-%s_%p.db";allocate channel d3 type disk FORMAT "H:orabackWeekly_%T_L0_%d-%s_%p.db";      backup             incremental level 0 tag Sadhan_Full_DBbackup            filesperset 8            FORMAT "H:orabackWeekly_%T_FULL_%d-%s_%p.db" DATABASE;            SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';      backup            archivelog all tag Sadhan_Full_Archiveback            filesperset 8            format "H:orabackWeekly_%T_FULL_%d-%s_%p.arch"; release channel d1; release channel d2; release channel d3; } The COPY command and some copy scripts: copy datafile 'D:oracleoradatausers01.dbf' TO 'H:orabackusers01.dbf' tag=DF3, datafile 4 to TO 'H:orabackusers04.dbf' tag=DF4, archivelog 'arch_1060.arch' TO 'arch_1060.bak' tag=CP2ARCH16; run { allocate channel c1 type disk; copy datafile 'd:oracleoradatausers01.dbf' TO 'h:orabackusers01.dbf' tag=DF3, archivelog 'arch_1060.arch' TO 'arch_1060.bak' tag=CP2ARCH16;    }COMPRESSED – Compresses the backup as it is taken.INCREMENTAL – Selecting incremental allows to backup only changes since last full backup.FORMAT – Allows you to specify an alternate location.TAG – You can name your backup.MAXSETSIZE – Limits backup piece size.TABLESPACE – Allows you to backup only a tablespace.RMAN MAINTAINANCE :You can review your RMAN backups using the LIST command. You can use LIST with options to customize what you want RMAN to return to you.RMAN> list backup SUMMARY;RMAN> list ARCHIVELOG ALL;RMAN> list backup COMPLETED before ‘02-FEB-09’;RMAN> list backup of database TAG Weekly_sadhan; RMAN> list backup of datafile "D:oracleoradatasadhanusers01.dbf" SUMMARY;You can test your backups using the validate command.RMAN> list copy of tablespace "SYSTEM"; You can ask RMAN to report backup information. RMAN> restore database validate; RMAN> report schema; RMAN> report need backup; RMAN> report need backup incremental 3 database; RMAN> report need backup days 3; RMAN> report need backup days 3 tablespace system; RMAN>report need backup redundancy 2; RMAN>report need backup recovery window of 3 days; RMAN> report unrecoverable; RMAN> report obsolete; RMAN> delete obsolete; RMAN> delete noprompt obsolete; RMAN> crosscheck; RMAN> crosscheck backup; RMAN> crosscheck backupset of database; RMAN> crosscheck copy; RMAN> delete expired;   --use this after crosscheck command RMAN> delete noprompt expired backup of tablespace users; To delete backup and copies: RMAN> delete backupset 104; RMAN> delete datafilecopy 'D:oracleoradatausers01.dbf'; To change the status of some backups or copies to unavailable come back to available: RMAN>change backup of controlfile unavaliable; RMAN>change backup of controlfile available; RMAN>change datafilecopy 'H:orabackusers01.dbf' unavailable; RMAN>change copy of archivelog sequence between 230 and 240 unavailable; To catalog or uncatalog in RMAN repository some copies of datafiles, archivelogs and controlfies made by users using OS commands: RMAN>catalog datafilecopy 'F:orabacksample01.dbf'; RMAN>catalog archivelog 'E:oraclearch_404.arc', 'F:oraclearch_410.arc'; RMAN>catalog controlfilecopy 'H:oracleoradatacontrolfile.ctl'; RMAN> change datafilecopy 'F:orabacksample01.dbf' uncatalog; RMAN> change archivelog 'E:oraclearch_404.arc', 'E:oraclearch_410.arc' uncatalog; RMAN> change controlfilecopy 'H:oracleoradatacontrolfile.ctl' uncatalog; RESTORING & RECOVERING WITH RMAN BACKUPYou can perform easily restore & recover operation with RMAN. Depending on the situation you can select either complete or incomplete recovery process. The complete recovery process applies all the redo or archivelog where as incomplete recovery does not apply all of the redo or archive logs. In this case of recovery, as you are not going to complete recovery of the database to the most current time, you must tell Oracle when to terminate recovery. Note: You must open your database with resetlogs option after each incomplete recovery. The resetlogs operation starts the database with a new stream of log sequence number starting with sequence 1. DATAFILE – Restore specified datafile.CONTROLFILE – To restore controlfile from backup database must be in nomount.ARCHIVELOG   or ARCHIVELOG from until – Restore archivelog to location there were backed up.TABLESPACE – Restores all the datafiles associated with specified tablespace. It can be done with database open.RECOVER TABLESPACE/DATAFILE:If a non-system tablespace or datafile is missing or corrupted, recovery can be performed while the database remains open.STARTUP; (you will get ora-1157 ora-1110 and the name of the missing datafile, the database will remain mounted)Use OS commands to restore the missing or corrupted datafile to its original location, ie: cp -p /user/backup/uman/user01.dbf /user/oradata/u01/dbtst/user01.dbfSQL>ALTER DATABASE DATAFILE3 OFFLINE; (tablespace cannot be used because the database is not open)SQL>ALTER DATABASE OPEN;SQL>RECOVER DATAFILE 3;SQL>ALTER TABLESPACE ONLINE; (Alternatively you can use ‘alter database’ command to take datafile online)If the problem is only the single file then restore only that particualr file otherwise restore & recover whole tablespace. The database can be in use while recovering the whole tablespace.run { sql ‘alter tablespace users offline’; allocate channel c1 device type disk|sbt; restore tablespace users; recover tablespace users; sql ‘alter tablespace users online’;}If the problem is in SYSTEM datafile or tableapce then you cannnot open the database. You need sifficient downtime to recover it. If problem is in more than one file then it is better to recover whole tablepace or database.startup mountrun { allocate channel c1 device type disk|sbt; allocate channel c2 device type disk|sbt; restore database check readonly; recover database;  alter database open;}DATABASE DISASTER RECOVERY:Disaster recovery plans start with risk assessment. We need to identify all the risks that our data center can face such as: All datafiles are lost, All copies of current controlfile are lost, All online redolog group member are lost, Loss of OS, loss of a disk drive, complete loss of our server etc: Our disaster plan should give brief description about recovery from above disaster. Planning Disaster Recovery in advance is essential for DBA to avoid any worrying or panic situation.The below method is used for complete disaster recovery on the same as well as different server. set dbid=xxxxxxxstartup nomount;run {allocate channel c1 device type disk|sbt;restore spfile to ‘some_location’ from autobackup;recover database; alter database open resetlogs;}shutdown immediate;startup nomount;run { allocate channel c1 device type disk|sbt; restore controlfile from autobackup;alter database mount; } RMAN> restore database;RMAN> recover database;   --no need incase of cold backupRMAN> alter database open resetlogs;}DATABASE POINT INTIME RECOVERY:DBPITR enables you to recover a database to some time in the past. For example, if logical error occurred today at 10.00 AM, DBPITR enables you to restore the entire database to the state it was in at 09:59 AM there by removing the effect of error but also remove all other valid update that occur since 09:59 AM. DBPTIR requires the database is in archivelog mode, and existing backup of database created before the point in time to which you wish to recover must exists, and all the archivelog and online logs created from the time of backup until the point in time to which you wish to recover must exist as well. RMAN> shutdown Abort; RMAN> startup mount; RMAN> run {   Set until time to_date('12-May-2012 00:00:00′, ‘DD-MON-YYYY  HH24:MI:SS');   restore database;   recover database;          }RMAN> alter database open resetlogs;Caution: It is highly recommended that you must backup your controlfile and online redo log file before invoking DBPITR. So you can recover back to the current point in time in case of any issue.Oracle will automatically stop recovery when the time specified in the RECOVER command has been reached. Oracle will respond with a recovery successful message.SCN/CHANGE BASED RECOVERY:Change-based recovery allows the DBA to recover to a desired point of System change number (SCN). This situation is most likely to occur if archive logfiles or redo logfiles needed for recovery are lost or damaged and cannot be restored.Steps:–        If the database is still open, shut down the database using the SHUTDOWN command with the ABORT option.–        Make a full backup of the database including all datafiles, a control file, and the parameter files in case an error is made during the recovery.–        Restore backups of all datafiles. Make sure the backups were taken before the point in time you are going to recover to. Any datafiles added after the point in time you are recovering to should not be restored. They will not be used in the recovery and will have to be recreated after recovery is complete. Any data in the datafiles created after the point of recovery will be lost.–        Make sure read-only tablespace are offline before you start recovery so recovery does not try to update the datafile headers.RMAN> shutdown Abort; RMAN> startup mount; RMAN>run {  set until SCN 1048438;  restore database;  recover database;  alter database open resetlogs;   }RMAN> restore database until sequence 9923; --Archived log sequence number RMAN> recover database until sequence 9923; --Archived log sequence number RMAN> alter database open resetlogs;Note: Query with V$LOG_HISTORY and check the alert.log to find the SCN of an event and recover to a prior SCN.IMPORTANT VIEW: Views to consult into the target database: v$backup_device: Device types accepted for backups by RMAN. v$archived_log:  Redo logs archived. v$backup_corruption: Corrupted blocks in backups. v$copy_corruption: Corrupted blocks in copies. v$database_block_corruption: Corrupted blocks in the database after last backup. v$backup_datafile: Backups of datafiles. v$backup_redolog: Backups of redo logs. v$backup_set: Backup sets made. v$backup_piece: Pieces of previous backup sets made. v$session_long_ops: Long operations running at this time. Views to consult into the RMAN catalog database: rc_database: Information about the target database. rc_datafile: Information about the datafiles of target database. rc_tablespace: Information about the tablespaces of target database. rc_stored_script: Stored scripts. rc_stored_script_line: Source of stored scripts. For More Information on RMAN click on the below link: Different RMAN Recovery Scenarios 24-Feb-13 Synchronizes the Test database with RMAN Cold Backup 16-Feb-13 Plan B: Renovate old Apps Server Hardware 27-Jan-13 Plan A: Renovate old Apps Server Hardware 25-Jan-13 Planning to Renovate old Apps Server Hardware 24-Jan-13 Duplicate Database with RMAN without Connecting to Target Database 23-Jan-13 Different RMAN Errors and their Solution 24-Nov-12 Block Media Recovery using RMAN 4-Nov-12 New features in RMAN since Oracle9i/10g 14-Oct-12 A Shell Script To Take RMAN Cold/Hot and Export Backup 7-Oct-12 Automate Rman Backup on Windows Environment 3-Sep-12 How to take cold backup of oracle database?‎ 26-Aug-12 Deleting RMAN Backups 22-Aug-12 Script: RMAN Hot Backup on Linux Environment 1-Aug-12 How RMAN behave with the allocated channel during backup 31-Jul-12 RMAN Important Commands Description. 7-Jul-12 Script: Crontab Use for RMAN Backup 2-Jun-12 RMAN Report and Show Commands 16-May-12 RMAN backup on a Windows server thruogh DBMS_SCHEDULING 15-May-12 Format Parameter of Rman Backup 12-May-12 Rman Backup with Stored Script 12-May-12 Rman: Disaster Recovery from the Scratch 6-May-12 RMAN- Change-Based (SCN) Recovery 30-Apr-12 RMAN-Time-Based Recovery 30-Apr-12 RMAN – Cold backup Restore 23-Apr-12 RMAN Backup on Network Storage 22-Apr-12 Rman Catalog Backup Script 18-Apr-12 Point to be considered with RMAN Backup Scripts 11-Apr-12 Monitoring RMAN Through V$ Views 7-Apr-12 RMAN Weekly and Daily Backup Scripts 25-Mar-12 Unregister Database from RMAN: 6-Mar-12

[youngprogrammersclub]

Different RMAN Recovery Scenarios

In this article we will discuss about different type of database recovery scenarios. The target database name as well as catalog database can be different. Consider you have sufficient backup for this example such as daily incremental backup for all targets database on (Sat-Thurs day) and Weekly full backup on (Friday). The Daily and weekly backup scripts includes datafile, archive log and control & spfile autobackup. The target databases versions are Oracle 9i/10g where as platform can be windows 2003 and LINUX.  The motive of this article is to give the fresher or Junior DBA confidence “How to apply different Recovery scenario in different situation”. Some of the scenario’s recovery logs are from our Production database and some of them taken from other DBA’s. One fine morning our DBserver is restarted due to hard disk crashed and OS issue. Once the disk is repaired and OS is restored then the database is mounted successfully but we cannot able to open the database. It needs media recovery with the following errors. As most of the tablespace datafiles are corrupted, we decided to do complete database recovery. SQL> startup; Database mounted. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: ‘D:ORACLEORADATASADHANSYSTEM01.DBF' The corruption happens Wednesday morning session so we have daily incremental night backup of Sun-Wed with full backup of Saturday (29thSeptember 2012) and entire month archive log on the disk. We just mounted the database. C:rman target sys/****@sadhan catalog catalog/catalog@rman Recovery Manager: Release 9.2.0.1.0 – Production Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved. RMAN> SHUTDOWN IMMEDIATE;database dismountedOracle instance shut downRMAN> STARTUP MOUNT;connected to target database (not started)RMAN> RESTORE DATABASE;Starting restore at 03-OCT-12allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=13 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:ORACLEORADATASADHANSYSTEM01.DBFrestoring datafile 00002 to D:ORACLEORADATASADHANUNDOTBS01.DBFrestoring datafile 00009 to G:ORA_DBF_EXTENDEDSDH_HRMS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKMONTHLY_20120929_FULL_SADHAN-2981_1.DB tag=SADHAN_FULL_D BBACKUP params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00005 to D:ORACLEORADATASADHANTOOLS01.DBFrestoring datafile 00011 to G:ORA_DBF_EXTENDEDSDH_SHTR01.DBFrestoring datafile 00012 to G:ORA_DBF_EXTENDEDSDH_FIN01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKMONTHLY_20120929_FULL_SADHAN-2983_1.DB tag=SADHAN_FULL_D BBACKUP params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00007 to D:ORACLEORADATASADHANSDH_TIMS01.DBFrestoring datafile 00013 to F:ORACLESADHANSDH_EDSS02.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKMONTHLY_20120929_FULL_SADHAN-2985_1.DB tag=SADHAN_FULL_D BBACKUP params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00004 to D:ORACLEORADATASADHANINDX01.DBFrestoring datafile 00010 to F:ORACLESADHANSDH_EDSS01.DBFrestoring datafile 00014 to D:ORACLEORADATASADHANINDX02.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKMONTHLY_20120929_FULL_SADHAN-2982_1.DB tag=SADHAN_FULL_D BBACKUP params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00003 to D:ORACLEORADATASADHANEXAMPLE01.DBFrestoring datafile 00006 to D:ORACLEORADATASADHANUSERS01.DBFrestoring datafile 00008 to D:ORACLEORADATASADHANSDH_TIMS02.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKMONTHLY_20120929_FULL_SADHAN-2984_1.DB tag=SADHAN_FULL_D BBACKUP params=NULLchannel ORA_DISK_1: restore completeFinished restore at 03-OCT-12RMAN> RECOVER DATABASE;Starting recover at 03-OCT-12using channel ORA_DISK_1channel ORA_DISK_1: starting incremental datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: D:ORACLEORADATASADHANSYSTEM01.DBFdestination for restore of datafile 00002: D:ORACLEORADATASADHANUNDOTBS01.DBFdestination for restore of datafile 00009: G:ORA_DBF_EXTENDEDSDH_HRMS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKDAILY_20121003_CUMUL_SADHAN-3036_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting incremental datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00005: D:ORACLEORADATASADHANTOOLS01.DBFdestination for restore of datafile 00011: G:ORA_DBF_EXTENDEDSDH_SHTR01.DBFdestination for restore of datafile 00012: G:ORA_DBF_EXTENDEDSDH_FIN01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKDAILY_20121003_CUMUL_SADHAN-3038_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting incremental datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00004: D:ORACLEORADATASADHANINDX01.DBFdestination for restore of datafile 00010: F:ORACLESADHANSDH_EDSS01.DBFdestination for restore of datafile 00014: D:ORACLEORADATASADHANINDX02.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKDAILY_20121003_CUMUL_SADHAN-3037_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting incremental datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00007: D:ORACLEORADATASADHANSDH_TIMS01.DBFdestination for restore of datafile 00013: F:ORACLESADHANSDH_EDSS02.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKDAILY_20121003_CUMUL_SADHAN-3040_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting incremental datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00003: D:ORACLEORADATASADHANEXAMPLE01.DBFdestination for restore of datafile 00006: D:ORACLEORADATASADHANUSERS01.DBFdestination for restore of datafile 00008: D:ORACLEORADATASADHANSDH_TIMS02.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKDAILY_20121003_CUMUL_SADHAN-3039_1.DB tag=CUMULATIVE_L1_DATAFILE params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting incremental datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00001: D:ORACLEORADATASADHANSYSTEM01.DBFdestination for restore of datafile 00002: D:ORACLEORADATASADHANUNDOTBS01.DBFdestination for restore of datafile 00009: G:ORA_DBF_EXTENDEDSDH_HRMS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKDAILY_20121003_INCR_SADHAN-3041_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting incremental datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00005: D:ORACLEORADATASADHANTOOLS01.DBFdestination for restore of datafile 00011: G:ORA_DBF_EXTENDEDSDH_SHTR01.DBFdestination for restore of datafile 00012: G:ORA_DBF_EXTENDEDSDH_FIN01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKDAILY_20121003_INCR_SADHAN-3043_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting incremental datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00004: D:ORACLEORADATASADHANINDX01.DBFdestination for restore of datafile 00010: F:ORACLESADHANSDH_EDSS01.DBFdestination for restore of datafile 00014: D:ORACLEORADATASADHANINDX02.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKDAILY_20121003_INCR_SADHAN-3042_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting incremental datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00007: D:ORACLEORADATASADHANSDH_TIMS01.DBFdestination for restore of datafile 00013: F:ORACLESADHANSDH_EDSS02.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKDAILY_20121003_INCR_SADHAN-3045_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting incremental datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setdestination for restore of datafile 00003: D:ORACLEORADATASADHANEXAMPLE01.DBFdestination for restore of datafile 00006: D:ORACLEORADATASADHANUSERS01.DBFdestination for restore of datafile 00008: D:ORACLEORADATASADHANSDH_TIMS02.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKDAILY_20121003_INCR_SADHAN-3044_1.DB tag=DIFFERENTIAL_L1_DATAFILE params=NULLchannel ORA_DISK_1: restore completestarting media recoverymedia recovery failedRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 10/03/2012 20:00:52ORA-00283: recovery session canceled due to errorsRMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed startORA-00283: recovery session canceled due to errorsORA-00600: internal error code, arguments: , , , , , , , ORA-10567: Redo is inconsistent with data block (file# 9, block# 84845)ORA-10564: tablespace SDH_HRMS_DBFORA-01110: data file 9: 'G:ORA_DBF_EXTENDEDSDH_HRMS01.DBF'ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 26918RMAN> ALTER DATABASE OPEN;RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of alter db command at 10/03/2012 20:01:30ORA-01113: file 1 needs media recoveryORA-01110: data file 1: 'D:ORACLEORADATASADHANSYSTEM01.DBF'At this stage we do not have to be panic. We have already restored the database successfully. This is due to the reason. It is very old database created in noresetlogs mode. We cannot open it without performing incomplete recovery. Now Login with SQL*Plus in mount phaseSQL> recover database until cancel;Press enter as long as you reach to the missing logCANCELSQL> alter database open resetlogs;Note: You must take fresh full backup after opening the database with resetlogs option and must perform "reset database" to connect rman target to take fresh backup.C:CONNECT RMAN TARGET SYS/SYSMAN@SADHAN.WORD CATALOG CATALOG/CATALOG@RMANRMAN> RESET DATABASE; One of the junior DBA while working with Production environment on Thursday afternoon, due to media failure, one of the data file is corrupted. But all other data files are working fine. As the DBA restarted the database, one of data file is starts complaining. Then the DBA decided to recover that particular datafile. SQL> startup; ORACLE instance started. Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: ‘D:ORACLEORA92MUJAZORCUSERS02.DBF' He made the corrupted data file OFFLINE and opened the database, so users can use the database while recovering that particular data file (but in case of system01.dbf you need sufficient downtime to recover). SQL> alter database datafile ‘D:ORACLEORA92MUJAZORCUSERS02.DBF' OFFLINE; Database altered. SQL> alter database open; Database altered. SQL> Select file_id from dba_data_files where file_name = DBF'; FILE_ID ---------- 6 SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options Now connect the RMAN with catalog and restore and recover the datafile 6 C:>rman target sys/****@mujazhr.world catalog catalog/catalog@rman Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jun 25 14:30:09 2012 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: MUJAZORC (DBID=1165034825) connected to recovery catalog database RMAN>run     {     restore datafile 6;     recover datafile 6;     } Starting restore at 25-JUN-12 starting full resync of recovery catalog full resync complete allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=135 devtype=DISK creating datafile fno=6 name=D:ORACLEORA92MUJAZORCUSERS02.DBF restore not done; all files readonly, offline, or already restored Finished restore at 25-JUN-12 Starting recover at 25-JUN-12 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 25-JUN-12 RMAN> exit Recovery Manager complete. C:>sqlplus sys/oracle as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 25 14:34:10 2012 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 SQL> alter database datafile ‘DBF' ONLINE; Database altered. SQL> Select distinct status from dba_data_files; STATUS --------- AVAILABLE One of our Production database is related with Retail & Trading firm on Windows 2003 env. On Friday morning database goes down. As we restarted the database, DB complaining one of datafile is corrupted, may be this is due to the hard disk repair work happened on Thursday Evening where as other tablespaces datafiles are as it is having no issue. So we decided to recover only that particular tablespace.SQL> startup; Database mounted. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: ‘D:ORACLEORADATAISSCOHREDSS_DBF01.DBF ' We made this tablespace offline and open the database. So that end users can use the database while recovering the tablespace (as this is not the system tablespace). SQL> alter database datafile ‘D:ORACLEORADATAISSCOHREDSS_DBF01.DBF' OFFLINE; Database altered. SQL> alter database open; alter database open * ERROR at line 1: ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: ‘D:ORACLEORADATAISSCOHREDSS_DBF02.DBF' SQL> alter database datafile ‘D:ORACLEORADATAISSCOHREDSS_DBF02.DBF' OFFLINE; Database altered. SQL> alter database open; Database altered. SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options C:>rman target sys/****@ISSCOHR.world catalog catalog/catalog@rman Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 12 10:13:26 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: ISSCOHR (DBID=2613999945) connected to recovery catalog database RMAN> run{       restore tablespace EDSS_DBF;       recover tablespace EDSS_DBF;       } Starting restore at 12-JAN-13 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=128 devtype=DISK creating datafile fno=7 name=DBF channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00006 to DBF channel ORA_DISK_1: reading from backup piece H:ORABACK WEEKLY_20130112_FULL_ISSCOHR-2587_1.DB channel ORA_DISK_1: restored backup piece 1 piece handle= H:ORABACKWEEKLY_20130112_FULL_ISSCOHR-2588_1.DB channel ORA_DISK_1: restore complete, elapsed time: 00:13:11 Finished restore at 12-JAN-13 Starting recover at 12-JAN-13 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 36 is already on disk as file E:ORACLEARCHIVEARC02244.001 archive log thread 1 sequence 37 is already on disk as file E:ORACLEARCHIVEARC02245.001 archive log thread 1 sequence 38 is already on disk as file E:ORACLEARCHIVEARC02246.001 archive log thread 1 sequence 39 is already on disk as file E:ORACLEARCHIVEARC02247.001 media recovery complete, elapsed time: 00:01:23 Finished recover at 12-JUN-13 SQL> connect sys/password as sysdba Connected. SQL> alter database datafile ‘DBF' ONLINE; Database altered. SQL> alter database datafile ‘DBF' ONLINE; If SYSTEM tablespace gets corrupted and others are intact. Then you need sufficient downtime to recover this tablespace as you can not open the database without recovering the SYSTEM tablespace. SQL> startup; Database mounted. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1:'C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF' Take the SYSTEM tablespace offline and recover that tablespace only. The recovery log taken from any other Test database. SQL> alter database datafile 'C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF’ OFFLINE; Database altered. SQL> ALTER DATABASE OPEN; ALTER DATABASE OPEN * ERROR at line 1: ORA-01147: SYSTEM tablespace file 1 is offline ORA-01110: data file 1: 'C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF' SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options C:>rman catalog=rman/rman@catdb target=sys/password@orcl Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 22 10:21:04 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: ORCL (DBID=1215124933, not open) connected to recovery catalog database RMAN> run      {      restore tablespace system;      recover tablespace system;      } Starting restore at 22-MAY-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 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 C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF channel ORA_DISK_1: reading from backup piece C:RMANBACKUPBACKUPORCL_DB_0JKFLE9Q_1_1_%S_%P channel ORA_DISK_1: restored backup piece 1 piece handle=C:RMANBACKUPBACKUPORCL_DB_0JKFLE9Q_1_1_%S_%P tag=TAG20090522T094738 channel ORA_DISK_1: restore complete, elapsed time: 00:00:35 Finished restore at 22-MAY-09 Starting recover at 22-MAY-09 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:04 Finished recover at 22-MAY-09 RMAN> exit Recovery Manager complete. C:>sqlplus sys/password as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 22 10:22:20 2009 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 SQL> alter database open; alter database open * ERROR at line 1: ORA-01147: SYSTEM tablespace file 1 is offline ORA-01110: data file 1: 'C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF' SQL> alter database datafile 'C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF' online; Database altered. SQL> ALTER DATABASE OPEN; Database altered. SQL> select distinct status from dba_tablespaces; STATUS --------- ONLINE SQL> select distinct status from dba_data_files; STATUS --------- AVAILABLE One afternoon, you restarted your database and realized that all the redo log files are gets corrupted but fortunately you did not lost the controlfile. Thus you are able to mount the database. To recover all those redo log files, you have decided to perform incomplete recovery. SQL> startup; ORACLE instance started. Database mounted. ORA-00313: open failed for members of log group 1 of thread 1 ORA-00312: online log 1 thread 1:'C:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG' SQL>exit As you have taken the backup in the morning mount the database and run the RMAN backup first, just to make sure all the archived redo log files are backed up before start the actual recovery process. Once RMAN backup is completed, you have to perform incomplete recovery and recovered until the last archived redo log. The recovery logs are taken from any other source. C:>rman catalog=rman/rman@catdb target=sys/password@orcl Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 23 20:36:24 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: ORCL (DBID=1215124933, not open) connected to recovery catalog database RMAN> backup archivelog all; Starting backup at 23-MAY-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=152 devtype=DISK channel ORA_DISK_1: starting archive log backupset channel ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=2 recid=31 stamp=687541158 input archive log thread=1 sequence=3 recid=32 stamp=687542042 input archive log thread=1 sequence=4 recid=33 stamp=687542062 comment=NONE Starting Control File and SPFILE Autobackup at 23-MAY-09 piece handle=C:ORACLEPRODUCT10.2.0DB_1DATABASEC-1215124933-20090523-0A comment=NONE Finished Control File and SPFILE Autobackup at 23-MAY-09 RMAN> list backup of archivelog from time='sysdate-1'; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 10692 37.00K DISK 00:00:01 23-MAY-09 BP Key: 10697 Status: AVAILABLE Compressed: NO Tag: TAG20090523T224042 Piece Name: C:RMANBACKUPBACKUPORCL_DB_07KFPFVA_7_1 List of Archived Logs in backup set 10692 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 6 551206 23-MAY-09 551263 23-MAY-09 RMAN> restore database until sequence=6 thread=1 force; Starting restore at 23-MAY-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 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 C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF restoring datafile 00002 to C:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBF restoring datafile 00003 to C:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF restoring datafile 00004 to C:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF channel ORA_DISK_1: reading from backup piece C:ORACLEPRODUCT10.2.0DB_1DATABASE2KFPFLV_1_1 channel ORA_DISK_1: restored backup piece 1 piece handle=C:ORACLEPRODUCT10.2.0DB_1DATABASE2KFPFLV_1_1 tag=TAG20090523T223542 channel ORA_DISK_1: restore complete, elapsed time: 00:00:45 Finished restore at 23-MAY-09 RMAN> recover database until sequence=6 thread=1 ; Starting recover at 23-MAY-09 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 4 is already on disk as file C:ORACLEPRODUCT10.2.0DB_1DATABASEARCHIVELOG_4_1_687649381.ARC archive log thread 1 sequence 5 is already on disk as file C:ORACLEPRODUCT10.2.0DB_1DATABASEARCHIVELOG_5_1_687649381.ARC archive log filename=C:ORACLEPRODUCT10.2.0DB_1DATABASEARCHIVELOG_4_1_687649381.ARC thread=1 sequence=4 archive log filename=C:ORACLEPRODUCT10.2.0DB_1DATABASEARCHIVELOG_5_1_687649381.ARC thread=1 sequence=5 media recovery complete, elapsed time: 00:00:03 Finished recover at 23-MAY-09 RMAN> alter database open resetlogs; database opened new incarnation of database registered in recovery catalog The database is recovered successfully. But this is incomplete recovery. Prior to oracle10g, oracle strongly recommended to takes the full database backup whenever there is incomplete recovery. But in oracle10g, it is optional. But still it is good to take the full database backup for safer side. Unfortunately, if you lost all the data files, control files, redo log files. But luckily the current archived redo log files were intact.  In that case you are not able to mount the database since lost your controlfile too. SQL> startup; ORACLE instance started. ORA-00205: error in identifying control file, check alert log for more info You need to recover first the control file from RMAN backup. Once control file is recovered, you are able to mount the database. After the database is mounted run the RMAN backup to make sure, all the current archive log files are backed up and recover the data base until last the sequence of archived log file. C:>set oracle_sid=orcl C:>rman catalog=rman/rman@catdb target=sys/password Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:26:51 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: orcl (not mounted) connected to recovery catalog database RMAN> restore controlfile; Starting restore at 24-MAY-09 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: restoring control file channel ORA_DISK_1: reading from backup piece C:ORACLEPRODUCT10.2.0DB_1DATABASEC-1215492928-20090524-00 channel ORA_DISK_1: restored backup piece 1 piece handle=C:ORACLEPRODUCT10.2.0DB_1DATABASEC-1215492928-20090524-00 tag=TAG20090524T152409 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 output filename=C:ORACLEPRODUCT10.2.0ORADATAORCLCONTROL01.CTL output filename=C:ORACLEPRODUCT10.2.0ORADATAORCLCONTROL02.CTL output filename=C:ORACLEPRODUCT10.2.0ORADATAORCLCONTROL03.CTL Finished restore at 24-MAY-09 SQL> alter database mount; Database altered. SQL> select archivelog_change#-1 from v$database; ARCHIVELOG_CHANGE#-1 -------------------- 547010 SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options C:>rman catalog=rman/rman@catdb target=sys/password@orcl Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:29:33 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: ORCL (DBID=1215492928, not open) connected to recovery catalog database RMAN> run{        set until scn 547010;        restore database;        recover database;        alter database open resetlogs;        } executing command: SET until clause Starting restore at 24-MAY-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 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 C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF restoring datafile 00002 to C:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBF restoring datafile 00003 to C:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF restoring datafile 00004 to C:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF channel ORA_DISK_1: reading from backup piece C:RMANBACKUPBACKUPORCL_DB_02KFRAN7_1_1_%S_%P channel ORA_DISK_1: restored backup piece 1 piece handle=C:RMANBACKUPBACKUPORCL_DB_02KFRAN7_1_1_%S_%P tag=TAG20090524T1523 channel ORA_DISK_1: restore complete, elapsed time: 00:00:45 Finished restore at 24-MAY-09 Starting recover at 24-MAY-09 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 3 is already on disk as file C:ORACLEPRODUCT10.2.0DB_1DATABASEARCHIVELOG_3_1_687712197.ARC archive log filename=C:ORACLEPRODUCT10.2.0DB_1DATABASEARCHIVELOG_3_1_687712197.ARC thread=1 sequence=3 media recovery complete, elapsed time: 00:00:04 Finished recover at 24-MAY-09 database opened new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete One of the Application programmer truncated the critical table in the evening around 3.46 PM and they need to recover the truncated table. They have to decide to go to the time based incomplete recovery of the exact 03.45 PM. Find the Exact time for recovery and set the date and time format along with time based recovery scripts. SQL> Select count(*) from employee; COUNT(*) ---------- 14 SQL> select to_char(sysdate,'DD-MM-YYYY:HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'DD ------------------- 24-05-2009:15:45:42 SQL> truncate table employee; Table truncated. SQL> select count(*) from employee; COUNT(*) ---------- 0 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Database mounted. SQL> C:>rman catalog=rman/rman@catdb target=sys/password@orcl Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 24 15:58:56 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: ORCL (DBID=1215492928, not open) connected to recovery catalog database RMAN> run       {       set until time "to_date('24-05-2009:15:45:42','DD-MM-YYYY HH24:MI:SS')";       restore database;       recover database;       } executing command: SET until clause Starting restore at 24-MAY-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=156 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 C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF restoring datafile 00002 to C:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBF restoring datafile 00003 to C:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF restoring datafile 00004 to C:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF channel ORA_DISK_1: reading from backup piece C:RMANBACKUPBACKUPORCL_DB_09KFRBBR_1_1_%S_%P channel ORA_DISK_1: restored backup piece 1 piece handle=C:RMANBACKUPBACKUPORCL_DB_09KFRBBR_1_1_%S_%P tag=TAG20090524T1534 channel ORA_DISK_1: restore complete, elapsed time: 00:00:45 Finished restore at 24-MAY-09 Starting recover at 24-MAY-09 using channel ORA_DISK_1 starting media recovery archive log thread 1 sequence 2 is already on disk as file C:ORACLEPRODUCT10.2.0DB_1DATABASEARCHIVELOG_2_1_687713476.ARC archive log thread 1 sequence 3 is already on disk as file C:ORACLEPRODUCT10.2.0DB_1DATABASEARCHIVELOG_3_1_687713476.ARC archive log filename=C:ORACLEPRODUCT10.2.0DB_1DATABASEARCHIVELOG_2_1_687713476.ARC thread=1 sequence=2 media recovery complete, elapsed time: 00:00:05 Finished recover at 24-MAY-09 RMAN> exit Recovery Manager complete. C:>sqlplus sys/password as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 24 16:01:31 2009 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 SQL> alter database open resetlogs; Database altered. SQL> connect scott/tiger@orcl Connected. SQL> select count(*) from employee; COUNT(*) ---------- 14 Note: If you use the ‘alter database open resetlogs’ command from SQL*PLUS then you must need to use ‘reset database’ command to connect RMAN catalog for fresh backup.

[youngprogrammersclub]

DBA Interview Questions with Answer Part14

Why drop table is not going into Recycle bin? If you are using SYS user to drop any table then user’s object will not go to the recyclebin as there is no recyclebin for SYSTEM tablespace, even we have already SET recycle bin parameter TRUE.Select * from v$parameter where name = 'recyclebin';Show parameter recyclebin; How to recover password in oracle 10g?You can query with the table user_history$. The password history is store in this table.How to detect inactive session to kill automatically?You can use the SQLNET.EXPIRE_TIME for the dead connections (for abnormal disconnections) by specifying a time interval in minute to send a problem message that verify client/server connections are active. Setting the value greater than 0 to this parameter ensures that connection is not left open indefinitely, due to abnormal client termination. If probe finds a terminated connection, or connection that is no longer in use, it returns an error, causing the server process to exit. SQLNET.EXPIRE_TIME=10Why we need CASCADE option with DROP USER command whenever dropping a user and why "DROP USER" commands fails when we don't use it?If a user having any object then ‘YES’ in that case you are not able to drop that user without using CASCADE option. The DROP USER with CASCADE option command drops user along with its all associated objects. Remember it is a DDL command after the execution of this command rollback cannot be performed.Can you suggest the best steps to refresh a Database?Refreshing the database is nothing but applying the change on one database (PROD) to another (Test). You can use import/export and RMAN method for this purpose.Import/Export Method:If you database is small and if you need to refresh particular schema only then it is always better to use this method.Export the dump file from source DBDrop and recreate Test environment User.Import the dump to destination DB.RMAN Method: Now days RMAN is most likely to be used for backup and recovery. It is relatively easier and better method for full database refresh to be refreshed. It is taking less time as compare to import/export method. Here also you can use particular SCN based refreshing.#!/usr/bin/kshexport ORAENV_ASK='NO'export ORACLE_SID=PRD/usr/local/bin/oraenvexport NLS_LANG=American_america.us7ascii;export NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS";$ORACLE_HOME/bin/rman target / nocatalog log=/tmp/duplicate_tape_TEST.log connect auxiliary sys/PASSWORD@TEST;run{allocate auxiliary channel aux1 device type disk;set until SCN 42612597059;duplicate target database to "TEST" pfile='/u01/app/xxxx/product/10.2.0/db_1/dbs/initTEST.ora' NOFILENAMECHECK;}EOFHow will we know the IP address of our system in Linux environment?Either use ipconfig command or ip addr showIt will give you all IP address and if you have oracle 9i you can query from SQL prompt.SELECT UTL_INADDR.GET_HOST_ADDRESS "Host Address", UTL_INADDR.GET_HOST_NAME "Host Name" FROM DUAL;Can we create Bigfile Tablespace for all databases?Infact your question do we create bigfile tablespace for every database is not clear for me. If you are asking can we create bigfile for every database?Yes you can but it is not ideal for every datafile if your work is suitable for small file then why you create bigfile but if your mean is impact of bigfile that depends on your requirements and storage.A bigfile tablespace is having single very big datafile which can store 4GB to 128 TB.Creating single large datafile reducing the requirement of SGA and also it will allow you modification at tablespace level. In fact it is ideal for ASM, logical device supporting stripping. Avoid using bigfile tablespace where there is limited space availability. For more details impact, advantage, disadvantage of bigfile on my blog.Can you gice more explanation on logfile states?“CURRENT” state means that redo records are currently being written to that group. It will be until a log switch occurs. At a time there can be only one redo group current.If a redo group containing redo’s of a dirty buffer that redo group is said to be ‘ACTIVE’ state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten.One more state ‘UNUSED’ initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.What is difference between oracle SID and Oracle service name?Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID. How to find session for Remote users?-- To return session id on remote session:‎SELECT distinct sid FROM v$mystat;-- Return session id of you in remote Environment:‎Select sid from v$mystat@remot_db where rownum=1;We have a complete cold Backup taken on Sunday. The database crashed on Wednesday. None of the database files are available. The only files we have are the taped backup archive files till Wednesday. Is there a possibility of recovering the database until the recent archive which we have in the tape using the cold backup.Yes, if you have all the archive logs since the cold backup then you can recover to your last logSteps:1) Restore all backup datafiles, and controlfile. Also restore the password file and init.ora if you lost those too. Don't restore your redo logs if you backed them up. 2) Make sure that ORACLE_SID is set to the database you want to recover 3) startup mount;4) Recover database using backup controlfile; At this point Oracle should start applying all your archive logs, assuming that they're in log_archive_dest5) alter database open resetlogs;How to check RMAN version in oracle?If you want to check RMAN catalog version then use the below query from SQL*plusSQL> Select * from rcver;If you want to check simply database version.SQL> Select * from v$version;What is the minimum size of Temporary Tablespace?1041 KBDifference b/w image copies and backup sets?An image copy is identical, byte by byte, to the original datafile, control file, or archived redo log file. RMAN can write blocks from many files into the same backup set but can’t do so in the case of an image copy.An RMAN image copy and a copy you make with an operating system copy command such as dd (which makes image copies) are identical. Since RMAN image copies are identical to copies made with operating system copy commands, you may use user-made image copies for an RMAN restore and recovery operation after first making the copies “known” to RMAN by using the catalog command.You can make image copies only on disk but not on a tape device. "backup as copy database;" Therefore, you can use the backup as copy option only for disk backups, and the backup as backupset option is the only option you have for making tape backups.How can we see the C: drive free space capacity from SQL?create an external table to read data from a file that will be as below create BAT file free.bat as @setlocal enableextensions enable delayedexpansion @echo off for /f "tokens=3" %%a in ('dir c:') do ( set bytesfree=%%a ) set bytesfree=%bytesfree:,=% echo %bytesfree% endlocal && set bytesfree=%bytesfree% You can create a schedular to run the above free.bat, free_space.txt inside the oracle directory.Differentiate between Tuning Advisor and Access Advisor?The tuning Advisor:–        It suggests indexes that might be very useful.–        It suggests query rewrites.–        It suggests SQL profileThe Access Advisor:–        It suggest indexes that may be useful–        Suggestion about materialized view.–        Suggestion about table partitions also in latest version of oracle.How to give Access of particular table for particular user?GRANT SELECT (EMPLOYEE_NUMBER), UPDATE (AMOUNT) ON HRMS.PAY_PAYMENT_MASTER TO SHAHID;The Below command checks the SELECT privilege on the table PAY_PAYMENT_MASTER on the HRMS schema (if connected user is different than the schema)SELECT PRIVILEGEFROM ALL_TAB_PRIVS_RECDWHERE PRIVILEGE = 'SELECT'AND TABLE_NAME = 'PAY_PAYMENT_MASTER'AND OWNER = 'HRMS'UNION ALLSELECT PRIVILEGEFROM SESSION_PRIVSWHERE PRIVILEGE = 'SELECT ANY TABLE';What are the problem and complexities if we use SQL Tuning Advisor and Access Advisor together?I think both the tools are useful for resolving SQL tuning issues. SQL Tuning Advisor seems to be doing logical optimization mainly by checking your SQL structure and statistics and the SQL Access Advisor does suggest good data access paths, that is mainly work which can be done better on disk.Both SQL Tuning Advisor and SQL Access Advisor tools are quite powerful as they can source the SQL they will tune automatically from multiple different sources, including SQL cache, AWR, SQL tuning Sets and user defined workloads. Related with the argument complexity and problem of using these tools or how you can use these tools together better to check oracle documentation.

[youngprogrammersclub]

How to Kill Idle Session

Consider a situation when DBA having some maintenance task. He has already passed the message to all the database user to stop using the database and he finds some of the users is still using the database and he is not able to communicate with that particular user (may be he is not available of chair) then DBA choose to kill that session. For this purpose either you can use Toad or run the below batch file. declare    ora_user varchar2(30) default 'HRMS'; --Oracle Username to kill all sessions for begin    execute immediate 'alter user '||ora_user||' account lock';    for crs in (select sid,serial# from v$session where username = ora_user) loop       execute immediate 'alter system kill session '''||crs.sid||','||crs.serial#||'''';    end loop; end; / How to Kill idle Session of particular Schema Contents: kill_idle_sessions.bat sqlplus orafin/**** @d:kill_idle_sessions.sql Contents: kill_idle_sessions.sql connect / as sysdba exec kill_idle_sessions exit

[youngprogrammersclub]

Synchronizes the Test database with RMAN Cold Backup

If the recovery catalog is maintained in the controlfile and that, prior to the restore, the controlfile has not been damaged, so the recovery catalog information it contains is fully intact. Thus RMAN can determine from its recovery catalog whether the backup was made using a DISK or SBT_TAPE channel and will automatically allocate a channel of the correct type for reading from the backup file. If the controlfile had been damaged, it would be necessary to use an additional step to restore the controlfile from the backup, and a DISK channel should be explicitly allocated for that step of the restore in an RMAN run block, in order to assure RMAN uses the correct device type to read the controlfile backup. This example shows if you want to restore Test environment with the backup of production database. Configure RMAN; assume Fast Recovery Area (FRA) is used, so no ‘format’ parameters needed.RMAN>Configure Controlfile autobackup ON;RMAN>Configure default device type to disk;Now Perform the RMAN Cold backup of Production database, while the database is in mount state, no matter the database is in Archive log or Noarchivelog . You can also perform Shutdown and startup from the RMAN prompt or use the below script for the RMAN_COLD backup. Below is the two .cmd file, one to connect the RMAN with target database and other to perform backup.// Create objects to mark the restore point before backupCREATE(using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:ORACLEORADATASADHANSYSTEM01.DBFrestoring datafile 00002 to D:ORACLEORADATASADHANUNDOTBS01.DBFrestoring datafile 00009 to G:ORA_DBF_EXTENDEDSDH_HRMS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKSADHAN_COLD_20130211_SADHAN-5006_1.DB tag=SADHAN_COLD_BACKUP params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00005 to D:ORACLEORADATASADHANTOOLS01.DBFrestoring datafile 00011 to G:ORA_DBF_EXTENDEDSDH_SHTR01.DBFrestoring datafile 00012 to G:ORA_DBF_EXTENDEDSDH_FIN01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKSADHAN_COLD_20130211_SADHAN-5008_1.DB tag=SADHAN_COLD_BACKUP params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00003 to D:ORACLEORADATASADHANEXAMPLE01.DBFrestoring datafile 00006 to D:ORACLEORADATASADHANUSERS01.DBFrestoring datafile 00008 to D:ORACLEORADATASADHANSDH_TIMS02.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKSADHAN_COLD_20130211_SADHAN-5009_1.DB tag=SADHAN_COLD_BACKUP params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00004 to D:ORACLEORADATASADHANINDX01.DBFrestoring datafile 00010 to F:ORACLESADHANSDH_EDSS01.DBFrestoring datafile 00014 to D:ORACLEORADATASADHANINDX02.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKSADHAN_COLD_20130211_SADHAN-5007_1.DB tag=SADHAN_COLD_BACKUP params=NULLchannel ORA_DISK_1: restore completechannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00007 to D:ORACLEORADATASADHANSDH_TIMS01.DBFrestoring datafile 00013 to F:ORACLESADHANSDH_EDSS02.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=H:ORABACKSADHAN_COLD_20130211_SADHAN-5010_1.DB tag=SADHAN_COLD_BACKUP params=NULLchannel ORA_DISK_1: restore completeFinished restore at 11-FEB-13 RMAN> alter database open resetlogs;database opened The “resetlogs” is needed when opening the database at this point. But once that command is issued, the same instance of the database cannot be restored again from old backups. Thus, it is important to perform another backup of the database immediately after this line has successfully been executed.SQL> shutdown immediate;SQL> startup mount;C:>rman target /RMAN> backup database;// Create Tempfile and Validate the success of RestoreCheck the restore point to validate the success of restore and check the Tempfile (must in case of new server restore). If the tempfile is not available create the new tempfile.SELECT FILE_NAME FROM DBA_DATA_FILES, DBA_TABLESPACES WHEREDBA_DATA_FILES.TABLESPACE_NAME = DBA_TABLESPACES.TABLESPACE_NAME AND DBA_TABLESPACES.CONTENTS = 'TEMPORARY'; SELECT * FROM DBA_TEMP_FILES; ALTER TABLESPACE TEMPADD TEMPFILE 'D:ORACLEORADATAsadhanTEMP02.DBF' SIZE 15000MAUTOEXTEND ON NEXT 5M MAXSIZE 30000M; SELECT INSTANCE_NAME FROM V$INSTANCE;SELECT DATABASE_NAME FROM V$DATABASE;SELECT * FROM GLOBAL_NAME;SELECT * FROM DBA_DATA_FILES;SELECT * FROM V$LOGFILE;SELECT * FROM V$LOG;SELECT LOG_MODE FROM V$DATABASE;SELECT * FROM HRMS.PAY_PAYMENT_MASTER ORDER BY PAYSLIP_DATE;SELECT * FROM V$CONTROLFILE;SELECT * FROM DATABASE_PROPERTIES;

[youngprogrammersclub]

Tuning ORACLE SHARED SERVER Parameters

Using shared server enables to reduce number of processes and the amount of memory consumed on the server machine. It beneficial for OLTP users performing intermittent transactions. Using shared servers rather than dedicated servers is also generally better for systems that have a high connection rate to the database. With shared servers, when a connect request is received, a dispatcher is already available to handle concurrent connection requests. With dedicated servers, on the other hand, a connection-specific dedicated server is sequentially initialized for each connection request. Performance of certain database features can improve when a shared server architecture is used, and performance of certain database features can degrade slightly when a shared server architecture is used. For example, a session can be prevented from migrating to another shared server while parallel execution is active.A session can remain nonmigratable even after a request from the client has been processed, because not all the user information has been stored in the UGA. If a server were to process the request from the client, then the part of the user state that was not stored in the UGA would be inaccessible. To avoid this, individual shared servers often need to remain bound to a user session. To check how many shared servers are currently running by issuing the following query: SELECT COUNT(*) "Shared Server Processes" FROM V$SHARED_SERVER WHERE STATUS != 'QUIT'; Proper configuration of shared servers can result in significant performance improvement. * Oracle Shared Server initialization parameters: Alter system set SHARED_SERVERS = nn; Alter system set MAX_SHARED_SERVERS = nn; Alter system set DISPATCHERS = '(PROTOCOL=TCP) (DISPATCHERS=nn)'; Alter system set MAX_DISPATCHERS = nn; Alter system set CIRCUITS = nn; Alter system set SHARED_SERVER_SESSIONS = nn; Note: The default value of MAX_SHARED_SERVERS is dependent on the value of SHARED_SERVER. If SHARED_SERVERS is less than or equal to 10, then MAX_SHARED_SERVERS defaults to 20. If SHARED_SERVERS is greater than 10, then MAX_SHARED_SERVERS defaults to two times the value of SHARED_SERVERS. * Check the usage for dispatcher processes.  Some high busy_rate possible indicate contention for dispatcher. Consider increasing the number of dispatcher. column protocol format a60; Select network as protocol, status, sum(owned) as clients,  trunc(sum(busy)/(sum(busy)+sum(idle)),3) as busy_rate from v$dispatcher group by network, status; * Check the values of dispatchers to analyze contention.    If CUR values are close or equal to the MAX values, consider increasing the number of dispatchers. Select NAME, PADDR, CUR_LOOP_RATE, CUR_EVENT_RATE, CUR_EVENTS_PER_LOOP, CUR_MSG_RATE, CUR_SVR_BUF_RATE, CUR_SVR_BYTE_RATE, CUR_SVR_BYTE_PER_BUF, CUR_CLT_BUF_RATE, CUR_CLT_BYTE_RATE, CUR_CLT_BYTE_PER_BUF, CUR_BUF_RATE, CUR_BYTE_RATE, CUR_BYTE_PER_BUF,CUR_IN_CONNECT_RATE, CUR_OUT_CONNECT_RATE, CUR_RECONNECT_RATE from v$dispatcher_rate; Select NAME, PADDR, MAX_LOOP_RATE, MAX_EVENT_RATE, MAX_EVENTS_PER_LOOP, MAX_MSG_RATE, MAX_SVR_BUF_RATE, MAX_SVR_BYTE_RATE, MAX_SVR_BYTE_PER_BUF, MAX_CLT_BUF_RATE, MAX_CLT_BYTE_RATE, MAX_CLT_BYTE_PER_BUF, MAX_BUF_RATE, MAX_BYTE_RATE, MAX_BYTE_PER_BUF, MAX_IN_CONNECT_RATE, MAX_OUT_CONNECT_RATE, MAX_RECONNECT_RATE from v$dispatcher_rate; * Check the average wait time (expressed in hundredths of a seconds).  With a increasing value consider increase the number of dispatchers. Select decode(sum(totalq),0,'No Responses', trunc( sum(wait)/sum(totalq),3) ) as average_wait_time from v$queue q, v$dispatcher d where q.type = 'DISPATCHER' and q.paddr = d.paddr; * Check the current status of shared servers.  With high pct_busy consider increase the number of shared servers. Select name, requests, busy*100/(busy+idle) as pct_busy, status from v$shared_server where status != 'QUIT'; * Check the average wait time per requests in hundredths of seconds.   With high wait time consider increase the number of shared servers. Select  decode(totalq, 0, 'No Requests', trunc( wait/totalq,3) ) as average_wait_time_hund_secs from v$queue q where q.type = 'COMMON';

[youngprogrammersclub]

Scripts: Disk I/O, Events, Waits (Contention issues)

The below scripts is useful to check Disk I/O issues, Events and Waits. It is useful to find disk contention problem as well as contention issues for Latches. DATAFILES I/O: ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- File Name - Datafile name Physical Reads - Number of physical reads Reads % - Percentage of physical reads Physical Writes - Number of physical writes Writes % - Percentage of physical writes Total Block I/O's - Number of I/O blocks Use this report to identify any "hot spots" or I/O contention Select NAME, PHYRDS "Physical Reads", round((PHYRDS / PD.PHYS_READS)*100,2) "Read %", PHYWRTS "Physical Writes", round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %", fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's" from ( select sum(PHYRDS) PHYS_READS, sum(PHYWRTS) PHYS_WRTS from   v$filestat ) pd, v$datafile df, v$filestat fs where df.FILE# = fs.FILE# order by fs.PHYBLKRD+fs.PHYBLKWRT desc; * A brif difference between phyrds and phyblkrd implies table scan are going on. Check the aplication SQL statements. I/O shoud be spread evenly across drives. column tablespace format a20    column file_name format a50    select d.tablespace_name as Tablespace, d.file_name, f.phyrds,    f.phyblkrd, f.readtim, f.phywrts, f.phyblkwrt, f.writetim     from v$filestat f, dba_data_files d     where f.file# = d.file_id     order by tablespace_name, file_name; * Check for full table scan operations. Investigate the need for full table scans. Consider specify the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter. Select name, value from v$sysstat where name like '%table scan%'; * Monitor long-running full table scans. Select SID, ELAPSED_SECONDS as el_sec, as TIME_REMAINING, substr( MESSAGE,1,85 ) as message, substr( USERNAME,1,10 ) as username, START_TIME, SOFAR, TOTALWORK, substr( OPNAME||' '||TARGET||' '||TARGET_DESC,1,40) as oper, substr(UNITS,1,10) as units from v$session_longops vl where totalwork > sofar and exists( select 1 from v$session s where sid = vl.sid and (select spid from v$process where addr = s.paddr) is not null ) order by 3 desc, 1; ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PROCESSES MEMORY USAGE: -- Check for the entry memory processes usage. select count(1) as qt_proc, trunc(avg(pga_alloc_mem)/1024/1024,3) as avg_pga_proc_mb, select s.username || ' / ' || osuser || ' / ' || p.PROGRAM || 

[youngprogrammersclub]

Tuning Oracle 9i/10g SGA Parameters

Oracle 9i database administrator can fully allocate the oracle server's RAM memory upto 80 percent of the total RAM on the oracle server. Oracle recommends that 20 percent of the RAM memory on a database server be reserved for operating system tasks. Dynamic nature of this parameters help to grow and shrink the existing SGA parameters. Here in this article my concentration is to focus the performance tuning on existing SGA parameters. Check my other post for more details about SGA parameters: SGA Sizing in Oracle 9i Automatic SGA Memory ManagementChecking SGA Parameters: Select pool, m_bytes    from (select pool, to_char( trunc(sum(bytes)/1024/1024,2), '99999.99' ) as M_bytes     from v$sgastat     where pool is not null group by pool     union  select name as pool, to_char( trunc(bytes/1024/1024,3), '99999.99' ) as M_bytes     from  v$sgastat     where pool is null order by 2 desc     )     UNION ALL     select 'TOTAL' as pool, to_char( trunc(sum(bytes)/1024/1024,3), '99999.99' ) from v$sgastat; Tuning BUFFER CACHE: It includes tuning the Initialization parameters DB_CACHE_SIZE, DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE Select name, value from v$parameter where name in ('db_cache_size', 'db_keep_cache_size', 'db_recycle_cache_size') order by name; This is the the most crucial parameter in Oracle database for performance tuning. If the DB_CACHE_SIZE is set too low, Oracle won’t have enough memory to operate efficiently and the system may run poorly, and If DB_CACHE_SIZE is too high, your system may begin to swap and may come to a halt. To have enough memory allocation, to store data in memory depends on the value used for DB_CACHE_SIZE. From the below script, you can check the current data cache hit ratio Select name, value from v$parameter where name = 'db_cache_advice'; Initialization parameter DB_CACHE_ADVICE must be either ON, OFF, READY. Select * from v$db_cache_advice; A data cache hit ratio of 95 percent or greater should be achievable for a well-tuned transactional application with the appropriate amount of memory. Because there is such a performance difference between some disk devices and memory access, improving the data cache hit ratio from 90 to 95 percent can nearly double system performance when reading disk devices that are extremely slow. Improving the cache hit ratio from 90 to 98 percent could yield nearly a 500 percent improvement where disks are extremely slow and under the right architectural setup. and p.name= 'open_cursors' group by p.value; --Check parameter CURSOR_SPACE_FOR_TIME. * Do not set to TRUE if the RELOADS of v$librarycache (select above) is greater than zero. Select value from v$parameter where name = 'cursor_space_for_time'; --Check parameter CURSOR_SHARING.   Possible values: EXACT, SIMILAR, FORCE. Select value from v$parameter where name = 'cursor_sharing'; DICTIONARY CACHE: * The Dictionary Cache percent misses should be less than 0.15 (< 15%). If it is greater than 0.15 consider increase SHARED_POOL_SIZE init parameter. Take this value some time after startup, not first time after startup. Select sum(getmisses) as getmisses, sum(gets) as gets, trunc(sum(getmisses)/sum(gets),4) as miss_ratio from v$rowcache; UGA inside Shared Pool: When usnig oracle Shared server, Consider increase this memory size value in the SHARED_POOL_SIZE init parameter. Select v.name, to_number(sum(value)) as Total_memory_in_Bytes from v$sesstat st, v$statname v where v.name in ('session uga memory', 'session uga memory max') and st.statistic# = v.statistic# group by v.name; Tuning REDO LOG BUFFER:There should be no Log Buffer Space waits. Consider making the log buffer bigger if it is small. Consider moving the log files to faster disks such as striped disks.Select event, total_waits, total_timeouts, time_waited, average_wait from  v$sysstat where name in ('redo log space requests');* The pct_buff_alloc_retries should be zero or less than 0.01 (< 1%). If it is greater consider making the log buffer bigger if it is small.  If it is greater consider moving the log files to faster disks such as striped disks. Select v1.value as redo_buff_alloc_retries, v2.value as redo_entries,

[youngprogrammersclub]

Discussion: Import/Export Utility?

Export (exp), Import (imp) are Oracle utilities which allow you to write data in an ORACLE-binary format from the database into operating system files and to read data back from those operating system files. A simple automated script to export full databaseSET ORACLE_SID=ORCL3Column instnc new_value v_inst noprintcolumn instdate new_value v_instdate noprintSELECT TO_CHAR(sysdate,'DDMMYYHH24') instdate FROM dual;host exp system/oracle@orcl3 full=y consistent=y file=D:BACKUPdump&&v_instdate..dmp log=D:BACKUPdump&&v_instdate..logexitWhich are the Import/Export modes?Full export/export: The EXP_FULL_DATABASE & IMP_FULL_DATABASE, respectively, are needed to perform a full export. Use the full export parameter for a full export. Tablespace: Use the tablespaces export parameter for a tablespace export.User: This mode can be used to export and import all objects that belong to a user. Use the owner export parameter and the FROMUSER import parameter for a user (owner) export-import. Table: Specific tables (or partitions) can be Exported/Imported with table export mode. Use the tables export parameter for a table Export/ Import mode. For more details example follow the other post: Using Import/Export Is it possible to exp/ imp to multiple files?Yes, is possible. Here is an example:  exp SCOTT/TIGER FILE=C:backupFile1.dmp,C:backupFile2.dmp LOG=C:backupscott.logExport and Import Schema in the same and different database EXP SYSTEM/SYSMAN@mujazorc.world OWNER=KAFAFIN FILE=H:dumpkafafin_dump.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y LOG= H:dumpkafafin.DMP.LOG The above two commands will export the 'kafafin' schema from mujazorc database and import (restore) into the 'awaed' schema located on the same database. But in the case if you want to import into new schema of new tablespace you need to quota 0 on old_tablespace and quota unlimited on new_tablespace before importing. For Example: if you have schema KAFAFIN with tablespace KAFA_FIN_DBF and need to import into new schema AWAED of new tablespace AWAED then before import you must set quota 0 on old_tablespace and quota unlimited on new tablespace. alter user AWAED quota 0 on KAFA_FIN_DBF quota unlimited on AWAED; revoke unlimited tablespace from AWAED; IMP SYSTEM/sysman@mujazorc.world FILE=D:backupkafafin_dump.DMP FROMUSER=KAFAFIN TOUSER=AWAED LOG=D:backupkafafin.DMP.LOG after import you will need to set it again ALTER USER AWAED DEFAULT TABLESPACE AWAED QUOTA UNLIMITED ON AWAED; While in case if you want to import different schema on different database there is no need to set the quota 0 for old_tablespace create tablespace AWAED datafile 'D:ORACLEORA92MUJAZORCAWAED.DBF' size 20480m autoextend on; create user AWAED IDENTIFIED BY AWAED TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE AWAED QUOTA UNLIMITED ON AWAED; GRANT CONNECT, RESOURCE TO AWAED; EXP SYSTEM/SYSMAN@sadhan OWNER=ORAFIN FILE=F:Dumporafin_16022015.DMP GRANTS=Y BUFFER=10000 COMPRESS=Y ROWS=Y  LOG= F:Dumporafin_16022015.log IMP SYSTEM/sysman@MUJAZORC FILE=F:Dumporafin_16022015.DMP FROMUSER=ORAFIN TOUSER=AWAED LOG=F:Dumporafin_16022015.DMP.log The above two commands will export the 'ORAFIN' schema from SADHAN database and import (restore) into the different schema (awaed) on the mujazorc database.How we can use exp/ imp when we have 2 different Oracle database versions?·

[youngprogrammersclub]

Plan A: Renovate old Apps Server Hardware

If our goal is to perform a Test run of our disaster recovery procedures, or to permanently move a database to a new host, then we can use the Restore & Recovery Procedure. If we use Restore & Recovery procedure, then the DBID for the restored database will be the same as the DBID for the original database, so after the restore & recovery procedure is completed, if we connect to the Test database and the recovery catalog DB, the recovery catalog will be updated with information about the Test DB that can interfere with RMAN's ability to restore and recover the source database (due to same DBID)If our goal is to create a new copy of our target database for ongoing use on a new host, then use the RMAN DUPLICATE command instead of Restore & Recovery procedure. The DUPLICATE command assigns a new DBID to the database it creates and enabling it to be registered in the same recovery catalog as the original database.Caution:If we are performing recovery only for TEST purpose, then make sure RMAN is NOT connected to the recovery catalog, otherwise RMAN records information about resorted database in recovery catalog. Thus you are not able to Restore and recover the original database in future with this RMAN.Scenario:This scenario assumes that we have two networked Windows 2003 hosts, DBSERVER and DBSERVER1. A target database named SADHAN is on DBSERVER and is registered in Recovery catalog RMAN. You want to TEST the Restore & Recovery of SADHAN on DBSERVER1, while keeping database SADHAN up and running on DBSERVER. Same version of Oracle Product needs to be installed with which the RMAN Backup was taken.Steps:Install Oracle software only with all its patched on destination folder.Taking RMAN FULL hotbackup including Datafile, Controlfile, Archivelog and Spfile backup.Copy the backup on other host on the same location as per the DBserver.Create PFILE from current server and Copy the Destination server location.Testing and Validating Listener and RMAN Repository Connection.Create Oracle Service for windows on destination server.Create password file for new server database.Start the database on new host with the pfile and Restore the spfile first.Restore ControlfileRestore  and Recover the whole databaseValidate the Restore & Recovery procedure Correctness Consider you have copied entire required backup and file on to the destination server and also you have configured the tnsname and listener configuration of new db and rman successfully.5. Create oracle service for windowsC:>oradim -new -sid SADHAN -intpwd sadhancmd> oradim -new -sid SADHAN -intpwd sys -startmode manual6. Create relevant folder on destination host.D:oracleadminSADHANbdump, cdump, udump, pfileD:oracleoradataSADHANE:oracleArchive7. Create password for new server database.CMD>ORAPWD FILE=D:oracleora92databasePWDsadhan.ora PASSWORD=oracle entries=5Now Configure the ORACLE_SID ON destination Host and try to connect RMAN without catalog.C:>SET ORACLE_SID = SADHANC:SQLPLUS /NOLOGSQL> CONNECT / AS SYSDBAConnected to an idle instance.8. Set the DBID and start the database instance using PFILE and restore the spfile.SQL>STARTUP FORCE NOMOUNT PFILE='D:oracleora92dbssadhan.sql';RMAN> CONNECT TARGET / NOCATALOGRMAN:>SET DBID 63198018;RMAN> RESTORE SPFILE FROM AUTOBACKUP;-or- optional methodRMAN> RESTORE spfile from "H:orabackC-1103102985-20100216-02";RMAN> restore controlfile from "H:ORABACKC-1103102985-20100216-02";You must bounce back the database to make sure spfile is restored.RMAN> SHUTDOWN IMMEDIATE;   RMAN> STARTUP NOMOUNT;9. Restore the Controlfile from autobackup and then Mount the database.RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;RMAN> ALTER DATABASE MOUNT; 10. Restore and Recover the database with the full backup. You must store the backup on the same location as on DBSERVER otherwise you have to catalog the new backup location with CATALOG START WITH 'H:oraback' command.RMAN> RESTORE DATABASE;   If archive log is not available (in case of Cold Backup) change the command as: RMAN> RECOVER DATABASE NOREDO;Restore the database in case of hotbackup to perform media recovery.RMAN> RECOVER DATABASE;Note: Do not panic if recovery fail askig for current redolog files, as you have full backup, no problem just exit RMAN relogin and open the database with resetlogs option.RMAN> ALTER DATABASE OPEN;-OR- ALTER DATABASE OPEN RESETLOGS;RMAN> sql 'alter database open resetlogs'; Verify the Database:SQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            E:oracleOldest online log sequence     0Next log sequence to archive   1Current log sequence           1 SQL> select name,dbid from v$database;NAME            DBID--------- ----------SADHAN       63198018 Connect Identifier of Database:Ping this connect identifier before performing actual restore and recovery process. You can validate it manually and can use Net Manager for the same. Later we can add the other database information in the tnsname.ora. This is just for security purpose not to connect any other database by mistake.tnsname.ora:RMAN.AL-SADHAN.COM =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = rmanbackup)(PORT = 1521))    )    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = RMAN)    )  )SADHAN.WORLD =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS =         (PROTOCOL = TCP)        (HOST = 192.168.14.222)        (PORT = 1521)      )    )    (CONNECT_DATA =       (SID = sadhan)    )  )Listener.ora:# TNSNAMES.ORA Network Configuration File: D:oracleora92networkadmintnsnames.ora# Generated by Oracle configuration tools.SADHAN.AL-SADHAN.COM =  (DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.222)(PORT = 1521))    )    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = SADHAN.WORLD)    )  )Sqlnet.ora:# SQLNET.ORA Network Configuration File: D:oracleora92networkadminsqlnet.ora# Generated by Oracle configuration tools. NAMES.DEFAULT_DOMAIN = al-sadhan.comSQLNET.AUTHENTICATION_SERVICES= (NTS)NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

[youngprogrammersclub]

Planning to Renovate old Apps Server Hardware

Our some of the Sister's companies complaining since long time, they are facing frequent restart of server and very slow performance of database application and different type of issues. As we are having very old server based on Windows 2003 and Oracle 9i database with the patched version of 9.2.0.7. We have very limited disk capacity and memory size as well as hardware configuration is very old due to this we are facing issues. Finally we have made two plan to renovate the hardware of these server as well as decided to increase some database parameters. As we are not having the complete idea of different application running on the server as well as not having the same patched version of oracle binaries. So we decided not to go fresh installation of OS/Oracle binaries/Applications on some of the server. Here in this article you can find the plan and its steps. The detail working procedure you can find with my separate post of link below: Install the Oracle into New server and Restore and recover the full backup of old DBserver.Get the Oracle Environment from system restore and use RMAN and other process to synchronize the data after testing. The actual process to prepare new sever is the Restore & Recover    the database after fresh installation of oracle software on destination server but if you are facing the complexity of running application as well as same patched version or if you do not have idea about Instillation process of any running software or facing any other complexity, we have another option to follow the System backup & Restore policy.Plan A:Consider the Drive structure and OS version is same. Perform fresh installation of oracle software and apply the required patch (if any) and use RMAN restore & recovery procedure to synchronizes the data. Check my other post “How to perform this plan: Plan A: Renovate old Apps Server HardwareSteps:Install the Oracle software only in New server and Apply the related patchTake RMAN full Hot or Cold backup of source database and copy to the same location on destination server.Create PFILE from Production server and Copy to the destination server.Create required folder on destination drive.Create Oracle service for Windows.Configure TNS and Listener setting as you can access the Prod, TEST, RMAN database.Create Password file for New Server database.Use the RMAN Duplicate Database concept to duplicate the database.Compare the Test Database with Prod DB after successful.Create the Temp file and Activate Archivelog switch for New Database.Install the entire running application on the new server.Perform Tuning of Database and Application and keep it under testing process (7 days).After verification apply restore and recover procedure again.Change the port of new server same as previous DBserver.Make this server Active for end user.Plan B:Get the same oracle binaries from system Restore & Recovery Procedure and use RMAN and other recovery process to synchronize the data after testing. Check the other post how to manage successful system restore and how to perform this plan: Plan B Renovate old Apps Server HardwareSteps:Create pfile from spfile on Prod database.Backup & Restore whole system of Prod DB on Destination Server.Make either RMAN HOT or COLD Backup of Prod Database and copy it to the destination server location.Make sure to check the exiting folder file, pwdfile, Windows services on the destination server.Configure the TNS and Listener for Prod/Test/RMAN databaseStart the database using Pfile in nomount phase and Restore the Spfile and Controlfile.Take the database in Mount phase and Restore & Recover the database.Open the database and verify the successful restore and recovery.Verify All the Application is running successfully with normal behavior by keeping the server under testing few days.If all are well, disconnect the original server and Restore & Recover the database with latest prod Backup.Switch the Production server IP and Name with the new server and activate for end user.Follow the Link for Detail description of Plan:Plan B Renovate old Apps Server HardwarePlan A: Renovate old Apps Server Hardware

[youngprogrammersclub]

Duplicate Database with RMAN without Connecting to Target Database

Tested on Oracle 10g database: Platform: Windows 2003Scenario: Assume that you have same Platform and Oracle version installed on destination server and you have already copied the Backup to the destination server same location. You have already created the oracle service (for windows env. only) and password file and tns configuration for destination server. For details about the above concept follow my other post: Create Duplicate Oracle Server in Oracle 9i cmd> set oracle_sid = sid_name_of_databaseCreate pfile and copy to the destination server location.Edit this pfile and give db_name= and control_files=. If you do not want to change leave it as it is. cmd> sqlplus /nologcmd> connect / as sysdbaConnected to an idle instance. SQL> Startup force nomount  pfile = ;SQL> Create spfile from pfile = ;Now connect to the rman and list the backup summary to conform the backup repository and then restore the controlfile by using any of the below method:RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;RMAN> RESTORE CONTROLFILE FROM "\testdborcl3c-691421794-20120506-01";RMAN> restore controlfile from "H:ORABACKC-1103102985-20100216-02"; RMAN> ALTER DATABASE MOUNT;Note: Make sure that backup pieces are on the same location where it was there on production db. If you don’t have the same location, then make RMAN aware of the changed location using “catalog” command. RMAN>catalog backuppiece ;If there are more backuppieces, than they can be cataloged using command :RMAN>catalog start with ; After cataloging backup piece, issue “restore database” command. If you need to restore datafiles to a location different to the one recorded in controlfile, use SET NEWNAME command as below:run {set newname for datafile 1 to ‘/newLocation/system.dbf’;set newname for datafile 2 to ‘/newLocation/undotbs.dbf’;…- otherwise-RMAN> Restore database;RMAN> Switch datafile all;

[youngprogrammersclub]

Killing Session with TOAD

You need to have DBA privilege to carry out such activity using toad, click on Session Browser, expand the program under program list, select the intended process; on the top click on Kill X button. You can see here not only the session and their SQL statement, cursor, explain plan and DML progress but you can see the database lock, blocking locks, database object being accessed and activities of rollback segment Open the Database --> Monitor --> Session Browsertab or directly clicking on the session toolbar button and find the ACTIVE one and press the X button. The session will be killed immediately.SQL>Select * from v$session where type = 'USER' and status = 'ACTIVE'; Take the sid and serial# values from there and use the below command to kill particular session. SQL>alter system kill session 'sid, serial#';Once you get the sid you can get the username from v$session SQL>Select username from v$session where sid=23;This command Toad issue to kill the process is of form: SQL>ALTER SYSTEM DISCONNECT SESSION ‘SID#, SERIAL#’ IMMEDIATE;

As the Monitoring and killing session is routine DBA task. Therefore you can schedule it using toad after each time toad open or session open it will automatically launch this widow. For more details check the option View –> Toad Option –> startup

[youngprogrammersclub]

Query to find locks

Query using v$process and v$locked_object to see specific locks SELECT s.sid, s.serial#, p.spid FROM v$session s, v$process p WHERE s.paddr = p.addr AND s.sid IN (SELECT SESSION_ID FROM v$locked_object); Query to find all blocking locks in database SELECT     i.instance_name, l.session_id || ' / ' || s.serial#, s.status     session_status, l.oracle_username, o.owner, o.object_name, o.object_type,                           DECODE (   l.locked_mode, 0, 'None', 1, 'NoLock', 2, 'Row-Share (SS)',     3, 'Row-Exclusive (SX)', 4, 'Share-Table', 5, 'Share-Row-Exclusive (SSX)',     6, 'Exclusive',          ''            )  locked_mode FROM     dba_objects o, gv$session s, gv$locked_object l, gv$instance i WHERE   i.inst_id = l.inst_id AND s.inst_id = l.inst_id   AND s.sid = l.session_id   AND o.object_id = l.object_id ORDER BY     i.instance_name, l.session_id; Query all DML/DDL locks in the database SELECT     i.instance_name, l.session_id || ' / ' || s.serial#,     s.status, s.username, l.lock_type, l.mode_held,     o.owner || '.' || o.object_name, SUBSTR(s.program, 0, 20),     ROUND(w.seconds_in_wait/60, 2)                 FROM     v$instance i, v$session s, dba_locks l, dba_objects o, v$session_wait  w WHERE   s.sid = l.session_id   AND l.lock_type IN ('DML','DDL')   AND l.lock_id1 = o.object_id   AND l.session_id = w.sid ORDER BY     i.instance_name, l.session_id; Query to Show locked objects SELECT oracle_username || ' (' || s.osuser || ')' username, s.sid || ',' || s.serial# sess_id ,  owner || ',' || object_name object,  object_type,  DECODE( l.block,  0, 'Not Blocking' , 1, 'Blocking',  2,  'Global') status,  DECODE(v.locked_mode,  0, 'None',  1,'Null' , 2, 'Row-S (SS)',  3, 'Row-X (SX)',  4, 'Share',  5, 'S/Row-X (SSX)' , 6, 'Exclusive', TO_CHAR(lmode)) mode_held  FROM v$locked_object v, dba_objects d, v$lock l, v$session s WHERE v.object_id = d.object_id AND v.object_id = l.id1 AND v.session_id = s.sid ORDER BY oracle_username,  session_id; Query to show locks for a Specific User‎s SELECT NVL(b.username,'SYS') username, session_id,lock_type,mode_held, mode_requested,lock_id1,lock_id2 FROM sys.dba_lock_internal a, sys.v_$session b WHERE username='HRMS'; DDL Locks Currently in use SELECT   NVL (owner, 'SYS') owner, session_id, NAME, TYPE, mode_held,          mode_requested     FROM dba_ddl_locks ORDER BY 2; DML locks Currently in use SELECT   NVL (owner, 'SYS') owner, session_id, NAME, mode_held, mode_requested     FROM sys.dba_dml_locks ORDER BY 2; Query to find DML locks along with the time they have been holding the lock SELECT     i.instance_name, l.sid || ' / ' || s.serial#, s.username, DECODE(   l.lmode, 1, NULL, 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive',    'None'), DECODE(   l.request, 1, NULL, 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive',    'None')                             , DECODE (   l.type, 'CF', 'Control File', 'DX', 'Distributed Transaction', 'FS', 'File Set', 'IR', 'Instance Recovery', 'IS', 'Instance State', 'IV', 'Libcache Invalidation', 'LS', 'Log Start or Log Switch', 'MR', 'Media Recovery', 'RT', 'Redo Thread', 'RW', 'Row Wait', 'SQ', 'Sequence Number', 'ST', 'Diskspace Transaction'             , 'TE', 'Extend Table', 'TT', 'Temp Table', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PLSQL User_lock'             , 'UN', 'User Name',  'Nothing'  ) , o.owner || '.' || o.object_name , ROUND(l.ctime/60, 2)                           FROM     v$instance i, v$session s, v$lock l, dba_objects o, dba_tables t WHERE   l.id1    =  o.object_id   AND s.sid            =  l.sid   AND o.owner          =  t.owner   AND o.object_name    =  t.table_name   AND o.owner           'SYS'   AND l.type           =  'TM' ORDER BY     i.instance_name, l.sid; Locks held by all users SELECT s.username, s.sid, DECODE ( l.TYPE,           'MR', 'Media Recovery', 'RT', 'Redo Thread',  'UN', 'User Name', 'TX', 'Transaction',           'TM', 'DML', 'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction', 'CF', 'Control File',           'IS', 'Instance State', 'DS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction',           'TS', 'Temp Segment', 'IV', 'Library Cache Invalidation', 'LS', 'Log Start or Switch',           'RW', 'Row Wait','SQ', 'Sequence Number', 'TE', 'Extend Table', 'TT', 'Temp Table'        ) ltype, o.object_name,        DECODE (  l.lmode, 2, 'Row-S(SS)', 3, 'Row-X(SX)', 4, 'Share', 5, 'S/Row-X(SSX)', 6, 'Exclusive',           'Other'   ) mode_held   FROM dba_objects o, v$session s, v$lock l  WHERE s.sid = l.sid AND o.object_id = l.id1; Query to find Deadlock SELECT sn.username, m.sid, m.type,    DECODE(m.lmode, 0, 'None'                  , 1, 'Null'                  , 2, 'Row Share'                  , 3, 'Row Excl.'                  , 4, 'Share'                  , 5, 'S/Row Excl.'                  , 6, 'Exclusive'                  , lmode, ltrim(to_char(lmode,'990'))) lmode,    DECODE(m.request, 0, 'None'                  , 1, 'Null'                  , 2, 'Row Share'                  , 3, 'Row Excl.'                  , 4, 'Share'                  , 5, 'S/Row Excl.'                  , 6, 'Exclusive'                  , request, ltrim(to_char(request,'990'))) request,          m.id1,m.id2 FROM v$session sn, V$lock m WHERE (sn.sid = m.sid AND m.request != 0)    OR (sn.sid = m.sid       AND m.request = 0 AND lmode != 4       AND (id1, id2 ) IN (SELECT s.id1, s.id2                           FROM v$lock s                           WHERE request != 0                                  AND s.id1 = m.id1                                  AND s.id2 = m.id2 )       ) ORDER BY id1,id2, m.request;

[youngprogrammersclub]

Script: Check Blocker in Oracle 10g

prompt === Wait Chains === SELECT /*+ rule */ rpad(rpad('+', level ,'-'),4,' ')||lpad(wc.sid,4,' ')||','||lpad(wc.sess_serial#,5,' ') sidser ,wc.blocker_sid bsid,se.username orauser,se.osuser osuser,lpad(wc.osid,5)||'-'||lpad(substr(nvl(se.program,'null'),instr(se.program,'(')+1,4),4) sp,decode(wc.num_waiters,0,' ','x') w ,wc.wait_event_text event,least(se.p1,9999999999) p1,least(se.p2,999999) p2,least(se.p3,99) p3 ,wc.row_wait_obj# wobj,nvl(se.module,'') module ,least(se.last_call_et,999) elaps ,decode (se.command, 0, ' 0 ', 1, 'CRTB', 2, 'ISRT', 3, 'SEL', 4, 'CRCL', 5, 'ALCL', 6, 'UPDT', 7, 'DEL' , 8, 'DR', 9, 'CRIX',10, 'DRIX',11, 'ALIX',12, 'DRTB',15, 'ALTB',17, 'GRNT',18, 'REVK',19, 'CSYN',20, 'DSYN',21, 'CRVW',22, 'DRVW',26, 'LKTB',27, 'NOOP',28, 'RENM',29, 'CMNT',30, 'AUDT',31, 'NAUD',32,'CRLN' ,33, 'DRLN',34, 'CRDB',35, 'ALDB',36, 'CRRB',37, 'ALRB',38, 'DRRB',39, 'CRTS',40, 'ALTS',41, 'DRTS',42, 'ALSE',43, 'ALUS',44, 'COMT',45, 'RLBK',46, 'SVPT',47, 'PLSQ',62, 'ANTB',63, 'ANIX',64, 'ANCL',85, 'TRTB' , to_char(se.command)) command ,nvl(se.sql_id,se.prev_sql_id)||':'||decode(se.sql_id,null,'p',se.sql_child_number) sqlid FROM v$wait_chains wc,v$session se where wc.sid = se.sid and wc.sess_serial# = se.serial# CONNECT BY PRIOR wc.sid = wc.blocker_sid AND PRIOR wc.sess_serial# = wc.blocker_sess_serial# AND PRIOR wc.INSTANCE = wc.blocker_instance START WITH wc.blocker_is_valid = 'FALSE'; --Show blocking user sessions and the waiting user sessions SELECT DISTINCT o.object_name, sh.username   || '('   || sh.sid || ')' "Holder",  sw.username || '(' || sw.sid || ')' "Waiter",   DECODE ( lh.lmode, 1, 'NULL', 2, 'row share', 3, 'row exclusive', 4, 'share',  5, 'share row exclusive',  6, 'exclusive'  ) "Lock Type"  FROM all_objects o, v$session sw, v$lock lw, v$session sh, v$lock lh  WHERE lh.id1 = o.object_id AND lh.id1 = lw.id1 AND sh.sid = lh.sid AND sw.sid = lw.sid  AND sh.lockwait IS NULL  AND sw.lockwait IS NOT NULL AND lh.TYPE = 'TM' AND lw.TYPE = 'TM' /

[youngprogrammersclub]

How to Tune Oracle 10g Database

Database Tuning is essential not only for better execution of SQL statement but also for smooth running of applications. Oracle gives us flexible option in which we can generate AWR report in for tuning purpose. Same work you can perform easily through License version of Enterprise Manager. It is not possible to highlights every thing as the performance tuning is a big concept. Through this article I am trying to cover some of the required concept for database performing tuning. Collecting Statspack/AWR Report: Login with SYSDBA and make the Oracle Environment for the particular Database is set. If you are oracle 10g or onward gather the AWR report. In oracle 9i you can generate Statspack for the same purpose. If you want the AWR report in Oracle RAC environment than use 'awrgrpt.sql' script to gather the report of multiple instances running on various nodes. SQL> SELECT DBID, NAME FROM V$DATABASE; DBID        NAME -------------------------- 691421794   ORCL3 SQL> select status from v$instance; STATUS -------- OPEN SQL>@?/rdbms/admin/awrrpt.sql Now select format for the report either ‘HTML’ or ‘TEXT’Here Select numbers of days you want to go back or just hit enter for listing all completed snapshots then Enter value for begin_snap: 181.Enter value for end_snap: 181.Enter value for report_name:awrrpt_1_181_181.htmlEnter value for report_type: html Here you can specify the name of the report or select the default name assigned (html). Enter 'html' for an HTML report, or 'text' for plain text Defaults is 'html'. Use ls –ltr to show the new file created under the path: d01/oratst/orcl3/product/10.2/rdbms/admin. Check my other post: How to Create AWR Report Manually, How to use Statspack ReportAnalyzing the AWR report and Suggesting possible Recommendations: Once we obtain the AWR report our main motive is to analyze the AWR report and come up with possible recommendations. Depending on the size of our Production Database we can come up with possible recommendations.Note: This recommended result should be first implemented in test environment and after successful results should be adopted in production environments. Redologs: We need to make sure our redo logs are large enough. Check the number of log switches, one every twenty minutes is ideal, more than this is too high and you should make them larger to reduce the number of switches. We can find the log switches in the Instance Activity Stats part of the AWR report. Check more about Redolog Performace: Redolog PerformaceExample: Instance Activity Stats - Thread Activity * Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour log switches (derived) 2 2.00 We can see in this system there are 2 log switches per hourly basis, which is good. So this tells us the redo logs are large enough. Parsing: Check the hard parsing amount. It should be zero. If it is not, this indicates that our SGA is probably too small, increase the size of SGA and test again. Hard parsing is caused by use of literals in SQL (as opposed to bind variables).If the queries in question are our own, we should change them to use bind variables. We can find this information on the first page. Load Profile per Second per Transaction per Exec per Call. Parses: 33.9 7.2 hard parses: 0.5 0.1We can see in this system the hard parses are almost zero, which is good. Now coming to the SGA we can focus on the below considerations: Buffer hit and Library hit percentages: Check the buffer hit and library hit percentages. We want them to be 100%, if not we should increase the size of SGA. This is also on the first page: How to take Health report check my post: DB Health ReportInstance Efficiency Percentages (Target 100%) Buffer Nowait %: 99.82 Redo NoWait %: 100.00 Buffer Hit %: 99.52 In-memory Sort %: 100.00 Library Hit %: 98.63 Soft Parse %: 98.60 Execute to Parse %: 50.96 Latch Hit %: 98.16 Parse CPU to Parse Elapsd %: 66.67 % Non-Parse CPU: 97.75 In this case they are also good. Top 5 Timed Foreground Events: Check the average wait times. Anything over 5ms indicates a problem. If we see database CPU events in the Top 5, this indicates that SGA is too small. We may also be missing indexes. Check the optimizer statistics. Avg wait % DB Event Waits Time(s) (ms) time Wait Class ---------- ------------ ----------- ------ ------ ---------- DB CPU 13 59.9 log file sync 1,592 8 5 32.3 Commit sort segment request 1 1 1001 4.0 Configurat db file sequential read 216 1 4 3.6 User I/O db file scattered read 64 0 6 1.5 User I/O We can see here that the major issue is DB CPU, which generally indicates SGA is too small. Check my other post (under Performance Tuning how Category) to find DB CPU report. Database file sequential/scattered read These indicate time spent doing table scans and index scans (respectively).If these are high (over 5ms),We should consider moving your data files to reduce disk I/O contention, or move them to faster disks. 5. Enqueue high watermark: This indicates hardware contention that occurs when there are multiple users inserting into LOB segments at once while the database is trying to reclaim unused space. We should consider enabling secure files to improve LOB performance (SECURE_FILES = ALWAYS). We cannot see these in my example report, because this was not a problem in my environment, so it did not make it into the Top 5. If it did, you would see an event called: example: HW – contention other things to be aware of… we will also check our database configuration. 6. MEMORY_TARGET: Do not use this setting. We should have our DBA tune the memory manually instead. This will result in a better tuned database. We should start with 60% of physical memory allocated to SGA and 20% to PGA. 7. AUDIT_TRAIL: Usually we do not use this setting much for tuning. But auditing on Database level can be overhead to the Database.Related Post:Dynamic View for Performace TuningDB Monitoring & Performance Script

[youngprogrammersclub]

Guaranteed Restore Points in Oracle

Guaranteed Restore Points came with oracle 10gR2. It can ensure that you can rewind the database to a time without tradional point in time recovery. It uses db_file_recovery_dest to keep the changed blocks, even if flashback logging is not enabled for your database. It never age out of the control file and must be explicitly dropped. A normal restore point assigns a restore point name to an SCN or specific point in time. The control file can maintain a record of thousands of normal restore points with no significant effect on database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.SQL>CREATE RESTORE POINT before_upgrade;Creating a normal restore point eliminates manually recording an SCN in advance or determines the correct SCN after the fact by using features such as Flashback Query. Like a normal restore point, a guaranteed restore point serves as an alias for an SCN in recovery operations. A guaranteed restore point ensures that you can use Flashback Database to rewind a database to its state at the restore point SCN, even if the generation of flashback logs is disabled. If flashback logging is enabled, then a guaranteed restore point enforces the retention of flashback logs required for Flashback Database to any SCN after the earliest guaranteed restore point.Caution: If we are using Guaranteed Restore Points with Flashback logging turned on, we need to exercise care that we drop restore points that are not in use or that we allocate sufficient space for the flashback logs as specified by the init.ora parameter db_recovery_file_dest_size. This is because the flashback logs will be retained and not overwritten due to space constraints when we create guaranteed restore points.To create first guaranteed restore point when flashback off, you first start the database in mount state after a consistent shut down. After opening in mount state you can create your first guaranteed restore point like below.SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;You can use the LIST command to list either a specific restore point or all restore points known to the RMAN repositoryLIST RESTORE POINT restore_point_name; LIST RESTORE POINT ALL;SCN              RSP Time  Type       Time      Name —————-           ——— ———-  ———        —---      ---- 431843           24-NOV-12            24-NOV-12 NORMAL_RS 448619           24-NOV-12 GUARANTEED 24-NOV-12 GUARANTEED_RSRMAN indicates the SCN and time of the restore point, the type of restore point, and the name of the restore point. You can use