Search This Blog

Total Pageviews

Friday 28 April 2017

Oracle 12c cloning pdb without local undo tablespace ...

Oracle 12c clone pdb !!!



[oracle@vbgeneric ~]$  export TWO_TASK=
[oracle@vbgeneric ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 28 04:14:36 2017

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


set linesize 200
COL VERSION FORMAT A15
COL STATUS  FORMAT A20
COL PRODUCT FORMAT a50
SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                            VERSION         STATUS
-------------------------------------------------- --------------- --------------------
NLSRTL                                             12.1.0.2.0      Production
Oracle Database 12c Enterprise Edition             12.1.0.2.0      64bit Production
PL/SQL                                             12.1.0.2.0      Production
TNS for Linux:                                     12.1.0.2.0      Production


SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production



SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
ORCL                           READ WRITE <<<<<<Create duplicate database from this database to ORCLDB
ORDS                           READ WRITE
ANUJ                           MOUNTED
VIHAAN                         MOUNTED


In tnsname.ora file 

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

 tnsping ORCL

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 28-APR-2017 04:19:24

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (10 msec)



check sys connection ..


[oracle@vbgeneric dbs]$ sqlplus 'sys/vihaan@orcl as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 28 04:52:17 2017

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



set linesize 200 pagesize 200
col FILE_NAME   for a70
SELECT CON_ID, TABLESPACE_NAME,FILE_NAME FROM CDB_DATA_FILES ;

    CON_ID TABLESPACE_NAME                FILE_NAME
---------- ------------------------------ ----------------------------------------------------------------------
         3 SYSTEM                         /u01/app/oracle/oradata/orcl12c/orcl/system01.dbf
         3 SYSAUX                         /u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf
         3 USERS                          /u01/app/oracle/oradata/orcl12c/orcl/SAMPLE_SCHEMA_users01.dbf
         3 EXAMPLE                        /u01/app/oracle/oradata/orcl12c/orcl/example01.dbf
         3 APEX_1851336378250219          /u01/app/oracle/oradata/orcl12c/orcl/APEX_1851336378250219.dbf
         3 APEX_5457999048253711          /u01/app/oracle/oradata/orcl12c/orcl/APEX_5457999048253711.dbf
         3 APEX_5500333564645084          /u01/app/oracle/oradata/orcl12c/orcl/APEX_5500333564645084.dbf

7 rows selected.

create dir ...

cd /u01/app/oracle/oradata/orcl12c/

[oracle@vbgeneric orcl12c]$ mkdir orcldp

[oracle@vbgeneric orcl12c]$ ls -ld */
drwxr-x--- 2 oracle oinstall 4096 Apr 21 16:01 ANUJ/
drwxr-x--- 2 oracle oinstall 4096 Nov 20 12:14 orcl/
drwxr-xr-x 2 oracle oinstall 4096 Apr 28 05:10 orcldp/
drwxr-x--- 2 oracle oinstall 4096 Apr 21 14:12 ORDS/
drwxr-x--- 2 oracle oinstall 4096 Jun  2  2016 pdbseed/
drwxr-xr-x 2 oracle oinstall 4096 Apr 23 14:13 VIHAAN/



[oracle@vbgeneric ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 28 04:14:36 2017

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


SET LINESIZE 200 PAGESIZE 100 SERVEROUTPUT ON
COLUMN "DB DETAILS" FORMAT A100
SELECT
 'DB_NAME: ' ||sys_context('userenv', 'db_name')                  ||
 ' \CDB:-' ||(select cdb from v$database)                         ||
 ' \AUTH_ID: ' ||sys_context('userenv', 'authenticated_identity') ||
 ' \USER: ' ||sys_context('userenv', 'current_user')              ||
 ' \CONTAINER:' ||nvl(sys_Context('userenv', 'con_Name'), 'NON-CDB') "DB DETAILS"
 FROM DUAL ;

DB DETAILS
----------------------------------------------------------------------------------------------------
DB_NAME: orcl12c \CDB:-YES \AUTH_ID: oracle \USER: SYS \CONTAINER:CDB$ROOT


SQL> create database link clone_link1 connect to clone identified by clone using 'orcl';
Database link created.


SQL> create pluggable database orcldp from orcl@clone_link1 file_name_convert=('orcl','orcldb');

Pluggable database created.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO
         4 ORDS                           READ WRITE NO
         5 ANUJ                           READ WRITE NO
         6 VIHAAN                         READ WRITE NO
         7 ORCLDP                         MOUNTED  <<<<<<<< Duplicate database from ORCL to ORCLDP


SQL> alter pluggable database ORCLDP open;

Pluggable database altered.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO
         4 ORDS                           READ WRITE NO
         5 ANUJ                           READ WRITE NO
         6 VIHAAN                         READ WRITE NO
         7 ORCLDP                         READ WRITE NO

Oracle DBA

anuj blog Archive