Özgür Umut VURGUN

1. 12c Pluggable Database Hands-On Özgür Umut VURGUN www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

2. Ajanda Oracle Kronoloji • Oracle Kronoloji • Oracle 12c Pluggable Database • Container Database • CDB$ROOT • Pluggable Database • Oracle 12c Yeni Kullanıcılar ve Haklar • Oracle 12c Hands On • Lab 1: Drop / Unplug PDB • Lab 2: Plug / Clone PDB • Lab 3: CLONE PDB FROM REMOTE CDB • Lab4: CLONE PDB FROM REMOTE CDB with DB Link • Lab5: Non CDB to CDB with Data PumpTransportableExport / Import www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

3. Oracle Kronoloji Manage changing Grid Real Application Cluster www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

4. Pre - Oracle 12c • Oracle 12c öncesi veritabanı yapısını inceleyecek olursak … • Background Proses çokluğu • Yüksek memory paylaşımı • Birden çok Oracle Medata zorunluluğu www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

5. Oracle 12c PluggableDatabase Container Database (CDB) Birden fazla veri tabanının çalıştığı ana yapı. Pluggable Database (PDB) Taşınabilir özelliği olan ve normal işlemlerin yürütüldüğü veri tabanları NonContainer Database Oracle 12c yapısı dışında kullanılan veri tabanları PDB$SEED Container DB üzerinde bulunan template PDB User & Grants CDB ve PDB üzerinde yeni kullanıcılar. Common Role &Users , Local Role & Users www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

6. Container Database (CDB) • ROOT yapısı altında ‘OracleSystem Metada’ ve ve işlemlerinin yapıldığı alandır. • OracleMetadata • ‘CommonUsers’ yönetiminin yapıldığı • Instance mantığında memory ve proses yönetiminin yapıldığı alandır. www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

7. CDB$ROOT / RootFiles • SYSTEM – SYSAUX - UNDO ve USERS tablespace’leri • Oracle Sistem Medata • Redolog • Default temp • PLSQL shared package(dbms_sql) [oracle@test1 scripts]$ sqlplus / as sysdba SQL> @tablespace CON_ID Status Name Type Size (M) ---------- ----- ------------------------------ --------------------- -------------- 1ONLINE SYSTEM PERMANENT 790.000 1 ONLINE SYSAUX PERMANENT 1,470.000 1 ONLINE UNDOTBS1 UNDO 250.000 1 ONLINE USERS PERMANENT 5.000 2 ONLINE SYSTEM PERMANENT 250.000 2 ONLINE SYSAUX PERMANENT 590.000 5 ONLINE ABYS_DATA PERMANENT 161.250 5 ONLINE SYSAUXPERMANENT 820.000 5 ONLINE USERSPERMANENT 38.750 5 ONLINE SYSTEMPERMANENT 440.000 [oracle@test1 scripts]$ sqlplus sys/Oracle3@abys as sysdba SQL> @tablespace CON_ID Status Name Type Extent Man Size (M) ---------- --------- ------------------------------ --------- ---------- -------------- 5 ONLINE USERS PERMANENT 38.750 5 ONLINE ABYS_DATA PERMANENT 161.250 5 ONLINE TEMP TEMPORARY 506.000 5 ONLINE SYSTEM PERMANENT 440.000 5 ONLINE SYSAUX PERMANENT 820.000 www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

8. Pluggable Database (PDB) Common Users Resource Plan Common Role OBJ$ Local Privilege Local Role Local Users • Application Tablespace • TEMP (lokal) • Kullanıcılar ve Hakları • PDB Sistem Metadata • Application Verisi • Resource Plan SYSTEM SYSAUX Temp Users www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

9. Topoloji www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

10. Oracle 12c Installation www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

11. UsersandRoles PDB CDB$ROOT LocalUsers CommonUsers • Root Container’dan yönetilir • C## ön adıyla başlamak zorundadır. • CDB üzerindeki her PDB’de geçerlidir. • Unique User • CONTAINER=ALL • Pre – Oracle 12c • PDB User • Multiple User Local Role Common Role • PDB’den yönetilir. • CREATE ROLE • Oluşturulduğu PDB üzerinde geçerlidir. • Root Container’dan yönetilir • CREATE ROLE – SET CONTAINER • C## ön adıyla başlamak zorundadır. • CONTAINER=ALLile kullanılır. www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

12. UsersandGrants PDB$ SEED SYS HR HR SYS HR SYS C##DBA NAT C##DBA NAT NAT pdbtst1 pdbtst2 CDB$ROOT HR SYS C##DBA C##DBA NAT Sqlplus sys/Oracle3@pdbtst1 as sysdba; Sqlplus sys/Oracle3@pdbtst2 as sysdba; Create user c##dba identified by test1 container=ALL; Grant create session to c##dba container=ALL; Grant select any table to c##dba container=ALL; www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

13. CREATE PDBs 1. FILE_NAME_CONVERT olmadan PDB oluşturma SQL> CREATE PLUGGABLE DATABASE PDBTST1 ADMIN USER PDBTST1_ADMIN IDENTIFIED BY oracle ROLES = (connect,resource); Pluggable database created. **** alter session set pdb_file_name_convert='pdb','pdb_test'; SQL> show con_name CON_NAME CDB$ROOT 2. FILE_NAME_CONVERT ile PDB oluşturma CREATE PLUGGABLE DATABASE PDBTST1 ADMIN USER PDBTST1_ADMIN IDENTIFIED BY oracle FILE_NAME_CONVERT = ('/disk2/app/oracle/oradata/orcl/pdbseed', '/disk2/dbf'); SQL> select NAME, OPEN_MODE, CON_ID from V$PDBS; NAME OPEN_MODE CON_ID PDB$SEED READ ONLY 2 PDBORCL MOUNTED 3 PDBTST1 MOUNTED 7 www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

14. PDB Open / Close SQL> alterpluggabledatabaseallopen; Pluggabledatabasesopened. SQL> SELECT name,open_mode FROM v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBORCL READ WRITE PDBTST1 READ WRITE PDB OPEN ALTER PLUGGABLE DATABASE pdbtst1 OPEN; ALTER PLUGGABLE DATABASE ALL OPEN; OPEN ALTER DATABASE OPEN; STARTUP SQL> startup ORACLE instancestarted. Database opened. SQL> SELECT name,open_mode FROM v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBORCL MOUNTED PDBTST1 MOUNTED MOUNT ALTER DATABASE MOUNT; STARTUP MOUNT NOMOUNT STARTUP NOMOUNT SQL> alterdatabasemount; Database altered. SQL> SELECT name,open_mode FROM v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED MOUNTED PDBORCL MOUNTED PDBTST1 MOUNTED ALTER PLUGGABLE DATABASE pdb1 CLOSE IMMEDIATE; ALTER PLUGGABLE DATABASE ALL EXCEPT pdb1 CLOSE; ALTER PLUGGABLE DATABASE ALL CLOSE; ALTER PLUGGABLE DATABASE CLOSE; SHUTDOWN IMMEDIATE SHUTDOWN www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

15. Lab1: UNPLUG / DROP PDBs CLOSE PDB 1 ALTER PLUGGABLE DATABASE PDBORCL CLOSE; SQL> SELECT name,open_mode FROM v$pdbs; NAME OPEN_MODE PDB$SEED READ ONLY PDBORCL MOUNT PDBTST1 READ WRITE PDB ORCL PDB$ SEED PDB TST1 ROOT UNPLUG PDB 2 ALTER PLUGGABLE DATABASE PDBORCL UNPLUG INTO '/DISK2/SETUP_FILES/PDBORCL.XML'; SQL> SELECT name,open_mode FROM v$pdbs; NAME OPEN_MODE PDB$SEED READ ONLY PDBTST1 READ WRITE PDB ORCL PDB$ SEED PDB TST1 ROOT DROP PDB 3 PDB$ SEED PDB TST1 DROP PLUGGABLE DATABASE PDBORCL; DROP PLUGGABLE DATABASE PDBORCL KEEP DATAFILES; DROP PLUGGABLE DATABASE PDBORCL INCLUDING DATAFILES DBF ROOT XML www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

16. Lab2 : PLUG / Clone PDB XML PLUG PDB 1 • CREATE PLUGGABLE DATABASE PDBORCL • USING '/disk2/setup_files/pdborcl.xml‘ • FILE_NAME_CONVERT=(…………………………); • CREATE PLUGGABLE DATABASE PDBORCL USING • '/disk2/setup_files/pdborcl.xml' • NOCOPY || COPY || MOVE TEMPFILE REUSE; • SQL> SELECT name,open_mode FROM v$pdbs; • NAME OPEN_MODE • PDB$SEED READ ONLY • PDBORCL MOUNTED • TST1 READ WRITE PDB$ SEED PDB TST1 PDBORCL ROOT CLONE PDB 2 TST08 PDB$ SEED TST07 ALTER PLUGGABLE DATABASE TST07 OPEN READ ONLY; CREATE PLUGGABLE DATABASE TST08 FROM TST07 FILE_NAME_CONVERT=(……………); ALTER PLUGGABLE DATABASE TST07 OPEN READ WRITE; ALTER PLUGGABLE DATABASE TST08 OPEN READ WRITE; ROOT www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

17. Lab 3 : CLONE PDB FROM REMOTE CDB XML ve DBF -> Target OS Create PDB CDB to CDB 1 scp -r * oracle@192.168.4.61:/disk2/setup_files/datafiles CDB’dePDB’lerin kontrolü SQL> select pdb_name, status from CDB_PDBS; PDB_NAME STATUS PDB$SEED NORMAL PDB01 NORMAL PDB05 NORMAL PDB1905 PDB$ SEED PDBWEB PDBCDR PDB Oluşturalım ROOT create pluggable database PDBCDR using  '/disk2/setup_files/ pdborcl.xml'  source_file_name_convert (‘……, ……')  nocopy tempfile reuse;   CDBIST Oluşan PDB’in durumu SQL> selectpdb_name, statusfromCDB_PDBS; PDB_NAME STATUS PDB$SEED NORMAL PDBTST1 NORMAL PDBCDR UNPLUG PDB01 PDB05 PDB$ SEED SQL> select name, open_mode from V$PDBS; NAME OPEN_MODE PDB$SEED READ ONLY PDBCDR MOUNTED PDBTST1 READ WRITE ROOT CDBANK www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

18. SQL> select pdb_name, status from CDB_PDBS; PDB_NAME STATUS PDB$SEED NORMAL PDBTST1 NORMAL TST06 UNPLUG SQL> select name, open_mode from V$PDBS; NAME OPEN_MODE PDB$SEED READ ONLY TST06 MOUNTED PDBTST1 READ WRITE Lab 4 : CLONE PDB FROM REMOTE CDB with DB LINK Create PDB CDB to CDB 4 PDB Read Only PDB1905 PDB$ SEED PDBWEB PDBCDR SQL> alterpluggabledatabase PDBCDR closeimmediate;   SQL> alterpluggabledatabase  PDBCDR openreadonly; ROOT DB Link CDBIST scp -r * oracle@192.168.4.61:/disk2/setup_files/datafiles SQL> select pdb_name, status from CDB_PDBS; PDB_NAME STATUS PDB$SEED NORMAL PDBTST1 NORMAL create pluggable database TST06  using  '/disk2/setup_files/ pdborcl.xml'  source_file_name_convert (‘……, ……') nocopy tempfile reuse;   create database link pdbcdr _lnk connect to SYSTEM identified by Oracle3 using '192.168.4.61:1521/pdbcdr'; DB Link PDB Oluşturma create pluggable database pdbcdr _rmt from pdbcdr @ pdbcdr _lnkfile_name_convert=('/disk2/app/oracle/oradata/orcl/','/disk2/app/oracle/oradata/orcl/remotedb'); PDB01 PDB05 PDB$ SEED SQL> select name, open_mode from V$PDBS; NAME OPEN_MODE PDB$SEED READ ONLY PDBCDR MOUNTED PDBTST1 READ WRITE ROOT CDBANK * BUG 15931910 www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

19. Lab 5 : MoveNon CDB to CDB Bu yöntemi uygulamak için ; **** Non CDB veritabanı 11.2.0.3 olmak zorunda **** Compatible Parametresi = 11.2.0.3 **** Karakter setleri aynı olmalı OS : Windows 7 32bit DB : Oracle 11.2.0.3 ABYS CDRDB ABYSDB DMP Dosyası 1. Tablespace Read Only Altertablespace ABYS readonly; Altertablespace USERS readonly; 2. Non CDB, Data PumpTransportableExport expdp 'sys/Oracle3 as sysdba' full=y job_name=EXPORT_CDRDB dumpfile=abysdbdb.dmp DIRECTORY=EXP_DATA_PUMP_DIR LOGFILE=exp_abysdb.log VERSION=12transportable=always CDBANK OS : Centos Linux 64bit DB : Oracle 12.1.0.1 3. RMAN Backup DBF PDB01 PDB05 PDB$ SEED rmantarget / Run { CONVERT DATAFILE 'D:\APP\197278\ORADATA\ORCL\ABYS.DBF' DB_FILE_NAME_CONVERT="D:\APP\197278\ORADATA\ORCL\ABYS.DBF", "D:\orabkup\ABYS.DBF" ………………….. ROOT www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

20. Lab 5 : MoveNon CDB to CDB OS : Windows 7 32bit DB : Oracle 11.2.0.3 ABYS 4. Dosyaların Transferi CDRDB FTP ile kopyalama *** $scp –r exp_files oracle@test2:/disk2/backup ABYSDB 5. Pluggable DB Oluşturma DMP Dosyası ABYS CREATE PLUGGABLE DATABASE abys ADMIN USER adminIDENTIFIED BY abys ROLES = (connect,resource); Alterpluggabledatabase ABYS openreadwrite ; 6. Restore Datafiles CDBANK OS : Centos Linux 64bit DB : Oracle 12.1.0.1 run { CONVERT DATAFILE '/disk2/exp/ABYS.dbf' DB_FILE_NAME_CONVERT="/disk2/exp/import/ABYS.dbf", "/disk2/app/oracle/oradata/orcl/abys_a/ABYS.dbf" FORMAT='/disk2/app/oracle/oradata/orcl/abys_a/ABYS.dbf';…………………….. PDB01 PDB05 PDB$ SEED ROOT www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

21. Lab 5: MoveNon CDB to CDB 7. Import ABYS OS : Windows 7 32bit DB : Oracle 11.2.0.3 impdp \'sys/Oracle3@abyspdb AS SYSDBA\' full=y DIRECTORY=IMP_DATA_PUMP_DIR dumpfile=DATAPUMP_DEV2DB.DMP LOGFILE=imp_abys.log VERSION=12 TRANSPORT_DATAFILES=USERS01.DBF_2 job_name=imp_ABYSdbparallel=2 REMAP_TABLESPACE='ABYS':'ABYS_DATA' CDRDB ABYSDB 8. PDB Status SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS; CON_ID NAME OPEN_MODE 2 PDB$SEED READ ONLY 4ABYS READ WRITE DMP Dosyası CDBANK OS : Centos Linux 64bit DB : Oracle 12.1.0.1 9. Tablo Kontrolü [oracle@test1]$ sqlplus / as sysdba SQL > connsys/Oracle3@abysdb as sysdba Connectted. SQL> selectcount(*) fromabys.abone; COUNT(*) 567 PDB01 PDB05 PDB$ SEED ABYS ROOT www.ozgurumutvurgun.com TROUG Ankara Bulusmasi

22. Teşekkürler Özgür Umut Vurgun Bilgisayar Mühendisi Oracle 10g OCA/OCP ozgurumutvurgun@gmail.com www.ozgurumutvurgun.com www.turkceoracle.com www.ozgurumutvurgun.com TROUG Ankara Bulusmasi