How to move controlfile to a new location in oracle

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’ […]

How to move spfile from file system to ASM in RAC

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 […]

How to change spfile in Oracle RAC.

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 […]

How to install oracle client in silent mode using response file

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 version. 1. Download Oracle client software […]

How to change flash recovery area location

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 […]

How to enable flash recovery area in oracle database

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 […]

How to drop SQL baselines in oracle

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 […]

How to get the DDL of an dbms scheduler job

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 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”‘); […]

How to modify awr snapshot interval setting

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 […]

How to create awr snapshot manually

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 […]