********************監聽********************
lsnrctl
start/stop/status
********************CDB/PDB********************
sqlplus / as
sysdba
startup
shutdown
immediate
show
con_name
show pdbs
alter pluggable
database all open;
alter pluggable
database pdbjcdb close immediate;
alter session set
container=pdbjcdb;
********************導入導出********************
alter session set
container=pdbjcdb;
CREATE DIRECTORY
DataPump_Dir AS '/Backup/jcdb/DataPump/pdbjcdb';
col OWNER for
a8;
col DIRECTORY_NAME for
a15;
col DIRECTORY_PATH for
a40;
col ORIGIN_CON_ID for
9999;
SELECT * FROM
dba_directories;
grant read, write on
directory datapump_dir to system;
expdp
system/[email protected] full=y logtime=all DIRECTORY=DATAPUMP_DIR
JOB_NAME=expdp_pdbjcdb_p2_job DUMPFILE=parallel_pdbjcdb_%U.dmp
LOGFILE=parallel_pdbjcdb_expdp.log PARALLEL=2
impdp
system/[email protected] SCHEMAS=zhouzzz DIRECTORY=DataPump_Dir
JOB_NAME=impdp_pdbjcdb_p2_job DUMPFILE=parallel_pdbjcdb_%U.dmp
logtime=all LOGFILE=parallel_pdbjcdb_impdp.log
PARALLEL=2
********************SQL********************
show parameter
parameter_name
select username from
all_users/dba_users;
1.啟動、關閉資料庫
$ lsnrctl
start
sqlplus /
as sysdba
[email protected]>
startup
[email protected]>
shutdown immediate
2.啟動PDB
[email protected]>
alter pluggable database all
open;
alter
pluggable database pdbjcdb open;
alter
pluggable database pdbjcdb close immediate;
檢視
show con_name
CON_NAME
------------------------------
CDB$ROOT
alter session
set container=pdbjcdb;
show pdbs;
CON_ID
CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------
---------- ----------
2
PDB$SEED READ ONLY NO
3
PDBJCDB READ WRITE NO
3.從模闆建立PDB
[email protected]> create pluggable database "PDB123" admin user
"PDBADMIN" identified by 123456;
[email protected]> alter pluggable database PDB123 open read
write;
[email protected]> alter session set container=pdb123;
[email protected]> show pdbs
CON_ID CON_NAME OPEN
MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBJCDB READ WRITE NO
4 PDB123 READ WRITE NO
删除PDB(從CDB$ROOT session下删除其他pdb)
[email protected]> alter pluggable database pdb123 close
immediate;
[email protected]> drop pluggable database pdb123 including
datafiles;
[email protected]> show
pdbs;
CON_ID CON_NAME OPEN
MODE RESTRICTED
----------
------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDBJCDB READ WRITE NO
4.以PDB登入EM報錯彈窗
解決方法
檢視em
[email protected]> WITH
2 FUNCTION b2vc (in_bool_expr
VARCHAR2)
3 RETURN VARCHAR2
4 IS
5 l_bool BOOLEAN;
6 l_plsql VARCHAR2(32767);
7 l_ret VARCHAR2(5);
8 BEGIN
9 l_plsql :=
'BEGIN :l_bool := ' || in_bool_expr || '; END;';
10 EXECUTE IMMEDIATE l_plsql
USING OUT l_bool;
11 IF l_bool IS NOT NULL
THEN
12 IF l_bool
THEN
13 l_ret := 'TRUE';
14 ELSE
15 l_ret := 'FALSE';
16 END
IF;
17 END IF;
18 RETURN l_ret;
19 END
b2vc;
20 SELECT
b2vc('DBMS_XDB_CONFIG.ISGLOBALPORTENABLED') FROM dual;
21 /
B2VC('DBMS_XDB_CONFIG.ISGLOBALPORTENABLED')
-------------------------------------------------------------------------------------------
FALSE
open pdb再次登入檢視變為TRUE，就可以登入了。
5.建立使用者報錯
建立新使用者，CDB容器中建立一個通用使用者，使用者名必須以C##或者c##開頭，因為CDB中預設建立的是common
user。如果想要建立本地使用者，則要在PDB容器中建立，切換進PDB
建立使用者
··········································································
[email protected]> show
con_name;
CON_NAME
CDB$ROOT
[email protected]> create user
zhouzzz identified by 123456;
create user zhouzzz identified
by 123456
*
ERROR at line 1:
ORA-65096: invalid common user
or role name
[email protected]> create user c##zhouzzz identified by
123456;
User created
···········································································
[email protected]> alter
session set container=pdbjcdb;
Session
altered.
[email protected]> show
con_name;
CON_NAME
PDBJCDB
[email protected]>
create user zhowwww identified by
123456;
User
created.
授權
grant connect,resource to c##zhouzzz
container=all;
[[email protected] ~]$ sqlplus
c##zhouzzz/123456;
C##[email protected]>
select
GRANTEE,con_id from cdb_ROLE_PRIVS where GRANTED_ROLE='CONNECT' AND
GRANTEE='C##ZHOUZZZ';
GRANTEE CON_ID
C##ZHOUZZZ 1
删除使用者
drop user c##zhouzzz cascade;