DBA Blogs

Modify non partitioned table to partitioned online

Tom Kyte - Tue, 2017-03-07 01:06
Hi Tom, I am trying to convert a non partitioned table into interval partitioned table online.I created a simple test table and ran the below script but I get ORA-14006 invalid partition name error. My conjecture would be this is not allowed for i...
Categories: DBA Blogs

Create Partition on load table 11g

Tom Kyte - Tue, 2017-03-07 01:06
Hi Connor/Chris, Please have a look at below table structure This is load table and there is no uniqueness related to any column, hence we cannot go for Primary key for this. <code> tb_card_details --------------- card_id start_date end...
Categories: DBA Blogs

Data guard

Tom Kyte - Tue, 2017-03-07 01:06
Hi AskTOM team, -- On standby database I am using real time redo apply. My standby database is started using following commands. STARTUP MOUNT; ALTER DATABASE OPEN READ ONLY; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFI...
Categories: DBA Blogs

Re: parameterized views revisited

Tom Kyte - Tue, 2017-03-07 01:06
Hi Connor, Thank you for your response to my question. I have a follow up question about the consolidated view. I did the following suggestion: <code> create or replace 2 view V as 3 select 'T1' tname, t1.* from t1 4 union all 5...
Categories: DBA Blogs

Exadata snapshot access limitation

Tom Kyte - Tue, 2017-03-07 01:06
we have Exadata and we've started to implement snapshots. the business case required: we create a snapshots from the same test master, and grant access on each two snapshot for a specific group of users while preventing them to access other snapsho...
Categories: DBA Blogs

User Active or Deactive

Tom Kyte - Mon, 2017-03-06 06:46
I used "alter user scott account lock;" for lock the account. Now i try to access database so oracle prompts a message user is locked. but when i connect via website build in asp.net , I first time also get message via exception handling in front-s...
Categories: DBA Blogs

weird behavior for namespaces of public synonym and normal tables

Tom Kyte - Mon, 2017-03-06 06:46
Hi ,tom I am currently working with synonym in oracle and find the fact that: SQL> create public synonym mysynonym for myschema.mytable; Synonym created. SQL> create table myschema.mytable(a int); ERROR at line 1: ORA-00955: name is alre...
Categories: DBA Blogs

DB Block size greater than 8K

Tom Kyte - Mon, 2017-03-06 06:46
Hi Tom, I am looking for guidance on when to choose a block size greater than 8K for Oracle DB. I have seen few posts from the past which indicated 8K typically should do fine for most scenarios but have always been under the impression that for D...
Categories: DBA Blogs

12cR1 RAC Posts -- 7 : OCR Commands

Hemant K Chitale - Mon, 2017-03-06 03:11
[Yes, I know that 12.2 is now available for download but it will be some time before I have a running 12.2 RAC environment]

Some OCR / OLR Commands :

The OCR is the Cluster Registry.  We also have an OLR that is the Local Registry which is created on a local filesystem.

We can check the consistency of the Registry with ocrcheck.  Note the difference between using oracle (or grid) and using root to run the check.  oracle can't check the OLR and can't do a logical consistency check of the OCR -- both require to be run as root.

[root@collabn1 ~]# su - oracle
[oracle@collabn1 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[oracle@collabn1 ~]$ ocrcheck -local
PROTL-602: Failed to retrieve data from the local registry
PROCL-26: Error while accessing the physical storage Operating System error [Permission denied] [13]
[oracle@collabn1 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1676
Available space (kbytes) : 407892
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 bypassed due to non-privileged user

[oracle@collabn1 ~]$ su
Password:
[root@collabn1 oracle]# ocrcheck -local
Status of Oracle Local Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1036
Available space (kbytes) : 408532
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]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1676
Available space (kbytes) : 407892
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]#


Oracle automates backups of the OCR (but not the OLR !).  Below, the -showbackuploc shows the location of backups.

[root@collabn1 oracle]# ocrconfig -showbackuploc
The Oracle Cluster Registry backup location is [/u01/app/12.1.0/grid/cdata/]
[root@collabn1 oracle]# ls -lt /u01/app/12.1.0/grid/cdata
total 1272
-rw-------. 1 root oinstall 503484416 Mar 6 17:03 collabn1.olr
drwxrwxr-x. 2 oracle oinstall 4096 Jan 16 14:12 collabn-cluster
drwxr-xr-x. 2 oracle oinstall 4096 Dec 19 15:06 collabn1
drwxr-xr-x. 2 oracle oinstall 4096 Dec 19 14:37 localhost
[root@collabn1 oracle]# ls -lt /u01/app/12.1.0/grid/cdata/collabn1
total 820
-rw-r--r--. 1 root root 839680 Dec 19 15:06 backup_20161219_150615.olr
[root@collabn1 oracle]# ocrconfig -showbackup

collabn1 2017/01/16 14:09:40 /u01/app/12.1.0/grid/cdata/collabn-cluster/backup00.ocr 0

collabn1 2017/01/16 14:09:40 /u01/app/12.1.0/grid/cdata/collabn-cluster/day.ocr 0

collabn1 2017/01/16 14:09:40 /u01/app/12.1.0/grid/cdata/collabn-cluster/week.ocr 0

collabn2 2016/12/19 15:47:24 /u01/app/12.1.0/grid/cdata/collabn-cluster/backup_20161219_154724.ocr 0

collabn2 2016/12/19 15:47:16 /u01/app/12.1.0/grid/cdata/collabn-cluster/backup_20161219_154716.ocr 0
[root@collabn1 oracle]#


All recent (4-Hourly, Daily, Weekly) of the OCR are on the "master" node -- collabn1 -- which comes up first in my cluster.    The 19-Dec backups (of the OCR and OLR) are when I started setting up the Cluster.  Note that there are no subsequent (automated) OLR backups.
Note : There are no 4-Hourly/Daily/Weekly backups since 16-Jan because I haven't had my cluster running for long enough for those backups to kick in.

[root@collabn1 oracle]# ocrconfig -local -manualbackup

collabn1 2017/03/06 17:11:29 /u01/app/12.1.0/grid/cdata/collabn1/backup_20170306_171129.olr 0

collabn1 2016/12/19 15:06:15 /u01/app/12.1.0/grid/cdata/collabn1/backup_20161219_150615.olr 0
[root@collabn1 oracle]# ocrconfig -manualbackup

collabn1 2017/03/06 17:12:21 /u01/app/12.1.0/grid/cdata/collabn-cluster/backup_20170306_171221.ocr 0

collabn2 2016/12/19 15:47:24 /u01/app/12.1.0/grid/cdata/collabn-cluster/backup_20161219_154724.ocr 0

collabn2 2016/12/19 15:47:16 /u01/app/12.1.0/grid/cdata/collabn-cluster/backup_20161219_154716.ocr 0
[root@collabn1 oracle]#


I can run manual backups (the -local is for the OLR) as shown above.

It is important to include these backups in the backup strategy for the filesystem(s) that hold the Grid Infrastructure and RDBMS installations (binaries, configuration files, trace files etc).
.
.
.
Categories: DBA Blogs

undo tablespace

Tom Kyte - Sun, 2017-03-05 12:26
Hi Tom, I have few questions related to undo tablespace. 1)how to startup the database if undo datafile lost and no backup(without undo how uncommitted transactions will be rolled back) 2)In rac if one undo datafile get corrupted,only that i...
Categories: DBA Blogs

Cursor with FOR UPDATE NOWAIT clause UTL_FILE.FOREMOVE ORA-29285: file write error-

Tom Kyte - Sun, 2017-03-05 12:26
Dear Experts, I am having problem with a oracle proc inside package which writes named xxx.txt file on the linux server. cursor with FOR UPDATE NOWAIT clause fetch data from the tables and write data into the file using the UTL_FILE oracle functio...
Categories: DBA Blogs

Bad, Bad data

Kubilay Çilkara - Sat, 2017-03-04 15:10



One should feel really sorry about anyone who will rely on filtering and making a decision based on bad, bad data. It is going to be a bad decision.


This is serious stuff. I read the other day a recent study by IBM which shows that "Bad Data" costs US $3.1 trillion per year!


OK, let's say you don't mind the money and have money to burn, how about the implications of using the bad data? As the article hints these could be misinformation, wrong calculations, bad products, weak decisions mind you these will be weak/wrong 'data' driven decisions. Opportunities can be lost here.


So why all this badness, isn't it preventable? Can't we not do something about it?


Here are some options


1) Data Cleansing: This is a reactive solution where you clean, disambiguate, correct, change the bad data and put the right values in the database after you find the bad data. This is something you do when is too late and you have bad data already. Better late than never. A rather expensive and very time consuming solution. Nevermind the chance that you can still get it wrong. There are tools out there which you can buy and can help you do data cleansing. These tools will 'de-dupe' and correct the bad data up to a point. Of course data cleansing tools alone are  not enough, you will still need those data engineers and data experts who know your data or who can study your data to guide you. Data cleansing is the damage control option. It is a solution hinted in the article as well.


2) Good Database Design: Use constraints! My favourite option. Constraints are key at the design time of the database, do put native database checks and constraints in the database entities and tables to guarantee the entity integrity and referential integrity of your database schema, validate more! Do not just create plain simple database tables, always think of ways to enforce the integrity of your data. Do not rely only on code. Prevent NULLS or empty strings as much as you can at database design time, put unique indexes and logical check constraints inside the database. Use database tools and features you are already paying for in your database license and already available to you, do not re-invent the wheel, validate your data. This way you will prevent the 'creation' of bad data at the source! Take a proactive approach. In projects don't just skip the database constraints and say I will do it in the app or later. You know you will not, chances are you will forget it in most of the cases. Also apps can change, but databases tend to outlast the apps. Look at a primer on how to do Database Design


My modus operandi is option 2, a Good Database Design and data engineering can save you money, a lot of money, don't rush into projects with neglecting or skipping database tasks, engage the data experts, software engineers with the business find out the requirements, talk about them, ask many questions and do data models. Reverse engineer everything in your database, have a look. Know your data! That's the only way to have good, integral and reliable true data, and it will help you and your customers win.

Categories: DBA Blogs

DBMS_COMPARISON: ora-23626: 'schema.indexname' not eligible index error

Tom Kyte - Fri, 2017-03-03 23:46
Hi Tom, I have a very large table with over 850 million rows of data. We are using CDC to extract the data from the source system to a target for publication and etl to a datawarehouse and ODS. I have a requirement to run periodic checks to ensu...
Categories: DBA Blogs

Why won't my table go in memory?

Tom Kyte - Fri, 2017-03-03 23:46
I downloaded the developers days VM with the latest 12.2 installation on it, and wanted to try out the in memory feature of 12c. I've read the documentation and done everything I think I need to, however unfortunately my table doesn't seem to want to...
Categories: DBA Blogs

SGA memory dynamic change

Tom Kyte - Fri, 2017-03-03 23:46
hi Tom, there is a question We have DB with following SGA info: SQL> select * from v$sgainfo; NAME BYTES RESIZEABLE -------------------------------- ---------- ---------- Fixed SGA Size 22...
Categories: DBA Blogs

Script to allow specific users the ability to kill a session

Tom Kyte - Fri, 2017-03-03 23:46
Hi, Is there a script available that can kill a session but will only allow specific users (pre-defined in the script) the capability to perform the kill? If not, how can this be performed?
Categories: DBA Blogs

Compile procedure automatically -- how to avoid cascading invalidations

Tom Kyte - Fri, 2017-03-03 23:46
I've two procedures: A calls B to do something. If I compile B, then A will become invalid. Can I have any setting in the database in order to compile A automatically when B is compiled? Thank you for your sincere help!
Categories: DBA Blogs

SQL Tuning advisor from SQL Developer throws ORA-00942 table or view does not exist error

Tom Kyte - Fri, 2017-03-03 05:26
Hi Tom, I tried to execute SQL tuning advisor for an sql id from back end (PUTTY session). It was keep on executing and never completing. Command: EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'g49yug9c4aar1_tuning'); I created a tunin...
Categories: DBA Blogs

Update parallelization

Tom Kyte - Fri, 2017-03-03 05:26
I need to do a heavy operation on each row of a big table. In order by paralelize this work I've created a table taking the rowid(C1) from the original table and adding a numeric field 'C2'. If C2=0 this row has not been proccessed and if 2C=1, th...
Categories: DBA Blogs

dynamically assign a collection value to variable

Tom Kyte - Fri, 2017-03-03 05:26
emp table create table emp(empno number,ename,varchar2(30),sal number deptno number); dept table create table dept(deptno number,deptname,varchar2(30); declare type ty_test(emp emp%rowtype,dept dept%rowtype); type ty_table is table of ty_tes...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs