maximumperformance转换maximumprotection
这部分需要添加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 >