PDB ADMIN ACCOUNT DOESN'T HAVE PERMISSIONS TO CREATE/DROP by DEFAULT unless explicitly granted:
********************* ******************************
pdborcl1 is the user created while creating PDB via DBCA:
********************* ******************************
[oracle@Linux03 pdborcl3]$ sqlplus pdborcl1/oracle@pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 14:36:23 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Mar 07 2016 11:21:22 -06:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select table_name from tabs;
no rows selected
SQL> create table test (name varchar2(20));
create table test (name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> exit
***************************** ******************************
NOW LOGIN AS USER pdborcl1_usr2 who has a table created in his user
***************************** ******************************
[oracle@Linux03 pdborcl3]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 14:39:21 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Mar 07 2016 11:46:08 -06:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select table_name from tabs;
TABLE_NAME
--------------------------------------------------------------------------------
TEST
I will be concentrating mostly on 12c database on this site. you can visit my other blog for knowledge on previous versions of oracle database.
Showing posts with label pdbs. Show all posts
Showing posts with label pdbs. Show all posts
Thursday, March 24, 2016
Tuesday, March 22, 2016
OPEN ALL PLUGGABLE DATABASES AT ONCE
[oracle@Linux03 Desktop]$ sqlplus /"As sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 22 14:47:02 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 788529152 bytes
Fixed Size 2929352 bytes
Variable Size 541068600 bytes
Database Buffers 239075328 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL MOUNTED
PDBORCL2 MOUNTED
PDBORCL1 MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL2 READ WRITE
PDBORCL1 READ WRITE
**************************************************************************************
The pluggable database <pdb> clause can be any of the following:
**************************************************************************************
The ALL keyword to indicate all PDBs.
The ALL EXCEPT keywords, followed by one or more PDB names in a comma-separate list, to indicate a subset of PDBs.
Some examples are shown below.
ALTER PLUGGABLE DATABASE pdborcl1, pdborcl2 OPEN READ ONLY FORCE;
ALTER PLUGGABLE DATABASE pdborcl1, pdborcl2 CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ALL OPEN;
ALTER PLUGGABLE DATABASE ALL CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdborcl1 OPEN;
ALTER PLUGGABLE DATABASE ALL EXCEPT pdborcl1 CLOSE IMMEDIATE;
Friday, March 4, 2016
Clone the pluggable database - 12c
********************* *********************
Select a pluggable database to clone
********************* *********************
SQL> select name,open_mode from v$pdbs order by name;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL1 READ ONLY
PDBORCL2 READ WRITE
SQL> alter pluggable database pdborcl1 close;
Pluggable database altered.
SQL> alter pluggable database pdborcl1 open read only;
Pluggable database altered.
********************* *********************
Lets clone the pluggable database now
********************* *********************
SQL> create pluggable database pdborcl3 from pdborcl1 file_name_convert=('/u01/app/oracle/oradata/orcl/pdborcl1/'
,'/u01/app/oracle/oradata/orcl/pdborcl3/');
Pluggable database created.
SQL> alter pluggable database pdborcl3 open;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs order by name;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL1 READ ONLY
PDBORCL2 READ WRITE
PDBORCL3 READ WRITE
SQL> alter pluggable database pdborcl1 close;
Pluggable database altered.
SQL> alter pluggable database pdborcl1 open;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs order by name;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL1 READ WRITE
PDBORCL2 READ WRITE
PDBORCL3 READ WRITE
************************** **************************
Check this below link on how to drop a pluggable database :
************************** **************************
DROPPING-PLUGGABLE-DATABASE
Select a pluggable database to clone
********************* *********************
SQL> select name,open_mode from v$pdbs order by name;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL1 READ ONLY
PDBORCL2 READ WRITE
SQL> alter pluggable database pdborcl1 close;
Pluggable database altered.
SQL> alter pluggable database pdborcl1 open read only;
Pluggable database altered.
********************* *********************
Lets clone the pluggable database now
********************* *********************
SQL> create pluggable database pdborcl3 from pdborcl1 file_name_convert=('/u01/app/oracle/oradata/orcl/pdborcl1/'
,'/u01/app/oracle/oradata/orcl/pdborcl3/');
Pluggable database created.
SQL> alter pluggable database pdborcl3 open;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs order by name;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL1 READ ONLY
PDBORCL2 READ WRITE
PDBORCL3 READ WRITE
SQL> alter pluggable database pdborcl1 close;
Pluggable database altered.
SQL> alter pluggable database pdborcl1 open;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs order by name;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL1 READ WRITE
PDBORCL2 READ WRITE
PDBORCL3 READ WRITE
************************** **************************
Check this below link on how to drop a pluggable database :
************************** **************************
DROPPING-PLUGGABLE-DATABASE
Thursday, March 3, 2016
Connecting to a pluggable database -12c
******* ********** *********
USING EZCONNECT:
******* ********** *********
[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxxx@localhost:1521/pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:12:17 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SHOW CON_NAME
CON_NAME
------------------------------
PDBORCL1
******** ********* *********
USING TNSNAMES:
******** ********* *********
Create a tnsnames entry for the pdb that you want to connect
[oracle@Linux03 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
pdborcl1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux03)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxx@pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:15:10 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SHOW CON_NAME
CON_NAME
------------------------------
PDBORCL1
USING EZCONNECT:
******* ********** *********
[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxxx@localhost:1521/pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:12:17 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SHOW CON_NAME
CON_NAME
------------------------------
PDBORCL1
******** ********* *********
USING TNSNAMES:
******** ********* *********
Create a tnsnames entry for the pdb that you want to connect
[oracle@Linux03 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
pdborcl1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux03)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxx@pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:15:10 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> SHOW CON_NAME
CON_NAME
------------------------------
PDBORCL1
Wednesday, February 24, 2016
Datapump enhancements and new features in 12c
*************************************************************************
Datapump enhancements and new features in 12c
*************************************************************************
Login as sys dba and create user & data pump directory as below:
*************************************************************************
sqlplus /"as sysdba"
SQL> create directory dpump as '/u01/app/oracle/dpump';
Directory created.
SQL> grant read,write on directory dpump to pdborcl1_usr2;
Grant succeeded.
***************************************************
EXPORTING TABLES FROM PDB :
***************************************************
[oracle@Linux03 oracle]$ expdp pdborcl1_usr2/xxxxxxxx@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID
Export: Release 12.1.0.2.0 - Production on Fri Mar 11 14:54:24 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01": pdborcl1_usr2/********@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "PDBORCL1_USR2"."TEST" 5.078 KB 1 rows
. . exported "PDBORCL1_USR2"."TEST_ID" 5.937 KB 2 rows
Master table "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for PDBORCL1_USR2.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/dpump/pdborc1_usr2_tables.dmp
Job "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 11 14:54:39 2016 elapsed 0 00:00:13
***************************************************
LOGTIME=[NONE | STATUS | LOGFILE | ALL]
NONE : The default value, no timestamps to be included in output, same as previous versions.
STATUS : Timestamps are included in output console, but not in log file.
LOGFILE : Timestamps are included in log file, but not on console.
ALL : Timestamps are included in output to the log file and console.
***************************************************
Simple example :
expdp pdborcl1_usr2/********@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables1 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=all
Export: Release 12.1.0.2.0 - Production on Fri Mar 11 14:56:54 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
11-MAR-16 14:56:56.798: Starting "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01": pdborcl1_usr2/********@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables1 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=all
11-MAR-16 14:56:57.303: Estimate in progress using BLOCKS method...
11-MAR-16 14:56:58.213: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
11-MAR-16 14:56:58.257: Total estimation using BLOCKS method: 128 KB
11-MAR-16 14:57:01.225: Processing object type TABLE_EXPORT/TABLE/TABLE
11-MAR-16 14:57:01.700: Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
11-MAR-16 14:57:02.212: Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
11-MAR-16 14:57:03.340: Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
11-MAR-16 14:57:03.385: Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
11-MAR-16 14:57:03.978: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
11-MAR-16 14:57:03.994: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
11-MAR-16 14:57:08.998: . . exported "PDBORCL1_USR2"."TEST" 5.078 KB 1 rows
11-MAR-16 14:57:09.013: . . exported "PDBORCL1_USR2"."TEST_ID" 5.937 KB 2 rows
11-MAR-16 14:57:09.235: Master table "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
11-MAR-16 14:57:09.240: ******************************************************************************
11-MAR-16 14:57:09.241: Dump file set for PDBORCL1_USR2.SYS_EXPORT_TABLE_01 is:
11-MAR-16 14:57:09.246: /u01/app/oracle/dpump/pdborc1_usr2_tables1.dmp
11-MAR-16 14:57:09.260: Job "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 11 14:57:09 2016 elapsed 0 00:00:13
***************************************************
DISABLE ARCHIVE LOGGINGTHE. DEFAULT SETTING OF "N" HAS NO AFFECT ON LOGGING BEHAVIOUR.USING A VALUE "Y" REDUCES THE LOGGING ASSOCIATED WITH TABLES :
***************************************************
impdp pdborcl1_usr2/pdborcl1_usr2@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables3 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=status TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
The effect can be limited to a specific type of object (TABLE or INDEX) as below.
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX
Subscribe to:
Posts (Atom)