DBA Blogs

INSTEAD OF TRIGGERS

Tom Kyte - Sat, 2016-11-26 07:06
INSTEAD OF triggers are executed instead of DML statements that fired it. Is it correct??
Categories: DBA Blogs

Drop MetaData

Tom Kyte - Sat, 2016-11-26 07:06
Hi, I installed 11g(enterprise) on my personal laptop.I Droped the Metadata DBA_OBJECTS,DBA_USERS(my intention is to know DB fuctioning),my Data base working properly.But When I execute Select query(SELECT * FROM dba_objects) getting *ora-10775 ...
Categories: DBA Blogs

Moving table partitions

Tom Kyte - Sat, 2016-11-26 07:06
Tom, Is it possible to move a table partition from one tablespace to another in the same way it is now possible to move a table from one tablespace to another ?
Categories: DBA Blogs

Oracle database In Memory

Tom Kyte - Sat, 2016-11-26 07:06
Hello Tom, I seen a new feature for Oracle 12c : In-Memory database cache. I seen it's a new memory area to organize data in column format for BI/DWH queries. I would like to know how Oracle decides when use the data from In-memory area or from...
Categories: DBA Blogs

Links for 2016-11-25 [del.icio.us]

Categories: DBA Blogs

Reading xml data from nested xml nodes using PL/SQL

Tom Kyte - Fri, 2016-11-25 12:46
Hello Tom, I am new to xml and don't know Java and, unfortunately, do not have time to learn it right now as I need to have a little prototype done of this in a couple of days. I did a lot of reading and reviewed many of the OTN references but I...
Categories: DBA Blogs

Oracle 12.2 new features: long names

Tom Kyte - Fri, 2016-11-25 12:46
I was just going through https://blogs.oracle.com/sql/entry/12_things_developers_will_love and checking the new Oracle 12.2 features, and noticed something about the "Loooooooooooooooong Names". So say if I define a variable as v_table_name use...
Categories: DBA Blogs

Synonyms

Tom Kyte - Fri, 2016-11-25 12:46
Hi, Please Go through the below code SQL> select * from san; --querying tbale NUM ---------- 1 2 SQL> select * from sam; --querying table VAR ---------- santhosh reddy SQL> SE...
Categories: DBA Blogs

Relation

Tom Kyte - Fri, 2016-11-25 12:46
Hi, Is it possible to create a relationship between tables without using foreign key?
Categories: DBA Blogs

Impact of archivelogs on DB during Hot backup and Rman inc0 backup

Tom Kyte - Fri, 2016-11-25 12:46
Hi Tom, Please clarify on below queries. 1. if database is in Hot backup mode (between 2:00 AM to 4:00 AM), the parallel tx at that time will be written to archive logs. And once the hot backup mode finished these logs will be applied to ...
Categories: DBA Blogs

oracle function - rounding

Tom Kyte - Fri, 2016-11-25 12:46
how to round 122.41 value to 122.40. thank you.
Categories: DBA Blogs

Package

Tom Kyte - Fri, 2016-11-25 12:46
Hi, I would like to know the benefits of creating a package with multiple procedures and functions instead of creating them individually?
Categories: DBA Blogs

12.2 New Features -- 3 : Flashback Pluggable Database

Hemant K Chitale - Fri, 2016-11-25 08:35
12.1 allows Point In Time Recovery of a Pluggable Database but not Flashback of an individual PDB.

12.2 now allows Flashback of an individual PDB.   This is easier with a Local Undo Tablespace instead of a Shared Undo Tablespace.

Here is a quick demo (all times in UTC timezone) :

[oracle@HKCORCL ~]$ sqlplus system/Oracle_4U@PDB1

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 25 14:19:06 2016

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

Last Successful login time: Thu Nov 24 2016 01:03:52 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(*) from hr.employees_part;

COUNT(*)
----------
107

SQL> drop table hr.employees_part purge;

Table dropped.

SQL> connect / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL>
SQL> select sysdate, sysdate-oldest_flashback_time
2 from v$flashback_database_log;

SYSDATE SYSDATE-OLDEST_FLASHBACK_TIME
--------- -----------------------------
25-NOV-16 2.36273148

SQL>
SQL> flashback pluggable database pdb1
2 to timestamp sysdate-2/24;

Flashback complete.

SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17:
'/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf
_pdb1undo_d3dgxhbh_.dbf'


SQL> alter pluggable database pdb1 open resetlogs;

Pluggable database altered.

SQL>
SQL> connect system/Oracle_4U@PDB1
Connected.
SQL> select count(*) from hr.employees_part;

COUNT(*)
----------
107

SQL>


(Note : The 12.2 documentation shows the TO TIME clause, it is actually TO TIMESTAMP).
I have done a flashback of PDB1 to a time as of 2hours ago, when the table HR.EMPLOYEES_PART still existed.

Let's look for messages in the alert log.

2016-11-25T14:19:52.992589+00:00
Archived Log entry 11 added for T-1.S-11 ID 0x38800462 LAD:1
2016-11-25T14:19:57.621705+00:00
alter pluggable database pdb1 close
2016-11-25T14:19:57.640353+00:00
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:19:58.885892+00:00
Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 close
2016-11-25T14:26:10.205824+00:00
flashback pluggable database pdb1
to timestamp sysdate-2/24
2016-11-25T14:26:10.627900+00:00
Flashback Restore Start
2016-11-25T14:26:11.513882+00:00
Restore Flashback Pluggable Database PDB1 (3) until change 3536013
Flashback Restore Complete
2016-11-25T14:26:11.707236+00:00
Flashback Media Recovery Start
2016-11-25T14:26:11.718480+00:00
Serial Media Recovery started
2016-11-25T14:26:12.006472+00:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /u04/app/oracle/redo/redo02.log
2016-11-25T14:26:12.283587+00:00
Incomplete Recovery applied until change 3536477 time 11/25/2016 12:26:56
Flashback Media Recovery Complete
Flashback Pluggable Database PDB1 (3) recovered until change 3536477, at 11/25/2016 12:26:56
Completed: flashback pluggable database pdb1
to timestamp sysdate-2/24
2016-11-25T14:26:21.451523+00:00
alter pluggable database pdb1 open
PDB1(3):Autotune of undo retention is turned on.
2016-11-25T14:26:21.659109+00:00
Pdb PDB1 hit error 1113 during open read write (1) and will be closed.
2016-11-25T14:26:21.659410+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_ora_7920.trc:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: '/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf_pdb1undo_d3dgxhbh_.dbf'
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:26:21.804780+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 9: '/u02/app/oracle/oradata/HKCORCL/PDB1/system01.dbf'
ORA-1113 signalled during: alter pluggable database pdb1 open...
2016-11-25T14:26:22.086212+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 10: '/u02/app/oracle/oradata/HKCORCL/PDB1/sysaux01.dbf'
2016-11-25T14:26:22.175778+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 12: '/u02/app/oracle/oradata/HKCORCL/PDB1/users01.dbf'
2016-11-25T14:26:22.270876+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 17: '/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf_pdb1undo_d3dgxhbh_.dbf'
Checker run found 4 new persistent data failures
2016-11-25T14:26:39.804216+00:00
alter pluggable database pdb1 open resetlogs
2016-11-25T14:26:40.377390+00:00
Online datafile 17
Online datafile 12
Online datafile 10
Online datafile 9
2016-11-25T14:26:40.881181+00:00
PDB1(3):Autotune of undo retention is turned on.
PDB1(3):Endian type of dictionary set to little
PDB1(3):[7920] Successfully onlined Undo Tablespace 7.
PDB1(3):Undo initialization finished serial:0 start:868281239 end:868281333 diff:94 ms (0.1 seconds)
PDB1(3):Database Characterset for PDB1 is AL32UTF8
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:26:42.441388+00:00
PDB1(3):Autotune of undo retention is turned on.
2016-11-25T14:26:42.827673+00:00
PDB1(3):Endian type of dictionary set to little
PDB1(3):[7920] Successfully onlined Undo Tablespace 7.
PDB1(3):Undo initialization finished serial:0 start:868283079 end:868283168 diff:89 ms (0.1 seconds)
PDB1(3):Pluggable database PDB1 dictionary check beginning
2016-11-25T14:26:43.706672+00:00
PDB1(3):Pluggable Database PDB1 Dictionary check complete
PDB1(3):Database Characterset for PDB1 is AL32UTF8
2016-11-25T14:26:44.083617+00:00
PDB1(3):Opatch validation is skipped for PDB PDB1 (con_id=0)
PDB1(3):Opening pdb with no Resource Manager plan active
2016-11-25T14:26:45.205147+00:00
Starting control autobackup

Deleted Oracle managed file /u03/app/oracle/fast_recovery_area/HKCORCL/415864F430FE5FFEE053B2D4100A149C/backupset/2016_11_16/o1_mf_nnndf_TAG20161116T024856_d2qldlnv_.bkp
2016-11-25T14:26:46.523130+00:00
Deleted Oracle managed file /u03/app/oracle/fast_recovery_area/HKCORCL/3E09703FB0AF1A7EE053DE4BC40A6C1D/backupset/2016_11_16/o1_mf_nnndf_TAG20161116T024856_d2qlfzqg_.bkp
Control autobackup written to DISK device

handle '/u03/app/oracle/fast_recovery_area/HKCORCL/autobackup/2016_11_25/o1_mf_s_928852005_d3jlk651_.bkp'

Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 open resetlogs


The set of ORA-01113 and ORA-01110 errors are when I tried to open PDB1 without a RESETLOGS.
The OPEN RESETLOGS issued at 2016-11-25T14:26:39.804216+00:00 was successful.
(Note : The ALTER SYSTEM SWITCH LOGFILE wasn't required but I like to archive out the CURRENT redo whenever I make a significant action against the database).

.
.
.

Categories: DBA Blogs

Foreign key reference table

Tom Kyte - Thu, 2016-11-24 18:26
Hi, A table contains a primary key so it referes to another table,I know table name and its primary key column name but i dont know to which table it referes. So is it possible to know the reference table name and foreign key column name?
Categories: DBA Blogs

MV log segments growing to big and to fast

Tom Kyte - Thu, 2016-11-24 18:26
Hi, I have 2 11g DB connected over DB link. - DB1 has master table (MASTER_TBL) and - DB2 materialized view (MV) connected to master table (MASTER_TBL) with fast refresh. MASTER_TBL is: - updated constantly from different sources. - si...
Categories: DBA Blogs

Understanding 'Outline Data' in Oracle Explain Plan

Tom Kyte - Thu, 2016-11-24 18:26
Hi Tom, I am using DBMS_XPLAN.DISPLAY_CURSOR(format ==> 'Advanced') to generate the explain plan. This is generating for me 2 sections of information 'Query Block Name / Object Alias (identified by operation id):' and 'Outline Data'. Could...
Categories: DBA Blogs

INITRANS and LOB index

Tom Kyte - Thu, 2016-11-24 18:26
Hi, Is there a way to specify INITRANS for a LOB index (in my specific case on a VARRAY attribute of an object column - to be even more specific on SDO_ELEM_INFO_ARRAY and SDO_ORDINATES attributes of the column of the MDSYS.SDO_GEOMETRY). Oracle v...
Categories: DBA Blogs

Retrieving new records

Tom Kyte - Thu, 2016-11-24 18:26
Hi, I have a question on data warehousing and am looking for guidance how this can be best achieved using features of the database. Assuming we have a very large table in one instance and are looking to retrieve all the new records from it each eve...
Categories: DBA Blogs

Difference between Role & Privilage

Tom Kyte - Thu, 2016-11-24 18:26
Hi, Could you Explain the exact Difference between the ROLE and Privilage with an example,also explain how they affect the user granted with roles and privilages? Thank you.
Categories: DBA Blogs

ORAAH 2.7 released!

The latest release of Oracle R Advanced Analytics for Hadoop (ORAAH), release 2.7.0, is one of the components of the Oracle Big Data Connectors software suite, an option to the Oracle Big Data...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs