ASM and Database does not auto start in Oracle restart environment (After Server start)

 If you are supporting Any Oracle restart environment and if you notice that your ASM instance and Database instance does not start automatically during server start/ reboot, follow the below steps (As suggested in the Oracle Support Documentation).

1. Check the status of HAS (High-Availability Services) daemon.

[oracle@linux1 ohasd]$ ps -ef | grep d.bin

oracle    3889     1  0 17:59 ?        00:00:17 /u01/app/oracle/product/11.2.0/grid/bin/ohasd.bin reboot

oracle    4679  4173  0 18:31 pts/1    00:00:00 grep d.bin

[oracle@linux1 ohasd]$ crsctl check has

CRS-4638: Oracle High Availability Services is online

[oracle@linux1 ohasd]$

You notice that only HASD process is running. CSSD process is missing. (Since this is not a RAC database, CRSD is not supposed to run anyway).

2. Next, check the resource status

[oracle@linux1 ohasd]$ crsctl stat res -t

--------------------------------------------------------------------------------

NAME           TARGET  STATE        SERVER                   STATE_DETAILS

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

ora.ARCH.dg

               OFFLINE OFFLINE      linux1

ora.DATA.dg

               OFFLINE OFFLINE      linux1

ora.LISTENER.lsnr

               OFFLINE OFFLINE      linux1

ora.asm

               OFFLINE OFFLINE      linux1

--------------------------------------------------------------------------------

Cluster Resources

--------------------------------------------------------------------------------

ora.cssd

      1        OFFLINE OFFLINE

ora.diskmon

      1        OFFLINE OFFLINE

ora.oradb1.db

      1        OFFLINE OFFLINE

[oracle@linux1 ohasd]$

 As you can see, ora.cssd process is offline. It did not start automatically.

3. Let's check the auto start setting for CSSD.

[oracle@linux1 ohasd]$ crsctl stat res ora.cssd -p -init

NAME=ora.cssd

TYPE=ora.cssd.type

ACL=owner:oracle:rwx,pgrp:oinstall:rwx,other::r--

ACTION_FAILURE_TEMPLATE=

ACTION_SCRIPT=

ACTIVE_PLACEMENT=0

AGENT_FILENAME=%CRS_HOME%/bin/cssdagent%CRS_EXE_SUFFIX%

AGENT_HB_INTERVAL=0

AGENT_HB_MISCOUNT=10

AUTO_START=never

CARDINALITY=1

AUTO_START is set to "never". It should be set to "always" for auto start to work.

4. Let's check the ASM auto start dependency on CSSD process

[oracle@linux1 ohasd]$ crsctl stat res ora.asm -p -init | grep -i dependencies

START_DEPENDENCIES=hard(ora.cssd) weak(ora.LISTENER.lsnr)

STOP_DEPENDENCIES=hard(ora.cssd)

[oracle@linux1 ohasd]$

As you can see, there is a hard dependency on CSSD process for auto start to work.

5. Next, change the auto start setting of cssd process to "always"

From root user:

(* Make sure to set the GRID environement correctly such as, Oracle_Home to Grid home directories and grid_home/bin directories to the OS PATH variable)

[root@linux1 ~]# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid

[root@linux1 ~]# export ORACLE_SID=+ASM

[root@linux1 ~]# export PATH=$PATH:$ORACLE_HOME/bin

[root@linux1 ~]# crsctl modify resource ora.cssd -attr "AUTO_START=always" -init

[root@linux1 ~]# crsctl stat res ora.cssd -p -init | grep -i start

AUTO_START=always

NOT_RESTARTING_TEMPLATE=

RESTART_ATTEMPTS=5

START_ARGS=

START_COMMAND=

START_DEPENDENCIES=weak(concurrent:ora.diskmon)

START_TIMEOUT=600

[root@linux1 ~]#

6. stop/ start HAS service or reboot the server and verify that the CSSD, ASM, Listener and databases are starting up automatically.

EM 12c, EM 13c: How to Update DBSNMP Password and Change Monitoring Credentials of an Enterprise Manager Cloud Control Target Database Using emcli Command Line (Doc ID 1592390.1)

EM 12c, EM 13c: How to Update DBSNMP Password and Change Monitoring Credentials of an Enterprise Manager Cloud Control Target Database Using emcli Command Line (Doc ID 1592390.1)

The DBSNMP password for one of my target dataase was in Expired status (But in Grace Period).

Use the following Steps to Change the DBSNMP Password on the Target Database and on the OMS Repository.
You will run all the commands from the OMS Server.

1. Use emcli command to login to the OMS

emcli login -username=sysman -password=<sysman_password>

2. Run the below command to get the target name for your database

./emcli get_targets -target="oracle_database"

3. Run the below command to change the DBSNMP password both on the OMS repository and the target database

emcli update_db_password -target_name="<target_name>" -target_type="oracle_database" -user_name="dbsnmp" -change_at_target=yes -old_password="<old_password>" -new_password="<new_password>" -retype_new_password="<new_password>"

=> Replace each value in <> in commandline with the appropriate values.

-- Check the password on the target database

How to copy a specific Execution Plan for any SQL ID from One Server to Another Database

Lt us say, you have a SQL, which is using a better plan on one environment and not-so-good plan in another environment. And you want to copy the better plan to the destination database and force the optimizer to use this plan.

[On Source DB]
1. Get the SQL ID, Plan Hash Value of the good Plan. You can use any of the following SQL.

SQL> select sql_id, plan_hash_value from v$sqlarea 
     WHERE sql_id IN ('fsy3ubymdz500');

SQL> select sql_id, plan_hash_value from v$sql 
     WHERE sql_id IN ('fsy3ubymdz500');

2. Create a SQL Tuning Set for the above SQL Id and Plan Hash Value. This Tuning Set will be copied to the Destination database using a staging table and the datapump utility.

Step 1 : Create an Empty SQL Tuning Set

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET (
sqlset_name => 'fsy3ubymdz500_STS'
, description => 'fsy3ubymdz500 STS to copy to Prod - better plan'
);
END;
/

Step 2 : Load the SQL Information for the SQL ID into this Tuning Set

DECLARE
 c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN c_sqlarea_cursor FOR
  SELECT VALUE(p)
    FROM TABLE(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id = ''fsy3ubymdz500'' AND plan_hash_value = 367652949 ')) p;

  -- load the tuning set
  DBMS_SQLTUNE.LOAD_SQLSET (sqlset_name => 'fsy3ubymdz500_STS', populate_cursor => c_sqlarea_cursor);
END;
/

** You can display the content of SQL Tuning Set using :
SELECT NAME, STATEMENT_COUNT, DESCRIPTION FROM DBA_SQLSET;

COLUMN SQL_TEXT FORMAT a30
COLUMN SCH FORMAT a3
COLUMN ELAPSED FORMAT 999999999

SELECT SQL_ID, PARSING_SCHEMA_NAME AS "SCH", SQL_TEXT,
ELAPSED_TIME AS "ELAPSED", BUFFER_GETS
FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'fsy3ubymdz500_STS' ) );

SELECT * FROM TABLE( DBMS_SQLTUNE.SELECT_SQLSET( 'fsy3ubymdz500_STS' ) );


2. Create a Staging table to hold the exported SQL Tuning set

--> Table name and other parameters are case-sensitive  <--
BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (table_name => 'FSY3UBYMDZ500_STS_TBL', schema_name => 'SYSTEM');
END;
/

2. Load the SQL Tuning Set information to the Staging Table. (See Above on how to create and populate a SQL Tuning Set)

BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
sqlset_name => 'fsy3ubymdz500_STS'
, sqlset_owner => 'SYS'
, staging_table_name => 'FSY3UBYMDZ500_STS_TBL'
, staging_schema_owner => 'SYSTEM'
);
END;
/

3. Export the table using datapump utility

expdp system DIRECTORY=data_pump_dir DUMPFILE=fsy3ubymdz500_STS.dmp TABLES=FSY3UBYMDZ500_STS_TBL

4. Transfer the dump file to the destination server. (Using scp or ftp command)

[On Target Server]
5. Import into The destination Database

impdp system DIRECTORY=data_pump_dir DUMPFILE=fsy3ubymdz500_STS.dmp TABLES=fsy3ubymdz500_STS_TBL

6. Unpack The SQL Tuning set from the staging table to the destination server

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
sqlset_name => '%'
, sqlset_owner => 'SYS'
, replace => true
, staging_table_name => 'FSY3UBYMDZ500_STS_TBL'
, staging_schema_owner => 'SYSTEM');
END;
/

7. Load the plan from SQL Tuning Set to SQL Plan Baseline.

VARIABLE v_plan_cnt NUMBER
EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( -
sqlset_name => 'fsy3ubymdz500_STS', -
sqlset_owner => 'SYS', -
basic_filter => 'sql_id = ''fsy3ubymdz500'' AND plan_hash_value = 367652949' );


8. Flush the two SQLs from the shared pool, so that the optimizer will pick the new plan.

select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||', '||HASH_VALUE||''', ''C'');'
from V$SQLAREA where SQL_ID in ('fsy3ubymdz500');

exec DBMS_SHARED_POOL.PURGE ('0000001006B396C8, 2113289046', 'C');

Disable Database Auto Task

1. Check the status of your auto task jobs before disabling it.

As you can notice, sql tuning advisor task is already disabled, but the remaining two tasks are enabled. Also, the overall task status is enabled (From DBA_AUTOTASK_STATUS view).

Let us now, disable the two individual task:

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO OPTIMIZER STATS COLLECTION',NULL,NULL);

EXEC DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO SPACE ADVISOR',NULL,NULL);

Now, check the task status again:

As you can see, the indivdual status and window client is disabled. But, the Auto Task in itself is enabled. (DBA_AUTOTASK_STATUS)
At this stage, the individual task will not run, even if the auto task status is enabled.
Also, note that the status on DBA_AUTOTASK_TASK is still Enabled. This status has no impact on the actual run of the task. So, you can ignore this status column.

Next, we will disable the auto task check the status again:

The status on DBA_AUTOTASK_STATUS is now shows "DISABLED".

So, you can either disable a single auto task or all the auto task together. As per Oracle Documentation:

"
The best way to understand how this works is this - think of an electrical panel supplying current to a house.

There are a number of circuit breakers on the panel and the main switch which turns off all power to the house. Each individual circuit breaker can be in an ON or OFF position, however if the main switch is OFF, then no power is reaching the circuit breakers, regardless of their position. Individual circuit breakers are controlled by DBMS_AUTO_TASK_ADMIN.DISABLE('AUTO OPTIMIZER STATS COLLECTION', NULL, NULL), etc.

The main switch is controlled by DBMS_AUTO_TASK_ADMIN.DISABLE().

DBA_AUTOTASK_CLIENTS view simply displays the switch position for each client (ENABLED or DISABLED). DBA_AUTOTASK_WINDOW_CLIENTS view is designed to give an idea as to when the jobs for the specific client may run. This view has a separate column for the"main switch", AUTOTASK_STATUS, in addition to the client-specific switches. This view also takes into account status of DBMS_SCHEDULER objects (window groups, individual windows, etc), providing the most realistic picture of when maintenance jobs can run.
"

ORA-27102: out of memory Error

Doc ID 301830.1

Sometime, you may get the below error, when you try to start the Oracle database:

SQL> startup nomount
ORA-27102: out of memory
Linux-x86_64 Error: 28: No space left on device

One of the reason is, shmall kerne parameter is set to a low value on /etc/sysctl.conf or /proc/sys/kernel/shmall file.

SHMALL is the total amount of shared memory, in pages, that the system can use at one time.
This value is set to 50% of the total physical memory available.

So, your total SGA allocated from all the database, hosted on a server, cannot be greater than the SHMALL value.

How to calculate the Memory limit from SHMALL parameter:

shmall parameter value is set in Page size.
So,
Memory in GB = (shmall * Page size in Bytes)/(1024*1024*1024)


run the below command to get the page size in Bytes : $ getconf PAGE_SIZE

To change the value of shmall, edit the /etc/sysctl.conf file as root and change the value of shmall.
Then, run the below command as root user to make the change to shamall value parmanent, without the need to to reboot the server. (Alternatively, you can reboot the server to make this change permanent).

# sysctl -p

Modifying /etc/sysctl.conf is a permanent workaround (applies at boot time). If for some reason you DO NOT want to change the system wide configuration, you can do it on the fly by directly changing the kernel pseudo FS AKA procfs.


e.g. # echo "4194304" > /proc/sys/kernel/shmall

How to Change the location of Database Directories

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u04/datapump/ORCL';


Specify OR REPLACE to re-create the directory database object if it already exists. You can use this clause to change the definition of an existing directory without dropping, re-creating, and regranting database object privileges previously granted on the directory.

Users who had previously been granted privileges on a redefined directory can still access the directory without being regranted the privileges.

How to Enable Active Data Guard Option

Active Data Guard

If you want to run the Redo Apply Process and at the same time, open the standby database in read-only mode, you will have to go for Active Data Guard option. This is not possible in a normal Data Guard database (Physical Standby). Remember that, Active Data Guard needs additional licenses and additional cost. So, please check with your Oracle representative before going for this option.

Steps to Enable Active Data Guard Option

1. Cancel The Redo Apply Process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Shutdown and start the database in Mount Mode
SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

3. Open the database in Read-Only Mode
(** If you do not want use the Active Data Guard option, then you do not have to execute the next Step. However, remember that, the redo apply will be disabled and real-time query can not be used)

SQL> ALTER DATABASE OPEN;

4. Start the Redo Apply. (Executing this Step will enable the Active Data Guard Option)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

5. Now that the standby is in real-time query mode (the standby is open in read-only
mode and Redo Apply is active), the V$DATABASE.OPEN_MODE column changes to
indicate the following:

SQL> SELECT open_mode FROM V$DATABASE;

OPEN_MODE
--------------------

READ ONLY WITH APPLY