- [PDB管理](#pdb管理)
  - [PDB檢查](#pdb檢查)
  - [PDB建立](#pdb建立)
  - [PDB閃回](#pdb閃回)
- [表空間管理](#表空間管理)
- [使用者管理](#使用者管理)
- [AWR](#awr)
  - [awr io收集](#awr-io收集)

### PDB管理

#### PDB檢查
``` sql
--檢視pdb資訊   or dba_pdbs
set lines 160
col name for a20
col open_time for a40
select con_id,dbid,name,open_mode,open_time,creation_time from v$pdbs;

--檢視CDB部分資訊
set lines 160
col con_name for a30
select c.con_id,c.name con_name,t.tablespace_name,t.contents,t.status 
from v$containers c,cdb_tablespaces t where c.con_id=t.con_id and t.contents='UNDO' 
order by 1,2;

--跟隨CDB啟動
alter pluggable database firsoul01 save state;

--檢視每個pdb大小
select c.name,round(sum(s.bytes/1024/1024/1024),2) size_gb 
from cdb_segments s,v$containers c where c.con_id=s.con_id group by c.name order by 2 desc;

--連線 or tnsnames
alter session set container=orclpdb;

--檢視pdb的服務名
col pdb for a15
col name for a20
col network_name for a20
SELECT PDB, name,NETWORK_NAME, CON_ID FROM CDB_SERVICES
WHERE PDB IS NOT NULL AND CON_ID > 2 ORDER BY PDB;

--檢視歷史pdb資訊
col db_name for a15
col pdb_name for a15
col CLONED_FROM_PDB_NAME for a20
SELECT DB_NAME, CON_ID, pdb_id,PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME  
FROM CDB_PDB_HISTORY  WHERE CON_ID<>pdb_id and pdb_id>2 
and db_name=(select name  from v$database) ORDER BY PDB_ID;
```

#### PDB建立
```sql
--建立一個預設表空間USERS，roles預設為pdb_dba,與dbca方式類似，dbca方式會自動open 該pdb。
create pluggable database pdb3 admin user pdbadmin identified by pdbadmin roles=(dba)  default tablespace users datafile '+data' size 250M autoextend on;

--沒有預設表空間 USERS，此時資料庫分配SYSTEM表空間為pdb預設表空間
create pluggable database pdb1 admin user pdbadmin identified by pdbadmin;

--指定pdb檔案位置
create pluggable database pdb4 admin user pdbadmin identified by pdbadmin roles=(dba)  default tablespace users datafile '/disk1/oracle/dbs/pdb4/users01.dbf' size 250M autoextend on 
FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdbseed/', '/disk1/oracle/dbs/pdb4/')
PATH_PREFIX = '/disk1/oracle/dbs/pdb4/';

--從已存在的pdb克隆
CREATE PLUGGABLE DATABASE newpdb FROM pdb4
  FILE_NAME_CONVERT = ('/disk1/oracle/dbs/pdb4/','/disk1/oracle/dbs/newpdb/')
  PATH_PREFIX = '/disk1/oracle/dbs/newpdb';

--遠端克隆
create pluggable database pdb8 from
mypdb@clonePdb FILE_NAME_CONVERT=('/u01/app/oracle/oradata/mypdb','/u01/app/oracle/oradata/TESTCDB/pdb8');

--檢視 pdb_dba許可權,也就是pdbadmin具有建立pdb的許可權
col granted_role for a20
select granted_role,ADMIN_OPTION from dba_role_privs where grantee='PDBADMIN';

select * from DBA_SYS_PRIVS where grantee='PDB_DBA';

--刪除PDB
drop pluggable database pdb1 including datafiles;
```
#### PDB閃回
``` sql
--開啟
alter system set db_recovery_file_dest_size=2g;
alter system set db_recovery_file_dest='+ARCH';
alter database flashback on;

--檢視閃回日誌保留時間
show parameter flashback

--檢查閃回日誌
select file_type,percent_space_used,number_of_files from v$recovery_area_usage;

```

> 閃回資料庫，是從資料檔案的當前狀態開始，然後應用閃回日誌，將它們回滾到過去的某一個狀態。

還原點
``` sql
--建立還原點,如果未開閃回，可使用guarantee flashback database 宣告還原點
create restore point before_release guarantee flashback database;

--檢視
list restore point all;  --無法辨別哪個pdb
--or
set lines 200
col time for a20
col name for a20
col cname for a15
select r.scn, r.DATABASE_INCARNATION# db_INCARNATION,to_char(r.time,'yyyy-mm-dd hh24:mi:ss') time,
r.GUARANTEE_FLASHBACK_DATABASE GUARANTEE,r.name,r.PDB_RESTORE_POINT,r.CLEAN_PDB_RESTORE_POINT,c.name cname
from v$restore_point r,v$containers c where r.con_id=c.con_id
union all
select r.scn, r.DATABASE_INCARNATION# db_INCARNATION,to_char(r.time,'yyyy-mm-dd hh24:mi:ss') time,
r.GUARANTEE_FLASHBACK_DATABASE GUARANTEE,r.name,r.PDB_RESTORE_POINT,r.CLEAN_PDB_RESTORE_POINT,'cdb' as cname
from v$restore_point r where r.con_id=0;

--刪除
drop restore point before_release;
```
**resetlogs pdb**
``` sql
--對pdb進行閃回,resetlogs  redo不會重建，繼續記錄例項中所有修改操作引起的變化
flashback pluggable database pdb1 to restore point regtest1;
alter pluggable database pdb1 open resetlogs;

--每個pdb以resetlogs方式開啟，都會建立一個incarnation,可以透過以下檢視
select CON_ID,db_incarnation#,pdb_incarnation#,status,incarnation_time from v$pdb_incarnation
order by con_id;
```

### 表空間管理
``` sql
--檢視錶空間
break on name skip 1
col name for a20
col tablespace_name for a30
set linesize 200 pagesize 500
select c.name name,f.tablespace_name tablespace_name,
round((d.sumbytes/1024/1024/1024),2) total_without_extend_GB,
round(((d.sumbytes+d.extend_bytes)/1024/1024/1024),2) total_with_extend_GB,
round((f.sumbytes+d.Extend_bytes)/1024/1024/1024,2) free_with_extend_GB,
round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_GB,
round((d.sumbytes-f.sumbytes)*100/(d.sumbytes+d.extend_bytes),2) used_percent_with_extend
from (select con_id,tablespace_name,sum(bytes) sumbytes from cdb_free_space group by con_id,tablespace_name) f, 
(select con_id,tablespace_name,sum(aa.bytes) sumbytes,sum(aa.extend_bytes) extend_bytes from
 (select  con_id,nvl(case  when autoextensible ='YES' then (case when (maxbytes-bytes)>=0 then (maxbytes-bytes) end) end,0) Extend_bytes
,tablespace_name,bytes  from cdb_data_files) aa group by con_id,tablespace_name) d,v$containers c
where  (f.con_id=d.con_id and f.tablespace_name=d.tablespace_name) and f.con_id=c.con_id
order by  name,used_percent_with_extend desc;

--檢視所有資料檔案
break on cdbname skip 1
set lines 200 pages 999
col cdbname for a10
col dbfile for a80
select c.name cdbname,d.file# dfile,d.name dbfile,round(f.bytes/1024/1024/1024,2) size_gb,f.AUTOEXTENSIBLE ,d.CREATION_TIME from v$datafile d,v$containers c,cdb_data_files f where c.con_id=d.con_id and d.name=f.file_name order by cdbname,d.creation_time;
```

### 使用者管理
``` sql
--檢視每個pdb下使用者大小
break on pname skip 1
col owner for a20
col pname for a15
select c.name pname,s.owner,round(sum(s.bytes/1024/1024/1024),2) size_gb 
from cdb_segments s,v$containers c 
where c.con_id=s.con_id group by c.name,s.owner order by 1,3 desc;

--查詢使用者名稱、鎖定狀態、表空間、配置檔案、許可權---
break on cname skip 1
set pagesize 999
set linesize 150
col cname for a20
col username for a25
col ACCOUNT_STATUS for a20
col DEFAULT_TABLESPACE for a20
col PROFILE for a20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select v.name cname,c.username,',',c.ACCOUNT_STATUS,','
,c.DEFAULT_TABLESPACE,',',c.PROFILE,c.CREATED 
from cdb_users c,v$containers v where c.con_id=v.con_id order by v.name,c.created;
```

### AWR
#### awr io收集
``` sql
set line 200
col table_name format a40
select *   from dba_hist_table_settings
where table_name in ('WRH$_FILESTATXS','WRH$_DATAFILE','Tempfile Group','WRH$_TEMPSTATXS');

exec dbms_workload_repository.modify_table_settings(table_name  => 'WRH$_FILESTATXS', flush_level => 'TYPICAL');
exec dbms_workload_repository.modify_table_settings(table_name  => 'WRH$_DATAFILE', flush_level => 'TYPICAL');
exec dbms_workload_repository.modify_table_settings(table_name  => 'Tempfile Group', flush_level => 'TYPICAL');
exec dbms_workload_repository.modify_table_settings(table_name  => 'WRH$_TEMPSTATXS', flush_level => 'TYPICAL');

```

【PDB】Oracle pdb維護常用sql命令

相關文章