HOW TO

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

2. Set the new location of controlfile:

3. start the database in nomount stage:

4. Restore controlfile to new location:

5. […]

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:

2. Now required directory in ASM diskgroup(where pfile will be created)

3. Create the spfile from pfile:

4. Update […]

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.

For changing the parameter in a specific instance.

If we don’t mention the SID parameter, then […]

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 12.1.0.2 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.

2. Change db_recovery_file_dest parameter.

From onwards new files will be created in this new location. However, the flashback logs will be created under the old location. For the FLASHBACK logfiles to be able to pick up […]

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:

2. Drop the baseline:

An sql_handle can have multiple sql baselines tagged, So if you want to drop all the sql baselines of that handle, then drop the sql handle itself. […]

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;

But what if the job owner is SYS? Will the same syntax work?

Throwing error!!!! Well we have an workaround for this. Lets copy the scheduler job to another schema.

Now we have the […]

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:

Modify the snapshot setting:( snap_interval 30 min and retention 30 days(60*24*30) The values for both ‘interval’ and ‘retention’ are expressed in […]

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

Page 1 of 41234
Skip to toolbar