DBA Blogs

BIG NEWS: I’ve Been Selected On “Journey To Mars” Program !!

Richard Foote - Sat, 2018-03-31 17:56
After many months of nervous waiting, knowing that my chances were extremely slim at best, I have just received my confirmation letter from NASA saying that I’ve been one of the lucky ones selected to participate in the “Journey To Mars” program !! I’m soooo damn excited !!! Planning is now under way for what […]
Categories: DBA Blogs

CPU utilization

Tom Kyte - Fri, 2018-03-30 08:46
How can I reduce CPU utilization of Oracle database?
Categories: DBA Blogs

Oracle DBAs and Meltdown & Spectre (M&S) vulnerability Patch

Pakistan's First Oracle Blog - Thu, 2018-03-29 21:01
So what Oracle DBAs need to do regarding Meltdown & Spectre (M&S) vulnerability patch? 



Well, they should ask the sysadmins to install the patch to the affected versions. They need to get a maintenance window for that. They need to take full backup of Oracle infrastructure and databases before that patching and they should get some baseline of OS metrics to compare it with post patch status of the system. 

Not much there is to do for Oracle DBAs in this regard as this vulnerability is in hardware and is mainly related to sysadmins. Nonetheless, Oracle DBAs should avail this opportunity and install latest CPU. 

The vulnerability is in the chipset itself, unrelated to OS. These vulnerabilities exist at the hardware layer and provide attackers with a way to essentially read the memory used by other processes. Because of the nature of this exploit, the database itself is not currently thought to be a vector in the risk, in-fact the real "fix" for this issue relies on fixing architecture at the chip-set level. 

To mitigate the risk currently without replacing your chips, OS vendors are releasing patches that fundamentally change interactions with memory structures by processes. This is why we're seeing in "Addendum to the January 2018 CPU Advisory for Spectre and Meltdown (Doc ID 2347948.1)" Oracle is releasing patches for Oracle VM (virtual machines are particularly susceptible to this exploit as one "VM" can read the memory processes of another, making this particularly deadly to cloud computing) and Oracle Enterprise Linux. We do understand that Oracle is exploring the possibility that there may be additional patches needed for Oracle Enterprise and Standard edition DBs themselves.


Only for Exadata, It is needed to apply the latest Exadata 12.2.1.1.6 software bundle (the full version number is 12.2.1.1.6.180125.1). Spectre / Meltdown patches are included into it.


The best course of action regarding this would be to get a word from Oracle support for any database related patch. 

Categories: DBA Blogs

Move a Datafile from one ASM Diskgroup to Another Diskgroup

Pakistan's First Oracle Blog - Thu, 2018-03-29 20:49
Following are steps to move a datafile from one ASM diskgroup to another diskgroup in the same ASM instance:




For this example, let's suppose the full path of datafile to be moved is +DATA/test/datafile/test.22.121357823 and datafile number is 11.

Step 1: From RMAN, put datafile 11 offline:

SQL "ALTER DATABASE DATAFILE ''+DATA/test/datafile/test.22.121357823'' OFFLINE";

Step 2: Backup Datafile 11 to Copy using RMAN:

$ rman target /
BACKUP AS COPY DATAFILE 11 FORMAT '+DATA_NEW';

--- Make note the path and name of the generated datafile copy.

Step 3: From RMAN, switch datafile 11 to copy:

SWITCH DATAFILE "+DATA/test/datafile/test.22.121357823" TO COPY;

Step 4: From RMAN, Recover Datafile 11:

RECOVER DATAFILE 11;

Step 5: From RMAN, put datafiles online:

SQL "ALTER DATABASE DATAFILE ''+DATA_NEW/'' ONLINE";

Step 6: From SQLPlus, verify if datafile 11 was correctly switched and was online:

sqlplus / as sysdba
SQL> select file_id,file_name,online_status from dba_data_files where file_id in (11);
Categories: DBA Blogs

SELECT INTO clause variable initialization when Too Many Rows exception raised

Tom Kyte - Wed, 2018-03-28 20:06
Hi, I created a Table and inserted two rows with duplicate values in 2 columns. I written an anonymous block to (SELECT INTO)fetch the data into 3 variables by passing 2 Values in WHERE Clause, but the values in these columns are not unique so ...
Categories: DBA Blogs

Using resource manager group to restrict DOP

Tom Kyte - Wed, 2018-03-28 20:06
Hi, We are planning to user Oracle Resource Manager Group to restrict DOP(degree of parallelism) to some fixed number in our database. We have around 10 schemas and we need to restrict DOP in those schemas. There is definitely benefit by using the...
Categories: DBA Blogs

Filling nulls in hierarchical query

Tom Kyte - Wed, 2018-03-28 20:06
Recently in the Oracle magazine of nov-06 you explained how to fill up null values with the previous non-null value of a column. I got a similar problem, but on hierachical query. I need to fill up null columns from its parent non-null value instea...
Categories: DBA Blogs

Log Switches and log file sync

Tom Kyte - Wed, 2018-03-28 01:46
Hello Tom, I currently have 6 log groups of 1 2GB member. These six groups are on the same mount point in a virtual environment. There is a log switch every hour. During peak times, however, the log file sync wait seems to be an issue for us. The ...
Categories: DBA Blogs

Shrink SYSAUX

Tom Kyte - Wed, 2018-03-28 01:46
I shrinked a tablespace SYSAUX but I have got segments towards the end of the datafile, the resize command will failed. I checked type segment names and it type this query: select file_id, block_id, block_id + blocks - 1 end_block,...
Categories: DBA Blogs

system statistics

Tom Kyte - Wed, 2018-03-28 01:46
Hi Tom, Recently with the help of your valuable answers to my questions regarding system statistics, I have put "system statsitics" into production system after testing and results are wonderful. It saved my more than 50% of execution time. My que...
Categories: DBA Blogs

Materialized view refresh in case of DDL on base table, like truncate, drop and recreate

Tom Kyte - Wed, 2018-03-28 01:46
Hi Tom, I was trying to prepare materialized view against some table which are periodically dropped and recreated. I did not find an option to automatically refresh the materialized view in this case. Refresh FAST ON COMMIT is not working, and REF...
Categories: DBA Blogs

Query transformation - Remote database

Tom Kyte - Wed, 2018-03-28 01:46
Hi, I have a query that fetches data from a remote database and this data is not what i was expecting (less data or no data). I did try to understand what is causing the issue and noticed the query was being transformed while run on the remote db....
Categories: DBA Blogs

Richard Foote Consulting Seminars At Your Workplace !!

Richard Foote - Wed, 2018-03-28 00:04
If you have 6 or more people in you organisation who would be interested in attending a seminar, please contact me at richard@richardfooteconsulting.com. I currently run my highly acclaimed “Oracle Indexing Internals and Best Practices” seminar as public events, which has now been run in some 18 countries. I’m also at work on developing an […]
Categories: DBA Blogs

Automating procedure testing which returns cursors

Tom Kyte - Tue, 2018-03-27 07:26
Hi Team, In my project we have testing suite built for PL/SQL and it tests basic test cases like input/output comparision. So in the tool if I configure procedure P1 (p_in IN number, P_out OUT number) with input and and expected output, the too...
Categories: DBA Blogs

Extracting 500 Million Rows from Oracle DB -- Pro*C OR PL/SQL UTL_FILE? Deep Dive into WHY?

Tom Kyte - Tue, 2018-03-27 07:26
Hi Tom, The question is simple. I need 500 Mil rows to offload from oracle, what should I choose to to develop an application with ? PRO*C or PL/SQL UTL_FILE? and why ? I have gone through the debated topics on which approach between PRO*C o...
Categories: DBA Blogs

Extracting execution plan for PL/SQL block

Tom Kyte - Tue, 2018-03-27 07:26
<code>Hi Tom, Need help extracting execution plan for any anonymous PL/SQL block . I am able to extract execution plan for SQL using explain plan but it doesn't work for pl/sql block EXPLAIN PLAN FOR select * from dual; select *...
Categories: DBA Blogs

Difference in parallel hints.

Tom Kyte - Tue, 2018-03-27 07:26
Hi Tom, What is difference in following two scenarios? I am using parallel hints like - 1. select /*+ parallel(e,4) parallel(d,4)*/ e.eid,d.did from emp e, dept d where e.did = d.did; 2. select /*+ parallel(4)*/ e.eid,d.did from e...
Categories: DBA Blogs

customizing the SQLprompt

Tom Kyte - Tue, 2018-03-27 07:26
Hello Tom, I notice in the Oracle Magazine that you have your SQL prompt displaying "user@instance". Please tell me how you accomplish this. I can get it to display the username OR the instance name but not both. Thanks! -Lisa
Categories: DBA Blogs

Oracle Function returning multiple values

Tom Kyte - Mon, 2018-03-26 13:06
Can Oracle function return multiple values..? if yes, could you give an example for it?
Categories: DBA Blogs

How to identify database export files

Tom Kyte - Mon, 2018-03-26 13:06
Any tools/mechanisms to identify database export files taken using exp and expdp, so that i can do a scan on the desktops in my organisation to find who all have kept the export files in their desktops.
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs