ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 第一次用rman配置dataguard手记

第一次用rman配置dataguard手记

原创 Oracle 作者：lifewise 时间：2007-09-07 03:52:47 0 删除 编辑
第一次用rman配置dataguard手记[@more@]

[oracle@oracle test]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 4 11:16:44 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: ITEM (DBID=1905378702)

RMAN> run {
2> allocate channel t1 type disk format '/home/oracle/test/%u.dmp';
3> backup database plus archivelog;
4> release channel t1;}

using target database control file instead of recovery catalog
allocated channel: t1
channel t1: sid=153 devtype=DISK


Starting backup at 04-SEP-07
current log archived
channel t1: starting archive log backupset
channel t1: specifying archive log(s) in backup set
input archive log thread=1 sequence=636 recid=5 stamp=632309338
input archive log thread=1 sequence=637 recid=3 stamp=632309281
input archive log thread=1 sequence=638 recid=4 stamp=632309328
input archive log thread=1 sequence=639 recid=6 stamp=632310024
input archive log thread=1 sequence=640 recid=7 stamp=632327889
input archive log thread=1 sequence=641 recid=8 stamp=632328437
input archive log thread=1 sequence=642 recid=9 stamp=632328455
input archive log thread=1 sequence=643 recid=10 stamp=632328677
input archive log thread=1 sequence=644 recid=11 stamp=632333032
input archive log thread=1 sequence=645 recid=12 stamp=632333848
input archive log thread=1 sequence=646 recid=13 stamp=632334668
input archive log thread=1 sequence=647 recid=14 stamp=632335044
input archive log thread=1 sequence=648 recid=15 stamp=632337504
input archive log thread=1 sequence=649 recid=16 stamp=632380150
input archive log thread=1 sequence=650 recid=17 stamp=632401757
input archive log thread=1 sequence=651 recid=18 stamp=632402172
input archive log thread=1 sequence=652 recid=19 stamp=632402239
channel t1: starting piece 1 at 04-SEP-07
channel t1: finished piece 1 at 04-SEP-07
piece handle=/home/oracle/test/07ir3c9v.dmp tag=TAG20070904T111719 comment=NONE
channel t1: backup set complete, elapsed time: 00:02:50
Finished backup at 04-SEP-07

Starting backup at 04-SEP-07
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
input datafile fno=00005 name=/home/oracle/oradata/item/ts_item01.dbf
input datafile fno=00006 name=/home/oracle/oradata/item/ttkf.dbf
input datafile fno=00001 name=/home/oracle/oradata/item/system01.dbf
input datafile fno=00008 name=/home/oracle/oradata/item/system02.dbf
input datafile fno=00007 name=/home/oracle/oradata/item/sysaux02.dbf
input datafile fno=00003 name=/home/oracle/oradata/item/sysaux01.dbf
input datafile fno=00009 name=/home/oracle/oradata/item/undotbs01.dbf
input datafile fno=00002 name=/home/oracle/oradata/item/B2C_TABLESPACE01.dbf
input datafile fno=00010 name=/home/oracle/oradata/item/wiki.dbf
input datafile fno=00011 name=/home/oracle/oradata/item/donate.dbf
input datafile fno=00004 name=/home/oracle/oradata/item/users01.dbf
channel t1: starting piece 1 at 04-SEP-07
channel t1: finished piece 1 at 04-SEP-07
piece handle=/home/oracle/test/08ir3cfb.dmp tag=TAG20070904T112010 comment=NONE
channel t1: backup set complete, elapsed time: 00:32:44
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel t1: starting piece 1 at 04-SEP-07
channel t1: finished piece 1 at 04-SEP-07
piece handle=/home/oracle/test/09ir3ecp.dmp tag=TAG20070904T112010 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:12
Finished backup at 04-SEP-07

Starting backup at 04-SEP-07
current log archived
channel t1: starting archive log backupset
channel t1: specifying archive log(s) in backup set
input archive log thread=1 sequence=653 recid=20 stamp=632404391
channel t1: starting piece 1 at 04-SEP-07
channel t1: finished piece 1 at 04-SEP-07
piece handle=/home/oracle/test/0air3ed9.dmp tag=TAG20070904T115313 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:02
Finished backup at 04-SEP-07

released channel: t1

RMAN> exit


Recovery Manager complete.
[oracle@oracle test]$ pwd
/home/oracle/test
[oracle@oracle test]$ scp *.dmp oracle@172.16.64.83:/home/oracle/test
oracle@172.16.64.83's password:
07ir3c9v.dmp 100% 237MB 4.2MB/s 00:56
08ir3cfb.dmp 100% 1249MB 4.2MB/s 04:56
09ir3ecp.dmp 100% 6976KB 3.8MB/s 00:01
0air3ed9.dmp 100% 567KB 4.8MB/s 00:00
[oracle@oracle test]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 4 12:00:53 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database backup standby controlfile as '/home/oracle/test/control02.ctl';
alter database backup standby controlfile as '/home/oracle/test/control02.ctl'
*
ERROR at line 1:
ORA-00905: missing keyword


SQL> alter database create standby controlfile as '/home/oracle/test/control02.ctl';

Database altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@oracle test]$ scp control02.ctl oracle@172.16.64.83:/home/oracle/test
oracle@172.16.64.83's password:
control02.ctl 100% 6896KB 11.2MB/s 00:00
[oracle@oracle test]$


-bash-3.00$ cd /opt/oracle
-bash-3.00$ ls
admin archlog flash_recovery_area oradata oraInventory product
-bash-3.00$ cd oradata
-bash-3.00$ ls
archlog esal esal.bak full_exp.log item rac sqlnet.log test.dmp
-bash-3.00$ cd item
-bash-3.00$ ls
control03.ctl.bak
-bash-3.00$ pwd
/opt/oracle/oradata/item
-bash-3.00$ cp /home/oracle/test/control02.ctl .
-bash-3.00$ ls
control02.ctl control03.ctl.bak
-bash-3.00$ cp control02.ctl control01.ctl
-bash-3.00$ cp control02.ctl control03.ctl
-bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 4 12:07:48 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database (not started)

RMAN> run {
2> allocate channel t1 type disk;
3> restore database;
4> release channel t1;}

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of allocate command at 09/04/2007 12:08:21
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory

RMAN> startup nomount

Oracle instance started

Total System Global Area 524288000 bytes

Fixed Size 1220384 bytes
Variable Size 146800864 bytes
Database Buffers 373293056 bytes
Redo Buffers 2973696 bytes

RMAN> run {
2> allocate channel t1 type disk;
3> restore database;
4> release channel t1;}

allocated channel: t1
channel t1: sid=157 devtype=DISK

Starting restore at 04-SEP-07
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/04/2007 12:15:32
ORA-01507: database not mounted

RMAN> sql "alter database mount"
2> ;

sql statement: alter database mount

RMAN> run {
2> allocate channel t1 type disk;
3> restore database;
4> release channel t1;}

allocated channel: t1
channel t1: sid=157 devtype=DISK

Starting restore at 04-SEP-07

channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oradata/item/system01.dbf
restoring datafile 00002 to /home/oracle/oradata/item/B2C_TABLESPACE01.dbf
restoring datafile 00003 to /home/oracle/oradata/item/sysaux01.dbf
restoring datafile 00004 to /home/oracle/oradata/item/users01.dbf
restoring datafile 00005 to /home/oracle/oradata/item/ts_item01.dbf
restoring datafile 00006 to /home/oracle/oradata/item/ttkf.dbf
restoring datafile 00007 to /home/oracle/oradata/item/sysaux02.dbf
restoring datafile 00008 to /home/oracle/oradata/item/system02.dbf
restoring datafile 00009 to /home/oracle/oradata/item/undotbs01.dbf
restoring datafile 00010 to /home/oracle/oradata/item/wiki.dbf
restoring datafile 00011 to /home/oracle/oradata/item/donate.dbf
channel t1: reading from backup piece /home/oracle/test/08ir3cfb.dmp
channel t1: restored backup piece 1
piece handle=/home/oracle/test/08ir3cfb.dmp tag=TAG20070904T112010
channel t1: restore complete, elapsed time: 00:04:13
Finished restore at 04-SEP-07

released channel: t1

RMAN>

开启备份库进入接受模式
-bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 4 14:51:07 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1220384 bytes
Variable Size 146800864 bytes
Database Buffers 373293056 bytes
Redo Buffers 2973696 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

等入主库进行测试

alter system switch logfile；

查看主库alert。log发现

[oracle@oracle bdump]$ tail alert_item.log
Errors in file /home/oracle/admin/item/bdump/item_arc0_19420.trc:
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
PING[ARC0]: Heartbeat failed to connect to standby 'standby'. Error is 12505.
Tue Sep 4 14:07:38 2007
Error 12541 received logging on to the standby
Check whether the listener is up and running.

检查tns配置正常

[oracle@oracle bdump]$ tnsping standby

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 04-SEP-2007 14:58:45

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.64.83)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = item)))
OK (0 msec)
[oracle@oracle bdump]$

有点奇怪，仔细查看错误，怀疑是不是listener没配置呢？
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(PROGRAM = extproc)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.64.83)(PORT = 1521))
)
)

采用的动态监听，手动建立监听，修改如下


# listener.ora Network Configuration File: /opt/oracle/product/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = item)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.64.83)(PORT = 1521))
)
)


重新测试，在主库
alter system switch logfile；

原来错误消失，出现新错误

[oracle@oracle bdump]$ tail /home/oracle/admin/item/bdump/item_arc0_19420.trc
*** 2007-09-04 14:37:40.079 60679 kcrr.c
Error 1031 received logging on to the standby
Error 1031 connecting to destination LOG_ARCHIVE_DEST_2 standby host 'standby'
Error 1031 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'standby'
ORA-01031: insufficient privileges
*** 2007-09-04 14:37:40.079 60679 kcrr.c
PING[ARC0]: Heartbeat failed to connect to standby 'standby'. Error is 1031.
*** 2007-09-04 14:37:40.079 58941 kcrr.c
kcrrfail: dest:2 err:1031 force:0 blast:1
kcrrwkx: nothing to do (end)
[oracle@oracle bdump]$ tail /home/oracle/admin/item/bdump/item_arc0_19420.trc
ORA-01031: insufficient privileges
*** 2007-09-04 14:42:40.279 60679 kcrr.c
PING[ARC0]: Heartbeat failed to connect to standby 'standby'. Error is 1031.
*** 2007-09-04 14:42:40.279 58941 kcrr.c
kcrrfail: dest:2 err:1031 force:0 blast:1

将主库密码文件copy过来，重新启动数据库，日志文件可以正常传送，成功

测试:
主库
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------
1 1 659 52428800 1 YES INACTIVE 117127589 04-SEP-07
2 1 658 52428800 1 YES INACTIVE 117126309 04-SEP-07
3 1 660 52428800 1 NO CURRENT 117128199 04-SEP-07

SQL> create table standby as select * from v$lock;

Table created.

SQL> alter system switch logfile;

System altered.

SQL>

重库:
-bash-3.00$ tail alert_item.log
RFS[1]: Archived Log: '/opt/oracle/archlog/1_659_606152283.dbf'
Tue Sep 4 15:10:09 2007
Media Recovery Log /opt/oracle/archlog/1_659_606152283.dbf
Media Recovery Waiting for thread 1 sequence 660
Tue Sep 4 15:46:02 2007
RFS[1]: No standby redo logfiles created
RFS[1]: Archived Log: '/opt/oracle/archlog/1_660_606152283.dbf'
Tue Sep 4 15:46:06 2007
Media Recovery Log /opt/oracle/archlog/1_660_606152283.dbf
Media Recovery Waiting for thread 1 sequence 661
-bash-3.00$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 4 15:48:12 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from standby;
select * from standby
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL> alter system recover managed standby database cancel;
alter system recover managed standby database cancel
*
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM


SQL> l
1* alter system recover managed standby database cancel
SQL> c/system/database
1* alter database recover managed standby database cancel
SQL> run
1* alter database recover managed standby database cancel

Database altered.

SQL> alter database open readonly;
alter database open readonly
*
ERROR at line 1:
ORA-02288: invalid OPEN mode


SQL> alter database open read only;

Database altered.

SQL> select * from standby;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
CTIME BLOCK
---------- ----------
3FBBA840 3FBBA854 165 XR 4 0 1 0
80790 0

3FBBA89C 3FBBA8B0 165 CF 0 0 2 0
80790 0

3FBBA954 3FBBA968 165 RS 25 1 2 0
80786 0


ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
CTIME BLOCK
---------- ----------
3FBBAA0C 3FBBAA20 166 RT 1 0 6 0
80786 0

3FBBAB7C 3FBBAB90 167 MR 1 0 4 0
80783 0
配置完成

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/620862/viewspace-968281/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： EXECUTE IMMEDIATE 用法
下一篇： oracle 10g 32bit 升级到64bit
请登录后发表评论 登录
全部评论
lifewise

注册时间：2008-01-07

  • 博文量
    52
  • 访问量
    496469

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593