DBA Blogs

Cross platform migration using GG

Tom Kyte - Sat, 2017-06-24 23:46
Goldengate is primarily sold as an Replication software, I understand. Can you clarify whether GG can be used to migrate from one database platform to Oracle. Say, MSSQL for example? Or is it required to use SQL Dev to convert the metadata from...
Categories: DBA Blogs

Package Calling Error

Tom Kyte - Sat, 2017-06-24 05:45
Hi, I created a package Order_headers with two procedures price and quantity in Tan Schema .called them in other procedure like this tan.order_headers.price; but i got Error like PLS-00302: component Component 'order_headers' must be declared. but...
Categories: DBA Blogs

based on multiple packages and procedure

Tom Kyte - Sat, 2017-06-24 05:45
Can we access the procedure in a package outside the package using another package? i.e. i need to access a package procedure outside of another package (i need to call from 2nd package to access the first package procedure)
Categories: DBA Blogs

Using ZFS compression for all database files

Tom Kyte - Sat, 2017-06-24 05:45
Hi Tom, I am hoping for some expert advice on the above, We have a T5 server using SAN storage which is allocated by ZFS. We have Solaris 11 OS and Oracle Database 12c with single instances (no RAC) and we do not use ASM, our database is limi...
Categories: DBA Blogs

SQR with 077 umask creates file with 611 permissions

Bobby Durrett's DBA Blog - Fri, 2017-06-23 11:18

I ran across this strange situation. An SQR opened a new data file for output and created the file with 611 permissions. We needed group read access so this caused a problem. I knew that our login script for the PeopleSoft Unix user set umask to 022 but that without running the login scripts the mask is 077. So, my first thought was that we had started the process scheduler without running the login scripts and the mask was 077. But, why would the file that the SQR created be 611 permissions and not 600? The 077 mask should get rid of all the group and others bits. I built a simple test case to show that the SQR creates the file with 611 permissions with a 077 mask.

Here is the test SQR:

begin-report
  Let $Unix_Cmd = 'umask'
  Call System Using $Unix_Cmd #Status
  Let $Unix_Cmd = 'rm /tmp/bobby/bobby.txt'
  Call System Using $Unix_Cmd #Status
  let $testfile = '/tmp/bobby/bobby.txt'
  open $testfile as 1 for-writing  record=1500:fixed
  close 1
  Let $Unix_Cmd = 'ls -l /tmp/bobby/bobby.txt'
  Call System Using $Unix_Cmd #Status
end-report

Here is its output:

SQR for PeopleSoft V8.53.05
077
-rw---x--x   1 psoft      psoft            0 Jun 23 10:54 /tmp/bobby/bobby.txt

Notice the execute bits for group and others.  Why were they not masked out? Also, the default permissions to create a file is 644. So, creating a new file should not set the execute bits at all no matter what mask you are using.

I created a Korn shell script to do the same thing as the SQR:

umask
rm /tmp/bobby/bobby.txt
touch /tmp/bobby/bobby.txt
ls -l /tmp/bobby/bobby.txt

Here is its output:

077
-rw-------   1 psoft      psoft            0 Jun 23 10:58 /tmp/bobby/bobby.txt

Notice that there are no group and others bits which is what I expected with a 077 mask. I tried searching the Internet for SQR and 611 permissions but could not find anything.

As it turns out, we did start the process scheduler with umask 077 so I just modified the script that started it to set umask 022 and that resolved the problem. Here is the output from my test SQR with umask 022:

SQR for PeopleSoft V8.53.05
022
-rw-r--r--   1 psoft      psoft            0 Jun 23 11:01 /tmp/bobby/bobby.txt

This is what we wanted and of course the Korn shell script does the same thing as it should.

022
-rw-r--r--   1 psoft      psoft            0 Jun 23 11:02 /tmp/bobby/bobby.txt

Seems very odd to me. Anyway, I hope that this post helps someone.

This was on HP-UX 11.31 and PeopleTools 8.53.05

Bobby

Categories: DBA Blogs

Partner Webcast – Delivering Contextual Collaboration and Engagement with Oracle Content and ...

The key to digital business transformation are the productivity tools and services that employees rely on to deliver business value. However, the digital workplace of today requires a new set of...

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

where are sql tuning sets stored and how can you tell how much space a specific one or all of them are using?

Tom Kyte - Thu, 2017-06-22 16:46
It takes a certain amount of database access to be able to create a sql tuning set. It is more than a little fuzzy to me what kind of object they are in the database. My question though is a little more focused than that ( maybe ha ha ). How can...
Categories: DBA Blogs

Roles and Privileges

Tom Kyte - Thu, 2017-06-22 16:46
Hi, Good Day ! Today we have a structured approach to access control, based on use of schemas (to group together database objects), and assigning specific permissions to roles. 2 roles : - USER_ROLE = CONNECT + SELECT/INSERT/UPDATE/DELETE...
Categories: DBA Blogs

Unrolling loop speeds up program

Bobby Durrett's DBA Blog - Thu, 2017-06-22 15:55

This is a follow-up to my earlier post about the assembly language book that I am working through. I have struggled to speed up a program using something that the book recommends, unrolling a loop. I think I have finally found an example where unrolling a loop speeds up a program so I wanted to share it.

I am working on Chapter 17 Exercise 2 of the book which asks you to write a program to find the longest common substring from two strings. I choose an inefficient and simple way to find the common substring and tried to speed the program up without changing the algorithm.

Here is the C version on GitHub: url

The core of the program is three loops. The outer loop tries each character in string 1 as the start of the substring. The middle loop tries each character in string 2 as the start of the substring. The inner loop advances through both strings until it finds the end of the common substring.

The C version ran in 27.2 seconds.

I built an assembly version that uses registers for most of the variables and it ran in about 11.7 seconds. It has the same three loops. Assembly register version: url

I tried to improve on the 11.7 seconds by unrolling each of the three loops. Unrolling the outer and inner loops resulted in no improvement in runtime. I was about to give up but finally decided to try unrolling the middle loop and it caused the program to run in 10.2 seconds. Very cool. Assembly unrolled middle loop version: url

I had to figure out how to use %rep, %assign, and how to have a label that I based on a nasm variable such as .skipif %+ i.

Kind of fun. I realize that this is off topic for my “DBA Blog” but it is something I’m playing with so I thought I would throw it out there. It doesn’t hurt a DBA to know some low-level computer science, even if you are not using it directly in your job. Anyway, it was a bit of a struggle to come up with an example that was faster with the loop unrolling and I think that I have found one.

Bobby

Categories: DBA Blogs

DENSE_RANK - AGGREGATE function

Tom Kyte - Wed, 2017-06-21 22:26
Below is the the employee table, EID NAME SALARY COMM_PCT 100 Steven 24000 101 Neena 17000 0.6 102 Lex 17000 0.8 145 John 14000 0.4 If I do the below select to employee table I get the output as 3. SELECT DENSE_RANK(17000,0.6) W...
Categories: DBA Blogs

SQL execution plan - access predicates determination

Tom Kyte - Wed, 2017-06-21 22:26
Hello Tom, I know Tom has retired. But glad that a group of Oracle expertise help to keep operation of AskTom. It is indeed very helpful. I was facing a SQL plan issue in an SAP/Oracle Environment. The SQL is: <code> SELECT DISTINCT "M...
Categories: DBA Blogs

EM13cR2 Installation Failing on BI Publisher Configuration

Pythian Group - Wed, 2017-06-21 16:07

This is going to be a short post as there wasn’t much information on this type of failure when searching for it. Also, the IP’s are changed due to security reasons

I was working on an Oracle EM13cR2 installation and when the installation reached 78%, it failed on the BI Publisher configuration. First, I looked at the CfmLogger log.

INFO: oracle.sysman.top.oms:WLST offline debugging is in the file: /u01/app/oracle/middleware/cfgtoollogs/bip/bipca_20170613152059.log.wlst_20170613152122.trc
INFO: oracle.sysman.top.oms:Securing BI Publisher to work with Enterprise Manager
INFO: oracle.sysman.top.oms:Locking Enterprise Manager ...
INFO: oracle.sysman.top.oms:OMS Console is locked. Access the console over HTTPS ports.
INFO: oracle.sysman.top.oms:BI Publisher is locked. Access BI Publisher over HTTPS ports.
INFO: oracle.sysman.top.oms:Restart OMS.
INFO: oracle.sysman.top.oms:Restarting Enterprise Manager
INFO: oracle.sysman.top.oms:Stopping Enterprise Manager, this can take some time ...
INFO: oracle.sysman.top.oms:Starting BI Publisher ...
INFO: oracle.sysman.top.oms:Failed to start Enterprise Manager. Diagnostic code 1.
INFO: oracle.sysman.top.oms:See log at /u01/app/oracle/middleware/cfgtoollogs/bip/bipca_20170613152059.log for details.
INFO: oracle.sysman.top.oms:Error extending domain
INFO: oracle.sysman.top.oms:See log at /u01/app/oracle/middleware/cfgtoollogs/bip/bipca_20170613152059.log for details.
INFO: oracle.sysman.top.oms:java.lang.Exception: See log at /u01/app/oracle/middleware/cfgtoollogs/bip/bipca_20170613152059.log for details.
INFO: oracle.sysman.top.oms: at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.extendDomainWithBIP(BIPCA.java:3401)
INFO: oracle.sysman.top.oms: at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.main(BIPCA.java:1973)

When this led me to the bipca.log , it referenced the details below (which really didn’t tell me much):

[2017-05-09T21:55:17.585-07:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: OracleEntMgr] [nwaddr: 192.168.51.10] [tid: 1] [userId: oracle] [ecid: a4412d2a-def5-49d8-ac27-1046c7243de9-00000001,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: handleFatalError] Error extending domain
[2017-05-09T21:55:17.586-07:00] [sysman] [ERROR] [] [oracle.sysman.bipca] [host: OracleEntMgr] [nwaddr: 192.168.51.10] [tid: 1] [userId: oracle] [ecid: a4412d2a-def5-49d8-ac27-1046c7243de9-00000001,0] [SRC_CLASS: oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA] [SRC_METHOD: handleFatalError] Fatal error:[[
java.lang.Exception: See log at /u01/app/oracle/middleware/cfgtoollogs/bip/bipca_20170509214828.log for details.
at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.extendDomainWithBIP(BIPCA.java:3401)
at oracle.sysman.sdkImpl.core.ip.model.bipca.BIPCA.main(BIPCA.java:1973)

A review of the bipca trace file didn’t tell me much about why the configuration was failing:

2017-05-24 09:26:51,250 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - Selected DB vendor: Oracle
2017-05-24 09:26:51,250 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - adding normal datasource: emgc-mgmt_view-pool
2017-05-24 09:26:51,250 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - datasource: emgc-mgmt_view-pool component name: null
2017-05-24 09:26:51,251 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - Found normal datasource: emgc-sysman-pool
2017-05-24 09:26:51,253 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - Decomposing url: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521)))(CONNECT_DATA=(SID=emrep))) for driver: oracle.jdbc.OracleDriver
2017-05-24 09:26:51,253 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - There is an unsupported jdbc URL or JDBC driver is detected. See Stack trace:
java.lang.NullPointerException
at com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper.decomposeURL(DatasourceXBeanAspectHelper.java:316)
at java.lang.reflect.Method.invoke(Method.java:606)
at weblogic.WLST.main(WLST.java:29)
2017-05-24 09:26:51,253 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - Found normal datasource: jvmd-ds
2017-05-24 09:26:51,255 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - Decomposing url: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=dbhost)(PORT=1521)))(CONNECT_DATA=(SID=emrep))) for driver: oracle.jdbc.OracleDriver
2017-05-24 09:26:51,255 FINE [1] com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper - There is an unsupported jdbc URL or JDBC driver is detected. See Stack trace:
java.lang.NullPointerException
at com.oracle.cie.domain.jdbc.DatasourceXBeanAspectHelper.decomposeURL(DatasourceXBeanAspectHelper.java:316)
at org.python.pycode._pyx95.updateDomain$21(/tmp/WLSTOfflineIni822569357732826272.py:103)
at org.python.pycode._pyx95.call_function(/tmp/WLSTOfflineIni822569357732826272.py)
at org.python.core.PyTableCode.call(Unknown Source)
at org.python.core.PyTableCode.call(Unknown Source)
at org.python.core.PyFunction.__call__(Unknown Source)

After reading for a while and working back and forth with Oracle, we identified the problem. The host wasn’t resolving via nslookup. Though we were able to ping it, what nslookup does is query a DNS domain nameserver to lookup and find IP address and it , so what we had to do was fix this :

[oracle@dbhost ~]# nslookup dbhost
Server: 128.101.1.256
Address: 128.101.1.256#53

Name: dbhost
Address: 128.101.1.1

Once we fixed this, we were able to install EM13cR2 successfully.

Note:This was originally published on rene-ace.com

Categories: DBA Blogs

Alert message on Listener Log growth

Tom Kyte - Wed, 2017-06-21 04:06
Hi, Need suggestion to get an Alert message over the Listener Log file's growth, now each file gets 10M. We want an alert message over 1GB of the combined listener log files. Regards Binoj
Categories: DBA Blogs

Oracle Linux 7.3 Production Server setup for OEM 13c and repository database.

Tom Kyte - Wed, 2017-06-21 04:06
I am planning to install Oracle Linux 7.3 on VMware. This server will be used to host our oracle enterprise manager 13c and its repository database. I have no experience as a server administrator. Currently I am in a situation were I have to setu...
Categories: DBA Blogs

How do I find out what username the database is passing through when connecting using Windows Authentication?

Tom Kyte - Wed, 2017-06-21 04:06
Hi, I'm having trouble using Windows authentication with my database accounts. I have a front-end application that creates a user account based on what their OS username is, OPS$Username, disregarding the domain name. Logging into the application ...
Categories: DBA Blogs

DBMS_JOB Poll Interval

Tom Kyte - Wed, 2017-06-21 04:06
We recently upgraded databases from 11.2.0.4 to 12.1.0.2. With that upgrade, we noticed that after submitted jobs via dbms_job, the procedures can take anywhere between 5-30 seconds in order to actually start to execute after committing. A sample scr...
Categories: DBA Blogs

How to exchange index partition, if there is such a thing

Tom Kyte - Wed, 2017-06-21 04:06
I have a global partition on an INDEX as below (the table was partitioned on different keys) Fiscal year, period_cd, and 2 more keys as shown <code>CREATE INDEX kr.ufy_idx_px ON kr.LD_LDGR_ENTR_T ( u_fy, u_prd_cd, c_cd, obj_cd) GLOBAL PAR...
Categories: DBA Blogs

12c MultiTenant Posts -- 4 : Relocate Individual PDB using RMAN

Hemant K Chitale - Tue, 2017-06-20 22:29
Given the current location of a PDB :

SQL> alter session set container=NEWPDB;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7
f8go_.dbf

/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7
f8hf_.dbf

/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dk
j7f8hg_.dbf

/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbc
p0wz_.dbf

FILE_NAME
--------------------------------------------------------------------------------


SQL>


I can use RMAN to relocate it. First I take an Image Copy Backup to the new location

RMAN> backup as copy pluggable database newpdb format '/u03/oradata/NEWPDB/%U';

Starting backup at 20-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2 tag=TAG20170620T231338 RECID=4 STAMP=947200428
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di tag=TAG20170620T231338 RECID=5 STAMP=947200441
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1 tag=TAG20170620T231338 RECID=6 STAMP=947200451
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4 tag=TAG20170620T231338 RECID=7 STAMP=947200454
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 20-JUN-17

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

RMAN>


Then I switch the database file pointers to the new location.

RMAN> alter pluggable database newpdb close;

Statement processed

RMAN> switch pluggable database newpdb to copy;

datafile 16 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di"
datafile 17 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2"
datafile 18 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1"
datafile 19 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4"

RMAN> recover pluggable database newpdb;

Starting recover at 20-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 20-JUN-17

RMAN> alter pluggable database newpdb open;

Statement processed

RMAN>


I can now verify the new location for the database files.

 
SQL> alter session set container=NEWPDB;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4

SQL>


The datafiles at the old location still remain and can be manually deleted later as they are no longer part of the database.  Note that those datafiles are still registered by RMAN as COPY

RMAN> list copy of pluggable database newpdb;

using target database control file instead of recovery catalog
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - --------------- ---------- --------------- ------
8 16 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
Container ID: 4, PDB Name: NEWPDB

9 17 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
Container ID: 4, PDB Name: NEWPDB

10 18 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
Container ID: 4, PDB Name: NEWPDB

11 19 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
Container ID: 4, PDB Name: NEWPDB


RMAN>


If I manually delete the old location files, I'd need to also delete them from the RMAN Registry.  Alternatively, I can directly delete them from RMAN.

RMAN> delete copy of pluggable database newpdb;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=278 device type=DISK
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - --------------- ---------- --------------- ------
8 16 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
Container ID: 4, PDB Name: NEWPDB

9 17 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
Container ID: 4, PDB Name: NEWPDB

10 18 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
Container ID: 4, PDB Name: NEWPDB

11 19 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
Container ID: 4, PDB Name: NEWPDB


Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf RECID=8 STAMP=947200522
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf RECID=9 STAMP=947200522
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf RECID=10 STAMP=947200522
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf RECID=11 STAMP=947200522
Deleted 4 objects


RMAN>


I still need to relocate the TEMP Tablespace Tempfile.

SQL> alter session set container=NEWPDB;

Session altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_temp_dkj7f8
hg_.dbf


SQL> alter tablespace temp add tempfile '/u03/oradata/NEWPDB/temp01.dbf' size 100M;

Tablespace altered.

SQL>
SQL> alter tablespace temp drop tempfile '/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_temp_dkj7f8hg_.dbf';

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u03/oradata/NEWPDB/temp01.dbf

SQL>


So, I used the same Image Copy method we'd use in 11g databases to relocate a 12c Pluggable Database when within the same server and storage.

To verify that I can access the PDB in the new location :

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
OBJ_LIST
HKC_STORE_FILE
T

SQL> insert into obj_list select * from obj_list;

72641 rows created.

SQL> commit;

Commit complete.

SQL>


.
.

Categories: DBA Blogs

How to update a materialized view directly

Tom Kyte - Tue, 2017-06-20 09:46
Hi Can we update data in Materialized view directly using update statement. If yes,will that updated to table as well. What if there are more than one table in view? Please help me on this.
Categories: DBA Blogs

Oracle Text ctxrule - MATCHES: special word work and not work?

Tom Kyte - Tue, 2017-06-20 09:46
Hi all, I use CTXRULE to classify some text, but AB&B work and AT&T not work. Please support me on this: Here is my search text <code> SQL> select text from test_lexer_special; TEXT -------------------- AB&B {AT&T} AT&T SQL></code> ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs