ORACLE 12C探索之路03(創(chuàng)建PDB)
- SQLcl 連接數(shù)據(jù)庫
[oracle@XAG143 ~]$ sql sys/123456@192.168.40.143:1521/MYCDB as sysdba
SQLcl: Release 12.2.0.1.0 RC on Fri Nov 02 19:21:20 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> help set SQLFORMAT
SET SQLFORMAT
SET SQLFORMAT { csv,html,xml,json,ansiconsole,insert,loader,fixed,default}
SQL> SET SQLFORMAT ansiconsole
SQL> SELECT name,DECODE(cdb,'YES', 'Multitenant Option enabled','Regular 12c Database:') as "Multitenant Option",open_mode,con_id
2 FROM v$database;
NAME Multitenant Option OPEN_MODE CON_ID
MYCDB Multitenant Option enabled READ WRITE 0
SQL> select con_id,name,open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
2 PDB$SEED READ ONLY
- 基于PDB$SEED演示創(chuàng)建PDB
SQL> alter system set db_create_file_dest='/u02/oradata/';
System SET altered.
SQL> create pluggable database MYPDB1 admin user admin identified by 123456 roles=(connect) file_name_convert=('/u02/oradata/MYCDB/pdbseed','/u02/oradata/MYCDB/MYPDB1');
- 查看創(chuàng)建后的狀態(tài)蛛勉，剛剛創(chuàng)建的pdb status列為NEW
SQL> select pdb_id,pdb_name,status,creation_time from cdb_pdbs;
PDB_ID PDB_NAME STATUS CREATION_TIME
3 MYPDB1 NEW 02-NOV-18
2 PDB$SEED NORMAL 31-OCT-18
#如下查詢，當(dāng)前的數(shù)據(jù)庫處于mount狀態(tài)
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
2 988450796 PDB$SEED READ ONLY
3 2853971070 MYPDB1 MOUNTED
- 將pdb數(shù)據(jù)庫切換到open狀態(tài)
SQL> alter pluggable database MYPDB1 open;
or
SQL>alter session set container=MYPDB1 ;
SQL> startup;
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
2 988450796 PDB$SEED READ ONLY
3 2853971070 MYPDB1 READ WRITE
- 使用公共用戶sys連接到剛剛創(chuàng)建的pdb數(shù)據(jù)庫
SQL> conn sys/123456@192.168.40.143:1521/MYPDB1 as sysdba;
Connected.
SQL> show user;
USER is "SYS"
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 MYPDB1 READ WRITE NO
- UNDO梳理
SQL> select tablespace_name,file_name,bytes/1024/1024 mb from dba_temp_files union all select tablespace_name,file_name,bytes/1024/1024 mb from dba_data_files;
TABLESPACE_NAME FILE_NAME MB
TEMP /u02/oradata/MYCDB/MYPDB1/temp012018-10-31_00-49-33-672-AM.dbf 64
UNDOTBS1 /u02/oradata/MYCDB/MYPDB1/undotbs01.dbf 100
SYSAUX /u02/oradata/MYCDB/MYPDB1/sysaux01.dbf 350
SYSTEM /u02/oradata/MYCDB/MYPDB1/system01.dbf 250
#此處為測試環(huán)境故設(shè)置100M，生產(chǎn)環(huán)境要放大10G以上
SQL> ALTER DATABASE DATAFILE '/u02/oradata/MYCDB/MYPDB1/undotbs01.dbf' RESIZE 100M;
SQL> ALTER DATABASE DATAFILE '/u02/oradata/MYCDB/MYPDB1/undotbs01.dbf' AUTOEXTEND ON NEXT 50M;
- TEMP 梳理
CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE
'/u02/oradata/MYCDB/MYPDB1/TEMP1_1.dbf' SIZE 256M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 1G,
'/u02/oradata/MYCDB/MYPDB1/TEMP1_2.dbf' SIZE 256M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER TABLESPACE TEMP1 TABLESPACE GROUP TEMP_GP;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GP;
#如無法刪除則需重啟db
drop tablespace TEMP including contents and datafiles;
select * from dba_tablespace_groups;
- 整理用戶表空間
create tablespace MYPDB1_UD datafile
'/u02/oradata/MYCDB/MYPDB1/MYPDB1_UD1.dbf' size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,
'/u02/oradata/MYCDB/MYPDB1/MYPDB1_UD2.dbf' size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
Create tablespace I_MYPDB1_UD datafile
'/u02/oradata/MYCDB/MYPDB1/I_MYPDB1_UD1.dbf' size 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED,
'/u02/oradata/MYCDB/MYPDB1/I_MYPDB1_UD2.dbf' size 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
ALTER DATABASE DEFAULT TABLESPACE MYPDB1_UD;
drop tablespace USERS including contents and datafiles;
- 對用戶授權(quán)
GRANT DBA to ADMIN;
grant connect,resource,unlimited tablespace to ADMIN;
grant create any directory to ADMIN;
grant drop any directory to ADMIN;
#修改密碼(可選)
alter user ADMIN identified by "xag123";
#賬戶鎖定后解鎖命令(可選) sys or system
alter user ADMIN account unlock;
#設(shè)置用戶密碼無限次嘗試登錄
alter profile default limit failed_login_attempts unlimited;
#設(shè)置用戶密碼不過期：
alter profile default limit password_life_time unlimited;
#查看配置的參數(shù)
select profile,RESOURCE_NAME,resource_type,limit from dba_profiles where
RESOURCE_NAME in('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME') and profile='DEFAULT';
- 使用pdb本地管理員賬戶連接到pdb數(shù)據(jù)庫
SQL> conn admin/xag123@192.168.40.143:1521/MYPDB1
Connected.
SQL> show user;
USER is "ADMIN"
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
3 MYPDB1 READ WRITE YES
#查看當(dāng)前用戶的角色
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADMIN_OPTION DELEGATE_OPTION DEFAULT_ROLE OS_GRANTED COMMON INHERITED
ADMIN CONNECT NO NO YES NO NO NO
ADMIN DBA NO NO YES NO NO NO
ADMIN PDB_DBA YES NO YES NO NO NO
ADMIN RESOURCE NO NO YES NO NO NO
#查看當(dāng)前用戶的權(quán)限
SQL> select * from session_privs;
#查看當(dāng)前的默認(rèn)表空間
SQL> SELECT PROPERTY_VALUE FROM database_properties WHERE PROPERTY_NAME ='DEFAULT_PERMANENT_TABLESPACE';
PROPERTY_VALUE
MYPDB1_UD
- 創(chuàng)建新用戶
[oracle@XAG143 ~]$ sql admin/xag123@192.168.40.143:1521/MYPDB1
SQL> CREATE USER OSSUSER IDENTIFIED BY "123456" DEFAULT TABLESPACE MYPDB1_UD TEMPORARY TABLESPACE TEMP_GP;
SQL> GRANT DBA to OSSUSER;
SQL> conn OSSUSER/123456@192.168.40.143:1521/MYPDB1
- CDB下查詢默認(rèn)表空間
[oracle@XAG143 ~]$ sql sys/123456@192.168.40.143:1521/MYCDB as sysdba
SQLcl: Release 12.2.0.1.0 RC on Fri Nov 02 20:27:18 2018
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> history 38
1* SELECT PROPERTY_VALUE FROM database_properties WHERE PROPERTY_NAME ='DEFAULT_PERMANENT_TABLESPACE'
SQL> /
PROPERTY_VALUE
-----------------------------------
XAG_UD
