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?主備庫(kù)啟動(dòng)FORCE LOGGING

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

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

#步驟3?主備庫(kù)啟動(dòng)歸檔模式

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 (主庫(kù)和備庫(kù)都添加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?備庫(kù)配置 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（主庫(kù)配置）

##連接自身的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 ：主庫(kù)數(shù)據(jù)文件路徑霹肝，備庫(kù)數(shù)據(jù)文件路徑

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

# log_file_name_convert：主庫(kù)的日志文件路徑，備庫(kù)的日志文件路徑

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)換為備庫(kù)角色時(shí)，從這里獲取丟失的歸檔文件）

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

#啟動(dòng)db接受或發(fā)送redo data，包括所有庫(kù)的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（備庫(kù)配置）

##連接自身的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 ：主庫(kù)數(shù)據(jù)文件路徑，備庫(kù)數(shù)據(jù)文件路徑

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

# log_file_name_convert：主庫(kù)的日志文件路徑放刨，備庫(kù)的日志文件路徑

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)換為備庫(kù)角色時(shí)进统，從這里獲取丟失的歸檔文件）

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

#啟動(dòng)db接受或發(fā)送redo data，包括所有庫(kù)的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?主備測(cè)試連接(配置后重啟主備兩臺(tái)機(jī)器的數(shù)據(jù)庫(kù))

#主
[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?主庫(kù)開始首次備份 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?備庫(kù)創(chuàng)建如上目錄

#備機(jī)操作

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

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

#主庫(kù)

[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)用日志

#主庫(kù)操作

[oracle@DB196 backupset]$ sqlplus / as sysdba

SQL> alter database open;

SQL> alter system archive log current;

SQL> archive log list;

SQL>

#備庫(kù)操作

[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?測(cè)試

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

#步驟1 8?備庫(kù) Open Read Only standby數(shù)據(jù)庫(kù)并且開啟實(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?附加說(shuō)明

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

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

DB_UNIQUE_NAME = Unique name for the database? ? ? ? ? ? ? ? ? 主庫(kù) 物理、邏輯迹栓、快照備庫(kù)

FAL_CLIENT = Oracle_Net_service_name? ? ? ? ? ? ? ? ? ? ? ? ? 物理掉分、快照備庫(kù)? ? ? ? ? ? 11g之后被棄用

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

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

LOG_ARCHIVE_DEST_n = {LOCATION=path_name | SERVICE=service_name, attribute, attribute, ...}? 主庫(kù) 物理、邏輯愿吹、快照備庫(kù)

LOG_ARCHIVE_DEST_STATE_n = {ENABLE|DEFER|ALTERNATE}? ? ? ? ? 主庫(kù) 物理不从、邏輯、快照備庫(kù)

LOG_ARCHIVE_FORMAT=log%d_%t_%s_%r.arc? ? ? ? ? ? ? ? ? ? ? ? 主庫(kù) 物理犁跪、邏輯椿息、快照備庫(kù)

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

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

LOG_FILE_NAME_CONVERT = '主庫(kù)日志文件位置','備庫(kù)日志文件位置' 物理寝优、邏輯、快照備庫(kù)

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

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

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

#創(chuàng)建測(cè)試自動(dòng)插入job

主庫(kù)system登錄

begin

? dbms_network_acl_admin.create_acl

? (? ? ?

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

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

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

? ? 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 (? ? -- 添加訪問(wèn)權(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

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啟動(dòng)時(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;

最后編輯于：2020.01.10 22:49:03

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末钻心，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子仑最，更是在濱河造成了極大的恐慌扔役，老刑警劉巖，帶你破解...
沈念sama閱讀 221,548評(píng)論 6贊 515
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件警医，死亡現(xiàn)場(chǎng)離奇詭異，居然都是意外死亡，警方通過(guò)查閱死者的電腦和手機(jī)预皇，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 94,497評(píng)論 3贊 399
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門侈玄，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)，“玉大人吟温，你說(shuō)我怎么就攤上這事序仙。” “怎么了鲁豪？”我有些...
開封第一講書人閱讀 167,990評(píng)論 0贊 360
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵潘悼，是天一觀的道長(zhǎng)。經(jīng)常有香客問(wèn)我爬橡，道長(zhǎng)治唤，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 59,618評(píng)論 1贊 296
?港島之戀（遺憾婚禮）
正文為了忘掉前任糙申，我火速辦了婚禮宾添，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘柜裸。我一直安慰自己缕陕，他們只是感情好，可當(dāng)我...
茶點(diǎn)故事閱讀 68,618評(píng)論 6贊 397
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開白布疙挺。她就那樣靜靜地躺著扛邑，像睡著了一般。火紅的嫁衣襯著肌膚如雪铐然。梳的紋絲不亂的頭發(fā)上鹿榜，一...
開封第一講書人閱讀 52,246評(píng)論 1贊 308
城市分裂傳說(shuō)
那天，我揣著相機(jī)與錄音锦爵，去河邊找鬼舱殿。笑死，一個(gè)胖子當(dāng)著我的面吹牛险掀，可吹牛的內(nèi)容都是我干的沪袭。我是一名探鬼主播，決...
沈念sama閱讀 40,819評(píng)論 3贊 421
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼樟氢，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼冈绊！你這毒婦竟也來(lái)了？” 一聲冷哼從身側(cè)響起埠啃，我...
開封第一講書人閱讀 39,725評(píng)論 0贊 276
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤死宣，失蹤者是張志新（化名）和其女友劉穎，沒(méi)想到半個(gè)月后碴开，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體毅该，經(jīng)...
沈念sama閱讀 46,268評(píng)論 1贊 320
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡博秫，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 38,356評(píng)論 3贊 340
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了眶掌。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片挡育。...
茶點(diǎn)故事閱讀 40,488評(píng)論 1贊 352
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖朴爬，靈堂內(nèi)的尸體忽然破棺而出即寒，到底是詐尸還是另有隱情，我是刑警寧澤召噩，帶...
沈念sama閱讀 36,181評(píng)論 5贊 350
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布母赵，位于F島的核電站，受9級(jí)特大地震影響具滴，放射性物質(zhì)發(fā)生泄漏凹嘲。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 41,862評(píng)論 3贊 333
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一抵蚊、第九天我趴在偏房一處隱蔽的房頂上張望施绎。院中可真熱鬧，春花似錦贞绳、人聲如沸谷醉。這莊子的主人今日做“春日...
開封第一講書人閱讀 32,331評(píng)論 0贊 24
一樁弒父案冈闭，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)俱尼。三九已至，卻和暖如春萎攒，著一層夾襖步出監(jiān)牢的瞬間遇八，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 33,445評(píng)論 1贊 272
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工耍休，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留刃永，地道東北人。一個(gè)月前我還...
沈念sama閱讀 48,897評(píng)論 3贊 376
代替公主和親
正文我出身青樓羊精，卻偏偏與公主長(zhǎng)得像斯够，于是被迫代替她去往敵國(guó)和親。傳聞我的和親對(duì)象是個(gè)殘疾皇子喧锦，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 45,500評(píng)論 2贊 359

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

#步驟1? 主備 hosts配置

推薦閱讀更多精彩內(nèi)容