Thinking Out Loud

August 4, 2017

Windows Datapump Export

Filed under: 11g,oracle,Windows — mdinh @ 3:45 am
Tags:

The purpose of the script is to perform full database export keeping 3 export copies.
If export is successful, then fullexp*.dmp will be renamed with _1.dmp suffix added to filename.
If export is unsuccessful, then code will exit, skipping rename operations.

Note: there should never be .dmp file without # suffix unless export is unsuccessful.

In hindsight, directoryName should be using variable (ORACLE_SID) versus hardcode.

SET ORACLE_SID=DB01
SET directoryName=D:\DB01\export

expdp ‘/ as sysdba’ full=y directory=DATA_PUMP_DIR dumpfile=fullexp_%ORACLE_SID%_%COMPUTERNAME%.dmp logfile=fullexp_%ORACLE_SID%_%COMPUTERNAME%.log flashback_time=SYSTIMESTAMP REUSE_DUMPFILES=YES
IF %ERRORLEVEL% NEQ 0 GOTO ERROR

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.dmp” (DEL “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.*”)

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.dmp” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.dmp” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.dmp”)
IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.log” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.log” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_3.log”)

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.dmp” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.dmp” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.dmp”)
IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.log” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.log” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_2.log”)

IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.dmp” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.dmp” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.dmp”)
IF EXIST “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.log” (RENAME “%directoryName%\fullexp_%ORACLE_SID%_%COMPUTERNAME%.log” “fullexp_%ORACLE_SID%_%COMPUTERNAME%_1.log”)

EXIT 0

:ERROR
EXIT 1

Results after 4 runs.

08/03/2017  07:53 PM     2,680,008,704 fullexp_DB01_CMWPHV1_1.dmp
08/03/2017  07:53 PM           161,707 fullexp_DB01_CMWPHV1_1.log
08/03/2017  07:46 PM     2,680,008,704 fullexp_DB01_CMWPHV1_2.dmp
08/03/2017  07:46 PM           161,707 fullexp_DB01_CMWPHV1_2.log
08/03/2017  07:37 PM     2,680,008,704 fullexp_DB01_CMWPHV1_3.dmp
08/03/2017  07:37 PM           161,707 fullexp_DB01_CMWPHV1_3.log

June 27, 2017

Notes on datapatch

Filed under: 12c,oracle — mdinh @ 1:47 pm

Mike Dietrich is the God father of all Oracle upgrades.

Here are just a few reference and should read

DBUA 12c and “datapatch.pl” – things to know

DBUA misses the post-upgrade datapatch execution in Oracle 12.1.0.2. The solution is to apply the SQL changes manually after DBUA has completed the database upgrade to Oracle Database 12c:

cd $ORACLE_HOME/OPatch
./datapatch -verbose

Does DBCA execute “datapatch” in Oracle 12.2?

My notes: when it doubt check it out.

$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 on Thu Jan 19 10:35:27 2017
Copyright (c) 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_93223_2017_01_19_10_35_27/sqlpatch_invocation.log

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Patch 24315824 (Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)):
  Installed in the binary registry only
Bundle series DBBP:
  ID 161018 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    24315824 (Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016))
    24340679 (DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679))

Installing patches...
Patch installation complete.  Total patches installed: 2

Validating logfiles...
Patch 24315824 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24315824/20676808/24315824_apply_DBFS_2017Jan19_10_35_43.log (no errors)
Patch 24340679 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_DBFS_2017Jan19_10_35_47.log (no errors)
SQL Patching tool complete on Thu Jan 19 10:37:16 2017

SQL> set serveroutput on
SQL> exec dbms_qopatch.get_sqlpatch_status;

-- Logfile is provided. How convenient is that?
Patch Id : 24315824
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:14
        Description : Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24315824/20676808/24315824_apply_DBFS_2017Jan19_10_35_43.log
        Status : SUCCESS

Patch Id : 24340679
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:16
        Description : DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_DBFS_2017Jan19_10_35_47.log
        Status : SUCCESS

PL/SQL procedure successfully completed.

SQL>


$ cd $ORACLE_HOME/OPatch
$ ./datapatch -verbose


SQL> set serveroutput on
SQL> exec dbms_qopatch.get_sqlpatch_status;


Patch Id : 24315824
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:14
        Description : Database PSU 12.1.0.2.161018, Oracle JavaVM Component (OCT2016)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24315824/20676808/24315824_apply_DBFS_2017Jan19_10_35_43.log
        Status : SUCCESS

Patch Id : 24340679
        Action : APPLY
        Action Time : 19-JAN-2017 10:37:16
        Description : DATABASE BUNDLE PATCH: 12.1.0.2.161018 (24340679)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/24340679/20646358/24340679_apply_DBFS_2017Jan19_10_35_47.log
        Status : SUCCESS

PL/SQL procedure successfully completed.

SQL>

June 16, 2017

12c DataGuard Validate and More

Filed under: 12c,Dataguard,oracle — mdinh @ 11:11 pm
12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt) (Doc ID 1582837.1)	

Configuration
db_name=hawk
db_unique_name=hawka (primary)
db_unique_name=hawkb (standby)

Configuration - hawkdg
  Protection Mode: MaxPerformance
  Members:
    hawka - Primary database
      hawkb - Physical standby database 

Validate DataGuard Configurations.

Monitorable (Read-Only) Properties

show configuration verbose

show database verbose hawka
show database verbose hawkb

validate database verbose hawka
validate database verbose hawkb

There is no need to use on database if the instance names are unique across primary and standby environments.

You might ask, why are instance name not the  same on primary and standby?

I don’t know.

show instance verbose hawka1 on database hawka
show instance verbose hawka2 on database hawka

show instance verbose hawkb1 on database hawkb
show instance verbose hawkb2 on database hawkb

The InconsistentProperties monitorable property returns a table that shows all properties whose values contained in the broker configuration file are inconsistent with the values in the corresponding server parameter file or the runtime values.

show database hawka InconsistentProperties
show database hawkb InconsistentProperties

The InconsistentLogXptProps monitorable property returns a table that shows all properties related to redo transport services whose values are inconsistent between the broker configuration file and the runtime value.

show database hawka InconsistentLogXptProps
show database hawkb InconsistentLogXptProps

The LogXptStatus monitorable property returns a table that contains the error status of redo transport services for each of the enabled configuration members. This property pertains to the primary database, a physical standby database that ships redo data, or a far sync instance.

show database hawka LogXptStatus

The SendQEntries monitorable property returns a table that shows all log files on the primary database that were not successfully archived to one or more standby databases. This property pertains to the primary database

show database hawka SendQEntries

The RecvQEntries monitorable property returns a table indicating all log files that were received by the standby database but have not yet been applied. If no rows are returned, it implies all log files received have been applied. This property pertains to a standby database.

show database hawkb RecvQEntries

The TopWaitEvents monitorable property specifies the 5 events with the longest waiting time in the specified instance.

show instance hawkb1 TopWaitEvents

How to edit database properties for all instances.

edit instance * on database hawka set property logarchivetrace=0;

Lastly, all commands and be run from shell script with example below.

echo "***** Checking Data Guard Broker Configuration ...."
dgmgrl -echo << END
connect /
show configuration verbose
show configuration TraceLevel
show database hawklas
show database hawksan
show instance hawklas DGConnectIdentifier
show instance hawksan DGConnectIdentifier
show instance hawklas StaticConnectIdentifier
show instance hawksan StaticConnectIdentifier
show instance hawklas InconsistentProperties
show instance hawksan InconsistentProperties
show instance hawklas LogArchiveMaxProcesses
show instance hawksan LogArchiveMaxProcesses
show instance hawklas DelayMins
show instance hawksan DelayMins
show instance hawklas LogArchiveTrace
show instance hawksan LogArchiveTrace
show instance hawklas statusreport
show instance hawksan statusreport
exit
END
exit

June 11, 2017

GoldenGate 12.2 TROUBLESHOOTING REPLICAT LAG

Filed under: 12c,GoldenGate,oracle — mdinh @ 2:16 pm

Time Since Chkpt and Lag at Chkpt from replicat keep increasing

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
REPLICAT    RUNNING     R_NEW12C    03:49:45      06:37:47    

This occurs for due to the following reasons:

Delivering a long running transaction
Waiting on a full table scan
Blocked by another sessions
Primary extract lag or pump lag keeps increasing

Is There a Way to Make Long-running Transactions Checkpoint? (Doc ID 969684.1)

The tradeoff with GROUPTRANSOPS is with efficiency. 
The tradeoff with MAXTRANSOPS is efficiency and transaction integrity. 

There are no long running transaction from the extract and is monitored using WARNLONGTRANS 15m, CHECKINTERVAL 3m.

grep "Long Running Transaction" dirrpt/E_OLD10G.rpt

My suspicion is FTS, but how to find out?

SQL> r
  1  select
  2  -- SQL_ID,PLAN_HASH_VALUE,
  3  OBJECT_OWNER,OBJECT_NAME, min(TIMESTAMP) min_ts, max(TIMESTAMP) max_ts, count(*) ct
  4  from DBA_HIST_SQL_PLAN
  5  where operation='TABLE ACCESS'
  6  and options='FULL'
  7  and NOT REGEXP_LIKE(object_owner,'SYS|SYSTEM|DBSNMP')
  8  and TIMESTAMP > TO_DATE('01-JUN-2017','DD-MON-YYYY')
  9  group by
 10  -- SQL_ID,PLAN_HASH_VALUE,
 11  OBJECT_OWNER,OBJECT_NAME
 12  order by count(*), OBJECT_OWNER,OBJECT_NAME
 13*
 
OBJECT_OWNER         OBJECT_NAME                    MIN_TS               MAX_TS                       CT
-------------------- ------------------------------ -------------------- -------------------- ----------
XXX                  THISISAREALLYLONGTABLENAME     01-JUN-2017 00:01:00 11-JUN-2017 02:55:36        114

SQL> select index_name from dba_indexes where table_name='THISISAREALLYLONGTABLENAME';
no rows selected
SQL> 

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#
;

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 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
;

April 16, 2017

Be Prepared When Using DBFS

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

Goldegate 12c implementation for 2-node RAC with XAG from Grid Home using DBFS.

We are facing situation where DBFS has 41G free while DBFS_TS has 1.5G free and having to resize BIGFILE tablespace.

Tablespace and DBFS size is 45G.

Basically, 45G tablespace is used to maintain 4G Goldengate files.

Based on steps followed from NOTE:1453350.1 – How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized?

Next step is to Shrink a SECUREFILE LOB using Online Redefinition (DBMS_REDEFINITION)? (Document  1394613.1)

At this point, does not look pretty.

Here are all the research I have gone through.

NOTE:1150157.1 - List of Critical Patches Required For Oracle 11.2 DBFS and DBFS Client
BUG:13824914 - DBFS FREE SPACE IS NOT RECLAIMED AFTER DELETIONS.
BUG:12662040 - SECUREFILE LOB SEGMENT KEEPS GROWING IN CASE OF PLENTY OF FREE SPACE

How DBFS Reclaims Free Space After Files Are Deleted (Doc ID 1438356.1) 
NOTE:162345.1 - LOBS - Storage, Read-consistency and Rollback
NOTE:386341.1 - How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM
--------------------------------------------------------------------------------
NOTE:1453350.1 - How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized?
3) Determine if the LOBSEGMENT is a candidate for shrink / reorganization

The following query will show the extents allocated for the LOBSEGMENT

SELECT BYTES, COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_NAME = ''  GROUP BY BYTES ORDER BY 2;

If NON Data Bytes is  one or more of the extent sizes from this query ...
     then this segment is a candiate for a shrink / reorganization as this process will likely be able to reduce the size of the LOBSEGMENT by at least one extent

4) If #3 is true ... then the method in the following note may be used to shrink / reorganize the lob segment

How to Shrink a SECUREFILE LOB using Online Redefinition (DBMS_REDEFINITION)? (Document  1394613.1)
--------------------------------------------------------------------------------
NOTE:66431.1 - LOBS - Storage, Redo and Performance Issues

April 9, 2017

Oracle Linux Vagrant Boxes

Filed under: oracle,Vagrant,VirtualBox — mdinh @ 5:57 pm

Almost like finding gold.

Oracle publishes Vagrant boxes based on Oracle Linux

April 8, 2017

Download and Install Vagrant Box Locally

Filed under: oracle,Vagrant — mdinh @ 7:20 pm

References:

Vagrant Box from oravirt

GitHub from oravirt

Blog Post from oravirt

Windows binaries of GNU Wget

Copy wget to C:\Users\dinh\AppData\Local\Programs\Git\mingw64\bin (My install location)

You might be thinking, why download box?

  1. Box may get removed.
  2. Box is staged at preferred versus default location.
dinh@CMWPHV1 MINGW64 /f/Vagrant
$ git clone --recursive https://github.com/oravirt/vagrant-vbox-si-fs.git
Cloning into 'vagrant-vbox-si-fs'...
remote: Counting objects: 59, done.
remote: Compressing objects: 100% (3/3), done.
remote: Total 59 (delta 0), reused 0 (delta 0), pack-reused 56
Unpacking objects: 100% (59/59), done.
Submodule 'ansible-oracle' (http://github.com/oravirt/ansible-oracle) registered for path 'ansible-oracle'
Cloning into 'F:/Vagrant/vagrant-vbox-si-fs/ansible-oracle'...
warning: redirecting to https://github.com/oravirt/ansible-oracle/
remote: Counting objects: 2169, done.
remote: Compressing objects: 100% (110/110), done.
remote: Total 2169 (delta 56), reused 0 (delta 0), pack-reused 2052
Receiving objects: 100% (2169/2169), 542.42 KiB | 0 bytes/s, done.
Resolving deltas: 100% (1009/1009), done.
Submodule path 'ansible-oracle': checked out 'd311447608e28a6df7816aeb05aa38d610254931'


dinh@CMWPHV1 MINGW64 /f/Vagrant
$ cd vagrant-vbox-si-fs/


dinh@CMWPHV1 MINGW64 /f/Vagrant/vagrant-vbox-si-fs (master)
$ wget -q https://atlas.hashicorp.com/oravirt/boxes/ol73/versions/20170110/providers/virtualbox.box


dinh@CMWPHV1 MINGW64 /f/Vagrant/vagrant-vbox-si-fs (master)
$ ls
ansible.cfg  ansible-oracle/  hosts.yml  README.md  swrepo/  Vagrantfile  virtualbox.box


dinh@CMWPHV1 MINGW64 /f/Vagrant/vagrant-vbox-si-fs (master)
$ vagrant box add oravirt/ol73 file:///F:/Vagrant/vagrant-vbox-si-fs/virtualbox.box
==> box: Box file was not detected as metadata. Adding it directly...
==> box: Adding box 'oravirt/ol73' (v0) for provider:
    box: Unpacking necessary files from: file:///F:/Vagrant/vagrant-vbox-si-fs/virtualbox.box
    box:
==> box: Successfully added box 'oravirt/ol73' (v0) for 'virtualbox'!


dinh@CMWPHV1 MINGW64 /f/Vagrant/vagrant-vbox-si-fs (master)
$ vagrant up
Bringing machine 'db1-fs' up with 'virtualbox' provider...
==> db1-fs: Importing base box 'oravirt/ol73'...
==> db1-fs: Matching MAC address for NAT networking...


dinh@CMWPHV1 MINGW64 /f/Vagrant/vagrant-vbox-si-fs (master)
$ vagrant ssh

----------------------------------------
Welcome to db1-fs
OracleLinux 7.3 x86_64

FQDN: db1-fs
IP:   10.0.2.15

Processor: Intel(R) Core(TM) i7-2640M CPU @ 2.80GHz
#CPU's:    1
Memory:    2749 MB
Kernel:    4.1.12-61.1.18.el7uek.x86_64

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

[vagrant@db1-fs ~]$ ifconfig -a
enp0s3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.0.2.15  netmask 255.255.255.0  broadcast 10.0.2.255
        inet6 fe80::a00:27ff:fe4f:4c27  prefixlen 64  scopeid 0x20
        ether 08:00:27:4f:4c:27  txqueuelen 1000  (Ethernet)
        RX packets 1125  bytes 118982 (116.1 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 814  bytes 127567 (124.5 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

enp0s8: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.9.61  netmask 255.255.255.0  broadcast 192.168.9.255
        inet6 fe80::a00:27ff:fe84:e732  prefixlen 64  scopeid 0x20
        ether 08:00:27:84:e7:32  txqueuelen 1000  (Ethernet)
        RX packets 76  bytes 8068 (7.8 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 25  bytes 1826 (1.7 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10
        loop  txqueuelen 0  (Local Loopback)
        RX packets 96  bytes 8148 (7.9 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 96  bytes 8148 (7.9 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[vagrant@db1-fs ~]$
Next Page »

Create a free website or blog at WordPress.com.