通用：
OS:CentOS 6.8 64
Oracle: 10g 10.2.0.1.0
SID: milly
主库：
192.168.111.130
DB_UNIQUE_NAME:master
备库：
192.168.111.131
DB_UNIQUE_NAME:slave
一、主库
查看主库当前的情况：
[oracle@master ~]$ ps -ef | grep ora_ oracle 3646 1 0 22:25 ? 00:00:00 ora_pmon_milly oracle 3648 1 0 22:25 ? 00:00:00 ora_psp0_milly oracle 3650 1 0 22:25 ? 00:00:00 ora_mman_milly oracle 3652 1 0 22:25 ? 00:00:00 ora_dbw0_milly oracle 3654 1 0 22:25 ? 00:00:00 ora_lgwr_milly oracle 3656 1 0 22:25 ? 00:00:00 ora_ckpt_milly oracle 3658 1 0 22:25 ? 00:00:00 ora_smon_milly oracle 3660 1 0 22:25 ? 00:00:00 ora_reco_milly oracle 3662 1 0 22:25 ? 00:00:00 ora_cjq0_milly oracle 3664 1 0 22:25 ? 00:00:00 ora_mmon_milly oracle 3666 1 0 22:25 ? 00:00:00 ora_mmnl_milly oracle 3668 1 0 22:25 ? 00:00:00 ora_d000_milly oracle 3670 1 0 22:25 ? 00:00:00 ora_s000_milly oracle 3674 1 0 22:26 ? 00:00:00 ora_qmnc_milly oracle 3680 1 0 22:26 ? 00:00:00 ora_p000_milly oracle 3682 1 0 22:26 ? 00:00:00 ora_p001_milly oracle 3684 1 0 22:26 ? 00:00:00 ora_p002_milly oracle 3686 1 0 22:26 ? 00:00:00 ora_p003_milly oracle 3691 1 0 22:26 ? 00:00:00 ora_q000_milly oracle 3693 1 0 22:26 ? 00:00:00 ora_q001_milly oracle 3695 3557 0 22:26 pts/0 00:00:00 grep ora_ [oracle@master ~]$ [oracle@master ~]$ export ORACLE_SID=milly [oracle@master ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 11 22:26:37 2016 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options SQL> select name,database_role,open_mode from v$database; NAME DATABASE_ROLE OPEN_MODE --------- ---------------- ---------- MILLY PRIMARY READ WRITE SQL>
1. force logging：
SQL> select name,force_logging from v$database; NAME FOR --------- --- MILLY NO SQL> SQL> alter database force logging; Database altered. SQL> select name,force_logging from v$database; NAME FOR --------- --- MILLY YES SQL>
2. 归档模式：
SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Current log sequence 2 SQL> SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount; ORACLE instance started. Total System Global Area 536870912 bytes Fixed Size 2022144 bytes Variable Size 155190528 bytes Database Buffers 377487360 bytes Redo Buffers 2170880 bytes Database mounted. SQL> SQL> alter database archivelog; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 2 Current log sequence 2 SQL> SQL> alter database open; Database altered. SQL>
3. 查询并创建相关目录：
【$ORACLE_BASE/admin】相关目录
主库：
[oracle@master ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 11 22:35:54 2016 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options SQL> show parameter dump NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ background_core_dump string partial background_dump_dest string /u01/app/oracle/admin/milly/bd ump core_dump_dest string /u01/app/oracle/admin/milly/cd ump max_dump_file_size string UNLIMITED shadow_core_dump string partial user_dump_dest string /u01/app/oracle/admin/milly/ud ump SQL> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options [oracle@master ~]$ [oracle@master ~]$ tree -L 1 /u01/app/oracle/admin/milly/ /u01/app/oracle/admin/milly/ ├── adump ├── bdump ├── cdump ├── dpdump ├── pfile └── udump 6 directories, 0 files [oracle@master ~]$
备库：
[oracle@slave ~]$ mkdir -p /u01/app/oracle/admin/milly/{adump,bdump,cdump,dpdump,pfile,udump}
[oracle@slave ~]$ tree /u01/app/oracle/admin/milly/
/u01/app/oracle/admin/milly/
├── adump
├── bdump
├── cdump
├── dpdump
├── pfile
└── udump
6 directories, 0 files
[oracle@slave ~]$
【ORACLE数据文件】相关目录
主库：
[oracle@master ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 11 22:48:31 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> set linesize 400
col file_name for a48
col tablespace_name for a18
select
a.file#,b.file_name,b.tablespace_name,b.bytes/1024/1024 "MB",b.maxbytes/1024/1024 "Max MB",to_char(a.CREATION_TIME,'yyyy-mm-dd hh24:mi:ss') "Create Date",b.status
from
v$datafile a,
SQL> SQL> SQL> 2 3 4 5 dba_data_files b
where a.file#=b.file_id
order by tablespace_name,file#; 6 7
FILE# FILE_NAME TABLESPACE_NAME MB Max MB Create Date STATUS
---------- ------------------------------------------------ ------------------ ---------- ---------- ------------------- ---------
5 /u01/app/oracle/oradata/milly/example01.dbf EXAMPLE 100 32767.9844 2016-09-11 22:24:14 AVAILABLE
3 /u01/app/oracle/oradata/milly/sysaux01.dbf SYSAUX 240 32767.9844 2005-10-22 21:44:46 AVAILABLE
1 /u01/app/oracle/oradata/milly/system01.dbf SYSTEM 480 32767.9844 2005-10-22 21:44:31 AVAILABLE
2 /u01/app/oracle/oradata/milly/undotbs01.dbf UNDOTBS1 25 32767.9844 2005-10-22 22:24:27 AVAILABLE
4 /u01/app/oracle/oradata/milly/users01.dbf USERS 5 32767.9844 2005-10-22 21:45:00 AVAILABLE
SQL>备库：
[oracle@slave ~]$ mkdir -p /u01/app/oracle/oradata/milly [oracle@slave ~]$
【ORACLE归档日志】相关目录
主库：
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 2 Current log sequence 2 SQL> SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/flash_recovery _area db_recovery_file_dest_size big integer 2G SQL>
备库：
[oracle@slave ~]$ mkdir -p /u01/app/oracle/flash_recovery_area [oracle@slave ~]$
4. 配置Oracle NET。
主库：
[oracle@master ~]$ cd $ORACLE_HOME/network/admin
[oracle@master admin]$ ls -ltr --time-style="+|%Y-%m-%d|%H:%M:%S|"
total 20
-rw-r----- 1 oracle oinstall 172 |2003-12-26|12:44:00| shrept.lst
drwxr-x--- 2 oracle oinstall 4096 |2016-09-11|20:54:35| samples
-rw-r----- 1 oracle oinstall 192 |2016-09-11|20:57:27| sqlnet.ora
-rw-r----- 1 oracle oinstall 504 |2016-09-11|20:57:27| listener.ora
-rw-r----- 1 oracle oinstall 526 |2016-09-11|22:24:47| tnsnames.ora
[oracle@master admin]$
[oracle@master admin]$ vi listener.ora
[oracle@master admin]$ ls -ltr --time-style="+|%Y-%m-%d|%H:%M:%S|"
total 20
-rw-r----- 1 oracle oinstall 172 |2003-12-26|12:44:00| shrept.lst
drwxr-x--- 2 oracle oinstall 4096 |2016-09-11|20:54:35| samples
-rw-r----- 1 oracle oinstall 192 |2016-09-11|20:57:27| sqlnet.ora
-rw-r----- 1 oracle oinstall 526 |2016-09-11|22:24:47| tnsnames.ora
-rw-r----- 1 oracle oinstall 640 |2016-09-11|22:55:11| listener.ora
[oracle@master admin]$
[oracle@master admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10g/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/10g/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = milly)
(ORACLE_HOME = /u01/app/oracle/product/10g/dbhome_1)
(SID_NAME = milly)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = master)(PORT = 1521))
)
)
[oracle@master admin]$
[oracle@master admin]$ vi tnsnames.ora
[oracle@master admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10g/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
MILLY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = master)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = milly)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
# Oracle Data Guard
master =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = master)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = milly)
)
)
slave =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = slave)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = milly)
)
)
[oracle@master admin]$备库一样的配置。
启动监听器：
[oracle@master admin]$ lsnrctl start LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 11-SEP-2016 23:09:44 Copyright (c) 1991, 2005, Oracle. All rights reserved. Starting /u01/app/oracle/product/10g/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.1.0 - Production System parameter file is /u01/app/oracle/product/10g/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/product/10g/dbhome_1/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=master)(PORT=1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production Start Date 11-SEP-2016 23:09:46 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/10g/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/product/10g/dbhome_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=master)(PORT=1521))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "milly" has 1 instance(s). Instance "milly", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@master admin]$
启动监听器后，测试：
[oracle@master admin]$ tnsping master TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 11-SEP-2016 23:10:44 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/10g/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = master)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = milly))) OK (10 msec) [oracle@master admin]$ [oracle@master admin]$ tnsping slave TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 11-SEP-2016 23:10:48 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/10g/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = slave)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = milly))) OK (0 msec) [oracle@master admin]$
5. 密码文件（主库传到备库）
主库：
[oracle@master ~]$ cd $ORACLE_HOME/dbs [oracle@master dbs]$ ls -ltr total 56 -rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora -rw-r----- 1 oracle oinstall 12920 May 3 2001 initdw.ora -rw-rw---- 1 oracle oinstall 1552 Sep 11 22:22 hc_master.dat -rw-rw---- 1 oracle oinstall 24 Sep 11 22:23 lkMILLY -rw-r----- 1 oracle oinstall 1536 Sep 11 22:24 orapwmilly -rw-r----- 1 oracle oinstall 2560 Sep 11 23:00 spfilemilly.ora -rw-r--r-- 1 oracle oinstall 1008 Sep 12 00:55 initmilly.ora -rw-rw---- 1 oracle oinstall 24 Sep 14 01:02 lkMASTER -rw-rw---- 1 oracle oinstall 1552 Sep 14 01:02 hc_milly.dat [oracle@master dbs]$ pwd /u01/app/oracle/product/10g/dbhome_1/dbs [oracle@master dbs]$ [oracle@master dbs]$ scp orapwmilly slave:/u01/app/oracle/product/10g/dbhome_1/dbs oracle@slave's password: orapwmilly 100% 1536 1.5KB/s 00:00 [oracle@master dbs]$
6. 创建初始化参数文件
主库端，导出初始化参数文件：
[oracle@master DataGuard]$ pwd
/home/oracle/DataGuard
[oracle@master DataGuard]$
[oracle@master DataGuard]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 14 00:36:59 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name,database_role,open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- ----------
MILLY PRIMARY READ WRITE
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10g/db
home_1/dbs/spfilemilly.ora
SQL> create pfile='/home/oracle/DataGuard/milly_master.pfile' from spfile;
File created.
SQL> !ls -ltr --time-style="+|%Y-%m-%d|%H:%M:%S|" /home/oracle/DataGuard
total 4
-rw-r--r-- 1 oracle oinstall 1008 |2016-09-14|00:38:16| milly_master.pfile
SQL> !du -sh /home/oracle/DataGuard/milly_master.pfile
4.0K /home/oracle/DataGuard/milly_master.pfile
SQL> !cat /home/oracle/DataGuard/milly_master.pfile | wc -l
26
SQL> !cat /home/oracle/DataGuard/milly_master.pfile
milly.__db_cache_size=373293056
milly.__java_pool_size=4194304
milly.__large_pool_size=4194304
milly.__shared_pool_size=150994944
milly.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/milly/adump'
*.background_dump_dest='/u01/app/oracle/admin/milly/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/oradata/milly/control01.ctl','/u01/app/oracle/oradata/milly/control02.ctl','/u01/app/oracle/oradata/milly/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/milly/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='milly'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=millyXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=389021696
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/milly/udump'
SQL>
编辑上面导出的初始化参数文件，以便于适合DG主库、备库环境：
[oracle@master DataGuard]$ pwd /home/oracle/DataGuard [oracle@master DataGuard]$ [oracle@master DataGuard]$ ls -ltr --time-style="+|%Y-%m-%d|%H:%M:%S|" total 8 -rw-r--r-- 1 oracle oinstall 1008 |2016-09-14|00:38:16| milly_master.pfile -rw-r--r-- 1 oracle oinstall 1008 |2016-09-14|00:42:20| milly_slave.pfile [oracle@master DataGuard]$
其中：
主库，milly_master.pfile
备库，milly_slave.pfile
主库：milly_master.pfile
[oracle@master DataGuard]$ cat milly_master.pfile milly.__db_cache_size=373293056 milly.__java_pool_size=4194304 milly.__large_pool_size=4194304 milly.__shared_pool_size=150994944 milly.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/milly/adump' *.background_dump_dest='/u01/app/oracle/admin/milly/bdump' *.compatible='10.2.0.1.0' *.control_files='/u01/app/oracle/oradata/milly/control01.ctl','/u01/app/oracle/oradata/milly/control02.ctl','/u01/app/oracle/oradata/milly/control03.ctl' *.core_dump_dest='/u01/app/oracle/admin/milly/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='milly' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=millyXDB)' *.job_queue_processes=10 *.open_cursors=300 *.pga_aggregate_target=389021696 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=536870912 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/milly/udump' *.db_unique_name='master' *.standby_file_management=auto *.fal_server='slave' *.fal_client='master' *.log_archive_config='DG_CONFIG=(slave,master)' *.log_archive_dest_1='location=/dg_data/arch valid_for=(all_logfiles,all_roles) db_unique_name=master' *.log_archive_dest_2='service=slave lgwr async=40960 valid_for=(online_logfiles,primary_role) db_unique_name=slave' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.standby_archive_dest='/dg_data/arch' [oracle@master DataGuard]$
备库：milly_slave.pfile
[oracle@master DataGuard]$ cat milly_slave.pfile milly.__db_cache_size=373293056 milly.__java_pool_size=4194304 milly.__large_pool_size=4194304 milly.__shared_pool_size=150994944 milly.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/milly/adump' *.background_dump_dest='/u01/app/oracle/admin/milly/bdump' *.compatible='10.2.0.1.0' *.control_files='/u01/app/oracle/oradata/milly/control01.ctl','/u01/app/oracle/oradata/milly/control02.ctl','/u01/app/oracle/oradata/milly/control03.ctl' *.core_dump_dest='/u01/app/oracle/admin/milly/cdump' *.db_block_size=8192 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='milly' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.dispatchers='(PROTOCOL=TCP) (SERVICE=millyXDB)' *.job_queue_processes=10 *.open_cursors=300 *.pga_aggregate_target=389021696 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=536870912 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/u01/app/oracle/admin/milly/udump' *.db_unique_name='slave' *.fal_server='master' *.fal_client='slave' *.log_archive_config='dg_config=(master,slave)' *.log_archive_dest_1='location=/dg_data/arch valid_for=(all_logfiles,all_roles) db_unique_name=slave' *.log_archive_dest_2='service=master lgwr async=40960 valid_for=(online_logfiles,primary_role) db_unique_name=master' *.log_archive_dest_state_1='enable' *.log_archive_dest_state_2='enable' *.standby_archive_dest='/dg_data/arch' *.standby_file_management=auto [oracle@master DataGuard]$
创建存放DG归档的目录【/dg_data/arch】：
[root@master ~]# mkdir -p /dg_data/arch [root@master ~]# [root@master ~]# id oracle uid=1200(oracle) gid=1000(oinstall) groups=1000(oinstall),2000(dba),4000(asmdba),6000(oper) [root@master ~]# [root@master ~]# chown -R oracle.oinstall /dg_data/ [root@master ~]# [root@master ~]# ls -ld /dg_data/ drwxr-xr-x 3 oracle oinstall 4096 Sep 14 00:55 /dg_data/ [root@master ~]#
以新的初始化参数文件启动数据库：
主库：
[oracle@master ~]$ ps -ef | grep --color pmon
oracle 3702 1 0 Sep13 ? 00:00:00 ora_pmon_milly
oracle 4351 4324 0 00:59 pts/2 00:00:00 grep --color pmon
[oracle@master ~]$
[oracle@master ~]$ env | grep SID
ORACLE_SID=milly
[oracle@master ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 14 00:59:42 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name,database_role,open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- ----------
MILLY PRIMARY READ WRITE
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10g/db
home_1/dbs/spfilemilly.ora
SQL>
SQL> !cp /u01/app/oracle/product/10g/dbhome_1/dbs/spfilemilly.ora /backup
cp: cannot create regular file `/backup/spfilemilly.ora': Permission denied
SQL> !cp /u01/app/oracle/product/10g/dbhome_1/dbs/spfilemilly.ora /home/oracle/
SQL> !ls -ltr /home/oracle/
total 48
drwxr-xr-x 2 oracle oinstall 4096 Sep 11 20:37 Videos
drwxr-xr-x 2 oracle oinstall 4096 Sep 11 20:37 Templates
drwxr-xr-x 2 oracle oinstall 4096 Sep 11 20:37 Public
drwxr-xr-x 2 oracle oinstall 4096 Sep 11 20:37 Pictures
drwxr-xr-x 2 oracle oinstall 4096 Sep 11 20:37 Music
drwxr-xr-x 2 oracle oinstall 4096 Sep 11 20:37 Downloads
drwxr-xr-x 2 oracle oinstall 4096 Sep 11 20:37 Documents
drwxr-xr-x 2 oracle oinstall 4096 Sep 11 20:37 Desktop
-rw-r--r-- 1 oracle oinstall 1125 Sep 12 00:59 milly_master.ora
drwxr-xr-x 2 oracle oinstall 4096 Sep 14 00:53 DataGuard
-rw-r--r-- 1 oracle oinstall 1008 Sep 14 00:59 origin_milly.pfile
-rw-r----- 1 oracle oinstall 2560 Sep 14 01:00 spfilemilly.ora
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup pfile='/home/oracle/DataGuard/milly_master.pfile';
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 2022144 bytes
Variable Size 159384832 bytes
Database Buffers 373293056 bytes
Redo Buffers 2170880 bytes
Database mounted.
Database opened.
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /dg_data/arch
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SQL>
SQL> !ls -ltr --time-style="+|%Y-%m-%d|%H:%M:%S|" /dg_data
total 4
drwxr-xr-x 2 oracle oinstall 4096 |2016-09-14|01:02:37| arch
SQL>
SQL> !ls -ltr --time-style="+|%Y-%m-%d|%H:%M:%S|" /dg_data/arch
total 25916
-rw-r----- 1 oracle oinstall 26535424 |2016-09-14|01:02:41| 1_2_922314229.dbf
SQL>
备库：
启动实例到nomount。
[oracle@slave ~]$ env | grep SID ORACLE_SID=milly [oracle@slave ~]$ [oracle@slave ~]$ ps -ef | grep pmon oracle 4054 3962 0 01:36 pts/2 00:00:00 grep pmon [oracle@slave ~]$ [oracle@slave ~]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 14 01:36:04 2016 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/home/oracle/milly_slave.pfile'; ORACLE instance started. Total System Global Area 536870912 bytes Fixed Size 2022144 bytes Variable Size 159384832 bytes Database Buffers 373293056 bytes Redo Buffers 2170880 bytes SQL> SQL> create spfile from pfile='/home/oracle/milly_slave.pfile'; File created. SQL>
7. RMAN操作
主库：
[oracle@master dbs]$ df -h
df: `/root/.gvfs': Permission denied
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 18G 7.3G 9.2G 45% /
tmpfs 1.9G 228K 1.9G 1% /dev/shm
/dev/sda1 283M 39M 229M 15% /boot
[oracle@master dbs]$
[oracle@master dbs]$ mkdir /dg_data/rman_data
[oracle@master dbs]$
[oracle@master dbs]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Sep 14 01:39:56 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MILLY (DBID=278281396)
RMAN> backup full format='/dg_data/rman_data/ora_milly_full_%d_%T_%s' database include current controlfile for standby plus archivelog format='/dg_data/rman_data/arch_%d_%T_%s';
Starting backup at 14-SEP-2016 01:41:12
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=922496563
input archive log thread=1 sequence=3 recid=2 stamp=922497909
input archive log thread=1 sequence=4 recid=3 stamp=922498024
input archive log thread=1 sequence=5 recid=4 stamp=922498058
input archive log thread=1 sequence=6 recid=5 stamp=922498321
input archive log thread=1 sequence=7 recid=6 stamp=922498872
channel ORA_DISK_1: starting piece 1 at 14-SEP-2016 01:41:13
channel ORA_DISK_1: finished piece 1 at 14-SEP-2016 01:41:17
piece handle=/dg_data/rman_data/arch_MILLY_20160914_1 tag=TAG20160914T014112 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 14-SEP-2016 01:41:17
Starting backup at 14-SEP-2016 01:41:17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/milly/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/milly/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/milly/example01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/milly/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/milly/users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-SEP-2016 01:41:17
channel ORA_DISK_1: finished piece 1 at 14-SEP-2016 01:42:12
piece handle=/dg_data/rman_data/ora_milly_full_MILLY_20160914_2 tag=TAG20160914T014117 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 14-SEP-2016 01:42:13
channel ORA_DISK_1: finished piece 1 at 14-SEP-2016 01:42:14
piece handle=/dg_data/rman_data/ora_milly_full_MILLY_20160914_3 tag=TAG20160914T014117 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 14-SEP-2016 01:42:14
Starting backup at 14-SEP-2016 01:42:14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=8 recid=7 stamp=922498934
channel ORA_DISK_1: starting piece 1 at 14-SEP-2016 01:42:15
channel ORA_DISK_1: finished piece 1 at 14-SEP-2016 01:42:16
piece handle=/dg_data/rman_data/arch_MILLY_20160914_4 tag=TAG20160914T014214 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 14-SEP-2016 01:42:16
RMAN>
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
1 26.01M DISK 00:00:03 14-SEP-2016 01:41:15
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20160914T014112
Piece Name: /dg_data/rman_data/arch_MILLY_20160914_1
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 2 554547 11-SEP-2016 22:24:45 561740 14-SEP-2016 01:02:34
1 3 561740 14-SEP-2016 01:02:34 562478 14-SEP-2016 01:25:09
1 4 562478 14-SEP-2016 01:25:09 562670 14-SEP-2016 01:27:01
1 5 562670 14-SEP-2016 01:27:01 582760 14-SEP-2016 01:27:38
1 6 582760 14-SEP-2016 01:27:38 583348 14-SEP-2016 01:31:58
1 7 583348 14-SEP-2016 01:31:58 583624 14-SEP-2016 01:41:12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
2 Full 573.18M DISK 00:00:47 14-SEP-2016 01:42:04
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20160914T014117
Piece Name: /dg_data/rman_data/ora_milly_full_MILLY_20160914_2
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
1 Full 583631 14-SEP-2016 01:41:17 /u01/app/oracle/oradata/milly/system01.dbf
2 Full 583631 14-SEP-2016 01:41:17 /u01/app/oracle/oradata/milly/undotbs01.dbf
3 Full 583631 14-SEP-2016 01:41:17 /u01/app/oracle/oradata/milly/sysaux01.dbf
4 Full 583631 14-SEP-2016 01:41:17 /u01/app/oracle/oradata/milly/users01.dbf
5 Full 583631 14-SEP-2016 01:41:17 /u01/app/oracle/oradata/milly/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
3 Full 6.77M DISK 00:00:01 14-SEP-2016 01:42:13
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20160914T014117
Piece Name: /dg_data/rman_data/ora_milly_full_MILLY_20160914_3
Standby Control File Included: Ckp SCN: 583654 Ckp time: 14-SEP-2016 01:42:12
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
4 11.50K DISK 00:00:01 14-SEP-2016 01:42:15
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20160914T014214
Piece Name: /dg_data/rman_data/arch_MILLY_20160914_4
List of Archived Logs in backup set 4
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 8 583624 14-SEP-2016 01:41:12 583657 14-SEP-2016 01:42:14
RMAN> exit
Recovery Manager complete.
[oracle@master dbs]$
8. 将主库的RMAN传到备库：
备库创建Rman目录：
[oracle@slave ~]$ mkdir /dg_data/rman_data [oracle@slave ~]$
主库传文件：
[oracle@master dbs]$ cd /dg_data/ [oracle@master dg_data]$ ls -ltr total 8 drwxr-xr-x 2 oracle oinstall 4096 Sep 14 01:42 arch drwxr-xr-x 2 oracle oinstall 4096 Sep 14 01:42 rman_data [oracle@master dg_data]$ du -sh * 26M arch 606M rman_data [oracle@master dg_data]$ [oracle@master dg_data]$ scp rman_data/* slave:/dg_data/rman_data/ oracle@slave's password: arch_MILLY_20160914_1 100% 26MB 26.0MB/s 00:00 arch_MILLY_20160914_4 100% 12KB 12.0KB/s 00:00 ora_milly_full_MILLY_20160914_2 100% 573MB 16.4MB/s 00:35 ora_milly_full_MILLY_20160914_3 100% 6944KB 6.8MB/s 00:00 [oracle@master dg_data]$
9. RMAN Duplicate。
主库：
[oracle@master dg_data]$ rman target / auxiliary sys/oracle@slave
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Sep 14 01:46:37 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MILLY (DBID=278281396)
connected to auxiliary database: MILLY (not mounted)
RMAN>
RMAN> duplicate target database for standby nofilenamecheck dorecover;
Starting Duplicate Db at 14-SEP-2016 01:47:04
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
contents of Memory Script:
{
set until scn 583657;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
executing command: SET until clause
Starting restore at 14-SEP-2016 01:47:04
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /dg_data/rman_data/ora_milly_full_MILLY_20160914_3
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/dg_data/rman_data/ora_milly_full_MILLY_20160914_3 tag=TAG20160914T014117
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output filename=/u01/app/oracle/oradata/milly/control01.ctl
output filename=/u01/app/oracle/oradata/milly/control02.ctl
output filename=/u01/app/oracle/oradata/milly/control03.ctl
Finished restore at 14-SEP-2016 01:47:10
sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
set until scn 583657;
set newname for tempfile 1 to
"/u01/app/oracle/oradata/milly/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/milly/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/milly/undotbs01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/milly/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/milly/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/milly/example01.dbf";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
renamed temporary file 1 to /u01/app/oracle/oradata/milly/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 14-SEP-2016 01:47:16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/milly/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/milly/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/milly/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/milly/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/milly/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /dg_data/rman_data/ora_milly_full_MILLY_20160914_2
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/dg_data/rman_data/ora_milly_full_MILLY_20160914_2 tag=TAG20160914T014117
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 14-SEP-2016 01:48:12
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=7 stamp=922499292 filename=/u01/app/oracle/oradata/milly/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=8 stamp=922499292 filename=/u01/app/oracle/oradata/milly/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=9 stamp=922499292 filename=/u01/app/oracle/oradata/milly/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=10 stamp=922499292 filename=/u01/app/oracle/oradata/milly/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=11 stamp=922499292 filename=/u01/app/oracle/oradata/milly/example01.dbf
contents of Memory Script:
{
set until scn 583657;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 14-SEP-2016 01:48:12
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 8 is already on disk as file /dg_data/arch/1_8_922314229.dbf
archive log filename=/dg_data/arch/1_8_922314229.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:02
Finished recover at 14-SEP-2016 01:48:14
Finished Duplicate Db at 14-SEP-2016 01:48:14
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@master dg_data]$
10. 备库创建standby log：
备库：
[oracle@slave ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 14 01:51:02 2016
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name,database_role,open_mode from v$database;
NAME DATABASE_ROLE OPEN_MODE
--------- ---------------- ----------
MILLY PHYSICAL STANDBY MOUNTED
SQL> set linesize 400
SQL> col name for a45
SQL> col member for a70
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
3 INVALID ONLINE /u01/app/oracle/flash_recovery_area/SLAVE/onlinelog/o1_mf_3_%u_.log YES
2 INVALID ONLINE /u01/app/oracle/flash_recovery_area/SLAVE/onlinelog/o1_mf_2_%u_.log YES
1 INVALID ONLINE /u01/app/oracle/flash_recovery_area/SLAVE/onlinelog/o1_mf_1_%u_.log YES
SQL>
SQL> !mkdir /dg_data/standby_log
SQL> alter database add standby logfile group 4 ('/dg_data/standby_log/stb_redo04a.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 5 ('/dg_data/standby_log/stb_redo05a.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 6 ('/dg_data/standby_log/stb_redo06a.log') size 50m;
Database altered.
SQL> alter database add standby logfile group 7 ('/dg_data/standby_log/stb_redo07a.log') size 50m;
Database altered.
SQL>
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- ---------------------------------------------------------------------- ---
3 INVALID ONLINE /u01/app/oracle/flash_recovery_area/SLAVE/onlinelog/o1_mf_3_%u_.log YES
2 INVALID ONLINE /u01/app/oracle/flash_recovery_area/SLAVE/onlinelog/o1_mf_2_%u_.log YES
1 INVALID ONLINE /u01/app/oracle/flash_recovery_area/SLAVE/onlinelog/o1_mf_1_%u_.log YES
4 STANDBY /dg_data/standby_log/stb_redo04a.log NO
5 STANDBY /dg_data/standby_log/stb_redo05a.log NO
6 STANDBY /dg_data/standby_log/stb_redo06a.log NO
7 STANDBY /dg_data/standby_log/stb_redo07a.log??? NO
7 rows selected.
SQL>
查看当前的归档日志的应用状态：
SQL> select thread#,name,sequence#,applied from v$archived_log order by sequence#;
THREAD# NAME SEQUENCE# APP
---------- --------------------------------------------- ---------- ---
1 /dg_data/arch/1_2_922314229.dbf 2 NO
1 /dg_data/arch/1_3_922314229.dbf 3 NO
1 /dg_data/arch/1_4_922314229.dbf 4 NO
1 /dg_data/arch/1_5_922314229.dbf 5 NO
1 /dg_data/arch/1_6_922314229.dbf 6 NO
1 /dg_data/arch/1_7_922314229.dbf 7 YES
1 /dg_data/arch/1_8_922314229.dbf 8 NO
7 rows selected.
SQL>
启动DG备库的应用进程：
SQL> col client_pid for a20; SQL> select process,pid,client_pid,status,delay_mins,known_agents,active_agents from v$managed_standby; PROCESS PID CLIENT_PID STATUS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS --------- ---------- -------------------- ------------ ---------- ------------ ------------- ARCH 4114 4114 CONNECTED 0 0 0 ARCH 4116 4116 CONNECTED 0 0 0 RFS 4127 4721 IDLE 0 0 0 SQL> SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> SQL> select process,pid,client_pid,status,delay_mins,known_agents,active_agents from v$managed_standby; PROCESS PID CLIENT_PID STATUS DELAY_MINS KNOWN_AGENTS ACTIVE_AGENTS --------- ---------- -------------------- ------------ ---------- ------------ ------------- ARCH 4114 4114 CONNECTED 0 0 0 ARCH 4116 4116 CONNECTED 0 0 0 RFS 4127 4721 IDLE 0 0 0 MRP0 4158 N/A WAIT_FOR_LOG 0 0 0 SQL>
再次查看备库的归档日志应用状态：
SQL> select thread#,name,sequence#,applied from v$archived_log order by sequence#;
THREAD# NAME SEQUENCE# APP
---------- --------------------------------------------- ---------- ---
1 /dg_data/arch/1_2_922314229.dbf 2 YES
1 /dg_data/arch/1_3_922314229.dbf 3 YES
1 /dg_data/arch/1_4_922314229.dbf 4 YES
1 /dg_data/arch/1_5_922314229.dbf 5 YES
1 /dg_data/arch/1_6_922314229.dbf 6 YES
1 /dg_data/arch/1_7_922314229.dbf 7 YES
1 /dg_data/arch/1_8_922314229.dbf 8 YES
7 rows selected.
SQL>可以看到，至此，Oracle 10g DG 物理备库的搭建就完成了。
——————————————
其他的一些有用的查询：
SQL> select thread#,sequence#,name,applied,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') "First",to_char(next_time,'yyyy-mm-dd hh24:mi:ss') "Next" from v$archived_log;
THREAD# SEQUENCE# NAME APP First Next
---------- ---------- --------------------------------------------- --- ------------------- -------------------
1 2 /dg_data/arch/1_2_922314229.dbf YES 2016-09-11 22:24:45 2016-09-14 01:02:34
1 3 /dg_data/arch/1_3_922314229.dbf YES 2016-09-14 01:02:34 2016-09-14 01:25:09
1 4 /dg_data/arch/1_4_922314229.dbf YES 2016-09-14 01:25:09 2016-09-14 01:27:01
1 5 /dg_data/arch/1_5_922314229.dbf YES 2016-09-14 01:27:01 2016-09-14 01:27:38
1 6 /dg_data/arch/1_6_922314229.dbf YES 2016-09-14 01:27:38 2016-09-14 01:31:58
1 7 /dg_data/arch/1_7_922314229.dbf YES 2016-09-14 01:31:58 2016-09-14 01:41:12
1 8 /dg_data/arch/1_8_922314229.dbf YES 2016-09-14 01:41:12 2016-09-14 01:42:14
7 rows selected.
SQL>
SQL> Set linesize 400;
Col name for a65;
select thread#,sequence#,name,applied,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') "First",to_char(next_time,'yyyy-mm-dd hh24:mi:ss') "Next" from v$archived_log ,(select thread# t,max(sequence#) s from v$archived_log where applied='YES' group by thread#) b where sequence#=b.s order by thread#;SQL> SQL>
THREAD# SEQUENCE# NAME APP First Next
---------- ---------- ----------------------------------------------------------------- --- ------------------- -------------------
1 8 /dg_data/arch/1_8_922314229.dbf YES 2016-09-14 01:41:12 2016-09-14 01:42:14
SQL>——————————————————
Done。