Oracle DBA Concepts: Background Processes in Oracle ASM (Automatic storage Management)

The following background processes are an integral part of Automatic Storage Management:


ARBn performs the actual rebalance data extent movements in an Automatic Storage Management instance. There can be many of these processes running at a time, named ARB0, ARB1, and so on.


ASMB runs in a database instance that is using an ASM disk group. ASMB communicates with the ASM instance, managing storage and providing statistics. ASMB can also run in the ASM instance. ASMB runs in ASM instances when the ASMCMD cp command runs or when the database instance first starts if the SPFILE is stored in ASM.


GMON maintains disk membership in ASM disk groups.


MARK marks ASM allocation units as stale following a missed write to an offline disk. This essentially tracks which extents require resync for offline disks.


RBAL runs in both database and ASM instances. In the database instance, it does a global open of ASM disks. In an ASM instance, it also coordinates rebalance activity for disk groups.


Oracle Database Administrator Interview Questions on RAC

Oracle RAC (Voting Disk ) FAQ’s


1.What is Voting Disk?


Voting Disk is a file that maintains node membership details.
All members in the Cluster read and write Heartbeat Information in Voting Disk.


2. What Information is stored in Voting Disk?


Voting disks contain static and dynamic data.
Static data : Info about nodes in the cluster
Dynamic data : Disk heartbeat logging
It maintains and consists of important details about the cluster nodes membership, such as
– which node is part of the cluster,
– which node is joining the cluster, and
– which node is leaving the cluster.


3. Why do we need Voting Disk?


CSSD processes (Cluster Services Synchronization Daemon) monitor the health of  RAC nodes employing two distinct heart 


beats: Network heart beat and Disk heart beat. Healthy nodes will have continuous network and disk heartbeats exchanged 


between the  nodes. Break in heart beat indicates a possible error scenario. There are few different scenarios possible 


with missing heart beats:
1. Network heart beat is successful, but disk heart beat is missed.
2. Disk heart beat is successful, but network heart beat is missed.
3. Both heart beats failed.
In addition, with numerous nodes, there are other possible scenarios too. Few possible scenarios:
1. Nodes have split in to N sets of nodes, communicating within the set, but not with members in other set.
2. Just one node is unhealthy.
Nodes with quorum will maintain active membership of the cluster and other node(s) will be fenced/rebooted.


4.Why do we have odd number of voting disks?


Odd number of Voting disks are required to prevent Split Brain Syndrome.
A node must be able to access more than half of the voting disks in order to decide which node can be evicted incase of 


failure.


5. How to backup Voting Disk?


Prior to Oracle 11g R2 version, Voting Disk is backed up using dd command


From 11g R2, no need to manually backup voting disk.
It is automatically backup along with OCR  whenever there is a configuration change.


In 11g R2, Restoring Voting disk from a manually copied backupfile may prevent cluster services from starting up.

Oracle Database Administrator Interview Questions on RMAN

RMAN INTERVIEW QUESTIONS
========================


1.What is the difference between recovery catalog and Nocatalog backup in RMAN?


NoCatalog Backup:


ByDefault RMAN connects to the target database in Nocatalog Mode.
In Nocatalog Mode, Backup Information and Metadata related to RMAN is stored in target database controlfile.


Catalog Backup:


A recovery catalog is a schema created in a separate database that contains metadata obtained from the target control file.
In recovery catalog we can store rman scripts.


We can store metadata about multiple incarnations of a single target database in the catalog.
Recovery catalog is central and can have information of many databases.
If the control file is lost and must be restored from backup, the backup configuration information is available when the database is not mounted.


2.What are the differences between crosscheck and validate commands?


Validate command is to examine a backup set and report whether it can be restored. RMAN scans all of the backup pieces in the specified backup sets and looks at the checksum to verify that the contents are intact so that backup can be successfully restored if necessary.


Crosscheck command is to verify the status of backups and copies recorded in the RMAN repository against media such as disk or tape. The crosscheck command only processes files created on the same device type as the channel running crosscheck.


3.What is obsolete backup & expired backup?


A status of “expired” means that the backup piece or backup set is not found in the backup destination.


A status of “obsolete” means the backup piece is still available, but it is no longer needed. The backup piece is no longer needed since RMAN has been configured to no longer need this piece after so many days have elapsed, or so many backups have been performed.


4.What is the difference between hot backup & RMAN backup?


We need to put the database in Backup mode for Hot  Backup. RMAN Backup does not need the database to be in Backup mode.


5. Which Tables are have information required for RMAN list & report commands ?
 V$BACKUP_FILES and recovery catalog views e.g.,  RC_DATAFILE_COPY or RC_ARCHIVED_LOG.


Below tables contain RMAN Catalog information:


RC_DATABASE_INCARNATION 
RC_BACKUP_COPY_DETAILS
RC_BACKUP_CORRUPTION
RC_BACKUP-DATAFILE_SUMMARY






Oracle RMAN Inremental Backup Algorithm and Change Tracking File

During an incremental backup, RMAN reads the SCN of each data block in the input file and compares it to the checkpoint SCN
of the parent incremental backup. If the SCN in the input data block is greater than or equal to the checkpoint SCN of the
parent, then RMAN copies the block.

Performance of incremental Backup can be improved by enabling block change tracking.When Change tracking is enabled, all the changes to datablocks are recored in change tracking file. RMAN can read the changed SCN’s from change tracking file, instead of reading each and every input file.
One change tracking file is created for the entire database.
By default, the change tracking file is created as an Oracle managed file in DB_CREATE_FILE_DEST. We can specify the name of the block change tracking file by providing the location you choose.



SQL command to enable block change tracking

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
This creates change tracking file in “db_create_file_dest” directory

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING  USING FILE ‘/test1/data/rman_change_track.f’ REUSE;
This creates the change tracking file in the location specified.

SQL command to disable block change tracking

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
By Default,  block change tracking is disabled.
If the change tracking feature is disabled, any existing change tracking files will be deleted

SQL query to find change tracking file Details

select filename, status from v$block_change_tracking

How to Relocate the change tracking file

1.Shut down the database

SHUTDOWN IMMEDIATE

2.Using OS commands, move the change tracking file to a new location.

3.Mount the database and move the change tracking file to a location that has more space.

SQL query to rename change tracking file

ALTER DATABASE RENAME FILE   ‘/test1/data/rman_change_track.f’ TO ‘/newtest1/data1/change_trk.f’;

4.Open the database:

ALTER DATABASE OPEN;

If we cannot afford downtime for database shutdown, change tracking file can be relocated as below

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘new-location’;

Contents of change tracking file will be lost if we use the above method.So its not a preferable method.
RMAN will have to scan the entire file until the next level 0 incremental backup is done

Advantages of RMAN Incremental Backups

What is RMAN Incremental Backup?


Incremental Backups take  back up  of only datafile blocks that have changed after a previous full backup. 
Incremental backups are applicable for databases, individual tablespaces or datafiles.




Benefits of RMAN Incremental Backup:


1.RMAN Incremental backups are used to periodically roll forward an image copy of the database


2.Reduced amount of time needed for Backups


3.Less Network bandwidth is required


4.To get adequate backup performance when the aggregate tape bandwidth available for tape write I/Os is much less than the aggregate disk bandwidth for disk read I/Os


5.To  recover changes to objects created with the NOLOGGING option. 


6.To reduce backup sizes for NOARCHIVELOG databases. Instead of making a whole database backup every time, you can make incremental backups.


Note:


If the database is in ARCHIVELOG mode,  incremental backups can be taken if the database is open;
If the database is in NOARCHIVELOG mode, then we can only make incremental backups after a consistent shutdown.




Click Here to know more about how incremental backup is implemented by RMAN and the imporatnace of block change tracking feature.

How to Perform Clusterware Compatibility Testing in Oracle RAC

+ Compatiblity Testing can be done using Oracle Certification Environment (OCE) kit

Installing the Oracle Certification Environment Software for Oracle RAC

The OCE Certification Kit required to certify the system for Oracle RAC 11g Release 1 (11.1) is available for download only. The Single Instance certification tests should be completed prior to installing the OCE kit for Oracle RAC. Refer to the previous section if necessary. Once Single Instance testing has successfully completed, the single instance OCE installations must be archived to allow OCE installations for Oracle RAC to succeed. The OCE kit for Oracle RAC should be installed separately on each node of the cluster. If the ORACLE_HOME is located on a shared disk, multiple installations of OCE will not be possible. In that case, it will not be possible to run OCE tests simultaneously, and the time required to complete certification will be greatly increased. To install the OCE Kit:

1.      Download OCE for Oracle RAC 11g Release 1 (11.1) archive to a suitable location, such as /tmp/oce. The OCE archives are either CPIO archives, or compressed CPIO archives.
If compressed, extract as follows:
gunzip -c OCE ARCHIVE | cpio -idmv
If not compressed, extract as follows:
cpio -idmv < OCE_ARCHIVE
Where, OCE_ARCHIVE is the name of the archive.
2.      Enter the following command to run the OCE Installer:
3.  $ archive_location/oce_install.sh
The Environment variable screen is displayed.
4.      Enter values for each of the environment variables as described on screen.
Note: You must press the Enter key once to enter the new variable value, and then press the Enter key again to move onto the next variable.
5.      Type Done when finished and press the Enter key.
The installation progress screen is displayed. When all stages are complete, the installer will exit.
6.      Check $ORACLE_HOME/OCEinstallRAC.log file, and verify that there are no errors.
7.      Download the Binaries Package for OCE for Oracle RAC 11g Release 1 (11.1) which matches your platform, and extract the archive as explained in Step 1.
8.      Enter the following command to run the OCE binaries installation script.
9.  $ /tmp/oce/oce_exes_install.sh
10.  Check the OCE Kit installation log file, $ORACLE_HOME/OCE/install_log.txt, and verify that the installation was successful.
The kit is installed in the $ORACLE_HOME/oce directory.
Preparing the System for Multi-Node High Availability Services Testing
The Oracle RAC High Availability Services test suite must use shared storage for data files. Depending on the type of shared storage utilized, some preliminary setup may be required. Before running the Oracle RAC High Availability Services test suite, complete the following:
  • If you are using raw devices or logical volumes for shared storage perform the following steps:
Note:
In this example, the OCE user is oracle, which is a member of the dba group; there are 4 nodes in the cluster; and the OCE logical volumes are located in /dev/ocevg/ directory.
    1. Set up the devices or logical volumesrequired by the tests.
    2. Ensure raw devices are accessible and writable across all nodes by the OCE user
      1. # chown -R oracle:dba /dev/ocevg
      2. # chmod -R og+w /dev/ocevg
    3. Export $ORACLE_HOME/oce/work$ORACLE_HOME/dbs, and $ORACLE_HOME/network/admin from the node 1 to all other nodes in the cluster.
On node 1:
      1. # exportfs -i -o rw <node2>:$ORACLE_HOME/oce/work
<node3>:$ORACLE_HOME/oce/work
<node4>:$ORACLE_HOME/oce/work
      1. # exportfs -i -o rw <node2>:$ORACLE_HOME/dbs
<node3>:$ORACLE_HOME/dbs
<node4>:$ORACLE_HOME/dbs
    1. # exportfs -i -o rw <node2>:$ORACLE_HOME/network/admin
<node3>:$ORACLE_HOME/network/admin
<node4>:$ORACLE_HOME/network/admin
    1. $ORACLE_HOME/oce/work$ORACLE_HOME/network/admin, and $ORACLE_HOME/dbs must be mounted on all secondary nodes from the primary (exported) node.
On all nodes except node 1:
      1. # mkdir –p $ORACLE_HOME/oce/work
      2. # chown oracle:dba $ORACLE_HOME/oce/work
      3. # mount <node1>:$ORACLE_HOME/oce/work $ORACLE_HOME/oce/work
      4. # mount <node1>:$ORACLE_HOME/dbs $ORACLE_HOME/dbs
      5. # mount <node1>:$ORACLE_HOME/network/admin
$ORACLE_HOME/network/admin
  • If you are using OCFS or NAS or a vendor clustered file system (CFS), and ORACLE_HOME directory is not located on shared partition, then perform the following steps:
Note:
If NAS, ensure that the appropriate mount options are employed when mounting the NAS partition. Oracle requires specific mount options. Consult your NAS Filer documentation for further details.
    1. Symbolically link $ORACLE_HOME/dbs to the OCFS/CFS/NAS partition on all nodes (in this example, the OCFS/CFS/NAS partition is at /sharedfs).
On node 1:
    1. mkdir /sharedfs/dbs
    2. chown oracle:dba /sharedfs
On all nodes:
      1. mv $ORACLE_HOME/dbs $ORACLE_HOME/dbs.BAK
      2. ln -s /sharedfs/dbs $ORACLE_HOME/dbs
    1. Export $ORACLE_HOME/oce/work and $ORACLE_HOME/network/admin from the primary node.
      1. # exportfs -i -o rw <node2>:$ORACLE_HOME/oce/work
<node3>:$ORACLE_HOME/oce/work
<node4>:$ORACLE_HOME/oce/work
    1. # exportfs -i -o rw <node2>:$ORACLE_HOME/network/admin
<node3>:$ORACLE_HOME/network/admin
<node4>:$ORACLE_HOME/network/admin
    1. $ORACLE_HOME/oce/work and $ORACLE_HOME/network/admin must be mounted on all secondary nodes from the primary (exported) node. Default mount options will suffice.
On all nodes except node 1:
      1. # mkdir –p $ORACLE_HOME/oce/work
      2. # chown oracle:dba $ORACLE_HOME/oce/work
      3. # mount <node1>:$ORACLE_HOME/oce/work $ORACLE_HOME/oce/work
      4. # mount <node1>:$ORACLE_HOME/dbs $ORACLE_HOME/dbs
      5. # mount <node1>:$ORACLE_HOME/network/admin
$ORACLE_HOME/network/admin
  • If you are using OCFS or CFS accessing a shared Oracle home directory, no setup is required.
  • Ensure that no databases are running.
Starting Test Manager
To start Test Manager:
  1. Ensure that the DISPLAY environment variable is set appropriately for your system. To verify that it is, try starting up xclock. If you do not see the clock, or you receive errors, DISPLAY is not set appropriately. You must correct any errors before proceeding.
  2. Enter the following command to launch OCE Test Manager:
3.  $ORACLE_HOME/oce/bin/startTM.sh > /tmp/OCETM.log 2>&1
The OCE Main Menu and OCE Test Manager windows appear.
Running a Test for the First Time
If this is your first time running certification tests, you must perform the following steps:
  1. Start Test Manager as described in Starting Test Manager.
  2. From the OCE – Main Menu window, double click Utilities.
  3. Run the bmchk test by selecting it and clicking Execute.
  4. When the test completes, click Results in the Test Manager window to check the outcome. If the test fails, you must analyze the output ($INST_HOME/work/bmchk) and resolve any issues. Do not proceed with testing until bmchk executes successfully.
  5. Run sdbck (the Seed Database Verification utility) test by selecting it and clicking Execute.
  6. When the test completes, click Results in the Test Manager window to check the outcome. If the test fails, you must analyze the output ($INST_HOME/work/sdbck) and resolve any issues. Do not proceed with testing until sdbck executes successfully.
  7. Run cssck (the CSS Daemon Verification utility) test by selecting it and clicking Execute.
  8. When the test completes, click Results in the Test Manager window to check the outcome. If the test fails, you must analyze the output ($INST_HOME/work/cssck) and resolve any issues. Do not proceed with testing until cssck executes successfully.

Running the OCE Test Suites

The OCE release consists of a set of test suites that you run from Test Manager. Each test suite consists of one or more individual tests. To complete the certification, run each of the Test Suites in the kit for the product for which you are certifying your system.

1.      From the OCE – Main Menu screen, double-click Complete test suites.
2.      From the screen that appears, select the test suite you want to run and click Execute to run it.
The test suite runs.
Test Manager creates two entries for the test suite in the Test Manager window; one in the Suite Name field and another in the History field:
o    The entry in the Current Test field is displayed only for the duration of a test. It displays the time at which you requested the test, and if it starts, when it started. Test Manager might display some tests with a status of Waiting until resources become available on the system.
o    The entry in the History field displays the time you requested it.
When a test finishes, Test Manager deletes its entry in the Current Tests field and adds another entry to the History field showing when the test finished.

Below are the Test plans for Oracle Clusterware Compatibility (Destructive) Testing
(Category : ORACLE HIGH AVAILABILITY FEATURES)

Clusterware Test Category
[Test Code]
Action  Target
Detailed Test Execution
Expected Test Outcome
Actual Test Outcome
[D]
Oracle HA Features
[HW-CW-09]
Run multiple cluvfy operations during Oracle Clusterware and RAC install  All RAC hosts
Configuration:
GNS:
Gns with dhcp (1)
Gns without dhcp (2)
Without gns (3)
Preferred option 1, if not applicable option   2,  if still not applicable option 3
ASM:
Flex asm (1)
Standard asm (2)
Preferred option 1, if not applicable option 2
DB:
CDB
Preconditions:
·          Type `cluvfy` to see all available command syntax and options
Steps:
1- Run cluvfy precondition
2- Do the next install step
3- Run cluvfy post-condition
(cluvfy comp software –n node_list) to check the file permissions
No need to collect CRS/RDBMS log for this test.  You need to submit the output for cluvfy.
Vendor Clusterware:
– same as RAC
RAC:
           Correct cluster verification checks given the state of the cluster hardware and software
Pls provide cvu related logs under
$CRS_HOME/cv/log
[HW-CW-10]
Run concurrent crsctl start/stop crs commands to stop or start Oracle Clusterware in planned mode  All RAC hosts
Configuration:
GNS:
Gns with dhcp (1)
Gns without dhcp (2)
Without gns (3)
Preferred option 1, if not applicable option 2,  if still not applicable option 3
ASM:
Flex asm (1)
Standard asm (2)
Preferred option 1, if not applicable option 2
DB:
CDB
Preconditions:
·          Initiate all Workloads
·          Identify both CSS and CRS master nodes
·          Type `crsctl` as root to see all available command syntax and options
Steps:
1- As root user, run `crsctl stop crs` command concurrently on more than one RAC host, to stop the resident Oracle Clusterware stack
2- Wait until the target Oracle Clusterware stack is fully stopped (via `ps` command)
3- As root user, run `crsctl start crs
    -wait` command concurrently on more than one RAC host, to start the resident Oracle Clusterware stack
Vendor Clusterware:
– N/A
RAC:
– Stop:  All Oracle Clusterware daemons stop without leaving open ports or zombie processes
– Start:  All Oracle Clusterware daemons start without error messages in stdout or any of the CRS, CSS or EVM traces
– Start:  All registered HA resource states match the “target” states, as per 
“crsctl stat res –t”
– For 12cR1, collect
“crsctl stat res –t” in a 60s loop from beginning till the end of run.  Attach the output for auditing.
[HW-CW-11]
Run other concurrent crsctl commands, such as crsctl check crs,   All RAC hosts
Configuration:
GNS:
Gns with dhcp (1)
Gns without dhcp (2)
Without gns (3)
Preferred option 1, if not applicable option   2,  if still not applicable option 3
ASM:
Flex asm (1)
Standard asm (2)
Preferred option 1, if not applicable option 2
DB:
CDB
Preconditions:
·          Initiate all Workloads
·          Identify both CSS and CRS master nodes
·          Type `crsctl` as root to see all available command syntax and options
Steps:
1-        As root user, run any `crsctl check crs` commands concurrently on all nodes
2-        As root user, run any `crsctl check cluster -all` commands concurrently on all nodes
Vendor Clusterware:
– same as RAC
RAC:
           Both `crsctl check crs` and `crsctl check cluster -all` commands produce the appropriate, useful output, without any error messages
           Collect output for step 1 and step 2
[HW-CW-12]
Votedisk and OCR operation
Configuration:
GNS:
Gns with dhcp (1)
Gns without dhcp (2)
Without gns (3)
Preferred option 1, if not applicable option 2,  if still not applicable option 3
ASM:
Flex asm (1)
Standard asm (2)
Preferred option 1, if not applicable option 2
DB:
CDB
Preconditions:
·          Make sure votedisk on ASM diskgroup
·          Make sure ASM OCR files are used
·          Make sure at least one normal redundancy ASM Diskgroup with three failgroups is created and its “compatible.asm” attribute is set to “11.2”;
Steps:
1-        Make sure crs stack are running in all nodes.
2-        Run “crsctl query css votedisk” to check configured VFs;
3-        Run “crsctl replace votedisk +{ASM_DG_NAME}”(As crs user or root user);
4-        Run “crsctl query css votedisk” to get the new VF list;
5-        Run “ocrconfig –add +{ASM_DGNAME}” as root user;
6-        Run “ocrcheck” to verify the OCR files;
7-        Restart CRS stack and then verify the VF/OCR after it comes back;
Variants:
1. Add up to 5 OCR files and restart CRS stack;
RAC:
           In 12cR1, we can support up to 5 OCRs;
            
[HW-CW-13]
crsctl command to manage Oracle clusterware stack
Configuration:
GNS:
Gns with dhcp (1)
Gns without dhcp (2)
Without gns (3)
Preferred option 1, if not applicable option 2,  if still not applicable option 3
ASM:
Flex asm (1)
Standard asm (2)
Preferred option 1, if not applicable option 2
DB:
CDB
Preconditions:
·          CRS stack is up and running on all nodes.
Steps:
1-        Run ‘crsctl check cluster –all’ to get the stack status on all cluster nodes. Make sure stack status of all cluster nodes are correct;
2-        Run ‘crsctl stop cluster –all’ to stop all CRS resource (CSSD/CRSD/EVMD) with application resources;
3-        Run ‘crsctl status cluster –all’ to make sure CRS resource are OFFLINE;
4-        Run ‘crsctl start cluster –all’ to bring back the whole cluster stack
RAC:
           After running “crsctl stop cluster –all”, make sure all ocssd/evmd/crsd processes are stopped on all cluster nodes by “ps –ef”.
– For 12cR1, collect
“crsctl stat res –t” in a 60s loop from beginning till the end of run.  Attach the output for auditing.
[HW-CW-14]
OCR stores in ASM’s diskgroup and kill asm fatal process
Configuration:
GNS:
Gns with dhcp (1)
Gns without dhcp (2)
Without gns (3)
Preferred option 1, if not applicable option   2,  if still not applicable option 3
ASM:
Flex asm (1)
Standard asm (2)
Preferred option 1, if not applicable option 2
DB:
CDB
Preconditions:
·          Initiate Workloads
Steps:
·          Make sure only ASM OCR files   are used by “ocrcheck –config”;
·          Kill the ASM pmon process on   the CRSD PE Master node;
Variants:
   Repeat the same test on non-OCR Master node.
Clusterware:
– Because OCR is stored in ASM, if ASM fails or is brought down on crsd pe master, CRSD pe master will exit and  select a new crsd pe master
– ASM, CRSD will be automatically restarted.
-RDBMS instance should connect to other available asm instance in flex asm env 
– After CRSD restart, all resources’ state shouldn’t change
– New crsd pe master node should be the old crsd pe standby master A new crsd pe standby master should be   elected on other nodes.
(CRSD should recover resources’ previous state)
– For 12cR1, collect
“crsctl stat res –t” in a 60s loop from beginning till the end of run.  Attach the output for auditing.
Collect Logfiles
Run each destructive test, taking note of the test start time, test stop time and fault injection time.  On the surviving node (if applicable), run the “date; crsctl stat res –t; sleep 60” in a loop
At the end of the test run, please collect the following logs and put them in directory <CRSHome>/log with the name format as [log_name]_[hostname] and then tar up and compress  with file name <VendorName>_<TestCode>.tar.gz.(e.g. WidgetCorp_HW-STOR-07.tar.gz):
·         Under <CRSHome>/log/<hostname>, the following logs are required
o   alert[hostname].log
o   crsd/crsd.log
o   cssd/ocssd.log
o   evmd/evmd.log
o   ohasd/ohasd.log
o   gpnpd/gpnpd.log
o   diskmon/diskmon.log
o   mdnsd/mdnsd.log
o   ctssd/ctssd.log
o   agent/*
o   gipcd/gipcd.log  (11.2.0.2 new feature)
o   cvu/cvulog/*.log (11.2.0.2 new feature)
o   cvu/cvutrc/*     (11.2.0.2 new feature)
o   srvm/*
o   admin/*
o   acfs/*
o   crfmond/*
o   crflogd/*
o   racg/*
o   gnsd/* (if gns configured)

SQL Tuning Advisor (10g and 11g)

SQL Tuning Advisor is used for tuning SQL statements.
This is run to implement a ADDM performance finding.
Additionally, you can run the SQL Tuning Advisor on the most resource-intensive SQL statements, referred to as top SQL, from the cursor cache or the AWR, as well as on a user-defined SQL workload.

To run the SQL Tuning Advisor do the following:

1. On the Home Page, under Related Links, click Advisor Central, then click SQL Tuning Advisor. The SQL Tuning Advisor Links page appears.
2. The advisor can be run on one of the following sources:
Top SQL—These consist of recently active top SQL statements from the cursor cache (Spot SQL) or historical top SQL from the AWR (Period SQL).
SQL Tuning Sets—These consist of SQL statements you provide. An STS can be created from SQL statements captured by AWR snapshots or from any SQL workload.
3. For example, you can select Top SQL. The Top SQL page appears. This page has two tabs, Spot SQL and Period SQL. Spot lists recent top SQL from the cursor cache, while Period SQL lists historical top SQL captured in the AWR. You must select an interval to analyze by dragging the shaded box over the period. You then select one or more SQL statements to analyze during the selected period.
4. Click Run SQL Tuning Advisor. The SQL Tuning Options page appears showing the SQL statements in the interval. Give your task a name and description, select the scope for the analysis (Comprehensive or Limited), and select a start time for the task. Click OK.
5. Navigate back to the Advisor Central page. The status of Advisor Tasks is listed under this heading in the results section. You must wait until your task status is COMPLETED. You can check the status by clicking your browser’s Refresh button. Then, select your task and click View Result.
6. The SQL Tuning Result page appears. To view recommendations, select the SQL statement and click View Recommendations. The recommendation can include one or more of the following:
Create an index to offer alternate, faster access paths to the query optimizer.
Accept SQL profile, which contains additional SQL statistics specific to the statement that enables the query optimizer to generate a significantly better execution plan.
Gather optimizer statistics on objects with stale or no statistics.
Advice on how to rewrite a query for better performance.

SQL Advisor in Oracle 10g


Another great feature of Oracle 10G that allow you to tune SQL. Now you don’t need to tune SQL statement manually. This new feature
does it for you.

SQL Tuning Advisor using DBMS_SQLTUNE package and very simple to use.

The example below shows how to use SQL advisor.

1. Grant following access to the user that is going to run this new tool. In the example below SCOTT is the owner of the schema.

GRANT ADVISOR TO USER1;
GRANT SELECT_CATALOG_ROLE TO USER1;
GRANT EXECUTE ON DBMS_SQLTUNE TO USER1;
2. Create the tuning task

DECLARE
task_name_var VARCHAR2(30);
sqltext_var CLOB;
BEGIN
sqltext_var := ‘SELECT * from TAB1 where empno = 1200’;
task_name_var := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sqltext_var,
user_name => ‘USER1’,
scope => ‘COMPREHENSIVE’,
time_limit => 60,
task_name => ‘sql_tuning_task_test1’,
description => ‘This is a  tuning task on TAB1 table’);
END;
/
Some time you may have queries that might take longer than the time that you have specified in the “time_limit” parameter. If this is the case then remove this parameter.

NOTE: You can not create more than one task with the same name. If this is the case then drop the existing task or use a different name.

2.1 To view the existing task for the user run the following statement.

select task_name from dba_advisor_log where owner = ‘SCOTT’;
3. Execute the tuning task

Execute dbms_sqltune.Execute_tuning_task (task_name => ‘sql_tuning_task_test1’);
3.1 You can check the status of the task using following query.

select status from dba_advisor_log where task_name=’sql_tuning_task_test1′;

4. Now view the Recommendation

set linesize 100
set long 1000
set longchunksize 1000

SQL> select dbms_sqltune.report_tuning_task(‘sql_tuning_task_test1’) from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘SQL_TUNING_TASK_TEST1’)
—————————————————————————————————-
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : sql_tuning_task_test1
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 06/22/2006 15:33:13
Completed at : 06/22/2006 15:33:14

——————————————————————————-
SQL ID : ad1489724nqpn
SQL Text: SELECT * from TAB1 where VALUE=18902;

——————————————————————————-
FINDINGS SECTION (1 finding)
——————————————————————————-

1- Statistics Finding
———————
Table “USER1”.”TAB1″ was not analyzed.

Recommendation
————–
Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => ‘USER1′, tabname =>’TAB1’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Based on this information, you can decide what actions are necessary to tune the SQL.

11g AUTOMATIC SQL TUNING

As part of Automatic SQL Tuning, Oracle 11g automatically runs the SQL Tuning Advisor against high impact SQL statements during maintenance windows. This process involves the following steps:
AWR statistics are used to compile an ordered list of the SQL statements with the greatest performance impact on the system, where the impact is the sum of the CPU and I/O times for the statement during the past week. The list excludes statements that are inherently less tunable, such as recently (within a month) tuned recursive statements, parallel queries, DML, DDL and SQL statements whose performance problems are caused by concurrency issues.
The SQL tuning advisor is run against each statement in turn. The outcome may include both SQL profiles and other recommendations.
Suggested SQL profiles are performance tested, and those that result in at least a threefold improvement are accepted if the ACCEPT_SQL_PROFILES parameter is set to TRUE, or reported if it is set to FALSE.
The accepted SQL profiles are optionally implemented . Several factors many prevent SQL profiles from being implemented automatically, including stale optimizer statistics of dependent objects. The TYPE column of the DBA_SQL_PROFILES view indicates if SQL profiles are created manually (MANUAL) or automatically (AUTO-TUNE).
The ENABLE and DISABLE procedures of the DBMS_AUTO_TASK_ADMIN package control whether automatic SQL tuning is included in the automated maintenance tasks.
— Enable
BEGIN
  DBMS_AUTO_TASK_ADMIN.enable(
    client_name => ‘sql tuning advisor’,
    operation   => NULL,
    window_name => NULL);
END;
/

— Disable
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => ‘sql tuning advisor’,
    operation   => NULL,
    window_name => NULL);
END;
/
It is also indirectly disabled by setting the STATISTICS_LEVEL parameter to BASIC, as this stops automatic statistics gathering by the AWR.

The SET_TUNING_TASK_PARAMETER procedure of the DBMS_SQLTUNE package controls the behavior of the SQL tuning advisor. The parameters specifically for the automatic runs include:
ACCEPT_SQL_PROFILES – Automatically accept SQL profiles (default FALSE).
MAX_SQL_PROFILES_PER_EXEC – The maximum number of SQL profiles automatically implemented per run (default 20).
MAX_AUTO_SQL_PROFILES – The maximum number of automatic SQL profiles allowed on the system (default 10000).
The current parameter values are displayed using the %_ADVISOR_PARAMETERS views.
COLUMN parameter_value FORMAT A30

SELECT parameter_name, parameter_value
FROM   dba_advisor_parameters
WHERE  task_name = ‘SYS_AUTO_SQL_TUNING_TASK’
AND    parameter_name IN (‘ACCEPT_SQL_PROFILES’,
                          ‘MAX_SQL_PROFILES_PER_EXEC’,
                          ‘MAX_AUTO_SQL_PROFILES’);

PARAMETER_NAME                 PARAMETER_VALUE
—————————— ——————————
ACCEPT_SQL_PROFILES            FALSE
MAX_SQL_PROFILES_PER_EXEC      20
MAX_AUTO_SQL_PROFILES          10000

3 rows selected.

SQL>
The following code shows how the SET_TUNING_TASK_PARAMETER procedure is used to turn on acceptance of automatically generated SQL profiles.
BEGIN
  DBMS_SQLTUNE.set_tuning_task_parameter(
    task_name => ‘SYS_AUTO_SQL_TUNING_TASK’,
    parameter => ‘ACCEPT_SQL_PROFILES’,
    value     => ‘TRUE’);
END;
/
The REPORT_AUTO_TUNING_TASK function of the DBMS_SQLTUNE package returns a CLOB containing a report from the specified automatic tuning task. Setting the BEGIN_EXEC and END_EXEC parameters to NULL produces a report from the most recent execution.
VARIABLE l_report CLOB;
BEGIN
  :l_report := DBMS_SQLTUNE.report_auto_tuning_task(
    begin_exec   => NULL,
    end_exec     => NULL,
    type         => DBMS_SQLTUNE.type_text,     — ‘TEXT’
    level        => DBMS_SQLTUNE.level_typical, — ‘TYPICAL’
    section      => DBMS_SQLTUNE.section_all,   — ‘ALL’
    object_id    => NULL,
    result_limit => NULL);
END;
/

SET LONG 1000000
PRINT :l_report

L_REPORT
——————————————————————————–
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name                        : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                       : SYS
Workload Type                           : Automatic High-Load SQL Workload
Execution Count                         : 31
Current Execution                       : EXEC_1_25
Execution Type                          : TUNE SQL
Scope                                   : COMPREHENSIVE
Global Time Limit(seconds)              : 3600
Per-SQL Time Limit(seconds)             : 1200
Completion Status                       : COMPLETED
Started at                              : 01/16/2008 22:00:06
Completed at                            : 01/16/2008 22:00:46
Number of Candidate SQLs                : 0
Cumulative Elapsed Time of SQL (s)      : 0

——————————————————————————-

How to Change Oracle Database User Password?


Scenarios which require password Change:

1. Its recommended to change passwords periodically for security concerns.

2. User has forgotten/lost the password and unable to login to the system.

3. User wants to change password for any operational Purpose

Oracle database allows user to change database user’s password using the below queries

+Logging in as the user

SQL> ALTER USER ME IDENTIFIED BY <NEW_PASSWORD>;

+Logging in as SYS/SYSTEM User

SQL> ALTER USER <USERNAME> IDENTIFIED BY <PASSWORD>;

There can be some cases when the DBA need to change password and revert back to older value after the dba action is completed.
Below test case helps in understanding how to change db user password and restore to older password value in Oracle 10g.

1. Change oracle user password using alter command

 SQL> conn system/manager
 Connected. 

SQL>  alter user TESTUSER identified by password1 
 User altered. 

2. Test the password is working


SQL> conn TESTUSER/password1;
 Connected.

3.Retreive the encrypted password for user

SQL> conn system/manager; 
Connected.


SQL> alter user TESTUSER identified by values ‘6057000499B128C3’; 
User altered. 


select username, password from dba_users where username = ‘TESTUSER’; 
  
USERNAME                       PASSWORD 
—————————— —————————— 
TESTUSER                            DB78866145D4E1C3 

4. Change the value of TESTUSER password to a new value

SQL> conn system/manager
Connected. 
SQL>  
SQL>  alter user TESTUSER identified by password2; 

User altered. 

 5. Verify the new password is working

SQL> conn TESTUSER/password2; 
Connected. 

6. Restore the older password using the encrypted password which we retreived from dba_users;
SQL> conn system/manager@dev; 
Connected. 
SQL> alter user TESTUSER identified by values ‘DB78866145D4E1C3’; 

User altered. 

7. Verify that you are able to connect using old password.

SQL> conn TESTUSER/password2@dev;


ORA-1017 invalid username/password ;logon denied


SQL> conn TESTUSER/password1@dev;
Connected.