DBA Blogs

about directory location of oracle database files,has it haven one parameter?

Tom Kyte - Wed, 2017-06-07 10:06
SYS@orcl28> col banner for a90 SYS@orcl28> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Rel...
Categories: DBA Blogs

Regarding trigger

Tom Kyte - Wed, 2017-06-07 10:06
Hi, my Question is 1) i have a statement level trigger and row level trigger on the same table but i want to update some date on the same table i finished my update then i select to see the data which has updated but output is no rows select....
Categories: DBA Blogs

direct connect Database links in the exadata environment

Tom Kyte - Wed, 2017-06-07 10:06
Can Dblinks be utilized in the Exadata environment. If not, how are direct connects to external databases done?
Categories: DBA Blogs

Convert loop to bulk collect forall

Tom Kyte - Wed, 2017-06-07 10:06
Here is my code: <code>procedure load_cmpnt_history is cursor c1 is select --+rule h.ndc_emp_id , nvl(upper(h.cmpnt_id),'FAA00000') cmpnt_id , h.compl_dt , nvl(nvl(h.crs_grade,h.cmpl_stat),'OTHER...
Categories: DBA Blogs

Partition Maintenance Induced Concurrency

Tom Kyte - Wed, 2017-06-07 10:06
Hi TOMs, A little back story: Our application incurs many "cursor: pin s wait on x" and "library cache lock" waits during our nightly partition maintenance job. It has been expected behavior and one that we've been living happily with for quit...
Categories: DBA Blogs

Abnormally RAM consuption by sqlldr

Tom Kyte - Wed, 2017-06-07 10:06
Hello, We have problem with sqlldr, we have server with 32 GB RAM and 24 GB swap. While loading big table ( 40 mil records with blobs ), free RAM decreases continuostly, and next swap decreases continuostly too. After some while loading finsih...
Categories: DBA Blogs

12c MultiTenant Posts -- 3 : Restore Individual PB

Hemant K Chitale - Wed, 2017-06-07 05:36
Restoring the single PDB in a Container Database.

$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jun 7 06:34:47 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL12C (DBID=768045447)

RMAN> restore pluggable database newpdb;

Starting restore at 07-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00016 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
channel ORA_DISK_1: restoring datafile 00017 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
channel ORA_DISK_1: restoring datafile 00018 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
channel ORA_DISK_1: restoring datafile 00019 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp tag=TAG20170605T061347
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 07-JUN-17

RMAN> recover pluggable database newpdb;

Starting recover at 07-JUN-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_06/o1_mf_1_16_dmgyksjy_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_17_dmhj6nbk_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp tag=TAG20170605T061438
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_15_dmhoqlq3_.arc thread=1 sequence=15
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_15_dmhoqlq3_.arc RECID=4 STAMP=946017330
media recovery complete, elapsed time: 00:00:11
Finished recover at 07-JUN-17

RMAN> alter pluggable database newpdb open;

Statement processed

RMAN>


I had connected to the root to restore the PDB.
.
.

.
Categories: DBA Blogs

Schedule a job to migrate the data from SQL Server to Oracle

Tom Kyte - Tue, 2017-06-06 15:46
Hello Experts, Is there any way with which I can schedule a job to migrate a data from MS SQL server to Oracle database on daily basis? Please suggest. Thanks, Ankit
Categories: DBA Blogs

Asynchronous Global Indexing does not work as expected in 12c in a Database upgraded from 11g to 12c

Tom Kyte - Tue, 2017-06-06 15:46
We observed following. 1) 11g database is upgraded to 12c. Set the COMPATIBLE parameter to 12c version. Restart the DB. Created a table, partitioned the table and insert data into that table. Then dropped the partition. This works as expected in ...
Categories: DBA Blogs

Timestamp with ff3 and ff7 issue

Tom Kyte - Tue, 2017-06-06 15:46
I have string field '2017/06/02 17:51:12.0000000' which get converted into timestamp with formatting 'YYYY/MM/DD HH24:MI:SS.FF3' correctly in 11g I am having issue of "ora-01830: date format picture ends before converting entire input string" on ...
Categories: DBA Blogs

Adding Dinamic Nulls to perform Union

Tom Kyte - Tue, 2017-06-06 15:46
Hello Sir, it's the first time I write to you! I've the necessity of listing per table the age of data (oldest data thet we have in the table). i'm trying to generate a dinamic sqls for each table of the schema like: <code>SELECT'ALL_OBJECT...
Categories: DBA Blogs

Solaris x86 cold Backup opened on Solaris Sparc with all DB versions same.

Tom Kyte - Mon, 2017-06-05 21:26
I am trying to minimize downtime for the move of a physical standby server to a new location across country and was hoping to set up an x86, 12.1.0.2 Virtual server and storage at the receiving location as another standby database and then just physi...
Categories: DBA Blogs

How to parse an array to update a table of records

Tom Kyte - Mon, 2017-06-05 21:26
I am going to receive an array of account numbers from a .NET program, together with another array of statuses (1 for each account number) and I need to parse these arrays and update the status of those accounts in a table on the database: Eg: ...
Categories: DBA Blogs

Parallel union all, rollup, cube degradation while insert (11.2.0.4)

Tom Kyte - Mon, 2017-06-05 21:26
the problem is that query like this:(table creation script at the end) <code> insert /*+no_append parallel(8)*/ into tmp_px0 select count(distinct rn) from tmp_px1 union all select count(distinct rn) from tmp_px2 union all select ...
Categories: DBA Blogs

Global Temporary Table - Commit issue over database link

Tom Kyte - Mon, 2017-06-05 21:26
<code>Below SQL gives 696 rows which I am processing in BULK collect 300 SELECT ROWID FROM TB_EMPLOYEE_TEST WHERE DT <= TO_DATE('01/01/2008', 'MM/DD/YYYY'); Global temporary table is created with ON COMMIT DELETE ROWS; still data in global tab...
Categories: DBA Blogs

How to create dynamic table type and variable to insert bulk data

Tom Kyte - Mon, 2017-06-05 21:26
Hi Tom, I have a scenario in which I need to pass the table name as input variable and create a table type based on that table and insert the data into input table using bulk collect dynamically.I have tried creating the table type and insertion ...
Categories: DBA Blogs

Log Buffer #512: A Carnival of the Vanities for DBAs

Pythian Group - Mon, 2017-06-05 09:50

This Log Buffer Edition covers Oracle, SQL Server and MySQL.

Oracle:

Upgrade Existing TDE to Use New Unified Key Management in 12c Upgraded Database (non-cdb)

Instrumentation … not just for debugging

12.2 Index Deferred Invalidation (Atomica)

Collation in 12cR2 – when AA equals Å (or not?)

Configuring Oracle Traffic Director 12c with WebGate

SQL Server:

PowerShell Scripting to Replicate Cloud & SaaS Data to SQL Server

Comparing and Synchronizing Two Folders with Azure

How to Handle Hybrid Cloud Database Security with SQL Secure

Use T-SQL to Find Folders Using the Most Disk Space

Generate charts rapidly with SQL Server using R and T-SQL

MySQL:

Hybrid Data Encryption by Example using MySQL Enterprise Edition

Announcing Scotch Box 3.0 and Scotch Box Pro

Testing MySQL 8.0 – let me create a ton of undo files

MariaDB 10.1.24 and Connector/C 2.3.3 now available

Migration from MySQL Master-Slave pair to MySQL InnoDB Cluster: howto

Categories: DBA Blogs

12c MultiTenant Posts -- 2 : Backup individual PDB

Hemant K Chitale - Mon, 2017-06-05 05:16
In the 12c MultiTenant architecture, you can backup (and restore) individual PDBs.  However, you must also backup the CDB (Root) !  You cannot restore a PDB as a "standalone" database --- it has to be restored back to the CDB.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8798312 bytes
Variable Size 343936920 bytes
Database Buffers 478150656 bytes
Redo Buffers 7974912 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter pluggable database newpdb open;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$
$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jun 5 06:10:17 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL12C (DBID=768045447)

RMAN> backup database root;

Starting backup at 05-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=270 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl12c/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl12c/sysaux01.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/orcl12c/undotbs2.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-JUN-17
channel ORA_DISK_1: finished piece 1 at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061033_dmbcjttq_.bkp tag=TAG20170605T061033 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 05-JUN-17

Starting Control File and SPFILE Autobackup at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_05/o1_mf_s_945843079_dmbcl8fm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUN-17

RMAN>
RMAN> backup pluggable database newpdb;

Starting backup at 05-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
input datafile file number=00016 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
input datafile file number=00018 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
input datafile file number=00019 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
channel ORA_DISK_1: starting piece 1 at 05-JUN-17
channel ORA_DISK_1: finished piece 1 at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp tag=TAG20170605T061347 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 05-JUN-17

Starting Control File and SPFILE Autobackup at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_05/o1_mf_s_945843252_dmbcqo6t_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUN-17

RMAN>
RMAN> backup archivelog all delete input;

Starting backup at 05-JUN-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=1 STAMP=945843278
channel ORA_DISK_1: starting piece 1 at 05-JUN-17
channel ORA_DISK_1: finished piece 1 at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp tag=TAG20170605T061438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_05/o1_mf_1_15_dmbcrfq1_.arc RECID=1 STAMP=945843278
Finished backup at 05-JUN-17

Starting Control File and SPFILE Autobackup at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_05/o1_mf_s_945843280_dmbcrjdg_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUN-17

RMAN>


Thus, a PDB in a MultiTenant environment can be backed-up but the Root must always be backed up.
.
.
.

Categories: DBA Blogs

Reading rows ahead of a particular value

Tom Kyte - Mon, 2017-06-05 03:06
Hello, I have data in a single column of a table. create table t (ctext varchar2(255)); insert into t values ('2017-06-04 17:17-4qxzmh15zwv35-GSAAPP'); insert into t values (' PUBLICATION_CELLS '); insert into t values ('2017-06-04 1...
Categories: DBA Blogs

cursor within cursor using forall is possible?

Tom Kyte - Mon, 2017-06-05 03:06
HI TOM, GOOD DAY! cursor within cursor using forall is possible? need to fetch records from all_objects in first cursor and then need to delete records using object name in where clause in FORALL
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs