<!-- @import "[TOC]" {cmd="toc" depthFrom=1 depthTo=6 orderedList=false} --> <!-- code_chunk_output --> - [PDB管理](#pdb管理) - [PDB检查](#pdb检查) - [PDB创建](#pdb创建) - [PDB闪回](#pdb闪回) - [表空间管理](#表空间管理) - [用户管理](#用户管理) - [AWR](#awr) - [awr io收集](#awr-io收集) <!-- /code_chunk_output --> ### 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'); ```