Thinking Out Loud

February 20, 2019

opatchauto is not that dumb

Filed under: 12c,PSU,RAC — mdinh @ 11:41 pm

I find it ironic that we want to automate yet fear automation.

Per documentation, ACFS Support On OS Platforms (Certification Matrix). (Doc ID 1369107.1),
the following patch is required to implement ACFS:

p22810422_12102160419forACFS_Linux-x86-64.zip
Patch for Bug# 22810422
UEKR4 SUPPORT FOR ACFS(Patch 22810422)

I had inquired why opatchauto is not used to patch the entire system versus manual patching for GI ONLY.

To patch GI home and all Oracle RAC database homes of the same version:
# opatchauto apply _UNZIPPED_PATCH_LOCATION_/22810422 -ocmrf _ocm response file_

OCM is not included in OPatch binaries since OPatch version 12.2.0.1.5; therefore, -ocmrf is not needed.
Reason for GI only patching is simply because we only need to do it to enable ACFS support.

Rationale makes sense and typically ACFS is only applied to GI home.

Being curious, shouldn’t opatchauto know what homes to apply the patch where applicable?
Wouldn’t be easier to execute opatchauto versus performing manual steps?

What do you think and which approach would you use?

Here are the results from applying patch 22810422.


[oracle@racnode-dc1-2 22810422]$ pwd
/sf_OracleSoftware/22810422

[oracle@racnode-dc1-2 22810422]$ sudo su -
Last login: Wed Feb 20 23:13:52 CET 2019 on pts/0

[root@racnode-dc1-2 ~]# . /media/patch/gi.env
ORACLE_SID = [root] ? The Oracle base has been set to /u01/app/oracle
ORACLE_SID=+ASM2
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/12.1.0.1/grid
ORACLE_HOME=/u01/app/12.1.0.1/grid
Oracle Instance alive for sid "+ASM2"

[root@racnode-dc1-2 ~]# export PATH=$PATH:$GRID_HOME/OPatch

[root@racnode-dc1-2 ~]# opatchauto apply /sf_OracleSoftware/22810422 -analyze

OPatchauto session is initiated at Wed Feb 20 23:21:46 2019

System initialization log file is /u01/app/12.1.0.1/grid/cfgtoollogs/opatchautodb/systemconfig2019-02-20_11-21-53PM.log.

Session log file is /u01/app/12.1.0.1/grid/cfgtoollogs/opatchauto/opatchauto2019-02-20_11-22-12PM.log
The id for this session is YBG6

Executing OPatch prereq operations to verify patch applicability on home /u01/app/12.1.0.1/grid

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/12.1.0.1/db1
Patch applicability verified successfully on home /u01/app/oracle/12.1.0.1/db1

Patch applicability verified successfully on home /u01/app/12.1.0.1/grid


Verifying SQL patch applicability on home /u01/app/oracle/12.1.0.1/db1
SQL patch applicability verified successfully on home /u01/app/oracle/12.1.0.1/db1

OPatchAuto successful.

--------------------------------Summary--------------------------------

Analysis for applying patches has completed successfully:

Host:racnode-dc1-2
RAC Home:/u01/app/oracle/12.1.0.1/db1


==Following patches were SKIPPED:

Patch: /sf_OracleSoftware/22810422/22810422
Reason: This patch is not applicable to this specified target type - "rac_database"


Host:racnode-dc1-2
CRS Home:/u01/app/12.1.0.1/grid


==Following patches were SUCCESSFULLY analyzed to be applied:

Patch: /sf_OracleSoftware/22810422/22810422
Log: /u01/app/12.1.0.1/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-02-20_23-22-24PM_1.log



OPatchauto session completed at Wed Feb 20 23:24:53 2019
Time taken to complete the session 3 minutes, 7 seconds


[root@racnode-dc1-2 ~]# opatchauto apply /sf_OracleSoftware/22810422

OPatchauto session is initiated at Wed Feb 20 23:25:12 2019

System initialization log file is /u01/app/12.1.0.1/grid/cfgtoollogs/opatchautodb/systemconfig2019-02-20_11-25-19PM.log.

Session log file is /u01/app/12.1.0.1/grid/cfgtoollogs/opatchauto/opatchauto2019-02-20_11-25-38PM.log
The id for this session is 3BYS

Executing OPatch prereq operations to verify patch applicability on home /u01/app/12.1.0.1/grid

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/12.1.0.1/db1
Patch applicability verified successfully on home /u01/app/oracle/12.1.0.1/db1

Patch applicability verified successfully on home /u01/app/12.1.0.1/grid


Verifying SQL patch applicability on home /u01/app/oracle/12.1.0.1/db1
SQL patch applicability verified successfully on home /u01/app/oracle/12.1.0.1/db1


Preparing to bring down database service on home /u01/app/oracle/12.1.0.1/db1
Successfully prepared home /u01/app/oracle/12.1.0.1/db1 to bring down database service


Bringing down CRS service on home /u01/app/12.1.0.1/grid
Prepatch operation log file location: /u01/app/12.1.0.1/grid/cfgtoollogs/crsconfig/crspatch_racnode-dc1-2_2019-02-20_11-28-00PM.log
CRS service brought down successfully on home /u01/app/12.1.0.1/grid


Start applying binary patch on home /u01/app/12.1.0.1/grid
Binary patch applied successfully on home /u01/app/12.1.0.1/grid


Starting CRS service on home /u01/app/12.1.0.1/grid
Postpatch operation log file location: /u01/app/12.1.0.1/grid/cfgtoollogs/crsconfig/crspatch_racnode-dc1-2_2019-02-20_11-36-59PM.log
CRS service started successfully on home /u01/app/12.1.0.1/grid


Preparing home /u01/app/oracle/12.1.0.1/db1 after database service restarted
No step execution required.........
Prepared home /u01/app/oracle/12.1.0.1/db1 successfully after database service restarted

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:racnode-dc1-2
RAC Home:/u01/app/oracle/12.1.0.1/db1
Summary:

==Following patches were SKIPPED:

Patch: /sf_OracleSoftware/22810422/22810422
Reason: This patch is not applicable to this specified target type - "rac_database"


Host:racnode-dc1-2
CRS Home:/u01/app/12.1.0.1/grid
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /sf_OracleSoftware/22810422/22810422
Log: /u01/app/12.1.0.1/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-02-20_23-30-39PM_1.log



OPatchauto session completed at Wed Feb 20 23:40:10 2019
Time taken to complete the session 14 minutes, 58 seconds
[root@racnode-dc1-2 ~]#

[oracle@racnode-dc1-2 ~]$ . /media/patch/gi.env
ORACLE_SID = [hawk2] ? The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_SID=+ASM2
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/12.1.0.1/grid
ORACLE_HOME=/u01/app/12.1.0.1/grid
Oracle Instance alive for sid "+ASM2"
[oracle@racnode-dc1-2 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
22810422;ACFS Interim patch for 22810422

OPatch succeeded.

[oracle@racnode-dc1-2 ~]$ . /media/patch/hawk.env
ORACLE_SID = [+ASM2] ? The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_UNQNAME=hawk
ORACLE_SID=hawk2
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/12.1.0.1/db1
Oracle Instance alive for sid "hawk2"
[oracle@racnode-dc1-2 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
There are no Interim patches installed in this Oracle Home "/u01/app/oracle/12.1.0.1/db1".

OPatch succeeded.
[oracle@racnode-dc1-2 ~]$

====================================================================================================

### Checking resources while patching racnode-dc1-1
[oracle@racnode-dc1-2 ~]$ crsctl stat res -t -w '((TARGET != ONLINE) or (STATE != ONLINE)'
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.hawk.db
      1        ONLINE  OFFLINE                               STABLE
ora.racnode-dc1-1.vip
      1        ONLINE  INTERMEDIATE racnode-dc1-2            FAILED OVER,STABLE
--------------------------------------------------------------------------------

[oracle@racnode-dc1-2 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.DATA.dg
               ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.FRA.dg
               ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.asm
               ONLINE  ONLINE       racnode-dc1-2            Started,STABLE
ora.net1.network
               ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.ons
               ONLINE  ONLINE       racnode-dc1-2            STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       racnode-dc1-2            169.254.178.60 172.1
                                                             6.9.11,STABLE
ora.cvu
      1        ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.hawk.db
      1        ONLINE  OFFLINE                               STABLE
      2        ONLINE  ONLINE       racnode-dc1-2            Open,STABLE
ora.mgmtdb
      1        ONLINE  ONLINE       racnode-dc1-2            Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.racnode-dc1-1.vip
      1        ONLINE  INTERMEDIATE racnode-dc1-2            FAILED OVER,STABLE
ora.racnode-dc1-2.vip
      1        ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       racnode-dc1-2            STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       racnode-dc1-2            STABLE
--------------------------------------------------------------------------------
[oracle@racnode-dc1-2 ~]$


====================================================================================================

[oracle@racnode-dc1-1 ~]$ sudo su -
Last login: Wed Feb 20 23:02:19 CET 2019 on pts/0

[root@racnode-dc1-1 ~]# . /media/patch/gi.env
ORACLE_SID = [root] ? The Oracle base has been set to /u01/app/oracle
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/12.1.0.1/grid
ORACLE_HOME=/u01/app/12.1.0.1/grid
Oracle Instance alive for sid "+ASM1"

[root@racnode-dc1-1 ~]# export PATH=$PATH:$GRID_HOME/OPatch

[root@racnode-dc1-1 ~]# opatchauto apply /sf_OracleSoftware/22810422 -analyze

OPatchauto session is initiated at Wed Feb 20 23:43:46 2019

System initialization log file is /u01/app/12.1.0.1/grid/cfgtoollogs/opatchautodb/systemconfig2019-02-20_11-43-54PM.log.

Session log file is /u01/app/12.1.0.1/grid/cfgtoollogs/opatchauto/opatchauto2019-02-20_11-44-12PM.log
The id for this session is M9KF

Executing OPatch prereq operations to verify patch applicability on home /u01/app/12.1.0.1/grid

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/12.1.0.1/db1
Patch applicability verified successfully on home /u01/app/oracle/12.1.0.1/db1

Patch applicability verified successfully on home /u01/app/12.1.0.1/grid


Verifying SQL patch applicability on home /u01/app/oracle/12.1.0.1/db1
SQL patch applicability verified successfully on home /u01/app/oracle/12.1.0.1/db1

OPatchAuto successful.

--------------------------------Summary--------------------------------

Analysis for applying patches has completed successfully:

Host:racnode-dc1-1
RAC Home:/u01/app/oracle/12.1.0.1/db1


==Following patches were SKIPPED:

Patch: /sf_OracleSoftware/22810422/22810422
Reason: This patch is not applicable to this specified target type - "rac_database"


Host:racnode-dc1-1
CRS Home:/u01/app/12.1.0.1/grid


==Following patches were SUCCESSFULLY analyzed to be applied:

Patch: /sf_OracleSoftware/22810422/22810422
Log: /u01/app/12.1.0.1/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-02-20_23-44-26PM_1.log



OPatchauto session completed at Wed Feb 20 23:46:31 2019
Time taken to complete the session 2 minutes, 45 seconds

[root@racnode-dc1-1 ~]# opatchauto apply /sf_OracleSoftware/22810422

OPatchauto session is initiated at Wed Feb 20 23:47:13 2019

System initialization log file is /u01/app/12.1.0.1/grid/cfgtoollogs/opatchautodb/systemconfig2019-02-20_11-47-20PM.log.

Session log file is /u01/app/12.1.0.1/grid/cfgtoollogs/opatchauto/opatchauto2019-02-20_11-47-38PM.log
The id for this session is RHMR

Executing OPatch prereq operations to verify patch applicability on home /u01/app/12.1.0.1/grid

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/12.1.0.1/db1
Patch applicability verified successfully on home /u01/app/oracle/12.1.0.1/db1

Patch applicability verified successfully on home /u01/app/12.1.0.1/grid


Verifying SQL patch applicability on home /u01/app/oracle/12.1.0.1/db1
SQL patch applicability verified successfully on home /u01/app/oracle/12.1.0.1/db1


Preparing to bring down database service on home /u01/app/oracle/12.1.0.1/db1
Successfully prepared home /u01/app/oracle/12.1.0.1/db1 to bring down database service


Bringing down CRS service on home /u01/app/12.1.0.1/grid
Prepatch operation log file location: /u01/app/12.1.0.1/grid/cfgtoollogs/crsconfig/crspatch_racnode-dc1-1_2019-02-20_11-50-01PM.log
CRS service brought down successfully on home /u01/app/12.1.0.1/grid


Start applying binary patch on home /u01/app/12.1.0.1/grid
Binary patch applied successfully on home /u01/app/12.1.0.1/grid


Starting CRS service on home /u01/app/12.1.0.1/grid
Postpatch operation log file location: /u01/app/12.1.0.1/grid/cfgtoollogs/crsconfig/crspatch_racnode-dc1-1_2019-02-20_11-58-57PM.log
CRS service started successfully on home /u01/app/12.1.0.1/grid


Preparing home /u01/app/oracle/12.1.0.1/db1 after database service restarted
No step execution required.........
Prepared home /u01/app/oracle/12.1.0.1/db1 successfully after database service restarted

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:racnode-dc1-1
RAC Home:/u01/app/oracle/12.1.0.1/db1
Summary:

==Following patches were SKIPPED:

Patch: /sf_OracleSoftware/22810422/22810422
Reason: This patch is not applicable to this specified target type - "rac_database"


Host:racnode-dc1-1
CRS Home:/u01/app/12.1.0.1/grid
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /sf_OracleSoftware/22810422/22810422
Log: /u01/app/12.1.0.1/grid/cfgtoollogs/opatchauto/core/opatch/opatch2019-02-20_23-52-37PM_1.log



OPatchauto session completed at Thu Feb 21 00:01:15 2019
Time taken to complete the session 14 minutes, 3 seconds

[root@racnode-dc1-1 ~]# logout

[oracle@racnode-dc1-1 ~]$ . /media/patch/gi.env
ORACLE_SID = [hawk1] ? The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
GRID_HOME=/u01/app/12.1.0.1/grid
ORACLE_HOME=/u01/app/12.1.0.1/grid
Oracle Instance alive for sid "+ASM1"
[oracle@racnode-dc1-1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
22810422;ACFS Interim patch for 22810422

OPatch succeeded.

[oracle@racnode-dc1-1 ~]$ . /media/patch/hawk.env
ORACLE_SID = [+ASM1] ? The Oracle base remains unchanged with value /u01/app/oracle
ORACLE_UNQNAME=hawk
ORACLE_SID=hawk1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/12.1.0.1/db1
Oracle Instance alive for sid "hawk1"
[oracle@racnode-dc1-1 ~]$ $ORACLE_HOME/OPatch/opatch lspatches
There are no Interim patches installed in this Oracle Home "/u01/app/oracle/12.1.0.1/db1".

OPatch succeeded.
[oracle@racnode-dc1-1 ~]$
Advertisements

December 21, 2018

Find Database Growth Using OEM Repository

Filed under: 12.2,oracle — mdinh @ 2:20 pm

Typically, what as been done is to schedule job for each database to collect database growth.

This may be problematic as it can be forgotten when new databases are created versus the likelihood of forgetting to add database to monitoring for OEM.

EM12c, EM13c : Querying the Repository Database for Building Reports using Metric Information (Doc ID 2347253.1)

Those raw data are inserted in various tables like EM_METRIC_VALUES for example. 
EM aggregates those management data by hour and by day. 
Those raw data are kept 7 days; the one hour aggregated data are kept 31 days, while one day aggregated data are kept one year.

How to obtain the Historical Database Total Used and Allocated Size from OEM Repository

The above blog post provided a good starting point.

This post is using query to collect database size (metric_name=’DATABASE_SIZE’) vs tablespace size (metric_name=’tbspAllocation’) to avoid having to sum all tablespaces to determine database size.

OMS: 13.2.0 and EMREP DB: 12.2.0

Comparison for METRIC_COLUMN between DATABASE_SIZE and tbspAllocation.

For tbspAllocation, the size was not clear and did not research further but it does appear to be GB.

SQL> select distinct metric_name, METRIC_COLUMN from sysman.mgmt$metric_daily where metric_name='tbspAllocation' order by 1;

METRIC_NAME                                                      METRIC_COLUMN
---------------------------------------------------------------- ----------------------------------------------------------------
tbspAllocation                                                   spaceUsed
tbspAllocation                                                   spaceAllocated

SQL> select distinct METRIC_COLUMN from sysman.mgmt$metric_daily WHERE metric_name='DATABASE_SIZE';

METRIC_COLUMN
----------------------------------------------------------------
ALLOCATED_GB
USED_GB

TARGET_TYPE used (not all results presented):

SQL> select distinct target_type from sysman.mgmt$metric_daily order by 1;

TARGET_TYPE
----------------------------------------------------------------
oracle_database
oracle_pdb
rac_database

METRIC_NAME used (not all results presented):

SQL> select distinct metric_name from sysman.mgmt$metric_daily order by 1;

METRIC_NAME
----------------------------------------------------------------
DATABASE_SIZE
tbspAllocation

DEMO:

SQL> @dbsize.sql
SQL> -- Michael Dinh : Dec 20, 2018
SQL> set echo off
Enter value for 1: perf

TARGET_NAME                                        TARGET_TYPE     MONTH_DT  USED_GB ALLOCATED_GB PREVIOUS_MONTH DIFF_USED_GB
-------------------------------------------------- --------------- --------- ------- ------------ -------------- ------------
xxxxperf                                           rac_database    01-MAR-18  2698.6       3526.8
                                                   rac_database    01-APR-18  2709.9       3526.8         2698.6        11.31
                                                   rac_database    01-MAY-18  2728.8       3526.8         2709.9        18.86
                                                   rac_database    01-JUN-18  2735.4       3548.8         2728.8         6.61
                                                   rac_database    01-JUL-18  2746.4       3548.8         2735.4        11.01
                                                   rac_database    01-AUG-18  2758.7       3548.8         2746.4        12.27
                                                   rac_database    01-SEP-18  2772.5       3548.8         2758.7        13.82
                                                   rac_database    01-OCT-18  4888.8       6207.8         2772.5       2116.3
                                                   rac_database    01-NOV-18  4647.8       6207.8         4888.8         -241
                                                   rac_database    01-DEC-18  3383.2       6207.8         4647.8        -1265
yyyyperf                                           oracle_database 01-MAR-18   63.07       395.58
                                                   oracle_database 01-APR-18   63.19       395.58          63.07          .12
                                                   oracle_database 01-MAY-18   64.33       395.58          63.19         1.14
                                                   oracle_database 01-JUN-18   64.81       395.58          64.33          .48
                                                   oracle_database 01-JUL-18    65.1       395.58          64.81          .29
                                                   oracle_database 01-AUG-18   65.22       395.58           65.1          .12
                                                   oracle_database 01-SEP-18   65.79       395.58          65.22          .57
                                                   oracle_database 01-OCT-18   68.18       395.58          65.79         2.39
                                                   oracle_database 01-NOV-18   75.79       395.72          68.18         7.61
                                                   oracle_database 01-DEC-18    80.4       395.72          75.79         4.61

29 rows selected.

SQL> @dbsize
SQL> -- Michael Dinh : Dec 20, 2018
SQL> set echo off
Enter value for 1: *

TARGET_NAME                                        TARGET_TYPE     MONTH_DT  USED_GB ALLOCATED_GB PREVIOUS_MONTH DIFF_USED_GB
-------------------------------------------------- --------------- --------- ------- ------------ -------------- ------------
CDByyyy_xxxxxxxxxxxxxxxxxxxxxxxxxx_CDBROOT         oracle_pdb      01-MAR-18    7.96        94.73
                                                   oracle_pdb      01-APR-18    3.44        94.73           7.96        -4.52
                                                   oracle_pdb      01-MAY-18   12.26        95.07           3.44         8.82
                                                   oracle_pdb      01-JUN-18   76.18        95.12          12.26        63.92
                                                   oracle_pdb      01-JUL-18   70.87        95.15          76.18        -5.31
                                                   oracle_pdb      01-AUG-18   77.63        95.15          70.87         6.76
                                                   oracle_pdb      01-SEP-18     4.9        95.15          77.63       -72.73
                                                   oracle_pdb      01-OCT-18       4        95.15            4.9          -.9
                                                   oracle_pdb      01-NOV-18   41.34        95.15              4        37.34
                                                   oracle_pdb      01-DEC-18   33.52        95.15          41.34        -7.82
CDByyyy_xxxxxxxxxxxxxxxxxxxxxxxxxx_xxxxxPDB        oracle_pdb      01-MAR-18  1610.6         2571
                                                   oracle_pdb      01-APR-18  1644.9         2571         1610.6        34.27
                                                   oracle_pdb      01-MAY-18  1659.3       2571.3         1644.9        14.43
                                                   oracle_pdb      01-JUN-18  1694.7       2571.4         1659.3        35.32
                                                   oracle_pdb      01-JUL-18  1753.8       2571.4         1694.7        59.18
                                                   oracle_pdb      01-AUG-18  1827.9       2571.4         1753.8        74.06
                                                   oracle_pdb      01-SEP-18  1900.8       2571.4         1827.9        72.91
                                                   oracle_pdb      01-OCT-18  1977.2       2571.4         1900.8        76.43
                                                   oracle_pdb      01-NOV-18  2044.8       2571.4         1977.2         67.6
                                                   oracle_pdb      01-DEC-18  2144.5       2571.4         2044.8        99.64

Script:

set line 200 verify off trimspool off tab off pages 1000 numw 6 echo on
-- Michael Dinh : Dec 20, 2018
set echo off
/*
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
*/
col target_name for a50
col target_type for a15
undefine 1
break on target_name
WITH dbsz AS (
SELECT
target_name, target_type, month_dt,
SUM(DECODE(metric_column, 'USED_GB', maximum)) used_gb,
SUM(DECODE(metric_column, 'ALLOCATED_GB', maximum)) allocated_gb
FROM (
SELECT target_name, target_type, trunc(rollup_timestamp,'MONTH') month_dt, metric_column, MAX(maximum) maximum
FROM sysman.mgmt$metric_daily
WHERE target_type IN ('rac_database','oracle_database','oracle_pdb')
AND metric_name = 'DATABASE_SIZE'
AND metric_column IN ('ALLOCATED_GB','USED_GB')
AND REGEXP_LIKE(target_name,'&&1','i')
GROUP BY target_name, target_type, trunc(rollup_timestamp,'MONTH'), metric_column
)
GROUP BY target_name, target_type, month_dt
ORDER BY target_name, month_dt
)
SELECT target_name, target_type, month_dt, used_gb, allocated_gb,
LAG(used_gb,1) OVER (PARTITION BY target_name ORDER BY target_name) previous_month,
used_gb-LAG(used_gb,1) OVER (PARTITION BY target_name ORDER BY target_name) diff_used_gb
FROM dbsz
ORDER BY target_name, month_dt
;

UPDATED SQL SCRIPT:

SQL> @dbsize.sql xxxprod
SQL> -- Michael Dinh : Dec 20, 2018
SQL> set echo off

TARGET_NAME                                        TARGET_TYPE     MONTH_DT  USED_GB ALLOCATED_GB PREVIOUS_MONTH DIFF_USED_GB
-------------------------------------------------- --------------- --------- ------- ------------ -------------- ------------
xxxprod                                            rac_database    31-MAR-18   333.2       704.42
                                                   rac_database    30-APR-18  336.65       704.42          333.2         3.45
                                                   rac_database    31-MAY-18  350.48       704.42         336.65        13.83
                                                   rac_database    30-JUN-18  423.47        714.1         350.48        72.99
                                                   rac_database    31-JUL-18  397.42        714.1         423.47       -26.05
                                                   rac_database    31-AUG-18  415.61        714.1         397.42        18.19
                                                   rac_database    30-SEP-18   417.2       714.69         415.61         1.59
                                                   rac_database    31-OCT-18  421.04       714.69          417.2         3.84
                                                   rac_database    30-NOV-18  425.35       715.37         421.04         4.31
                                                   rac_database    20-DEC-18  428.44       723.11         425.35         3.09

10 rows selected.

SQL> !cat dbsize.sql



SQL> !cat dbsize.sql
set line 200 verify off trimspool off tab off pages 1000 numw 6 echo on
-- Michael Dinh : Dec 20, 2018
set echo off
/*
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
How to obtain the Historical Database Total Used and Allocated Size from OEM Repository
*/
col target_name for a50
col target_type for a15
break on target_name
WITH dbsz AS (
SELECT
target_name, target_type, month_dt,
SUM(DECODE(metric_column, 'USED_GB', maximum)) used_gb,
SUM(DECODE(metric_column, 'ALLOCATED_GB', maximum)) allocated_gb
FROM (
-- This shows LATEST date of month
SELECT target_name, target_type, MAX(rollup_timestamp) month_dt, metric_column, MAX(maximum) maximum
-- This shows FIRST date of month
-- SELECT target_name, target_type, TRUNC(rollup_timestamp,'MONTH') month_dt, metric_column, MAX(maximum) maximum
FROM sysman.mgmt$metric_daily
WHERE target_type IN ('rac_database','oracle_database','oracle_pdb')
AND metric_name = 'DATABASE_SIZE'
AND metric_column IN ('ALLOCATED_GB','USED_GB')
AND REGEXP_LIKE(target_name,'&1','i')
GROUP BY target_name, target_type, TRUNC(rollup_timestamp,'MONTH'), metric_column
)
GROUP BY target_name, target_type, month_dt
-- ORDER BY target_name, month_dt
)
SELECT target_name, target_type, month_dt, used_gb, allocated_gb,
LAG(used_gb,1) OVER (PARTITION BY target_name ORDER BY target_name) previous_month,
used_gb-LAG(used_gb,1) OVER (PARTITION BY target_name ORDER BY target_name) diff_used_gb
FROM dbsz
ORDER BY target_name, month_dt
;
undefine 1

October 27, 2018

Troubleshooting GoldenGate OGG-00303: Unable to open credential store. Error code 43,490

Filed under: 12c,GoldenGate — mdinh @ 2:13 am

After applying Oracle GoldenGate V12.2.0.1.170919 for Oracle Database 12c OPTIMIZER Patch for Bug# 26849949, starting GoldenGate extract failed with OGG-00303: Unable to open credential store. Error code 43,490.

Here is what the report looks like.

$ head -50 E_LAX6.rpt
***********************************************************************
                 Oracle GoldenGate Capture for Oracle
 Version 12.2.0.1.170919 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_171030.0908_FBO
   Linux, x64, 64bit (optimized), Oracle 12c on Oct 30 2017 20:59:41
 
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2018-10-25 15:08:33
***********************************************************************

Operating System Version:
Linux
Version #2 SMP Wed Jul 11 12:11:36 PDT 2018, Release 4.1.12-94.8.5.el7uek.x86_64
Node: localhost
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 23154

Description: 

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************

2018-10-25 15:08:33  INFO    OGG-03059  Operating system character set identified as UTF-8.

2018-10-25 15:08:33  INFO    OGG-02695  ANSI SQL parameter syntax is used for parameter parsing.
EXTRACT e_lax
USERIDALIAS gguser

Source Context :
  SourceModule            : [er.init]
  SourceID                : [/scratch/aime/adestore/views/aime_adc4150431/oggcore/OpenSys/src/app/er/init.cpp]
  SourceFunction          : [get_infile_params]
  SourceLine              : [5554]
  ThreadBacktrace         : [11] elements
                          : [/u01/gg/12.2.0/libgglog.so(CMessageContext::AddThreadContext()+0x1b) [0x7f714024709b]]
                          : [/u01/gg/12.2.0/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x135) [0x7f7140241165]]
                          : [/u01/gg/12.2.0/libgglog.so(_MSG_ERR_STARTUP_PARAMERROR_ERRORTEXT(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x30) [0x7f71402308d0]]
                          : [/u01/gg/12.2.0/extract(get_infile_params(time_elt_def*, time_elt_def*, char**, ggs::gglib::ggdatasource::DataSourceParams&, ggs::Heartbeat::MapGeneratorParams&)+0x5da1) [0x5c4c91]]
                          : [/u01/gg/12.2.0/extract() [0x5f036a]]
                          : [/u01/gg/12.2.0/extract(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x60) [0x6cea60]]
                          : [/u01/gg/12.2.0/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*)+0x14d) [0x6cfcdd]]

 

Since I did not have the password for database gguser, I modified the password from the database and recreated credentialstore using ggsci.

info credentialstore
delete credentialstore
create wallet.
add credentialstore
alter credentialstore add user gguser alias gguser.
alter credentialstore add user GGUSER alias GGUSER
info credentialstore

Reason check credentialstore before deleting is to determine aliases.

GGSCI 1> info credentialstore

Reading from ./dircrd/:

Default domain: OracleGoldenGate

  Alias: gguser
  Userid: gguser

  Alias: GGUSER
  Userid: GGUSER

Here are the steps from ggserr.log.

2018-10-25 15:44:00  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): create wallet.
2018-10-25 15:44:00  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Created wallet at location 'dirwlt'.
2018-10-25 15:44:00  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Opened wallet at location 'dirwlt'.
2018-10-25 15:44:10  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): add credentialstore.
2018-10-25 15:44:10  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Credential store created in ./dircrd/.
2018-10-25 15:44:22  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): alter credentialstore add user gguser alias gguser.
2018-10-25 15:44:31  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Credential store in ./dircrd/ altered.
2018-10-25 15:44:55  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): alter credentialstore add user GGUSER alias GGUSER.
2018-10-25 15:45:06  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Credential store in ./dircrd/ altered.
2018-10-25 15:45:15  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): info credentialstore.
2018-10-25 15:45:15  INFO    OGG-02096  Oracle GoldenGate Command Interpreter for Oracle:  Reading from ./dircrd/:.

Here are the permissions for the files.

$ chmod 775 -R dirwlt/ dircrd/
$ ls -l dirwlt/ dircrd/
dircrd/:
total 4
-rwxrwxr-x 1 gguser oinstall 701 Oct 25 15:45 cwallet.sso

dirwlt/:
total 4
-rwxrwxr-x 1 gguser oinstall 290 Oct 25 15:44 cwallet.sso

Starting extract failed again!

2018-10-25 15:45:30  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (gguser): dblogin useridalias gguser.
2018-10-25 15:45:35  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (gguser): start e*.
2018-10-25 15:45:35  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host 10.80.27.191:39060 (START EXTRACT E_LAX ).
2018-10-25 15:45:35  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #5).
2018-10-25 15:45:35  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT E_LAX starting.
2018-10-25 15:45:35  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, e_lax.prm:  EXTRACT E_LAX starting.
2018-10-25 15:45:35  INFO    OGG-03059  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Operating system character set identified as UTF-8.
2018-10-25 15:45:35  INFO    OGG-02695  Oracle GoldenGate Capture for Oracle, e_lax.prm:  ANSI SQL parameter syntax is used for parameter parsing.
2018-10-25 15:45:35  ERROR   OGG-00303  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Unable to open credential store. Error code 43,490.
2018-10-25 15:45:35  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, e_lax.prm:  PROCESS ABENDING.

Set environment variables for ORACLE_HOME and ORACLE_SID for extract, and restart extract solved the issue.

FYI – environment variables already exist from OS.

2018-10-25 15:45:38  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (gguser): info all.
2018-10-25 15:52:44  INFO    OGG-00987  Oracle GoldenGate Command Interpreter for Oracle:  GGSCI command (ggsuser): start e*.
2018-10-25 15:52:44  INFO    OGG-00963  Oracle GoldenGate Manager for Oracle, mgr.prm:  Command received from GGSCI on host 10.80.27.191:39169 (START EXTRACT E_LAX ).
2018-10-25 15:52:44  INFO    OGG-00960  Oracle GoldenGate Manager for Oracle, mgr.prm:  Access granted (rule #5).
2018-10-25 15:52:44  INFO    OGG-00975  Oracle GoldenGate Manager for Oracle, mgr.prm:  EXTRACT E_LAX starting.
2018-10-25 15:52:44  INFO    OGG-00992  Oracle GoldenGate Capture for Oracle, e_lax.prm:  EXTRACT E_LAX starting.
2018-10-25 15:52:44  INFO    OGG-03059  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Operating system character set identified as UTF-8.
2018-10-25 15:52:44  INFO    OGG-02695  Oracle GoldenGate Capture for Oracle, e_lax.prm:  ANSI SQL parameter syntax is used for parameter parsing.
2018-10-25 15:52:44  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_2.
2018-10-25 15:52:44  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Successfully set environment variable ORACLE_SID=sourcedb.
2018-10-25 15:52:44  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Successfully set environment variable ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_2.
2018-10-25 15:52:44  INFO    OGG-02095  Oracle GoldenGate Capture for Oracle, e_lax.prm:  Successfully set environment variable ORACLE_SID=sourcedb.
2018-10-25 15:52:56  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, e_lax.prm:  EXTRACT E_LAX started.

Currently, I don’t know if patching caused the issue or if it is a pre-existing condition.

What’s interesting is the same issue happened for another system where environment variables for ORACLE_HOME and ORACLE_SID were not set for extract.

Instead of restarting just the extract, all the processes were stopped, and restarted.

It would be a good idea to stop and start Goldengate processes before patching in order to identify any pre-existing conditions.

September 23, 2018

12.1.0.2.0 ORA-01033: ORACLE initialization or shutdown in progress cascade standby

Filed under: Dataguard,12c — mdinh @ 3:22 pm

Objective is to create RAC cascade standby (olapdr) from existing standby (oltpdr) on the same host.

Cascade Standby: ORACLE_SID=olap1; db_name=oltp; db_unique_name=olapdr
Standby:         ORACLE_SID=oltp1; db_name=oltp; db_unique_name=oltpdr

 

Configuration for standby (oltpdr)

$ srvctl config database -d oltpdr
Spfile: +DATA/OLTPDR/spfileoltpdr.ora
Password file: +DATA/OLTPDR/orapwoltpdr

Copy existing password file from disk to ASM for cascade standby (olapdr)

This did not work and possibly different from password file at ASM.
Please don’t ask me why.

-rw-r----- 1 oracle oinstall 7680 Sep 18 13:28 orapwolap
-rw-r----- 1 oracle oinstall 7680 Sep 18 13:28 orapwolap1
-rw-r----- 1 oracle oinstall 7680 May 28 12:08 orapwoltp
-rw-r----- 1 oracle oinstall 7680 May 28 12:08 orapwoltp1

$ cp $ORACLE_HOME/dbs/orapwolap /tmp/orapwolapdr
ASMCMD> pwcopy /tmp/orapwolapdr +DATA/OLAPDR/orapwolapdr

Check standby (oltpdr)

oltp1> @dataguard.sql

Session altered.

*** v$database ***

DB              OPEN                   DATABASE                                REMOTE     SWITCHOVER         DATAGUARD  PRIMARY_DB
UNIQUE_NAME     MODE                   ROLE               PROTECTION_MODE      ARCHIVE    STATUS             BROKER     UNIQUE_NAME
--------------- ---------------------- ------------------ -------------------- ---------- ------------------ ---------- ---------------
oltpdr          READ ONLY WITH APPLY   PHYSICAL STANDBY   MAXIMUM PERFORMANCE  ENABLED    NOT ALLOWED        ENABLED    oltp

*** gv$archive_dest_status ***
                             DB                                        DATABASE                     RECOVERY
 INST  DEST TARGET           UNIQUE_NAME     DESTINATION               MODE            STATUS       MODE                    SCHEDULE PROCESS
----- ----- ---------------- --------------- ------------------------- --------------- ------------ ----------------------- -------- --------
    1     1 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST OPEN_READ-ONLY  VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
          5 REMOTE           olapdr          olapdr                    UNKNOWN         ERROR        IDLE                    ACTIVE   ARCH
         32 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST UNKNOWN         VALID        IDLE                    ACTIVE   RFS

    2     1 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST OPEN_READ-ONLY  VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
          5 REMOTE           olapdr          olapdr                    UNKNOWN         ERROR        IDLE                    ACTIVE   ARCH
         32 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST UNKNOWN         VALID        IDLE                    ACTIVE   RFS

6 rows selected.

 INST  DEST STATUS       SRL GAP_STATUS      ERROR
----- ----- ------------ --- --------------- --------------------------------------------------------------------------------
    1     1 VALID        NO                  NONE
          5 ERROR        NO  RESOLVABLE GAP  ORA-01033: ORACLE initialization or shutdown in progress
         32 VALID        NO                  NONE

    2     1 VALID        NO                  NONE
          5 ERROR        NO  RESOLVABLE GAP  ORA-01033: ORACLE initialization or shutdown in progress
         32 VALID        NO                  NONE

6 rows selected.

Suggestion from teammate is to copy password file at ASM from oltpdr to olapdr

ASMCMD> pwcopy +DATA/OLTPDR/orapwoltpdr /tmp/orapwolapdr
ASMCMD> pwcopy /tmp/orapwolapdr +DATA/OLAPDR/orapwolapdr

Check standby (oltpdr) and don’t forget to defer and enable dest.

oltp1> alter system set log_archive_dest_state_5=defer;

System altered.

oltp1> alter system set log_archive_dest_state_5=enable

oltp1> @dataguard.sql

Session altered.

*** v$database ***

DB              OPEN                   DATABASE                                REMOTE     SWITCHOVER         DATAGUARD  PRIMARY_DB
UNIQUE_NAME     MODE                   ROLE               PROTECTION_MODE      ARCHIVE    STATUS             BROKER     UNIQUE_NAME
--------------- ---------------------- ------------------ -------------------- ---------- ------------------ ---------- ---------------
oltpdr          READ ONLY WITH APPLY   PHYSICAL STANDBY   MAXIMUM PERFORMANCE  ENABLED    NOT ALLOWED        ENABLED    oltp

*** gv$archive_dest_status ***
                             DB                                        DATABASE                     RECOVERY
 INST  DEST TARGET           UNIQUE_NAME     DESTINATION               MODE            STATUS       MODE                    SCHEDULE PROCESS
----- ----- ---------------- --------------- ------------------------- --------------- ------------ ----------------------- -------- --------
    1     1 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST OPEN_READ-ONLY  VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
          5 REMOTE           olapdr          olapdr                    MOUNTED-STANDBY VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
         32 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST UNKNOWN         VALID        IDLE                    ACTIVE   RFS

    2     1 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST OPEN_READ-ONLY  VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
          5 REMOTE           olapdr          olapdr                    MOUNTED-STANDBY VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
         32 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST UNKNOWN         VALID        IDLE                    ACTIVE   RFS

6 rows selected.

 INST  DEST STATUS       SRL GAP_STATUS      ERROR
----- ----- ------------ --- --------------- --------------------------------------------------------------------------------
    1     1 VALID        NO                  NONE
          5 VALID        YES RESOLVABLE GAP  NONE
         32 VALID        NO                  NONE

    2     1 VALID        NO                  NONE
          5 VALID        YES RESOLVABLE GAP  NONE
         32 VALID        NO                  NONE

6 rows selected.

Check standby (olapdr)

olap1> @dataguard.sql

Session altered.

*** v$database ***

DB              OPEN                   DATABASE                                REMOTE     SWITCHOVER         DATAGUARD  PRIMARY_DB
UNIQUE_NAME     MODE                   ROLE               PROTECTION_MODE      ARCHIVE    STATUS             BROKER     UNIQUE_NAME
--------------- ---------------------- ------------------ -------------------- ---------- ------------------ ---------- ---------------
olapdr          MOUNTED                PHYSICAL STANDBY   MAXIMUM PERFORMANCE  ENABLED    NOT ALLOWED        DISABLED   oltp

*** gv$archive_dest_status ***
                             DB                                        DATABASE                     RECOVERY
 INST  DEST TARGET           UNIQUE_NAME     DESTINATION               MODE            STATUS       MODE                    SCHEDULE PROCESS
----- ----- ---------------- --------------- ------------------------- --------------- ------------ ----------------------- -------- --------
    1     1 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST MOUNTED-STANDBY VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
         32 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST UNKNOWN         VALID        IDLE                    ACTIVE   RFS

    2     1 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST MOUNTED-STANDBY VALID        MANAGED REAL TIME APPLY ACTIVE   ARCH
         32 LOCAL            NONE            USE_DB_RECOVERY_FILE_DEST UNKNOWN         VALID        IDLE                    ACTIVE   RFS

 INST  DEST STATUS       SRL GAP_STATUS      ERROR
----- ----- ------------ --- --------------- --------------------------------------------------------------------------------
    1     1 VALID        NO                  NONE
         32 VALID        NO                  NONE

    2     1 VALID        NO                  NONE
         32 VALID        NO                  NONE

*** v$archived_log ***

TIME                  THREAD# ARCHIVED  APPLIED      GAP
-------------------- -------- -------- -------- --------
23-SEP-2018 09:26:05        1    37550    37467       83
23-SEP-2018 09:26:05        2    32631    32566       65

*** gv$managed_standby ***
                                        CLIENT                                               DELAY
 INST PID                       THREAD# PROCESS    PROCESS  STATUS       SEQUENCE#   BLOCK#   MINS
----- ------------------------ -------- ---------- -------- ------------ --------- -------- ------
    1 399156                          2 N/A        MRP0     APPLYING_LOG     32570   721960      0

olap1> r
  1  select inst_id inst,PID,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where status not in ('CLOSING','IDLE','CONNECTED')
  4  order by inst_id, status desc, thread#, sequence#
  5*
                                        CLIENT                                               DELAY
 INST PID                       THREAD# PROCESS    PROCESS  STATUS       SEQUENCE#   BLOCK#   MINS
----- ------------------------ -------- ---------- -------- ------------ --------- -------- ------
    1 399156                          2 N/A        MRP0     APPLYING_LOG     32570   733658      0

olap1> r
  1  select inst_id inst,PID,thread#,client_process,process,status,sequence#,block#,DELAY_MINS
  2  from gv$managed_standby
  3  where status not in ('CLOSING','IDLE','CONNECTED')
  4  order by inst_id, status desc, thread#, sequence#
  5*

                                       CLIENT                                               DELAY
 INST PID                       THREAD# PROCESS    PROCESS  STATUS       SEQUENCE#   BLOCK#   MINS
----- ------------------------ -------- ---------- -------- ------------ --------- -------- ------
    1 399156                          1 N/A        MRP0     APPLYING_LOG     37474   499677      0
    2 366691                          2 UNKNOWN    RFS      RECEIVING        32632  1073153      0

olap1> 

Next, configure DataGuard Broker and not looking pretty.

September 21, 2018

RMAN-03002: ORA-19693: backup piece already included

Filed under: 12c,RMAN — mdinh @ 11:36 pm

I have been cursed trying to create 25TB standby database.

Active duplication using standby as source failed due to bug.

Backup based duplication using standby as source failed due to bug again.

Now performing traditional restore.

Both attempts failed with RMAN-20261: ambiguous backup piece handle

RMAN> list backuppiece '/bkup/ovtdkik0_1_1.bkp';
RMAN> change backuppiece '/bkup/ovtdkik0_1_1.bkp' uncatalog;

What’s in the backup?

RMAN> spool log to /tmp/list.log
RMAN> list backup;
RMAN> exit

There are 2 identical backuppiece and don’t know how this could have happened.

$ grep ovtdkik0_1_1 /tmp/list.log
    201792  1   AVAILABLE   /bkup/ovtdkik0_1_1.bkp
    202262  1   AVAILABLE   /bkup/ovtdkik0_1_1.bkp

RMAN> delete backuppiece 202262;

Restart restore and is running again.

Don’t Drop Your Career Using Drop Database

Filed under: 12c — mdinh @ 3:12 am

I first learned about drop database in 2007.

Environment contains standby database oltpdr.
Duplicate standby database olapdr on the same host using oltpdr as source failed during restore phase.
Clean up data files from failed olapdr duplication.

Check database olapdr.

olap1> show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      oltp
db_unique_name                       string      olapdr

olap1> select count(*) from gv$session;

  COUNT(*)
----------
        90

Elapsed: 00:00:00.00
olap1> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

Elapsed: 00:00:00.03
olap1> startup force mount restrict exclusive;
ORACLE instance started.

Total System Global Area 2.5770E+10 bytes
Fixed Size                  6870952 bytes
Variable Size            5625976920 bytes
Database Buffers         1.9998E+10 bytes
Redo Buffers              138514432 bytes
Database mounted.

olap1> show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      oltp
db_unique_name                       string      olapdr

olap1> select count(*) from gv$session;

  COUNT(*)
----------
        92

Elapsed: 00:00:00.01
olap1> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

Elapsed: 00:00:00.04

At this point, I was ready to run drop database and somehow an angel was watching over me and I decided to check v$datafile.

olap1> select name from v$datafile where rownum < 10;

NAME
-----------------------------------------------------------
+DATA/OLTPDR/DATAFILE/system.4069.986394171
+DATA/OLTPDR/DATAFILE/dev_odi_temp.4067.986394187
+DATA/OLTPDR/DATAFILE/sysaux.4458.985845085
+DATA/OLTPDR/DATAFILE/big_dmstaging_data_new_2.4687.986498821
+DATA/OLTPDR/DATAFILE/account_toll_index.3799.985714921
+DATA/OLTPDR/DATAFILE/users.2524.985777377
+DATA/OLTPDR/DATAFILE/dev_ias_temp.4141.985846937
+DATA/OLTPDR/DATAFILE/dev_stb.4143.985846937
+DATA/OLTPDR/DATAFILE/dev_odi_user.4144.985846937

9 rows selected.

Elapsed: 00:00:00.01

olap1> exit

Strange data files are the same for source and target.

oltp1> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Elapsed: 00:00:00.07
oltp1> select name from v$datafile where rownum < 10;

NAME
-----------------------------------------------------------
+DATA/OLTPDR/DATAFILE/system.4069.986394171
+DATA/OLTPDR/DATAFILE/dev_odi_temp.4067.986394187
+DATA/OLTPDR/DATAFILE/sysaux.4458.985845085
+DATA/OLTPDR/DATAFILE/big_dmstaging_data_new_2.4687.986498821
+DATA/OLTPDR/DATAFILE/account_toll_index.3799.985714921
+DATA/OLTPDR/DATAFILE/users.2524.985777377
+DATA/OLTPDR/DATAFILE/dev_ias_temp.4141.985846937
+DATA/OLTPDR/DATAFILE/dev_stb.4143.985846937
+DATA/OLTPDR/DATAFILE/dev_odi_user.4144.985846937

9 rows selected.

Elapsed: 00:00:00.01
oltp1> exit

Check data files from ASM.

ASMCMD> cd DATA
ASMCMD> ls
OLAPDR/
OLTP/
OLTPDR/
SCHDDBDR/
_MGMTDB/

ASMCMD> cd OLAPDR
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ASMCMD> cd DATAFILE
ASMCMD> pwd
+DATA/OLAPDR/DATAFILE
ASMCMD> exit

Shutdown olapdr.

olap1> show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      oltp
db_unique_name                       string      olapdr

olap1> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

Elapsed: 00:00:00.03
olap1> shut abort;
ORACLE instance shut down.
olap1> exit

Manually remove data files from ASM.

$ asmcmd lsof -G +DATA|grep -ic OLAPDR
0
$ asmcmd ls +DATA/OLAPDR/DATAFILE|wc -l
1665
$ asmcmd lsof -G +DATA/OLAPDR/DATAFILE|wc -l
0
$ asmcmd
ASMCMD> cd datac1
ASMCMD> cd olapdr
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ASMCMD> cd datafile
ASMCMD> pwd
+DATA/olapdr/datafile
ASMCMD> rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y

What would have happened if drop database was executed?
Does anyone know for sure?
Would you have executed drop database?

August 5, 2018

ReCreate ASM Disks RAC

Filed under: 12.2,ASM — mdinh @ 3:01 pm

A long time ago I had written about ReCreate ASM Disks; however this was single instance.

Basically, need to duplicate 11.2.0.4 database to 12.2 RAC. Unfortunately, compatible.rdbms=12.1.0.2.0.

alter diskgroup DATA set attribute ‘compatible.rdbms’ = ‘11.2’ does not work.

You are thinking, “Why not create with 11.2 to start with?”

I am using oravirt (Mikael Sandström) · GitHub to build RAC environment.

This is the best, hands down resource to build sand box and unfortunately for me, have not figured out how to configured all the details.

NOTE: This is a newly created environment and does not contain any database.

15:51:36 SYS @ +ASM1:>select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME                           COMPATIBILITY                                                DATABASE_COMPATIBILITY
------------ ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
           1 CRS                            12.2.0.1.0                                                   11.2.0.0.0
           2 DATA                           12.2.0.1.0                                                   12.1.0.2.0
           3 FRA                            12.2.0.1.0                                                   12.1.0.2.0

15:51:44 SYS @ +ASM1:>alter diskgroup DATA set attribute 'compatible.rdbms' = '11.2';
alter diskgroup DATA set attribute 'compatible.rdbms' = '11.2'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.rdbms
ORA-15244: new compatibility setting less than current [12.1.0.2.0]


15:51:52 SYS @ +ASM1:>

+++ Review DG attributes
[oracle@racnode-dc1-1 sql]$ asmcmd lsattr -l -G DATA
Name                        Value
access_control.enabled      FALSE
access_control.umask        066
appliance._partnering_type  GENERIC
au_size                     4194304
cell.smart_scan_capable     FALSE
cell.sparse_dg              allnonsparse
compatible.advm             12.2.0.1.0
compatible.asm              12.2.0.1.0
compatible.rdbms            12.1.0.2.0
content.check               FALSE
content.type                data
disk_repair_time            3.6h
failgroup_repair_time       24.0h
idp.boundary                auto
idp.type                    dynamic
logical_sector_size         512
phys_meta_replicated        true
preferred_read.enabled      FALSE
scrub_async_limit           1
scrub_metadata.enabled      FALSE
sector_size                 512
thin_provisioned            FALSE

[oracle@racnode-dc1-1 sql]$ asmcmd lsattr -l -G FRA
Name                        Value
access_control.enabled      FALSE
access_control.umask        066
appliance._partnering_type  GENERIC
au_size                     4194304
cell.smart_scan_capable     FALSE
cell.sparse_dg              allnonsparse
compatible.advm             12.2.0.1.0
compatible.asm              12.2.0.1.0
compatible.rdbms            12.1.0.2.0
content.check               FALSE
content.type                data
disk_repair_time            3.6h
failgroup_repair_time       24.0h
idp.boundary                auto
idp.type                    dynamic
logical_sector_size         512
phys_meta_replicated        true
preferred_read.enabled      FALSE
scrub_async_limit           1
scrub_metadata.enabled      FALSE
sector_size                 512
thin_provisioned            FALSE

+++ Review DG Path
[oracle@racnode-dc1-1 sql]$ asmcmd lsdsk -G DATA
Path
ORCL:DATA01

[oracle@racnode-dc1-1 sql]$ asmcmd lsdsk -G FRA
Path
ORCL:FRA01

+++ Remove DG from Cluster.
[oracle@racnode-dc1-1 sql]$ srvctl remove diskgroup -diskgroup DATA -force
[oracle@racnode-dc1-1 sql]$ srvctl remove diskgroup -diskgroup FRA -force

+++ Dismount DG before drop.
15:56:28 SYS @ +ASM1:>drop diskgroup DATA;
drop diskgroup DATA
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15073: diskgroup DATA is mounted by another ASM instance

15:58:07 SYS @ +ASM1:>select inst_id, name, state from gv$asm_diskgroup;


   INST_ID NAME                           STATE
---------- ------------------------------ -----------
         2 CRS                            MOUNTED
         2 DATA                           MOUNTED
         2 FRA                            MOUNTED
         1 CRS                            MOUNTED
         1 DATA                           MOUNTED
         1 FRA                            MOUNTED

6 rows selected.

15:58:37 SYS @ +ASM1:>alter diskgroup DATA dismount;

Diskgroup altered.

15:58:47 SYS @ +ASM1:>alter diskgroup FRA dismount;

Diskgroup altered.

15:59:02 SYS @ +ASM1:>select inst_id, name, state from gv$asm_diskgroup order by 1,2;

   INST_ID NAME                           STATE
---------- ------------------------------ -----------
         1 CRS                            MOUNTED
         1 DATA                           DISMOUNTED
         1 FRA                            DISMOUNTED
         2 CRS                            MOUNTED
         2 DATA                           MOUNTED
         2 FRA                            MOUNTED

6 rows selected.

15:59:10 SYS @ +ASM1:>

+++ Drop DG from 2nd instance.
16:00:42 SYS @ +ASM2:>drop diskgroup DATA including contents;

Diskgroup dropped.

16:01:06 SYS @ +ASM2:>drop diskgroup FRA including contents;

Diskgroup dropped.

16:01:17 SYS @ +ASM2:>select inst_id, name, state from gv$asm_diskgroup order by 1,2;

   INST_ID NAME                           STATE
---------- ------------------------------ -----------
         1 CRS                            MOUNTED
         2 CRS                            MOUNTED

16:01:27 SYS @ +ASM2:>

+++ Review ASM Disks.
[root@racnode-dc1-2 ~]# /etc/init.d/oracleasm listdisks
CRS01
DATA01
FRA01

[root@racnode-dc1-2 ~]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks:               [  OK  ]

+++ Create and mount DG.
16:18:32 SYS @ +ASM2:>create diskgroup FRA external redundancy disk 'ORCL:FRA01' ATTRIBUTE 'compatible.asm'='12.2.0.1.0', 'compatible.rdbms'='11.2.0.0.0', 'au_size'='4194304';

Diskgroup created.

16:19:07 SYS @ +ASM2:>create diskgroup DATA external redundancy disk 'ORCL:DATA01' ATTRIBUTE 'compatible.asm'='12.2.0.1.0', 'compatible.rdbms'='11.2.0.0.0', 'au_size'='4194304';

Diskgroup created.

16:19:31 SYS @ +ASM2:>

+++ Mount DG.
16:20:34 SYS @ +ASM1:>select inst_id, name, state from gv$asm_diskgroup order by 1,2;

   INST_ID NAME                           STATE
---------- ------------------------------ -----------
         1 CRS                            MOUNTED
         1 DATA                           DISMOUNTED
         1 FRA                            DISMOUNTED
         2 CRS                            MOUNTED
         2 DATA                           MOUNTED
         2 FRA                            MOUNTED

6 rows selected.

16:20:37 SYS @ +ASM1:>alter diskgroup DATA mount;

Diskgroup altered.

16:21:01 SYS @ +ASM1:>alter diskgroup FRA mount;

Diskgroup altered.

16:21:10 SYS @ +ASM1:>select inst_id, name, state from gv$asm_diskgroup order by 1,2;

   INST_ID NAME                           STATE
---------- ------------------------------ -----------
         1 CRS                            MOUNTED
         1 DATA                           MOUNTED
         1 FRA                            MOUNTED
         2 CRS                            MOUNTED
         2 DATA                           MOUNTED
         2 FRA                            MOUNTED

6 rows selected.

16:21:42 SYS @ +ASM1:>select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME                           COMPATIBILITY                                                DATABASE_COMPATIBILITY
------------ ------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
           1 CRS                            12.2.0.1.0                                                   11.2.0.0.0
           3 DATA                           12.2.0.1.0                                                   11.2.0.0.0
           2 FRA                            12.2.0.1.0                                                   11.2.0.0.0

16:21:45 SYS @ +ASM1:>

+++ Review ASM
[oracle@racnode-dc1-1 sql]$ srvctl status asm -v
ASM is running on racnode-dc1-1,racnode-dc1-2
Detailed state on node racnode-dc1-1: Started
Detailed state on node racnode-dc1-2: Started

[oracle@racnode-dc1-1 sql]$ asmcmd lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     40952    40592                0           40592              0             Y  CRS/
MOUNTED  EXTERN  N         512             512   4096  4194304      8188     8056                0            8056              0             N  DATA/
MOUNTED  EXTERN  N         512             512   4096  4194304     12284    12152                0           12152              0             N  FRA/
[oracle@racnode-dc1-1 sql]$

July 19, 2018

Playing With Service Relocation 12c

Filed under: 12c,RAC — mdinh @ 2:14 pm
With 12c, use verbose to display services running.
[oracle@racnode-dc1-1 rac_relocate]$ srvctl -V
srvctl version: 12.1.0.2.0

[oracle@racnode-dc1-1 rac_relocate]$ srvctl status database -d hawk -v
Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open.
Instance hawk2 is running on node racnode-dc1-2 with online services p21,p22,p23,p24,p25. Instance status: Open.

[oracle@racnode-dc1-1 rac_relocate]$ srvctl status instance -d hawk -i hawk1 -v
Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open.

[oracle@racnode-dc1-1 rac_relocate]$ srvctl status instance -d hawk -i hawk2 -v
Instance hawk2 is running on node racnode-dc1-2 with online services p21,p22,p23,p24,p25. Instance status: Open.

There is option to provide comma delimited list of services to check the status.
Unfortunately, option is not available for relocation which I failed to understand.
[oracle@racnode-dc1-1 rac_relocate]$ srvctl status service -d hawk -s "p11,p12,p13,p14"
Service p11 is running on instance(s) hawk1
Service p12 is running on instance(s) hawk1
Service p13 is running on instance(s) hawk1
Service p14 is running on instance(s) hawk1

[oracle@racnode-dc1-1 rac_relocate]$ srvctl status service -d hawk -s "p21,p22,p23,p24,p25"
Service p21 is running on instance(s) hawk2
Service p22 is running on instance(s) hawk2
Service p23 is running on instance(s) hawk2
Service p24 is running on instance(s) hawk2
Service p25 is running on instance(s) hawk2

Puzzled that status for services is able to use delimited list where as relocation is not.

I have blogged about new features for service failover: 12.1 Improved Service Failover

Another test shows that it’s working as it should be.

[oracle@racnode-dc1-1 ~]$ srvctl status database -d hawk -v
Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open.
Instance hawk2 is running on node racnode-dc1-2 with online services p21,p22,p23,p24,p25. Instance status: Open.

[oracle@racnode-dc1-1 ~]$ srvctl stop instance -d hawk -instance hawk1 -failover

[oracle@racnode-dc1-1 ~]$ srvctl status database -d hawk -v
Instance hawk1 is not running on node racnode-dc1-1
Instance hawk2 is running on node racnode-dc1-2 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open.
[oracle@racnode-dc1-1 ~]$


[root@racnode-dc1-1 ~]# crsctl stop crs
[root@racnode-dc1-1 ~]# crsctl start crs


[oracle@racnode-dc1-1 ~]$ srvctl status database -d hawk -v
Instance hawk1 is not running on node racnode-dc1-1
Instance hawk2 is running on node racnode-dc1-2 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open.
[oracle@racnode-dc1-1 ~]$

[oracle@racnode-dc1-1 ~]$ srvctl start database -d hawk

[oracle@racnode-dc1-1 ~]$ srvctl status database -d hawk -v
Instance hawk1 is running on node racnode-dc1-1. Instance status: Open.
Instance hawk2 is running on node racnode-dc1-2 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open.
[oracle@racnode-dc1-1 ~]$

However, the requirement is to relocate services versus failover.

Here are scripts and demo for that.

Script will only work for 2-nodes RAC and service is running on 1 instance only.

[oracle@racnode-dc1-1 ~]$ srvctl config service -d hawk |egrep 'Service name|instances'
Service name: p11
Preferred instances: hawk1
Available instances: hawk2
Service name: p12
Preferred instances: hawk1
Available instances: hawk2
Service name: p13
Preferred instances: hawk1
Available instances: hawk2
Service name: p14
Preferred instances: hawk1
Available instances: hawk2
Service name: p21
Preferred instances: hawk2
Available instances: hawk1
Service name: p22
Preferred instances: hawk2
Available instances: hawk1
Service name: p23
Preferred instances: hawk2
Available instances: hawk1
Service name: p24
Preferred instances: hawk2
Available instances: hawk1
Service name: p25
Preferred instances: hawk2
Available instances: hawk1
[oracle@racnode-dc1-1 ~]$

DEMO:

[oracle@racnode-dc1-1 rac_relocate]$ ls *relocate*.sh
relocate_service.sh  validate_relocate_service.sh

[oracle@racnode-dc1-1 rac_relocate]$ ls *restore*.sh
restore_service_instance1.sh  restore_service_instance2.sh
[oracle@racnode-dc1-1 rac_relocate]$


========================================================================
+++++++ SAVE SERVICES LOCATION AND PREVENT ACCIDENTAL OVERWRITE
========================================================================
[oracle@racnode-dc1-1 rac_relocate]$ srvctl status database -d hawk -v > /tmp/service.org

[oracle@racnode-dc1-1 rac_relocate]$ chmod 400 /tmp/service.org; ll /tmp/service.org; cat /tmp/service.org
-r-------- 1 oracle oinstall 222 Jul 18 14:54 /tmp/service.org
Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open.
Instance hawk2 is running on node racnode-dc1-2 with online services p21,p22,p23,p24,p25. Instance status: Open.

[oracle@racnode-dc1-1 rac_relocate]$ srvctl status database -d hawk -v > /tmp/service.org
-bash: /tmp/service.org: Permission denied
[oracle@racnode-dc1-1 rac_relocate]$

	
========================================================================
+++++++ RELOCATE SERVICES FROM INSTANCE 1 TO 2

Validate is similar to RMAN validate.
No relocation is performed and only syntax is provided for verification.
========================================================================
[oracle@racnode-dc1-1 rac_relocate]$ ./validate_relocate_service.sh
./validate_relocate_service.sh: line 4: 1: ---> USAGE: ./validate_relocate_service.sh -db_unique_name -oldinst# -newinst#

[oracle@racnode-dc1-1 rac_relocate]$ ./validate_relocate_service.sh hawk 1 2
+ OUTF=/tmp/service_1.conf
+ srvctl status instance -d hawk -instance hawk1 -v
+ ls -l /tmp/service_1.conf
-rw-r--r-- 1 oracle oinstall 109 Jul 18 14:59 /tmp/service_1.conf
+ cat /tmp/service_1.conf
Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open.
+ set +x

**************************************
***** SERVICES THAT WILL BE RELOCATED:
**************************************
srvctl relocate service -d hawk -service p11 -oldinst hawk1 -newinst hawk2
srvctl relocate service -d hawk -service p12 -oldinst hawk1 -newinst hawk2
srvctl relocate service -d hawk -service p13 -oldinst hawk1 -newinst hawk2
srvctl relocate service -d hawk -service p14 -oldinst hawk1 -newinst hawk2


[oracle@racnode-dc1-1 rac_relocate]$ ./relocate_service.sh hawk 1 2
-rw-r--r-- 1 oracle oinstall 109 Jul 18 15:00 /tmp/service_1.conf
Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open.
+ srvctl relocate service -d hawk -service p11 -oldinst hawk1 -newinst hawk2
+ set +x
+ srvctl relocate service -d hawk -service p12 -oldinst hawk1 -newinst hawk2
+ set +x
+ srvctl relocate service -d hawk -service p13 -oldinst hawk1 -newinst hawk2
+ set +x
+ srvctl relocate service -d hawk -service p14 -oldinst hawk1 -newinst hawk2
+ set +x
+ srvctl status instance -d hawk -instance hawk1 -v
Instance hawk1 is running on node racnode-dc1-1. Instance status: Open.
+ srvctl status instance -d hawk -instance hawk2 -v
Instance hawk2 is running on node racnode-dc1-2 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open.
+ set +x
[oracle@racnode-dc1-1 rac_relocate]$


========================================================================
+++++++ RELOCATE SERVICES FROM INSTANCE 2 TO 1
========================================================================
[oracle@racnode-dc1-1 rac_relocate]$ ./relocate_service.sh hawk 2 1
-rw-r--r-- 1 oracle oinstall 129 Jul 18 15:02 /tmp/service_2.conf
Instance hawk2 is running on node racnode-dc1-2 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open.
+ srvctl relocate service -d hawk -service p11 -oldinst hawk2 -newinst hawk1
+ set +x
+ srvctl relocate service -d hawk -service p12 -oldinst hawk2 -newinst hawk1
+ set +x
+ srvctl relocate service -d hawk -service p13 -oldinst hawk2 -newinst hawk1
+ set +x
+ srvctl relocate service -d hawk -service p14 -oldinst hawk2 -newinst hawk1
+ set +x
+ srvctl relocate service -d hawk -service p21 -oldinst hawk2 -newinst hawk1
+ set +x
+ srvctl relocate service -d hawk -service p22 -oldinst hawk2 -newinst hawk1
+ set +x
+ srvctl relocate service -d hawk -service p23 -oldinst hawk2 -newinst hawk1
+ set +x
+ srvctl relocate service -d hawk -service p24 -oldinst hawk2 -newinst hawk1
+ set +x
+ srvctl relocate service -d hawk -service p25 -oldinst hawk2 -newinst hawk1
+ set +x
+ srvctl status instance -d hawk -instance hawk2 -v
Instance hawk2 is running on node racnode-dc1-2. Instance status: Open.
+ srvctl status instance -d hawk -instance hawk1 -v
Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open.
+ set +x
[oracle@racnode-dc1-1 rac_relocate]$


========================================================================
+++++++ RESTORE SERVICES FOR INSTANCE
========================================================================
[oracle@racnode-dc1-1 rac_relocate]$ srvctl status database -d hawk -v
Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14,p21,p22,p23,p24,p25. Instance status: Open.
Instance hawk2 is running on node racnode-dc1-2. Instance status: Open.

[oracle@racnode-dc1-1 rac_relocate]$ ./restore_service_instance2.sh
./restore_service_instance2.sh: line 4: 1: ---> USAGE: ./restore_service_instance2.sh -db_unique_name

[oracle@racnode-dc1-1 rac_relocate]$ ./restore_service_instance2.sh hawk
+ srvctl relocate service -d hawk -service p21 -oldinst hawk1 -newinst hawk2
+ set +x
+ srvctl relocate service -d hawk -service p22 -oldinst hawk1 -newinst hawk2
+ set +x
+ srvctl relocate service -d hawk -service p23 -oldinst hawk1 -newinst hawk2
+ set +x
+ srvctl relocate service -d hawk -service p24 -oldinst hawk1 -newinst hawk2
+ set +x
+ srvctl relocate service -d hawk -service p25 -oldinst hawk1 -newinst hawk2
+ set +x

[oracle@racnode-dc1-1 rac_relocate]$ srvctl status database -d hawk -v
Instance hawk1 is running on node racnode-dc1-1 with online services p11,p12,p13,p14. Instance status: Open.
Instance hawk2 is running on node racnode-dc1-2 with online services p21,p22,p23,p24,p25. Instance status: Open.
[oracle@racnode-dc1-1 rac_relocate]$

CODE:


========================================================================
+++++++ validate_relocate_service.sh
========================================================================
#!/bin/sh -e
DN=`dirname $0`
BN=`basename $0`
DB=${1:?"---> USAGE: $DN/$BN -db_unique_name -oldinst# -newinst#"}
OLD=${2:?"---> USAGE: $DN/$BN -db_unique_name -oldinst# -newinst#"}
NEW=${3:?"---> USAGE: $DN/$BN -db_unique_name -oldinst# -newinst#"}
set -x
OUTF=/tmp/service_${OLD}.conf
srvctl status instance -d ${DB} -instance ${DB}${OLD} -v > $OUTF
ls -l $OUTF;cat $OUTF
set +x
export svc=`tail -1 $OUTF | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
IFS=","
echo
echo "**************************************"
echo "***** SERVICES THAT WILL BE RELOCATED:"
echo "**************************************"
for s in ${svc}
do
echo "srvctl relocate service -d ${DB} -service ${s} -oldinst ${DB}${OLD} -newinst ${DB}${NEW}"
done
exit

========================================================================
+++++++ relocate_service.sh
========================================================================
#!/bin/sh -e
DN=`dirname $0`
BN=`basename $0`
DB=${1:?"---> USAGE: $DN/$BN -db_unique_name -oldinst# -newinst#"}
OLD=${2:?"---> USAGE: $DN/$BN -db_unique_name -oldinst# -newinst#"}
NEW=${3:?"---> USAGE: $DN/$BN -db_unique_name -oldinst# -newinst#"}
OUTF=/tmp/service_${OLD}.conf
srvctl status instance -d ${DB} -instance ${DB}${OLD} -v > $OUTF
ls -l $OUTF;cat $OUTF
export svc=`tail -1 $OUTF | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
IFS=","
for s in ${svc}
do
set -x
srvctl relocate service -d ${DB} -service ${s} -oldinst ${DB}${OLD} -newinst ${DB}${NEW}
set +x
done
set -x
srvctl status instance -d ${DB} -instance ${DB}${OLD} -v
srvctl status instance -d ${DB} -instance ${DB}${NEW} -v
set +x
exit

========================================================================
+++++++ restore_service_instance1.sh
========================================================================
#!/bin/sh -e
DN=`dirname $0`
BN=`basename $0`
DB=${1:?"---> USAGE: $DN/$BN -db_unique_name"}
export svc=`head -1 /tmp/service.org | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
IFS=","
for s in ${svc}
do
set -x
srvctl relocate service -d ${DB} -service ${s} -oldinst ${DB}2 -newinst ${DB}1
set +x
done
exit

========================================================================
+++++++ restore_service_instance2.sh
========================================================================
#!/bin/sh -e
DN=`dirname $0`
BN=`basename $0`
DB=${1:?"---> USAGE: $DN/$BN -db_unique_name"}
export svc=`tail -1 /tmp/service.org | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
IFS=","
for s in ${svc}
do
set -x
srvctl relocate service -d ${DB} -service ${s} -oldinst ${DB}1 -newinst ${DB}2
set +x
done
exit

July 14, 2018

How To Delete Integrated Extract Replicat

Filed under: 12c,GoldenGate — mdinh @ 12:46 am

Why is this important?

If processes are not unregistered from database, they are orphaned.

Hence:
unregister replicat $replicat_name DATABASE
unregister extract $extract_name DATABASE

$ ./ggsci 

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     E_DB        00:00:05      00:00:00    
EXTRACT     RUNNING     P_DB        00:00:00      00:00:00    
REPLICAT    RUNNING     R_OWL       00:00:00      00:00:03    
REPLICAT    STOPPED     R_JAY       00:00:00      00:24:15   

GGSCI 2> info E_DB debug

EXTRACT    E_DB      Last Started 2018-06-18 08:48   Status RUNNING
Checkpoint Lag       00:00:05 (updated 00:00:00 ago)
Process ID           365696
Log Read Checkpoint  Oracle Integrated Redo Logs
                     2018-07-13 20:20:45  Seqno 151864, RBA 31183128
                     SCN 188.1068581841 (808522433489)


GGSCI 3> info r_jay debug

REPLICAT   R_JAY     Last Started 2018-06-16 11:42   Status STOPPED
INTEGRATED
Checkpoint Lag       00:00:00 (updated 578:24:33 ago)
Log Read Checkpoint  File ./dirdat/
                     2018-06-19 17:55:08.604509  RBA 409140739


GGSCI 4> info r_owl debug

REPLICAT   R_OWL     Last Started 2018-06-16 11:42   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:03 ago)
Process ID           284878
Log Read Checkpoint  File ./dirdat/
                     2018-07-13 20:21:09.000628  RBA 132791008

GGSCI 5> exit


SQL> @pr "select * from DBA_APPLY"
APPLY_NAME                    : OGG$R_JAY
QUEUE_NAME                    : OGGQ$R_JAY
APPLY_CAPTURED                : YES
APPLY_USER                    : GGSUSER13
APPLY_TAG                     : 00
STATUS                        : DISABLED
MAX_APPLIED_MESSAGE_NUMBER    : 0
STATUS_CHANGE_TIME            : 19-jun-2018 17:56:28
MESSAGE_DELIVERY_MODE         : CAPTURED
PURPOSE                       : GoldenGate Apply
LCRID_VERSION                 : 2
-------------------------
APPLY_NAME                    : OGG$R_MIG - ORPHANED REPLICAT AS PROCESS DOES NOT EXISTS
QUEUE_NAME                    : OGGQ$R_MIG
QUEUE_OWNER                   : GGSUSER12
APPLY_CAPTURED                : YES
APPLY_USER                    : GGSUSER12
APPLY_TAG                     : 00
STATUS                        : DISABLED
MAX_APPLIED_MESSAGE_NUMBER    : 0
STATUS_CHANGE_TIME            : 24-feb-2018 07:12:24
MESSAGE_DELIVERY_MODE         : CAPTURED
PURPOSE                       : GoldenGate Apply
LCRID_VERSION                 : 2
-------------------------
APPLY_NAME                    : OGG$E_DB
QUEUE_NAME                    : OGG$Q_E_DB
QUEUE_OWNER                   : GGSUSER13
APPLY_CAPTURED                : YES
RULE_SET_NAME                 : RULESET$_9
APPLY_TAG                     : 00
STATUS                        : ENABLED
MAX_APPLIED_MESSAGE_NUMBER    : 0
STATUS_CHANGE_TIME            : 18-jun-2018 08:48:02
MESSAGE_DELIVERY_MODE         : CAPTURED
PURPOSE                       : GoldenGate Capture
LCRID_VERSION                 : 2
-------------------------

PL/SQL procedure successfully completed.

SQL> @pr "select * from DBA_CAPTURE"
CAPTURE_NAME                  : OGG$CAP_E_DB
QUEUE_NAME                    : OGG$Q_E_DB
START_SCN                     : 776572270090
STATUS                        : ENABLED
CAPTURED_SCN                  : 808484653323
APPLIED_SCN                   : 808484649784
USE_DATABASE_LINK             : NO
FIRST_SCN                     : 776572270090
SOURCE_DATABASE               : DB01
SOURCE_DBID                   : 689358028
SOURCE_RESETLOGS_SCN          : 279476595350
SOURCE_RESETLOGS_TIME         : 826720979
LOGMINER_ID                   : 1
MAX_CHECKPOINT_SCN            : 808510343793
REQUIRED_CHECKPOINT_SCN       : 808484621637
LOGFILE_ASSIGNMENT            : IMPLICIT
STATUS_CHANGE_TIME            : 18-jun-2018 08:48:04
VERSION                       : 12.2.0.1.0
CAPTURE_TYPE                  : LOCAL
CHECKPOINT_RETENTION_TIME     : 7
PURPOSE                       : GoldenGate Capture
SOURCE_ROOT_NAME              : DB01
CLIENT_NAME                   : E_DB
CLIENT_STATUS                 : ATTACHED
OLDEST_SCN                    : 776572270090
FILTERED_SCN                  : 773378341423
-------------------------

PL/SQL procedure successfully completed.

May 5, 2018

DBFS Nightmare

Filed under: 12c,GoldenGate — mdinh @ 4:19 pm
====================================================================================================
How to cleanup DBFS tablespace after removing files at DBFS filesystem (Doc ID 2331565.1)	
====================================================================================================
High level overview of the DBMS_DBFS_SFS.REORGANIZEFS procedure:
----------------------------------------------------------------------------------------------------
1) Create a NEW tablespace
2) Create a temporary filesystem with dbms_dbfs_sfs.createFilesystem in the new tablespace.
3) Run dbms_dbfs_sfs.reorganizeFS -->>
   EXEC DBMS_DBFS_SFS.REORGANIZEFS(SRCSTORE=>'FS_FS1', DSTSTORE=>'FS_TMP_FS');
4) The dbfs data is now in the smaller NEW tablespace.
5) Drop the temporay filesystem with dbms_dbfs_sfs.dropFilesystem
6) The OLD original tablespace is empty now.
----------------------------------------------------------------------------------------------------
To reuse the same tablespace again please follow the below steps
----------------------------------------------------------------------------------------------------
7) Create a temporary filesystem with dbms_dbfs_sfs.createFilesystem in the OLD ORIGINAL tablespace.
8) Run dbms_dbfs_sfs.reorganizeFS
9) The dbfs data is now in the smaller the ORIGINAL tablespace.
10) Drop the temporay filesystem with dbms_dbfs_sfs.dropFilesystem
11) The NEW small tablespace is empty now and can be dropped.

====================================================================================================
DBFS tablespace keep growing not using expired blocks (Doc ID 2327299.1)	
====================================================================================================
----------------------------------------------------------------------------------------------------
First perform the DBFS re-org to cleanup the tablespace after removing the files at file system level by following below document
----------------------------------------------------------------------------------------------------
How to cleanup DBFS tablespace after removing files at DBFS filesystem (Doc ID 2331565.1)

----------------------------------------------------------------------------------------------------
After the re-org, set the  below parameter to reuse the expired blocks
----------------------------------------------------------------------------------------------------
ALTER SYSTEM SET "_ENABLE_SPACE_PREALLOCATION" = 0;

====================================================================================================
ORA-1654 - DBFS FREE SPACE NOT RECLAIMED AFTER CLEARING FILES (Doc ID 1948305.1)	
====================================================================================================
----------------------------------------------------------------------------------------------------
a) Check Lob retention setting:
----------------------------------------------------------------------------------------------------
  ex. SQL> select 
  table_name,retention_type,retention_value,retention,securefile from dba_lobs 
  where table_name = 'T_FS1';

----------------------------------------------------------------------------------------------------  
b) Change LOB retention to none.
----------------------------------------------------------------------------------------------------
  ex. SQL> alter table DBFS_USER.T_FS1 modify lob (FILEDATA) (retention none);

After countless discussions, team found GOLD.

12.1 Shrinking and Reorganizing DBFS Filesystems

STEPS:

sqlplus /as sysdba
create bigfile tablespace DBFS_NEW datafile size 8G autoextend on next 1G maxsize 70G;
grant dba to dbfs_user;
alter user dbfs_user default role all;
exit

+++ REORGANIZE DBFS FILESYSTEM FS1 IN TABLESPACE DBFS_TS INTO A NEW TABLESPACE DBFS_NEW, 
+++ USING A TEMPORARY FILESYSTEM NAMED TMP_FS, WHERE ALL FILESYSTEMS BELONG TO DATABASE USER DBFS_USER
cd $ORACLE_HOME/rdbms/admin
sqlplus dbfs_user
@dbfs_create_filesystem DBFS_NEW TMP_FS
exec dbms_dbfs_sfs.reorganizefs('FS1','TMP_FS');
@dbfs_drop_filesystem TMP_FS
purge user_recyclebin;
exit

sqlplus / aa sysdba 
revoke dba from dbfs_user;
select count(*) from dba_extents where tablespace_name='DBFS_TS'; 
select count(*) from dba_extents where tablespace_name='DBFS_NEW'; 
-- DROP TABLESPACE HAVING ZERO EXTENTS
-- BE CAREFUL IN CASE USER AND DBFS ARE USING SAME TABLESPACE
Example:
create user dbfs_user identified by **** default tablespace dbfs_ts quota unlimited on dbfs_ts;
@$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem.sql dbfs_ts FS1

TRUE CONFESSION:

I created an imperfect plan at first that might have saved me from a major catastrophe.

Create DBFS_TMP in a separate Disk Group since I was concern existing may not have enough storage.
Reorg from DBFS_TS to DBFS_TMP (bad idea to name tablespace as DBFS_TMP as LOB Segments migrated here).
Reorg from DBFS_TMP back to DBFS_TS.
Drop tablespace DBFS_TMP.

There’s 29G difference between old and new. How much if this space will be reclaimable due to HWM in data file?

Performing reorg twice is really not an option in prod.

RESULTS:

SEGMENT_NAME                   TABLESPACE_NAME                        MB    EXTENTS
------------------------------ ------------------------------ ---------- ----------
LOB_SFS$_FST_1                 DBFS_TS                        30647.1875       1851
LOB_SFS$_FST_6225924           DBFS_NEW                         1025.125        130

WARNINGS:

This has only been tested in a vacuum (non-prod env w very low activities) – YMMV.

Next Page »

Blog at WordPress.com.