Saturday, June 17, 2017

Enabling Automatic AWR Snapshots on PDB

Oracle 12.2 allows AWR snapshots to be taken both at CDB and PDB level. CDB level snapshots will contain both CDB and PDB statistics.
AWR report will list the containers whose statistics are available in the AWR report. Below screenshot shows part of SQL statistics section of a CDB level AWR report. It could be seen it has SQL stats for both PDB and the root CDB.
On the other-hand PDB level snapshots will have statistics local to that PDB and some global statistics. Read here for further information on these.
Automatic snapshot is only enabled on the CDB level and by default is disabled on PDB. Oracle recommendation is "to generally use manual snapshots for a PDB. You should enable automatic snapshots only selectively for a PDB for performance reasons".
However, if required automatic AWR snapshots could be enabled on PDB by setting awr_pdb_autoflush_enabled to true. This could be set on CDB root level or PDB level. If set to true on CDB root level then automatic snapshots will be enabled for all the PDBs. If set for PDB then automatic AWR snapshot is enabled only for that PDB. Once awr_pdb_autoflush_enabled is set to true login to the PDB and run DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS with an interval value greater than 0.
Below steps shows enable automatic AWR snapshots for all the PDBs in a CDB.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APDB                           READ WRITE NO
Enable awr_pdb_autoflush to true on cdb
SQL> show con_name

CON_NAME
---------
CDB$ROOT

SQL>  alter system set awr_pdb_autoflush_enabled=true scope=both sid='*';
System altered.
Login to PDB and run the MODIFY_SNAPSHOT_SETTINGS with interval > 0. using PDB's DBID.
SQL> show con_name;

CON_NAME
---------
APDB

SQL> select * from awr_pdb_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID
---------- -------------------- -------------------- ---------- ----------
3913427709 +40150 00:01:00.0    +00008 00:00:00.0    DEFAULT             3


BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 11520, 
                                                           interval => 60,
                                                           topnsql => 34,
                                                           dbid => 3913427709);
END;
/

SQL> select * from awr_pdb_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL        CON_ID
---------- -------------------- -------------------- ---------- ----------
3913427709 +00000 01:00:00.0    +00008 00:00:00.0            34          3
AWR_PDB_WR_CONTROL view shows that interval has been set to every one hour.



When the first snapshot is generated, the AWR_PDB_DATABASE_INSTANCE view will be populated with PDB data.
SQL> select con_id,dbid,INSTANCE_NUMBER,INSTANCE_NAME,CDB,LAST_ASH_SAMPLE_ID from AWR_PDB_DATABASE_INSTANCE;

    CON_ID       DBID INSTANCE_NUMBER INSTANCE_NAME    CDB LAST_ASH_SAMPLE_ID
---------- ---------- --------------- ---------------- --- ------------------
         3 3913427709               2 cdb12c22         YES            1102638
         3 3913427709               1 cdb12c21         YES            1105261
Running awrrpt.sql before a record for PDB exists in AWR_PDB_DATABASE_INSTANCE view will result in "ORA-20200: Database/Instance 3913427709/1 does not exist in AWR_PDB_DATABASE_INSTANCE" (refer 2267849.1). When running PDB AWR reports use AWR_PDB as the location for the AWR_DATA.
Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
Enter value for awr_location: AWR_PDB
There is another optional parameter to consider when enable automatic AWR snapshots for PDBs. AWR_SNAPSHOT_TIME_OFFSET specifies an off-set for snapshot start time. When there are large number of PDBs in a CDB, setting same snapshot time for all of them could result in CPU spikes. Setting this to special value of 1000000 will result in an off-set based on the DB name, reducing CPU spikes due to multiple PDBs taking snapshot at the same time.
SQL> alter system set awr_snapshot_time_offset=1000000 scope=both sid='*';
System altered.
In summary to enable automatic snapshot taking on PDB, set awr_pdb_autoflush_enabled to true and set snapshot interval to value greater than 0.

Useful metalink notes
ORA-20200 Error When Generating AWR or ADDM Report as a PDB DBA User From a 12.2.0.1 CDB Database [ID 2267849.1]
Posted by at
Labels: , ,

Sunday, June 11, 2017

Boot in Single User Mode on RHEL 7

On RHEl7 booting in single user mode is done with the use of rescue mode. RHEL7 documentation states that in RHEL7 rescue mode is equivalent to single user mode. In this mode the system attempts to mount all local file systems and start some important system services. However, it does not activate network interfaces or allow more users to login to the system at the same time.
To boot in the single user mode (rescue mode) press e (edit) on the GRUB2 boot screen.
Scroll down to the line beginning with "linux16" (on x86_64 bit systems) For other systems refer RHEL 7 documentation. On the following screenshot this is underlined in red. Add the following to the end of this line (underline in green in the image below).
systemd.unit=rescue.target


Press Ctrl+x to boot the system in the rescue mode. Enter the root password when prompted to gain access to the system in single user mode.
Posted by at
Labels:

Thursday, June 1, 2017

Installing 12cR2 (12.2.0.1) RAC on RHEL 6 with Role Separation - Database

Following the clusterware installation the next step is to install database software and create the database. The database software isn't much different from that of 12.1 database software installation. Even though the oracle installation guides states gcc and gcc-c++ are not required for RHEL, the cluvfy still searches for them.
$ cluvfy stage -pre dbinst -n rhel6m1,rhel6m2 -r 12.2 -verbose

Verifying Physical Memory ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       7.6865GB (8059836.0KB)    1GB (1048576.0KB)         passed
  rhel6m1       7.6865GB (8059836.0KB)    1GB (1048576.0KB)         passed
Verifying Physical Memory ...PASSED
Verifying Available Physical Memory ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       6.1648GB (6464272.0KB)    50MB (51200.0KB)          passed
  rhel6m1       5.0643GB (5310272.0KB)    50MB (51200.0KB)          passed
Verifying Available Physical Memory ...PASSED
Verifying Swap Size ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       4GB (4194296.0KB)         7.6865GB (8059836.0KB)    failed
  rhel6m1       4GB (4194296.0KB)         7.6865GB (8059836.0KB)    failed
Verifying Swap Size ...FAILED (PRVF-7573)
Verifying Free Space: rhel6m2:/tmp ...
  Path              Node Name     Mount point   Available     Required      Status
  ----------------  ------------  ------------  ------------  ------------  ------------
  /tmp              rhel6m2       /             23.0566GB     1GB           passed
Verifying Free Space: rhel6m2:/tmp ...PASSED
Verifying Free Space: rhel6m1:/tmp ...
  Path              Node Name     Mount point   Available     Required      Status
  ----------------  ------------  ------------  ------------  ------------  ------------
  /tmp              rhel6m1       /             19.7646GB     1GB           passed
Verifying Free Space: rhel6m1:/tmp ...PASSED
Verifying User Existence: grid ...
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  rhel6m2       passed                    exists(502)
  rhel6m1       passed                    exists(502)

  Verifying Users With Same UID: 502 ...PASSED
Verifying User Existence: grid ...PASSED
Verifying Group Existence: dba ...
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  rhel6m2       passed                    exists
  rhel6m1       passed                    exists
Verifying Group Existence: dba ...PASSED
Verifying Group Existence: oinstall ...
  Node Name     Status                    Comment
  ------------  ------------------------  ------------------------
  rhel6m2       passed                    exists
  rhel6m1       passed                    exists
Verifying Group Existence: oinstall ...PASSED
Verifying Group Membership: dba ...
  Node Name         User Exists   Group Exists  User in Group  Status
  ----------------  ------------  ------------  ------------  ----------------
  rhel6m2           yes           yes           yes           passed
  rhel6m1           yes           yes           yes           passed
Verifying Group Membership: dba ...PASSED
Verifying Group Membership: oinstall(Primary) ...
  Node Name         User Exists   Group Exists  User in Group  Primary       Status
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m2           yes           yes           yes           yes           passed
  rhel6m1           yes           yes           yes           yes           passed
Verifying Group Membership: oinstall(Primary) ...PASSED
Verifying Run Level ...
  Node Name     run level                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       3                         3,5                       passed
  rhel6m1       3                         3,5                       passed
Verifying Run Level ...PASSED
Verifying Hard Limit: maximum open file descriptors ...
  Node Name         Type          Available     Required      Status
  ----------------  ------------  ------------  ------------  ----------------
  rhel6m2           hard          65536         65536         passed
  rhel6m1           hard          65536         65536         passed
Verifying Hard Limit: maximum open file descriptors ...PASSED
Verifying Soft Limit: maximum open file descriptors ...
  Node Name         Type          Available     Required      Status
  ----------------  ------------  ------------  ------------  ----------------
  rhel6m2           soft          1024          1024          passed
  rhel6m1           soft          1024          1024          passed
Verifying Soft Limit: maximum open file descriptors ...PASSED
Verifying Hard Limit: maximum user processes ...
  Node Name         Type          Available     Required      Status
  ----------------  ------------  ------------  ------------  ----------------
  rhel6m2           hard          16384         16384         passed
  rhel6m1           hard          16384         16384         passed
Verifying Hard Limit: maximum user processes ...PASSED
Verifying Soft Limit: maximum user processes ...
  Node Name         Type          Available     Required      Status
  ----------------  ------------  ------------  ------------  ----------------
  rhel6m2           soft          2047          2047          passed
  rhel6m1           soft          2047          2047          passed
Verifying Soft Limit: maximum user processes ...PASSED
Verifying Soft Limit: maximum stack size ...
  Node Name         Type          Available     Required      Status
  ----------------  ------------  ------------  ------------  ----------------
  rhel6m2           soft          10240         10240         passed
  rhel6m1           soft          10240         10240         passed
Verifying Soft Limit: maximum stack size ...PASSED
Verifying Architecture ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       x86_64                    x86_64                    passed
  rhel6m1       x86_64                    x86_64                    passed
Verifying Architecture ...PASSED
Verifying OS Kernel Version ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       2.6.32-358.el6.x86_64     2.6.32                    passed
  rhel6m1       2.6.32-358.el6.x86_64     2.6.32                    passed
Verifying OS Kernel Version ...PASSED
Verifying OS Kernel Parameter: semmsl ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           3010          3010          250           passed
  rhel6m2           3010          3010          250           passed
Verifying OS Kernel Parameter: semmsl ...PASSED
Verifying OS Kernel Parameter: semmns ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           385280        385280        32000         passed
  rhel6m2           385280        385280        32000         passed
Verifying OS Kernel Parameter: semmns ...PASSED
Verifying OS Kernel Parameter: semopm ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           3010          3010          100           passed
  rhel6m2           3010          3010          100           passed
Verifying OS Kernel Parameter: semopm ...PASSED
Verifying OS Kernel Parameter: semmni ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           128           128           128           passed
  rhel6m2           128           128           128           passed
Verifying OS Kernel Parameter: semmni ...PASSED
Verifying OS Kernel Parameter: shmmax ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           68719476736   68719476736   4126636032    passed
  rhel6m2           68719476736   68719476736   4126636032    passed
Verifying OS Kernel Parameter: shmmax ...PASSED
Verifying OS Kernel Parameter: shmmni ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           4096          4096          4096          passed
  rhel6m2           4096          4096          4096          passed
Verifying OS Kernel Parameter: shmmni ...PASSED
Verifying OS Kernel Parameter: shmall ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           4294967296    4294967296    805983        passed
  rhel6m2           4294967296    4294967296    805983        passed
Verifying OS Kernel Parameter: shmall ...PASSED
Verifying OS Kernel Parameter: file-max ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           6815744       6815744       6815744       passed
  rhel6m2           6815744       6815744       6815744       passed
Verifying OS Kernel Parameter: file-max ...PASSED
Verifying OS Kernel Parameter: ip_local_port_range ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           between 9000 & 65500  between 9000 & 65500  between 9000 & 65535  passed
  rhel6m2           between 9000 & 65500  between 9000 & 65500  between 9000 & 65535  passed
Verifying OS Kernel Parameter: ip_local_port_range ...PASSED
Verifying OS Kernel Parameter: rmem_default ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           4194304       4194304       262144        passed
  rhel6m2           4194304       4194304       262144        passed
Verifying OS Kernel Parameter: rmem_default ...PASSED
Verifying OS Kernel Parameter: rmem_max ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           4194304       4194304       4194304       passed
  rhel6m2           4194304       4194304       4194304       passed
Verifying OS Kernel Parameter: rmem_max ...PASSED
Verifying OS Kernel Parameter: wmem_default ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           1048576       1048576       262144        passed
  rhel6m2           1048576       1048576       262144        passed
Verifying OS Kernel Parameter: wmem_default ...PASSED
Verifying OS Kernel Parameter: wmem_max ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           2097152       2097152       1048576       passed
  rhel6m2           2097152       2097152       1048576       passed
Verifying OS Kernel Parameter: wmem_max ...PASSED
Verifying OS Kernel Parameter: aio-max-nr ...
  Node Name         Current       Configured    Required      Status        Comment
  ----------------  ------------  ------------  ------------  ------------  ------------
  rhel6m1           3145728       3145728       1048576       passed
  rhel6m2           3145728       3145728       1048576       passed
Verifying OS Kernel Parameter: aio-max-nr ...PASSED
Verifying Package: binutils-2.20.51.0.2 ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       binutils-2.20.51.0.2-5.36.el6  binutils-2.20.51.0.2      passed
  rhel6m1       binutils-2.20.51.0.2-5.36.el6  binutils-2.20.51.0.2      passed
Verifying Package: binutils-2.20.51.0.2 ...PASSED
Verifying Package: compat-libcap1-1.10 ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       compat-libcap1-1.10-1     compat-libcap1-1.10       passed
  rhel6m1       compat-libcap1-1.10-1     compat-libcap1-1.10       passed
Verifying Package: compat-libcap1-1.10 ...PASSED
Verifying Package: compat-libstdc++-33-3.2.3 (x86_64) ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       compat-libstdc++-33(x86_64)-3.2.3-69.el6  compat-libstdc++-33(x86_64)-3.2.3  passed
  rhel6m1       compat-libstdc++-33(x86_64)-3.2.3-69.el6  compat-libstdc++-33(x86_64)-3.2.3  passed
Verifying Package: compat-libstdc++-33-3.2.3 (x86_64) ...PASSED
Verifying Package: libgcc-4.4.7 (x86_64) ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       libgcc(x86_64)-4.4.7-3.el6  libgcc(x86_64)-4.4.7      passed
  rhel6m1       libgcc(x86_64)-4.4.7-3.el6  libgcc(x86_64)-4.4.7      passed
Verifying Package: libgcc-4.4.7 (x86_64) ...PASSED
Verifying Package: libstdc++-4.4.7 (x86_64) ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       libstdc++(x86_64)-4.4.7-3.el6  libstdc++(x86_64)-4.4.7   passed
  rhel6m1       libstdc++(x86_64)-4.4.7-3.el6  libstdc++(x86_64)-4.4.7   passed
Verifying Package: libstdc++-4.4.7 (x86_64) ...PASSED
Verifying Package: libstdc++-devel-4.4.7 (x86_64) ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       libstdc++-devel(x86_64)-4.4.7-3.el6  libstdc++-devel(x86_64)-4.4.7  passed
  rhel6m1       libstdc++-devel(x86_64)-4.4.7-3.el6  libstdc++-devel(x86_64)-4.4.7  passed
Verifying Package: libstdc++-devel-4.4.7 (x86_64) ...PASSED
Verifying Package: sysstat-9.0.4 ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       sysstat-9.0.4-20.el6      sysstat-9.0.4             passed
  rhel6m1       sysstat-9.0.4-20.el6      sysstat-9.0.4             passed
Verifying Package: sysstat-9.0.4 ...PASSED
Verifying Package: gcc-4.4.7 ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       gcc-4.4.7-3.el6           gcc-4.4.7                 passed
  rhel6m1       gcc-4.4.7-3.el6           gcc-4.4.7                 passed
Verifying Package: gcc-4.4.7 ...PASSED
Verifying Package: gcc-c++-4.4.7 ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       gcc-c++-4.4.7-3.el6       gcc-c++-4.4.7             passed
  rhel6m1       gcc-c++-4.4.7-3.el6       gcc-c++-4.4.7             passed
Verifying Package: gcc-c++-4.4.7 ...PASSED
Verifying Package: ksh ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       ksh                       ksh                       passed
  rhel6m1       ksh                       ksh                       passed
Verifying Package: ksh ...PASSED
Verifying Package: make-3.81 ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       make-3.81-20.el6          make-3.81                 passed
  rhel6m1       make-3.81-20.el6          make-3.81                 passed
Verifying Package: make-3.81 ...PASSED
Verifying Package: glibc-2.12 (x86_64) ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       glibc(x86_64)-2.12-1.107.el6  glibc(x86_64)-2.12        passed
  rhel6m1       glibc(x86_64)-2.12-1.107.el6  glibc(x86_64)-2.12        passed
Verifying Package: glibc-2.12 (x86_64) ...PASSED
Verifying Package: glibc-devel-2.12 (x86_64) ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       glibc-devel(x86_64)-2.12-1.107.el6  glibc-devel(x86_64)-2.12  passed
  rhel6m1       glibc-devel(x86_64)-2.12-1.107.el6  glibc-devel(x86_64)-2.12  passed
Verifying Package: glibc-devel-2.12 (x86_64) ...PASSED
Verifying Package: libaio-0.3.107 (x86_64) ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       libaio(x86_64)-0.3.107-10.el6  libaio(x86_64)-0.3.107    passed
  rhel6m1       libaio(x86_64)-0.3.107-10.el6  libaio(x86_64)-0.3.107    passed
Verifying Package: libaio-0.3.107 (x86_64) ...PASSED
Verifying Package: libaio-devel-0.3.107 (x86_64) ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       libaio-devel(x86_64)-0.3.107-10.el6  libaio-devel(x86_64)-0.3.107  passed
  rhel6m1       libaio-devel(x86_64)-0.3.107-10.el6  libaio-devel(x86_64)-0.3.107  passed
Verifying Package: libaio-devel-0.3.107 (x86_64) ...PASSED
Verifying Package: smartmontools-5.43-1 ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       smartmontools-5.43-1.el6  smartmontools-5.43-1      passed
  rhel6m1       smartmontools-5.43-1.el6  smartmontools-5.43-1      passed
Verifying Package: smartmontools-5.43-1 ...PASSED
Verifying Package: net-tools-1.60-110 ...
  Node Name     Available                 Required                  Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       net-tools-1.60-110.el6_2  net-tools-1.60-110        passed
  rhel6m1       net-tools-1.60-110.el6_2  net-tools-1.60-110        passed
Verifying Package: net-tools-1.60-110 ...PASSED
Verifying Users With Same UID: 0 ...PASSED
Verifying Current Group ID ...PASSED
Verifying Root user consistency ...
  Node Name                             Status
  ------------------------------------  ------------------------
  rhel6m2                               passed
  rhel6m1                               passed
Verifying Root user consistency ...PASSED
Verifying Node Connectivity ...
  Verifying Hosts File ...
  Node Name                             Status
  ------------------------------------  ------------------------
  rhel6m1                               passed
  rhel6m2                               passed
  Verifying Hosts File ...PASSED

Interface information for node "rhel6m2"

 Name   IP Address      Subnet          Gateway         Def. Gateway    HW Address        MTU
 ------ --------------- --------------- --------------- --------------- ----------------- ------
 eth0   192.168.0.94    192.168.0.0     0.0.0.0         192.168.0.100   08:00:27:7E:61:A9 1500
 eth0   192.168.0.125   192.168.0.0     0.0.0.0         192.168.0.100   08:00:27:7E:61:A9 1500
 eth0   192.168.0.98    192.168.0.0     0.0.0.0         192.168.0.100   08:00:27:7E:61:A9 1500
 eth1   192.168.1.88    192.168.1.0     0.0.0.0         192.168.0.100   08:00:27:69:2C:B6 1500

Interface information for node "rhel6m1"

 Name   IP Address      Subnet          Gateway         Def. Gateway    HW Address        MTU
 ------ --------------- --------------- --------------- --------------- ----------------- ------
 eth0   192.168.0.93    192.168.0.0     0.0.0.0         192.168.0.100   08:00:27:AC:F3:CC 1500
 eth0   192.168.0.97    192.168.0.0     0.0.0.0         192.168.0.100   08:00:27:AC:F3:CC 1500
 eth0   192.168.0.145   192.168.0.0     0.0.0.0         192.168.0.100   08:00:27:AC:F3:CC 1500
 eth0   192.168.0.135   192.168.0.0     0.0.0.0         192.168.0.100   08:00:27:AC:F3:CC 1500
 eth1   192.168.1.87    192.168.1.0     0.0.0.0         192.168.0.100   08:00:27:A3:C4:6F 1500

Check: MTU consistency on the private interfaces of subnet "192.168.1.0"

  Node              Name          IP Address    Subnet        MTU
  ----------------  ------------  ------------  ------------  ----------------
  rhel6m2           eth1          192.168.1.88  192.168.1.0   1500
  rhel6m1           eth1          192.168.1.87  192.168.1.0   1500

Check: MTU consistency of the subnet "192.168.0.0".

  Node              Name          IP Address    Subnet        MTU
  ----------------  ------------  ------------  ------------  ----------------
  rhel6m2           eth0          192.168.0.94  192.168.0.0   1500
  rhel6m2           eth0          192.168.0.125  192.168.0.0   1500
  rhel6m2           eth0          192.168.0.98  192.168.0.0   1500
  rhel6m1           eth0          192.168.0.93  192.168.0.0   1500
  rhel6m1           eth0          192.168.0.97  192.168.0.0   1500
  rhel6m1           eth0          192.168.0.145  192.168.0.0   1500
  rhel6m1           eth0          192.168.0.135  192.168.0.0   1500

  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  rhel6m1[eth1:192.168.1.87]      rhel6m2[eth1:192.168.1.88]      yes

  Source                          Destination                     Connected?
  ------------------------------  ------------------------------  ----------------
  rhel6m1[eth0:192.168.0.93]      rhel6m2[eth0:192.168.0.125]     yes
  rhel6m1[eth0:192.168.0.93]      rhel6m2[eth0:192.168.0.98]      yes
  rhel6m1[eth0:192.168.0.93]      rhel6m2[eth0:192.168.0.94]      yes
  rhel6m1[eth0:192.168.0.93]      rhel6m1[eth0:192.168.0.97]      yes
  rhel6m1[eth0:192.168.0.93]      rhel6m1[eth0:192.168.0.145]     yes
  rhel6m1[eth0:192.168.0.93]      rhel6m1[eth0:192.168.0.135]     yes
  rhel6m2[eth0:192.168.0.125]     rhel6m2[eth0:192.168.0.98]      yes
  rhel6m2[eth0:192.168.0.125]     rhel6m2[eth0:192.168.0.94]      yes
  rhel6m2[eth0:192.168.0.125]     rhel6m1[eth0:192.168.0.97]      yes
  rhel6m2[eth0:192.168.0.125]     rhel6m1[eth0:192.168.0.145]     yes
  rhel6m2[eth0:192.168.0.125]     rhel6m1[eth0:192.168.0.135]     yes
  rhel6m2[eth0:192.168.0.98]      rhel6m2[eth0:192.168.0.94]      yes
  rhel6m2[eth0:192.168.0.98]      rhel6m1[eth0:192.168.0.97]      yes
  rhel6m2[eth0:192.168.0.98]      rhel6m1[eth0:192.168.0.145]     yes
  rhel6m2[eth0:192.168.0.98]      rhel6m1[eth0:192.168.0.135]     yes
  rhel6m2[eth0:192.168.0.94]      rhel6m1[eth0:192.168.0.97]      yes
  rhel6m2[eth0:192.168.0.94]      rhel6m1[eth0:192.168.0.145]     yes
  rhel6m2[eth0:192.168.0.94]      rhel6m1[eth0:192.168.0.135]     yes
  rhel6m1[eth0:192.168.0.97]      rhel6m1[eth0:192.168.0.145]     yes
  rhel6m1[eth0:192.168.0.97]      rhel6m1[eth0:192.168.0.135]     yes
  rhel6m1[eth0:192.168.0.145]     rhel6m1[eth0:192.168.0.135]     yes
  Verifying Check that maximum (MTU) size packet goes through subnet ...PASSED
  Verifying subnet mask consistency for subnet "192.168.1.0" ...PASSED
  Verifying subnet mask consistency for subnet "192.168.0.0" ...PASSED
Verifying Node Connectivity ...PASSED
Verifying Multicast check ...
Checking subnet "192.168.1.0" for multicast communication with multicast group "224.0.0.251"
Verifying Multicast check ...PASSED
Verifying User Mask ...
  Node Name     Available                 Required                  Comment
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       0022                      0022                      passed
  rhel6m1       0022                      0022                      passed
Verifying User Mask ...PASSED
Verifying CRS Integrity ...
  Verifying Clusterware Version Consistency ...PASSED
Verifying CRS Integrity ...PASSED
Verifying Cluster Manager Integrity ...
  Node Name                             Status
  ------------------------------------  ------------------------
  rhel6m1                               running
  rhel6m2                               running
Verifying Cluster Manager Integrity ...PASSED
Verifying Node Application Existence ...

Checking existence of VIP node application (required)

  Node Name     Required                  Running?                  Comment
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       yes                       yes                       passed
  rhel6m1       yes                       yes                       passed


Checking existence of NETWORK node application (required)

  Node Name     Required                  Running?                  Comment
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       yes                       yes                       passed
  rhel6m1       yes                       yes                       passed


Checking existence of ONS node application (optional)

  Node Name     Required                  Running?                  Comment
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       no                        yes                       passed
  rhel6m1       no                        yes                       passed
Verifying Node Application Existence ...PASSED
Verifying Clock Synchronization ...
  Node Name                             Status
  ------------------------------------  ------------------------
  rhel6m1                               passed
  rhel6m2                               passed

  Node Name                             State
  ------------------------------------  ------------------------
  rhel6m2                               Active
  rhel6m1                               Active

  Node Name     Time Offset               Status
  ------------  ------------------------  ------------------------
  rhel6m2       0.0                       passed
  rhel6m1       0.0                       passed
Verifying Clock Synchronization ...PASSED
Verifying resolv.conf Integrity ...
  Verifying (Linux) resolv.conf Integrity ...
  Node Name                             Status
  ------------------------------------  ------------------------
  rhel6m2                               passed
  rhel6m1                               passed

  checking response for name "rhel6m2" from each of the name servers specified
  in "/etc/resolv.conf"

  Node Name     Source                    Comment                   Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m2       192.168.0.66              IPv4                      passed

  checking response for name "rhel6m1" from each of the name servers specified
  in "/etc/resolv.conf"

  Node Name     Source                    Comment                   Status
  ------------  ------------------------  ------------------------  ----------
  rhel6m1       192.168.0.66              IPv4                      passed
  Verifying (Linux) resolv.conf Integrity ...PASSED
Verifying resolv.conf Integrity ...PASSED
Verifying Time zone consistency ...PASSED
Verifying Single Client Access Name (SCAN) ...
  SCAN Name         Node          Running?      ListenerName  Port          Running?
  ----------------  ------------  ------------  ------------  ------------  ------------
  rac-scan          rhel6m2       true          LISTENER_SCAN1  1521          true
  rac-scan          rhel6m1       true          LISTENER_SCAN2  1521          true
  rac-scan          rhel6m1       true          LISTENER_SCAN3  1521          true

Checking TCP connectivity to SCAN listeners...

  Node          ListenerName              TCP connectivity?
  ------------  ------------------------  ------------------------
  rhel6m2       LISTENER_SCAN1            yes
  rhel6m2       LISTENER_SCAN2            yes
  rhel6m2       LISTENER_SCAN3            yes

  Verifying DNS/NIS name service 'rac-scan' ...
    Verifying Name Service Switch Configuration File Integrity ...PASSED

  SCAN Name     IP Address                Status                    Comment
  ------------  ------------------------  ------------------------  ----------
  rac-scan      192.168.0.145             passed
  rac-scan      192.168.0.125             passed
  rac-scan      192.168.0.135             passed
  Verifying DNS/NIS name service 'rac-scan' ...PASSED
Verifying Single Client Access Name (SCAN) ...PASSED
Verifying VIP Subnet configuration check ...PASSED
Verifying Database Clusterware Version Compatibility ...PASSED
Verifying ASM storage privileges for the user: grid ...
  Verifying Group Membership: asmdba ...
  Node Name         User Exists   Group Exists  User in Group  Status
  ----------------  ------------  ------------  ------------  ----------------
  rhel6m2           yes           yes           yes           passed
  rhel6m1           yes           yes           yes           passed
  Verifying Group Membership: asmdba ...PASSED
Verifying ASM storage privileges for the user: grid ...PASSED
Verifying Daemon "proxyt" not configured and running ...
  Node Name     Configured                Status
  ------------  ------------------------  ------------------------
  rhel6m2       no                        passed
  rhel6m1       no                        passed

  Node Name     Running?                  Status
  ------------  ------------------------  ------------------------
  rhel6m2       no                        passed
  rhel6m1       no                        passed
Verifying Daemon "proxyt" not configured and running ...PASSED
Verifying ACFS device special file ...PASSED
Verifying /dev/shm mounted as temporary file system ...PASSED
Verifying Maximum locked memory check ...FAILED (PRVE-0059)

Pre-check for database installation was unsuccessful on all the nodes.


Failures were encountered during execution of CVU verification request "stage -pre dbinst".

Verifying Swap Size ...FAILED
rhel6m2: PRVF-7573 : Sufficient swap size is not available on node "rhel6m2"
         [Required = 7.6865GB (8059836.0KB) ; Found = 4GB (4194296.0KB)]

rhel6m1: PRVF-7573 : Sufficient swap size is not available on node "rhel6m1"
         [Required = 7.6865GB (8059836.0KB) ; Found = 4GB (4194296.0KB)]

Verifying Maximum locked memory check ...FAILED
rhel6m2: PRVE-0059 : no default entry or entry specific to user "grid" was
         found in the configuration file "/etc/security/limits.conf" when
         checking the maximum locked memory limit on node "rhel6m2.domain.net"

rhel6m1: PRVE-0059 : no default entry or entry specific to user "grid" was
         found in the configuration file "/etc/security/limits.conf" when
         checking the maximum locked memory limit on node "rhel6m1.domain.net"


CVU operation performed:      stage -pre dbinst
Date:                         May 17, 2017 12:36:35 PM
CVU home:                     /opt/app/12.2.0/grid/
User:                         grid
Execute runInstaller to commence the database software installation.
As mentioned earlier a new user group for RAC management has been introduced in 12.2. This could be specified as the RAC administrative group when specifying privilege OS groups.
Summary



Once the database software is installed next step is to create the database. Before running DBCA make sure oracle user has write permission on $ORACLE_BASE/cfgtoollogs, $ORACLE_BASE/cfgtoollogs/dbca and $ORACLE_BASE/cfgtoollogs/sqlpatch directories on the local node. Cluvfy could be used to check pre-requisite for RAC DB (output is without verbose option).
cluvfy stage -pre dbcfg -n rhel6m1,rhel6m2 -d /opt/app/oracle/product/12.2.0/dbhome_1

Verifying Physical Memory ...PASSED
Verifying Available Physical Memory ...PASSED
Verifying Swap Size ...FAILED (PRVF-7573)
Verifying Free Space: rhel6m2:/tmp ...PASSED
Verifying Free Space: rhel6m1:/tmp ...PASSED
Verifying User Existence: oracle ...
  Verifying Users With Same UID: 501 ...PASSED
Verifying User Existence: oracle ...PASSED
Verifying Group Existence: dgdba ...PASSED
Verifying Group Existence: dba ...PASSED
Verifying Group Existence: racdba ...PASSED
Verifying Group Existence: backupdba ...PASSED
Verifying Group Existence: oper ...PASSED
Verifying Group Existence: oinstall ...PASSED
Verifying Group Membership: backupdba ...PASSED
Verifying Group Membership: racdba ...PASSED
Verifying Group Membership: dgdba ...PASSED
Verifying Group Membership: oinstall(Primary) ...PASSED
Verifying Group Membership: dba ...PASSED
Verifying Group Membership: oper ...PASSED
Verifying Run Level ...PASSED
Verifying Hard Limit: maximum open file descriptors ...PASSED
Verifying Soft Limit: maximum open file descriptors ...PASSED
Verifying Hard Limit: maximum user processes ...PASSED
Verifying Soft Limit: maximum user processes ...PASSED
Verifying Soft Limit: maximum stack size ...PASSED
Verifying Architecture ...PASSED
Verifying OS Kernel Version ...PASSED
Verifying OS Kernel Parameter: semmsl ...PASSED
Verifying OS Kernel Parameter: semmns ...PASSED
Verifying OS Kernel Parameter: semopm ...PASSED
Verifying OS Kernel Parameter: semmni ...PASSED
Verifying OS Kernel Parameter: shmmax ...PASSED
Verifying OS Kernel Parameter: shmmni ...PASSED
Verifying OS Kernel Parameter: shmall ...PASSED
Verifying OS Kernel Parameter: file-max ...PASSED
Verifying OS Kernel Parameter: ip_local_port_range ...PASSED
Verifying OS Kernel Parameter: rmem_default ...PASSED
Verifying OS Kernel Parameter: rmem_max ...PASSED
Verifying OS Kernel Parameter: wmem_default ...PASSED
Verifying OS Kernel Parameter: wmem_max ...PASSED
Verifying OS Kernel Parameter: aio-max-nr ...PASSED
Verifying Package: binutils-2.20.51.0.2 ...PASSED
Verifying Package: compat-libcap1-1.10 ...PASSED
Verifying Package: compat-libstdc++-33-3.2.3 (x86_64) ...PASSED
Verifying Package: libgcc-4.4.7 (x86_64) ...PASSED
Verifying Package: libstdc++-4.4.7 (x86_64) ...PASSED
Verifying Package: libstdc++-devel-4.4.7 (x86_64) ...PASSED
Verifying Package: sysstat-9.0.4 ...PASSED
Verifying Package: gcc-4.4.7 ...PASSED
Verifying Package: gcc-c++-4.4.7 ...PASSED
Verifying Package: ksh ...PASSED
Verifying Package: make-3.81 ...PASSED
Verifying Package: glibc-2.12 (x86_64) ...PASSED
Verifying Package: glibc-devel-2.12 (x86_64) ...PASSED
Verifying Package: libaio-0.3.107 (x86_64) ...PASSED
Verifying Package: libaio-devel-0.3.107 (x86_64) ...PASSED
Verifying Package: smartmontools-5.43-1 ...PASSED
Verifying Package: net-tools-1.60-110 ...PASSED
Verifying Current Group ID ...PASSED
Verifying CRS Integrity ...
  Verifying Clusterware Version Consistency ...PASSED
Verifying CRS Integrity ...PASSED
Verifying Node Application Existence ...PASSED
Verifying Time zone consistency ...PASSED
Verifying Single Client Access Name (SCAN) ...
  Verifying DNS/NIS name service 'rac-scan' ...
    Verifying Name Service Switch Configuration File Integrity ...PASSED
  Verifying DNS/NIS name service 'rac-scan' ...PASSED
Verifying Single Client Access Name (SCAN) ...PASSED
Verifying ASM Integrity ...
  Verifying Node Connectivity ...
    Verifying Hosts File ...PASSED
    Verifying Check that maximum (MTU) size packet goes through subnet ...PASSED
    Verifying subnet mask consistency for subnet "192.168.1.0" ...PASSED
    Verifying subnet mask consistency for subnet "192.168.0.0" ...PASSED
  Verifying Node Connectivity ...PASSED
Verifying ASM Integrity ...PASSED
Verifying Database Clusterware Version Compatibility ...PASSED
Verifying File system mount options for path ORACLE_HOME ...PASSED
Verifying /dev/shm mounted as temporary file system ...PASSED

Pre-check for database configuration was unsuccessful on all the nodes.

Failures were encountered during execution of CVU verification request "stage -pre dbcfg".

Verifying Swap Size ...FAILED
As this is a test system, the failure due to insufficient SWAP size is ignorable. The DBCA interfaces are different on 12.2 compared to earlier version of 12.1.
The database created here is a non-CDB. Though this is depreciated, it it still possible to create non-CDB on 12.2.
Select ASM for storage
Select / de-select DB options as required.
Set configuration options. On 12.2 the database character set defaults to AL2UTF8.
Customize Storage, including control file options.
Create the database, generate template or scripts.
DBCA runs a pre-requisite check.
Summary
Completion
This concludes the creation of non-CDB on 12.2. One notable difference on 12.2 DB creation compared to 12.1 is when creating CDBs. Unlike in 12.1, in the 12.2 DBCA it is allowed to select subset of DB options during the database creation. In 12.1 this had to be done using scripts (Refer 2001512.1 and 1616554.1).
The database is created as CDB.
DBCA allows selecting subset of options similar to non-CDB.
Summary page showing database is container DB.
Create the pluggable database
Querying the registry shows the subset of options being installed.
SQL> show con_name

CON_NAME
------------------------------
PDB12C2
SQL> select comp_id,comp_name,status,version from dba_registry;

COMP_ID    COMP_NAME                                STATUS   VERSION
---------- ---------------------------------------- -------- ---------------
CATALOG    Oracle Database Catalog Views            VALID    12.2.0.1.0
CATPROC    Oracle Database Packages and Types       VALID    12.2.0.1.0
RAC        Oracle Real Application Clusters         VALID    12.2.0.1.0
XDB        Oracle XML Database                      VALID    12.2.0.1.0
OWM        Oracle Workspace Manager                 VALID    12.2.0.1.0
As mentioned in the previous post the standalone cluster mode is flex. This allows database instance to function even when the ASM instance on the node fails. This could be tested using the same steps listed in converting standard cluster to flex cluster post. Following is the status before ASM is shutdown on node 2. This shows ASM instance up on both instances and DB as a client.
[grid@rhel6m1 ~]$ srvctl status asm -detail
ASM is running on rhel6m2,rhel6m1
ASM is enabled.
ASM instance +ASM1 is running on node rhel6m1
Number of connected clients: 3
Client names: -MGMTDB:_mgmtdb:ora12c2-cluster cdb12c21:cdb12c2:ora12c2-cluster rhel6m1.domain.net:_OCR:ora12c2-cluster
ASM instance +ASM2 is running on node rhel6m2
Number of connected clients: 2
Client names: cdb12c22:cdb12c2:ora12c2-cluster rhel6m2.domain.net:_OCR:ora12c2-cluster
Stop the ASM on node 2 with 
[grid@rhel6m1 ~]$ srvctl stop asm -node rhel6m2 -force
The database instance on node two would be running by becoming a client of ASM1.
[grid@rhel6m1 ~]$ srvctl status database -d cdb12c2
Instance cdb12c21 is running on node rhel6m1
Instance cdb12c22 is running on node rhel6m2

[grid@rhel6m1 ~]$ srvctl status asm -detail
ASM is running on rhel6m1
ASM is enabled.
ASM instance +ASM1 is running on node rhel6m1
Number of connected clients: 4
Client names: -MGMTDB:_mgmtdb:ora12c2-cluster cdb12c21:cdb12c2:ora12c2-cluster cdb12c22:cdb12c2:ora12c2-cluster rhel6m1.domain.net:_OCR:ora12c2-cluster
Starting the ASM instance on node 2 again will relocate the database instance to local ASM instance.
[grid@rhel6m2 ~]$ srvctl start asm -node rhel6m2

[grid@rhel6m1 ~]$ srvctl status asm -detail
ASM is running on rhel6m2,rhel6m1
ASM is enabled.
ASM instance +ASM1 is running on node rhel6m1
Number of connected clients: 3
Client names: -MGMTDB:_mgmtdb:ora12c2-cluster cdb12c21:cdb12c2:ora12c2-cluster rhel6m1.domain.net:_OCR:ora12c2-cluster
ASM instance +ASM2 is running on node rhel6m2
Number of connected clients: 2
Client names: cdb12c22:cdb12c2:ora12c2-cluster rhel6m2.domain.net:_OCR:ora12c2-cluster
Unlike in 12.1 where relocation had to be done manually, on 12.2 the relocation process happens automatically. Following could be observed on alert logs of node 1 and node 2. From node 1 ASM alert log
2017-05-31T10:13:05.648771+01:00
NOTE: relocating client cdb12c22:cdb12c2:ora12c2-cluster (reg:2404143953) to its local instance; Message from node 2 - ASM Instance +ASM2 is up
NOTE: cleaned up ASM client cdb12c22:cdb12c2:ora12c2-cluster connection state (reg:2404143953)
From node 2 ASM alert log
NOTE: registered owner id 0x10006 for cdb12c22:cdb12c2:ora12c2-cluster (reconnecting)
NOTE: Flex client cdb12c22:cdb12c2:ora12c2-cluster re-registered, osid 22652, mbr 0x0, asmb 28041 (reg:163318476)
Related Posts
Installing 12c (12.1.0.1) RAC on RHEL 6 with Role Separation - Database Software
Installing 12c (12.1.0.1) RAC on RHEL 6 with Role Separation - Creating CDB & PDB
Posted by at
Labels: , , , , , , ,
Subscribe to: Posts (Atom)