Migrate a Non-Container Database (CDB) to a Pluggable Database (PDB) in Oracle Database 12c Release 2 (12.2)

Migration:

DBMS_PDB package permits to generate an XML metadata file from a non-CDB 12c database, Its like a unplug a Pluggable database.

Step 1 : Check the database its properly up and running.shutdown the non-CDB and start it in read-only mode.

[oracle@ram ~]$ export ORACLE_SID=hari
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 22:54:03 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name,open_mode from v$database;

NAME         OPEN_MODE
--------- --------------------
HARI         READ WRITE

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size 8620224 bytes
Variable Size 452986688 bytes
Database Buffers 788529152 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.

In the non-DBC database using the DBMS_PDB_DESCRIBE procedure  creates an XML file in the same way that the unplug operation does for a PDB.

Step 2 : Shutdown the non-CDB database.

SQL> begin dbms_pdb.describe(pdb_descr_file => '/u01/app/oracle/oradata/HARI/tmp/hari.xml');
2 end;
3 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Step 3 : Connect to an existing CDB

Step 4 : Create a new PDB using that xml file which we described already in  the non-CDB database. Remember to configure the FILE_NAME_CONVERT parameter to convert the existing files to the new location.

[oracle@ram ~]$ export ORACLE_SID=Petonas
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 23:46:58 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create pluggable database pdb5new using '/u01/app/oracle/oradata/HARI/tmp/hari.xml'
2 copy file_name_convert = ('/u01/app/oracle/oradata/HARI/datafile/','/u01/app/oracle/oradata/PETONAS/datafile/pdb5new/');


Pluggable database created.

Step 5 : Switch to the PDB container and run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean up the new PDB, removing any items that should not be present in a PDB.

SQL> alter session set container=pdb5new;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;

step 6 : Startup the PDB and check the open mode

SQL> alter session set container=pdb5new;

Session altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> select name ,open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB5NEW
READ WRITE


SQL> col name for a15
SQL> /

NAME             OPEN_MODE
--------------- ----------
PDB5NEW          READ WRITE


The non-cdb database has been converted to CDB now successfully.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Connecting to Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 2 (12.2)

Connecting to a Container Database (CDB)

Connecting to the root of a container database is the same as  database instance.

On the database server you can use OS Authentication.

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 02:10:26 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL>

You can connect to other common users in similar way.

SQL> conn system/oracle
Connected.
SQL>

The v$services views can be used to display available services from the database.

SQL> col name for a20
SQL> col pdb for a20
SQL> select name , pdb from v$services order by name;

NAME                     PDB
--------------------   --------------
SYS$BACKGROUND           CDB$ROOT
SYS$USERS                CDB$ROOT
dbwr.carrierzone.com     CDB$ROOT
dbwrXDB                  CDB$ROOT
pdb1.carrierzone.com     PDB1
pdb5.carrierzone.com     PDB5
pdb6.carrierzone.com     PDB6

7 rows selected.

The lsnrctl utility allows you to display the available services from the command line

[oracle@ram ~]$ lsnrctl service

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-OCT-2018 02:19:58

Copyright (c) 1991, 2016, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ram.localdomain.com)(PORT=1521)))
Services Summary...
Service "777d95e41a572d82e053dd971d40e224.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "77813d4b379738f5e0536501a8c05864.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "77f4b206457338a1e0536501a8c0ed42.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "dbwr.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "dbwrXDB.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: ram.localdomain.com, pid: 32509>
(ADDRESS=(PROTOCOL=tcp)(HOST=ram.localdomain.com)(PORT=58780))
Service "pdb1.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdb5.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "pdb6.carrierzone.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully.

Connections using services

[oracle@ram admin]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 02:30:30 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn system/oracle@dbwr
Connected.
SQL>

The connection using a TNS requires an entry in the “$ORACLE_HOME/network/admin/tnsnames.ora” file

[oracle@ram admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DBWR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbwr.carrierzone.com)
)
)
Displaying the Current Container

The show con_name and show con_id commands in SQL*Plus display the current container name and ID respectively.

SQL> sho con_name

CON_NAME
-----------------
CDB$ROOT
SQL> show con_id

CON_ID
------------------
1
SQL>

They can also be retrieved using the sys_context function.

SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
---------------------------------------------------
CDB$ROOT

SQL> select sys_context('userenv','con_id') from dual;

SYS_CONTEXT('USERENV','CON_ID')
---------------------------------------------------
1

SQL>
Switching Between Containers

When logged in to the CDB as an appropriately privileged user, the alter session command can be used to switch between containers within the container database.

SQL> conn / as sysdba
Connected.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container = pdb5;

Session altered.

SQL> sho con_name

CON_NAME
------------------------------
PDB5

SQL> alter session set container =cdb$root;

Session altered.

SQL> sho con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>

Connecting to a Pluggable Database (PDB)

Direct connections to pluggable databases must be made by using a service. Each pluggable database automatically registers a service with the listener.

This is how any application will connect to a pluggable database, as well as administrative connections.

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 02:47:52 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> conn / as sysdba
Connected.
SQL> conn system/oracle@pdb5
Connected.
SQL>

The connection using a TNS alias requires an entry in the “$ORACLE_HOME/network/admin/tnsnames.ora” file, such as the one shown below.

[oracle@ram admin]$ cat tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PDB5 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ram.localdomain.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdb5.carrierzone.com)
)
)

PDB users with the SYSDBA, SYSOPER, SYSBACKUP, or SYSDG privilege can connect to a closed PDB.

All other PDB users can only connect when the PDB is open. As with regular databases, the PDB users require the create session privilege to enable connections.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Manage Users and Privileges For Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 2 (12.2)

Manage Users and Privileges:

When connected to a multitenant database the management of users and privileges is a little different to traditional Oracle environments. In multitenant environments there are two types of user.

  • Common User : The user is present in all containers (root and all PDBs).
  • Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.

Likewise, there are two types of roles.

  • Common Role : The role is present in all containers (root and all PDBs).
  • Local Role : The role is only present in a specific PDB. The same role name can be used in multiple PDBs, but they are unrelated.

Some DDL statements have a container clause added to allow them to be directed to the current container or all containers.

Create Common Users :

While creating a common user the following requirements must all be met.

  • Must be connected to a common user with the create user privilege.
  • The current container must be the root container.
  • The username for the common user must be prefixed with “C##” or “c##” and contain only ASCII or EBCDIC characters.
  • Username must be unique across all containers.
  • The default tablespace, temporary tablespace , quota and profile must all reference objects that exist in all containers.
  • You can either specify the container=all clause, or omit it, as this is the default setting when the current container is the root.

now we can see how to create common users with and without the container clause from the root container.

[oracle@ram ~]$ ps -ef | grep pmon
oracle 37524 34096 0 18:42 pts/0 00:00:00 grep pmon
[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 18:42:58 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> conn / as sysdba
Connected.

Create the common user using the CONTAINER clause

SQL> create user c##user1 identified by password1 container=all;

User created.

SQL> grant create session to c##user1 container=all;

Grant succeeded.

Create the common user using the default CONTAINER setting

SQL> create user c##user2 identified by password2;

User created.

SQL> grant create session to c##user2;

Grant succeeded.
Create Local Users:

While creating a local user the following requirements must all be met.

  • Must be connected to a user with the create user privilege.
  • Username for the local user must not be prefixed with “C##” or “c##”.
  • Username must be unique within the PDB.
  • You can either specify the container=all clause, or omit it, as this is the default setting when the current container is a PDB.
Switch container while connected to a common user
SQL> conn / as sysdba
Connected.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container =pdb5;

Session altered.
Create the local user using the CONTAINER clause
SQL> create user user3 identified by password3 container=current;

User created.

SQL> grant create session to user3 container=current;

Grant succeeded.
Connect to a privileged user in the PDB
SQL> conn system/oracle@pdb5 
Connected.
Create the local user using the default CONTAINER setting
SQL> create user user4 identified by password4;

User created.

SQL> grant create session to user4;

Grant succeeded.
Create Common Roles:

Roles can be common or local. All Oracle-supplied roles are common and therefore available in the root container and all PDBs. Common roles can be created, provided the following conditions are must.

  • You must be connected to a common user with create role and the set container privileges granted commonly.
  • The current container must be the root container.
  • The role name for the common role must be prefixed with “C##” or “c##” and contain only ASCII or EBCDIC characters.
  • The role name must be unique across all containers.
  • The role is created with the container=all clause

Now we can see how to create a common role and grant it to a common and local user.

Create the common role
SQL> conn / as sysdba
Connected.
SQL> create role c##role1;

Role created.

SQL> grant create session to c##role1;

Grant succeeded.
Grant it to a common user
SQL> conn / as sysdba
Connected.
SQL> grant c##role1 to c##user1 container=all;

Grant succeeded.

SQL> alter pluggable database all open;

Pluggable database altered.
Grant it to a local user
SQL> alter session set container =pdb5;

Session altered.

SQL> grant c##role1 to user3;

Grant succeeded.

SQL>
Create Local Roles:

Local roles are created in a similar manner to pre-12c databases. Each PDB can have roles with matching names, since the scope of a local role is limited to the current PDB. The following conditions are must.

  • Must be connected to a user with the create role privilege.
  • If you are connected to a common user, the container must be set to the local PDB.
  • Role name for the local role must not be prefixed with “C##” or “c##”.
  • Role name must be unique within the PDB.

we can see now how to create local a role and grant it to a common user and a local user.

SQL> conn / as sysdba
Connected.
SQL>
Switch container
SQL> alter session set container = pdb5;

Session altered.

Alternatively , we can connect pluggable database through local  or common user  with PDB service.

Create the common role
SQL> create role role1;

Role created.

SQL> grant create session to role1;

Grant succeeded.
Grant it to a common user
SQL> grant role1 to c##user1;

Grant succeeded.
Grant it to a local user
SQL> grant role1 to user3;

Grant succeeded.
Granting Roles and Privileges to Common and Local Users:

The basic difference between a local and common grant is the value used by the container clause.

Common grants
SQL> conn / as sysdba
Connected.
SQL> grant create session to c##user1 container=all;

Grant succeeded.

SQL> grant create session to c##role1 container=all;

Grant succeeded.

SQL> grant c##role1 to c##user1 container=all;

Grant succeeded.

SQL>
Local grants
SQL> conn system/oracle@pdb5
Connected.
SQL> grant create session to user3;

Grant succeeded.

SQL> grant create session to role1;

Grant succeeded.

SQL> grant role1 to user3;

Grant succeeded.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

Flashback Pluggable Database (PDB) in Oracle Database 12c Release 2 (12.2)

Enable/Disable Flashback Database

Pre-request:

1.Must be in archivelog mode

2.Before enable flashback database we need to make sure the database is in archivelog mode.

3.Must do this from the root container.

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 17 00:53:58 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.

SQL> conn / as sysdba
Connected.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

We can now enable/disable flashback database with the open mode in 12c.

SQL> alter database flashback on;

Database altered.

SQL> alter database flashback off;

Database altered.

SQL> alter database flashback on;

Database altered.

We can check the flashback status from v$database.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

SQL>

with db_flashback_retention_target parameter we can set flashback retention time.

SQL> sho parameter retention

NAME                                   TYPE          VALUE
------------------------------------ ----------- ----------------------
db_flashback_retention_target          integer        1440
undo_retention                         integer        900

SQL> alter system set db_flashback_retention_target=10080 scope=both;

System altered.

SQL>
Creating Restore Points
  • Creating restore point is just like same in normal database.
  • Its alias for a SCN.
  • Guaranteed restored point prevents the database from removing any flashback logs between that point and the current time.
  • Always remove unwanted guaranteed restore point.
Normal restore point:
SQL> conn / as sysdba
Connected.
SQL> 
SQL> 
SQL> create restore point rsp;

Restore point created.

SQL> drop restore point rsp;

Restore point dropped.

SQL>
Guaranteed restore point:
SQL> create restore point rsp1 guarantee flashback database;

Restore point created.

SQL> drop restore point rsp1;

Restore point dropped.

SQL>

Creating PDB level restore point and Guarantee restore point as follows.

SQL> conn / as sysdba
Connected.

SQL> alter session set container=pdb5;

Session altered.

SQL> create restore point rsp;

Restore point created.

SQL> drop restore point rsp;

Restore point dropped.

SQL> create restore point rsp1 guarantee flashback database;

Restore point created.

SQL> drop restore point rsp1;

Restore point dropped.

From the root container also we can create PDB level restore point and Guarantee restore point

SQL> conn / as sysdba
Connected.
SQL> 
SQL> 
SQL> create restore point rsp for pluggable database pdb5;

Restore point created.

SQL> drop restore point rsp for pluggable database pdb5;

Restore point dropped.

SQL>

Guarantee restore point from root container:

SQL> 
SQL> create restore point rsp for pluggable database pdb5 guarantee flashback database;

Restore point created.

SQL> drop restore point rsp for pluggable database pdb5;

Restore point dropped.

SQL>

Clean restore points can be created while connected to the PDB

[oracle@ram ~]$ export ORACLE_SID=dbwr
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 17 02:09:44 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> conn / as sysdba
Connected.
SQL> col name for a15;
SQL> select name,open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB5            MOUNTED
PDB6            MOUNTED
PDB1            MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME            OPEN_MODE
--------------- ----------
PDB$SEED        READ ONLY
PDB5            READ WRITE
PDB6            READ WRITE
PDB1            READ WRITE

SQL> alter session set container=pdb5;

Session altered.


SQL> shutdown;
Pluggable Database closed.


SQL> create clean restore point crsp;
create clean restore point crsp
*
ERROR at line 1:
ORA-39891: Clean PDB restore point cannot be created when CDB is in local undo
mode.

SQL> SELECT property_name, property_value from database_properties where property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME
----------------------------------------------------------------------PROPERTY_VALUE
-----------------------------------------------------------------------
LOCAL_UNDO_ENABLED
TRUE
  • The container database to be running in local undo mode, but flashback PDB does not depend on it.
  • If the CDB is running in shared undo mode, it is more efficient to flashback to clean restore points.
  • These are restore points taken when the pluggable database is down, with no outstanding transactions.
SQL> conn / as sysdba
Connected.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.
SQL> alter database local undo off;

Database altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
Database opened.

SQL> col property_name for a20;
SQL> col property_value for a20;

SQL> SELECT property_name, property_value from database_properties where property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME          PROPERTY_VALUE
-------------------- --------------------
LOCAL_UNDO_ENABLED     FALSE

SQL>
SQL> create clean restore point crsp;

Restore point created. 

SQL> drop restore point crsp; 

Restore point dropped. 

SQL>
SQL> create clean restore point crsp guarantee flashback database;

Restore point created. 

SQL> drop restore point crsp; 

Restore point dropped. 

SQL> startup;


ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size		    8795904 bytes
Variable Size		  671090944 bytes
Database Buffers	 1828716544 bytes
Redo Buffers		    7979008 bytes
Database mounted.
Database opened.

They can also be created from the root container.

SQL> conn / as sysdba
Connected.
SQL> alter pluggable database pdb5 close;

Pluggable database closed.

SQL> conn / as sysdba 
Connected. 

SQL> 

SQL> create clean restore point rsp for pluggable database pdb5; 
Restore point created. 

SQL> drop restore point rsp for pluggable database pdb5; Restore point dropped. 

SQL>

SQL> create clean restore point rsp for pluggable database pdb5 guarantee flashback database; 

Restore point created. 

SQL> drop restore point rsp for pluggable database pdb5; Restore point dropped.

 SQL>
Flashback CDB and  PDB:
SQL> select * from t1;

ID
----------
1
2
3
4
5

SQL> create restore point rsp; 

Restore point created.

SQL> desc v$restore_point
Name                                        Null?      Type
-----------------------------------      -------- --------------------
SCN                                                 NUMBER
DATABASE_INCARNATION#                               NUMBER
GUARANTEE_FLASHBACK_DATABASE                        VARCHAR2(3)
STORAGE_SIZE                                        NUMBER
TIME                                                TIMESTAMP(9)
RESTORE_POINT_TIME                                  TIMESTAMP(9)
PRESERVED                                           VARCHAR2(3)
NAME                                                VARCHAR2(128)
PDB_RESTORE_POINT                                   VARCHAR2(3)
CLEAN_PDB_RESTORE_POINT                             VARCHAR2(3)
PDB_INCARNATION#                                    NUMBER
CON_ID                                              NUMBER

SQL> select name,scn from v$restore_point;

NAME                                          SCN
--------------------                       ----------
RSP                                           3096868
RSP1                                          3098148
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.

SQL> conn / as sysdba
Connected.


SQL> flashback database to restore point rsp;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00942: table or view does not exist

Flashback can work in different situations:

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
SQL> flashback database to scn 3096868;

Flashback complete.

SQL> flashback database to before scn 3096868;

Flashback complete.

SQL> flashback database to restore point rsp1;

Flashback complete.

Flashback of a PDB differ on whether local undo mode or not.

SQL> conn / as sysdba 
Connected.

SQL> alter pluggable database pdb5 close;

Pluggable database closed.

SQL>flashback pluggable database pdb5 to restore point rsp;

Flashback complete.

SQL> alter pluggable database pdb5 open resetlogs;

Database altered.

Flashback can work in different situations:

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
SQL> flashback database pdb5 to scn 3196745;

Flashback complete.

SQL> flashback database pdb5 to before scn 3196745;

Flashback complete.

SQL> flashback database pdb5 to restore point rsp1;

Flashback complete.
Flashback a Pluggable Database:
  • create a restore point.
  • connect inside the PDB and make changes.
  • Flashback the PDB to the restore point.
  • Check the table content is missing.
SQL> startup 
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8795904 bytes
Variable Size 671090944 bytes
Database Buffers 1828716544 bytes
Redo Buffers 7979008 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> conn / as sysdba
Connected.

SQL> create restore point rsp2 for pluggable database pdb5;

Restore point created.

SQL> conn hari/hari@pdb5
Connected.

SQL> select * from t1; 

ID 
---------- 
1
2 
3 
4 
5

SQL> conn / as sysdba
Connected.

SQL> alter pluggable database pdb5 close;

Pluggable database closed.

SQL> flashback pluggable database pdb5 to restore point rsp2;

Flashback complete.

SQL> alter pluggable database pdb5 open resetlogs;

Pluggable database altered. 


SQL> conn hari/hari@pdb5
Connected.

SQL> select * from t1; 
select * from t1 
* 
ERROR at line 1: 
ORA-00942: table or view does not exist


 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

Oracle Automatic Diagnostic Repository

ADRCI (AUTOMATIC DIAGNOSTIC REPOSITORY COMMAND INTERPRETER) :-

ADRCI is a command-line tool that is part of the fault diagnosability infrastructure introduced in Oracle Database Release 11g. ADRCI enables you to:

  1. View diagnostic data within the Automatic Diagnostic Repository (ADR).
  2. View Health Monitor reports.
  3. Package incident and problem information into a zip file for transmission to Oracle Support.
  • The ADR is a file-based repository for database diagnostic data such as traces, dumps, the alert log, health monitor reports, and more.
  • It has a unified directory structure across multiple instances and multiple products.
  • Beginning with Release 11g, the database, Automatic Storage Management (ASM), and other Oracle products or components store all diagnostic data in the ADR.
  • Each instance of each product stores diagnostic data underneath its own ADR home directory.
  • For example,In an Oracle Real Application Clusters environment with shared storage and ASM, each database instance and each ASM instance has a home directory within the ADR. The ADR’s unified directory structure enables customers and Oracle Support to correlate and analyze diagnostic data across multiple instances and multiple products.
  • Most of the diagnostic logs stored in the admin directory in Oracle 10g and earlier has now been moved to the Automatic Diagnostic Repository,or ADR directory, in Oracle 11g.  The ADR directory is created in $ORACLE_BASE/diag and contains a subdirectory for each different Oracle installation type on the system and may also contain some empty directories for products which are not installed.  Within the database, the ADR location is defined by the diagnostic_dest initialization parameter, and the background_dump_dest, user_dump_dest and similar parameters have been deprecated.

Definitions :

  • Automatic Diagnostic Repository (ADR)
  • Problem
  • Incident
  • Problem Key
  • Incident Package
  • Finalizing
  • ADR Home
  • ADR Base
  • Homepath

ADR directory structure :

Subdirectories Under ADR Base :

ADR repository location using V$DIAG_INFO view :

Diagnostic_dest location :-

SQL> show parameter diagnostic_dest;

NAME            TYPE    VALUE
--------------- ------- ------------------
diagnostic_dest string  /oradb/app/oracle

ADRCI command interpreter :

Viewing the alert log:-

One is present in the conventional text format, per OFA in $ORACLE_BASE/diag/rdbms/name of the db/name of the instance/trace This location is determined by the new initialization parameter DIAGNOSTIC_DEST, while BACKGROUND_DUMP_DEST is deprecated in 11g.

The other one is in XML format placed in $ORACLE_BASE/diag/rdbms/name of the db/name of the instance/alert.

[oracle@orcl:~ orcldemo] adrci

ADRCI: Release 12.2.0.1.0 - Production on Wed Oct 24 21:15:43 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.


ADR base = "/oradb/app/oracle"
adrci> show homes;
ADR Homes:
diag/clients/user_oracle/host_680780456_107
diag/rdbms/orcldemo/orcldemo
diag/rdbms/orcl/orcl
diag/tnslsnr/orcl/listener


adrci> set home diag/rdbms/orcldemo/orcldemo


adrci> show alert -tail 20
2018-10-24 03:36:06.135000 +05:30
Warning: VKTM detected a forward time drift.
Time drifts can result in unexpected behavior such as time-outs.
Please see the VKTM trace file for more details:
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
2018-10-24 05:33:24.412000 +05:30
Warning: VKTM detected a forward time drift.
Please see the VKTM trace file for more details:
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
2018-10-24 12:41:59.003000 +05:30
Warning: VKTM detected a forward time drift.
Time drifts can result in unexpected behavior such as time-outs.
Please see the VKTM trace file for more details:
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
2018-10-24 15:31:50.376000 +05:30
Resize operation completed for file# 3, old size 1085440K, new size 1095680K
2018-10-24 16:59:56.481000 +05:30
Warning: VKTM detected a forward time drift.
Time drifts can result in unexpected behavior such as time-outs.
Please see the VKTM trace file for more details:
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
2018-10-24 17:59:55.271000 +05:30
Warning: VKTM detected a forward time drift.
Time drifts can result in unexpected behavior such as time-outs.
Please see the VKTM trace file for more details:
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
adrci> show alert -p "message_text like '%incident%'"

ADR Home = /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo:
****************************************************************
Output the results to file: /tmp/alert_47719_1400_orcldemo_3.ado
Eg:-
  show alert
  show alert -p "message_text like '%incident%'"
  show alert -tail 20

Viewing Trace files :

To directly viewed the trace file using the SHOW_TRACE command.

adrci> show trace /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
Output the results to file: /tmp/utsout_47719_14007_5.ado
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
----------------------------------------------------------
LEVEL PAYLOAD 
----- ------------------------------------------------------------------------------------------------------------------------------------------------
Trace file /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_vktm_4748.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME: /oradb/app/oracle/product/12.2.0.1/db_1
System name: Linux
Node name: orcl.localdomain.com
Release: 2.6.32-696.el6.x86_64
Version: #1 SMP Tue Mar 21 19:29:05 UTC 2017
Machine: x86_64
Instance name: orcldemo
Redo thread mounted by this instance: 0 <none>
Oracle process number: 5
Unix process pid: 4748, image: oracle@orcl.localdomain.com (VKTM)


*** 2018-10-15T23:33:55.572017+05:30
*** SESSION ID:(5.6196) 2018-10-15T23:33:55.572050+05:30
*** CLIENT ID:() 2018-10-15T23:33:55.572056+05:30
*** SERVICE NAME:() 2018-10-15T23:33:55.572061+05:30
*** MODULE NAME:() 2018-10-15T23:33:55.572068+05:30
*** ACTION NAME:() 2018-10-15T23:33:55.572073+05:30
*** CLIENT DRIVER:() 2018-10-15T23:33:55.572077+05:30

kstmmainvktm: succeeded in setting elevated priority
highres_disabled
VKTM running at (100ms) precision
kstmrmtickcntkeeper: param _dbrm_quantum will not be effective
[Start] HighResTick = 743299503
kstmrmtickcnt = 0, ksudbrmseccnt[0] = 1539626635
kstmchkdrift (kstmrmtickcntkeeper:highres): Time jumped forward by (1796157)usec at (15368631714) whereas (1000000) is allowed

Purging tracefiles :

Database will creates lots of tracefiles that need to be purged from time to time. In fact, this is done automatically, but you may want to change the default purge policy:

The ordinary tracefiles will stay for 30 days (720 hours), while files like incident files stay one year (8760 hours) by default. We can change that policy,

Purging tracefiles manually :

adrci> purge -age 1440   (for older than 1 days)

Problem :

  • A problem is a critical error in the database. Critical errors include internal errors such as ORA-00600 and other severe errors such as ORA-07445 (operating system exception) or ORA-04031 (out of memory in the shared pool).
  • Problems are tracked in the ADR. Each problem has a problem key and a unique problem ID.

To view the problem occurred in database from ADRCI command line,type SHOW PROBLEM

 

INCIDENT :

  • An incident is a single occurrence of a problem. When a problem occurs multiple times, an incident is created for each occurrence. Incidents are tracked in the ADR. Each incident is identified by a numeric incident ID, which is unique within the ADR. When an incident occurs, the database makes an entry in the alert log, sends an incident alert to Oracle Enterprise Manager, gathers diagnostic data about the incident in the form of dump files (incident dumps), tags the incident dumps with the incident ID, and stores the incident dumps in an ADR subdirectory created for that incident.
  • Diagnosis and resolution of a critical error usually starts with an incident alert. You can obtain a list of all incidents in the ADR with an ADRCI command. Each incident is mapped to a single problem only.
  • Incidents are flood-controlled so that a single problem does not generate too many incidents and incident dumps.

 

adrci> show incident -mode detail -p "incident_id=12209";

ADR Home = /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo:
**********************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID 12209
STATUS ready
CREATE_TIME 2018-09-19 05:07:14.674000 +05:30
PROBLEM_ID 1
CLOSE_TIME <NULL>
FLOOD_CONTROLLED none
ERROR_FACILITY ORA
ERROR_NUMBER 600
ERROR_ARG1 ktcrcm: caller passed invalid xcb
ERROR_ARG2 4
ERROR_ARG3 0x06CF235C0
ERROR_ARG4 0x000000000
ERROR_ARG5 1
ERROR_ARG6 4232
ERROR_ARG7 <NULL>
ERROR_ARG8 <NULL>
ERROR_ARG9 <NULL>
ERROR_ARG10 <NULL>
ERROR_ARG11 <NULL>
ERROR_ARG12 <NULL>
SIGNALLING_COMPONENT <NULL>
SIGNALLING_SUBCOMPONENT <NULL>
SUSPECT_COMPONENT <NULL>
SUSPECT_SUBCOMPONENT <NULL>
ECID <NULL>
IMPACTS 0
CON_UID 0
PROBLEM_KEY ORA 600 [ktcrcm: caller passed invalid xcb]
FIRST_INCIDENT 12209
FIRSTINC_TIME 2018-09-19 05:07:14.674000 +05:30
LAST_INCIDENT 14407
LASTINC_TIME 2018-09-19 05:56:05.333000 +05:30
IMPACT1 0
IMPACT2 0
IMPACT3 0
IMPACT4 0
KEY_NAME Service
KEY_VALUE orcldemo.localdomain.com
KEY_NAME Module
KEY_VALUE oracle@test.localdomain.com (TNS V1-V3)
KEY_NAME ProcId
KEY_VALUE 26.141
KEY_NAME Client ProcId
KEY_VALUE oracle@orcl.localdomain.com (TNS V1-V3).13694_139900975931168
KEY_NAME PQ
KEY_VALUE (0, 1537313832)
KEY_NAME SID
KEY_VALUE 65.20850
OWNER_ID 1
INCIDENT_FILE /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_ora_13694.trc
OWNER_ID 1
INCIDENT_FILE /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/incident/incdir_12209/orcldemo_ora_13694_i 12209.trc
1 row fetched

Show incident tracefile

adrci> show tracefile -t /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/incident/incdir_12209/orcldemo_ora_13694_i12209.trc
DIA-48415: Syntax error found in string [show tracefile -t /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/incident/incdir_12209/orcldemo_ora_13694_i12209.trc] at column [116]

adrci> show trace /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/incident/incdir_12209/orcldemo_ora_13694_i12209.trc
Output the results to file: /tmp/utsout_49795_13979_4.ado
/oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/incident/incdir_12209/orcldemo_ora_13694_i12209.trc
----------------------------------------------------------
LEVEL PAYLOAD 
----- ------------------------------------------------------------------------------------------------------------------------------------------------
Dump file /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/incident/incdir_12209/orcldemo_ora_13694_i12209.trc
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125
ORACLE_HOME: /oradb/app/oracle/product/12.2.0.1/db_1
System name: Linux
Node name: orcl.localdomain.com
Release: 2.6.32-696.el6.x86_64
Version: #1 SMP Tue Mar 21 19:29:05 UTC 2017
Machine: x86_64
Instance name: orcldemo
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 13694, image: oracle@orcl.localdomain.com


*** 2018-09-19T05:07:14.933565+05:30
*** SESSION ID:(65.20850) 2018-09-19T05:07:14.933582+05:30
*** CLIENT ID:() 2018-09-19T05:07:14.933587+05:30
*** SERVICE NAME:(orcldemo.localdomain.com) 2018-09-19T05:07:14.933593+05:30
*** MODULE NAME:(oracle@test.localdomain.com (TNS V1-V3)) 2018-09-19T05:07:14.933599+05:30
*** ACTION NAME:() 2018-09-19T05:07:14.933604+05:30
*** CLIENT DRIVER:() 2018-09-19T05:07:14.933608+05:30

[TOC00000]
Jump to table of contents
Dump continued from file: /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo/trace/orcldemo_ora_13694.trc
[TOC00001]
1> ***** Error Stack *****
ORA-00600: internal error code, arguments: [ktcrcm: caller passed invalid xcb], [4], [0x06CF235C0], [0x000000000], [1], [4232], [], [], [], [],
[], []

Incident Package :

  • An incident package (package) is a collection of data about incidents for one or more problems. Before sending incident data to Oracle Support it must be collected into a package using the Incident Packaging Service (IPS). After a package is created, you can add external files to the package, remove selected files from the package, or scrub (edit) selected files in the package to remove sensitive data.
  • A package is a logical construct only, until you create a physical file from the package contents. That is, an incident package starts out as a collection of metadata in the Automatic Diagnostic Repository (ADR). As you add and remove package contents, only the metadata is modified. When you are ready to upload the data to Oracle Support, you create a physical package using ADRCI, which saves the data into a zip file.

Creation of Packages & ZIP files to send to Oracle Support :

Get Problem ID from incident output.

adrci> show incident -mode detail -p "incident_id=12209";

ADR Home = /oradb/app/oracle/diag/rdbms/orcldemo/orcldemo:
*************************************************************************

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID 12209
STATUS ready
CREATE_TIME 2018-09-19 05:07:14.674000 +05:30
PROBLEM_ID 1

Gather all the required information with a method called “Incident Packaging Service” (IPS).

adrci> ips create package problem 1 correlate all
Created package 1 based on problem id 1, correlation level all

Create a ZIP file using logical package

adrci> ips generate package 1 in "/home/oracle"
Generated package 1 in file /home/oracle/ORA600ktc_20181024223141_COM_1.zip, mode complete

The package is now zipped and ready to upload to Oracle Support.

[oracle@orcl:~ orcldemo] ls -lrt ORA600*

-rw-r--r-- 1 oracle oinstall 5452175 Oct 24 22:33 /home/oracle/ORA600ktc_20181024223141_COM_1.zip

 

Examples :-

  • At the ADRCI prompt, enter the following command:

IPS CREATE PACKAGE INCIDENT incident_number

For example, the following command creates a package based on incident 3:

IPS CREATE PACKAGE INCIDENT 3

ADRCI generates output similar to the following:

Created package 10 based on incident id 3, correlation level typical The package number assigned to this logical package is 10.

The following are variations on the IPS CREATE PACKAGE command:

  IPS CREATE PACKAGE

This creates an empty package. You must use the IPS ADD INCIDENT or IPS ADD FILE commands to add diagnostic data to the package before generating it. 

  IPS CREATE PACKAGE PROBLEM problem_ID

This creates a package and includes diagnostic information for incidents that reference the specified problem ID. 

  IPS CREATE PACKAGE SECONDS sec

This creates a package and includes diagnostic information for all incidents that occurred from sec seconds ago until now. sec must be an integer. 

  IPS CREATE PACKAGE TIME ‘start_time’ TO ‘end_time’

  This creates a package and includes diagnostic information for all incidents that occurred within the specified time range. start_time and end_time must be in the format ‘YYYY-MM-DD HH24:MI:SS.FF TZR’. This is a valid format string for the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.

Finalizing :

  • Before ADRCI can generate a physical package from a logical package, the package must be finalized. This means that other components are called to add any correlated diagnostic data files to the incidents already in this package.
  • Finalizing also adds recent trace files, alert log entries, Health Monitor reports, SQL test cases, and configuration information. This step is run automatically when a physical package is generated, and can also be run manually using the ADRCI utility. After manually finalizing a package, you can review the files that were added and then remove or edit any that contain sensitive information.

ADR Home :

  • An ADR home is the root directory for all diagnostic data—traces, dumps, alert log, and so on—for a particular instance of a particular Oracle product or component.
  • For example, in a Real Application Clusters environment with ASM, each database instance and each ASM instance has an ADR home. All ADR homes share the same hierarchical directory structure. Some of the standard subdirectories in each ADR home include alert (for the alert log), trace (for trace files), and incident (for incident information). All ADR homes are located within the ADR base directory.
  • Some ADRCI commands can work with multiple ADR homes simultaneously. The current ADRCI homepath determines the ADR homes that are searched for diagnostic data when an ADRCI command is issued.

ADR Base :

  • To permit correlation of diagnostic data across multiple ADR homes, ADR homes are grouped together under the same root directory called the ADR base.
  • For example, in an Oracle Real Application Clusters (RAC) environment, the ADR base could be on a shared disk, and the ADR home for each Oracle RAC instance could be located under this ADR base.
  • The location of the ADR base for a database instance is set by the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted or is null, the database sets it to a default value.
  • When multiple database instances share an Oracle home, whether they are multiple single instances or the instances of an Oracle Real Application Clusters database, and when one or more of these instances set ADR base in different locations, the last instance to start up determines the default ADR base for ADRCI.

Homepath :

  • All ADRCI commands operate on diagnostic data in the “current” ADR homes. More than one ADR home can be current at any one time. Some ADRCI commands (such as SHOW INCIDENT) search for and display diagnostic data from all current ADR homes, while other commands require that only one ADR home be current, and display an error message if more than one is current.
  • The ADRCI homepath determines the ADR homes that are current. It does so by pointing to a directory within the ADR base hierarchy. If it points to a single ADR home directory, that ADR home is the only current ADR home. If the homepath points to a directory that is above the ADR home directory level in the hierarchy, all ADR homes that are below the directory that is pointed to become current.
  • The homepath is null by default when ADRCI starts. This means that all ADR homes under ADR base are current.
  • The SHOW HOME and SHOW HOMEPATH commands display a list of the ADR homes that are current, and the SET HOMEPATH command sets the homepath.
Catch Me On:- Hariprasath Rajaram

LinkedIn:      https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:      https://www.facebook.com/HariPrasathdba
Facebook Group:https://www.facebook.com/groups/894402327369506/
Facebook Page: https://www.facebook.com/dbahariprasath/?
Twitter:       https://twitter.com/hariprasathdba

 

Analyze Oracle Server Diagnostic Information Using OSWatcher Tool

OSWatcher:

Oracle OSWatcher Black Box (OSWbb) collects and archives operating system and network metrics that you can use to diagnose performance issues.

OSWbb operates as a set of background processes on the server and gathers data on a regular basis, invoking such Unix utilities as vmstat, netstat, iostat, and top.

OSWatcher includes a File Manager process that will run once per hour to clean up any data files older than the retention period.

The collection interval and retention period can be changed with the first two parameters, respectively, to the shell script that starts OSWatcher.

OSWbb is particularly useful for Oracle RAC (Real Application Clusters) and Oracle Grid Infrastructure configurations.

The RAC-DDT (Diagnostic Data Tool) script file includes OSWbb, but does not install it by default.

Installing OSWbb

To install OSWbb:

[root@ram db_1]# tar -xvf oswbb812.tar 
oswbb/
oswbb/sarsub.sh
oswbb/psmemsub.sh
oswbb/stopOSWbb.sh
oswbb/analysis/
oswbb/docs/
oswbb/docs/OSWatcher/
oswbb/docs/OSWatcher/oswbb_README.txt
oswbb/docs/OSWatcher/RAC_Traceroute_Automation_Readme.txt
oswbb/docs/OSWatcher/OSWatcherUserGuide.html
oswbb/docs/The_Analyzer/
oswbb/docs/The_Analyzer/AnalyzerUserGuide.html
oswbb/docs/The_Analyzer/AnalyzerWalkThrough.pdf
oswbb/docs/The_Analyzer/oswbba_README.txt
oswbb/docs/OSWatcherOverview090517.pdf
oswbb/Exampleprivate.net
oswbb/OSWatcher.sh~
oswbb/tmp/
oswbb/oswbba.jar
oswbb/oswib.sh
oswbb/ltop.sh
oswbb/oswnet.sh
oswbb/call_uptime.sh
oswbb/OSWatcher.sh
oswbb/vmsub.sh
oswbb/src/
oswbb/src/js/
oswbb/src/js/bootstrap.js
oswbb/src/js/modernizr.js
oswbb/src/js/jquery-2.1.4.min.js
oswbb/src/js/THIRDPARTYLICENSE.txt
oswbb/src/js/jquery.lightbox.js
oswbb/src/js/tabs.js
oswbb/src/js/scripts.js
oswbb/src/js/templatemo_custom.js
oswbb/src/Thumbs.db
oswbb/src/fonts/
oswbb/src/fonts/FontAwesome.otf
oswbb/src/fonts/fontawesome-webfont.woff
oswbb/src/fonts/fontawesome-webfont.woff2
oswbb/src/fonts/fontawesome-webfont.eot
oswbb/src/fonts/fontawesome-webfont.ttf
oswbb/src/fonts/fontawesome-webfont.svg
oswbb/src/META-INF/
oswbb/src/META-INF/application-client.xml
oswbb/src/images/
oswbb/src/images/Thumbnail_Placeholder.png
oswbb/src/images/next.png
oswbb/src/images/bx_loader.gif
oswbb/src/images/close.png
oswbb/src/images/loading.gif
oswbb/src/images/previous.png
oswbb/src/css/
oswbb/src/css/templatemo_misc.css
oswbb/src/css/bootstrap.min.css
oswbb/src/css/style.css
oswbb/src/css/font-awesome.min.css
oswbb/src/css/templatemo_style.css
oswbb/src/css/animate.css
oswbb/src/oswbba_input.txt
oswbb/topaix.sh
oswbb/iosub.sh
oswbb/oswsub.sh
oswbb/nfssub.sh
oswbb/xtop.sh
oswbb/genprvnet.sh
oswbb/oswrds.sh
oswbb/call_sar.sh
oswbb/call_du.sh
oswbb/ifconfigsub.sh
oswbb/mpsub.sh
oswbb/Example_extras.txt
oswbb/locks/
oswbb/OSWatcherFM.sh
oswbb/tar_up_partial_archive.sh
oswbb/data/
oswbb/tar_up_full_archive.sh
oswbb/gif/
oswbb/startOSWbb.sh
[root@ram db_1]#
  •  If the ksh package is not already installed on your system, use yum to install it.
[root@ram db_1]# yum install ksh
Loaded plugins: refresh-packagekit, security
Setting up Install Process
Trying other mirror.
Package ksh-20120801-37.el6_9.x86_64 already installed and latest version
Nothing to do

[root@ram db_1]#[root@bash oswbb]# ls -lrth
total 544K
-rwxr-xr-x. 1 2052 8500  524 Oct  7  2013 oswsub.sh
-rwxr-xr-x. 1 2052 8500  825 Oct  7  2013 oswrds.sh
-rwxr-xr-x. 1 2052 8500  414 Oct  7  2013 oswib.sh
-rwxr-xr-x. 1 2052 8500  740 Oct  7  2013 nfssub.sh
-rwxr-xr-x. 1 2052 8500  542 Oct  7  2013 mpsub.sh
-rwxr-xr-x. 1 2052 8500  743 Oct  7  2013 iosub.sh
-rwxr-xr-x. 1 2052 8500 1.9K Oct  7  2013 Exampleprivate.net
-rwxr-xr-x. 1 2052 8500   68 Oct  7  2013 call_sar.sh
-rwxr-xr-x. 1 2052 8500   71 Jan  7  2014 call_uptime.sh
-rwxr-xr-x. 1 2052 8500  435 Jan  8  2014 oswnet.sh
-rwxr-xr-x. 1 2052 8500 1.5K Jan  8  2014 ltop.sh
-rwxr-xr-x. 1 2052 8500   67 Jan 15  2014 call_du.sh
-rwxr-xr-x. 1 2052 8500  772 May  8  2014 ifconfigsub.sh
-rwxr-xr-x. 1 2052 8500  545 Feb 23  2015 vmsub.sh
-rwxr-xr-x. 1 2052 8500 2.6K Feb 26  2015 startOSWbb.sh
-rwxr-xr-x. 1 2052 8500 1.5K Feb 26  2015 xtop.sh
-rwxr-xr-x. 1 2052 8500 5.3K Feb  7  2017 tar_up_partial_archive.sh
-rwxr-xr-x. 1 2052 8500  834 Feb  7  2017 tar_up_full_archive.sh
-rwxr-xr-x. 1 2052 8500  665 Feb  7  2017 Example_extras.txt
-rwxr-xr-x. 1 2052 8500  527 Feb  8  2017 topaix.sh
-rwxr-xr-x. 1 2052 8500 3.9K Apr 18  2017 genprvnet.sh
drwx------. 2 2052 8500 4.0K May  3  2017 data
-rwxr-xr-x. 1 2052 8500  557 Jul 12  2017 sarsub.sh
-rwxr-xr-x. 1 2052 8500 6.7K Jul 12  2017 OSWatcherFM.sh
-rwxr-xr-x. 1 2052 8500  751 Aug 15  2017 stopOSWbb.sh
-rw-r--r--. 1 2052 8500  47K Aug 15  2017 OSWatcher.sh~
-rwxr-xr-x. 1 2052 8500 1.5K Aug 25  2017 psmemsub.sh
drwx------. 4 2052 8500 4.0K Sep  5  2017 docs
drwx------. 2 2052 8500 4.0K Sep  6  2017 gif
drwx------. 7 2052 8500 4.0K Sep  6  2017 src
-rwxr-xr-x. 1 2052 8500  47K Dec 13  2017 OSWatcher.sh
-rw-r--r--. 1 2052 8500 310K Dec 13  2017 oswbba.jar
drwxr-xr-x. 2 2052 8500 4.0K Dec 13  2017 tmp
drwxr-xr-x. 2 2052 8500 4.0K Dec 13  2017 locks
drwxr-xr-x. 2 2052 8500 4.0K Dec 13  2017 analysis

To start OSWbb, run the startOSWbb.sh script from the oswbb directory.

[root@bash oswbb]# ./startOSWbb.sh 
[root@bash oswbb]# 
Info...You did not enter a value for snapshotInterval.
Info...Using default value = 30
Info...You did not enter a value for archiveInterval.
Info...Using default value = 48
Setting the archive log directory to/u01/app/irfan/product/12.2.0/dbhome_1/oswbb/archive

Testing for discovery of OS Utilities...
VMSTAT found on your system.
IOSTAT found on your system.
MPSTAT found on your system.
IFCONFIG found on your system.
NETSTAT found on your system.
TOP found on your system.
TRACEROUTE found on your system.

Discovery of CPU CORE COUNT
CPU CORE COUNT will be used by oswbba to automatically look for cpu problems

CPU CORE COUNT = 2
VCPUS/THREADS = 2

Discovery completed.

Starting OSWatcher v8.1.2  on Sat Oct 27 01:41:22 IST 2018
With SnapshotInterval = 30
With ArchiveInterval = 48

OSWatcher - Written by Carl Davis, Center of Expertise,
Oracle Corporation
For questions on install/usage please go to MOS (Note:301137.1)
If you need further assistance or have comments or enhancement
requests you can email me Carl.Davis@Oracle.com

Data is stored in directory: /u01/app/irfan/product/12.2.0/dbhome_1/oswbb/archive

Starting Data Collection...

oswbb heartbeat:Sat Oct 27 01:41:27 IST 2018
oswbb heartbeat:Sat Oct 27 01:41:57 IST 2018
oswbb heartbeat:Sat Oct 27 01:42:27 IST 2018
oswbb heartbeat:Sat Oct 27 01:42:57 IST 2018
^C
[root@bash oswbb]# ./stopOSWbb.sh 
[root@bash oswbb]#

To stop OSWbb permanently, run the stopOSWbb.sh script from the oswbb directory.

To View The Collected Data

Collected data will be stored under archive directory, it is created when OSWbb is started for the first time. OSWbb stores data in hourly archive files named system_name_utility_name_timestamp.dat, and each entry in a file is preceded by the characters *** and a timestamp.

To check any data, just navigate to corresponding directory and view the corresponding file. For testing purpose, we printed vmstat file output.

OSWbb collects data in the following directories under the oswbb/archive directory:

Directory Description
oswiostat Contains output from the iostat utility.
oswmeminfo Contains a listing of the contents of /proc/meminfo.
oswmpstat Contains output from the mpstat utility.
oswnetstat Contains output from the netstat utility.
oswprvtnet If you have enable private network tracing for RAC, contains information about the status of the private networks.
oswps Contains output from the ps utility.
oswslabinfo Contains a listing of the contents of /proc/slabinfo.
oswtop Contains output from the top utility.
oswvmstat Contains output from the vmstat utility.

 

 

[root@bash tfa]# ls -lrth
total 266M
-rwxrwxr-x. 1 irfan irfan 266M Oct 27 00:43 TFA-LINUX_v18.3.3.zip
[root@bash tfa]# unzip TFA-LINUX_v18.3.3.zip 
Archive: TFA-LINUX_v18.3.3.zip
inflating: README.txt 
inflating: installTFA-LINUX 
[root@bash tfa]# ls -lrth
total 532M
-rwxr-xr-x. 1 root root 267M Oct 5 23:39 installTFA-LINUX
-rw-r--r--. 1 root root 1.5K Oct 5 23:41 README.txt
-rwxrwxr-x. 1 irfan irfan 266M Oct 27 00:43 TFA-LINUX_v18.3.3.zip
[root@bash tfa]# ./installTFA-LINUX 
TFA Installation Log will be written to File : /tmp/tfa_install_6496_2018_10_27-00_49_26.log

Starting TFA installation

TFA Version: 183300 Build Date: 201810050542

Enter a location for installing TFA (/tfa will be appended if not supplied) [/u01/app/irfan/product/12.2.0/dbhome_1/tfa]:
/u01/app/irfan/product/12.2.0/dbhome_1/tfa/

Running Auto Setup for TFA as user root...

Would you like to do a [L]ocal only or [C]lusterwide installation ? [L|l|C|c] [C] : L
Installing TFA now...

Discovering Nodes and Oracle resources

Starting Discovery...


No Grid Infrastructure Discovered on this system . . . . .

bash
Searching for running databases...
1. IRFAN


Searching out ORACLE_HOME for selected databases...


Getting Oracle Inventory...

ORACLE INVENTORY: /u01/app/oraInventory


Discovery Complete...

TFA Will be Installed on bash...

TFA will scan the following Directories
++++++++++++++++++++++++++++++++++++++++++++

.-----------------------------------------------------------------.
| bash |
+------------------------------------------------------+----------+
| Trace Directory | Resource |
+------------------------------------------------------+----------+
| /u01/app/irfan/cfgtoollogs | CFGTOOLS |
| /u01/app/irfan/diag/rdbms/clone/CLONE/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/clone/CLONE/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/clone/clone/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/clone/clone/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/crcc_pitr_irfan/Crcc/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/crcc_pitr_irfan/Crcc/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/iobh_pitr_irfan/ioBh/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/iobh_pitr_irfan/ioBh/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/irfan/IRFAN/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/irfan/IRFAN/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/ivee_pitr_irfan/ivEE/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/ivee_pitr_irfan/ivEE/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/macaw/IRFAN/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/macaw/IRFAN/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/nrsd_pitr_irfan/nrsd/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/nrsd_pitr_irfan/nrsd/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/orcl/orcl/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/orcl/orcl/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/osiz_pitr_irfan/osiz/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/osiz_pitr_irfan/osiz/trace | RDBMS |
| /u01/app/irfan/diag/rdbms/wfha_pitr_irfan/wfhA/cdump | RDBMS |
| /u01/app/irfan/diag/rdbms/wfha_pitr_irfan/wfhA/trace | RDBMS |
| /u01/app/irfan/diag/tnslsnr | TNS |
| /u01/app/irfan/product/12.2.0/dbhome_1/cfgtoollogs | CFGTOOLS |
| /u01/app/irfan/product/12.2.0/dbhome_1/install | INSTALL |
| /u01/app/irfan/product/12.2.0/dbhome_1/rdbms/log | RDBMS |
| /u01/app/irfan/product/12.2.0/dbhome_2/cfgtoollogs | CFGTOOLS |
| /u01/app/irfan/product/12.2.0/dbhome_2/install | INSTALL |
| /u01/app/oraInventory/ContentsXML | INSTALL |
| /u01/app/oraInventory/logs | INSTALL |
'------------------------------------------------------+----------'


Installing TFA on bash:
HOST: bash TFA_HOME: /u01/app/irfan/product/12.2.0/dbhome_1/tfa/bash/tfa_home

.-------------------------------------------------------------------------.
| Host | Status of TFA | PID | Port | Version | Build ID |
+------+---------------+------+-------+------------+----------------------+
| bash | RUNNING | 7102 | 55356 | 18.3.3.0.0 | 18330020181005054218 |
'------+---------------+------+-------+------------+----------------------'

Running Inventory in All Nodes...

Enabling Access for Non-root Users on bash...
ERROR: /u01/app/irfan/product/12.2.0/dbhome_1/tfa/bash/tfa_home/internal/cached_kv.out does not exists

Adding default users to TFA Access list...

Summary of TFA Installation:
.--------------------------------------------------------------------------------.
| bash |
+---------------------+----------------------------------------------------------+
| Parameter | Value |
+---------------------+----------------------------------------------------------+
| Install location | /u01/app/irfan/product/12.2.0/dbhome_1/tfa/bash/tfa_home |
| Repository location | /u01/app/irfan/product/12.2.0/dbhome_1/tfa/repository |
| Repository usage | 0 MB out of 4876 MB |
'---------------------+----------------------------------------------------------'

TFA is successfully installed...

Usage : /u01/app/irfan/product/12.2.0/dbhome_1/tfa/bin/tfactl <command> [options]
commands:diagcollect|collection|analyze|ips|run|start|stop|enable|disable|status|print|access|purge|directory|host|receiver|set|toolstatus|uninstall|diagnosetfa|syncnodes|setupmos|upload|availability|rest|events|search|changes|isa
For detailed help on each command use:
/u01/app/irfan/product/12.2.0/dbhome_1/tfa/bin/tfactl <command> -help





 

 

 

[irfan@bash bin]$ tfactl print version
TFA Version : 18.3.3.0.0



[irfan@bash bin]$ tfactl toolstatus

.------------------------------------------------------------------.
| TOOLS STATUS - HOST : bash |
+----------------------+--------------+--------------+-------------+
| Tool Type | Tool | Version | Status |
+----------------------+--------------+--------------+-------------+
| Development Tools | orachk | 12.2.0.1.3 | DEPLOYED |
| | oratop | 14.1.2 | DEPLOYED |
+----------------------+--------------+--------------+-------------+
| Support Tools Bundle | darda | 2.10.0.R6036 | DEPLOYED |
| | oswbb | 8.1.2 | RUNNING |
| | prw | 12.1.13.11.4 | NOT RUNNING |
+----------------------+--------------+--------------+-------------+
| TFA Utilities | alertsummary | 12.2.1.1.0 | DEPLOYED |
| | calog | 12.2.0.1.0 | DEPLOYED |
| | dbcheck | 18.3.0.0.0 | DEPLOYED |
| | dbglevel | 12.2.1.1.0 | DEPLOYED |
| | grep | 12.2.1.1.0 | DEPLOYED |
| | history | 12.2.1.1.0 | DEPLOYED |
| | ls | 12.2.1.1.0 | DEPLOYED |
| | managelogs | 12.2.1.1.0 | DEPLOYED |
| | menu | 12.2.1.1.0 | DEPLOYED |
| | param | 12.2.1.1.0 | DEPLOYED |
| | ps | 12.2.1.1.0 | DEPLOYED |
| | pstack | 12.2.1.1.0 | DEPLOYED |
| | summary | 12.2.1.1.0 | DEPLOYED |
| | tail | 12.2.1.1.0 | DEPLOYED |
| | triage | 12.2.1.1.0 | DEPLOYED |
| | vi | 12.2.1.1.0 | DEPLOYED |
'----------------------+--------------+--------------+-------------'

Note :-
DEPLOYED : Installed and Available - To be configured or run interactively.
NOT RUNNING : Configured and Available - Currently turned off interactively.
RUNNING : Configured and Available.

 

Invoking OSWBB

[irfan@bash bin]$ tfactl oswbb

Starting OSW Analyzer V8.1.2
OSWatcher Analyzer Written by Oracle Center of Expertise
Copyright (c) 2017 by Oracle Corporation

Parsing Data. Please Wait...

Scanning file headers for version and platform info...

Parsing file bash.localhost.com_iostat_18.10.27.0000.dat ...
Parsing file bash.localhost.com_iostat_18.10.27.0100.dat ...
This directory already exists. Rewriting...

Parsing file bash.localhost.com_vmstat_18.10.27.0000.dat ...
Parsing file bash.localhost.com_vmstat_18.10.27.0100.dat ...


Parsing file bash.localhost.com_netstat_18.10.27.0000.dat ...
Parsing file bash.localhost.com_netstat_18.10.27.0100.dat ...

Parsing file bash.localhost.com_top_18.10.27.0000.dat ...
Parsing file bash.localhost.com_top_18.10.27.0100.dat ...

Parsing file bash.localhost.com_ps_18.10.27.0000.dat ...
Parsing file bash.localhost.com_ps_18.10.27.0100.dat ...


Parsing Completed.


Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs

Enter GC to Generate All CPU Gif Files
Enter GM to Generate All Memory Gif Files
Enter GD to Generate All Disk Gif Files
Enter GN to Generate All Network Gif Files

Enter L to Specify Alternate Location of Gif Directory
Enter Z to Zoom Graph Time Scale (Does not change analysis dataset)
Enter B to Returns to Baseline Graph Time Scale (Does not change analysis dataset)
Enter R to Remove Currently Displayed Graphs

Enter X to Export Parsed Data to Flat File
Enter S to Analyze Subset of Data(Changes analysis dataset including graph time scale)
Enter A to Analyze Data
Enter D to Generate DashBoard

Enter Q to Quit Program

Please Select an Option:D
Enter a unique analysis/dashBoard directory name or enter <CR> to accept default name:<CR>

A new analysis file analysis/<CR>/analysis.txt has been created.

Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Run_Queue.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Run_Adjusted_Queue.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Block_Queue.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_HB.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_PS_Processes.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Cpu_Idle.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Cpu_System.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Cpu_User.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Cpu_Wa.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Cpu_Interrupts.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Context_Switches.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Memory_Swap.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Memory_Free.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Memory_Page_In_Rate.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Memory_Page_Out_Rate.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Cpu_Wa.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_Block_Queue.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_IO_ST.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_IO_AW.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_IO_PB.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_IO_RPS.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_IO_WPS.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_OS_IO_TPS.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_rx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_rx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_rx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_rx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_tx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_tx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_tx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_lo_tx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_rx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_rx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_rx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_rx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_tx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_tx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_tx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_eth0_tx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_rx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_rx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_rx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_rx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_tx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_tx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_tx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0_tx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_rx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_rx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_rx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_rx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_tx_ok.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_tx_err.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_tx_drp.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_link_virbr0-nic_tx_ovr.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_requests_sent_out.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_total_packets_received.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_bad_header_checksum.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_fragments_dropped.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_fragments_created.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_fragments_received.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_fragments_dropped_after.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_fragments_warn1.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_ip_fragments_warn2.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_udp_datagrams_in.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_udp_datagrams_out.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_udp_dropped.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_udp_broadcast_dropped.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_udp_socket_overflows.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_udp_bad_header_checksums.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_in_segs.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_out_segs.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_retrans_segs.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_conn_resets_received_segs.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_resets_sent.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_failed_conn_attempts.jpg
Generating file analysis/<CR>/dashboard/generated_files/OSWg_tcp_retran_error_rate.jpg

Enter 1 to Display CPU Process Queue Graphs
Enter 2 to Display CPU Utilization Graphs
Enter 3 to Display CPU Other Graphs
Enter 4 to Display Memory Graphs
Enter 5 to Display Disk IO Graphs

Enter GC to Generate All CPU Gif Files
Enter GM to Generate All Memory Gif Files
Enter GD to Generate All Disk Gif Files
Enter GN to Generate All Network Gif Files

Enter L to Specify Alternate Location of Gif Directory
Enter Z to Zoom Graph Time Scale (Does not change analysis dataset)
Enter B to Returns to Baseline Graph Time Scale (Does not change analysis dataset)
Enter R to Remove Currently Displayed Graphs

Enter X to Export Parsed Data to Flat File
Enter S to Analyze Subset of Data(Changes analysis dataset including graph time scale)
Enter A to Analyze Data
Enter D to Generate DashBoard

Enter Q to Quit Program

Please Select an Option:

Creating the dashboard

If you choose option “D” oswbb will go ahead and create a dashboard.

the result is stored in

$ORACLE_BASE/tfa/repository/suptools/$(host)/oswbb/oracle/oswbb/analysis

[root@bash oswbb]# ls -lrth
total 560K
-rwxr-xr-x. 1 irfan irfan 47K Oct 27 00:52 OSWatcher.sh
-rwxr-xr-x. 1 irfan irfan 545 Oct 27 00:52 vmsub.sh
drwxr-xr-x. 7 irfan irfan 4.0K Oct 27 00:52 src
-rwxr-xr-x. 1 irfan irfan 740 Oct 27 00:52 nfssub.sh
-rwxr-xr-x. 1 irfan irfan 1.5K Oct 27 00:52 ltop.sh
-rwxr-xr-x. 1 irfan irfan 71 Oct 27 00:52 call_uptime.sh
-rwxr-xr-x. 1 irfan irfan 1.5K Oct 27 00:52 xtop.sh
-rwxr-xr-x. 1 irfan irfan 529 Oct 27 00:52 topother.sh
-rwxr-xr-x. 1 irfan irfan 2.6K Oct 27 00:52 startOSWbb.sh
-rwxr-xr-x. 1 irfan irfan 557 Oct 27 00:52 sarsub.sh
-rwxr-xr-x. 1 irfan irfan 524 Oct 27 00:52 oswsub.sh
-rwxr-xr-x. 1 irfan irfan 825 Oct 27 00:52 oswrds.sh
-rwxr-xr-x. 1 irfan irfan 435 Oct 27 00:52 oswnet.sh
-rwxr-xr-x. 1 irfan irfan 414 Oct 27 00:52 oswib.sh
-rwxr-xr-x. 1 irfan irfan 20K Oct 27 00:52 oswbb.pm
-rwxr-xr-x. 1 irfan irfan 542 Oct 27 00:52 mpsub.sh
-rwxr-xr-x. 1 irfan irfan 772 Oct 27 00:52 ifconfigsub.sh
-rwxr-xr-x. 1 irfan irfan 3.9K Oct 27 00:52 genprvnet.sh
-rwxr-xr-x. 1 irfan irfan 1.9K Oct 27 00:52 Exampleprivate.net
-rwxr-xr-x. 1 irfan irfan 665 Oct 27 00:52 Example_extras.txt
-rwxr-xr-x. 1 irfan irfan 67 Oct 27 00:52 call_du.sh
-rwxr-xr-x. 1 irfan irfan 5.3K Oct 27 00:52 tar_up_partial_archive.sh
-rwxr-xr-x. 1 irfan irfan 751 Oct 27 00:52 stopOSWbb.sh
-rwxr-xr-x. 1 irfan irfan 1.5K Oct 27 00:52 psmemsub.sh
-rwxr-xr-x. 1 irfan irfan 310K Oct 27 00:52 oswbba.jar
-rwxr-xr-x. 1 irfan irfan 47K Oct 27 00:52 OSWatcher.sh~
-rwxr-xr-x. 1 irfan irfan 743 Oct 27 00:52 iosub.sh
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 00:52 data
-rwxr-xr-x. 1 irfan irfan 834 Oct 27 00:52 tar_up_full_archive.sh
-rwxr-xr-x. 1 irfan irfan 6.7K Oct 27 00:52 OSWatcherFM.sh
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 00:52 gif
-rwxr-xr-x. 1 irfan irfan 68 Oct 27 00:52 call_sar.sh
drwxr-xr-x. 3 irfan irfan 4.0K Oct 27 01:53 analysis
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 02:02 tmp
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 02:02 locks
[root@bash oswbb]# pwd
/u01/app/irfan/product/12.2.0/dbhome_1/tfa/repository/suptools/bash/oswbb/irfan/oswbb
[root@bash oswbb]# cd analysis/
[root@bash analysis]# ls -lrth
total 4.0K
drwxrwxr-x. 3 irfan irfan 4.0K Oct 27 01:53 <CR>

[root@bash analysis]# cd \<CR\>

[root@bash <CR>]# ls -lrth
total 316K
-rw-rw-r--. 1 irfan irfan 312K Oct 27 01:53 analysis.txt
drwxrwxr-x. 7 irfan irfan 4.0K Oct 27 01:53 dashboard
[root@bash <CR>]# cd dashboard/
[root@bash dashboard]# ls -lrth
total 76K
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 01:53 js
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 01:53 css
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 01:53 images
drwxr-xr-x. 2 irfan irfan 4.0K Oct 27 01:53 fonts
drwxrwxr-x. 2 irfan irfan 4.0K Oct 27 01:53 generated_files
-rw-rw-r--. 1 irfan irfan 54K Oct 27 01:53 index.html


[root@bash dashboard]# cp index.html /home/irfan/Desktop/

The HTML output

 

 

 

 

 

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FBGroup:https://www.facebook.com/groups/894402327369506/ FBPage: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

 

SQL Health Check (SQLHC) Tips And Tricks

SQL Health Check (SQLHC)

Description 

The SQL Tuning Health-Check Script is a tool developed by the Oracle Server Technologies Center of Expertise.

The tool, also known as SQLHC, is used to check the environment in which a single SQL Statement runs, checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed.

SQL Health Check (SQLHC)

Overview: SQLHC checks elements that may affect the performance of the SQL being analyzed, such as Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters. SQLHC is a subset of SQLT, which we will discuss next.  This script is a quick way to diagnose problems without having SQLT installed.

Documentation and Install:SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

Proactive: Use this script to examine SQL that is not running up to standards while testing an upgrade.

Reactive: Use this script to get recommendations on problem SQL after an upgrade.

Caveats: SQLHC does not require any special licensing, it is free and it is designed to run on all systems. If the Diagnostics and Tuning packs are installed, that data will be used by the script. If you are not licensed for Diagnostics and Tuning, answer “NO” to that question in the script to avoid licensing problems.

Parameters Required:
  1. Login to the database server and set the environment used by the Database Instance.
  2.  Download the “sqlhc.zip”  archive file and extract the contents to a suitable directory/folder.
  3. Connect into SQL*Plus as SYS, a DBA account, or a user with access to Data Dictionary views and simply execute the “sqlhc.sql” script. It will request to enter two parameters:
    1. Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
      If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning pack includes Oracle Diagnostics)
    2. A valid SQL_ID for the SQL to be analyzed.

 

Health-checks are performed over:
  • CBO Statistics for schema objects accessed by the one SQL statement being analyzed
  • CBO Parameters
  • CBO System Statistics
  • CBO Data Dictionary Statistics
  • CBO Fixed-objects Statistics
[oracle@ram oracle]$ !sq
sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Wed Oct 24 22:06:19 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name,open_mode from v$database;

NAME        OPEN_MODE
--------- --------------------
TEST        READ WRITE

SQL> create user d1 identified by d1 default tablespace tbsnew;

User created.

SQL> grant dba to d1;

Grant succeeded.

SQL> conn d1/d1
Connected.

SQL> create table t1 (id number,name varchar2(100),salary number);

Table created.


SQL> begin
for i in 1..100000 loop
insert into t1 values(i,'testing',20000);
commit;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL> create table t2 (id number,name varchar2(100),salary number);

Table created.

SQL> begin
for i in 1..1000 loop
insert into emp values(i,'testing',2000);
commit;
end loop;
end;
/


SQL> begin
for i in 1..1000 loop
insert into t2 values(i,'testing',2000);
commit;
end loop;
end;
/ 

PL/SQL procedure successfully completed.

SQL>

I am doing Few updations in t1 table

SQL>update t1 set name='vicky' where id between 1 and 1000;
2000 rows updated.

SQL>update t1 set name='venkat' where id between 1001 and 2000;
2000 rows updated.

SQL>update t1 set name='gayathri' where id between 2001 and 3000;
2000 rows updated.

SQL>update t1 set name='haja' where id between 3001 and 4000;
2000 rows updated.

SQL>update t1 set name='srinath' where id between 4001 and 5000;
2000 rows updated.

SQL>update t1 set name='senthil' where id between 5001 and 6000;
2000 rows updated.

SQL>update t1 set name='ram' where id between 6001 and 7000;
2000 rows updated.

SQL>update t1 set name='karthi' where id between 7001 and 8000;
2000 rows updated.

SQL>update t1 set name='venkat' where id between 10001 and 20000;
20000 rows updated.

SQL>update t1 set name='gayathri' where id between 20001 and 30000;
20000 rows updated.

SQL>update t1 set name='haja' where id between 30001 and 40000;
19999 rows updated.

SQL>update t1 set name='srinath' where id between 40001 and 50000;
10000 rows updated.

SQL>update t1 set name='senthil' where id between 50001 and 60000;
10000 rows updated.

SQL>update t1 set name='ram' where id between 60001 and 70000;
10000 rows updated.

SQL>update t1 set name='karthi' where id between 70001 and 80000;
10000 rows updated.

SQL>delete from t1 where id between 1 and 1000;
2000 rows deleted.

SQL>delete from t1 where id between 1001 and 2000;
2000 rows deleted.

SQL>delete from t1 where id between 2001 and 3000;
2000 rows deleted.

SQL>delete from t1 where id between 3001 and 4000;
2000 rows deleted.

SQL>delete from t1 where id between 4001 and 5000;
2000 rows deleted.

SQL>delete from t1 where id between 5001 and 6000;
2000 rows deleted.

SQL>delete from t1 where id between 6001 and 7000;
2000 rows deleted.

SQL>delete from t1 where id between 7001 and 8000;
2000 rows deleted.

SQL>delete from t1 where id between 10001 and 20000;
20000 rows deleted.

SQL>delete from t1 where id between 40001 and 50000;
10000 rows deleted.

 

For particular SQL Id : b62q7nc33gzwx  we need going to check health

 

 

I attached the output files here.

SQL HEALTH CHECK:

Full output screen you can see here

SQL DIAGNOSTICS:

Full output screen you can see here

SQL EXECUTION PLAN:

Full output screen you can see here

SQL DETAIL:

sqlhc_20181024_232227_b62q7nc33gzwx_4_sql_detail

Log Files:

sqlhc_20181024_232227_b62q7nc33gzwx_8_sqldx sqlhc_20181024_232227_b62q7nc33gzwx_9_log

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

Health Checks For The Oracle Stack Using ORAchk

ORAchk:

ORAchk replaces the RACCheck utility. ORAchk extends health check coverage to the entire Oracle software stack, and identifies and addresses top issues reported by Oracle users.

ORAchk proactively scans for known problems with Oracle products and deployments, including the following:

  • Standalone Oracle Database
  • Oracle Grid Infrastructure
  • Oracle Real Application Clusters
  • Maximum Availability Architecture (MAA) Validation
  • Upgrade Readiness Validations
  • Oracle Golden Gate

Oracle is continuing to expand checks, based on customer requests.

Installation :

[root@ram orachk]# cp -r /home/oracle/Desktop/orachk.zip /u01/app/oracle/product/12.2.0.1/db_1/suptools/orachk
[root@ram orachk]# ls -lrt
total 418548
-rw-r--r--. 1 oracle oinstall 3879 Jun 10 2016 generate_guests_list.sh
-rw-r--r--. 1 oracle oinstall 11487 Jun 10 2016 cgrep
-rwxr-x---. 1 oracle oinstall 182 Jun 10 2016 UserGuide.txt
-rwxr-x---. 1 oracle oinstall 2201 Jun 10 2016 readme.txt
-rw-r--r--. 1 oracle oinstall 2888 Aug 10 2016 user_defined_checks.xsd
-rwxr-x---. 1 oracle oinstall 5906493 Aug 10 2016 rules.dat
-rwxr-x---. 1 oracle oinstall 2856776 Aug 10 2016 orachk
-rwxr-x---. 1 oracle oinstall 42423762 Aug 10 2016 collections.dat
-rw-r--r--. 1 oracle oinstall 2170483 Aug 10 2016 ORAchk_Health_Check_Catalog.html
-rw-r--r--. 1 oracle oinstall 4801825 Aug 10 2016 CollectionManager_App.sql
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:35 templates
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:35 exadiscover
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:35 doc
-rw-r--r--. 1 oracle oinstall 32159 Oct 22 20:22 orachk_debug_20181022_202100.log
-rw-r--r--. 1 oracle oinstall 11540087 Oct 22 21:14 orachk_debug_20181022_203840.log
-rwxr--r--. 1 root root 358799048 Oct 23 02:22 orachk.zip
[root@ram orachk]# unzip orachk.zip 
Archive: orachk.zip
replace collections.dat? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
inflating: collections.dat 
replace rules.dat? [y]es, [n]o, [A]ll, [N]one, [r]ename: y
inflating: rules.dat 
replace .cgrep/versions.dat? [y]es, [n]o, [A]ll, [N]one, [r]ename: A
inflating: .cgrep/versions.dat 
inflating: .cgrep/profiles.dat 
inflating: .cgrep/profiles/EF6C016813C51366E04313C0E50AE11F.prf 
inflating: .cgrep/profiles/DA94919CD0DE0913E04312C0E50A7996.prf 
inflating: .cgrep/profiles/D49C0FBF8FBF4B1AE0431EC0E50A0F24.prf 
inflating: .cgrep/profiles/06889D8BB65E575CE05313C0E50ADFD3.prf 
inflating: .cgrep/profiles/D8367AD6754763FEE04312C0E50A6FCB.prf 
inflating: .cgrep/profiles/DF65D6117CB41054E04312C0E50A69D1.prf 
inflating: .cgrep/profiles/579C1EAF1380DEC3E053D298EB0AB847.prf 
inflating: .cgrep/profiles/06702DE980726771E05313C0E50ACF83.prf 
inflating: .cgrep/profiles/42AEB5616C5A7648E0530C98EB0A4B18.prf 
inflating: .cgrep/profiles/68A6BCC041C53A8FE053D398EB0AA546.prf 
inflating: .cgrep/profiles/1B0907A7BA8EA932E0530C98EB0A0947.prf 
inflating: .cgrep/profiles/20E3DAB976AD7377E0530A98EB0A9BCA.prf 
inflating: .cgrep/profiles/398C70DD35E55DE9E0530E98EB0A880F.prf 
inflating: .cgrep/profiles/E1BF012E8F210839E04313C0E50A7B68.prf 
inflating: .cgrep/profiles/D462A6F7E9C340FDE0431EC0E50ABE12.prf 
inflating: .cgrep/profiles/3E121C16D3714EE0E0530E98EB0A6C66.prf 
extracting: .cgrep/profiles/638ED6170D1C053CE053D498EB0A65F6.prf 
inflating: .cgrep/profiles/EA5EE324E7E05128E04313C0E50A4B2A.prf 
inflating: .cgrep/profiles/3194E615F4BBEDD0E0530A98EB0A046E.prf 
inflating: .cgrep/profiles/069273EAA9873FD1E05312C0E50A8953.prf 
inflating: .cgrep/profiles/1C6E4AC8EF3674D0E0530D98EB0ACEC1.prf 
inflating: .cgrep/profiles/E8DF76E07DD82E0DE04313C0E50AA55D.prf 
inflating: .cgrep/profiles/206B850D83B1CE54E0530C98EB0A5C89.prf 
inflating: .cgrep/profiles/53C6697D0F4E825DE0530D98EB0A38AF.prf 
inflating: .cgrep/profiles/D49AD88F8EE75CD8E0431EC0E50A0BC3.prf 
inflating: .cgrep/profiles/F32F44CE0BCD662FE04312C0E50AB058.prf 
inflating: .cgrep/profiles/E2E972DDE1E14493E04312C0E50A1AB1.prf 
inflating: .cgrep/profiles/12B66730A5161437E05312C0E50AABAB.prf 
inflating: .cgrep/profiles/D49B218473787400E0431EC0E50A0BB9.prf 
inflating: .cgrep/profiles/0A82EA8BF9646097E05313C0E50A26D6.prf 
inflating: .cgrep/profiles/1B0907A7BA8DA932E0530C98EB0A0947.prf 
inflating: .cgrep/profiles/F6AFECA37F177C3FE04313C0E50A56BF.prf 
inflating: .cgrep/profiles/F9ED0179CCD8256BE04312C0E50A5399.prf 
inflating: .cgrep/profiles/45E86D52410AC60AE0530E98EB0AA8EB.prf 
inflating: .cgrep/profiles/4F59DD9703B8547CE0530C98EB0A9927.prf 
inflating: .cgrep/profiles/177BBFEE0215240AE0530E98EB0AEBF7.prf 
inflating: .cgrep/profiles/09DC8AC7C7974BDDE05313C0E50A2339.prf 
inflating: .cgrep/profiles/4329C86BB3C356BFE0530B98EB0A4B22.prf 
extracting: .cgrep/profiles/21A3C08B67727E6AE0530E98EB0AE59C.prf 
inflating: .cgrep/profiles/165CCF84D4FE0342E0530A98EB0AAE6E.prf 
inflating: .cgrep/profiles/D49BDC2EC9E624AEE0431EC0E50A3E12.prf 
inflating: .cgrep/profiles/D49C0AB26A6D45A8E0431EC0E50ADE06.prf 
inflating: .cgrep/profiles/70B251DD093D2F72E053D498EB0AD124.prf 
inflating: .cgrep/profiles/D49C4F9F48735396E0431EC0E50A9A0B.prf 
inflating: .cgrep/profiles/DFE9C207A8F2428CE04313C0E50A6B0A.prf 
inflating: .cgrep/profiles/271BD73C756AE5EDE0530B98EB0A6A13.prf 
inflating: .cgrep/profiles/DF65D0F7FB6F1014E04312C0E50A7808.prf 
inflating: .cgrep/profiles/1B0907A7BA8CA932E0530C98EB0A0947.prf 
inflating: .cgrep/profiles/178E758EB8CA06D8E0530D98EB0A7AC9.prf 
inflating: .cgrep/profiles/270F37922A89B520E0530B98EB0ADDE9.prf 
inflating: orachk 
inflating: orachk.bat 
inflating: orachk.pyc 
inflating: CollectionManager_App.sql 
inflating: Apex5_CollectionManager_App.sql 
inflating: sample_user_defined_checks.xml 
inflating: user_defined_checks.xsd 
inflating: .cgrep/acchk.jar 
inflating: .cgrep/utlu112i.sql 
inflating: .cgrep/upgrade.oracle.jar 
inflating: .cgrep/registry_validation.sql 
inflating: .cgrep/acgrep 
inflating: .cgrep/diff_collections.pl 
inflating: .cgrep/lcgreps10 
inflating: .cgrep/raw_data_browser.pl 
inflating: .cgrep/checkvg.sh 
inflating: .cgrep/Recursive.pm 
inflating: .cgrep/checkDiagCollections.sh 
inflating: .cgrep/scgrep 
inflating: .cgrep/cluster_check_os_collect 
inflating: .cgrep/filechecker.sh 
inflating: .cgrep/parse_user_defined_checks.pl 
inflating: .cgrep/idmhc_get_check_status.pl 
extracting: .cgrep/dbsat.zip 
inflating: .cgrep/scnhealthcheck.sql 
inflating: .cgrep/reset_crshome.pl 
inflating: .cgrep/host_specific_collections.pl 
inflating: .cgrep/lcgrep4 
inflating: .cgrep/checkFlashCache.sh 
inflating: .cgrep/checkLocalDisks.sh 
inflating: .cgrep/zonecores.sh 
inflating: .cgrep/isc_summary.pl 
inflating: .cgrep/lcgreps11 
inflating: .cgrep/lcgrep5 
inflating: .cgrep/checkHiddenParams.sh 
inflating: .cgrep/validatePassword.sh 
inflating: .cgrep/preupgrade122.jar 
inflating: .cgrep/ocm_switch.py 
inflating: .cgrep/checkDiskFGMapping.sh 
inflating: .cgrep/parse_index.pl 
inflating: .cgrep/append_merge_collections.pl 
inflating: .cgrep/psqlplus 
inflating: .cgrep/detect_custom_rpms.sh 
inflating: .cgrep/ggdiscovery.sh 
inflating: .cgrep/EM_NLSID_ID.dat 
inflating: .cgrep/preupgrade19.jar 
inflating: .cgrep/preupgrd.sql 
inflating: .cgrep/get_zfs_checks.pl 
inflating: .cgrep/discover_java_home.sh 
inflating: .cgrep/pxhcdr.sql 
inflating: .cgrep/preupgrade18.jar 
inflating: .cgrep/discoverdbasm.pl 
inflating: .cgrep/check_sysctl.awk 
inflating: .cgrep/mineocr.pm 
inflating: .cgrep/lcgreps9 
inflating: .cgrep/zlcgrep6 
inflating: .cgrep/utlusts.sql 
inflating: .cgrep/readreg.pl 
inflating: .cgrep/rac_main.pl 
inflating: .cgrep/lcgrep3 
inflating: .cgrep/check_reblance_free_space.sql 
inflating: .cgrep/ogghc_11203.sql 
inflating: .cgrep/lcgrep6 
inflating: .cgrep/cgrepwin61 
inflating: .cgrep/ofm_client.sh 
inflating: .cgrep/auto_upgrade.pl 
inflating: .cgrep/create_small_file.pl 
inflating: .cgrep/vmpscan.sh 
inflating: .cgrep/check_dom0_ocfs2.sh 
inflating: .cgrep/checkDiskScheduler.sh 
inflating: .cgrep/rac_lib.pm 
inflating: .cgrep/asrexacheck 
inflating: .cgrep/ra_check_version.pl 
inflating: .cgrep/scgrepx86 
inflating: .cgrep/load_checks_attributes.pl 
inflating: .cgrep/oracle-upstarttmpl.conf 
inflating: .cgrep/rack_comparison.py 
inflating: .cgrep/hiacgrep 
inflating: .cgrep/utluppkg.sql 
inflating: .cgrep/top_consumers.pl 
inflating: .cgrep/rac_file_checker.pl 
inflating: .cgrep/merge_collections.pl 
inflating: .cgrep/show_file_in_html.pl 
inflating: .cgrep/wincgrep.exe 
inflating: .cgrep/lcgrep6s 
inflating: .cgrep/profile_collections.pl 
inflating: .cgrep/create_version.pl 
inflating: .cgrep/zfschecks/SoftringWorkflow 
inflating: .cgrep/zfschecks/Datasets 
inflating: .cgrep/zfschecks/ZSPrivateNetworkInterface 
inflating: .cgrep/zfschecks/BlockSize 
inflating: .cgrep/zfschecks/MirrorProfile 
inflating: .cgrep/zfschecks/ZSPool 
inflating: .cgrep/zfschecks/SnapshotVisibility 
inflating: .cgrep/zfschecks/ZSIPMP 
inflating: .cgrep/zfschecks/NFSDomain 
inflating: .cgrep/zfschecks/Cluster 
inflating: .cgrep/zfschecks/DNSConfiguration 
inflating: .cgrep/zfschecks/ComputeZSServices 
inflating: .cgrep/zfschecks/IlomHealth 
inflating: .cgrep/zfschecks/Maintenance 
inflating: .cgrep/zfschecks/ZSDIMM 
inflating: .cgrep/zfschecks/StorageMemSize 
inflating: .cgrep/zfschecks/ComputeAnalyticsRetentionPolicy 
inflating: .cgrep/zfschecks/ExtShareDedup 
inflating: .cgrep/zfschecks/ExtShadows 
inflating: .cgrep/zfschecks/DiskTimeoutWarning 
inflating: .cgrep/zfschecks/ZSPowerSupply 
inflating: .cgrep/zfschecks/Shadows 
inflating: .cgrep/zfschecks/ExtMirrorProfile 
inflating: .cgrep/zfschecks/HeadStatus 
inflating: .cgrep/zfschecks/ZSServices 
inflating: .cgrep/zfschecks/ExtShareQuota 
inflating: .cgrep/zfschecks/ComputeZSZillas 
inflating: .cgrep/zfschecks/Lock 
inflating: .cgrep/zfschecks/ZSFan 
inflating: .cgrep/zfschecks/NISService 
inflating: .cgrep/zfschecks/ComputeDatasets 
inflating: .cgrep/zfschecks/ZSNTP 
inflating: .cgrep/zfschecks/ZSVersion 
inflating: .cgrep/zfschecks/ZSZillas 
inflating: .cgrep/zfschecks/ZSRouting 
inflating: .cgrep/zfschecks/NFSDelegation 
inflating: .cgrep/zfschecks/Backend 
inflating: .cgrep/zfschecks/ZSILOM 
inflating: .cgrep/zfschecks/L2ARCHeader 
inflating: .cgrep/zfschecks/NFS4LockObjectLeak 
inflating: .cgrep/zfschecks/CommonCode 
inflating: .cgrep/zfschecks/AnalyticsRetentionPolicy 
inflating: .cgrep/zfschecks/ZSSlot 
inflating: .cgrep/zfschecks/ZSCPU 
inflating: .cgrep/zfschecks/ExtBlockSize 
inflating: .cgrep/zfschecks/ShareDedup 
inflating: .cgrep/zfschecks/ShareQuota 
inflating: .cgrep/run_individual_checks.pl 
inflating: .cgrep/diff_checks.pl 
inflating: .cgrep/hugeSGAchecks.sh 
inflating: .cgrep/ogghc_12101.sql 
inflating: .cgrep/ogghc_11204.sql 
inflating: .cgrep/combine_collections.pl 
inflating: .cgrep/OVMMCheckChannels.py 
inflating: .cgrep/cluster_check_os_check 
inflating: .cgrep/cgrepwin63 
creating: lib/
inflating: lib/debugger.pyc 
inflating: lib/__init__.pyc 
inflating: lib/fileattr.pyc 
inflating: lib/utils.pyc 
inflating: lib/mail.pyc 
inflating: lib/command.pyc 
inflating: lib/messages.pyc 
inflating: lib/em_xml_template.pyc 
inflating: lib/execute_checks.pyc 
inflating: lib/wallet.pm 
inflating: lib/logger.pyc 
inflating: lib/constant.pyc 
inflating: lib/help.pyc 
inflating: lib/CM_UpgradeScript.sql 
inflating: lib/autoscheduler.tmpl 
inflating: lib/data_collections.pyc 
inflating: lib/discover_env.pyc 
inflating: lib/cleanup.pyc 
inflating: lib/init.tmpl 
inflating: lib/patch_recommendation.pyc 
inflating: lib/mkstore.pyc 
inflating: lib/autostart 
inflating: lib/CM_Wrapper.sql 
inflating: lib/oracle-upstarttmpl.conf 
inflating: lib/generate_report.pyc 
inflating: lib/winservice.pyc 
inflating: lib/cm_lib.pl 
creating: lib/wallet_jars/
inflating: lib/wallet_jars/ojpse.jar 
inflating: lib/wallet_jars/osdt_core.jar 
inflating: lib/wallet_jars/osdt_cert.jar 
extracting: lib/wallet_jars/cwallet.sso 
inflating: lib/wallet_jars/oraclepki.jar 
inflating: lib/wallet_jars/ojmisc.jar 
inflating: lib/dbconnection.pyc 
inflating: lib/connection.pyc 
inflating: lib/security.pyc 
inflating: lib/watchdog.pyc 
creating: build/
extracting: build/Python3_zlinux.zip 
extracting: build/Python3_aix.zip 
extracting: build/Python3_solaris_sparc.zip 
extracting: build/Python3_solaris.zip 
extracting: build/Python3_linux.zip 
inflating: exadiscover/README 
inflating: exadiscover/list_master_node.sql 
inflating: exadiscover/list_all_ips.sql 
inflating: exadiscover/list_all_ips_vars.sql 
inflating: exadiscover/list_assets.sql 
inflating: exadiscover/CHANGELOG 
inflating: exadiscover/exadiscover.py 
inflating: exadiscover/list_ovmm.sql 
inflating: exadiscover/exadiscover.sh 
inflating: templates/exachk_exalogic.conf.tmpl_quarter 
inflating: templates/exachk_exalogic.conf.tmpl_half 
inflating: templates/exachk_exalogic.conf.tmpl_full 
inflating: templates/exachk_exalogic.conf.tmpl_eighth 
creating: bash/
inflating: bash/psqlplus 
inflating: bash/discoverdbasm.pl 
inflating: bash/orachk 
creating: web/
inflating: web/orachk.jar 
inflating: web/setup_ords.sh 
inflating: web/ords.war 
inflating: .cgrep/orachk.pyc 
extracting: build/.DIAGKIT.zip 
inflating: ORAchk_Health_Check_Catalog.html 
inflating: UserGuide.txt 
inflating: readme.txt 
inflating: doc/ORAchk_and_EXAchk_User_Guide.pdf 
[root@ram orachk]#
[oracle@ram ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/suptools/
[oracle@ram suptools]$ ls -lrt
total 12
drwxr-xr-x. 3 oracle oinstall 4096 Jun 19 10:33 tfa
drwxr-xr-x. 6 oracle oinstall 4096 Jun 19 10:35 orachk
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:39 oratop
[oracle@ram suptools]$ cd orachk/
[oracle@ram orachk]$ ls -lrt
total 56848
-rw-r--r--. 1 oracle oinstall 3879 Jun 10 2016 generate_guests_list.sh
-rw-r--r--. 1 oracle oinstall 11487 Jun 10 2016 cgrep
-rwxr-x---. 1 oracle oinstall 182 Jun 10 2016 UserGuide.txt
-rwxr-x---. 1 oracle oinstall 2201 Jun 10 2016 readme.txt
-rw-r--r--. 1 oracle oinstall 2888 Aug 10 2016 user_defined_checks.xsd
-rwxr-x---. 1 oracle oinstall 5906493 Aug 10 2016 rules.dat
-rwxr-x---. 1 oracle oinstall 2856776 Aug 10 2016 orachk
-rwxr-x---. 1 oracle oinstall 42423762 Aug 10 2016 collections.dat
-rw-r--r--. 1 oracle oinstall 2170483 Aug 10 2016 ORAchk_Health_Check_Catalog.html
-rw-r--r--. 1 oracle oinstall 4801825 Aug 10 2016 CollectionManager_App.sql
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:35 templates
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:35 exadiscover
drwxr-xr-x. 2 oracle oinstall 4096 Jun 19 10:35 doc
[oracle@ram orachk]$ cat readme.txt 
For full documentation on ORAchk including details on what's new in 12.1.0.2.7 see the ORAchk_and_EXAchk_User_Guide.pdf in the doc directory or the ORAchk and EXAchk User's Guide at http://docs.oracle.com/cd/E75572_01/index.html

Issues fixed in ORAchk 12.1.0.2.7
---------------------------------
23041776 lnx64-12.2-orachk: failed to use psqlplus
23193819 request orachk to check udev best practice on network configuration hotplug="n"
23147689 orachk 12.1.0.2.6 resource discovery fails for 10.2 databases
23144636 support anonymous pl/sql block in sql checks
23140046 check if compatible.asm and compatible.dbms should be advanced
23074512 problems parsing the values for autorun_schedule
23074163 daemon does not send diff report as email attachment
23071612 orachk to check that service_names parameter is not set in the spfile
23041743 lnx64-12.2-orachk:output not under oracle_base when run orachk not in its home
23014620 can not run orachk from stage directory due to permission issues
23012747 ac: application continuity - filter acchk output
23007768 orachk execution appends discovery name on path
22861038 orachk db discovery fails on linux 7
22746951 password visible in -setupload and -getupload
22721891 testemail does not validate address
22642844 orachk to warn if large amount of hugepage is free
22642302 provide centralized wallet for orachk cm uploads
21947833 orachk 12.1.0.2.4 - database prompt mismatch on standalone server
21773769 lnx64-12.2-oracheck: implement the file checker component for oracheck module
19161674 lnx64-12.1-orachk:orachk hit syntax error
18956483 lnx64-121-cmt: oakcli orachk hit warning sys.audses$ sequence cache size < 10000
18944821 solsp-12.1-utl-orachk:orachk give a wrong information about stack status
18944432 solsp-12.1-utl-orachk:tabular information isn't well aligned in orachk output
18789186 solsp-12.1-utl-orachk: remove/update log ownership check for orachk
18748527 solsp-12.1-utl-orachk:no need to ask non-gi user to set rat_output for -h option

Issues fixed in previous ORAchk releases
----------------------------------------
For a full list of ORAchk version history see ORAchk_EXAchk_Feature_Fix_History.pdf in the doc directory.

To check the version:

using the -v option.

[oracle@ram orachk]$ ./orachk -v

ORACHK VERSION: 12.1.0.2.7_20160526

To capture debug output:

  1. Reproduce the problem with fewest runs before enabling debug.

    Debug captures a lot and the resulting zip file can be large so try to narrow down the amount of run necessary to reproduce the problem.

    Use command-line options to limit the scope of checks.

  2. Enable debug.
    If you are running the tool in on-demand mode, then use the -debug option:
[oracle@ram orachk]$ ./orachk –debug

Output : click here

Permission Problems:

You must have sufficient directory permissions to run Oracle ORAchk

  1. Verify that the permissions on the tools scripts orachk is set to                        755 (-rwrr-xr-x).
    If the permissions are not set, then set the permissions as follows:

Automatic start from TFA install

Oracle Trace File Analyzer root installations on Linux or Solaris on          non-engineered systems will automatically setup and run the ORAchk daemon.

The daemon will be restarted at 1am every morning, in order to discover any environment changes. A full local ORAchk run will be performed at 2am every morning and a partial run of the most impactful checks will be run every 2 hours via the oratier1 profile.

Any collections older than 2 weeks will automatically be purged.

If ORAchk is installed in stand-alone mode Auto start can also be used for quick setup by running orachk -autostart, again only when run as root on Linux or Solaris on a non-engineered system.

Once auto start is enabled the daemon settings can be changed as per normal and auto start can be removed any time by using orachk -autostop or tfactl run orachk -autostop

Pre check:

[root@ram orachk]# ./orachk -u -o pre
This version of orachk was released on 26-May-2016 and its older than 120 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report.


Do you want to download latest version from my oracle support? [y/n][y]y

updates.oracle.com is not reachable. Please establish connectivity to updates.oracle.com and try again.


Do you want to continue running this version? [y/n][y]y
Running older version...

Running orachk
----------------------------------------------------------
PATH : /u01/app/oracle/product/12.2.0.1/db_1/suptools/orachk
VERSION : 12.1.0.2.7_20160526 
COLLECTIONS DATA LOCATION : /u01/app/oracle/orachk 
----------------------------------------------------------


This version of orachk was released on 26-May-2016 and its older than 120 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report.


Do you want to download latest version from my oracle support? [y/n][y]y

updates.oracle.com is not reachable. Please establish connectivity to updates.oracle.com and try again.


Do you want to continue running this version? [y/n][y]y
Running older version...
Enter upgrade target version (valid versions are 11.2.0.3.0, 11.2.0.4.0, 12.1.0.1.0 and 12.1.0.2.0):-

Invalid upgrade target version format or upgrade version not yet supported.

Enter upgrade target version (valid versions are 11.2.0.3.0, 11.2.0.4.0, 12.1.0.1.0 and 12.1.0.2.0) 2 tries left:- 12.1.0.2.0
This computer is for [S]ingle instance database or part of a [C]luster to run RAC database [S|C] [C]:S

RDBMS binaries found at /u01/app/oracle/product/12.2.0.1/db_1/ and ORACLE_HOME not set. Do you want to set ORACLE_HOME to /u01/app/oracle/product/12.2.0.1/db_1/?[y/n][y]y

Checking for prompts for root user on all nodes...


Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

. . . . . . . . . 
-------------------------------------------------------------------------------------------------------
Oracle Stack Status 
-------------------------------------------------------------------------------------------------------
Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
-------------------------------------------------------------------------------------------------------
ram No No Yes No No No 
-------------------------------------------------------------------------------------------------------


---------------------------------------------------------------------------------------------------------------------------------
Installed components summary 
---------------------------------------------------------------------------------------------------------------------------------
GI_HOME ORACLE_HOME Database Names 
---------------------------------------------------------------------------------------------------------------------------------

Checking for prompts for oracle user on all nodes...

Source vesrsion (122010) is higher than Target version (121020).

orachk is exiting...

To avoid problems while running the tool from terminal sessions on a network attached workstation or laptop, consider running the tool using VNC.

If there is a network interruption, then the tool continues to process to completion.

If the tool fails to run, then re-run the tool. The tool does not resume from the point of failure.

Running Health Checks On-Demand:

To start on-demand health check runs, log in to the system as an appropriate user, and then run an appropriate tool.

Specify the options to direct the type of run that you want.

[root@ram orachk]# ./orachk 
This version of orachk was released on 26-May-2016 and its older than 120 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report.


Do you want to download latest version from my oracle support? [y/n][y]y

updates.oracle.com is not reachable. Please establish connectivity to updates.oracle.com and try again.


Do you want to continue running this version? [y/n][y]y
Running older version...

Running orachk
----------------------------------------------------------
PATH : /u01/app/oracle/product/12.2.0.1/db_1/suptools/orachk
VERSION : 12.1.0.2.7_20160526 
COLLECTIONS DATA LOCATION : /u01/app/oracle/orachk 
----------------------------------------------------------


This version of orachk was released on 26-May-2016 and its older than 120 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report.


Do you want to download latest version from my oracle support? [y/n][y]y

updates.oracle.com is not reachable. Please establish connectivity to updates.oracle.com and try again.


Do you want to continue running this version? [y/n][y]y
Running older version...

Checking for prompts for oracle user on all nodes...


List of running databases
1. clone
2. test
3. All of above
4. None of above


Searching out ORACLE_HOME for selected databases.

. . . .


Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

. . . . . . . . . . . . 
Checking for prompts for root user on all nodes...

. . . . . . . . . . . . 
-------------------------------------------------------------------------------------------------------
Oracle Stack Status 
-------------------------------------------------------------------------------------------------------
Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
-------------------------------------------------------------------------------------------------------
ram No No Yes No No Yes clone test 
-------------------------------------------------------------------------------------------------------


Copying plug-ins

. . . . . . . . .


*** Checking Best Practice Recommendations (PASS/WARNING/FAIL) ***


Collections and audit checks log file is 
/u01/app/oracle/orachk/orachk_ram_test_102318_001148/log/orachk.log

Checking for prompts in /root/.bash_profile on ram for root user...


. . 
=============================================================
Node name - ram 
=============================================================
. . . . .

Collecting - Database Parameters for test database
Collecting - Database Undocumented Parameters for test database
Collecting - RDBMS Feature Usage for test database
Collecting - CPU Information
Collecting - DiskMount Information
Collecting - Kernel parameters
Collecting - Maximum number of semaphore sets on system
Collecting - Maximum number of semaphores on system
Collecting - Maximum number of semaphores per semaphore set
Collecting - Memory Information
Collecting - OS Packages
Collecting - Operating system release information and kernel version
Collecting - Patches for RDBMS Home 
Collecting - Table of file system defaults
Collecting - number of semaphore operations per semop system call
Collecting - Disk Information 
Collecting - Linux Operating system health check using vmpscan.sh 
Collecting - Root user limits 
Collecting - Verify no database server kernel out of memory errors

Data collections completed. Checking best practices on ram.
--------------------------------------------------------------------


FAIL => Bash is vulnerable to code injection (CVE-2014-6271)
WARNING => Linux Swap Configuration does NOT meet Recommendation
WARNING => physical memory is not sufficient
INFO => Important Storage Minimum Requirements for Grid & Database Homes
INFO => Most recent ADR incidents for /u01/app/oracle/product/12.2.0.1/db_1
INFO => Oracle GoldenGate failure prevention best practices
INFO => user_dump_dest has trace files older than 30 days for test
FAIL => Operating system hugepages count does not satisfy total SGA requirements
WARNING => OSWatcher is not running as is recommended.
WARNING => Database parameter DB_BLOCK_CHECKING on PRIMARY is NOT set to the recommended value. for test
INFO => Operational Best Practices
INFO => Database Consolidation Best Practices
INFO => Computer failure prevention best practices
INFO => Data corruption prevention best practices
INFO => Logical corruption prevention best practices
INFO => Database/Cluster/Site failure prevention best practices
INFO => Client failover operational best practices
WARNING => Duplicate objects were found in the SYS and SYSTEM schemas for test
WARNING => Oracle clusterware is not being used
WARNING => RAC Application Cluster is not being used for database high availability on test instance
WARNING => DISK_ASYNCH_IO is NOT set to recommended value for test
FAIL => Flashback on PRIMARY is not configured for test
INFO => Database failure prevention best practices
WARNING => fast_start_mttr_target has NOT been changed from default on test instance
FAIL => Primary database is NOT protected with Data Guard (standby database) for real-time data protection and availability for test
FAIL => Active Data Guard is not configured for test
INFO => Oracle recovery manager(rman) best practices
INFO => Consider increasing the COREDUMPSIZE size
WARNING => Consider investigating changes to the schema objects such as DDLs or new object creation for test
WARNING => Consider investigating the frequency of SGA resize operations and take corrective action for test


Best Practice checking completed.Checking recommended patches on ram.
---------------------------------------------------------------------------------


Collecting patch inventory on ORACLE_HOME /u01/app/oracle/product/12.2.0.1/db_1 
---------------------------------------------------------------------------------


---------------------------------------------------------------------------------
0 Recommended RDBMS patches for 122010 from /u01/app/oracle/product/12.2.0.1/db_1 on ram
---------------------------------------------------------------------------------
Patch# RDBMS ASM type Patch-Description 
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------
---------------------------------------------------------------------------------


---------------------------------------------------------------------------------
RDBMS homes patches summary report
---------------------------------------------------------------------------------
Total patches Applied on RDBMS Applied on ASM ORACLE_HOME 
---------------------------------------------------------------------------------
0 0 0 /u01/app/oracle/product/12.2.0.1/db_1
---------------------------------------------------------------------------------

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



Detailed report (html) - /u01/app/oracle/orachk/orachk_ram_test_102318_001148/orachk_ram_test_102318_001148.html
UPLOAD(if required) - /u01/app/oracle/orachk/orachk_ram_test_102318_001148.zip


[root@ram orachk]# 


Database Server Details

After i run the ORAchk utility i got few output from my server which i added below.

For detailed report click here

Top Consumers :

ORAchk Assessment Report:

For detailed report  click here

Post Upgrade:

After upgrading, run the post-upgrade checks:

[root@ram orachk]# ./orachk -u -o post

Running On-Demand With or Without the Daemon

When running on-demand, if the daemon is running, then the daemon answers all prompts where possible including the passwords.

To run health checks on-demand if the daemon is running, then use:

[root@ram orachk]# ./orachk 

To avoid connecting to the daemon process, meaning the tool to interactively prompt you as required, use the -nodaemon option:

[root@ram orachk]#  ./orachk -nodaemon

Daemon mode is supported only on the Linux and Solaris operating systems.

Sending Results by Email

Optionally email the HTML report to one or more recipients using the            -sendemail option.

[oracle@ram orachk]$ ./orachk -sendemail "NOTIFICATION_EMAIL=support@gmail.com"
Few Commands:
  1. To prevent prompting for which database to run against and check all databases, use the –dball option.
    [oracle@ram orachk]$ ./orachk -dball
  2. To prevent prompting and skip all database checks, use the –dbnone option.
    [oracle@ram orachk]$ ./orachk –dbnone
  3. To run checks against a subset of databases, use the –dbnames database_name option.

    You can check multiple database instances by listing them in a comma-delimited list.

    [oracle@ram orachk]$ ./orachk –dbnames db1,db2,db3

    By default, Oracle ORAchk run checks on all database nodes in the cluster.

  4. To run checks against a subset of PDBs, use the -pdbnames pdb_name option.

    You can check multiple PDBs by listing them in a comma-delimited list.

    [oracle@ram orachk]$ ./orachk –pdbnames pdb1,pdb2,pdb3

    By default, Oracle ORAchk run checks on all PDBs in the cluster.

  5. To run checks against a subset of cluster nodes, use the                                          -clusternodes node option.

    You can check multiple cluster nodes by listing them in a comma-delimited list.

    [oracle@ram orachk]$ ./orachk –clusternodes node1,node2,node3
  6. To run checks against the local node, use the -localonly option.
    [oracle@ram orachk]$ ./orachk -localonly
Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FBGroup:https://www.facebook.com/groups/894402327369506/ FBPage: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba

Linux iostat Command Usage For Oracle DBA

iostat  linux command:-

It displays information about CPU usage, and I/O statistics for every partition and network filesystems(NFS).It is default size is in KB.

[oracle@orcl:~ ] iostat
Linux 2.6.32-696.el6.x86_64 (orcl.localdomain.com) 10/22/18 _x86_64_ (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
         1.09  0.00  0.54    1.75    0.00   96.62

Device: tps  Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda     7.66 61.77      155.95     11792568 29771464

Options :

-c Display the CPU utilization report.
-d Display the device utilization report.
-h Make the NFS report displayed by option -n easier to read by a human.
-k Display statistics in kilobytes per second instead of blocks per second. Data displayed are valid only with kernels 2.4 and later.
-m Display statistics in megabytes per second instead of blocks or kilobytes per second. Data displayed are valid only with kernels 2.4 and later.
-N Display the registered device mapper names for any device mapper devices. Useful for viewing LVM2 statistics.
-n Display the network filesystem (NFS) report. This option works only with kernel 2.6.17 and later.
-p [ { device [,...] | ALL } ]
The -p option displays statistics for block devices and all their partitions that are used by the system. If a device name is entered on the command line, then statistics for it and all its partitions are displayed. Last, the ALL keyword indicates that statistics have to be displayed for all the block devices and partitions defined by the system, including those that have never been used. Note that this option works only with post 2.5 kernels.
-t Print the time for each report displayed. The timestamp format may depend on the value of the S_TIME_FORMAT environment variable (see below).
-V Print version number then exit.
-x Display extended statistics. This option works with post 2.5 kernels since it needs /proc/diskstats file or a mounted sysfs to get the statistics. This option may also work with older kernels (e.g. 2.4) only if extended statistics are available in /proc/partitions (the kernel needs to be patched for that).
-z Tell iostat to omit output for any devices for which there was no activity during the sample period.

Examples :

  1. iostat output  in MB
[oracle@orcl:~ ] iostat -m
Linux 2.6.32-696.el6.x86_64 (orcl.localdomain.com) 10/22/18 _x86_64_ (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
1.09 0.00 0.54 1.75 0.00 96.62

Device: tps MB_read/s MB_wrtn/s MB_read MB_wrtn
sda 7.64 0.03 0.08 5760 14629

2. iostat with -c arguments displays only CPU statistics

[oracle@orcl:~ ] iostat -c
Linux 2.6.32-696.el6.x86_64 (orcl.localdomain.com) 10/22/18 _x86_64_ (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
1.09 0.00 0.54 1.75 0.00 96.62

3. iostat with -c arguments displays only CPU statistics for every 2 seconds.

[oracle@orcl:~ ] iostat -c 2
Linux 2.6.32-696.el6.x86_64 (orcl.localdomain.com) 10/22/18 _x86_64_ (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
1.08 0.00 0.54 1.75 0.00 96.63

avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.51 0.00 0.00 99.49

4. iostat with -d arguments displays only disks I/O statistics of all partitions

[oracle@orcl:~ ] iostat -d
Linux 2.6.32-696.el6.x86_64 (orcl.localdomain.com) 10/22/18 _x86_64_ (1 CPU)

Device: tps  Blk_read/s Blk_wrtn/s Blk_read  Blk_wrtn
sda     7.66 61.71      155.87     11793016  29788536
dm-0    3.26 8.32       23.06      446295370 1237237064

5. It displays the I/O statistics for specific device sda

[oracle@orcl:~ ] iostat -p sda
Linux 2.6.32-696.el6.x86_64 (orcl.localdomain.com) 10/22/18 _x86_64_ (1 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
1.08 0.00 0.54 1.75 0.00 96.62

Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
sda 7.65 61.62 155.75 11793576 29809400
sda1 0.00 0.14 0.00 26011 64
sda2 0.92 15.43 14.98 2952435 2866192
sda3 0.01 0.37 1.73 71657 331632
sda4 0.00 0.00 0.00 13 0
sda5 6.72 45.68 139.04 8742243 26611512

6. iostat version

[oracle@orcl:~ ] iostat -V
sysstat version 9.0.4
(C) Sebastien Godard (sysstat <at> orange.fr)

7. It displays a usage of statement

[oracle@orcl:~ ] iostat -?
Usage: iostat [ options ] [ <interval> [ <count> ] ]
Options are:
[ -c ] [ -d ] [ -N ] [ -n ] [ -h ] [ -k | -m ] [ -t ] [ -V ] [ -x ] [ -y ] [ -z ]
[ -j { ID | LABEL | PATH | UUID | ... } [ <device> [...] | ALL ] ]
[ <device> [...] | ALL ] [ -p [ <device> [,...] | ALL ] ]

 

The iostat command generates three types of reports, the CPU Utilization report, the Device Utilization report and the Network Filesystem report.

CPU utilization in iostat output :

avg-cpu: %user %nice %system %iowait %steal %idle
         27.71 0.00  1.16    2.82    0.00   68.31

Columns in iostat CPU utlization output :

%user
Show the percentage of CPU utilization that occurred while executing at the user level (application).

%nice
Show the percentage of CPU utilization that occurred while executing at the user level with nice priority.

%system
Show the percentage of CPU utilization that occurred while executing at the system level (kernel).

%iowait
Show the percentage of time that the CPU or CPUs were idle during which the system had an outstanding disk I/O request.

%steal
Show the percentage of time spent in involuntary wait by the virtual CPU or CPUs while the hypervisor was servicing another virtual processor.

%idle
Show the percentage of time that the CPU or CPUs were idle and the system did not have an outstanding disk I/O request.

Device Utilization in iostat output :

Device: tps  Blk_read/s   Blk_wrtn/s Blk_read Blk_wrtn
sda     7.59 60.05        154.04     11807456 30287688

To get more detailed statistics, we can use ‘-x’ option along with iostat command

[oracle@fhpasadbdr01 ~]$ iostat -x
Linux 2.6.32-279.5.2.el6.x86_64 (fhpasadbdr01.pasa.pas.local) 10/22/2018 _x86_64_ (16 CPU)

avg-cpu: %user %nice %system %iowait %steal %idle
27.71 0.00 1.16 2.82 0.00 68.31

Device: rrqm/s wrqm/s r/s  w/s  rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util
sda     12.04  11.67  5.59 2.24 146.36 111.29 32.93    0.02     2.68 1.99  1.56
dm-0    0.00   0.00   0.37 2.88 8.32   23.06  9.64     0.04     12.51 1.49  0.49

Columns in iostat device utlization output :

The standard iostat device display shows the following statistics:

           KB/t    kilobytes per transfer
           tps     transfers per second
           MB/s    megabytes per second

           The standard iostat device display, with the -I flag specified,
           shows the following statistics:

           KB/t    kilobytes per transfer
           xfrs    total number of transfers
           MB      total number of megabytes transferred

           The old-style iostat display (using -o) shows the following statistics:

           sps     sectors transferred per second
           tps     transfers per second
           msps    average milliseconds per transaction

           The old-style iostat display, with the -I flag specified, shows the following statistics:

           blk     total blocks/sectors transferred
           xfr     total transfers
           msps    average milliseconds per transaction

Network file utilization in iostat output :

We can use nfsiostat command to generate the NFS i/o statistics reports. nfsiostat command is the part of the package ‘nfs-utils’. Let’s assume we have mounted two NFS shares on our server, so to generate the statistics report for NFS share run the below command,

[root@prod ~]$ nfsiostat

orcl.localdomain.com:/ifs/backup mounted on /backup:

op/s rpc bklog
9.50 0.00
read: ops/s kB/s kB/op retrans avg RTT (ms) avg exe (ms)
2.492 261.008 104.749 0 (0.0%) 12.042 12.185
write: ops/s kB/s kB/op retrans avg RTT (ms) avg exe (ms)
0.011 0.132 11.821 0 (0.0%) 4.042 8.698

orcl.localdomain.com:/ifs/orashare1 mounted on /orashare1:

op/s rpc bklog
3.16 0.00
read: ops/s kB/s kB/op retrans avg RTT (ms) avg exe (ms)
0.333 3.366 10.105 0 (0.0%) 4.081 4.264
write: ops/s kB/s kB/op retrans avg RTT (ms) avg exe (ms)
0.001 0.445 351.851 0 (0.0%) 23.907 9531.542

Click the link for more information about  iostat

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Hot Clone a Remote Non-CDB in Oracle Database 12c Release 2 (12.2)

Cloning a remote non-cdb:

Step 1  : Connect to the remote database. Make sure it up and running.

[oracle@ram admin]$ export ORACLE_SID=orcl
[oracle@ram admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 13 03:06:02 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 629149632 bytes
Database Buffers 197132288 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.

Step 2 : Create a user in the remote database for use with the database link.

SQL> CREATE USER r_user IDENTIFIED BY r_user;

User created.

SQL> GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO r_user;

Grant succeeded.

Step 3 : Check the remote non-CDB is archivelog mode. If not make it archivelog mode.

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> alter database close;
Database altered.

SQL> alter database archivelog;
Database altered.

SQL> shut immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 629149632 bytes
Database Buffers 197132288 bytes
Redo Buffers 3952640 bytes
Database mounted.
Database opened.

SQL>

Step 4 : In Oracle 12.1  the remote database to read-only mode before continuing, but this is not necessary in Oracle 12.2 provided the source database is in archivelog mode.

Step 5 : Switch to the local server and create a “tnsnames.ora” entry pointing to the remote database for use in the using clause of the database link.

[oracle@ram admin]$ export ORACLE_SID=dbwr
[oracle@ram admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 13 03:23:13 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 
SQL> select db_link from dba_db_links;

DB_LINK
--------------
SYS_HUB

SQL> col name for a15
SQL> select name,open_mode from v$pdbs;

NAME             OPEN_MODE
--------------- ----------
PDB$SEED         READ ONLY
PDB5             READ WRITE
PDB6             READ WRITE
PDB1             READ WRITE

Step 6 : Connect to the local database to initiate the clone.

Step 7 : Create a database link in the local database, pointing to the remote database.

SQL> CREATE DATABASE LINK clone_link CONNECT TO r_user IDENTIFIED BY r_user USING 'orcl';

Database link created.

Step 8 : Create a new PDB in the local database by cloning the remote non-CDB. We are using Oracle Managed Files (OMF), so we don’t need to bother with FILE_NAME_CONVERT parameter for file name conversions. Since there is no PDB to name, we use “DB12C” as the PDB name.

SQL> create pluggable database db12c from dbwr@clone_link;

Pluggable database created

Step 9 : The new PDB has been created, but it is in the MOUNTED state.

column name for a15;

SQL> select name,open_mode from v$pdbs;

NAME                OPEN_MODE
-------------      -----------
DB12C               Mounted

SQL>

Step 10 : This PDB was created as a clone of a non-CDB, before it can be opened we need to run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean it up

ALTER SESSION SET CONTAINER=db12c;

@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Step 11 : The PDB can now be opened in read-write mode.

ALTER PLUGGABLE DATABASE db12c OPEN;

SELECT name, open_mode FROM v$pdbs WHERE name = 'DB12C';

NAME                  OPEN_MODE
-------------------- ----------
DB12C                 READ WRITE

SQL>

Thus the cloning PDB has been created from non-cdb.

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba