1.假设条件
您有1台服务器，其中装有操作系统，并在其上安装了Oracle Linux 7 和Oracle Database 12.2。
主服务器xag130上安装了实例及监听。
参考 Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle 12.2 之01准备(DB安装)
2.创建实例
#启动监听
[oracle@xag130 ~]$ lsnrctl start
[oracle@xag130 ~]$ export DISPLAY=192.168.0.3:0.0
[oracle@xag130 ~]$ dbca
3.查看表空间
[oracle@xag130 ~]$ sql / as sysdba
SQL> set sqlformat ansiconsole
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/cdb1/temp01.dbf 33
SYSTEM /u02/oradata/cdb1/system01.dbf 800
SYSAUX /u02/oradata/cdb1/sysaux01.dbf 460
UNDOTBS1 /u02/oradata/cdb1/undotbs01.dbf 70
USERS /u02/oradata/cdb1/users01.dbf 5
4.UNDO梳理
#此处为测试环境故设置100M，生产环境要放大10G以上
SQL> ALTER DATABASE DATAFILE ‘/u02/oradata/cdb1/undotbs01.dbf‘ RESIZE 100M;
SQL> ALTER DATABASE DATAFILE ‘/u02/oradata/cdb1/undotbs01.dbf‘ AUTOEXTEND ON NEXT 50M;
5.TEMP 梳理
CREATE TEMPORARY TABLESPACE CDB_TEMP TEMPFILE
‘/u02/oradata/cdb1/CDB_TEMP_1.dbf‘ SIZE 256M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 1G,
‘/u02/oradata/cdb1/CDB_TEMP_2.dbf‘ SIZE 256M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER TABLESPACE CDB_TEMP TABLESPACE GROUP CDB_TEMP_GP;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE CDB_TEMP_GP;
#如无法删除则需重启db
SQL> shutdown immediate;
SQL> startup;
SQL> drop tablespace TEMP including contents and datafiles;
select * from dba_tablespace_groups
6.整理用户表空间
create tablespace XAG_UD datafile
‘/u02/oradata/cdb1/XAG_UD1.dbf‘ size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,
‘/u02/oradata/cdb1/XAG_UD2.dbf‘ size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
Create tablespace I_XAG_UD datafile
‘/u02/oradata/cdb1/I_XAG_UD1.dbf‘ size 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED,
‘/u02/oradata/cdb1/I_XAG_UD2.dbf‘ size 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
ALTER DATABASE DEFAULT TABLESPACE XAG_UD;
drop tablespace USERS including contents and datafiles;
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
CDB_TEMP /u02/oradata/cdb1/CDB_TEMP_1.dbf 256
CDB_TEMP /u02/oradata/cdb1/CDB_TEMP_2.dbf 256
SYSTEM /u02/oradata/cdb1/system01.dbf 800
SYSAUX /u02/oradata/cdb1/sysaux01.dbf 460
UNDOTBS1 /u02/oradata/cdb1/undotbs01.dbf 100
XAG_UD /u02/oradata/cdb1/XAG_UD1.dbf 128
XAG_UD /u02/oradata/cdb1/XAG_UD2.dbf 128
I_XAG_UD /u02/oradata/cdb1/I_XAG_UD1.dbf 64
I_XAG_UD /u02/oradata/cdb1/I_XAG_UD2.dbf 64
7.創建測試自動插入job(system用户下)
[oracle@xag130 ~]$ sql system/123456
SQL> set sqlformat ansiconsole
begin
dbms_network_acl_admin.create_acl
(
acl => ‘utl_http.xml‘, -- 文件名称
description => ‘HTTP Access‘, -- 描述
principal => ‘SYSTEM‘, -- 授权或者取消授权账号，大小写敏感
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 => ‘SYSTEM‘, -- 授权或取消授权用户
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;
/
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,sysdate as update_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;
/
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;
SQL> select * from V_XAG_JOB1;
JOB LAST_DATE NEXT_DATE TOTAL_TIME FAILURES BROKEN WHAT INTERVAL
1 2020-06-25 19:22:40 2020-06-25 19:23:40 0 0 N pr_my_healt_check_insert; sysdate + (1/(24*60))
SQL> select * from V_XAG_JOB2;
no rows selected
SQL> select host_address,host_name,db_name,to_char(create_time,‘yyyy-mm-dd hh24:mi:ss‘) as create_time,to_char(update_time,‘yyyy-mm-dd hh24:mi:ss‘) as update_time from my_healt_check order by create_time desc;
HOST_ADDRESS HOST_NAME DB_NAME CREATE_TIME UPDATE_TIME
192.168.40.130 xag130 CDB$ROOT 2020-06-25 19:23:45
192.168.40.130 xag130 CDB$ROOT 2020-06-25 19:22:40
192.168.40.130 xag130 CDB$ROOT 2020-06-25 19:21:35
192.168.40.130 xag130 CDB$ROOT 2020-06-25 19:20:30
192.168.40.130 xag130 CDB$ROOT 2020-06-25 19:19:19 2020-06-25 19:19:19
8.DB启动时 open all pdb
conn / as sysdba
CREATE TRIGGER open_all_pdbs
AFTER STARTUP
ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘alter pluggable database all open‘;
END open_all_pdbs;
/
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
9.创建pdb1
[oracle@xag130 ~]$ sql sys/123456@192.168.40.130:1521/cdb1 as sysdba
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 FROM v$database;
NAME Multitenant Option OPEN_MODE CON_ID
CDB1 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
10.基于PDB$SEED演示创建PDB
SQL> show parameters db_create_file_dest;
NAME TYPE VALUE
------------------- ------ -----
db_create_file_dest string
SQL> alter system set db_create_file_dest=‘/u02/oradata/‘;
SQL> create pluggable database pdb1 admin user admin identified by 123456 roles=(connect) file_name_convert=(‘/u02/oradata/cdb1/pdbseed‘,‘/u02/oradata/cdb1/pdb1‘);
11.查看创建后的状态，刚刚创建的pdb status列为NEW
SQL> select pdb_id,pdb_name,status,creation_time from cdb_pdbs;
PDB_ID PDB_NAME STATUS CREATION_TIME
3 PDB1 NEW 25-JUN-20
2 PDB$SEED NORMAL 25-JUN-20
#如下查询，当前的数据库处于mount状态
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
2 373239448 PDB$SEED READ ONLY
3 1516042335 PDB1 MOUNTED
- 将pdb数据库切换到open状态
SQL> alter pluggable database PDB1 open;
or
SQL>alter session set container=PDB1 ;
SQL> startup;
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
2 373239448 PDB$SEED READ ONLY
3 1516042335 PDB1 READ WRITE
- 使用公共用户sys连接到刚刚创建的pdb数据库
SQL> conn sys/123456@192.168.40.130:1521/pdb1 as sysdba;
SQL> show user;
USER is "SYS"
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 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/cdb1/pdb1/temp012020-06-25_18-54-38-594-PM.dbf 64
UNDOTBS1 /u02/oradata/cdb1/pdb1/undotbs01.dbf 100
SYSAUX /u02/oradata/cdb1/pdb1/sysaux01.dbf 330
SYSTEM /u02/oradata/cdb1/pdb1/system01.dbf 250
#此处为测试环境故设置200M，生产环境要放大10G以上
SQL> ALTER DATABASE DATAFILE ‘/u02/oradata/cdb1/pdb1/undotbs01.dbf‘ RESIZE 200M;
SQL> ALTER DATABASE DATAFILE ‘/u02/oradata/cdb1/pdb1/undotbs01.dbf‘ AUTOEXTEND ON NEXT 50M;
- TEMP 梳理
CREATE TEMPORARY TABLESPACE PDB1_TEMP TEMPFILE
‘/u02/oradata/cdb1/pdb1/PDB1_TEMP_1.dbf‘ SIZE 256M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 1G,
‘/u02/oradata/cdb1/pdb1/PDB1_TEMP_2.dbf‘ SIZE 256M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE 1G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
ALTER TABLESPACE PDB1_TEMP TABLESPACE GROUP PDB1_TEMP_GP;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PDB1_TEMP_GP;
#如无法删除则需重启db
drop tablespace TEMP including contents and datafiles;
SQL> select * from dba_tablespace_groups;
GROUP_NAME TABLESPACE_NAME
PDB1_TEMP_GP PDB1_TEMP
- 整理用户表空间
create tablespace PDB1_UD datafile
‘/u02/oradata/cdb1/pdb1/PDB1_UD1.dbf‘ size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,
‘/u02/oradata/cdb1/pdb1/PDB1_UD2.dbf‘ size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
Create tablespace I_PDB1_UD datafile
‘/u02/oradata/cdb1/pdb1/I_PDB1_UD1.dbf‘ size 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED,
‘/u02/oradata/cdb1/pdb1/I_PDB1_UD2.dbf‘ size 64M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
default storage (initial 128K next 2M pctincrease 0);
ALTER DATABASE DEFAULT TABLESPACE PDB1_UD;
drop tablespace USERS including contents and datafiles;
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
PDB1_TEMP /u02/oradata/cdb1/pdb1/PDB1_TEMP_1.dbf 256
PDB1_TEMP /u02/oradata/cdb1/pdb1/PDB1_TEMP_2.dbf 256
SYSTEM /u02/oradata/cdb1/pdb1/system01.dbf 250
SYSAUX /u02/oradata/cdb1/pdb1/sysaux01.dbf 350
UNDOTBS1 /u02/oradata/cdb1/pdb1/undotbs01.dbf 200
PDB1_UD /u02/oradata/cdb1/pdb1/PDB1_UD1.dbf 128
PDB1_UD /u02/oradata/cdb1/pdb1/PDB1_UD2.dbf 128
I_PDB1_UD /u02/oradata/cdb1/pdb1/I_PDB1_UD1.dbf 64
I_PDB1_UD /u02/oradata/cdb1/pdb1/I_PDB1_UD2.dbf 64
- 对用户授权
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;
#设置用户密码无限次尝试登录
alter profile default limit failed_login_attempts unlimited;
#设置用户密码不过期：
alter profile default limit password_life_time unlimited;
#查看配置的参数
SQL> select profile,RESOURCE_NAME,resource_type,limit from dba_profiles where RESOURCE_NAME in(‘FAILED_LOGIN_ATTEMPTS‘,‘PASSWORD_LIFE_TIME‘) and profile=‘DEFAULT‘;
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
- 使用pdb本地管理员账户连接到pdb数据库
SQL> conn admin/xag123@192.168.40.130:1521/pdb1
SQL> show user;
USER is "ADMIN"
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
3 PDB1 READ WRITE YES
#查看当前用户的角色
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
#查看当前用户的权限
SQL> select * from session_privs;
#查看当前的默认表空间
SQL> SELECT PROPERTY_VALUE FROM database_properties WHERE PROPERTY_NAME =‘DEFAULT_PERMANENT_TABLESPACE‘;
PROPERTY_VALUE
PDB1_UD
- 创建新用户
[oracle@XAG130 ~]$ sql admin/xag123@192.168.40.130:1521/pdb1
SQL> SET SQLFORMAT ansiconsole
SQL> CREATE USER XAG IDENTIFIED BY "123456" DEFAULT TABLESPACE PDB1_UD TEMPORARY TABLESPACE PDB1_TEMP_GP;
SQL> GRANT DBA to XAG;
grant connect,resource,unlimited tablespace to XAG;
grant create any directory to XAG;
grant drop any directory to XAG;
#修改密码(可选)
alter user XAG identified by "123456";
#账户锁定后解锁命令(可选) sys or system
alter user XAG account unlock;
#设置用户密码无限次尝试登录
alter profile default limit failed_login_attempts unlimited;
#设置用户密码不过期：
alter profile default limit password_life_time unlimited;
#查看配置的参数
SQL> select profile,RESOURCE_NAME,resource_type,limit from dba_profiles where RESOURCE_NAME in(‘FAILED_LOGIN_ATTEMPTS‘,‘PASSWORD_LIFE_TIME‘) and profile=‘DEFAULT‘;
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED
DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED
SQL> conn XAG/123456@192.168.40.130:1521/pdb1
SQL> SET SQLFORMAT ansiconsole
- CDB下查询默认表空间
[oracle@XAG130 ~]$ sql sys/123456@192.168.40.130:1521/cdb1 as sysdba
SQL> SELECT PROPERTY_VALUE FROM database_properties WHERE PROPERTY_NAME =‘DEFAULT_PERMANENT_TABLESPACE‘;
PROPERTY_VALUE
-----------------------------------
XAG_UD
21.新用户xag 登录 pdb1 且創建測試自動插入job
[oracle@XAG130 ~]$ sql xag/123456@192.168.40.130:1521/pdb1
SQL>SET SQLFORMAT ansiconsole
begin
dbms_network_acl_admin.create_acl
(
acl => ‘xagutl_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 => ‘xagutl_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 => ‘xagutl_http.xml‘, host =>‘*‘);
end;
/
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,sysdate as update_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;
/
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;
SQL> select * from V_XAG_JOB1;
JOB LAST_DATE NEXT_DATE TOTAL_TIME FAILURES BROKEN WHAT INTERVAL
1 2020-06-25 20:03:03 2020-06-25 20:04:03 0 0 N pr_my_healt_check_insert; sysdate + (1/(24*60))
SQL> select * from V_XAG_JOB2;
no rows selected
SQL> select host_address,host_name,db_name,to_char(create_time,‘yyyy-mm-dd hh24:mi:ss‘) as create_time,to_char(update_time,‘yyyy-mm-dd hh24:mi:ss‘) as update_time from my_healt_check order by create_time desc;
HOST_ADDRESS HOST_NAME DB_NAME CREATE_TIME UPDATE_TIME
192.168.40.130 xag130 PDB1 2020-06-25 20:03:03
192.168.40.130 xag130 PDB1 2020-06-25 20:02:24 2020-06-25 20:02:24