我们公司的Oracle DB从7345到9205都有,在很早以前,我就把它的standby给搞定了,不过用的是自己写的代码。Standby架构弄好以后,主要是用它恢复正式库,每个月都用这个standby机制作2次正式数据库的恢复演练,感觉运行了2年多了,也是瞒顺的,缺点用定时执行apply的程序去apply archive,时机有时掌握不够好,并且8i的版本的好像不能read only,用户需要恢复数据都是open resetlogs,然后再把standby更新一次。最近,看了一下网上的文章,从815往后都可以使用oracle原版的standby,Main产生的archive都能够及时地apply到standby上面去。9ir2后的版本还有逻辑standby,关键有一点,听说9i的standby还可做到Main DB挂了,他的current-redo-log里面东东也不会丢失,这难道是传说中的 NON_DATA_LOSE ,看起来的确很诱人。今天先来做做oracle的物理standby,记录如下:
Environment:
Oracle:9.0.1.1
OS:Solaris 5.10
Main: 192.1.1.44 Standby 192.1.1.45
Prepared by:Compard
I. Action in Main:
1.create user group dba and member oracle, create a home folder for oracle.
2.copy a .profile template and update it to match the environment.
3.update /etc/system and reboot.
4.use oracle account to login to 192.1.1.44 with X tools and create the main database.
5.After step 4 ,check the DB if it's in archivelog mode.
6.create a new control file named standbycontrolfile to for standbyDB,SQL as the following:
alter
database
create
standby controlfile
as
'
$ORACLE_HOME/dbs/standbycontrol.ctl
'
7.Shutdown DB,stop listener.
II. Action in Standby:
1.create user and group same as main db.
2.copy all of the file from Main db, include home folder for oracle and data files/standbycontrol file /Redo Files in Main DB except control files.Copy '$ORACLE_HOME/dbs/standbycontrol.ctl' in Main DB to standby
db, renames it to match the parameter control_files in spfile/pfile.
III. Action in Main:
1.Open database and listener.
2.edit file $ORACLE_HOME/network/admin/tnsnames.ora, add a node names standby_db,link to standby DB.
3.check the archive log location, it must exist at lest 1 valid location,if standby at lest 2 valid location, one tolocally, the other to standby, so please check the following parameters, for example:
log_archive_format
=
'
arc%t_%s.arc
'
log_archive_start
=
true
#locally
log_archive_dest_1
=
'
location=/disk2/aaa/arch
'
# You'd better choose a different location
from
the datafile.
log_archive_dest_state_1
=
enable
#standby
#"madatory" specifies that filled online log files must be successfully archived to the destination before they can be reused.
#"reopen" specifies the minimum number of seconds before redo transport services should try to reopen a failed destination.
log_archive_dest_2
=
'
service=standby_db mandatory reopen=30
'
# standby_db
is
a nodename define
in
tnsnames.ora.
log_archive_dest_state_2
=
enable
#Choose the following parameter
to
control the minimum
number
of
destinations that must succeed
in
order
for
the online logfile
to
be available
for
reuse.
log_archive_min_succeed_dest
=
1
4.please restart db after setting in step 3.
IV. Action in Standby:
1.edit file $ORACLE_HOME/network/admin/listener.ora, update default listener to standby listener.
standby
=
(DESCRIPTION_LIST
=
(DESCRIPTION
=
(ADDRESS_LIST
=
(ADDRESS
=
(PROTOCOL
=
IPC)(
KEY
=
EXTPROC))
)
(ADDRESS_LIST
=
(ADDRESS
=
(PROTOCOL
=
TCP)(HOST
=
192.1
.
1.45
)(PORT
=
1521
))
)
)
)
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SID_LIST_standby
=
(SID_LIST
=
(SID_DESC
=
(SID_NAME
=
PLSExtProc)
(ORACLE_HOME
=
/
export
/
home
/
oracle)
(PROGRAM
=
extproc)
)
(SID_DESC
=
(GLOBAL_DBNAME
=
aaa)
(ORACLE_HOME
=
/
export
/
home
/
oracle)
(SID_NAME
=
ASRS)
)
)
2.create the archive log destination folder to match the parameter log_archive_dest in spfile/pfile.
3.start listener
4.user the following SQL to open standby DB to standby mode.
startup nomount
alter
database
mount standby
database
;
alter
database
recover managed standby
database
disconnect
from
session;
V.TEST standby struct
Main DB:
SQL
>
conn scott
/
tiger
Connected.
SQL
>
create
table
aaa
as
select
*
from
dual;
Table
created.
SQL
>
select
table_name
from
user_tables;
TABLE_NAME
--
----------------------------------------------------------
AAA
BONUS
DEPT
EMP
SALGRADE
SQL
>
drop
table
aaa;
Table
dropped.
SQL
>
conn
/
as
sysdba
Connected.
SQL
>
alter
system switch logfile;
System altered.
SQL
>
alter
system switch logfile;
Standby DB:
1.you can find the archive log files have been transfered to archive file's folders in standby DB.
2.use sqlplus tools to check if the table aaa is exist or not:
SQL
>
shutdown
immediate
Database
closed.
Database
dismounted.
ORACLE instance shut down.
SQL
>
startup nomount
ORACLE instance started.
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
Total System Global Area
286187904
bytes
Fixed Size
434560
bytes
Variable Size
218103808
bytes
Database
Buffers
67108864
bytes
Redo Buffers
540672
bytes
SQL
>
alter
database
mount standby
database
; ![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
Database
altered.![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SQL
>
alter
database
recover managed standby
database
disconnect
from
session;![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
Database
altered.![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SQL
>
!who
root pts
/
2
Dec
19
19
:
34
(
172.16
.
87.98
)
SQL
>
alter
database
recover managed standby
database
cancel;
Database
altered.
SQL
>
alter
database
open
read
only
;
Database
altered.
SQL
>
conn scott
/
tiger
Connected.
SQL
>
select
table_name
from
user_tables;
TABLE_NAME
--
----------------------------------------------------------
BONUS
DEPT
EMP
SALGRADE
SQL
>
conn
/
as
sysdba
Connected.
SQL
>
alter
database
recover managed standby
database
disconnect
from
session;
alter
database
recover managed standby
database
disconnect
from
session;
*
ERROR at line
1
:
ORA
-
01093
:
ALTER
DATABASE
CLOSE
only
permitted
with
no sessions connected
SQL
>
select
'
kill -9 -
'
||
b.spid
from
v$session a,v$process b
2
where
a.paddr
=
b.addr
3
and
a.type
<>
'
BACKGROUND
'
;
SQL
>
!
kill
-
9
1029
SQL
>
alter
database
recover managed standby
database
disconnect
from
session;
Database
altered.
VI. FAQ:
Q1:ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed
A1:在Main上重新启动DB的时候,报这个错误,最后发现修改了log_archive_dest_1,把/disk2/aaa/arch' 改成'location=/disk2/aaa/arch' 后成功。
Q2:在standby DB的alter文件发现如下错误:
Wed Dec
19
15
:
48
:
39
2007
Fetching gap sequence for thread
1
,
gap sequence
1
-
1
Trying FAL server:
Error fetching gap sequence
,
no FAL server specified
Wed Dec
19
15
:
48
:
54
2007
Failed to request gap sequence. Thread #:
1
,
gap sequence:
1
-
1
All FAL server has been attempted.
Wed Dec
19
18
:
03
:
48
2007
Shutting down instance: further logons disabled
Wed Dec
19
18
:
03
:
53
2007
MRP0: Background Media Recovery terminating as requested
MRP0: The following warnings/errors are found:
ORA-
01547
: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-
01152
: file
1
was not restored from a sufficiently old backup
ORA-
01110
: data file
1
: '/export/home/oracle/oradata/aaa/system01.dbf'
ORA-
16037
: user requested cancel of managed recovery operation
MRP0: Background Media Recovery process is now terminated
A2:检查了一下,发现standby DB control文件产生的时候,MainDB还是noarchivelog模式,最后重新备份了一下,确认standby的DB也是archivelog状态后可以自动apply了。不知道是不是这个原因。
Q3:今天手工运行了一下alter system switch logfile; hang机了。
A3:发现是standby_db这个node指向DB不对,解决后正常了,这样同样有个问题,如果网络传输慢或者standby机器出问题不能及时解决,那岂不是有很严重的性能问题?
Q4:如果正式的挂了,某个数据文件坏能否用standby上面的数据文件,如果整个数据库挂了,如何把standby数据库激活成主的:
A4:下面是模拟Main数据库关闭后,把standby激活成Main DB的过程:
$ sqlplus
/
nolog
SQL
*
Plus: Release
9.0
.
1.0
.
0
-
Production
on
Thu
Dec
20
09
:
23
:
56
2007
(c) Copyright
2001
Oracle Corporation.
All
rights reserved.
SQL
>
conn
/
as
sysdba
Connected.
SQL
>
alter
database
open
;
alter
database
open
*
ERROR at line
1
:
ORA
-
16003
: standby
database
is
restricted
to
read
-
only
access
SQL
>
alter
database
mount standby
database
;
alter
database
mount standby
database
*
ERROR at line
1
:
ORA
-
01100
:
database
already mounted
SQL
>
alter
database
activate standby
database
;
alter
database
activate standby
database
*
ERROR at line
1
:
ORA
-
01679
:
database
must be mounted EXCLUSIVE
and
not
open
to
activate
SQL
>
show parameter password
NAME TYPE
--
---------------------------------- ----------------------
VALUE
--
----------------------------
remote_login_passwordfile string
EXCLUSIVE
SQL
>
alter
database
activate standby
database
;
alter
database
activate standby
database
*
ERROR at line
1
:
ORA
-
01679
:
database
must be mounted EXCLUSIVE
and
not
open
to
activate
SQL
>
shutdown
immediate
ORA
-
01109
:
database
not
open
Database
dismounted.
ORACLE instance shut down.
SQL
>
!ps
-
ef
|
grep ora_
oracle
9861
9855
0
09
:
27
:
50
pts
/
2
0
:
00
/
bin
/
sh
-
c ps
-
ef
|
grep ora_
oracle
9862
9861
0
09
:
27
:
50
pts
/
2
0
:
00
grep ora_
SQL
>
startup nomount;
ORACLE instance started.
Total System Global Area
286187904
bytes
Fixed Size
434560
bytes
Variable Size
218103808
bytes
Database
Buffers
67108864
bytes
Redo Buffers
540672
bytes
SQL
>
alter
database
mount standby
database
;
Database
altered.
SQL
>
alter
database
activate standby
database
;
Database
altered.
SQL
>
archive
log
list;
ORA
-
01507
:
database
not
mounted
SQL
>
shutdown
immediate
ORA
-
01507
:
database
not
mounted
ORACLE instance shut down.
SQL
>
SQL
>
startup
ORACLE instance started.
Total System Global Area
286187904
bytes
Fixed Size
434560
bytes
Variable Size
218103808
bytes
Database
Buffers
67108864
bytes
Redo Buffers
540672
bytes
Database
mounted.
Database
opened.
SQL
>
----这时候standby DB就是主的了,如果关闭再执行下面SQL就会报错:
SQL
>
alter
database
mount standby
database
;
alter
database
mount standby
database
*
ERROR at line
1
:
ORA
-
01665
: controlfile
is
not
a standby controlfile
下面是模拟Main DB上删除了一个数据文件,利用standby恢复的过程:
SQL
>
shutdown
immediate
Database
closed.
Database
dismounted.
ORACLE instance shut down.
SQL
>
!mv
/
export
/
home
/
oracle
/
oradata
/
aaa
/
tools01.dbf
/
export
/
home
/
oracle
/
oradat
/
aaa
/
tools01.bak
SQL
>
startup
ORACLE instance started.
Total System Global Area
286187904
bytes
Fixed Size
434560
bytes
Variable Size
218103808
bytes
Database
Buffers
67108864
bytes
Redo Buffers
540672
bytes
Database
mounted.
ORA
-
01157
: cannot identify
/
lock data
file
7
-
see DBWR trace
file
ORA
-
01110
: data
file
7
:
'
/export/home/oracle/oradata/aaa/tools01.dbf
'
SQL
>
alter
database
datafile
'
/export/home/oracle/oradata/aaa/tools01.dbf
'
offline;
Database
altered.
SQL
>
alter
database
open
;
Database
altered.
--
------ftp the tools01.dbf from standby DB---------
SQL
>
recover datafile
'
/export/home/oracle/oradata/aaa/tools01.dbf
'
;
Media recovery complete.
SQL
>
alter
database
datafile
'
/export/home/oracle/oradata/aaa/tools01.dbf
'
online;
Database
altered.
Q5:control文件有变化的时候该如何做。
A5:下面模拟的是添加一个数据文件后的处理过程:
-----------------Main DB-------------------------
SQL
>
alter
system
set
log_archive_min_succeed_dest
=
1
scope
=
spfile;![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
System altered.![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SQL
>
alter
system switch logfile;![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
System altered.![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SQL>
create
tablespace compard datafile
'
/export/home/oracle/compard01.dbf
'
size 20M;![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
Tablespace created.![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SQL
>
create
user
compard
default
tablespace compard identified
by
cctv
temporary
tablespace
temp
;![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
User
created.![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SQL
>
grant
connect
to
compard
;![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
Grant
succeeded.
SQL
>
alter
system switch logfile;![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
System altered.![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SQL
>
archive
log
list
Database
log
mode Archive Mode
Automatic archival Enabled
Archive destination
/
export
/
home
/
oracle
/
dbs
/
arch
/
Oldest online
log
sequence
18
Next
log
sequence
to
archive
20
Current
log
sequence
20
SQL
>
alter
system switch logfile;
--
execute 5 times
--------------standby DB --------------------------
SQL
>
SELECT
SEQUENCE#,APPLIED
FROM
V$ARCHIVED_LOG;
SEQUENCE# APPLIE
--
-------- ------
18
YES
19
NO
20
NO
21
NO
22
NO
23
NO
24
NO
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
error
in
alter
file
:
Errors
in
file
/
export
/
home
/
oracle
/
admin
/
aaa
/
bdump
/
asrs_mrp0_9281.trc:
ORA
-
01670
: new datafile
9
needed
for
standby
database
recovery
ORA
-
01157
: cannot identify
/
lock data
file
9
-
see DBWR trace
file
ORA
-
01110
: data
file
9
:
'
/export/home/oracle/compard01.dbf
'
MRP0: Background Media Recovery process
is
now terminated
Thu
Dec
20
09
:
01
:
44
2007
kccrsz: expanded controlfile section
11
from
13
to
27
records
requested
to
grow
by
13
record(s); added
1
block(s)
of
records
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
SQL
>
alter
database
create
datafile
'
/export/home/oracle/compard01.dbf
'
;
Database
altered.
--
-----------wait for several minutes------------------
SQL
>
SELECT
SEQUENCE#,APPLIED
FROM
V$ARCHIVED_LOG;
SEQUENCE# APPLIE
--
-------- ------
18
YES
19
YES
20
YES
21
YES
22
YES
23
YES
24
YES
另外9i中提供了一个新的参数standby_file_management,在standby上面把它设置为auto就可以自动建立数据文件,我在9201中测试了一下,如果这个参数在新增数据文件之后才加进去,是不可以的,只能用上面这个办法处理或者重新建立standby control文件。在测试的过程中,这个参数如果auto是在增加了数据文件之后设置的,接下来,就碰到了了ORA-01203,ORA-01111,ORA-01157等错误,都是手工干预才搞定。正确的方法是在控制文件变化之前先设置这个参数,就没有问题了。
Q6:standby 的日常检查
A6:在Main DB上面执行:
SQL
>
archive
log
list;
Database
log
mode Archive Mode
Automatic archival Enabled
Archive destination
/
export
/
home
/
oracle
/
dbs
/
arch
/
Oldest online
log
sequence
11
Next
log
sequence
to
archive
12
Current
log
sequence
12
在Standby上面执行
SQL
>
SELECT
SEQUENCE#,APPLIED
FROM
V$ARCHIVED_LOG;
SEQUENCE# APPLIE
--
-------- ------
8
YES
9
YES
10
YES
11
YES
发现standby 上面11已经apply完毕就说明正常,如果发现还没apply到11,就说明2个数据库之间存在差异,看情况,有些需要手工处理。
Q7:启动关闭standby struct顺序
A7:一般的是先开启从的,再主的,不过感觉到过来也没有什么问题。
Q8:non data lose
A8:其实就是在standby作redo log的镜像,如果standby 的redo没有写成功,那Main DB就会hung住,对效能影响比较大,我们这些生产的数据库对效能要求较高,所以没有实际测试,不作评论,大家有兴趣的可以看看网上的文章。
Q9:日志不连续的情况下该怎么办
A9:copy Main DB's archive log files to standby DB and recover manually.SQL:
recover automatic standby
database
;
or
recover standby
database
until cancel;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10325341/viewspace-573095/,如需转载,请注明出处,否则将追究法律责任。