DB 12c – 시작 및 서비스접속, CDB/PDB 시작 및 종료

배경 : 현재 12c 설치되어 있고 PDB 2개 설치 되어 있는 상태 입니다.

# 시작 및 서비스 접속방법

1. CDB & PDB 시작
   

   [root@test ~]# su – oracle
   

   [test@cdb1 : /home/oracle]$ sqlplus
   

    
    

   SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 7 11:11:51 2015
   

    
    

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

    
    

   Enter user-name: /as sysdba
   

   Connected to an idle instance.
   

    
    

   SQL> startup (CDB시작)
   

   ORACLE instance started.
   

    
    

   Total System Global Area 835104768 bytes
   

   Fixed Size 2293880 bytes
   

   Variable Size 595595144 bytes
   

   Database Buffers 234881024 bytes
   

   Redo Buffers 2334720 bytes
   

   Database mounted.
   

   Database opened.
   

   SQL> !lsnrctl start
   

    
    

   LSNRCTL for Linux: Version 12.1.0.1.0 – Production on 07-AUG-2015 11:17:31
   

    
    

   Copyright (c) 1991, 2013, Oracle. All rights reserved.
   

    
    

   Starting /oracle/product/12.1.0/db_1/bin/tnslsnr: please wait…
   

    
    

   TNSLSNR for Linux: Version 12.1.0.1.0 – Production
   

   System parameter file is /oracle/product/12.1.0/db_1/network/admin/listener.ora
   

   Log messages written to /oracle/diag/tnslsnr/test/listener/alert/log.xml
   

   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
   

   Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
   

    
    

   Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test)(PORT=1521)))
   

   STATUS of the LISTENER
   

   ————————
   

   Alias LISTENER
   

   Version TNSLSNR for Linux: Version 12.1.0.1.0 – Production
   

   Start Date 07-AUG-2015 11:17:31
   

   Uptime 0 days 0 hr. 0 min. 0 sec
   

   Trace Level off
   

   Security ON: Local OS Authentication
   

   SNMP OFF
   

   Listener Parameter File /oracle/product/12.1.0/db_1/network/admin/listener.ora
   

   Listener Log File /oracle/diag/tnslsnr/test/listener/alert/log.xml
   

   Listening Endpoints Summary…
   

   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test)(PORT=1521)))
   

   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
   

   The listener supports no services
   

   The command completed successfully
   

    
    

   SQL> alter pluggable database all open; (PDB 전체 OPEN)
   

    
    

   Pluggable database altered.
   

    
    

   SQL> show pdbs (PDB 확인)
   

    
    

   CON_ID CON_NAME OPEN MODE RESTRICTED
   

   ———- —————————— ———- ———-
   

   2 PDB$SEED READ ONLY NO
   

   3 PDB11 READ WRITE NO
   

   4 PDB12 READ WRITE NO
   

    
    

2. CDB 접속 방법
   

• OS 인증 방법

  [test@cdb1 : /home/oracle]$ sqlplus / as sysdba
  

   
   

  SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 7 11:33:06 2015
  

   
   

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

   
   

   
   

  Connected to:
  

  Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
  

  With the Partitioning, OLAP and Real Application Testing options
  

   
   

  SQL>
  

   
   

• Easy Connect 방법

  [test@cdb1 : /home/oracle]$ sqlplus sys/oracle@test:1521/cdb1 as sysdba
  

   
   

  SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 7 11:34:30 2015
  

   
   

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

   
   

   
   

  Connected to:
  

  Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
  

  With the Partitioning, OLAP and Real Application Testing options
  

   
   

  SQL>
  

   
   

• Net Service Name 방법(tnsnames.ora 설정)

  [test@cdb1 : /home/oracle]$ sqlplus sys/oracle@cdb1 as sysdba
  

   
   

  SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 7 11:46:58 2015
  

   
   

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

   
   

   
   

  Connected to:
  

  Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
  

  With the Partitioning, OLAP and Real Application Testing options
  

   
   

  SQL>
  

   
   

   
   

1. PDB 접속 방법
   

• Easy Connect 방법

  [test@cdb1 : /home/oracle]$ sqlplus sys/oracle@test:1521/pdb11 as sysdba
  

   
   

  SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 7 11:44:26 2015
  

   
   

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

   
   

   
   

  Connected to:
  

  Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
  

  With the Partitioning, OLAP and Real Application Testing options
  

   
   

  SQL>
  

   
   

• Net Service Name 방법(tnsnames.ora 설정)

  [test@cdb1 : /oracle/product/12.1.0/db_1/network/admin]$ cat tnsnames.ora
  

  # tnsnames.ora Network Configuration File: /oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
  

  # Generated by Oracle configuration tools.
  

   
   

  LISTENER_CDB1 =
  

  (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
  

   
   

   
   

  CDB1 =
  

  (DESCRIPTION =
  

  (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
  

  (CONNECT_DATA =
  

  (SERVER = DEDICATED)
  

  (SERVICE_NAME = cdb1)
  

  )
  

  )
  

   
   

  PDB11 =
  

  (DESCRIPTION =
  

  (ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
  

  (CONNECT_DATA =
  

  (SERVER = DEDICATED)
  

  (SERVICE_NAME = pdb11)
  

  )
  

  )
  

   
   

  [test@cdb1 : /home/oracle]$ sqlplus sys/oracle@pdb11 as sysdba
  

   
   

  SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 7 11:46:16 2015
  

   
   

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

   
   

   
   

  Connected to:
  

  Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
  

  With the Partitioning, OLAP and Real Application Testing options
  

   
   

  SQL>
  

# 관련 View 및 Database 파일 살펴보기

1. v$ view 살펴보기
   

   SQL> select instance_name,status,con_id from v$instance;
   

    
    

   INSTANCE_NAME STATUS CON_ID
   

   ——————————– ———————— ———-
   

   cdb1 OPEN 0
   

   

    
    

• CON_ID 컬럼은 위와 같은 규칙으로 번호가 부여된다.

   
   

  SQL> select cdb from v$database;
  

   
   

  CDB
  

  ——
  

  YES
  

   
   

  SQL> column name format a8;
  

  SQL> select name,con_id,dbid,con_uid,guid from v$containers order by con_id;
  

   
   

  NAME CON_ID DBID CON_UID GUID
  

  ——– ———- ———- ———- ——————————–
  

  CDB$ROOT 1 822464397 1 FA6D34E1B2670B5BE0430100007F1063
  

  PDB$SEED 2 4091349325 4091349325 FA6D34E1B2660B5BE0430100007F1063
  

  PDB11 3 3833008008 3833008008 FA811565F8BA0989E0430100007FF65C
  

  PDB12 4 4037515505 4037515505 FA811565F8BB0989E0430100007FF65C
  

   
   

• CDB일 때

  SQL> select name,con_id from v$active_services order by 1;
  

   
   

  NAME CON_ID
  

  ——– ———-
  

  SYS$BACK 1
  

  GROUND
  

   
   

  SYS$USER 1
  

  S
  

   
   

  cdb1 1
  

  cdb1XDB 1
  

  pdb11 3
  

  pdb12 4
  

   
   

  6 rows selected.
  

   
   

• PDB일 때

  SQL> select name,con_id from v$active_services order by 1;
  

   
   

  NAME CON_ID
  

  ——– ———-
  

  pdb11 3
  

   
   

   
   

  

   
   

• CDB에는 모든 DBA_ view에 상응하는 CDB_ view가 존재합니다
• 현재 CDB에 접속해 있다면 CDB_ view를 통하여 root와 PDBs 의 metadata를 볼 수 있습니다.
• PDB에 접속해 있다면 PDB의 정보만 볼 수 있습니다

   
   

1. DATABASE FILE 살펴보기
   

   SQL> COL MEMBER FORMAT A40
   

   SQL> SELECT GROUP#, CON_ID, MEMBER FROM V$LOGFILE;
   

    
    

   GROUP# CON_ID MEMBER
   

   ———- —— —————————————-
   

   1 0 /oradata/cdb1/redo01.log
   

   2 0 /oradata/cdb1/redo02.log
   

   3 0 /oradata/cdb1/redo03.log
   

    
    

   SQL> COL NAME FORMAT A60;
   

   SQL> SELECT NAME FROM V$CONTROLFILE;
   

    
    

   NAME
   

   ————————————————————
   

   /oradata/cdb1/control01.ctl
   

   /oradata/cdb1/control02.ctl
   

    
    

   SQL> set line 140 pages 1000
   

   SQL> col TABLESPACE_NAME for a10
   

   SQL> COL FILE_NAME FORMAT A60
   

   SQL> SELECT FILE_NAME, TABLESPACE_NAME, FILE_ID FROM CDB_TEMP_FILES;
   

    
    

   FILE_NAME TABLESPACE FILE_ID
   

   ———————————————————— ———- ———-
   

   /oradata/cdb1/temp01.dbf TEMP 1
   

   /oradata/cdb1/PDB11/temp01.dbf TEMP 3
   

   /oradata/cdb1/PDB12/temp01.dbf TEMP 4
   

   /oradata/cdb1/pdbseed/temp01.dbf TEMP 2
   

    
    

• Redo log, Controlfile은 CDB, PDB 모두 같이 사용하게 된다.
• CDB Temp Tablespace의 경우 모든 PDB가 다 같이 사용하지만, 각 PDB 만을 위한 local temporary tablespace를

  만들어 사용할 수 있다
  

   
   

  SQL> COL FILE_NAME FORMAT A50
  

  SQL> COL TABLESPACE_NAME FORMAT A8
  

  SQL> COL FILE_ID FORMAT 9999
  

  SQL> COL FILE_NAME FORMAT A50
  

  SQL> COL TABLESPACE_NAME FORMAT A8
  

  SQL> COL CON_ID FORMAT 999
  

  SQL> SELECT FILE_NAME, TABLESPACE_NAME, FILE_ID, CON_ID FROM CDB_DATA_FILES ORDER BY CON_ID;
  

   
   

  FILE_NAME TABLESPA FILE_ID CON_ID
  

  ————————————————– ——– ——- ——
  

  /oradata/cdb1/system01.dbf SYSTEM 1 1
  

  /oradata/cdb1/sysaux01.dbf SYSAUX 3 1
  

  /oradata/cdb1/undotbs01.dbf UNDOTBS1 5 1
  

  /oradata/cdb1/users01.dbf USERS 6 1
  

  /oradata/cdb1/pdbseed/sysaux01.dbf SYSAUX 4 2
  

  /oradata/cdb1/pdbseed/system01.dbf SYSTEM 2 2
  

  /oradata/cdb1/PDB11/system01.dbf SYSTEM 7 3
  

  /oradata/cdb1/PDB11/sysaux01.dbf SYSAUX 8 3
  

  /oradata/cdb1/PDB12/system01.dbf SYSTEM 9 4
  

  /oradata/cdb1/PDB12/sysaux01.dbf SYSAUX 10 4
  

   
   

  10 rows selected.
  

# CDB, PDB 시작, 중지

1. CDB SHUTDOWN & STARTUP
   

• CDB는 NON-CDB와 같이 shutdown 명령어로 내릴 수 있다.
• CDB를 shutdown하게 되면 PDB들은 자동으로 닫히게 된다.
• CDB가 시작되어도 PDB는 자동으로 시작되지 않는다.

   
   

  SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;

   
   

  NAME CDB CON_ID

  —————— —— ———-

  CDB1 YES 0

   
   

  SQL> SHUTDOWN IMMEDIATE;

  Database closed.

  Database dismounted.

  ORACLE instance shut down.

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

  SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS

  *

  ERROR at line 1:

  ORA-01034: ORACLE not available

  Process ID: 0

  Session ID: 0 Serial number: 0

   
   

   
   

  SQL> STARTUP NOMOUNT;

  ORACLE instance started.

   
   

  Total System Global Area 835104768 bytes

  Fixed Size 2293880 bytes

  Variable Size 595595144 bytes

  Database Buffers 234881024 bytes

  Redo Buffers 2334720 bytes

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  no rows selected

   
   

  SQL> ALTER DATABASE MOUNT;

   
   

  Database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED MOUNTED

  3 PDB11 MOUNTED

  4 PDB12 MOUNTED

   
   

  SQL> ALTER PLUGGABLE DATABASE PDB11 OPEN;

  ALTER PLUGGABLE DATABASE PDB11 OPEN

  *

  ERROR at line 1:

  ORA-01109: database not open

   
   

   
   

  SQL> ALTER DATABASE OPEN;

   
   

  Database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED READ ONLY

  3 PDB11 MOUNTED

  4 PDB12 MOUNTED

   
   

  SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

   
   

  Pluggable database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED READ ONLY

  3 PDB11 READ WRITE

  4 PDB12 READ WRITE

   
   

1. PDB OPEN and CLOSE
   

• ALTER PLUGGABLE DATABASE 구문
  또는 SQL*PLUS STARTUP 명령어로 PDB를
  컨트롤
  할
  수
  있음
• CDB에서
  실시

  SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;

   
   

  NAME CDB CON_ID

  ——————————————— —— ———-

  CDB1 YES 0

   
   

  SQL> STARTUP FORCE;

  ORACLE instance started.

   
   

  Total System Global Area 835104768 bytes

  Fixed Size 2293880 bytes

  Variable Size 595595144 bytes

  Database Buffers 234881024 bytes

  Redo Buffers 2334720 bytes

  Database mounted.

  Database opened.

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED READ ONLY

  3 PDB11 MOUNTED

  4 PDB12 MOUNTED

   
   

  SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

   
   

  Pluggable database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED READ ONLY

  3 PDB11 READ WRITE

  4 PDB12 READ WRITE

   
   

  SQL> ALTER PLUGGABLE DATABASE PDB11 CLOSE;

   
   

  Pluggable database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED READ ONLY

  3 PDB11 MOUNTED

  4 PDB12 READ WRITE

   
   

  SQL> CONNECT SYS/oracle@PDB11

  ERROR:

  ORA-01033: ORACLE initialization or shutdown in progress

  Process ID: 0

  Session ID: 0 Serial number: 0

   
   

   
   

  SQL> CONNECT SYS/oracle@CDB1 AS SYSDBA;

  Connected.

  SQL> ALTER PLUGGABLE DATABASE PDB11 OPEN;

   
   

  Pluggable database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED READ ONLY

  3 PDB11 READ WRITE

  4 PDB12 READ WRITE

   
   

  SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT PDB12 CLOSE;

   
   

  Pluggable database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED READ ONLY

  3 PDB11 MOUNTED

  4 PDB12 READ WRITE

   
   

  SQL> ALTER PLUGGABLE DATABASE ALL CLOSE;

   
   

  Pluggable database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED READ ONLY

  3 PDB11 MOUNTED

  4 PDB12 MOUNTED

   
   

  SQL> ALTER PLUGGABLE DATABASE ALL EXCEPT PDB11 OPEN;

   
   

  Pluggable database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED READ ONLY

  3 PDB11 MOUNTED

  4 PDB12 READ WRITE

   
   

  SQL> STARTUP PLUGGABLE DATABASE PDB11 OPEN READ ONLY;

  Pluggable Database opened.

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED READ ONLY

  3 PDB11 READ ONLY

  4 PDB12 READ WRITE

   
   

  SQL> STARTUP PLUGGABLE DATABASE PDB11 FORCE;

  Pluggable Database opened.

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED READ ONLY

  3 PDB11 READ WRITE

  4 PDB12 READ WRITE

   
   

  SQL> ALTER PLUGGABLE DATABASE PDB11 CLOSE;

   
   

  Pluggable database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  2 PDB$SEED READ ONLY

  3 PDB11 MOUNTED

  4 PDB12 READ WRITE

   
   

• PDB에서
  실시

  SQL> CONNECT SYS/oracle@PDB11 AS SYSDBA;

  Connected.

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  3 PDB11 MOUNTED

   
   

  SQL> STARTUP;

  Pluggable Database opened.

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  3 PDB11 READ WRITE

   
   

  SQL> SHUTDOWN IMMEDIATE;

  Pluggable Database closed.

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  3 PDB11 MOUNTED

   
   

  SQL> STARTUP OPEN READ ONLY;

  Pluggable Database opened.

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  3 PDB11 READ ONLY

   
   

  SQL> STARTUP FORCE;

  Pluggable Database opened.

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  3 PDB11 READ WRITE

   
   

  SQL> ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

   
   

  Pluggable database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  3 PDB11 MOUNTED

   
   

  SQL> ALTER PLUGGABLE DATABASE OPEN READ ONLY;

   
   

  Pluggable database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  3 PDB11 READ ONLY

   
   

  SQL> ALTER PLUGGABLE DATABASE OPEN FORCE;

   
   

  Pluggable database altered.

   
   

  SQL> SELECT CON_ID, NAME, OPEN_MODE FROM V$PDBS;

   
   

  CON_ID NAME OPEN_MODE

  ———- ——————————————— ——————–

  3 PDB11 READ WRITE