How To Manually Install OLS , DV In PDB In Multitenant Enviornment (Doc ID 2362597.1)

Scenario: If the target database is migrated from rman backup, then dbvault  is not installed.
In this case the following steps should be followed.

1. As user who has been granted the SYSDBA administrative privilege, log in to the PDB in which you want to install Oracle Database Vault.

For example, to log in to a PDB named hr_pdb:

sqlplus sec_admin@hr_pdb as sysdba
Enter password: password

Note: To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the 'show con_name' command.

2. If necessary, Query DBA_DV_STATUS, CDB_DV_STATUS, DBA_OLS_STATUS, and CDB_OLS_STATUS data dictionary views to verify if Oracle Database Vault and Oracle Label Security are already configured and enabled in this PDB. If not, proceed to next step.


3. Install Oracle Label Security by executing the catols.sql script.

@$ORACLE_HOME/rdbms/admin/catols.sql

Oracle Label Security must be installed before you can use Oracle Database Vault.


4. Install Oracle Database Vault by executing the catmac.sql script.

@$ORACLE_HOME/rdbms/admin/catmac.sql

At the Enter value for 1 prompt, enter the default tablespace for the PDB.

At the Enter value for 2 prompt, enter the temporary tablespace for the PDB.

 5. After the installation is complete, you can register Oracle Database Vault.

Database Vault 12cR2 installation and configuration

Starting with Oracle Database 12c, Oracle Database Vault is installed by default but not enabled. Customers can enable it using DBCA or from the command line using SQL*Plus.

This post covers the following topics:

1. DB Vault Enable

2. Realm Creation

3.  DB vault testing

4.  Secure Applicatin Role Creation

# Step_01: Checking the DB Vault service is enabled or not.
---------------------------------------------------------
SQL> SELECT parameter, VALUE FROM v$option WHERE parameter = 'Oracle Database Vault';

PARAMETER                                                        VALUE
---------------------------------------------------------------- -----------------------------
Oracle DATABASE Vault                                            FALSE

----------------------------------------------------------------
# Step_02: Create user dbv_owner and dbv_acctmgr if doesn't exist
----------------------------------------------------------------

SQL> CREATE USER dbv_owner IDENTIFIED BY dbv_owner ;

USER created.

SQL> CREATE USER dbv_acctmgr IDENTIFIED BY dbv_acctmgr;

USER created.

----------------------------------------------------------------------------------------------
# Step_03: Configure DBVAULT for those users
----------------------------------------------------------------------------------------------

SQL> EXEC DVSYS.CONFIGURE_DV (dvowner_uname => 'dbv_owner', dvacctmgr_uname => 'dbv_acctmgr');

-----------------------------------------------------------------------------------------------
# Step_4: Enable DBVAULT
-----------------------------------------------------------------------------------------------

SQL> EXEC DVSYS.CONFIGURE_DV (dvowner_uname => 'dbv_owner', dvacctmgr_uname => 'dbv_acctmgr');

PL/SQL PROCEDURE successfully completed.

SQL> CONNECT dbv_owner/dbv_owner
Connected.
SQL> EXEC dbms_macadm.enable_dv;

PL/SQL PROCEDURE successfully completed.

-----------------------------------------------------------------------------------------------
# Step_5: Recheck DBVAULT Status
-----------------------------------------------------------------------------------------------

SQL> SET lines 150
SQL> SELECT parameter, VALUE FROM v$option WHERE parameter = 'Oracle Database Vault';

PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Oracle DATABASE Vault                                            TRUE



#======== Database Vault 12cR2 testing (Manually REALM Creation) ===========

-----------------------------------------------------------------------------------------------
# Step_1: Creating an object on CMS Schema to apply REALM
-----------------------------------------------------------------------------------------------
SQL> conn cms/cms
Connected.
SQL>
SQL>
SQL> create table cms.app (sl number, host varchar2(20));

Table created.

SQL> insert into app values (10, 'app1.aibl.com');

1 row created.

SQL> commit;

Commit complete.

-----------------------------------------------------------------------------------------------
# Step_2: Creating another user to access object on CMS schema
-----------------------------------------------------------------------------------------------

SQL> conn dbv_acctmgr/dbv_acctmgr
Connected.
SQL>
SQL>
SQL> create user app_read identified by app_read;

User created.

SQL> GRANT CONNECT TO app_read;

Grant succeeded.

SQL> conn sys / as sysdba
Connected.

SQL> GRANT SELECT, UPDATE ON cms.app to app_read;

------------------------------------------------------------------------------------------------------------------------------------------
# Step_3: REALM CREATION:
# In PL/SQL it gives the two below command, parameter realm_type is set to 0 to avoid creation of a mandatory realms which means that
# objects owner (CMS) will still have full access to its objects. I also activate the realms right after its creation (enabled parameter):
--------------------------------------------------------------------------------------------------------------------

SQL> conn dbv_acctmgr/dbv_acctmgr
Connected.

SQL> EXEC dbms_macadm.create_realm(realm_name => 'CMS schema', description => 'Protect CMS Schema ', enabled => 'Y', audit_options => 1, realm_type =>'0' );

PL/SQL procedure successfully completed.

-----------------------------------------------------------------------------------------------
# Step_4: Add Object to REALM
-----------------------------------------------------------------------------------------------------------------

SQL> EXEC dbms_macadm.add_object_to_realm(realm_name => 'CMS schema', object_owner => 'CMS', object_name => '%', object_type => '%' );

PL/SQL procedure successfully completed.

-------------------------------------------------------------------------------------------------------
# Step_5: Add Authorizaion to REALM:
# If you grant the APP account as a participant to its own realm (can also be done with Cloud Control):
-------------------------------------------------------------------------------------------------------

SQL> EXEC dbms_macadm.add_auth_to_realm(realm_name => 'CMS schema', grantee => 'CMS', rule_set_name => '', auth_options => DBMS_MACUTL.G_REALM_AUTH_PARTICIPANT);

PL/SQL procedure successfully completed.

-------------------------------------------------------------------------------------------------
#  Checking the impact of REALM from SYS/System user who has the sysdba grant:
-------------------------------------------------------------------------------------------------

Step_1:
SQL> conn sys / as sysdba
Connected.

SQL> select * from cms.app;
select * from cms.app
                  *
ERROR at line 1:
ORA-01031: insufficient privileges

--------------------------------------------------------------------------------------------------
# Step_2: Checking the impact of REALM from a different user who has the right to read the object:
--------------------------------------------------------------------------------------------------

SQL> conn app_read/app_read
Connected.

SQL>
SQL> select * from cms.app;

        SL HOST
---------- --------------------
        10 app1.aibl.com


------------------------------------------------------------------------------------------------
# Step_3: Checking the impact of REALM from the schema user:
------------------------------------------------------------------------------------------------

SQL> conn cms/cms
Connected.

SQL>
SQL> select * from cms.app;

        SL HOST
---------- --------------------
        10 app1.aibl.com


## -------------- CHANGING REALM ROLE ------------

SQL> EXEC dbms_macadm.delete_auth_from_realm(realm_name => 'CMS schema', grantee => 'CMS');

PL/SQL PROCEDURE successfully completed.

SQL> EXEC dbms_macadm.add_auth_to_realm(realm_name => 'CMS schema', grantee => 'CMS', rule_set_name => '', auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER);

PL/SQL PROCEDURE successfully completed.

## Note: now owner of the schema can grant it's own objects now:

## ------------- REMOVE REALM -----------------

To remove everything simply execute:

SQL> EXEC dbms_macadm.delete_realm(realm_name => 'CMS schema');

PL/SQL PROCEDURE successfully completed.




===============Create Secure Applicatin Role===============================


Step 1: Create a Rule

begin
DVSYS.DBMS_MACADM.CREATE_RULE(rule_name => 'CMSAPPRULE', rule_expr => 'SYS_CONTEXT('USERENV', 'SESSION_USER') != 'SCOTT'');
end;

Step 2: Create a Rule Set

begin
DECLARE x VARCHAR2(40);
static_option BOOLEAN := FALSE;
BEGIN
x:='N';
IF x = 'Y' THEN
static_option := TRUE;
ELSE static_option := FALSE;
END IF;
DVSYS.DBMS_MACADM.CREATE_RULE_SET(rule_set_name => 'APP_USER_AUTH', description => ' ssss', enabled => 'Y', eval_options => 1, audit_options => 1, fail_options => 1, fail_message => '', fail_code => '', handler_options => 0, handler => '',is_static => static_option);
END;
DVSYS.DBMS_MACADM.ADD_RULE_TO_RULE_SET(rule_set_name => 'APP_USER_AUTH', rule_name => 'CMSAPPRULE', rule_order => '1', enabled => 'Y');
end;

Step 3:
Create a Secure Application Role

begin
DVSYS.DBMS_MACADM.CREATE_ROLE(role_name=>DBMS_ASSERT.ENQUOTE_NAME('DV_APP_USR_ROLE'), enabled => 'Y', rule_set_name => 'APP_USER_AUTH');
end;


Step 4: grant to created DV Role

GRANT SELECT, UPDATE ON APP TO DV_APP_USR_ROLE;
GRANT SELECT, UPDATE ON TEST01 TO DV_APP_USR_ROLE;

Step 5:
SQL>  EXEC DBMS_MACSEC_ROLES.SET_ROLE('DV_APP_USR_ROLE');

PL/SQL procedure successfully completed.

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

Scenario: When creating a physical standby database using rman duplication command

bash-4.2$ rman target sys/pwd@primary auxiliary sys/pwd @stby

After running the above command, then the following error shows:

RMAN-04006: error from auxiliary database: 

ORA-01017: invalid username/password; logon denied

Solution:

Case 1:

SQL> show parameter REMOTE_LOGIN_PASSWORD

NAME                                 TYPE                              VALUE

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

remote_login_passwordfile            string                            EXCLUSIVE

SQL>show parameter REMOTE_OS_AUTHENT

NAME                                 TYPE                              VALUE

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

remote_os_authent                    boolean                           FALSE

By default remote_login_passwordfile is EXCLUSIVE and remote_os_authent is FALSE.

If  other than EXCLUSIVE and FALSE then run the following command, which should resolve the problem.

SQL> alter system set REMOTE_LOGIN_PASSWORD= EXCLUSIVE

SQL> alter system set REMOTE_OS_AUTHENT = FALSE

Case 2:

I have such problem where I never explicitly set a password for the SYS user in the newly created database and I could still connect locally. I have fixed that by the following way:

1.      Execute the following command in primary/target database.

SQL>ALTER USER SYS IDENTIFIED BY pwd;

2.      The command update the password file automatically $ORACLE_HOME/dbs/orapwORACLE_SID

3.      Copy password file to standby server

Transparent Data Encryption (TDE) In Oracle 12c

-- Creating Keystore directory
$ mkdir -p /orafs/app/oracle/12.2.0.1/db_1/server_wallet_home

-- Editing sqlnet.ora file as following:
$ vi /orafs/app/oracle/12.2.0.1/db_1/network/admin/sqlnet.ora

### Advance security wallet location
ENCRYPTION_WALLET_LOCATION =
 (SOURCE =
   (METHOD = FILE)
   (METHOD_DATA =
     (DIRECTORY = /orafs/app/oracle/12.2.0.1/db_1/server_wallet_home)
    )
  )


-- Bouncing Database 
SQL > shutdown immediate;
SQL > startup;

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status: NOT_AVAILABLE

-- Creating the keystore
SQL> administer key management create keystore '/orafs/app/oracle/12.2.0.1/db_1/server_wallet_home' identified by "wlt123";

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status: CLOSED

-- Opening the keystore

SQL> administer key management set keystore open identified by "wlt123";

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status : OPEN_NO_MASTER_KEY

-- Creating a MASTER KEY
SQL> administer key management set key identified by "wlt123" with backup;

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status : OPEN


-- Creating auto login setup :

SQL> administer key management create auto_login keystore from keystore '/orafs/app/oracle/12.2.0.1/db_1/server_wallet_home' identified by "wlt123";





 ======================== Checking TDE =======================

---- Encrypting column of an existing table:

SQL> alter table test.account modify (ACCNAME encrypt);

Table altered.

-- You can turn off the encryption for a table:
SQL> alter table test.account modify (ACCNAME decrypt);

Table altered.


--- Encrypting a tablespace online using TDE encryption

SQL> alter tablespace test_tsp ENCRYPTION online ENCRYPT FILE_NAME_CONVERT = ('test_dbs.dbf', 'test_dbs_encrypt.dbf');

Tablespace altered.


SQL> select file_name from dba_data_files where tablespace_name = 'test_tsp';

FILE_NAME
-------------------------------------------------------------------------------------
/u01/app/oracle/oradata/test_dbs_encrypt.dbf


--- Decrypting the same tablespace online using TDE encryption
SQL> alter tablespace saidur_tsp ENCRYPTION ONLINE DECRYPT FILE_NAME_CONVERT = ('test_dbs_encrypt.dbf', 'test_dbs.dbf');

Tablespace altered.


 ============================== End ===================== 

No standby redo logfiles available for T-1 (Log Shipping when log switch occurs)

In Oracle 12c single instance Dataguard environment ,

 Standby  alert log shows below information; 

Primary database is in MAXIMUM PERFORMANCE mode

RFS[1]: Assigned to RFS process (PID:18416050)

RFS[1]: No standby redo logfiles available for T-1 

RFS[1]: Opened log for T-1.S-1164 dbid 1226709381 branch 1005380631

Archived Log entry 84 added for thread 1 sequence 1164 rlc 1005380631 ID 0x55f53b9e LAD3 :

Solutions1:

Checked both primary and standby database, the standby redo logs have been created. It is found that the thread id is 1 in both primary and standby db.

Drop all standby online redo logs on standby databases, and recreate then again by excluding “thread 1” .

Finally restart the recovery process, everything is fine.

We have found this kind of error for single instance database in 12c, 

which is a new feature or may be bug issue?


Solutions2:

If you have configured a standby redo log on one or more standby databases in the configuration, ensure the size of the standby redo log files on each standby database exactly matches the size of the online redo log files on the primary database.

At log switch time, if there are no available standby redo log files that match the size of the new current online redo log file on the primary database:

• The primary database will shut down if it is operating in maximum protection mode,
  or
• The RFS process on the standby database will create an archived redo log file on the standby database and write the following message in the alert log:

  No standby log files of size <#> blocks available.
  

For example, if the primary database uses two online redo log groups whose log files are 100K, then the standby database should have 3 standby redo log groups with log file sizes of 100K.

Also, whenever you add a redo log group to the primary database, you must add a corresponding standby redo log group to the standby database. This reduces the probability that the primary database will be adversely affected because a standby redo log file of the required size is not available at log switch time.

Source: https://docs.oracle.com/cd/B19306_01/server.102/b14239/troubleshooting.htm#i639505

Step by Step 12c Opatch Update Prcess

1. Backup the OPatch directory and Copy the new OPatch file into the Oracle Home directory then run then command:

bash-4.2$ cp p6880880_122010_AIX64-5L.zip /orafs/app/oracle/12.2.0.1/db_1/p6880880_122010_AIX64-5L.zip

bash-4.2$  unzip p6880880_122010_AIX64-5L.zip

2. copy and unzip the PSU file to the following location and then unzip the file

bash-4.2$ bash-4.2$ cp p28662603_122010_AIX64-5L.zip 

/orafs/app/oracle/12.2.0.1/db_1/OPatch/p28662603_122010_AIX64-5L.zip

bash-4.2$  unzip p28662603_122010_AIX64-5L.zip

bash-4.2$  cd 28662603

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

$ORACLE_HOME/OPatch/opatch lsinventory

bash-4.2$  $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

3.  apply opatch :

bash-4.2$  $ORACLE_HOME/OPatch/opatch apply

4. Run ./datapatch after patching :

sqlplus /nolog

Connect / as sysdba

startup

quit

bash-4.2$  cd $ORACLE_HOME/OPatch

bash-4.2$  ./datapatch -verbose

DB Upgrade from 11.2.0.3 to 12.2.0.1

DB upgrade  from 11g to 12c using DBUA

Step 1: 

At first install the 12c binary and then follow the following activities.

Copy spfilea.ora and password file to /orafs/app/oracle/12.2.0.1/db_1/dbs/ 

Step 2:

$ORACLE_HOME/jdk/bin/java -jar /orafs/app/oracle/12.2.0.1/db_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade_log

bash-4.2$ sqlplus / as sysdba

SQL> @preupgrade_fixups.sql

Step 3:

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Step 4. EM Remove:

cd /orafs/app/oracle/12.2.0.1/db_1/rdbms/admin/

SQL>SET ECHO ON

SQL>SET SERVEROUTPUT ON

SQL>@emremove.sql

Step 5:

SQL> show parameter SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE

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

sec_case_sensitive_logon             boolean     FALSE

SQL> alter system set sec_case_sensitive_logon=true;

System altered.

Step 6:

bash-4.2$ vi sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

Step 7:

Add the following line on the /etc/oratab

bash-4.2$ vi /etc/oratab

dbname:/orafs/app/oracle/11.2.0.3/db_1:N

Step 8: run DBUA

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

Restore RMAN Backup TO a Different Server

This blog will detail the steps to restore a database on a new host using RMAN. 

Step_1. Create Pfile

Step_2. Startup nomount:

SQL> startup nomount pfile='/rmanfs/pfile.ora';
----------------------------------------------------
Transfer the full backup with control file to the desired server.

Step_3. Restore Controlfile:

RMAN> restore controlfile from '/rmanfs/backup/o1_mf_s_99261014_.bkp';
RMAN> catalog start with '/rmanfs/backup';


Step_4: Mount Database

SQL> alter database mount;

Step_5: Restrore DB


ORACLE_UNQNAME=dbname; export ORACLE_UNQNAME
ORACLE_BASE=/orafs/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/11.2.0.3/db_1; export ORACLE_HOME
ORACLE_SID=dbname; export ORACLE_SID

PATH=$PATH:$ORACLE_HOME/bin; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

$ORACLE_HOME/bin/rman  target /

run
{
allocate channel disk1 device type disk;
allocate channel disk2 device type disk;

set newname for datafile 1 to '/dbsystemfs/oradata/system01.dbf';
set newname for datafile 2 to '/dbsystemfs/oradata/sysaux01.dbf';
set newname for datafile 3 to '/undofs/undo_tbs01.dbf';
set newname for datafile 4 to '/dbsystemfs/oradata/ababil/users/users01.dbf';
set newname for tempfile 1 to '/tempfs/oradata/temp/temp01.dbf';

restore database;
switch datafile all;
switch tempfile all;
release channel disk1;
release channel disk2;
}


Step_6: Recover Database;

RMAN> recover database;(RMAN> recover database until scn 21046745532;)
RMAN> alter database open resetlogs;

How to Disable the triger on standby database and how to resolve the error- ORA-00604: error occurred at recursive SQL level 1

We have added an AFTER LOGON ON DATABASE,AFTER DDL ON DATABASE and AFTER SERVERERROR ON DATABASE trigger.
Standby databases are not writable and so this is not going to work.

As a result every users connecting to the standby database were complaining:

ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
ORA-06512: at line 2
ORA-02063: preceding 3 lines
00604. 00000 -"error occurred at recursive SQL level %s"
*Cause:An error occurred while processing a recursive SQL statement
(a statement applying to internal dictionary tables).
*Action: If the situation described in the next error on the stack
can be corrected, do so; otherwise contact Oracle Support.

Another issue raises the replication does not work i.e. Data sync is no working.

Realising my mistake I dropped/disable the trigger but this didn’t make the problem go away on the standby database.

A quick look in the alert log on the primary and there was a logon failure related to Data Guard:

Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
------------------------------------------------------------
PING[ARCl]: Heartbeat failed to connect to standby 'adgrpt'. Error is 16191.

I checked the SYS passwords on both nodes in case an underlying problem had suddenly occurred but all was ok.

Looking at the output we have a big clue here,
as it looks like the error the users were getting:

ORA-16000: database open for read-only access

Why are we still getting that when I’ve dropped the trigger on the primary database? It is immediately apparent that
 I have broken Data Guard and my drop is in a log file awaiting shipping to the standby. This is a catch 22:
I can’t apply the log because the trigger won’t let Data Guard authenticate and Data Guard can’t authenticate because of the presence of the trigger.

Two questions sprang to my mind:

    Can I disable the trigger?
    Why is the trigger affecting the SYS user?

I can’t workaround the trigger in any documented way because any attempt to do so on the physical standby is prevented by its read-only status, for example:

SQL> grant ADMINISTER DATABASE TRIGGER to appuser;
grant ADMINISTER DATABASE TRIGGER to appuser
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

Here user SYS is connecting with a password not “as sysdba” and so we are hitting the logon trigger.

we can the following system parameter .

alter system set "_system_trig_enabled"=FALSE;

I can then restart the recovery with:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

And of course not forgetting to re-enable system triggers:

alter system set "_system_trig_enabled"=TRUE;

It should be pointed out that this method is nice because it doesn’t involve restarting the standby and therefore interrupting any running reports
but if you don’t like undocumented parameters or you have other system triggers you depend on you can instead:

    Shut down the standby.
    Restart the standby in MOUNT mode. This means that any triggers will not fire.
    Start the recovery.
    If the trigger has been dropped on the primary it should replicate to the secondary.
    Open the database.


Source URL: https://oraganism.wordpress.com/2015/08/26/non-working-logon-trigger-on-data-guard-standby-database/

Shared Pool Utilization Script

DECLARE
   object_mem       NUMBER;
   shared_sql       NUMBER;
   cursor_mem       NUMBER;
   mts_mem          NUMBER;
   used_pool_size   NUMBER;
   free_mem         NUMBER;
   pool_size        VARCHAR2 (512);                     -- Now from V$SGASTAT
BEGIN
   -- Stored objects (packages, views)
   SELECT SUM (sharable_mem)
     INTO object_mem
     FROM v$db_object_cache;

   -- Shared SQL -- need to have additional memory if dynamic SQL used
   SELECT SUM (sharable_mem)
     INTO shared_sql
     FROM v$sqlarea;

   -- User Cursor Usage -- run this during peak usage.
   --  assumes 250 bytes per open cursor, for each concurrent user.
   SELECT SUM (250 * users_opening)
     INTO cursor_mem
     FROM v$sqlarea;

   -- For a test system -- get usage for one user, multiply by # users
   -- select (250 * value) bytes_per_user
   -- from v$sesstat s, v$statname n
   -- where s.statistic# = n.statistic#
   -- and n.name = 'opened cursors current'
   -- and s.sid = 25;  -- where 25 is the sid of the process
   -- MTS memory needed to hold session information for shared server users
   -- This query computes a total for all currently logged on users (run
   --  multiply by # users.
   SELECT SUM (VALUE)
     INTO mts_mem
     FROM v$sesstat s, v$statname n
    WHERE s.statistic# = n.statistic# AND n.NAME = 'session uga memory max';

   -- Free (unused) memory in the SGA: gives an indication of how much memory
   -- is being wasted out of the total allocated.
   SELECT BYTES
     INTO free_mem
     FROM v$sgastat
    WHERE NAME = 'free memory' AND pool = 'shared pool';

   -- For non-MTS add up object, shared sql, cursors and 20% overhead.
   used_pool_size := ROUND (1.2 * (object_mem + shared_sql + cursor_mem));

   -- For MTS mts contribution needs to be included (comment out previous line)
   -- used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem));
   SELECT SUM (BYTES)
     INTO pool_size
     FROM v$sgastat
    WHERE pool = 'shared pool';

   -- Display results
   DBMS_OUTPUT.put_line ('Shared Pool Memory Utilization Report');
   DBMS_OUTPUT.put_line ('Obj mem:  ' || TO_CHAR (object_mem) || ' bytes');
   DBMS_OUTPUT.put_line ('Shared sql:  ' || TO_CHAR (shared_sql) || ' bytes');
   DBMS_OUTPUT.put_line ('Cursors:  ' || TO_CHAR (cursor_mem) || ' bytes');
   -- dbms_output.put_line ('MTS session: '||to_char (mts_mem) || ' bytes');
   DBMS_OUTPUT.put_line (   'Free memory: '
                         || TO_CHAR (free_mem)
                         || ' bytes '
                         || '('
                         || TO_CHAR (ROUND (free_mem / 1024 / 1024, 2))
                         || 'MB)'
                        );
   DBMS_OUTPUT.put_line (   'Shared pool utilization (total):  '
                         || TO_CHAR (used_pool_size)
                         || ' bytes '
                         || '('
                         || TO_CHAR (ROUND (used_pool_size / 1024 / 1024, 2))
                         || 'MB)'
                        );
   DBMS_OUTPUT.put_line (   'Shared pool allocation (actual):  '
                         || pool_size
                         || ' bytes '
                         || '('
                         || TO_CHAR (ROUND (pool_size / 1024 / 1024, 2))
                         || 'MB)'
                        );
   DBMS_OUTPUT.put_line (   'Percentage Utilized:  '
                         || TO_CHAR (ROUND (used_pool_size / pool_size * 100))
                        );
END;

Step by Step configuration Oracle Grid Infrastructure for a standalone server (11gR2) into the existing Database System.

Scenario: Oracle (11gR2 11.2.0.3) RDBMS had already been installed on Linux 5.4, After some times, Management decided that ASM should be included (GI installation and configuration ) without disturbing the current environment.

Steps to setup action plan:

1.      RDBMS setup(already installed)

2.      Creating virtual hard disks in VMware for ASM disks

3.      Creating recommended OS groups and user for Grid Infrastructure(GI)

4.      Edit the basic files from root user

5.      Installing Oracle ASM packages, Configuring and load the ASM kernel module (as the “root” user)

6.      Creating ASM disk Volume

7.      Download and Install the GI software only

8.      Configuring the Software Binaries

9.      Creating the ASM instance using ASMCA and Create the ASM disk Group

10.  Create Database Instance

Step-1: RDBMS setup

RDBMS had already been installed. If any DB instance exists, then delete that first if not needed.  In case of Production, data backup must be ensured for restoration purpose.

Step-2: Creating virtual hard disks in VMware for ASM disks

1. CRSDISK1 (2 GB) – for CRSVOL1 disk where Cluster Ready Services (CRS) files will be stored. CRS provides many system management services and interacts with the vendor clusterware to coordinate cluster membership information.

2. DATADISK1 (20 GB) – disk for DATAVOL1 disk. Here database will keep all datafiles, control files, log files …

3. FRADISK1 (10 GB) – disk for FRAVOL1 disk for database Fast Recovery Area (FRA) files. For example: database backup files, copy of database control files.

Open virtual machine properties window and use wizard to create new virtual hard disks:

Step-3: Creating recommended OS groups and user for Grid Infrastructure(GI)

Create user grid and directories

User oracle, group oinstall, dba are ready created by oracle-rdbms-server-11gr2-preinstall:

[root@asmdb ~]# id oracle

uid=500(oracle) gid=501(oinstall) groups=501(oinstall),500(dba),502(oper)

[root@asmdb ~]# id grid

id: grid: No such user

[root@asmdb ~]#

Add this new groups:

 [root@asmdb ~]# groupadd asmadmin

[root@asmdb ~]# groupadd asmdba

[root@asmdb ~]# groupadd asmoper

Create new user grid:

[root@asmdb ~]# useradd -g oinstall -G asmadmin,asmdba,asmoper,dba -c "Grid Infrastructure Owner" grid

Modify user oracle:

 [root@asmdb ~]# usermod -G dba,oper,asmdba -c "Database Owner" oracle

Result:

[root@asmdb ~]# id grid

uid=501(grid) gid=501(oinstall) groups=501(oinstall),500(dba),503(asmadmin),504(asmdba),505(asmoper)

[root@asmdb ~]# id oracle

uid=500(oracle) gid=501(oinstall) groups=501(oinstall),500(dba),502(oper),504(asmdba)

Note: User grid must be put on secondary group dba and user oracle must be put on secondary group asmdba 

Create directories for grid:

*** Oracle software binaries and GI software binaries may be installed into separate mount point.

[root@asmdb ~]# mkdir -p /u01/app/grid

[root@asmdb ~]# mkdir -p /u01/app/grid/product/11.2.0/grid

[root@asmdb ~]# chown -R grid:oinstall /u01/app/grid

[root@asmdb ~]# chmod -R 775 /u01

[root@asmdb ~]# mkdir -p /softgrid

[root@asmdb ~]# chown -R grid:oinstall /softgrid

[root@asmdb ~]# chmod -R 775 /softgrid

Step-4: Edit the basic files from root user

 

Add the following lines to the "/etc/security/limits.conf" file.

[root@asmdb softgrid]# vi  /etc/security/limits.conf

oracle   soft   nofile    131072

oracle   hard   nofile    131072

oracle   soft   nproc    131072

oracle   hard   nproc    131072

oracle   soft   core    unlimited

oracle   hard   core    unlimited

oracle   soft   memlock    50000000

oracle   hard   memlock    50000000

oracle   soft    stack   10240

grid soft nproc 2047

grid hard nproc 16384

grid soft nofile 1024

grid hard nofile 65536

For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file by running the following command:

[root@asmdb softgrid]# cat  /etc/profile

if [ \$USER = "oracle" ] || [ \$USER = "grid" ]; then

if [ \$SHELL = "/bin/ksh" ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

umask 022

fi

Step-5: Installing Oracle ASM packages, Configuring and load the ASM kernel module (as the “root” user)

Install the following RPM for ASM

[root@asmdb Desktop]# rpm -Uvh oracleasm-support-2.1.7-1.el5.x86_64.rpm

[root@asmdb Desktop]# rpm -Uvh oracleasm-2.6.18-238.el5-2.0.5-1.el5.x86_64.rpm

[root@asmdb Desktop]# rpm -Uvh oracleasmlib-2.0.4-1.el5.x86_64.rpm

Configuring and load the ASM kernel module (as the “root” user):

[root@asmdb Desktop]# /usr/sbin/oracleasm configure -i

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library

driver.  The following questions will determine whether the driver is

loaded on boot and what permissions it will have.  The current values

will be shown in brackets ('[]').  Hitting <ENTER> without typing an

answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: grid

Default group to own the driver interface []: asmadmin

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

The script completes the following tasks:

Creates the /etc/sysconfig/oracleasm configuration file

Creates the /dev/oracleasm mount point

Mounts the ASMLib driver file system

Enter the following command to load the oracleasm kernel module:

[root@asmdb Desktop]# /usr/sbin/oracleasm init

Creating /dev/oracleasm mount point: /dev/oracleasm

Loading module "oracleasm": oracleasm

Mounting ASMlib driver filesystem: /dev/oracleasm

Step-6: Creating ASM Disk Volume

Let’s take a look at the available disks in Oracle Linux (remember we have created three additional disks for ASM)

[root@asmdb Desktop]# fdisk -l

Disk /dev/sda: 42.9 GB, 42949672960 bytes

255 heads, 63 sectors/track, 5221 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

/dev/sda1   *           1          13      104391   83  Linux

/dev/sda2              14        5221    41833260   8e  Linux LVM

Disk /dev/sdb: 2147 MB, 2147483648 bytes

255 heads, 63 sectors/track, 261 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

/dev/sdb1               1         261     2096451   83  Linux

Disk /dev/sdc: 21.4 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

/dev/sdc1               1        2610    20964793+  83  Linux

Disk /dev/sdd: 10.7 GB, 10737418240 bytes

255 heads, 63 sectors/track, 1305 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

   Device Boot      Start         End      Blocks   Id  System

/dev/sdd1               1        1305    10482381   83  Linux

We’ve got three new disks available for ASM disks:

Disk /dev/sdb: 2147 MB

Disk /dev/sdc: 21.4 GB

Disk /dev/sdd: 10.7 GB

First we have to create partitions to be able to use those disks as ASM disks;

[root@asmdb ~]# ls /dev/sd*

/dev/sda  /dev/sda1  /dev/sda2  /dev/sdb  /dev/sdc  /dev/sdd

[root@asmdb ~]#

[root@asmdb ~]# fdisk /dev/sdb

Command (m for help): n

Command action

   e   extended

   p   primary partition (1-4)

p

Partition number (1-4): 1

First cylinder (1-261, default 1): 1

Last cylinder or +size or +sizeM or +sizeK (1-261, default 261): 261

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

Same  command should be run for /dev/sdc and /dev/sdd

Creating ASM disks:

 [root@asmdb Desktop]# /usr/sbin/oracleasm createdisk CRSVOL1 /dev/sdb1

Writing disk header: done

Instantiating disk: done

[root@asmdb Desktop]# /usr/sbin/oracleasm createdisk DATAVOL1 /dev/sdc1

Writing disk header: done

Instantiating disk: done

 [root@asmdb Desktop]# /usr/sbin/oracleasm createdisk FRAVOL1 /dev/sdd1

Writing disk header: done

Instantiating disk: done

List ASM disks:

 [root@asmdb Desktop]# /usr/sbin/oracleasm listdisks

CRSVOL1

DATAVOL1

FRAVOL1

Test disks discovery (oracle-discovery is being used by the oracle database creation assistant (dbca))

[root@asmdb Desktop]# oracleasm-discover

Using ASMLib from /opt/oracle/extapi/64/asm/orcl/1/libasm.so

[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]

Discovered disk: ORCL:CRSVOL1 [4192902 blocks (2146765824 bytes), maxio 512]

Discovered disk: ORCL:DATAVOL1 [41929587 blocks (21467948544 bytes), maxio 512]

Discovered disk: ORCL:FRAVOL1 [20964762 blocks (10733958144 bytes), maxio 512]

Step-7: Download and Install the GI software only

Download Oracle Database 11g R 2 Grid Infrastructure (11.2.0.3.0) for Linux x86 –

Linux_11gR2_grid.zip

Copy linux.x64_11gR2_grid.zip to oracle server into /softgrid directory using, for example WinSCP

[grid@asmdb /]$ unzip /softgrid/linux.x64_11gR2_grid.zip

[grid@asmdb /]$ cd /softgrid/grid_11.2.3.0

[grid@asmdb  grid_11.2.3.0]$ ./runInstaller

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB. Actual 15673 MB Passed

Checking swap space: must be greater than 150 MB. Actual 3999 MB Passed

Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-01-11_10-65-27AM. Please wait ...

Select the Grid Infrastructure only and follow the wizard, finally finish the installation.

Step-8: Configuring the Software Binaries

To configure and activate a software-only Oracle Grid Infrastructure installation for Oracle Restart, complete the following tasks:

    Login as root and run the roothas.pl script from Grid_home using the following syntax:

    Grid_home/perl/bin/perl -I Grid_home/perl/lib -I Grid_home/crs/install

    Grid_home/crs/install/roothas.pl

    For example, if your Oracle Grid Infrastructure home is /u01/app/oracle/product/11.2.0/grid, then run the following script:

    # /u01/app/oracle/product/11.2.0/grid/perl/bin/perl -I /u01/app/oracle/product/11.2.0/grid/perl/lib -I /u01/app/oracle/product/11.2.0/grid/crs/install  /u01/app/oracle/product/11.2.0/grid/crs/install/roothas.pl

    Change the directory to Grid_home/oui/bin, where Grid_home is the path of the Oracle Grid Infrastructure home.

    Login as the Oracle Restart software owner user and enter the following command:

    ./runInstaller -updateNodeList ORACLE_HOME=Grid_home -defaultHomeName CLUSTER_NODES= CRS=TRUE

    For example:

    $ ./runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid

    -defaultHomeName CLUSTER_NODES= CRS=TRUE

Step-9: Creating the ASM instance using ASMCA and the desired ASM disk groups

Use the SRVCTL utility along with Network Configuration Assistant and Oracle ASMCA to add the listener, the Oracle ASM instance, and all Oracle ASM disk groups to the Oracle Restart configuration.

Now run ASMCA and create ASM instance, provide proper information and using the ASMCA create the desired ASM disk groups.

Step-10: Create Database Instance

Create the DB instance using DBCA and provide proper information like ASM file system with ASM disk groups. For FRA use the FRA disk group.