Below are steps to move or rename controlfile to a new location(Mount point or diskgroup). In ORACLE RAC:(Move from +ORACLDG diskgroup to +DATA diskgroup) 1. Get the current control_file location SQL> show parameter control_files NAME TYPE VALUE ———————————— ——————————– —————————— control_files string +ORACLDG/DBACLASS/CONTROLFILE/control01.ctl 2. Set the new location of controlfile: SQL> alter system set control_files=’+DATA’ […]

Below are the steps for moving spfile from file system to ASM diskgroup in RAC. database name – ORCL instance_names – ORCL1,ORCL2 oracle DB nodes – dbhstorcl1, dbhstorcl2 1. Check current pfile location: SQL> show parameter pfile NAME TYPE VALUE ———————————— ———– —————————— spfile string /oracle/app/oracle/product/dbs/temp_init.ora 2. Now required directory in ASM diskgroup(where pfile will […]

If we are changing spfile in oracle RAC, then we need to use additional SID parameter in the alter system command. Here SID is the instance_name. For changing the parameter in both the instances of the RAC. alter system set SEC_CASE_SENSITIVE_LOGON=FALSE scope=both sid=’*’; For changing the parameter in a specific instance. alter system set job_queue_processes=500 […]

Though GUI is a preferable method to do client installation, But sometimes DBAs might be not to be able to xmanager to enable GUI for the server. In that case, installation using response file will be useful. In this example, we will do the installation of Oracle client 12.1.0.2 version. 1. Download Oracle client software […]

Below are the steps for changing the flash recovery area(FRA destination). 1. Check the current FRA destination. SQL> show parameter db_recovery_file NAME TYPE VALUE ———————————— ———– —————————— db_recovery_file_dest string /dbaclassdb/oradata/FRA db_recovery_file_dest_size big integer 20G SQL> select name from V$RECOVERY_FILE_DEST; NAME ————————————– /dbaclassdb/oradata/FRA 2. Change db_recovery_file_dest parameter. SQL> alter system set db_recovery_file_dest=’/fradg/oradata/FRA’ scope=both; System altered. select […]

The flash recovery area(FRA) is an Oracle-managed destination( either FILE SYSTEM or ASM ) for centralized backup and recovery files. It simplifies the backup management. The following recovery-related files are stored in the flash recovery area: — Current control file — Online redo logs — Archived redo logs — Flashback logs — Control file auto […]

Below is the steps for dropping a sql baseline from the database.   1.Get the sql_handle and sql_baseline name of the sql_id: SELECT sql_handle, plan_name FROM dba_sql_plan_baselines WHERE signature IN ( SELECT exact_matching_signature FROM gv$sql WHERE sql_id=’&SQL_ID’) SQL_HANDLE PLAN_NAME ——————————————— —————————————————- SQL_164b2be280f1ffba SQL_PLAN_1cktbwa0g3zxu06dab5d5 2. Drop the baseline: SQL> select sql_handle,plan_name from dba_sql_plan_baselines where plan_name=’SQL_PLAN_1cktbwa0g3zxu06dab5d5′; SQL_HANDLE […]

The easy way to get the ddl of an dbms scheduler job is : select dbms_metadata.get_ddl(‘PROCOBJ’,’JOB_NAME’,’JOB_OWNER’) from dual; set pagesize 299 set long 999 select dbms_metadata.get_ddl(‘PROCOBJ’,’DBACLASS_PURGE’,’DBAUSER’) from dual; DBMS_METADATA.GET_DDL(‘PROCOBJ’,’DBACLASS_PURGE’,’DBAUSER’) ——————————————————————————– BEGIN dbms_scheduler.create_job(‘”DBACLASS_PURGE”‘, job_type=>’STORED_PROCEDURE’, job_action=> ‘DBAUSER.DBA_CREATE_DBACIRCUIT_PATH’ , number_of_arguments=>0, start_date=>TO_TIMESTAMP_TZ(’06-JUN-2017 06.58.16.001208000 AM ASIA/BAHRAIN’,’DD -MON-RRRR HH.MI.SSXFF AM TZR’,’NLS_DATE_LANGUAGE=english’), repeat_interval=> ‘FREQ=HOURLY;INTERVAL=2;’ , end_date=>NULL, job_class=>'”DEFAULT_JOB_CLASS”‘, enabled=>FALSE, auto_drop=>FALSE,comments=> ‘DBACLASS_PURGE’ ); dbms_scheduler.enable(‘”DBACLASS_PURGE”‘); […]

We can change the snap_interval and retention period for the automatic awr snapshot collection, using modify_snapshot_settings function. The default settings for ‘interval’ and ‘retention’ are 60 minutes and 8 days . DEFAULT SETTING: select snap_interval, retention from dba_hist_wr_control; SNAP_INTERVAL RETENTION ————————————————————————— ————————————————————————— +00000 01:00:00.0 +00008 00:00:00.0 Modify the snapshot setting:( snap_interval 30 min and retention […]

Automatic Workload Repository (AWR) is a collection of database statistics owned by the SYS user. By default snapshot are generated once every 60min . But In case we wish to generate awr snapshot manually, then we can run the below script.  This is usually useful, when we need to generate an awr report for a non-standard window with smaller interval. Lets say, we want to generate a report […]