SYSAUX tablespace is growing like anything

SYSAUX tablespace is growing like anything
##################################

AWR is identified to be consuming more space.

SQL> select * from v$sysaux_occupants ;

SQL> SELECT occupant_name, occupant_desc, space_usage_kbytes FROM v$sysaux_occupants ORDER BY space_usage_kbytes DESC ;


OCCUPANT_NAME                   OCCUPANT_DESC                                          SPACE_USAGE_KBYTES

SM/AWR                  Server Manageability - Automatic Workload Repository               19906624
SM/OPTSTAT              Server Manageability - Optimizer Statistics History                  191808
EM                      Enterprise Manager Repository                                         59648


Solution
#########

1.) See the snapshots interval and retention period time

SQL> select * from DBA_HIST_WR_CONTROL;

Check whether the retention period is set too high. In such case we need to modify  the retention period.

If it is set to high value, then consider reducing the retention period to avoid growing of space.

By default, it is set to 7 days.

The new retention time is specified in minutes.

SQL> execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 4320);

Here 4320 is 3*24*60 ( Convert the days to minutes). For 3 days, it 4320 minutes.



2). Next thing is to check the amount of snapshots have been generated and available.


If this is too high, then try to drop the unwanted snapshots to reclaim the space.


SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id,high_snap_id);

low_snap_id : The low snapshot id of snapshots to drop.

high_snap_id : The high snapshot id of snapshots to drop.



3) Then check whether the statistics_level parameter is set to typical.


SQL> show parameter statistics


NAME                                         TYPE                        VALUE

statistics_level string ALL   ----->  Should be set TYPICAL


If it is set to ALL, please change it to TYPICAL. Because statistics_level=ALL will gather lot of additional information in AWR repository which would consume more space. Most of the cases, if the statistics_level is set to TYPICAL then the growth would be stopped.

SQL> alter system set statistics_level=typical;

What is Split Brain and Node Eviction in RAC ???

What is Voting Disk ??


Oracle Clusterware uses the voting disk to determine which instances are members of a cluster. The voting disk must reside on a shared disk. Basically all nodes in the RAC cluster register their heart-beat information on thes voting disks. The number decides the number of active nodes in the RAC cluster. These are also used for checking the availability of instances in RAC and remove the unavailable nodes out of the cluster. It helps in preventing split-brain condition and keeps database information intact. The split brain syndrome and its affects and how it has been managed in oracle is mentioned below.
For high availability, Oracle recommends that you have a minimum of three voting disks. If you configure a single voting disk, then you should use external mirroring to provide redundancy. You can have up to 32 voting disks in your cluster. What I could understand about the odd value of the number of voting disks is that a noe should see maximun number of voting disk to continue to function, so with 2, if it can see only 1, its not the maximum value but a half value of voting disk. I am still trying to search more on this concept.

Split Brain  in RAC


In a Oracle RAC environment all the instances/servers communicate with each other using high-speed interconnects on the private network. This private network interface or interconnect are redundant and are only used for inter-instance oracle data block transfers. Now talking about split-brain concept with respect to oracle rac systems, it occurs when the instance members in a RAC fail to ping/connect to each other via this private interconnect, but the servers are all pysically up and running and the database instance on each of these servers is also running. These individual nodes are running fine and can conceptually accept user connections and work independently. So basically due to lack of commincation the instance thinks that the other instance that it is not able to connect is down and it needs to do something about the situation. The problem is if we leave these instance running, the sane block might get read, updated in these individual instances and there would be data integrity issue, as the blocks changed in one instance, will not be locked and could be over-written by another instance. Oracle has efficiently implemented check for the split brain syndrome.


Node Eviction in RAC


In RAC if any node becomes inactive, or if other nodes are unable to ping/connect to a node in the RAC, then the node which first detects that one of the node is not accessible, it will evict that node from the RAC group. e.g. there are 4 nodes in a rac instance, and node 3 becomes unavailable, and node 1 tries to connect to node 3 and finds it not responding, then node 1 will evict node 3 out of the RAC groups and will leave only Node1, Node2 & Node4 in the RAC group to continue functioning.
The split brain concepts can become more complicated in large RAC setups. For example there are 10 RAC nodes in a cluster. And say 4 nodes are not able to communicate with the other 6. So there are 2 groups formed in this 10 node RAC cluster ( one group of 4 nodes and other of 6 nodes). Now the nodes will quickly try to affirm their membership by locking controlfile, then the node that lock the controlfile will try to check the votes of the other nodes. The group with the most number of active nodes gets the preference and the others are evicted. Moreover, I have seen this node eviction issue with only 1 node getting evicted and the rest function fine, so I cannot really testify that if thats how it work by experience, but this is the theory behind it.

When we see that the node is evicted, usually oracle rac will reboot that node and try to do a cluster reconfiguration to include back the evicted node.

You will see oracle error: ORA-29740, when there is a node eviction in RAC. There are many reasons for a node eviction like heart beat not received by the controlfile, unable to communicate with the clusterware etc.

Using find Command in ASM

Purpose

Displays the absolute paths of all occurrences of the specified name pattern (with wildcards) in a specified directory and its subdirectories.

Syntax and Description

find [-t type] dir_name name_pattern

This command searches the specified directory and all subdirectories under it in the directory tree for the supplied name_pattern. The value that you use forname_pattern can be a directory name or a filename, and can include wildcard characters. See "Wildcard Characters".

In the output of the command, directory names are suffixed with the slash character (/) to distinguish them from filenames.

You use the -t flag to find all the files of a particular type (specified as type). For example, you can search for control files by specifying type asCONTROLFILE. Valid values for type are the following:

CONTROLFILE

DATAFILE

ONLINELOG

ARCHIVELOG

TEMPFILE

BACKUPSET

DATAFILE

PARAMETERFILE

DATAGUARDCONFIG

FLASHBACK

CHANGETRACKING

DUMPSET

AUTOBACKUP

XTRANSPORT

These are the type values from the type column of the V$ASM_FILE view.

Examples

The following example searches the dgroup1 disk group for files that begin with 'UNDO'.

ASMCMD> find +dgroup1 undo*

+dgroup1/sample/DATAFILE/UNDOTBS1.258.555341963

+dgroup1/sample/DATAFILE/UNDOTBS1.272.557429239

The following example returns the absolute path of all the control files in the +dgroup1/sample directory.

ASMCMD> find -t CONTROLFILE +dgroup1/sample *

+dgroup1/sample/CONTROLFILE/Current.260.555342185

+dgroup1/sample/CONTROLFILE/Current.261.555342183

FRM-92101: There was a Failure in the Forms Server During Startup

Symptoms
Intermittently E-Business suite forms sessions are failing with the error

Error

FRM-92101 : There was a failure in the Forms Server during startup. This could happen due to invalid configuration.

Steps to Reproduce

The issue can be reproduced at will with the following steps:

1:- Log into the E-Business suite.
2:- Choose a responsibility and click a function which loads forms (any form)

Business Impact

The issue has the following business impact:
Due to this issue, forms sessions are being disconnected for up to 100 users.
Changes
Implemented a shared application tier file system (shared APPL_TOP)
Cause
The OPMN process pings the HTTP server process to check that it is still alive. If OPMN does not get a response from the HTTP server in the timeframe configured by the E-Business suite, it presumes that the HTTP server is down and restarts it, affecting all users connected to that application server.

In the $LOG_HOME/ora/10.1.3/opmn/opmn.log we can see that OPMN is restarting the HTTP server because it has become unresponsive.

10/02/16 12:08:22 [libopmnohs] Process Ping Failed:
HTTP_Server~HTTP_Server~HTTP_Server~1 (241450421:15863) [The connection
receive timed out]
10/02/16 12:09:13 [libopmnohs] Process Ping Failed:
HTTP_Server~HTTP_Server~HTTP_Server~1 (241450421:15863) [The connection
receive timed out]
10/02/16 12:09:13 [libopmnohs] Process Unreachable:
HTTP_Server~HTTP_Server~HTTP_Server~1 (241450421:15863)
10/02/16 12:09:15 [pm-process] Stopping Process:
HTTP_Server~HTTP_Server~HTTP_Server~1 (241450421:15863)
10/02/16 12:09:23 [pm-process] Starting Process:
HTTP_Server~HTTP_Server~HTTP_Server~1 (241450421:0)

The reason that OPMN is restarting the HTTP server is that a shared APPL_TOP is being used, but the recommended settings for the Network-attached storage (NAS) file system have not been implemented.

As the NAS mount gets busier, the HTTP server process becomes swamped and does not respond to OPMN. OPMN then restarts the process, causing all forms users connected to that Application server to fail with the FRM-92100 error.

Self service session users are much less likely to notice the issue as most self service application screens are stateless HTML, so no context is lost when the HTTP server is bounced.
Solution
To implement the solution, please execute the following steps:

1:- Stop ALL middle tier services
2:- Update the $CONTEXT_FILE and change "s_lock_pid_dir" to a directory on a local disk.

For example

/d01

3:- Run autoconfig

4:- Ensure that the NAS device is configured with the correct mount options.

On Linux, it should be using "nolock" option.
On Solaris, it should be using "llock" option.

5:- Start ALL middle tier services
6:- Retest the issue.
7:- Migrate the solution as appropriate to other environments.


References

BUG:5407140 - [LIBOPMNOHS] PROCESS PING FAILED: HTTP_SERVER~HTTP_SERVER~HTTP_SERVER~1
BUG:9279182 - USER SESSION CRASHES WITH FRM-92102 AND SEGMENTATION FAULT
NOTE:384248.1 - Sharing The Application Tier File System in Oracle E-Business Suite Release 12
NOTE:802704.1 - Poor Performance of APPS 11i or 12 When Using Shared APPL_TOP with NAS

Oracle RAC Training with ASM

For Oracle RAC Training with ASM Check the following URL:

http://www.youtube.com/watch?v=y9Wgo0XpuUs&feature=colike

Oracle RAC clusterware startup sequence (11gR2) - 2

ohasd = Oracle High Availability Services Daemon  

Link to Oracle Clusterware components

Here is the Oracle 11gR2 clusterware startup sequence:

ohasd -> orarootagent -> ora.cssdmonitor : Monitors CSSD and node health (along with the cssdagent). Try to restart the node if the node is unhealthy.

-> ora.ctssd : Cluster Time Synchronization Services Daemon ->

                                                    ora.crsd   -> oraagent  -> ora.LISTENER.lsnr

                                                                             -> ora.LISTENER_SCAN.lsnr

                                                                             -> ora.ons

                                                                             -> ora.eons

                                                                             -> ora.asm

                                                                             -> ora.DB.db

                                                         ->orarootagent -> ora.nodename.vip

                                                                                 -> ora.net1.network

                                                                                 -> ora.gns.vip

                                                                                 -> ora.gnsd

                                                                                 -> ora.SCANn.vip

         -> cssdagent -> ora.cssd : Cluster Synchronization Services

 -> oraagent -> ora.mdnsd : Used for DNS lookup
                            -> ora.evmd

                            -> ora.asmd

                            -> ora.gpnpd : Grid Plug and Play = adding a node to the cluster is easier (we need less configuration for the new node)

If a resource is written using blue & bold font => resource owned by root. The other resources are owner by oracle. (all this on UNIX environment)

When a resource is managed by root, we need to run the command crsctl as root or oracle.

Clusterware Resource Status Check

$ crsctl status resource -t
 

--------------------------------------------------------------------------------
NAME                  TARGET  STATE      SERVER            STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr    

                      ONLINE  ONLINE    rac1.nitish.com
                      ONLINE  ONLINE    rac2.nitish.com
ora.asm              

                      OFFLINE OFFLINE   rac1.nitish.com
                      OFFLINE OFFLINE   rac2.nitish.com
ora.eons             

                      ONLINE  ONLINE    rac1.nitish.com
                      ONLINE  ONLINE    rac2.nitish.com
ora.gsd
                      OFFLINE OFFLINE   rac1.nitish.com
                      OFFLINE OFFLINE   rac2.nitish.com
ora.net1.network
                      ONLINE  ONLINE    rac1.nitish.com
                      ONLINE  ONLINE    rac2.nitish.com
ora.ons
                      ONLINE  ONLINE    rac1.nitish.com
                      ONLINE  ONLINE    rac2.nitish.com
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1                     ONLINE  ONLINE    rac1.nitish.com
ora.LISTENER_SCAN2.lsnr
1                     ONLINE  ONLINE    rac2.nitish.com
ora.LISTENER_SCAN3.lsnr
1                     ONLINE  ONLINE    rac2.nitish.com
ora.oc4j
1                     OFFLINE OFFLINE
ora.scan1.vip
1                     ONLINE  ONLINE    rac1.nitish.com
ora.scan2.vip
1                     ONLINE  ONLINE    rac2.nitish.com
ora.scan3.vip
1                     ONLINE  ONLINE    rac2.nitish.com
ora.NITI.db
1                     ONLINE  ONLINE    rac1.nitish.com         Open
2                     ONLINE  ONLINE    rac2.nitish.com
ora.rac1.nitish.com.vip
1                     ONLINE  ONLINE    rac1.nitish.com
ora.rac2.nitish.com.vip
1                     ONLINE  ONLINE    rac2.nitish.com
 

crsctl start has    -> start all the clusterware services/ resources (including the database server and the listener);

crsctl stop has    -> stop all the clusterware services/ resources (including the database server and the listener);

crsctl check has -> to check if ohasd is running/ stopped

crsctl check has
CRS-4638: Oracle High Availability Services is online
>> the ohasd daemon is running => the clusterware is (must be) up and running (if no error occur).

crsctl check has
CRS-4639: Could not contact Oracle High Availability Services

>> the ohasd daemon is NOT running => the clusterware is DOWN (stopped).

crsctl enable has  -> enable Oracle High Availability Services autostart

crsctl disable has -> disable Oracle High Availability Services autostart

crsctl config has -> check if Oracle High Availability Services autostart is enabled/ disabled.

crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
 

Cloning Oracle Application 11i /R12 with Rapid Clone using Hot Backup

You need to follow the "pre-requisite" and "preparation" steps from section 1 and 2 of the cloning note applicable to your EBS Version:

Release 11i  : Note 230672.1 - 'Cloning Oracle Applications Release 11i with Rapid Clone'
Release 12  : Note 406982.1 - 'Cloning Oracle Applications Release 12 with Rapid Clone'


This Note helps you to implement the steps mentioned in "Appendix B: Recreating database control files manually in Rapid Clone" as per above Clone Note.

Step 1: Ensure adpreclone.pl has been run

Step 2: Obtain a trace file script to recreate the controlfile. On the source database issue the following
            command:

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;


The trace file script will be put into the user_dump_dest directory. The name of the trace file script will be something like PRODUCTION_ora_99999.trc.
Compare the date and time of the new trace file script to the time in which you entered the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command.
This will ensure you will be using the most recent trace file script, the one you created in this step


Step 3: In the create controlfile script just created in step 2 change:
CREATE CONTROLFILE REUSE DATABASE "PRODUCTION" NORESETLOGS

to:

CREATE CONTROLFILE DATABASE "PRODUCTION" RESETLOGS ARCHIVELOG
If you want to change the Database Name , You need to use the clause SET DATABASEin the create control file script as follows:
CREATE CONTROLFILE SET DATABASE "newdbname" RESETLOGS NOARCHIVELOG
You must specify RESETLOGS.

The ARCHIVELOG mode may be changed to NOARCHIVELOG if you wish to run the copied database in noarchive log mode. Change all directories in the create controlfile clause to point to the correct directories for the new target database, if necessary.

Leave "only" the CREATE CONTROLFILE clause. The other statements, like the recover command, will be done manually. Be sure you also remove the STARTUP NOMOUNT command.
Note:  Please ensure that there is no new datafile/tablespace added to Database after you generate controlfile script as above

Step 4: On the source database make an online copy of all datafiles using:
SQL> ALTER TABLESPACE BEGIN BACKUP;

Copy all datafiles within tablespace to the new directory. 
On Unix systems, this can be done with the cp command.

Then do:

SQL> ALTER TABLESPACE END BACKUP;

Do NOT copy the controlfiles and redo log files as they will be recreated. You must copy the datafiles only after the ALTER .. BEGIN BACKUP command has been executed, otherwise the datafiles may be corrupted.

The names of the datafiles and tablespaces to which the datafiles belong can be obtained using the following command:

SQL> SELECT FILE_NAME, TABLESPACE_NAME FROM DBA_DATA_FILES;

Note: Instead of performing step 4 and step 5 to create a new online backup, you may instead use a previously taken online backup of your database.If you choose to use a previous online backup be sure to copy the required archived redo logs taken with the the previous online backup.

Step 5: After all datafiles have been copied and the tablespaces taken out of backup mode issue the following command:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

You will need all of the archivelog files from the start of datafile copy commands including the one just created with the command ALTER SYSTEM ARCHIVE LOG CURRENT.

Step 6: Copy the database (DBF) files,controlfile script and archive log files from the source to the target
           system

Step 7: So, As mentioned in 230672.1 (Appendix B), Replace section 2.2a (Configure the target system database server) with the following steps:

Execute the following commands to configure the target system. You will be prompted for the target system specific values (SID, Paths, Ports, etc)

· Log on to the target system as the ORACLE user
· Configure the 
cd /appsutil/clone/bin
perl adcfgclone.pl dbTechStack

Step 7: On the target system issue STARTUP NOMOUNT command. For example:
     
SQL> startup nomount pfile=initTESTING.ora


Step 8: Run the prepared script created in step 3 to create the new controlfile. For example:

SQL> @PRODUCTION_ora_99999.trc

Step 9: Issue the command:
          
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE


You will be prompted to apply all of the archived redo logs from the source database including
the last archive redo log file created with the ALTER DATABASE ARCHIVE LOG CURRENT 
command from step 5.

After applying all of these archive log files issue the CANCEL command.

Step 10: Open the database with resetlogs:
         
SQL>ALTER DATABASE OPEN RESETLOGS

 At this point the target database will have been successfully cloned and opened.

Step 11:  Create Temporary Tablespace if not created in Source, else you shall add Temporary tablespace

You can check if tablespace TEMP has tempfiles or datafiles using the following SQL:

SQL> SELECT FILE_NAME,TABLESPACE_NAME,STATUS,AUTOEXTENSIBLE from DBA_TEMP_FILES where TABLESPACE_NAME like 'TEMP';

SQL> SELECT FILE_NAME,TABLESPACE_NAME, STATUS,AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'TEMP';

If Temporary Tablespace is not created from above query,
SQL> create temporary tablespace temp add tempfile 'xxxx.dbf' size xx

Or

SQL> alter tablespace TEMP add tempfile  'xxxx.dbf' size xx


Step 12:  Run the library update script against the database

· cd /appsutil/install/
· sqlplus "/ as sysdba" @adupdlib.sql 
      where is "sl" for HP-UX, "so" for any other UNIX platform and 
      not required for Windows.

Step 13: Configure the target database (the database must be open)

· cd /appsutil/clone/bin
· perl adcfgclone.pl dbconfig 
     where target context file is:
     /appsutil/.xml 


Finally, refer back to cloning notes and the following sections:

· Copy the Application Tier File System
· Configure the Target System Application Tier Server Nodes
· Finishing Tasks
Release 11i : Note 230672.1 - 'Cloning Oracle Applications Release 11i with Rapid Clone'
Release 12 : Note 406982.1 - 'Cloning Oracle Applications Release 12 with Rapid Clone'

Could not validate ASMSNMP password due to following error- "ORA-01031: insufficient privileges".

  Recently i have come across this error. If you get this error when configuring RAC database.

           Please do the following:

          As for as 11GR2 is concern, ASM is installed along with the GRID.

          Please login as user grid

         [grid]$ orapwd file=/u01/app/11.2.0/grid/dbs/orapwasm password=asm


         Using SCP copy ASM password file to rac2

         [grid]$ scp orapwasm rac2:/u01/app/11.2.0/grid/dbs/

       
        Once SCP is done 

        Login in to ASM database as 

        [grid]sqlplus / as sysasm

        SQL>create user asmsnmp identified by asm;

        SQL> grant sysdba to asmsnmp;

       The reason for creating above user is to collect the statistics for ASM.