31 December, 2016

12cR1 RAC Posts -- 2 : Convert AdminManaged DB to PolicyManaged

I have an AdminManaged Database in my (2node) RAC Cluster.

How do I convert it to PolicyManaged ?

(Yes, let me admit :  It makes no sense to have PolicyManaged on a 2node Cluster.  But since I can't create an 8 or 16 node Cluster (with multiple databases to boot ?!), let me demonstrate with a 2node  Cluster.  The principle remains the same).

First, I show the configuration of the database in the Cluster :
[oracle@collabn1 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node collabn1
Instance RAC2 is running on node collabn2
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/spfileRAC1.ora
Password file: +DATA/RAC/PASSWORD/pwdrac.277.931824933
Domain: racattack
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: FRA,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: RAC1,RAC2
Configured nodes: collabn1,collabn2
Database is administrator managed
[oracle@collabn1 ~]$


This is the current definition of server pool(s) :
[oracle@collabn1 ~]$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names: collabn1,collabn2
[oracle@collabn1 ~]$


So, we see that I don't have any Server Pools defined. Only the default FREE and GENERIC (for AdminManaged database(s)) exist.
I now proceed to remove the database from the configuration.
[oracle@collabn1 ~]$ srvctl stop database -d RAC
[oracle@collabn1 ~]$ srvctl remove database -d RAC
Remove the database RAC? (y/[n]) y
[oracle@collabn1 ~]$


I now create a new (custom) Server Pool (called "MyPool").
[oracle@collabn1 ~]$ srvctl add srvpool -serverpool MyPool -importance 100 -min 1 -max 2 \
> -servers "collabn1,collabn2" -verbose
[oracle@collabn1 ~]$ srvctl config srvpool -serverpool MyPool
Server pool name: MyPool
Importance: 100, Min: 1, Max: 2
Category:
Candidate server names: collabn1,collabn2
[oracle@collabn1 ~]$


So, now with an "upto 2nodes" Server Pool, I add my database to it.
[oracle@collabn1 ~]$ srvctl add database -d RAC -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 \
> -serverpool MyPool -verbose
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: MyPool
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is policy managed
[oracle@collabn1 ~]$


This shows that RAC is now a PolicyManaged database in the "MyPool" Server Pool !
Can I now start the database and check on the instance(s) ?
[oracle@collabn1 ~]$ srvctl start database -d RAC
PRCR-1079 : Failed to start resource ora.rac.db
CRS-5017: The resource action "ora.rac.db start" encountered the following error:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initRAC_2.ora'
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/collabn2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.rac.db' on 'collabn2' failed
CRS-5017: The resource action "ora.rac.db start" encountered the following error:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initRAC_1.ora'
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/collabn1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.rac.db' on 'collabn1' failed
CRS-2632: There are no more servers to try to place resource 'ora.rac.db' on that would satisfy its placement policy
[oracle@collabn1 ~]$


Yes, of course. I need to create initRAC_1.ora and initRAC_2.ora.
After creating the new parameter files (pointing to the SPFILE in the ASM Diskgroup), I try again.
[oracle@collabn1 ~]$ srvctl start database -d RAC
[oracle@collabn1 ~]$ srvctl status database -d RAC
Instance RAC_1 is running on node collabn1
Instance RAC_2 is running on node collabn2
[oracle@collabn1 ~]$ ps -ef |grep smon
oracle    3447     1  0 11:37 ?        00:00:00 asm_smon_+ASM1
root      3605     1  0 11:37 ?        00:00:11 /u01/app/12.1.0/grid/bin/osysmond.bin
oracle    4203     1  0 11:38 ?        00:00:00 mdb_smon_-MGMTDB
oracle   22882     1  0 12:08 ?        00:00:00 ora_smon_RAC_1
oracle   23422 12657  0 12:10 pts/0    00:00:00 grep smon
[oracle@collabn1 ~]$
[oracle@collabn2 ~]$ ps -ef |grep smon
oracle    3495     1  0 11:41 ?        00:00:00 asm_smon_+ASM2
root      3593     1  0 11:41 ?        00:00:09 /u01/app/12.1.0/grid/bin/osysmond.bin
oracle   15973     1  0 12:08 ?        00:00:00 ora_smon_RAC_2
oracle   16647  4582  0 12:10 pts/0    00:00:00 grep smon
[oracle@collabn2 ~]$
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file:
Domain: racattack
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: MyPool
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is policy managed
[oracle@collabn1 ~]$


Yes, I now have instances RAC_1 and RAC_2 (instead of RAC1 and RAC2) now running. If I had multiple (4 or more ?!) nodes (and a Server Pool configuration to match), there's no guarantee that RAC_1 starts on collabn1 (and RAC_2 on collabn2).  These are "floating" instances that can start on any nodes in the Cluster.


(UPDATE : It seems that when I shutdown a node, a PolicyManaged Instance is SHUTDOWN ABORT, unlike an AdminManaged Instance which gets SHUTDOWN NORMAL ?)

.
.
.

24 December, 2016

12cR1 RAC Posts -- 1 : Grid Infrastructure Install completed (first cycle)

Just as I had posted 11gR2 RAC Posts in 2014  (listed here), I plan to post some 12cR1 RAC (GI, ASM) posts over the next few weeks.

Here's my Grid Infrastructure up and running.  (Yes, I used racattack for this first 12cR1 RAC setup.)

[root@collabn1 ~]# crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.SHARED.advm
               ONLINE  ONLINE       collabn1                 Volume device /dev/a
                                                             sm/shared-141 is onl
                                                             ine,STABLE
               ONLINE  ONLINE       collabn2                 Volume device /dev/a
                                                             sm/shared-141 is onl
                                                             ine,STABLE
ora.DATA.dg
               ONLINE  ONLINE       collabn1                 STABLE
               ONLINE  ONLINE       collabn2                 STABLE
ora.FRA.dg
               ONLINE  ONLINE       collabn1                 STABLE
               ONLINE  ONLINE       collabn2                 STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       collabn1                 STABLE
               ONLINE  ONLINE       collabn2                 STABLE
ora.asm
               ONLINE  ONLINE       collabn1                 Started,STABLE
               ONLINE  ONLINE       collabn2                 Started,STABLE
ora.data.shared.acfs
               ONLINE  ONLINE       collabn1                 mounted on /shared,S
                                                             TABLE
               ONLINE  ONLINE       collabn2                 mounted on /shared,S
                                                             TABLE
ora.net1.network
               ONLINE  ONLINE       collabn1                 STABLE
               ONLINE  ONLINE       collabn2                 STABLE
ora.ons
               ONLINE  ONLINE       collabn1                 STABLE
               ONLINE  ONLINE       collabn2                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       collabn2                 STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       collabn1                 STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       collabn1                 STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       collabn1                 169.254.3.70 172.16.
                                                             100.51,STABLE
ora.collabn1.vip
      1        ONLINE  ONLINE       collabn1                 STABLE
ora.collabn2.vip
      1        ONLINE  ONLINE       collabn2                 STABLE
ora.cvu
      1        ONLINE  ONLINE       collabn1                 STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       collabn1                 Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       collabn1                 STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       collabn2                 STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       collabn1                 STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       collabn1                 STABLE
--------------------------------------------------------------------------------
[root@collabn1 ~]#
[root@collabn1 ~]# crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   96fbcb40bfeb4ff7bf18881adcfef149 (/dev/asm-disk1) [DATA]
Located 1 voting disk(s).
[root@collabn1 ~]#
[root@collabn1 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          4
         Total space (kbytes)     :     409568
         Used space (kbytes)      :       1632
         Available space (kbytes) :     407936
         ID                       :  827167720
         Device/File Name         :      +DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check succeeded

[root@collabn1 ~]#
[root@collabn1 ~]# nslookup collabn-cluster-scan
Server:         192.168.78.51
Address:        192.168.78.51#53

Name:   collabn-cluster-scan.racattack
Address: 192.168.78.252
Name:   collabn-cluster-scan.racattack
Address: 192.168.78.253
Name:   collabn-cluster-scan.racattack
Address: 192.168.78.251

[root@collabn1 ~]#


I hope to run a few cycles of setups, switching to different node names, IPs, DiskGroup names etc over the next few weeks).
.
.
.

06 December, 2016

12.2 New Features -- 5 : Memory Parameters for Pluggable Database

12.2 allows Instance Memory parameters to be configured at the PDB level.

[timestamp in UTC]

[oracle@HKCORCL ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 6 13:56:28 2016

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


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

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 2544M
sga_min_size                         big integer 0
sga_target                           big integer 2544M
unified_audit_sga_queue_size         integer     1048576
SQL> show parameter db_cach

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 0
SQL>


Those are parameters set at the CDB level. Let's see the PDB.

SQL> alter session set container = PDB1;

Session altered.

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga            boolean     FALSE
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 2544M
sga_min_size                         big integer 0
sga_target                           big integer 0
unified_audit_sga_queue_size         integer     1048576
SQL> show parameter db_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 0
SQL> alter system set db_cache_size=400M;

System altered.

SQL>
SQL> alter system set sga_target=512M;
alter system set sga_target=512M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-56750: invalid value 536870912 for parameter sga_target; must be larger
than 200% of parameter db_cache_size


SQL> alter system set sga_target=810M;

System altered.

SQL> alter system set shared_pool_size=256M;

System altered.

SQL> show parameter db_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 400M
SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 810M
SQL> show parameter shared_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 26004684
shared_pool_size                     big integer 256M
SQL>
SQL> alter system set pga_aggregate_target=128M;

System altered.

SQL>


Returning to the CDB ...

SQL> connect / as sysdba
Connected.
SQL> show parameter db_cache

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice                      string      ON
db_cache_size                        big integer 0
SQL> show parameter sga_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 2544M
SQL> show parameter shared_pool

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 26004684
shared_pool_size                     big integer 0
SQL> show parameter pga_aggergate_target
SQL> show parameter pga_aggregate_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 1775294400
SQL>


Thus, multiple PDBs can have their own private target and limits (even an SGA_MIN_SIZE) all shared within the single instance that they co-exist in.
Note : The requirement is that MEMORY_TARGET is to be not set.
.
.
.

01 December, 2016

12.2 New Features -- 4 : AWR for Pluggable Database

12.2 now allows AWR Snapshots and Reports to be created at the PDB level.

Here I demonstrate a Manual Snapshot.  Although Automatic PDB AWR Snapshots are possible (with the AWR_PDB_AUTOFLUSH_ENABLED parameter) , they are disabled by default and Oracle recommends Manual Snapshots.

[timestamps in UTC]

SQL> connect / as sysdba
Connected.
SQL> alter session set container=PDB1;

Session altered.

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL>


I then proceed to create an AWR Report, still in the PDB1 container.

SQL> @?/rdbms/admin/awrrpt

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.  Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'          HTML format (default)
'text'          Text format
'active-html'   Includes Performance Hub active report

Enter value for report_type: text

Type Specified:  text

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




Current Instance
~~~~~~~~~~~~~~~~
DB Id          DB Name        Inst Num       Instance       Container Name
-------------- -------------- -------------- -------------- --------------
 3774315809     HKCORCL                     1 HKCORCL        PDB1


Root DB Id      Container DB Id AWR DB Id
--------------- --------------- ---------------
    947935822      3774315809      3774315809









Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num   DB Name      Instance     Host
------------ ---------- ---------    ----------   ------
  3774315809     1      HKCORCL      HKCORCL      HKCORCL.comp

Using 3774315809 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots
Instance     DB Name      Snap Id       Snap Started    Snap Level
------------ ------------ ---------- ------------------ ----------

HKCORCL      HKCORCL              1  01 Dec 2016 08:48    1
                                  2  01 Dec 2016 08:49    1
                                  3  01 Dec 2016 08:52    1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 3
End   Snapshot Id specified: 3




Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1_3.txt.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_1_3.txt


Here's a look at the header of the AWR report.

WORKLOAD REPOSITORY PDB report (PDB snapshots)

DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
HKCORCL       3774315809 HKCORCL     PRIMARY          EE      12.2.0.1.0 NO  NO

Instance     Inst Num Startup Time
------------ -------- ---------------
HKCORCL             1 16-Nov-16 06:13

    PDB Name PDB Id PDB DB Id        Open Time
------------ ------ ---------- ---------------
PDB1               3  3774315809 25-Nov-16 14:11

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
HKCORCL.compute- Linux x86 64-bit                    2     2       1       7.05

              Snap Id      Snap Time      Sessions Curs/Sess
            --------- ------------------- -------- ---------
Begin Snap:         1 01-Dec-16 08:48:46         0       4.0
  End Snap:         3 01-Dec-16 08:52:08         1      12.0
   Elapsed:                3.36 (mins)
   DB Time:                0.29 (mins)

Load Profile                    Per Second   Per Transaction  Per Exec  Per Call
~~~~~~~~~~~~~~~            ---------------   --------------- --------- ---------
             DB Time(s):               0.1               4.3      0.00      0.06
              DB CPU(s):               0.1               2.9      0.00      0.04
      Background CPU(s):               0.0               0.0      0.00      0.00
      Redo size (bytes):         138,443.8       6,976,599.0
  Logical read (blocks):           1,798.4          90,625.0
          Block changes:             282.3          14,224.3
 Physical read (blocks):              21.0           1,055.8
Physical write (blocks):               0.7              34.5
       Read IO requests:              20.9           1,051.8
      Write IO requests:               0.3              12.5
           Read IO (MB):               0.2               8.3
          Write IO (MB):               0.0               0.3
           IM scan rows:               0.0               0.0
Session Logical Read IM:               0.0               0.0
             User calls:               1.5              77.5
           Parses (SQL):              17.9             904.0
      Hard parses (SQL):               3.2             161.5
     SQL Work Area (MB):               2.5             123.5
                 Logons:               0.0               1.0
         Executes (SQL):              45.7           2,302.3
              Rollbacks:               0.0               0.0
           Transactions:               0.0

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Avg   % DB Wait
Event                                Waits Time (sec)      Wait   time Class
------------------------------ ----------- ---------- --------- ------ --------
DB CPU                                           11.5             66.9
db file sequential read              3,758        2.5  667.74us   14.6 User I/O
direct path write                       23         .7   29.08ms    3.9 User I/O
flashback log file sync                 36         .6   16.98ms    3.6 User I/O
local write wait                        12         .4   37.17ms    2.6 User I/O
acknowledge over PGA limit               9         .1    9.50ms     .5 Schedule
control file sequential read           189         .1  293.42us     .3 System I
PGA memory operation                 3,687          0   11.02us     .2 Other
db file scattered read                   4          0    8.32ms     .2 User I/O
log file sync                            3          0    9.35ms     .2 Commit


The Header identifies the PDB being reported on.Note that Snapshots 1 to 3 are local to the PDB and are not in the Root.  PDB Snapshots can be maintained (create or drop snapshot) in the same manner as CDB snapshots.  (Note : PDB AWR Snapshots are in the view AWR_PDB_SNAPSHOT,  not DBA_HIST_SNAPSHOT).




In contrast, this below is the Header for a CDB where Automatic Snapshots have meant Snap IDs are already at 379,380.  Thus, the CDB snapshots are different from the PDB snapshots.

WORKLOAD REPOSITORY report for

DB Name         DB Id    Unique Name DB Role          Edition Release    RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
HKCORCL        947935822 HKCORCL     PRIMARY          EE      12.2.0.1.0 NO  YES

Instance     Inst Num Startup Time
------------ -------- ---------------
HKCORCL             1 16-Nov-16 06:13

Host Name        Platform                         CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
HKCORCL.compute- Linux x86 64-bit                    2     2       1       7.05

              Snap Id      Snap Time      Sessions Curs/Sess  PDBs
            --------- ------------------- -------- --------- -----
Begin Snap:       379 01-Dec-16 08:00:47        51        .6     2
  End Snap:       380 01-Dec-16 09:00:09        62        .9     2
   Elapsed:               59.36 (mins)
   DB Time:                1.14 (mins)



Note how it doesn't identify a PDB.

You need to be explicitly connected to a PDB before awrrpt shows you the option to generate PDB-level AWR report.
.
.
.