Oracle 12c CDB與PDB

標籤：down   comment   rect   host   blog   CON_ID   www   data-   roo   

Oracle安裝參考：https://www.cnblogs.com/zhichaoma/p/9288739.html對於CDB，啟動和關閉與之前傳統的方式一樣，具體文法如下：    STARTUP[NOMOUNT | MOUNT | RESTRICT | UPGRADE | FORCE | READ ONLY]    SHUTDOWN[IMMEDIATE | ABORT]手動建立CDB1. 修改~/.bash_profile檔案，修改ORACLE_SID名稱

export ORACLE_SID=orcl

 2. 建立所需要目錄

$ mkdir -p /data/app/oracle/oradata/orcl$ mkdir -p /data/app/oracle/oradata/pdbseed$ mkdir -p /data/app/oracle/admin/orcl/adump$ mkdir -p /data/app/oracle/fast_recovery_area

 3.建立初始化設定檔

$ vim $ORACLE_HOME/dbs/initcdb.oradb_name=‘orcl‘memory_target=1Gprocesses = 150#audit_file_dest=‘<ORACLE_BASE>/admin/orcl/adump‘audit_file_dest=‘/data/app/oracle/admin/orcl/adump‘audit_trail =‘db‘db_block_size=8192db_domain=‘‘db_recovery_file_dest=‘/data/app/oracle/fast_recovery_area‘db_recovery_file_dest_size=2Gdiagnostic_dest=‘/data/app/oracle‘dispatchers=‘(PROTOCOL=TCP) (SERVICE=orcl)‘open_cursors=300 remote_login_passwordfile=‘EXCLUSIVE‘undo_tablespace=‘UNDOTBS1‘control_files = (/data/app/oracle/oradata/orcl/ora_control01.ctl, /data/app/oracle/oradata/orcl/ora_control02.ctl)compatible =‘12.2.0‘enable_pluggable_database=true

4.開始建立庫

$ sqlplus / as sysdbaSQL> shutdown abortSQL> create spfile from pfile=‘$ORACLE_HOME/dbs/initcdb.ora‘;SQL> startup nomount

SQL> CREATE DATABASE orclUSER SYS IDENTIFIED BY passUSER SYSTEM IDENTIFIED BY passLOGFILE GROUP 1 (‘/data/app/oracle/oradata/orcl/redo01a.log‘,‘/data/app/oracle/oradata/orcl/redo01b.log‘)    SIZE 100M BLOCKSIZE 512,  GROUP 2 (‘/data/app/oracle/oradata/orcl/redo02a.log‘,‘/data/app/oracle/oradata/orcl/redo02b.log‘)    SIZE 100M BLOCKSIZE 512MAXLOGHISTORY 1MAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 1024CHARACTER SET AL32UTF8NATIONAL CHARACTER SET AL16UTF16EXTENT MANAGEMENT LOCALDATAFILE ‘/data/app/oracle/oradata/orcl/system01.dbf‘ SIZE 700MSYSAUX DATAFILE ‘/data/app/oracle/oradata/orcl/sysaux01.dbf‘ SIZE 550MDEFAULT TABLESPACE deftbs DATAFILE ‘/data/app/oracle/oradata/orcl/deftbs01.dbf‘ SIZE 500MDEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE ‘/data/app/oracle/oradata/orcl/temp01.dbf‘ SIZE 20M UNDO TABLESPACE undotbs1 DATAFILE ‘/data/app/oracle/oradata/orcl/undotbs01.dbf‘ SIZE 200M ENABLE PLUGGABLE DATABASESEEDFILE_NAME_CONVERT = (‘/data/app/oracle/oradata/orcl/‘, ‘/data/app/oracle/oradata/pdbseed/‘)SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITEDSYSAUX DATAFILES SIZE 100MUSER_DATA TABLESPACE usertbsDATAFILE ‘/data/app/oracle/oradata/pdbseed/usertbs01.dbf‘ SIZE 200M;

填充資料字典，如果是12cR2版本，則直接執行catcdb.sql代替以下指令碼

使用sysdba許可權執行以下指令碼

SQL> @?/rdbms/admin/catalog.sql --資料庫字典，動態視圖建立等SQL> @?/rdbms/admin/catproc.sql --PL/SQL存過過程相關代碼SQL> @?/rdbms/admin/utlrp.sql  --編譯

 使用SYSTEM使用者執行以下指令碼

SQL> @?/sqlplus/admin/pupbld.sql #SQL*Plus相關

12CR2

註：這個過程可能比較漫長

SQL> @?/rdbms/admin/catcdb.sql

會提示輸入以下資訊

Enter value for 1: /data/app/oracle/product/12.2.0/db_1/rdbms/adminEnter value for 2: /data/app/oracle/product/12.2.0/db_1/rdbms/admin/catcdb.pl

如果出現以下報錯

1）錯誤-1

Can‘t locate util.pm in @INC (@INC contains: /data/app/oracle/product/12.2.0/db_1/rdbms/admin /usr/local/lib64/perl5
/usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at 
/data/app/oracle/product/12.2.0/db_1/rdbms/admin/catcdb.pl line 35.BEGIN failed--compilation aborted at /data/app/oracle/product/12.2.0/db_1/rdbms/admin/catcdb.pl line 35.

 解決方案：

$ find $ORACLE_HOME -name util.pm | wc -l0$ find $ORACLE_HOME -name Util.pm | wc -l5$ find $ORACLE_HOME -name Util.pm/data/app/oracle/product/12.2.0/db_1/perl/lib/5.22.0/x86_64-linux-thread-multi/Hash/Util.pm/data/app/oracle/product/12.2.0/db_1/perl/lib/5.22.0/x86_64-linux-thread-multi/List/Util.pm/data/app/oracle/product/12.2.0/db_1/perl/lib/5.22.0/x86_64-linux-thread-multi/Scalar/Util.pm/data/app/oracle/product/12.2.0/db_1/perl/lib/5.22.0/x86_64-linux-thread-multi/Sub/Util.pm/data/app/oracle/product/12.2.0/db_1/perl/lib/site_perl/5.22.0/HTTP/Headers/Util.pm

手工修改catcdb.pl指令碼

use Util qw(trim, splitToArray);     //util改為Util

再來一輪測試，結果發現還是會有報錯，這種嘗試會讓你開始懷疑自己的選擇到底是不是正確的方向。

如果還是沒有找到，說明在當前的環境變數中沒有匹配到相關的內容，我們需要直接切換到目錄Hash下，然後運行指令碼才可以，這個時候又出現一個錯誤

2）錯誤-2

Can‘tlocate Term/ReadKey.pm in @INC (@INC contains: /data/app/oracle/product/12.2.0/db_1/rdbms/admin /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /data/app/oracle/product/12.2.0/db_1/rdbms/admin/catcdb.pl line 30.BEGINfailed--compilation aborted at /data/app/oracle/product/12.2.0/db_1/rdbms/admin/catcdb.pl line 30.

 聲明環境變數

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$ORACLE_HOME/jdk/bin:$PATH

驗證結果

SQL> SELECT name,   DECODE (cdb,     ‘YES‘, ‘Multitenant Option enabled‘,     ‘Regular 12c Database: ‘)   "Multitenant Option",   open_mode,   con_id FROM v$database;

建立PDB

1.查看當前容器

$ sqlplus / as sysdbaSQL> show con_name;CON_NAME------------------------------CDB$ROOT

 2. 建立一個新的PDB

$ mkdir -p /data/app/oracle/oradata/pdb1SQL> create pluggable database pdb1 admin user pdb1 identified by pdb1 file_name_convert=(‘/data/app/oracle/oradata/pdbseed/‘,‘/data/app/oracle/oradata/pdb1/‘);

3. 查看所有PDB

SQL> show pdbs;    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB1   MOUNTED

4. 啟動和關閉一個建立好的PDB

1）啟動pdb和關閉pdb

    a) 啟動pdb

SQL> alter pluggable database pdb1 open;Pluggable database altered.SQL> show pdbs;    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB1   READ WRITE NO

    b)關閉pdb

SQL> alter pluggable database pdb1 close;Pluggable database altered.SQL> show pdbs;    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED  READ ONLY  NO 3 PDB1   MOUNTED

 2）通過sqlplus使用傳統的startup和shutdown命令來啟動和關閉PDB

    a) 啟動pdb

SQL> alter session set container=pdb1;Session altered.SQL> show pdbs;    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 3 PDB1   MOUNTEDSQL> startupPluggable Database opened.SQL> show pdbs;    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 3 PDB1   READ WRITE NO

    b) 關閉pdb

SQL> shutdown immediate;Pluggable Database closed.SQL> show pdbs;    CON_ID CON_NAME  OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ---------- 3 PDB1   MOUNTED

 5. 配置監聽

$ vim $ORACLE_HOME/network/admin/listener.ora# listener.ora Network Configuration File: /data/app/oracle/product/12.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER =  (DESCRIPTION_LIST =    (DESCRIPTION =      (ADDRESS = (PROTOCOL = TCP)(HOST = DB)(PORT = 1521))      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))    )  )SID_LIST_LISTENER =  (SID_LIST =    (SID_DESC=      (GLOBAL_DBNAME = ORCL)      (SID_NAME = ORCL)     )    (SID_DESC=      (GLOBAL_DBNAME = PDB1)      (SID_NAME = PDB1)    )  )

 

$ vim $ORACLE_HOME/network/admin/tnsnames.ora    //如果此檔案不存在，手動建立LISTENER_ORCL =  (ADDRESS = (PROTOCOL = TCP)(HOST = DB)(PORT = 1521))ORCL =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = DB)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = orcl)    )  )pdb1 =  (DESCRIPTION =    (ADDRESS = (PROTOCOL = TCP)(HOST = DB)(PORT = 1521))    (CONNECT_DATA =      (SERVER = DEDICATED)      (SERVICE_NAME = pdb1)    )  )

 6. 測試與登陸

$ tnsping pdb1//測試登陸$ sqlplus pdb1/[email protected]        //登陸

 資料泵匯入匯出PDB資料

以pdb1資料庫為例

1. 啟動pdb1資料庫

$ sqlplus / as sysdbaSQL> alter session set container=pdb1;SQL> startup

 2. 建立匯入資料目錄和資料庫映射關係

$ mkdir -p /home/oracle/dump_dirSQL> alter session set container=pdb1;SQL> create directory dump_dir as ‘/home/oracle/dump_dir‘;

 3. 建立一個dba許可權的資料泵匯入匯出使用者

SQL> grant dba to dp identified by dp;SQL> grant read,write on directory dump_dir to dp;

 4. 匯出資料

$ expdp dp/[email protected] directory="dump_dir" dumpfile="aa.dmp" logfile=aa.log

 5. 匯入資料

$ impdp dp/[email protected] directory="dump_dir" dumpfile="aa.dmp" logfile=bb.log

 

Oracle 12c CDB與PDB