1.環境檢查（源庫）
sql / as sysdba
SET SQLFORMAT ansiconsole
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 PDB2 READ WRITE NO
SELECT property_name, property_value
FROM database_properties
WHERE property_name = 'LOCAL_UNDO_ENABLED';
------------------------------------------------------
PROPERTY_NAME PROPERTY_VALUE
LOCAL_UNDO_ENABLED TRUE
SQL> SELECT log_mode FROM v$database;
LOG_MODE
ARCHIVELOG
2.UNDO梳理
sql sys/123456@xag224:1521/pdb2 as sysdba
SET SQLFORMAT ansiconsole
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 /home/oradata/MPCDB/pdb2/temp01.dbf 36
SYSTEM /home/oradata/MPCDB/pdb2/system01.dbf 270
SYSAUX /home/oradata/MPCDB/pdb2/sysaux01.dbf 330
UNDOTBS1 /home/oradata/MPCDB/pdb2/undotbs01.dbf 105
USERS /home/oradata/MPCDB/pdb2/users01.dbf 5
#此处为测试环境故设置100M，生产环境要放大10G以上
ALTER DATABASE DATAFILE '/home/oradata/MPCDB/pdb2/undotbs01.dbf' RESIZE 200M;
ALTER DATABASE DATAFILE '/home/oradata/MPCDB/pdb2/undotbs01.dbf' AUTOEXTEND ON NEXT 50M;
3.TEMP 梳理
CREATE TEMPORARY TABLESPACE TS TEMPFILE
'/home/oradata/MPCDB/pdb2/TS_1.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 1G,
'/home/oradata/MPCDB/pdb2/TS_2.dbf' SIZE 64M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER TABLESPACE TS TABLESPACE GROUP TS_GP;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TS_GP;
#如无法删除则需重启db
drop tablespace TEMP including contents and datafiles;
select * from dba_tablespace_groups;
4.整理用户表空间
create tablespace US datafile
'/home/oradata/MPCDB/pdb2/US_1.dbf' size 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED,
'/home/oradata/MPCDB/pdb2/US_2.dbf' size 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
extent management local segment space management auto;
ALTER DATABASE DEFAULT TABLESPACE US;
drop tablespace USERS including contents and datafiles;
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
TS /home/oradata/MPCDB/pdb2/TS_1.dbf 64
TS /home/oradata/MPCDB/pdb2/TS_2.dbf 64
SYSTEM /home/oradata/MPCDB/pdb2/system01.dbf 270
SYSAUX /home/oradata/MPCDB/pdb2/sysaux01.dbf 330
UNDOTBS1 /home/oradata/MPCDB/pdb2/undotbs01.dbf 200
US /home/oradata/MPCDB/pdb2/US_1.dbf 32
US /home/oradata/MPCDB/pdb2/US_2.dbf 32
5.创建新用户
CREATE USER xag IDENTIFIED BY "123456" DEFAULT TABLESPACE US TEMPORARY TABLESPACE TS_GP;
GRANT DBA to xag;
#修改密码(可选)
alter user xag identified by "xag123";
#账户锁定后解锁命令(可选) sys or system
alter user xag account unlock;
#设置用户密码无限次尝试登录
alter profile default limit failed_login_attempts unlimited;
#设置用户密码不过期：
alter profile default limit password_life_time unlimited;
#查看配置的参数
select profile,RESOURCE_NAME,resource_type,limit from dba_profiles where
RESOURCE_NAME in('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME') and profile='DEFAULT';
創建測試自動插入job(system用户下)
begin
dbms_network_acl_admin.create_acl
(
acl => 'utl_http.xml', -- 文件名称
description => 'HTTP Access', -- 描述
principal => 'XAG', -- 授权或者取消授权账号，大小写敏感
is_grant => TRUE, -- 授权还是取消授权
privilege => 'connect', -- 授权或者取消授权的权限列表
start_date => null, -- 起始日期
end_date => null -- 结束日期
);
end;
/
begin
dbms_network_acl_admin.add_privilege ( -- 添加访问权限列表项
acl => 'utl_http.xml', -- 刚才创建的acl名称
principal => 'XAG', -- 授权或取消授权用户
is_grant => TRUE, -- 与上同
privilege => 'resolve', -- 权限列表
start_date => null,
end_date => null
);
end;
/
begin
dbms_network_acl_admin.assign_acl(acl => 'utl_http.xml', host =>'*');
end;
/
創建測試job（xag下）
SQL> show user;
USER is "SYS"
SQL> conn xag/123456@xag224:1521/pdb2;
SQL> show user;
USER is "XAG"
create table my_healt_check
as
select utl_inaddr.get_host_address as host_address
,utl_inaddr.get_host_name as host_name
,sys_context('USERENV','CON_NAME') as db_name
,sysdate as create_time from dual;
create or replace procedure pr_my_healt_check_insert as
begin
delete from my_healt_check a where a.create_time<sysdate-1/24;
insert into my_healt_check(host_address,host_name,db_name,create_time)
select utl_inaddr.get_host_address,utl_inaddr.get_host_name,sys_context('USERENV','CON_NAME'),sysdate from dual;
commit;
end;
/
Declare job1 Number;
Begin
dbms_job.submit(job1,'pr_my_healt_check_insert;',Sysdate,'sysdate + (1/(24*60))');
Commit;
End;
/
or
declare
v_count int := 0;
begin
select count(*) into v_count from user_scheduler_jobs where job_name='TEST_JOB1';
if v_count > 0 then
dbms_scheduler.drop_job('HEALT_CHECK');
end if;
dbms_scheduler.create_job (
job_name => 'healt_check',
job_type => 'STORED_PROCEDURE',
job_action => 'pr_my_healt_check_insert',
start_date => sysdate,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
AUTO_DROP => FALSE,
enabled => true
);
end;
/
CREATE OR REPLACE VIEW V_XAG_JOB1 AS
SELECT job,to_char(LAST_DATE,'yyyy-mm-dd hh24:mi:ss') as last_date
,to_char(next_date,'yyyy-mm-dd hh24:mi:ss') as next_date,to_char(total_time,9999999999) as total_time
,to_char(case when failures is null then 0 else failures end,9999999999) as failures
,broken,what,interval FROM user_jobs order by job;
CREATE OR REPLACE VIEW V_XAG_JOB2 AS
SELECT JOB_NAME,JOB_ACTION job,to_char(START_DATE,'yyyy-mm-dd hh24:mi:ss') as START_DATE
,REPEAT_INTERVAL,ENABLED,to_char(LAST_START_DATE,'yyyy-mm-dd hh24:mi:ss') as LAST_START_DATE
,to_char(NEXT_RUN_DATE,'yyyy-mm-dd hh24:mi:ss') as NEXT_RUN_DATE
,COMMENTS
FROM user_scheduler_jobs order by JOB_NAME;
select * from V_XAG_JOB1
select * from V_XAG_JOB2
select host_address ,host_name,db_name, to_char(create_time,'hh24:mi:ss')
from my_healt_check order by create_time desc;
DB启动时 open all pdb (sys 下)
CREATE TRIGGER open_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
