这部分需要添加standby redo log,添加日志相关操作请参照与DG physical DB 转 snapshot DB第一部分, 以下内容摘自physical DB 转 snapshot DB第一部分
成都网站建设 、网站建设的关注点不是能为您做些什么网站,而是怎么做网站,有没有做好网站,给成都创新互联公司 一个展示的机会来证明自己,这并不会花费您太多时间,或许会给您带来新的灵感和惊喜。面向用户友好,注重用户体验,一切以用户为中心。
在备库设置快速恢复区,大小,路径,创建4组standby redo log
[root@sink ~]# su - oracle
[oracle@sink ~]$ echo $ORACLE_SID
sink
[oracle@sink ~]$ export ORACLE_SID=gotime
[oracle@sink ~]$ echo $ORACLE_SID
gotime
[oracle@sink ~]$ !sql
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 12 10:25:51 2018
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
10:25:51 SYS @ gotime >select status from v$instance;
STATUS
------------
MOUNTED
1 row selected.
Elapsed: 00:00:00.01
10:26:03 SYS @ gotime >select name,database_role,protection_mode,open_mode from v$database;
NAME DATABASE_ROLE PROTECTION_MODE OPEN_MODE
--------- ---------------- -------------------- --------------------
SLOW PHYSICAL STANDBY MAXIMUM PERFORMANCE MOUNTED
1 row selected.
Elapsed: 00:00:00.02
10:26:54 SYS @ gotime >show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
10:29:42 SYS @ gotime >recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active
10:30:31 SYS @ gotime >alter system set db_recovery_file_dest_size=4g;
System altered.
Elapsed: 00:00:00.00
10:32:25 SYS @ gotime >edit
Wrote file afiedt.buf
1* alter system set db_recovery_file_dest='/dsk1'
10:32:36 SYS @ gotime >r
1* alter system set db_recovery_file_dest='/dsk1'
System altered.
Elapsed: 00:00:00.00
10:32:37 SYS @ gotime >show parameter recover;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /dsk1
db_recovery_file_dest_size big integer 4G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
10:39:51 SYS @ gotime >edit
Wrote file afiedt.buf
1* alter system set db_recovery_file_dest_size=6g
10:40:14 SYS @ gotime >r
1* alter system set db_recovery_file_dest_size=6g
System altered.
Elapsed: 00:00:00.00
10:40:15 SYS @ gotime >alter database convert to snapshot standby;
alter database convert to snapshot standby
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_01/12/2018 10:40:26'.
ORA-38788: More standby database recovery is needed
Elapsed: 00:00:00.00
----------------意思是提示备库没有创建standby redo log所以下面创建4组-------------------
13:16:50 SYS @ gotime >alter system set db_recovery_file_dest_size=4g;
System altered.
Elapsed: 00:00:00.01
13:18:31 SYS @ gotime >edit
Wrote file afiedt.buf
1* select group#,thread#,sequence#,archived,status from v$standby_log
13:18:55 SYS @ gotime >r
1* select group#,thread#,sequence#,archived,status from v$standby_log
no rows selected
Elapsed: 00:00:00.00
13:18:55 SYS @ gotime >select member from v$logfile;
MEMBER
------------------------
/u01/app/oracle/oradata/gotime/redo01a.log
/u01/app/oracle/oradata/gotime/redo01b.log
/u01/app/oracle/oradata/gotime/redo02a.log
/u01/app/oracle/oradata/gotime/redo02b.log
/u01/app/oracle/oradata/gotime/redo03a.log
/u01/app/oracle/oradata/gotime/redo03b.log
6 rows selected.
Elapsed: 00:00:00.01
备库上依据redo log的路径建立4组standby redo log
13:21:44 SYS @ gotime >edit
Wrote file afiedt.buf
1 alter database add standby logfile group 4
2* ('/u01/app/oracle/oradata/gotime/&a') size 200m
13:22:33 SYS @ gotime >r
1 alter database add standby logfile group 4
2* ('/u01/app/oracle/oradata/gotime/&a') size 200m
Enter value for a: standby04.log
old 2: ('/u01/app/oracle/oradata/gotime/&a') size 200m
new 2: ('/u01/app/oracle/oradata/gotime/standby04.log') size 200m
Database altered.
Elapsed: 00:00:01.74
13:23:02 SYS @ gotime >edit
Wrote file afiedt.buf
1* alter database add standby logfile group 4
13:23:12 SYS @ gotime >edit
Wrote file afiedt.buf
1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/gotime/&b') size 200m
13:23:58 SYS @ gotime >r
1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/gotime/&b') size 200m
Enter value for a: 5
Enter value for b: standby05.log
old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/gotime/&b')size 200m
new 1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/gotime/standby05.log') size 200m
Database altered.
Elapsed: 00:00:02.02
13:24:16 SYS @ gotime >r
1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/gotime/&b') size 200m
Enter value for a: 6
Enter value for b: standby06.log
old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/gotime/&b')size 200m
new 1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/gotime/standby06.log') size 200m
Database altered.
Elapsed: 00:00:01.98
13:24:35 SYS @ gotime >r
1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/gotime/&b') size 200m
Enter value for a: 7
Enter value for b: standby07.log
old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/gotime/&b')size 200m
new 1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/gotime/standby07.log') size 200m
Database altered.
Elapsed: 00:00:01.69
13:24:50 SYS @ gotime >select group#,thread#,sequence#,archived,status from v$standby_log
13:25:15 2 ;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED
4 rows selected.
Elapsed: 00:00:00.00
13:25:16 SYS @ gotime >
10:24:42 SYS @ slow >startup
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 377489432 bytes
Database Buffers 138412032 bytes
Redo Buffers 3780608 bytes
Database mounted.
Database opened.
10:24:57 SYS @ slow >select name,database_role,protection_mode,open_mode from v$database;
NAME DATABASE_ROLE PROTECTION_MODE OPEN_MODE
--------- ---------------- -------------------- --------------------
SLOW PRIMARY MAXIMUM PERFORMANCE READ WRITE
1 row selected.
Elapsed: 00:00:00.02
10:28:35 SYS @ slow >show parameter recover;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
10:37:21 SYS @ slow >alter system set db_recovery_file_dest_size=4g;
System altered.
Elapsed: 00:00:00.00
10:37:54 SYS @ slow >alter system set db_recovery_file_dest='/dsk1';
System altered.
Elapsed: 00:00:00.01
10:38:19 SYS @ slow >select member from v$logfile;
MEMBER
-----------------------------------------
/u01/app/oracle/oradata/slow/redo01a.log
/u01/app/oracle/oradata/slow/redo01b.log
/u01/app/oracle/oradata/slow/redo02a.log
/u01/app/oracle/oradata/slow/redo02b.log
/u01/app/oracle/oradata/slow/redo03a.log
/u01/app/oracle/oradata/slow/redo03b.log
6 rows selected.
Elapsed: 00:00:00.01
主库上依据redo log的路径建立4组standby redo log
13:26:06 SYS @ slow >edit
Wrote file afiedt.buf
1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
13:27:02 SYS @ slow >r
1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
Enter value for a: 4
Enter value for b: standby04.log
old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size200m
new 1: alter database add standby logfile group 4 ('/u01/app/oracle/oradata/slow/standby04.log') size 200m
Database altered.
Elapsed: 00:00:02.73
13:27:19 SYS @ slow >r
1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
Enter value for a: 5
Enter value for b: standby05.log
old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size200m
new 1: alter database add standby logfile group 5 ('/u01/app/oracle/oradata/slow/standby05.log') size 200m
Database altered.
Elapsed: 00:00:03.50
13:27:44 SYS @ slow >r
1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
Enter value for a: 6
Enter value for b: standby06.log
old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size200m
new 1: alter database add standby logfile group 6 ('/u01/app/oracle/oradata/slow/standby06.log') size 200m
Database altered.
Elapsed: 00:00:02.04
13:28:03 SYS @ slow >r
1* alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size 200m
Enter value for a: 7
Enter value for b: standby07.log
old 1: alter database add standby logfile group &a ('/u01/app/oracle/oradata/slow/&b') size200m
new 1: alter database add standby logfile group 7 ('/u01/app/oracle/oradata/slow/standby07.log') size 200m
Database altered.
Elapsed: 00:00:02.33
13:28:19 SYS @ slow >select group#,thread#,sequence#,archived,status from v$standby_log;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED
4 rows selected.
Elapsed: 00:00:00.00
主库修改传输方式sync affirm ,下面备库也将跟着修改
13:55:51 SYS @ slow >r
1* alter system set log_archive_dest_2='SERVICE=gotime OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=gotime' scope=spfile
System altered.
Elapsed: 00:00:00.00
备库没有设置spfile,是以pfile打开库的,所以创建spifle,以spfile打开,才能修改
13:57:43 SYS @ gotime >edit
Wrote file afiedt.buf
1* alter system set log_archive_dest_2='SERVICE=slow optional sync AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=slow' scope=spfile
13:58:01 SYS @ gotime >r
1* alter system set log_archive_dest_2='SERVICE=slow optional sync AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=slow' scope=spfile
alter system set log_archive_dest_2='SERVICE=slow optional sync AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=slow' scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use
Elapsed: 00:00:00.00
13:58:02 SYS @ gotime >create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file '?/dbs/spfile@.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Elapsed: 00:00:00.00
13:59:06 SYS @ gotime >create spfile from pfile;
File created.
Elapsed: 00:00:00.02
14:00:45 SYS @ gotime >alter system set log_archive_dest_2='SERVICE=slow optional sync AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=slow' scope=spfile;
alter system set log_archive_dest_2='SERVICE=slow optional sync AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=slow' scope=spfile
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE is in use
Elapsed: 00:00:00.00
14:01:06 SYS @ gotime >shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
14:01:37 SYS @ gotime >startup mount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 377489432 bytes
Database Buffers 138412032 bytes
Redo Buffers 3780608 bytes
Database mounted.
14:01:57 SYS @ gotime >alter system set log_archive_dest_2='SERVICE=slow optional sync AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=slow' scope=spfile;
System altered.
Elapsed: 00:00:00.01
14:02:09 SYS @ gotime >
主库开始转换,成功的从maximum performance 到 maximum protection模式
13:55:52 SYS @ slow >startup mount force;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 377489432 bytes
Database Buffers 138412032 bytes
Redo Buffers 3780608 bytes
Database mounted.
14:09:15 SYS @ slow >select status from v$instance;
STATUS
------------
MOUNTED
1 row selected.
Elapsed: 00:00:00.01
14:09:29 SYS @ slow >select name,database_role,protection_mode from v$database;
NAME DATABASE_ROLE PROTECTION_MODE
--------- ---------------- --------------------
SLOW PRIMARY MAXIMUM PERFORMANCE
1 row selected.
Elapsed: 00:00:00.01
14:10:02 SYS @ slow >alter database set standby database to maximize protection;
Database altered.
Elapsed: 00:00:00.01
14:10:52 SYS @ slow >alter database open;
Database altered.
Elapsed: 00:00:03.83
14:11:20 SYS @ slow >select name,database_role,protection_mode from v$database;
NAME DATABASE_ROLE PROTECTION_MODE
--------- ---------------- --------------------
SLOW PRIMARY MAXIMUM PROTECTION
1 row selected.
Elapsed: 00:00:00.00
14:11:45 SYS @ slow >
14:14:09 SYS @ gotime >select status from v$instance;
STATUS
------------
MOUNTED
1 row selected.
Elapsed: 00:00:00.01
14:14:38 SYS @ gotime >select name,database_role,protection_mode from v$database;
NAME DATABASE_ROLE PROTECTION_MODE
--------- ---------------- --------------------
SLOW PHYSICAL STANDBY MAXIMUM PROTECTION
1 row selected.
Elapsed: 00:00:00.00
14:15:03 SYS @ gotime >alter database open;
Database altered.
Elapsed: 00:00:00.41
14:15:10 SYS @ gotime >select name,database_role,protection_mode from v$database;
NAME DATABASE_ROLE PROTECTION_MODE
--------- ---------------- --------------------
SLOW PHYSICAL STANDBY MAXIMUM PROTECTION
1 row selected.
Elapsed: 00:00:00.01
14:18:31 SYS @ slow >select * from t2017;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected.
Elapsed: 00:00:00.00
14:18:46 SYS @ slow >insert into t2017 select * from t2017;
4 rows created.
Elapsed: 00:00:00.00
14:19:29 SYS @ slow >select * from t2017;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
8 rows selected.
Elapsed: 00:00:00.00
14:19:43 SYS @ slow >commit;
Commit complete.
Elapsed: 00:00:00.00
14:19:45 SYS @ slow >insert into t2017 select * from t2017;
8 rows created.
Elapsed: 00:00:00.00
14:20:42 SYS @ slow >select * from t2017;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
16 rows selected.
Elapsed: 00:00:00.00
14:21:18 SYS @ slow >alter system switch logfile;
System altered.
Elapsed: 00:00:00.12
14:23:28 SYS @ slow >
免费获取网站建设与品牌策划方案报价
*主要业务范围包括:高端网站建设, 集团网站建设(网站建设网站制作)找网站建设公司就上快上网。