Thinking Out Loud

May 19, 2017

NOLOGGING Bad For Recovery

Filed under: 12c,oracle — mdinh @ 3:41 am

What doesn’t kill you makes you stronger.
I have gone through NOLOGGING hell and not sure if I am out yet.
Disk was lost from server and database was recovered.

Oracle Standard Edition (SE2) for Microsoft Windows x64 (64-bit).

RMAN> restore database validate preview;

  List of Archived Logs in backup set 4098
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    15209   134553557  26-APR-17 134574622  26-APR-17
  1    15218   134681063  26-APR-17 134690751  27-APR-17
RMAN-05119: recovery can not be done to a consistent state.
Media recovery start SCN is 133224369
Recovery must be done beyond SCN 133235710 to clear datafile fuzziness

RMAN> spool log to recover.log
RMAN> recover database until sequence 15209;
RMAN> alter database open resetlogs;
RMAN> exit 

Bug 20315311 – RMAN-5119: recovery can not be done to a consistent state (Doc ID 20315311.8)
The content was last updated on: 17-FEB-2017

RMAN gives the following notification in the 'restore database preview' output even though a successful backup has been performed:
  RMAN-05119: recovery can not be done to a consistent state
 
Workaround
 None

SQL> select * from mytable;

select * from mytable
                      *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 915)
ORA-26040: Data block was loaded using the NOLOGGING option

RMAN> validate tablespace USERS;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     334            263346       848640          134981624

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     295            1225517      3932160         134981561

Finished validate at 18-MAY-17

RMAN> list failure;

Database Role: PRIMARY

no failures found that match specification

RMAN> advise failure;

Database Role: PRIMARY

no failures found that match specification

RMAN> exit

RMAN> backup validate check logical database;

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     334            263346       848640          134982083
  File Name: 

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8    OK     295            1225517      3932160         134982061
  File Name: 

Finished validate at 18-MAY-17  

Basically, NOLOGGING operation occurred from backup before resetlogs recovery
and there is no datafile/database backup following NOLOGGING operations
since database backup is performed once a day.

The Gains and Pains of Nologging Operations (Doc ID 290161.1)

However, NOLOGGING is intended for configurations in which media recovery 
or the recovery of the corresponding object is not important. 
Thus, if the disk or tape or storage media fails, 
you will not be able to recover your changes from the redo because the changes were never logged.  
SQL> select max(NONLOGGED_END_CHANGE#), max(NONLOGGED_END_TIME), max(RESETLOGS_CHANGE#), max(RESETLOGS_TIME) from v$nonlogged_block;
MAX(NONLOGGED_END_CHANGE#) MAX(NONLOGGED_END_TI MAX(RESETLOGS_CHANGE#) MAX(RESETLOGS_TIME)
-------------------------- -------------------- ---------------------- --------------------
                 134534115

SQL> select count(*) from v$nonlogged_block;
  COUNT(*)
----------
        64

-- NOTICE: this matched what RMAN reported.
SQL> select file#, count(*), sum(blocks) from v$nonlogged_block group by file#;
     FILE#   COUNT(*) SUM(BLOCKS)
---------- ---------- -----------
         5         54         334
         8         10         295

SQL> select resetlogs_change#, resetlogs_time, current_scn from v$database;
RESETLOGS_CHANGE# RESETLOGS_TIME       CURRENT_SCN
----------------- -------------------- -----------
        134553558 15-MAY-2017 18:28:53   135266743

SQL> select file#, block#, NONLOGGED_START_CHANGE# from v$nonlogged_block
  2  where NONLOGGED_START_CHANGE# > (select RESETLOGS_CHANGE# from v$database)
  3  ;

no rows selected

SQL>

Truncating table and reloading data resolve SELECT issues but block corruption (Free Block) still exists.
You can read more about Playing with Oracle Free Block Corruption

SQL> truncate table mytable;

Table truncated.

SQL> select * from mytable;

no rows selected

SQL> r
  1  SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2  , greatest(e.block_id, c.block#) corr_start_block#
  3  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5  - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6  , null description
  7  FROM dba_extents e, v$nonlogged_block c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1 10 AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 13  , greatest(f.block_id, c.block#) corr_start_block#
 14  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 15  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 16  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 17  , 'Free Block' description
 18  FROM dba_free_space f, v$nonlogged_block c
 19  WHERE f.file_id = c.file#
 20  AND f.block_id <= c.block# + c.blocks - 1 21 AND f.block_id + f.blocks - 1 >= c.block#
 22  order by file#, corr_start_block#
 23*

                                                                                      START     END
OWNER      SEGMENT_TYPE         SEGMENT_NAME         PARTITION_NAME        FILE#     BLOCK#  BLOCK# BLOCKS_CORRUPTED DESCRIPTION
---------- -------------------- -------------------- -------------------- ------ ---------- ------- ---------------- ------------------------------
SCHEMA    TABLE                ########                                       5        219     219                1
SCHEMA    TABLE                ########                                       5        227     227                1
SCHEMA    TABLE                ########                                       5        235     235                1
SCHEMA    TABLE                ##################                             5        251     251                1
SCHEMA    TABLE                ##########                                     5        275     275                1
SCHEMA    TABLE                ############                                   5        283     283                1
SCHEMA    TABLE                #############                                  5        459     459                1
SCHEMA    TABLE                ##########                                     5        467     467                1
SCHEMA    TABLE                ################                               5        491     491                1
SCHEMA    TABLE                #############                                  5        499     499                1
SCHEMA    TABLE                ##################                             5        851     851                1
SCHEMA    TABLE                ########                                       5        875     875                1
SCHEMA    TABLE                #############                                  5        883     883                1
SCHEMA    TABLE                ################                               5        891     891                1
SCHEMA    TABLE                ##################                             5        899     903                5
SCHEMA    TABLE                ################                               5        907     907                1
SCHEMA    TABLE                ############                                   5        915     919                5
SCHEMA    TABLE                ############                                   5       1091    1091                1
SCHEMA    TABLE                ##########                                     5       1107    1107                1
SCHEMA    TABLE                ##############                                 5       1323    1327                5
SCHEMA    TABLE                ############                                   5       1331    1331                1
SCHEMA    TABLE                ##############                                 5       1347    1351                5
SCHEMA    TABLE                ############                                   5       1355    1355                1
SCHEMA    TABLE                ###########                                    5       1643    1643                1
SCHEMA    TABLE                ##########                                     5       1651    1651                1
SCHEMA    TABLE                ###########                                    5       2099    2103                5
SCHEMA    TABLE                ###############                                5       2531    2531                1
SCHEMA    TABLE                ##############                                 5       2539    2543                5
SCHEMA    TABLE                ##############                                 5       2547    2547                1
SCHEMA    TABLE                ###############                                5       2707    2707                1
SCHEMA    TABLE                ###########                                    5       2715    2715                1
SCHEMA    TABLE                ###############                                5       2875    2875                1
SCHEMA    TABLE                ##############                                 5       4211    4215                5
                                                                               5       4216    4218                3 Free Block
SCHEMA    TABLE                ###########                                    5       4611    4611                1
SCHEMA    TABLE                ########                                       5       4627    4627                1
                                                                               5       4872    4877                6 Free Block
                                                                               5       4880    4886                7 Free Block
                                                                               5       5064    5071                8 Free Block
                                                                               5       5073    5087               15 Free Block
                                                                               5       5089    5103               15 Free Block
                                                                               5       5105    5119               15 Free Block
                                                                               5       5121    5135               15 Free Block
                                                                               5       5137    5159               23 Free Block
                                                                               5       5161    5175               15 Free Block
                                                                               5       5177    5191               15 Free Block
                                                                               5       5193    5207               15 Free Block
                                                                               5       5209    5223               15 Free Block
                                                                               5       5225    5239               15 Free Block
                                                                               5       5241    5255               15 Free Block
                                                                               5       5257    5263                7 Free Block
                                                                               5       5328    5335                8 Free Block
                                                                               5       5337    5343                7 Free Block
                                                                               5     229506  229550               45 Free Block
                                                                               5     608697  608703                7 Free Block
                                                                               8    1120824 #######                5 Free Block
                                                                               8    1120872 #######                8 Free Block
                                                                               8    1274888 #######                8 Free Block
                                                                               8    1536681 #######               14 Free Block
                                                                               8    1536697 #######               11 Free Block
                                                                               8    1536713 #######                6 Free Block
                                                                               8    1791056 #######                8 Free Block
                                                                               8    2804571 #######               37 Free Block
                                                                               8    3213819 #######                5 Free Block
                                                                               8    3854212 #######              193 Free Block

65 rows selected.

Elapsed: 00:09:22.13
SQL>

How to identify all the Corrupted Objects in the Database with RMAN (Doc ID 472231.1)
In 12c the NOLOGGING blocks identified by rman validate are in new view v$nonlogged_block

set lines 200 pages 1000 tab off trimsp on timing on
col owner for a10
col segment_type for a20
col segment_name for a20
col partition_name for a20
col partition_name for a20
col description for a30
col file# for 99999
col corr_start_block# heading 'CORRUPT|START|BLOCK#' for 999999999
col corr_end_block# heading 'CORRUPT|END|BLOCK#' for 999999999
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$nonlogged_block c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$nonlogged_block c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#
;

Advertisements

May 17, 2017

Windows Oracle Services Using PowerShell

Filed under: oracle,Windows — mdinh @ 10:39 pm

Lately, I have been getting feet wet with Windows.
I know GUI can be used but not very good to reproduce.

Here is now to find Stopped Oracle Windows Services and start them.

And if you want to use GUI, run Services.msc from command line.

Windows PowerShell
Copyright (C) 2012 Microsoft Corporation. All rights reserved.

PS C:\Users\oracle> hostname
minions

PS C:\Users\oracle> Get-Service -Name *oracle* | Where Status -eq "Stopped" | Format-List
Name                : Oracleagent12c1Agent
DisplayName         : Oracleagent12c1Agent
Status              : Stopped
DependentServices   : {}
ServicesDependedOn  : {}
CanPauseAndContinue : False
CanShutdown         : False
CanStop             : False
ServiceType         : Win32OwnProcess

Name                : OracleOraHome1ClrAgent
DisplayName         : OracleOraHome1ClrAgent
Status              : Stopped
DependentServices   : {}
ServicesDependedOn  : {}
CanPauseAndContinue : False
CanShutdown         : False
CanStop             : False
ServiceType         : Win32OwnProcess

Name                : OracleRemExecServiceV2
DisplayName         : OracleRemExecServiceV2
Status              : Stopped
DependentServices   : {}
ServicesDependedOn  : {}
CanPauseAndContinue : False
CanShutdown         : False
CanStop             : False
ServiceType         : Win32OwnProcess

PS C:\Users\oracle> Start-Service -name Oracleagent12c1Agent
WARNING: Waiting for service 'Oracleagent12c1Agent (Oracleagent12c1Agent)' to start...
WARNING: Waiting for service 'Oracleagent12c1Agent (Oracleagent12c1Agent)' to start...
WARNING: Waiting for service 'Oracleagent12c1Agent (Oracleagent12c1Agent)' to start...
WARNING: Waiting for service 'Oracleagent12c1Agent (Oracleagent12c1Agent)' to start...

PS C:\Users\oracle> Get-Service -name Oracleagent12c1Agent
Status   Name               DisplayName
------   ----               -----------
Start... Oracleagent12c1... Oracleagent12c1Agent

PS C:\Users\oracle> Get-Service -name Oracleagent12c1Agent
Status   Name               DisplayName
------   ----               -----------
Running  Oracleagent12c1... Oracleagent12c1Agent

PS C:\Users\oracle> Get-Service -Name *oracle* | Where Status -eq "Stopped" | Format-List
Name                : OracleOraHome1ClrAgent
DisplayName         : OracleOraHome1ClrAgent
Status              : Stopped
DependentServices   : {}
ServicesDependedOn  : {}
CanPauseAndContinue : False
CanShutdown         : False
CanStop             : False
ServiceType         : Win32OwnProcess

Name                : OracleRemExecServiceV2
DisplayName         : OracleRemExecServiceV2
Status              : Stopped
DependentServices   : {}
ServicesDependedOn  : {}
CanPauseAndContinue : False
CanShutdown         : False
CanStop             : False
ServiceType         : Win32OwnProcess

PS C:\Users\oracle> C:\app\oracle\product\agent12c\core\12.1.0.3.0\bin\emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
Agent Version     : 12.1.0.3.0
OMS Version       : 12.1.0.3.0
Protocol Version  : 12.1.0.1.0
Agent Home        : C:/app/oracle/product/agent12c/agent/core/12.1.0.3.0
Agent Binaries    : c:\app\oracle\product\agent12c\core\12.1.0.3.0
Agent Process ID  : 538684
Parent Process ID : 536640
Agent URL         : https://minions.local:1830/emd/main/
Repository URL    : https://cloud.local:4903/empbs/upload
Started at        : 2017-05-17 13:30:23
Started by user   : minions$
Last Reload       : (none)
Last successful upload                       : 2017-05-17 13:31:00
Last attempted upload                        : 2017-05-17 13:31:00
Total Megabytes of XML files uploaded so far : 0.03
Number of XML files pending upload           : 1
Size of XML files pending upload(MB)         : 0
Available disk space on upload filesystem    : 68.70%
Collection Status                            : Collections enabled
Heartbeat Status                             : Ok
Last attempted heartbeat to OMS              : 2017-05-17 13:30:41
Last successful heartbeat to OMS             : 2017-05-17 13:30:41
Next scheduled heartbeat to OMS              : 2017-05-17 13:31:41

---------------------------------------------------------------
Agent is Running and Ready

PS C:\Users\oracle> Services.msc

May 15, 2017

DBFS and XAG for Goldengate P5

Filed under: 12c,GoldenGate — mdinh @ 11:49 pm
$ $GRID_HOME/crs/script/mount-dbfs.sh version
20160215

$ grep ^VERSION $GRID_HOME/crs/script/mount-dbfs.sh 
VERSION=20160215

The following is an option for customers that have removed the dos2unix tool:
1. vi /tmp/mount-dbfs.sh
2. :set ff=unix
3. :wq
4. repeat for /tmp/mount-dbfs.conf

May 14, 2017

DBFS and XAG for Goldengate P4

Filed under: 12c,GoldenGate — mdinh @ 3:13 pm

I encountered a situation where I was not able to start dbfs_mount to save my life.

2 people spent over 5 hours and was not able to resolve the issue.

1 person suggested to to restart clusterware because it looks like fuse group was added to oracle after oracle clusterware was up.

During configuration:
kernel-devel was missing, /etc/rc.modules did not exists, oracle was not part of fuse group

# rpm -qa |egrep 'fuse|kernel-devel'
# cat /etc/rc.modules
cat: /etc/rc.modules: No such file or directory
# grep fuse /etc/group
fuse:x:993:

Corrections made:

# cat /etc/rc.modules
/sbin/modprobe fuse

# grep fuse /etc/group
fuse:x:993:oracle
$ crsctl start res dbfs_mount
CRS-2672: Attempting to start 'dbfs_mount' on 'hawk1'
CRS-2672: Attempting to start 'dbfs_mount' on 'hawk2'
CRS-2674: Start of 'dbfs_mount' on 'hawk2' failed
CRS-2679: Attempting to clean 'dbfs_mount' on 'hawk2'
CRS-2674: Start of 'dbfs_mount' on 'hawk1' failed
CRS-2679: Attempting to clean 'dbfs_mount' on 'hawk1'
CRS-2681: Clean of 'dbfs_mount' on 'hawk1' succeeded
CRS-2681: Clean of 'dbfs_mount' on 'hawk2' succeeded
CRS-4000: Command Start failed, or completed with errors.

$ crsctl stat res dbfs_mount
NAME=dbfs_mount
TYPE=local_resource
TARGET=ONLINE , ONLINE
STATE=OFFLINE, OFFLINE

After restarting clusterware:

Note: this was started by root which I don't think is a good idea.
# crsctl start resource dbfs_mount
CRS-2672: Attempting to start 'dbfs_mount' on 'hawk1'
CRS-2676: Start of 'dbfs_mount' on 'hawk1' succeeded

Should have been started from grid user
$ crsctl start res dbfs_mount
CRS-2672: Attempting to start 'dbfs_mount' on 'hawk2'
CRS-2672: Attempting to start 'dbfs_mount' on 'hawk1'
CRS-2676: Start of 'dbfs_mount' on 'hawk1' succeeded
CRS-2676: Start of 'dbfs_mount' on 'hawk2' succeeded

$ crsctl stat res dbfs_mount
NAME=dbfs_mount
TYPE=local_resource
TARGET=ONLINE             , ONLINE
STATE=ONLINE on hawk1, ONLINE on hawk2

It really bothered me how this information was missed and where was it documented.

After hours of research, the information is not consistently documented.
Not Found:
White Paper: How To Setup 12c DBFS FileSystem. (Doc ID 1938421.1)

Found:
Configuring DBFS on Oracle Exadata Database Machine (Doc ID 1054431.1)

To pick up the additional group (fuse) membership for the oracle user on Linux or the workaround above on Solaris, Clusterware must be restarted.
For example, to restart Clusterware on all nodes at the same time (non-rolling), you can use the following commands as root:

Hopefully, you won’t have a much fun as I did.

May 9, 2017

DBFS and XAG for Goldengate P3

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

Start Pump Extract at source failed as shown below.

INFO OGG-00993 Oracle GoldenGate Capture for Oracle, p_test.prm: EXTRACT P_TEST started.
ERROR OGG-01224 Oracle GoldenGate Capture for Oracle, p_test.prm: TCP/IP error 111 (Connection refused), endpoint: ggvip_hawk.local:7809.
ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, p_test.prm: PROCESS ABENDING.

Hypothesis is ports are not opened from source to target and let’s verify configuration.

VIP name and address from DNS.

Name:    ggvip_hawk.local
Address: 10.10.10.201

Recall this is how goldengate instance was created.

# $GRID_HOME/bin/agctl add goldengate gg_xx \
--instance_type target \
--oracle_home /u01/app/oracle/product/12.1.0/db_1 \
--nodes hawk1,hawk2 \
--network 1 --ip 10.10.10.201 \
--user ggsuser --group dba \
--filesystems dbfs_mount \
--gg_home /u03/gg/12.2.0 

From Goldengate instance gg_xx,
Application VIP (gg_xx-vip) is created
using address (10.10.10.201).

Check for xag resource.

ggsuser@hawk1 ~ $ $GRID_HOME/bin/crsctl stat res -t|grep -A2 xag
xag.gg_xx-vip.vip
      1        ONLINE  ONLINE       hawk1                STABLE
xag.gg_xx.goldengate
      1        ONLINE  ONLINE       hawk1                STABLE

Verify Application VIP address assigned.

ggsuser@hawk1 /u03/app/gg/12.2.0 
$ $GRID_HOME/bin/crsctl stat res xag.gg_xx-vip.vip -p|grep USR_ORA_VIP
GEN_USR_ORA_VIP=
USR_ORA_VIP=10.10.10.201
ggsuser@hawk1 /u03/app/gg/12.2.0 
$ nslookup 10.10.10.201
Server:		10.80.107.101
Address:	10.80.107.101#53

name = ggvip_hawk.local
ggsuser@hawk1 /u03/app/gg/12.2.0 $ 
$GRID_HOME/bin/crsctl stat res xag.gg_xx-vip.vip -p
NAME=xag.gg_xx-vip.vip
TYPE=app.appvipx.type
ACL=owner:root:rwx,pgrp:root:r-x,other::r--,group:dba:r-x,user:ggsuser:r-x
ACTIONS=
ACTION_SCRIPT=
ACTION_TIMEOUT=60
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/orarootagent%CRS_EXE_SUFFIX%
APPSVIP_FAILBACK=0
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=1
CHECK_TIMEOUT=0
CLEAN_TIMEOUT=60
DEGREE=1
DELETE_TIMEOUT=60
DESCRIPTION=Application VIP
ENABLED=1
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
GEN_USR_ORA_STATIC_VIP=
GEN_USR_ORA_VIP=
HOSTING_MEMBERS=
INSTANCE_FAILOVER=1
INTERMEDIATE_TIMEOUT=0
LOAD=1
LOGGING_LEVEL=1
MODIFY_TIMEOUT=60
NLS_LANG=
OFFLINE_CHECK_INTERVAL=0
PLACEMENT=balanced
RELOCATE_BY_DEPENDENCY=1
RESTART_ATTEMPTS=0
SCRIPT_TIMEOUT=60
SERVER_CATEGORY=
SERVER_POOLS=*
START_CONCURRENCY=0
START_DEPENDENCIES=hard(ora.net1.network) pullup(ora.net1.network)
START_TIMEOUT=0
STOP_CONCURRENCY=0
STOP_DEPENDENCIES=hard(ora.net1.network)
STOP_TIMEOUT=0
TYPE_VERSION=1.1
UPTIME_THRESHOLD=7d
USER_WORKLOAD=no
USE_STICKINESS=0
USR_ORA_ENV=
USR_ORA_VIP=10.10.10.201
VERSION=12.1.0.1.0

May 7, 2017

free.sql

Filed under: oracle — mdinh @ 11:02 am

Provide info if tablespace is BIGFILE and existing increment by.
What I did might and might not be for the better – alter tablespace TBSNAME_XXXX autoextend on next 1g maxsize 250g;

sqlplus / as sysdba @free.sql TBSNAME_XXXX

SQL*Plus: Release 11.2.0.4.0 Production on Sun May 7 05:47:54 2017

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


Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option


BIG TABLESPACE_NAME                      BLKSZ   DFCT    CT_FRAG MB_FREE_FRAG     MB_FREE    MB_TOTAL PCT_USED   MAX_MB_SZ MAX_PCT_USED
--- ----------------------------------- ------ ------ ---------- ------------ ----------- ----------- -------- ----------- ------------
YES *a s TBSNAME_XXXX                      8192      1         24        3,968      79,788     179,199    97.79     256,000        68.45
                                               ------                         -----------                      -----------
sum                                                 1                              79,788                          256,000


   FILE_ID FILE_NAME                                          AUT          GB      INC_GB      MAX_GB
---------- -------------------------------------------------- --- ----------- ----------- -----------
        11 +DATA/xxx/datafile/TBSNAME_XXXX.274.800368305       YES         175           1         250

SQL> exit

free.sql

set line 150 echo off verify off trimspool off tab off
break on report
COMPUTE sum of mb_used on report
COMPUTE sum of mb_free on report
COMPUTE sum of max_mb_sz on report
COMPUTE sum of dfct on report
COLUMN file_name format a50
COLUMN mb_used format 99,999,999
COLUMN mb_free format 99,999,999
COLUMN mb_total format 99,999,999
COLUMN max_mb_sz format 99,999,999
COLUMN mb_free_frag format 99,999,999
COLUMN dfct format 99999
COLUMN blksz format 99999
COLUMN pct_used format 999.99
COLUMN max_pct_used format 999.99
COLUMN gb format 99,999,999
COLUMN inc_gb format 99,999,999
COLUMN max_gb format 99,999,999
SELECT bigfile,
DECODE(extent_management,'LOCAL','*',' ') ||
DECODE(segment_space_management,'AUTO','a ','m ') ||
DECODE(allocation_type,'SYSTEM','s ','u ') ||
fs.tablespace_name tablespace_name, block_size blksz, dfct,
fs.nfrag ct_frag,
fs.mxfrag / 1048576 mb_free_frag,
fs.free_bytes / 1048576 mb_free,
df.avail / 1048576 mb_total,
(df.avail-fs.mxfrag)/df.avail*100 pct_used,
df.max_bytes / 1048576 max_mb_sz,
(df.avail-fs.mxfrag)/df.max_bytes*100 max_pct_used
FROM dba_tablespaces ts,
(SELECT tablespace_name, count(*) dfct,
SUM(decode(maxbytes,0,user_bytes,greatest(maxbytes,user_bytes))) max_bytes,
SUM(user_bytes) avail
FROM dba_data_files
GROUP BY tablespace_name
) df,
(SELECT tablespace_name, nvl(sum(bytes),0) free_bytes, count(bytes) nfrag, nvl(max(bytes),0) mxfrag
FROM dba_free_space
GROUP BY tablespace_name
) fs
WHERE fs.tablespace_name = ts.tablespace_name(+)
AND fs.tablespace_name = df.tablespace_name
AND regexp_like(fs.tablespace_name,'&1','i')
ORDER BY pct_used desc
;
SELECT
file_id,file_name,autoextensible,
bytes/1024/1024/1024 gb,
increment_by*(bytes/blocks)/1024/1024/1024 inc_gb,
maxbytes/1024/1024/1024 max_gb
FROM dba_data_files
WHERE regexp_like(tablespace_name,'&1','i')
ORDER BY 4 asc
;

May 6, 2017

DBFS and XAG for Goldengate P2

Filed under: 12c,GoldenGate — mdinh @ 4:26 pm

In order to use agctl commands, we need to know goldengate instance_name.

Unfortunately, agctl does not work the same way as srvctl where it’s possible to determine what is configured.

ggsuser@hawk1 ~ $ $ORACLE_HOME/bin/srvctl config database
DBFS

ggsuser@hawk1 ~ $ $GRID_HOME/bin/agctl config goldengate
XAG-212: Instance '' is not yet registered.
ggsuser@hawk1 ~ $ 

How do we find out what the goldengate instance name is? IFF XAG is configured, then grep for it.

ggsuser@hawk1 ~ $ $GRID_HOME/bin/crsctl stat res -t|grep -A2 xag
xag.gg_xx-vip.vip
      1        ONLINE  ONLINE       hawk1                STABLE
xag.gg_xx.goldengate
      1        ONLINE  ONLINE       hawk1                STABLE
--------------------------------------------------------------------------------

ggsuser@hawk1 ~ $ $GRID_HOME/bin/agctl status goldengate gg_xx
Goldengate  instance 'gg_xx' is running on hawk1
ggsuser@hawk1 ~ $ 

Some other useful commands to gather configurations info.

ggsuser@hawk1 ~ $ $GRID_HOME/bin/crsctl stat res|grep xag
NAME=xag.gg_xx-vip.vip
NAME=xag.gg_xx.goldengate
TYPE=xag.goldengate.type
ggsuser@hawk1 ~ $ 

ggsuser@hawk1 ~ $ $GRID_HOME/bin/crsctl stat res|grep -i type|sort -u
TYPE=app.appvipx.type
TYPE=local_resource
TYPE=ora.asm.type
TYPE=ora.cluster_vip_net1.type
TYPE=ora.cvu.type
TYPE=ora.database.type
TYPE=ora.diskgroup.type
TYPE=ora.listener.type
TYPE=ora.mgmtdb.type
TYPE=ora.mgmtlsnr.type
TYPE=ora.network.type
TYPE=ora.oc4j.type
TYPE=ora.ons.type
TYPE=ora.scan_listener.type
TYPE=ora.scan_vip.type
TYPE=xag.goldengate.type

ggsuser@hawk1 ~ $ $GRID_HOME/bin/crsctl stat res -w "TYPE = xag.goldengate.type" -p
NAME=xag.gg_xx.goldengate
TYPE=xag.goldengate.type
ACL=owner:ggsuser:rwx,pgrp:dba:r-x,other::r--
ACTIONS=
ACTION_SCRIPT=%CRS_HOME%/bin/aggoldengateas
ACTION_TIMEOUT=60
ACTIVE_PLACEMENT=0
AGENT_FILENAME=%CRS_HOME%/bin/scriptagent
AUTO_START=restore
CARDINALITY=1
CHECK_INTERVAL=30
CHECK_TIMEOUT=0
CLEAN_TIMEOUT=60
CRITICAL_EXTRACTS=
CRITICAL_REPLICATS=
CRS_ATTRIBUTES=
DATABASES= (No DB dependencies - User Exits)
DATAGUARD_AUTOSTART=no
DB_SERVICES=
DEGREE=1
DELETE_TIMEOUT=60
DESCRIPTION="Oracle GoldenGate Clusterware Resource"
ENABLED=1
ENVIRONMENT_VARS=
FAILOVER_DELAY=0
FAILURE_INTERVAL=600
FAILURE_THRESHOLD=5
FILESYSTEMS=dbfs_mount
GG_HOME=/u03/gg/12.2.0
GG_INSTANCE_TYPE=target
HOSTING_MEMBERS=hawk1 hawk2
INSTANCE_FAILOVER=1
INTERMEDIATE_TIMEOUT=0
JAGENT_AUTOSTART=no
LOAD=1
LOGGING_LEVEL=1
MODIFY_TIMEOUT=60
MONITOR_EXTRACTS=
MONITOR_REPLICATS=
OFFLINE_CHECK_INTERVAL=0
ORACLE_CLIENT_HOME=
ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
PLACEMENT=restricted
RELOCATE_BY_DEPENDENCY=1
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=60
SERVER_CATEGORY=
SERVER_POOLS=
START_CONCURRENCY=0
START_DEPENDENCIES=hard(xag.gg_xx-vip.vip,dbfs_mount) pullup(xag.gg_xx-vip.vip,dbfs_mount)
START_TIMEOUT=300
STOP_CONCURRENCY=0
STOP_DEPENDENCIES=hard(xag.gg_xx-vip.vip,intermediate:dbfs_mount)
STOP_TIMEOUT=300
UPTIME_THRESHOLD=10m
USER_WORKLOAD=no
USE_STICKINESS=0
VERSION=2
VIP_CREATED=1
VIP_NAME=xag.gg_xx-vip.vip
ggsuser@hawk1 ~ $ 

You might be thinking, if there are no dependencies for database, then why is it referencing Database Home?

ggsuser@hawk1 ::/u03/gg/12.2.0
$ ldd ggsci 
	linux-vdso.so.1 =>  (0x00007ffcaa8ff000)
	librt.so.1 => /lib64/librt.so.1 (0x00007f6a02c5b000)
	libdl.so.2 => /lib64/libdl.so.2 (0x00007f6a02a56000)
	libgglog.so => /u03/gg/12.2.0/./libgglog.so (0x00007f6a02630000)
	libggrepo.so => /u03/gg/12.2.0/./libggrepo.so (0x00007f6a023ba000)
	libdb-6.1.so => /u03/gg/12.2.0/./libdb-6.1.so (0x00007f6a01fd5000)
	libggperf.so => /u03/gg/12.2.0/./libggperf.so (0x00007f6a01da5000)
	libggparam.so => /u03/gg/12.2.0/./libggparam.so (0x00007f6a00c8d000)
	libicui18n.so.48 => /u03/gg/12.2.0/./libicui18n.so.48 (0x00007f6a0089d000)
	libicuuc.so.48 => /u03/gg/12.2.0/./libicuuc.so.48 (0x00007f6a0051c000)
	libicudata.so.48 => /u03/gg/12.2.0/./libicudata.so.48 (0x00007f69fed57000)
	libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f69feb3a000)
	libxerces-c.so.28 => /u03/gg/12.2.0/./libxerces-c.so.28 (0x00007f69fe574000)
	libantlr3c.so => /u03/gg/12.2.0/./libantlr3c.so (0x00007f69fe35b000)
	libnnz12.so => /u01/app/oracle/product/12.1.0/db_1/lib/libnnz12.so (0x00007f69fdc36000)
	libclntsh.so.12.1 => /u01/app/oracle/product/12.1.0/db_1/lib/libclntsh.so.12.1 (0x00007f69fabbf000)
	libons.so => /u01/app/oracle/product/12.1.0/db_1/lib/libons.so (0x00007f69fa97a000)
	libclntshcore.so.12.1 => /u01/app/oracle/product/12.1.0/db_1/lib/libclntshcore.so.12.1 (0x00007f69fa406000)
	libggnnzitp.so => /u03/gg/12.2.0/./libggnnzitp.so (0x00007f69f9922000)
	libm.so.6 => /lib64/libm.so.6 (0x00007f69f9620000)
	libc.so.6 => /lib64/libc.so.6 (0x00007f69f925e000)
	/lib64/ld-linux-x86-64.so.2 (0x00005624a8090000)
	libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f69f8f56000)
	libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f69f8d3f000)
	libmql1.so => /u01/app/oracle/product/12.1.0/db_1/lib/libmql1.so (0x00007f69f8ac8000)
	libipc1.so => /u01/app/oracle/product/12.1.0/db_1/lib/libipc1.so (0x00007f69f8750000)
	libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f69f8537000)
	libaio.so.1 => /lib64/libaio.so.1 (0x00007f69f8335000)

Would’t be much better if Goldengate installation is self contained without having to download and install 2 components!

May 5, 2017

DBFS and XAG for Goldengate P1

Filed under: 12c,GoldenGate — mdinh @ 9:53 pm

What’s the difference between the 2 GoldenGate configurations below.

$ $GRID_HOME/bin/agctl config goldengate gg_xx

GoldenGate location is: /u03/gg/12.2.0
GoldenGate instance type is: target
Configured to run on Nodes: arrow1 arrow2
ORACLE_HOME location is: /u01/app/oracle/product/12.1.0/db_1
Databases needed: ora.emu1.db
File System resources needed: dbfs_mount
Extracts to monitor: 
Replicats to monitor: 
Critical extracts: 
Critical replicats: 
Autostart on DataGuard role transition to PRIMARY: no
Autostart JAgent: no

$ $GRID_HOME/bin/agctl config goldengate gg_xx

GoldenGate location is: /u03/gg/12.2.0
GoldenGate instance type is: target
Configured to run on Nodes: hawk1 hawk2
ORACLE_HOME location is: /u01/app/oracle/product/12.1.0/db_1
File System resources needed: dbfs_mount
Extracts to monitor: 
Replicats to monitor: 
Critical extracts: 
Critical replicats: 
Autostart on DataGuard role transition to PRIMARY: no
Autostart JAgent: no

Here are how they are added:

# $GRID_HOME/bin/agctl add goldengate gg_xx \
--instance_type target \
--oracle_home /u01/app/oracle/product/12.1.0/db_1 \
--nodes hawk1,hawk2 \
--network 1 --ip 10.10.10.101 \
--user ggsuser --group dba \
--filesystems dbfs_mount \
--gg_home /u03/gg/12.2.0 \
--databases ora.emu1.db

# $GRID_HOME/bin/agctl add goldengate gg_xx \
--instance_type target \
--oracle_home /u01/app/oracle/product/12.1.0/db_1 \
--nodes arrow1,arrow2 \
--network 1 --ip 10.10.10.201 \
--user ggsuser --group dba \
--filesystems dbfs_mount \
--gg_home /u03/gg/12.2.0 

One is actually source from Database and the other is target User Exits.
But, but, but –instance_type is target for both.
That’s right (meaning your observation is correct) – hehe
Bad implementation – don’t do that.

instance_type – OGG source or OGG target (source, target) (dual is bi-directional)

$XAG_HOME/bin/agctl add goldengate lax_ggate \
--gg_home /acfsmount/ggs112 \
--instance_type dual \
--nodes rac01,rac02 \
--vip_name lax-ggate1-vip \
--filesystems ora.dg_acfs.vg_acfs.acfs \
--databases ora.emu.db \
--oracle_home /u01/app/oracle/product/11.2.0.4/db_1 \
--monitor_extracts ELAX,PLAX_DEN \
--critical_extracts ELAX,PLAX_DEN \
--monitor_replicats RDEN_LAX \
--critical_replicats RDEN_LAX

Why agctl is called from $GRID_HOME in one and $XAG_HOME in another?

$ $GRID_HOME/bin/agctl query releaseversion
The Oracle Grid Infrastructure Agents release version is 3.1.0

$ $GRID_HOME/bin/agctl query deployment
The Oracle Grid Infrastructure Agents deployment is bundled

agctl from GRID_HOME is bundled with install, but older version.
agctl from XAG_HOME is standalone, downloaded and installed from

Oracle Grid Infrastructure Standalone Agents for Oracle Clusterware 11g Rel. 2, 12c Rel. 1 and 12c Rel. 2
http://www.oracle.com/technetwork/database/database-technologies/clusterware/downloads/xag-agents-downloads-3636484.html

Blog at WordPress.com.