ORACLE 12C ADG 之三A (DG 配置管理)

#步驟1? 主備 hosts配置

[oracle@DB196 database]$ cat /etc/hosts

#add

192.168.1.196? DB196? ? DB196.COM

192.168.1.197? DB197? ? DB197.COM

[oracle@DB197 ~]$ cat /etc/hosts

#add

192.168.1.196? DB196? ? DB196.COM

192.168.1.197? DB197? ? DB197.COM

#步驟2?主備庫啟動FORCE LOGGING

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

????????????YES

#步驟3?主備庫啟動歸檔模式

SQL> archive log list;

SQL> shutdown immediate

SQL> startup mount

SQL> alter database archivelog;

SQL> archive log list;

SQL> show parameter recovery

NAME ? ? ???????????????????????????? TYPE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?VALUE

------------------------------------ ---------------------------------

db_recovery_file_dest? ? ? ? ? ? ?string? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?/u03/fast_recovery_area/MPCDB

db_recovery_file_dest_size ? ? big integer? ? ? ? ? ? ? ? ? ? ? ? ? ? 500G

recovery_parallelism? ? ? ? ? ? ? ? ? integer? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0

remote_recovery_file_dest? ? ? string


#步驟4?添加standby logfile (主庫和備庫都添加standby log)

SQL> show con_name

????????????CDB$ROOT

SQL> select? group#, members,? bytes? from v$log;

? ? GROUP#? ? MEMBERS ? BYTES

---------- ---------- ----------

1 ? ? 1? 209715200

2 ? ? 1? 209715200

3 ? ? 1? 209715200

SQL> select member from v$logfile;

/u02/oradata/MPCDB/redo03.log

/u02/oradata/MPCDB/redo02.log

/u02/oradata/MPCDB/redo01.log

SQL> alter database add standby logfile '/u02/oradata/MPCDB/stdredo01.log' size 200M;

SQL> alter? database add? standby? logfile '/u02/oradata/MPCDB/stdredo02.log'? size 200M;

SQL> alter? database add? standby? logfile '/u02/oradata/MPCDB/stdredo03.log'? size 200M;

SQL> alter? database add? standby? logfile '/u02/oradata/MPCDB/stdredo04.log'? size 200M;

SQL> select group#, members, bytes from v$log;

? ? GROUP#? ? MEMBERS ? BYTES

---------- ---------- ----------

1 ? ? 1? 209715200

2 ? ? 1? 209715200

3 ? ? 1? 209715200

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u02/oradata/MPCDB/redo03.log

/u02/oradata/MPCDB/redo02.log

/u02/oradata/MPCDB/redo01.log

/u02/oradata/MPCDB/stdredo01.log

/u02/oradata/MPCDB/stdredo02.log

/u02/oradata/MPCDB/stdredo03.log

/u02/oradata/MPCDB/stdredo04.log

7 rows selected.

SQL> col member format a50;

SQL> set linesize 100;

SQL> select member,group#,status from v$logfile;

MEMBER ? ? ? GROUP# STATUS

-------------------------------------------------- ---------- ---------------------

/u02/oradata/MPCDB/redo03.log ? ? 3

/u02/oradata/MPCDB/redo02.log ? ? 2

/u02/oradata/MPCDB/redo01.log ? ? 1

/u02/oradata/MPCDB/stdredo01.log ? ? 4

/u02/oradata/MPCDB/stdredo02.log ? ? 5

/u02/oradata/MPCDB/stdredo03.log ? ? 6

/u02/oradata/MPCDB/stdredo04.log ? ? 7

7 rows selected

#步驟5?網(wǎng)絡(luò)配置(主)

[oracle@DB196 ~]$ cd $ORACLE_HOME/network/admin

[oracle@DB196 admin]$

[oracle@DB196 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

? (SID_LIST =

? ? (SID_DESC =

? ? ? (SID_NAME = PLSExtProc)

? ? ? (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)

? ? ? (PROGRAM = extproc)

? ? )

? ? (SID_DESC =

? ? ? (SDU=32767)

? ? ? (SID_NAME = MPCDB)

? ? ? (GLOBAL_DBNAME = MPCDB196_DGMGRL)

? ? ? (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)

? ? )

? )

LISTENER =

? (DESCRIPTION_LIST =

? ? (DESCRIPTION =

? ? ? (ADDRESS = (PROTOCOL = TCP)(HOST = DB196)(PORT = 1521))

? ? ? (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

? ? )

? )

ADR_BASE_LISTENER = /u01/app/oracle

--------------------------------------------------------------------------------------

#步驟6?網(wǎng)絡(luò)配置(備)

[oracle@12c ~]$ cd $ORACLE_HOME/network/admin

[oracle@DB197 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

? (SID_LIST =

? ? (SID_DESC =

? ? ? (SID_NAME = PLSExtProc)

? ? ? (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)

? ? ? (PROGRAM = extproc)

? ? )

? ? (SID_DESC =

? ? ? (SDU=32767)

? ? ? (SID_NAME = MPCDB)

? ? ? (GLOBAL_DBNAME = MPCDB197_DGMGRL)

? ? ? (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1)

? ? )

? )

LISTENER =

? (DESCRIPTION_LIST =

? ? (DESCRIPTION =

? ? ? (ADDRESS = (PROTOCOL = TCP)(HOST = DB197)(PORT = 1521))

? ? ? (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

? ? )

? )

ADR_BASE_LISTENER = /u01/app/oracle

#步驟7?主機(jī)tns配置

[oracle@DB196 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

MPCDB =

? (DESCRIPTION =

? ? (ADDRESS = (PROTOCOL = TCP)(HOST = DB196)(PORT = 1521))

? ? (CONNECT_DATA =

? ? ? (SERVER = DEDICATED)

? ? ? (SERVICE_NAME = MPCDB)

? ? )

? )

LISTENER_MPCDB =

? (ADDRESS = (PROTOCOL = TCP)(HOST = DB196)(PORT = 1521))


PL =

? (DESCRIPTION =

? ? (SDU=32767)(SEND_BUF_SIZE=9375000)(RECV_BUF_SIZE=9375000)

? ? (ADDRESS_LIST =

? ? ? (ADDRESS = (PROTOCOL = TCP)(HOST = DB196)(PORT = 1521))

? ? )

? ? (CONNECT_DATA =

? ? ? (SERVER = DEDICATED)

? ? ? (SERVICE_NAME = MPCDB)

? ? )

? )


SL =

? (DESCRIPTION =

? ? (SDU=32767)(SEND_BUF_SIZE=9375000)(RECV_BUF_SIZE=9375000)?

? ? (ADDRESS_LIST =

? ? ? (ADDRESS = (PROTOCOL = TCP)(HOST = DB197)(PORT = 1521))

? ? )

? ? (CONNECT_DATA =

? ? ? (SERVER = DEDICATED)

? ? ? (SERVICE_NAME = MPCDB)

? ? )

? )

------------------------------------------------------------------------------------------

#步驟8?備庫配置 tns

[oracle@DB197 admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

MPCDB =

? (DESCRIPTION =

? ? (ADDRESS = (PROTOCOL = TCP)(HOST = DB197)(PORT = 1521))

? ? (CONNECT_DATA =

? ? ? (SERVER = DEDICATED)

? ? ? (SERVICE_NAME = MPCDB)

? ? )

? )

LISTENER_MPCDB =

? (ADDRESS = (PROTOCOL = TCP)(HOST = DB197)(PORT = 1521))

PL =

? (DESCRIPTION =

? ? (SDU=32767)(SEND_BUF_SIZE=9375000)(RECV_BUF_SIZE=9375000)

? ? (ADDRESS_LIST =

? ? ? (ADDRESS = (PROTOCOL = TCP)(HOST = DB196)(PORT = 1521))

? ? )

? ? (CONNECT_DATA =

? ? ? (SERVER = DEDICATED)

? ? ? (SERVICE_NAME = MPCDB)

? ? )

? )

SL =

? (DESCRIPTION =

? ? (SDU=32767)(SEND_BUF_SIZE=9375000)(RECV_BUF_SIZE=9375000)

? ? (ADDRESS_LIST =

? ? ? (ADDRESS = (PROTOCOL = TCP)(HOST = DB197)(PORT = 1521))

? ? )

? ? (CONNECT_DATA =

? ? ? (SERVER = DEDICATED)

? ? ? (SERVICE_NAME = MPCDB)

? ? )

? )

---------------------------------------------------------------------------------

#步驟9?拷貝密碼文件

[oracle@DB196 admin]$ cd $ORACLE_HOME/dbs

[oracle@DB196 dbs]$ pwd

/u01/app/oracle/product/12.2.0/db_1/dbs

[oracle@DB196 dbs]$ ls

hc_MPCDB.dat? init.ora? lkMPCDB? orapwMPCDB? snapcf_MPCDB.f? spfileMPCDB.ora

[oracle@DB196 dbs]$

[oracle@DB196 dbs]$ scp orapwMPCDB oracle@192.168.1.197:$ORACLE_HOME/dbs

#步驟1 0?Set ADG Parameter（主庫配置）

##連接自身的tnsname里面的服務(wù)名

SQL> alter system set job_queue_processes=10 scope=spfile;

SQL> alter system set db_unique_name='MPCDB196' scope=spfile;

SQL> alter system set local_listener='PL' scope=spfile;

SQL> alter system set global_names=true scope=both;

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO MPCDB;

#db_file_name_convert ：主庫數(shù)據(jù)文件路徑，備庫數(shù)據(jù)文件路徑

SQL> alter system set db_file_name_convert='/u02/oradata/MPCDB/','/u02/oradata/MPCDB/' scope=spfile;

# log_file_name_convert：主庫的日志文件路徑，備庫的日志文件路徑

SQL> alter system set log_file_name_convert='/u02/oradata/MPCDB/','/u02/oradata/MPCDB/' scope=spfile;

#（配置tnsnames.ora網(wǎng)絡(luò)服務(wù)名，fal_server拷貝丟失的歸檔文件到這里）

SQL> alter system set fal_client='PL' scope=spfile;

#（配置tnsnames.ora網(wǎng)絡(luò)服務(wù)名，假如轉(zhuǎn)換為備庫角色時(shí)，從這里獲取丟失的歸檔文件）

SQL> alter system set fal_server='SL' scope=spfile;

#啟動db接受或發(fā)送redo data，包括所有庫的db_unique_name

SQL> alter system set log_archive_config = 'DG_CONFIG=(MPCDB196,MPCDB197)' scope=spfile;

SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=MPCDB196' scope=spfile;

SQL> alter system set log_archive_dest_2='service=SL LGWR SYNC AFFIRM valid_for=(all_logfiles,primary_role) db_unique_name=MPCDB197' scope=spfile;

SQL> alter system set log_archive_format='ARC_%t_%S_%r.arc' scope=spfile;

SQL> alter system set standby_file_management='AUTO' scope=spfile;

SQL> alter system set service_names='MPCDB' scope=spfile;

SQL> alter system set log_archive_max_processes=4 scope=spfile;


---------------------------------------------------------------------------

#步驟1?1?Set ADG Parameter（備庫配置）

##連接自身的tnsname里面的服務(wù)名

SQL> alter system set job_queue_processes=10 scope=spfile;

SQL> alter system set db_unique_name='MPCDB197' scope=spfile;

SQL> alter system set global_names=true scope=both;

SQL> alter system set local_listener='SL' scope=spfile;

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO MPCDB;

#db_file_name_convert ：主庫數(shù)據(jù)文件路徑，備庫數(shù)據(jù)文件路徑

SQL> alter system set db_file_name_convert='/u02/oradata/MPCDB/','/u02/oradata/MPCDB/' scope=spfile;

# log_file_name_convert：主庫的日志文件路徑，備庫的日志文件路徑

SQL> alter system set log_file_name_convert='/u02/oradata/MPCDB/','/u02/oradata/MPCDB/' scope=spfile;

#（配置tnsnames.ora網(wǎng)絡(luò)服務(wù)名，fal_server拷貝丟失的歸檔文件到這里）

SQL> alter system set fal_client='SL' scope=spfile;

#（配置tnsnames.ora網(wǎng)絡(luò)服務(wù)名，假如轉(zhuǎn)換為備庫角色時(shí)，從這里獲取丟失的歸檔文件）

SQL> alter system set fal_server='PL' scope=spfile;

#啟動db接受或發(fā)送redo data，包括所有庫的db_unique_name

SQL> alter system set log_archive_config = 'DG_CONFIG=(MPCDB197,MPCDB196)' scope=spfile;

SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=MPCDB197' scope=spfile;

SQL> alter system set log_archive_dest_2='service=PL LGWR SYNC AFFIRM valid_for=(all_logfiles,primary_role) db_unique_name=MPCDB196' scope=spfile;

SQL> alter system set log_archive_format='ARC_%t_%S_%r.arc' scope=spfile;

SQL> alter system set standby_file_management='AUTO' scope=spfile;

SQL> alter system set service_names='MPCDB' scope=spfile;

SQL> alter system set log_archive_max_processes=4 scope=spfile;

---------------------------------------------------------------------------------------

#步驟1 2?重啟主備DB及監(jiān)聽

#步驟1 3?主備測試連接(配置后重啟主備兩臺機(jī)器的數(shù)據(jù)庫)

#主
[oracle@DB196 MPCDB]$ sqlplus sys/MPCDB@PL as sysdba

[oracle@DB196 MPCDB]$ sqlplus? sys/MPCDB@SL as sysdba

#備

[oracle@DB197 admin]$ sqlplus sys/MPCDB@PL as sysdba

[oracle@DB197 admin]$ sqlplus? sys/MPCDB@SL as sysdba

#步驟1 4?主庫開始首次備份 196

[oracle@DB196 MPCDB]$ sqlplus / as sysdba

SQL> shutdown immediate;

SQL>

SQL> startup mount;

SQL>? ?

SQL> exit

[oracle@DB196 MPCDB]$

[oracle@DB196 MPCDB]$ rman target /

connected to target database: MPCDB (DBID=3274499937, not open)

RMAN>

RMAN> crosscheck archivelog all;

RMAN> delete expired archivelog all;

RMAN> crosscheck backup;

RMAN> delete expired backup;

RMAN> backup as compressed backupset database include current controlfile for standby plus archivelog;

RMAN> exit

[oracle@DB196 MPCDB]$ cd /u03/fast_recovery_area/MPCDB/MPCDB196/backupset/

[oracle@DB196 backupset]$ ls

2018_09_21

[oracle@DB196 backupset]$ scp -r /u03/fast_recovery_area/MPCDB/MPCDB196/backupset/2018_09_21/ oracle@192.168.1.197:/u03/fast_recovery_area/MPCDB/MPCDB197/backupset

scp: /u03/fast_recovery_area/MPCDB/MPCDB197/backupset: No such file or directory

#步驟1 5?備庫創(chuàng)建如上目錄

#備機(jī)操作

[oracle@DB197 admin]$ mkdir /u03/fast_recovery_area/MPCDB/MPCDB197

[oracle@DB197 admin]$ mkdir /u03/fast_recovery_area/MPCDB/MPCDB197/backupset

#主庫

[oracle@DB196 backupset]$ scp -r /u03/fast_recovery_area/MPCDB/MPCDB196/backupset/2018_09_21/ oracle@192.168.1.197:/u03/fast_recovery_area/MPCDB/MPCDB197/backupset

[oracle@DB196 backupset]$

#備機(jī)操作

[oracle@DB197 admin]$ cd /u03/fast_recovery_area/MPCDB/MPCDB197/backupset

[oracle@DB197 backupset]$ ls

2018_09_21

[oracle@DB197 backupset]$ sqlplus / as sysdba

SQL> shutdown immediate;

SQL> startup nomount;

SQL> exit

[oracle@DB197 backupset]$ rman target sys/MPCDB@PL auxiliary sys/MPCDB@SL

connected to target database: MPCDB (DBID=3274499937, not open)

connected to auxiliary database: MPCDB (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 2018:09:2119:27:51

...

Finished Duplicate Db at 2018:09:2119:28:27

RMAN> exit

[oracle@DB197 backupset]$

[oracle@DB197 backupset]$ sqlplus / as sysdba

SQL> col member format a30;

SQL> set linesize 300;

SQL> select database_role,protection_mode,protection_level from v$database;

DATABASE_ROLE? ? ? ? ? ? ?????PROTECTION_MODE ? ? ? ????????????PROTECTION_LEVEL

------------------------------------------------ ------------------------------------------------------------

PHYSICAL STANDBY ???????? MAXIMUM PERFORMANCE ? ? ? MAXIMUM PERFORMANCE

SQL>? select sequence# ,applied from v$archived_log;

no rows selected

#步驟1 6?開始應(yīng)用日志

#主庫操作

[oracle@DB196 backupset]$ sqlplus / as sysdba

SQL> alter database open;

SQL> alter system archive log current;

SQL> archive log list;

SQL>

#備庫操作

[oracle@DB197 backupset]$ sqlplus / as sysdba

SQL> alter database open;

SQL> alter database recover managed standby database using current logfile disconnect from session;

SQL> set pagesize 100

SQL> select sequence# ,applied from v$archived_log;

SEQUENCE# APPLIED

---------- ---------------------------

11 YES

12 YES

13 YES

14 IN-MEMORY

#步驟1 7?測試

主庫創(chuàng)建表后，到備庫檢查 一致則完成


#步驟1 8?備庫 Open Read Only standby數(shù)據(jù)庫并且開啟實(shí)時(shí)日志應(yīng)用

SQL> shutdown immediate;

SQL> exit

[oracle@DB197 backupset]$ sqlplus / as sysdba

SQL> startup

SQL> select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE? ? ? ????????PROTECTION_MODE? ? ? ? ? ? ? ? ????????OPEN_MODE

------------------------------------------------------------------------------------------------------------

PHYSICAL STANDBY? ????????MAXIMUM PERFORMANCE? ? ?????????READ ONLY? ? ??

#如?OPEN_MODE= READ ONLY WITH APPLY 則已經(jīng)開始實(shí)時(shí)應(yīng)用日誌


SQL> recover managed standby database using current logfile disconnect from session;

SQL>? select database_role,protection_mode,open_mode from v$database;

DATABASE_ROLE? ? ? ? ????????PROTECTION_MODE? ? ? ? ? ? ? ?????OPEN_MODE

------------------------------------------------------------------------------------------------------------

PHYSICAL STANDBY? ????????MAXIMUM PERFORMANCE? ? ?????READ ONLY WITH APPLY

#如?OPEN_MODE= READ ONLY WITH APPLY 則已經(jīng)開始實(shí)時(shí)應(yīng)用日誌


SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;

? ? CON_ID ???????????????????????? DBID????????????????????NAME????????????????OPEN_MODE

---------- ------------------------------------------------------------------------------------------

? ?2? ????????????????????????????????????319070549????????????PDB$SEED????????????READ ONLY

?#如 存在?OPEN_MODE= MOUNTED 則? alter pluggable database PDBnnn open;

至此 已完成ORACLE 12C DG的 創(chuàng)建


#步驟1 9?附加說明

------------------------------------------------------------------------------------------------

DB_FILE_NAME_CONVERT = '主庫數(shù)據(jù)文件位置','備庫數(shù)據(jù)文件位置'? 物理、快照備庫? ? ?

DB_UNIQUE_NAME = Unique name for the database? ? ? ? ? ? ? ? ? 主庫 物理、邏輯、快照備庫

FAL_CLIENT = Oracle_Net_service_name? ? ? ? ? ? ? ? ? ? ? ? ? 物理、快照備庫? ? ? ? ? ? 11g之后被棄用

FAL_SERVER = Oracle_Net_service_name? ? ? ? ? ? ? ? ? ? ? ? ? 物理、快照備庫? ? ? （如果主庫發(fā)送日志缺失）備庫自動裂痕檢測發(fā)現(xiàn)之后會從該配置的server中獲取

LOG_ARCHIVE_CONFIG ='DG_CONFIG ( db_unique_name, db_unique_name, ... )' 主庫 物理、邏輯、快照備庫? data guaid 建議

LOG_ARCHIVE_DEST_n = {LOCATION=path_name | SERVICE=service_name, attribute, attribute, ...}? 主庫 物理、邏輯、快照備庫

LOG_ARCHIVE_DEST_STATE_n = {ENABLE|DEFER|ALTERNATE}? ? ? ? ? 主庫 物理、邏輯、快照備庫

LOG_ARCHIVE_FORMAT=log%d_%t_%s_%r.arc? ? ? ? ? ? ? ? ? ? ? ? 主庫 物理、邏輯、快照備庫

LOG_ARCHIVE_LOCAL_FIRST = {TRUE | FALSE}? ? ? ? ? ? ? ? ? ? ? 主庫 快照備庫? ? ? ? ? ? 建議設(shè)置成true

LOG_ARCHIVE_MAX_PROCESSES =integer? ? ? ? ? ? ? ? ? ? ? ? ? ? 主庫 物理、邏輯、快照備庫 1-30 默認(rèn)4

LOG_FILE_NAME_CONVERT = '主庫日志文件位置','備庫日志文件位置' 物理、邏輯、快照備庫

STANDBY_ARCHIVE_DEST = filespec? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 物理、邏輯、快照備庫? ? 11g之后已經(jīng)被棄用

STANDBY_FILE_MANAGEMENT = {AUTO | MANUAL}? ? ? ? ? ? ? ? ? ? 主庫 邏輯、快照備庫? ? 建議設(shè)置成auto


============================================================

#創(chuàng)建測試自動插入job

主庫system登錄

begin

? dbms_network_acl_admin.create_acl

? (? ? ?

? ? acl? ? ? ? => 'utl_http.xml',? ? ? ? ? -- 文件名稱

? ? description => 'HTTP Access',? ? ? ? ? -- 描述

? ? principal? => 'SYSTEM',? ? ? ? ? ? ? ? -- 授權(quán)或者取消授權(quán)賬號，大小寫敏感

? ? is_grant? ? => TRUE,? ? ? ? ? ? ? ? ? ? -- 授權(quán)還是取消授權(quán)

? ? privilege? => 'connect',? ? ? ? ? ? ? -- 授權(quán)或者取消授權(quán)的權(quán)限列表

? ? start_date? => null,? ? ? ? ? ? ? ? ? ? -- 起始日期

? ? end_date? ? => null? ? ? ? ? ? ? ? ? ? -- 結(jié)束日期

? );

end;

begin

? dbms_network_acl_admin.add_privilege (? ? -- 添加訪問權(quán)限列表項(xiàng)

? ? acl? ? ? ? => 'utl_http.xml',? ? ? ? ? -- 剛才創(chuàng)建的acl名稱

? ? principal? => 'SYSTEM',? ? ? ? ? ? ? ? -- 授權(quán)或取消授權(quán)用戶

? ? is_grant? => TRUE,? ? ? ? ? ? ? ? ? ? -- 與上同

? ? privilege? => 'resolve',? ? ? ? ? ? ? ? -- 權(quán)限列表

? ? start_date => null,? ? ? ? ? ? ? ? ? ?

? ? end_date? => null

? );

end;

begin

? dbms_network_acl_admin.assign_acl(acl => 'utl_http.xml', host =>'*');

end;

create table test_c

as

select utl_inaddr.get_host_address as host_address

,utl_inaddr.get_host_name || '-' || sys_context('USERENV','CON_NAME') as get_host_name

,sysdate as create_time

from dual;

create or replace procedure pr_test_c as

begin

? delete from test_c a where a.create_time<sysdate-0.5/24;

? insert into test_c

? 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_test_c;',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_JOB 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 test_c

select * from?V_XAG_JOB1

#DB啟動時(shí) 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;

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容