VERSION: 1.1.0
LAST UPDATE: 2005-03-28
MSN: foreverlee0619@msn.com
Primary database (Oracle 10g)
IP:172.16.75.35
INSTANCE_NAME=ora1
Standby database (Oracle 10g)
IP:172.16.75.30
INSTANCE_NAME=ora2
1 前期操作系统级别准备:
设置操作系统用户profile文件,
注意ORACLE_SID,ORACLE_BASE,ORACLE_HOME,PATH,NLS_LANG变量的设置
主数据库服务器端
IP:172.16.75.35
[oracle@db1 ora10g]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
export ORACLE_BASE=/ora10g/app
export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
export ORACLE_SID=ora1
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export PATH=$ORACLE_HOME/bin:$PATH:/usr/local/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export LC_CTYPE=en_US.UTF-8
[oracle@db1 ora10g]$
从数据库端
IP:172.16.75.30
[oracle@db_gurid ora10g]$ cat .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
unset USERNAME
export ORACLE_BASE=/ora10g/app
export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
export ORACLE_SID=ora2
export PATH=$ORACLE_HOME/bin:$PATH:/usr/local/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export LC_CTYPE=en_US.UTF-8
[oracle@db_gurid ora10g]$
2 前期主数据库准备工作 IP:172.16.75.35
主数据库初始化参数文件
检验初始化参数文件中指定目录的正确性.
[oracle@db1 dbs]$ pwd
/ora10g/app/product/10.1.0/db_1/dbs
如果文件initora1.ora不存在则vi新建文件initora1.ora
[oracle@db1 dbs]$ cat initora1.ora
*.background_dump_dest='/ora10g/app/admin/starmap/bdump'
*.compatible='10.1.0.2.0'
*.control_file_record_keep_time=15
*.control_files='/oradata/starmap/control01.ctl','/redo/starmap/controlfile/control02.ctl','/redo/starmap/controlfile/control03.ctl'
*.core_dump_dest='/ora10g/app/admin/starmap/cdump'
*.cursor_sharing='similar'
*.db_block_size=8192
*.db_cache_size=600m
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='starmap'
*.db_recovery_file_dest='/ora10g/app/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.java_pool_size=10m
*.job_queue_processes=21
*.large_pool_size=50m
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=250
*.query_rewrite_enabled='true'
*.query_rewrite_integrity='trusted'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=200m
*.timed_statistics=true
*.undo_management='AUTO'
*.undo_retention=1800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/ora10g/app/admin/starmap/udump'
*.utl_file_dir='/admin/logminer/'
instance_name=ora1
DB_UNIQUE_NAME=ora1
sga_max_size=900m
######standby#database
standby_archive_dest='/arch/'
log_archive_format='%t_%s_%r.arc'
SERVICE_NAMES=ora1
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ora1,ora2)'
LOG_ARCHIVE_DEST_1='LOCATION=/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ora1'
LOG_ARCHIVE_DEST_2=
'SERVICE=ora2
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ora2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAL_SERVER=ora2
FAL_CLIENT=ora1
DB_FILE_NAME_CONVERT='/oraguard/oradata1/','/oradata/starmap/','/oraguard/oradata2/','/orabak/oradata/'
LOG_FILE_NAME_CONVERT='/oraguard/redo1/','/redo/starmap/redo1/','/oraguard/redo1/','/redo/starmap/redo2/'
STANDBY_FILE_MANAGEMENT=AUTO
LOG_ARCHIVE_MAX_PROCESSES=2
主数据网络监听服务文件listener.ora
[oracle@db1 admin]$ pwd
/ora10g/app/product/10.1.0/db_1/network/admin
[oracle@db1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10.1.0.3/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora1)
(ORACLE_HOME = /ora10g/app/product/10.1.0/db_1)
(SID_NAME = ora1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.75.35)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
)
主数据库网络客户端文件tnsnames.ora
其中ora2为连接从数据库的Oracle Net Service String
[oracle@db1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/10.1.0/Db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ora2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.75.30)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora2)
)
)
ora1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.75.35)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora1)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
主数据库网络客户端文件sqlnet.ora
[oracle@db1 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/product/10.1.0/Db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
主数据库SYS口令文件
[oracle@db1 admin]$rm $ORACLE_HOME/dbs/orapw$ORACLE_SID
[oracle@db1 admin]$$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=ora1 entries=5
entries:表示sysdba这个角色能够被授予的个数
3 前期从数据库准备工作 IP:172.16.75.30
从数据库初始化参数文件
检验初始化参数文件中指定目录的正确性.需要创建相应dump目录并分配相应读写权限
[oracle@db_gurid dbs]$ pwd
/ora10g/app/product/10.1.0/db_1/dbs
[oracle@db_gurid dbs]$ cat initora2.ora
*.background_dump_dest='/ora10g/admin/smjs/bdump'
*.compatible='10.1.0.2.0'
*.control_file_record_keep_time=15
*.control_files='/oraguard/control1/control01.ctl','/oraguard/control2/control02.ctl'
*.core_dump_dest='/ora10g/admin/smjs/cdump'
*.cursor_sharing='similar'
*.db_block_size=8192
*.db_cache_size=500m
*.db_domain=''
*.db_file_multiblock_read_count=16
db_name='starmap'
*.db_recovery_file_dest='/oraguard/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.java_pool_size=10m
*.job_queue_processes=21
*.large_pool_size=50m
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=250
*.query_rewrite_enabled='true'
*.query_rewrite_integrity='trusted'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=200m
*.timed_statistics=true
*.undo_management='AUTO'
*.undo_retention=1800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/ora10g/admin/smjs/udump'
instance_name=ora2
DB_UNIQUE_NAME=ora2
sga_max_size=800m
######standby#database
standby_archive_dest='/arch/'
log_archive_format='%t_%s_%r.arc'
SERVICE_NAMES=ora2
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ora1,ora2)'
LOG_ARCHIVE_DEST_1='LOCATION=/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ora2'
LOG_ARCHIVE_DEST_2=
'SERVICE=ora1
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ora1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAL_SERVER=ora1
FAL_CLIENT=ora2
DB_FILE_NAME_CONVERT='/oradata/starmap/','/oraguard/oradata1/','/orabak/oradata/','/oraguard/oradata2/'
LOG_FILE_NAME_CONVERT='/redo/starmap/redo1/','/oraguard/redo1/','/redo/starmap/redo2/','/oraguard/redo2/'
STANDBY_FILE_MANAGEMENT=AUTO
LOG_ARCHIVE_MAX_PROCESSES=2
从数据库网络文件相关
[oracle@db_gurid admin]$ pwd
/ora10g/app/product/10.1.0/db_1/network/admin
[oracle@db_gurid admin]$ cat listener.ora
# listener.ora Network Configuration File: /opt/oracle/product/10.1.0.3/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ora2)
(ORACLE_HOME = /ora10g/app/product/10.1.0/db_1)
(SID_NAME = ora2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.75.30)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
)
)
[oracle@db_gurid admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/10.1.0/Db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ora2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.75.30)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora2)
)
)
ora1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.75.35)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora1)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@db_gurid admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /oracle/product/10.1.0/Db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
从数据库SYS口令文件
[oracle@db1 admin]$rm $ORACLE_HOME/dbs/orapw$ORACLE_SID
[oracle@db1 admin]$$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=ora2 entries=5
entries:表示sysdba这个角色能够被授予的个数
4 Oracle10g Dataguard配置过程
1> 将primary database启用Force logging模式
[oracle@db1 arch]$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.1.0.3.0 - Production on Mon Mar 28 13:14:42 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> ALTER DATABASE FORCE LOGGING;
会花费比较长的时间,等待没有日志化的磁盘读写.
2> 重启主数据库建创spfile
shutdown immediate;
create spfile from pfile;
将主数据库启动至mount状态
startup mount;
3> mount状态下备份主数据库
[oracle@db1 ora10g]$ rman target /
Recovery Manager: Release 10.1.0.3.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: STARMAP (DBID=1880758955)
RMAN> show all;
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/arch/%U.bk';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ora10g/app/product/10.1.0/db_1/dbs/snapcf_ora1.f'; # default
//设置自动备份控制文件
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
//设置备份集路径，这里很重要,非常重要.
//备份信息保存在控制文件中.控制文件记录备份集的位置.确保从数据库/arch/目录存在并有足够空间保//存备份文件
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/arch/%U.bk';
old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/arch/%U.bk';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/arch/%U.bk';
new RMAN configuration parameters are successfully stored
//开始备份主数据库
RMAN> backup database;
Starting backup at 28-MAR-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=254 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=/oradata/starmap/business.dbf
input datafile fno=00005 name=/orabak/oradata/indx.dbf
input datafile fno=00004 name=/orabak/oradata/users01.dbf
input datafile fno=00001 name=/oradata/starmap/system01.dbf
input datafile fno=00003 name=/oradata/starmap/sysaux01.dbf
input datafile fno=00006 name=/oradata/starmap/billing.dbf
input datafile fno=00002 name=/oradata/starmap/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 28-MAR-05
channel ORA_DISK_1: finished piece 1 at 28-MAR-05
piece handle=/arch/0gggerh9_1_1.bk comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 28-MAR-05
Starting Control File and SPFILE Autobackup at 28-MAR-05
piece handle=/ora10g/app/flash_recovery_area/ORA1/autobackup/2005_03_28/o1_mf_s_554135166_14h8vzlq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-MAR-05
//切换主数据库日志
RMAN> sql 'Alter System Archive Log Current';
sql statement: Alter System Archive Log Current
RMAN> exit
Recovery Manager complete.
4> 将主数据库备份集scp到从数据库/arch/目录
登录到从数据库172.16.75.30
进入/arch/目录后scp主数据备份文件
[oracle@db1 arch]$ pwd
/arch
[oracle@db1 arch]$ scp 172.16.75.35:/arch/0gggerh9_1_1.bk .
5>open主数据库创建从数据库控制文件
alter database archivelog;
alter database open;
[oracle@db1 arch]$ sqlplus '/ as sysdba'
SQL*Plus: Release 10.1.0.3.0 - Production on Mon Mar 28 14:31:03 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter database create standby controlfile as '/arch/control01.ctl';
Database altered.
6> 将/arch/control01.ctl文件scp至从数据库
位置为:从数据库初始化文件中定义(*.control_files='/oraguard/control1/control01.ctl','/oraguard/control2/control02.ctl')
7> 重启从数据库重新创建从数据库spfile
shutdown immediat;
create spfile from pfile;
startup mount;
8> 将主数据库归档文件scp至从数据库归档目录
9> 通过rman恢复从数据库
[oracle@db1 arch]$ rman target /
Recovery Manager: Release 10.1.0.3.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: STARMAP (DBID=1880758955)
//将数据文件从备份集取出
RMAN> restore database;
//应用归档日志恢复从数据库
如果有恢复的日志并想手工恢复，可以运行如下命令
SQL>recover database;
如果过程中出现如下类似错误，则可以忽略
ORA-00279: change 50775 generated at 06/08/2004 21:57:21 needed for thread 1
ORA-00289: suggestion : /oracle/oradata/tbdb/archive/1_5.dbf
ORA-00280: change 50775 for thread 1 is in sequence #5
ORA-00278: log file '/oracle/oradata/tbdb/archive/1_5.dbf' no longer needed
for this recovery
ORA-00308: cannot open archived log '/oracle/oradata/tbdb/archive/1_5.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
因为最后需要的联机日志根本还没有从主数据库送过来
10> 启动standby database日志传送
SQL>alter database recover managed standby database disconnect from session;
至此standby database配置部分结束
总结:
standby database配置前期准备工作比较繁琐,但深入理解Oracle的体系结构后还是能够理出个头绪.
首先编辑系统级profile文件,毕竟Oracle运行在Linux系统。
初始化参数文件,控制文件,主数据库全备份文件,Oracle网络文件之间的关系.
初始化参数文件记录控制文件位置,控制文件中记录备份文件信息.通过tnsnames.ora文件中定义的Oracle Net Services String使得主数据库与从数据库发生联系等等.
补充:
Dataguard database配置完成后,必须马上为从数据库添加临时文件
在从数据库 IP:172.16.75.30
具体步骤:
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered.
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oraguard/oradata2/users01.dbf
/oraguard/oradata1/sysaux01.dbf
/oraguard/oradata1/undotbs01.dbf
/oraguard/oradata1/system01.dbf
/oraguard/oradata2/indx.dbf
/oraguard/oradata1/billing.dbf
/oraguard/oradata1/business.dbf
7 rows selected.
SQL> desc dba_temp_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS CHAR(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
SQL> select file_name from dba_temp_files;
no rows selected
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
TEMP02
INDX
BILLING
BUSINESS
9 rows selected.
SQL> alter tablespace temp add tempfile '/oraguard/oradata2/temp01.dbf' size 300m;
Tablespace altered.
SQL> alter tablespace temp02 add tempfile '/oraguard/oradata2/temp02.dbf' size 300m;
Tablespace altered.
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
/oraguard/oradata2/temp01.dbf
/oraguard/oradata2/temp02.dbf
SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/76065/viewspace-823745/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/76065/viewspace-823745/
扫一扫
75万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
