Feed aggregator

Using bitmap indexes in OLTP database with mostly inserts

Tom Kyte - 10 hours 18 min ago
Hi, We have a table for logging metadata about processed messages in a production system. The table have approx 32M rows today and 25 columns. The <b>total number of rows is expected to be around 100M</b> in the future. When the processing of a...
Categories: DBA Blogs

Trigger based on set of data

Tom Kyte - 10 hours 18 min ago
Hi All, i have a scenario where I want to create a trigger which will generate a flat file whenever a set of data like department number's(10,20,30,40,50...) changes(insert/updates) on a particular date(sysdate).
Categories: DBA Blogs

How to fetch up to 5MB of text/string/data from a larger a clob in oracle

Tom Kyte - 10 hours 18 min ago
Hi Oracle, <code> CREATE TABLE XMLISSUE ( xmltablecolumn clob ); </code> Created a table As shown below code I have inserted some data to clob <code> DECLARE XMLCLO...
Categories: DBA Blogs

when I drop a plsql function, 1 view goes invalid, 1 view and 1 procedure remain valid

Tom Kyte - 10 hours 18 min ago
We recently upgraded to database version 12c... We found an anomaly that we can't explain and wondering if this is a bug or a misunderstanding of new functionality within 12c. We can reproduce this issue on demand with the following example....
Categories: DBA Blogs

use of Block change tracking file for offline incremental backup

Tom Kyte - 10 hours 18 min ago
Hi , If block change tracking is ENABLED, Does oracle uses this file when we take an offline incremental backup ( in mount mode ) ? As the Db is not open , I think it can not access the file ( change tracking file), I believe the feature is only...
Categories: DBA Blogs

Why Focused Learning Is Better For Your Career

Complete IT Professional - 18 hours 23 min ago
In this article, I’ll explain what I mean by “focused learning” and why it’s a good idea for your career. What is Focused Learning? When we want to learn something, we tend to research on it. We look for websites, articles, videos on the topic. Or we enrol in a university course, if it’s career […]
Categories: Development

12cR1 RAC Posts -- 6 :Running the Cluster Verification Utility

Hemant K Chitale - 19 hours 39 min ago
With a successful RAC Cluster, running the Cluster Verification Utility

[oracle@collabn1 ~]$ cluvfy stage -post crsinst -n collabn1,collabn2

Performing post-checks for cluster services setup

Checking node reachability...
Node reachability check passed from node "collabn1"


Checking user equivalence...
User equivalence check passed for user "oracle"

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity using interfaces on subnet "192.168.78.0"
Node connectivity passed for subnet "192.168.78.0" with node(s) collabn2,collabn1
TCP connectivity check passed for subnet "192.168.78.0"


Check: Node connectivity using interfaces on subnet "172.16.100.0"
Node connectivity passed for subnet "172.16.100.0" with node(s) collabn1,collabn2
TCP connectivity check passed for subnet "172.16.100.0"

Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "192.168.78.0".
Subnet mask consistency check passed for subnet "172.16.100.0".
Subnet mask consistency check passed.

Node connectivity check passed

Checking multicast communication...

Checking subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251"...
Check of subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251" passed.

Check of multicast communication passed.

Checking whether the ASM filter driver is active and consistent on all nodes
ASM filter driver library is not installed on any of the cluster nodes.
ASM filter driver configuration was found consistent across all the cluster nodes.
Time zone consistency check passed

Checking Cluster manager integrity...


Checking CSS daemon...
Oracle Cluster Synchronization Services appear to be online.

Cluster manager integrity check passed


UDev attributes check for OCR locations started...
UDev attributes check passed for OCR locations


UDev attributes check for Voting Disk locations started...
UDev attributes check passed for Voting Disk locations

Default user file creation mask check passed

Checking cluster integrity...


Cluster integrity check passed


Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations


Checking daemon liveness...
Liveness check passed for "CRS daemon"

Checking OCR config file "/etc/oracle/ocr.loc"...

OCR config file "/etc/oracle/ocr.loc" check successful


Disk group for ocr location "+OCRVOTE/collabn-cluster/OCRFILE/registry.255.934671619" is available on all the nodes


Checking OCR backup location "/u01/app/12.1.0/grid/cdata/collabn-cluster"

OCR backup location "/u01/app/12.1.0/grid/cdata/collabn-cluster" check passed

NOTE:
This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.

OCR integrity check passed

Checking CRS integrity...

Clusterware version consistency passed.

CRS integrity check passed

Checking node application existence...

Checking existence of VIP node application (required)
VIP node application check passed

Checking existence of NETWORK node application (required)
NETWORK node application check passed

Checking existence of ONS node application (optional)
ONS node application check passed


Checking Single Client Access Name (SCAN)...

Checking TCP connectivity to SCAN listeners...
TCP connectivity to SCAN listeners exists on all cluster nodes

Checking name resolution setup for "collabn-cluster-scan.racattack"...

Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...
All nodes have same "hosts" entry defined in file "/etc/nsswitch.conf"
Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed


Checking SCAN IP addresses...
Check of SCAN IP addresses passed

Verification of SCAN VIP and listener setup passed

Checking OLR integrity...
Check of existence of OLR configuration file "/etc/oracle/olr.loc" passed
Check of attributes of OLR configuration file "/etc/oracle/olr.loc" passed

WARNING:
This check does not verify the integrity of the OLR contents. Execute 'ocrcheck -local' as a privileged user to verify the contents of OLR.

OLR integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed

User "oracle" is not part of "root" group. Check passed
Oracle Clusterware is installed on all nodes.
CTSS resource check passed
Query of CTSS for time offset passed

CTSS is in Active state. Proceeding with check of clock time offsets on all nodes...
Check of clock time offsets passed


Oracle Cluster Time Synchronization Services check passed
Checking VIP configuration.
Checking VIP Subnet configuration.
Check for VIP Subnet configuration passed.
Checking VIP reachability
Check for VIP reachability passed.

Post-check for cluster services setup was successful.
[oracle@collabn1 ~]$
[oracle@collabn1 ~]$ su
Password:
[root@collabn1 oracle]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1688
Available space (kbytes) : 407880
ID : 827167720
Device/File Name : +OCRVOTE
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@collabn1 oracle]#
[root@collabn1 oracle]# ocrcheck -local
Status of Oracle Local Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1028
Available space (kbytes) : 408540
ID : 1014277103
Device/File Name : /u01/app/12.1.0/grid/cdata/collabn1.olr
Device/File integrity check succeeded

Local registry integrity check succeeded

Logical corruption check succeeded

[root@collabn1 oracle]#



[root@collabn2 oracle]# ocrcheck -local
Status of Oracle Local Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 796
Available space (kbytes) : 408772
ID : 1896774486
Device/File Name : /u01/app/12.1.0/grid/cdata/collabn2.olr
Device/File integrity check succeeded

Local registry integrity check succeeded

Logical corruption check succeeded

[root@collabn2 oracle]#


The "cluvfy stage -post crsinst" command as the Grid Infrastructure user checks the status of all components after the installation and configuration of the Cluster.  Note : This does not and cannot check any database (RAC or non-RAC) that is created.

The "ocrcheck" commands by root are to check the integrity of the Cluster Registry and Local Registry.  (You would run "ocrcheck -local" on each node of the Cluster).

.
.
.

Categories: DBA Blogs

Links for 2017-02-19 [del.icio.us]

Categories: DBA Blogs

RMAN unused block compression against TABLE drop vs TABLE with lob column compression.

Tom Kyte - Sun, 2017-02-19 19:06
Hi Tom, I am just trying to understand RMAN unused block compression against TABLE with LOB column compression vs TABLE drop. Consider LOB data for column 'DATA' in a table 'EMPLOYEES' under SCOTT schema. - lob segment SELECT SEGMENT_NAME FR...
Categories: DBA Blogs

Index column order and Partitioned table

Tom Kyte - Sun, 2017-02-19 19:06
We have a table with few hundred millions of rows. We perform INSERT, DELETE, SEARCH operation on this table. Column ID is unique and column CODE has very few distinct values (lets say 100 distinct values). So we partition table by LIST CODE column...
Categories: DBA Blogs

How to speed up slow unicode migration of a table with xmltype columns

XTended Oracle SQL - Sun, 2017-02-19 18:46

Recently I have had an issue with slow unicode migration of the database upgraded from 10g to 12.1.0.2. The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:

update  /*+ PARALLEL(A,16)*/ "RRR"."T_XMLDATA" A  set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16')

“SYS_NC00011$” was internal hidden CLOB column used to store XMLTYPE. As you can see DMU added PARALLEL hint, but though oracle can use parallel dml for xmltype since 12.1.0.1, we can’t use it because of its’ limitations:

Changes in Oracle Database 12c Release 1 (12.1.0.1) for Oracle XML DB

Parallel DML Support for XMLType
Support for parallel DML has been improved for XMLType storage model binary XML using SecureFiles LOBs. The performance and scalability have been improved for both CREATE TABLE AS SELECT and INSERT AS SELECT.

Restrictions on Parallel DML

Parallel DML can be done on tables with LOB columns provided the table is partitioned. However, intra-partition parallelism is not supported.

For non-partitioned tables with LOB columns, parallel INSERT operations are supported provided that the LOB columns are declared as SecureFiles LOBs. Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.

Btw, Oracle didn’t support parallel dml for xmltype on previous releases:

No Parallel DML for XMLType – DML operations on XMLType data are always performed in serial. Parallel DML is not supported for XMLType. (Parallel query and DDL are supported for XMLType.)

So I had to use manual parallelization:
1. Monitor “Convert application tables” step through “View Table Conversion progress” and press “Stop” button during conversion of this table.
2. Create table with ROWIDs of this table and split them into 16 groups:

create table tmp_rids as 
select rowid rid, ntile(16)over(order by rowid) grp 
from t_xmldata;

3. Execute

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT LEVEL 1,FOREVER'; 

to avoid “ORA-22839: Direct updates on SYS_NC columns are disallowed”
4. Start 16 sessions and each of them have to update own part:

update t_xmldata A 
set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16') 
where rowid in (select rid from tmp_rids where grp=&grp);
commit;

5. Disable event 22838:

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT OFF'; 

6. Open “View Table Conversion progress” window, click on this table and change “Retry” to “Skip” option for the update step.

This simple method allowed to make unicode migration about 16 times faster.

Categories: Development

Data Pump or Data Pain Part04 – The End

Michael Dinh - Sun, 2017-02-19 13:10

Please take a look at my notes below:
DataPump: Schema/DB Migration Notes


Masking bind values with Oracle Transparent Sensitive Data Protection

Tom Kyte - Sun, 2017-02-19 00:46
Hi, I'm trying to use the Oracle Transparent Sensitive Data Protection feature on a database ver. 12.1.0.2.0. The plan is to use the predefined REDACT_AUDIT policy in order to hide bind variables that refer sensitive columns. I've followed the i...
Categories: DBA Blogs

Data to be displayed grouped by start and end date

Tom Kyte - Sun, 2017-02-19 00:46
I have a table t I have a table t Create table t (employee varchar2(10), (job varchar2(5), start_date date, end_date date, FTE int ); with this data in it: insert into t values (1111,1,19-May-2008,18-May-2010,100); insert into t v...
Categories: DBA Blogs

Regarding expressions around columns in SQL

Tom Kyte - Sun, 2017-02-19 00:46
Hi Tom, i havae a query that i given below SELECT 2 tab_type , ID,LVNAME,LV_NAME,APPL_FROM,GRANT_FROM,GRANT_TO, APPLICABLE,LWP,LVSTR,TYPE,EFFECT_ON,AGNST_FROM, HALFPAID,UNPAID,QTRPAID,0,IS_PRORATA,SYSDATE FROM UHO.LVOPEN W...
Categories: DBA Blogs

Shifting / Replacing RAC nodes

Tom Kyte - Sun, 2017-02-19 00:46
Greetings, Hope you are doing well. I wanted to ask a short question/advice for one of our issues. We are replacing some nodes in a production rac cluster with newer ones. Is adding/deleting nodes the best way to go about? Or are there any ...
Categories: DBA Blogs

Updating Materialized Views - Unable to render MATERIALIZED VIEW DDL ...with DBMS_METADATA attempting internal generator

Tom Kyte - Sun, 2017-02-19 00:46
I know it was not efficient, but due to my privileges on an Oracle database I had tablespace on, I had been running an SSIS package to drop and recreate about 20 tables daily. Tables used for reporting queries. It took 2 hours. I was running SSIS f...
Categories: DBA Blogs

Parse a valid WHERE clause syntax using PL/SQL

Tom Kyte - Sun, 2017-02-19 00:46
I have a predicate column in a table which stores a text which must be a valid WHERE clause SEQ PREDICATE --- --------- 1 EMPNO in ( select mgr from emp) AND ( COMISSION is null or SAL > 100 ) 2 RTRIM(LAST_NAME) LIKE '%KYTE%' How to ...
Categories: DBA Blogs

Script to get OMS ,Agent and DB Plugin Patch details

Arun Bavera - Sat, 2017-02-18 18:17

 

GETTING OMS  PATCH DETAILS

export OMS_HOME=/u01/middleware/oms
export ORACLE_HOME=$OMS_HOME
export PLUGIN_HOME=/u01/middleware/plugins/oracle.sysman.db.oms.plugin_12.1.0.8.0/

DT=$(date '+%m%d%Y')

LOG=/tmp/EM_Patch_details_${DT}.txt

echo "1.Getting the OMS system patches" >${LOG}
echo "=====================================================================================" >>${LOG}

$ORACLE_HOME/OPatch/opatch lsinventory -details >>${LOG}

echo "" >>${LOG}
echo "======================================================================================" >>${LOG}
echo "2.Getting the OMS DB PLUGIN patches" >>${LOG}
echo "======================================================================================" >>${LOG}
$ORACLE_HOME/OPatch/opatch lsinventory -oh $PLUGIN_HOME -details >>${LOG}

 

GETTING AGENT PATCH DETAILS

export AGENT_BASE=/u01/app/oracle/product/agent12c
export AGENT_HOME=${AGENT_BASE}/core/12.1.0.5.0
export ORACLE_HOME=${AGENT_HOME}
export PLUGIN_HOME=${AGENT_BASE}/plugins/oracle.sysman.db.agent.plugin_12.1.0.8.0/

DT=$(date '+%m%d%Y')

LOG=/tmp/AGENT_Patch_details_${DT}.txt

echo "1.Getting the AGENT system patches" >${LOG}
echo "=====================================================================================" >>${LOG}

$ORACLE_HOME/OPatch/opatch lsinventory -details >>${LOG}

echo "" >>${LOG}
echo "======================================================================================" >>${LOG}
echo "2.Getting the AGENT DB PLUGIN patches" >>${LOG}
echo "======================================================================================" >>${LOG}
$ORACLE_HOME/OPatch/opatch lsinventory -oh $PLUGIN_HOME -details >>${LOG}

Categories: Development

Log File Switches Twice as Often as ARCHIVE_LAG_TARGET

Tom Kyte - Sat, 2017-02-18 06:26
I have an odd issue occurring on my primary pre-production database server. In both my production and pre-production environments, we have the ARCHIVE_LAG_TARGET parameter set to force a log switch at regular intervals. I know this is not a modern-...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator