Showing posts with label pdbs. Show all posts
Showing posts with label pdbs. Show all posts

Thursday, March 24, 2016

PDB ADMIN account created while Pluggable database creation doesn't have DBA level privs

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

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:
**************************************************************************************

One or more PDB names, specified as a comma-separated list.
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








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

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