规划:
两台redhat5.4机器:
PRIMARY库:
IP地址:10.0.18.251
数据库SID:zsddb
DB_UNIQUE_NAME:zsddb8
数据库软件安装路径: /data/ora11g/product/11.2.0/db_1
数据文件路径: /data/ora11g/oradata/zsddb/
本地归档路径: /data/ora11g/oradata/zsddb/archivelog
STANDBY库:
IP地址:10.0.18.250
数据库SID:zsddb
DB_UNIQUE_NAME:zsddb9
数据库软件安装路径: /data/ora11g/product/11.2.0/db_1
数据文件路径: /data/ora11g/oradata/zsddb/
本地归档路径: /data/ora11g/oradata/zsddb/archivelog
一:Primary数据库配置和操作:
步骤:
1. 确认主库处于归档模式
-
Archive log list;
-
(如果不是归档模式,详见:Oracle学习系列之如何配置归档模式的数据库)
2. 将Primary数据库置为Force Logging模式
-
Alter database force logging
3. 配置Primary数据库的初始化参数
思路:想要修改spfile,首先获取pfile,然后用文本工具打开编辑,改成你所需要的初始化参数。然后再重新加载至数据库中
*从当前的SPFILE中创建PFILE:
-
create pfile ='/home/oracle/pfileZSDDB8.ora' from spfile;
-
*.db_unique_name='zsddb8'
-
*.fal_client='zsddb_1.8'
-
*.fal_server='zsddb_1.9'
-
*.log_archive_config='DG_CONFIG=(zsddb6,zsddb7,zsddb8,zsddb9)'
-
*.log_archive_dest_1='location=/data/ora11g/oradata/zsddb/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=zsddb8'
-
*.log_archive_dest_2='service=zsddb_1.9 sync valid_for=(online_logfiles,primary_role) db_unique_name=zsddb9'
-
*.log_archive_dest_3='service=zsddb_31.7 async valid_for=(online_logfiles,primary_role) db_unique_name=zsddb7'
-
*.log_archive_dest_4='service=zsddb_31.6 async valid_for=(online_logfiles,primary_role) db_unique_name=zsddb6'
-
*.log_archive_dest_state_2='defer'
-
*.log_archive_dest_state_3='defer'
-
*.log_archive_dest_state_4='defer'
-
*.standby_file_management='auto'
-
Shutdown immediate;
-
create spfile from pfile='/home/oracle/pfileZSDDB8.ora' ;
-
startup;
4. 配置主库的监听和网络服务名:
-
cd $ORACLE_HOME/network/admin
-
vi listener.ora
-
-
SID_LIST_LISTENER =
-
(SID_LIST =
-
(SID_DESC =
-
(GLOBAL_DBNAME = zsddb)
-
(ORACLE_HOME = /data/ora11g/product/11.2.0/db_1)
-
(SID_NAME = zsddb)
-
)
-
)
-
-
LISTENER =
-
(DESCRIPTION_LIST =
-
(DESCRIPTION =
-
(ADDRESS = (PROTOCOL = TCP)(HOST = zsddb8)(PORT = 1521))
-
)
-
)
*使得监听器生效
-
lsnrctl stop
-
lsnrctl status
-
lsnrctl start
-
vi $ORACLE_HOME/network/admin/tnsname.ora
-
-
zsddb_1.8 =
-
(DESCRIPTION =
-
(ADDRESS_LIST =
-
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.18.251)(PORT = 1521))
-
)
-
(CONNECT_DATA =
-
(SERVICE_NAME = zsddb)
-
(SERVER = DEDICATED)
-
)
-
)
-
-
zsddb_1.9 =
-
(DESCRIPTION =
-
(ADDRESS_LIST =
-
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.18.250)(PORT = 1521))
-
)
-
(CONNECT_DATA =
-
(SERVICE_NAME = zsddb)
-
(SERVER = DEDICATED)
-
)
-
)
5. 创建密钥文件(由于我是dbca创建库的,所以就是有了)
具体位置: /data/ora11g/product/11.2.0/db_1/dbs/orapwzsddb
创建命令:
-
orapwd file=/data/ora11g/product/11.2.0/db_1/dbs/orapwzsddb password=zsdzsd entries=30
6. 创建Standby Redologs
配置细节:确保Standby Redologs的文件大小与Primary数据库的Online Redologs文件
大小一致。
创建命令:
-
SQL>alter database add standby logfile group4 ('/data/ora11g/oradata/dgfile/std01.log') size 200M;
-
SQL>alter database add standby logfile group5 ('/data/ora11g/oradata/dgfile/std02.log') size200M;
-
SQL>alter database add standby logfile group6 ('/data/ora11g/oradata/dgfile/std03.log') size200M;
删除命令:
-
SQL>alter database drop stand by logfile group4;
二:STANDBY数据库配置和操作:
Normal
0
7.8 磅
0
2
false
false
false
EN-US
ZH-CN
X-NONE
思路:使用RMAN方式创建STANDBY,需要的准备工作:1,要指定实例2.创建standby的初始化文件。3.启动到nomount状态,4.创建密钥文件(可以直接copy过来).最后使用rman恢复Standby
1. 创建standby实例
2. 再配置对应的监听和tnsname.ora文件
-
vi $ORACLE_HOME/network/admin/listener.ora
-
-
SID_LIST_LISTENER =
-
(SID_LIST =
-
(SID_DESC =
-
(GLOBAL_DBNAME = zsddb)
-
(ORACLE_HOME = /data/ora11g/product/11.2.0/db_1)
-
(SID_NAME = zsddb)
-
)
-
)
-
-
LISTENER =
-
(DESCRIPTION_LIST =
-
(DESCRIPTION =
-
(ADDRESS = (PROTOCOL = TCP)(HOST = zsddb9)(PORT = 1521))
-
)
-
)
-
-
配置Net Server Name。
-
vi $ORACLE_HOME/network/admin/tnsnames.ora
-
-
zsddb_1.8 =
-
(DESCRIPTION =
-
(ADDRESS_LIST =
-
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.18.251)(PORT = 1521))
-
)
-
(CONNECT_DATA =
-
(SERVICE_NAME = zsddb)
-
(SERVER = DEDICATED)
-
)
-
)
-
-
zsddb_1.9 =
-
(DESCRIPTION =
-
(ADDRESS_LIST =
-
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.18.250)(PORT = 1521))
-
)
-
(CONNECT_DATA =
-
(SERVICE_NAME = zsddb)
-
(SERVER = DEDICATED)
-
)
-
)
*测试主库和备库直接监听器是否正常,可以用如下命令:
-
tnsping zsddb_1.8;(tnsping zsddb_1.9)
3. 创建Standby的pfile
Pfile参数文件具体内容
-
zsddb.__db_cache_size=369098752
-
zsddb.__java_pool_size=4194304
-
zsddb.__large_pool_size=4194304
-
zsddb.__oracle_base='/data/ora11g'#ORACLE_BASE set from environment
-
zsddb.__pga_aggregate_target=180355072
-
zsddb.__sga_target=545259520
-
zsddb.__shared_io_pool_size=0
-
zsddb.__shared_pool_size=159383552
-
zsddb.__streams_pool_size=0
-
*.audit_file_dest='/data/ora11g/admin/zsddb/adump'
-
*.audit_trail='db'
-
*.compatible='11.2.0.0.0'
-
*.control_files='/data/ora11g/oradata/dgfile/control01.ctl','/data/ora11g/oradata/dgfile/control02.ctl'
-
*.db_block_size=8192
-
*.db_domain=''
-
*.db_name='zsddb'
-
*.db_unique_name='zsddb9'
-
*.fal_client='zsddb_1.8'
-
*.fal_server='zsddb_1.9'
-
*.log_archive_config='DG_CONFIG=(zsddb6,zsddb7,zsddb8,zsddb9)'
-
*.log_archive_dest_1='location=/data/ora11g/oradata/zsddb/archivelog/ valid_for=(all_logfiles,all_roles) db_unique_name=zsddb9'
-
*.log_archive_dest_2='service=zsddb_1.8 sync valid_for=(online_logfiles,primary_role) db_unique_name=zsddb8'
-
*.log_archive_dest_3='service=zsddb_31.7 async valid_for=(online_logfiles,primary_role) db_unique_name=zsddb7'
-
*.log_archive_dest_4='service=zsddb_31.6 async valid_for=(online_logfiles,primary_role) db_unique_name=zsddb6'
-
*.log_archive_dest_state_2='enable'
-
*.standby_file_management='auto'
-
*.diagnostic_dest='/data/ora11g'
-
*.dispatchers='(PROTOCOL=TCP) (SERVICE=zsddbXDB)'
-
*.log_archive_dest_1='LOCATION=/data/ora11g/oradata/zsddb/archivelog'
-
*.log_archive_format='%t_%s_%r.dbf'
-
*.open_cursors=300
-
*.pga_aggregate_target=180355072
-
*.processes=300
-
*.remote_login_passwordfile='EXCLUSIVE'
-
*.sessions=335
-
*.sga_target=543162368
-
*.undo_tablespace='UNDOTBS1'
*
使得初始参数文件生效:
-
create spfile from pfile='/home/oracle/pfile_userdb9.ora' ;
*
之前如果没有相应的归档目录或者audit目录,都要重新的创建
-
cd /data/ora11g/admin/zsddb/
-
mkdir adump bdump cdump udump
-
mkdir -p /data/ora11g/oradata/zsddb/archivelog/
4. 启动standby到nomount状态
5. 创建Standby的密码
把PRIMARY库的密码copy到Standby数据库中。
-
scp /data/ora11g/product/11.2.0/db_1/dbs/orapwzsddb root@10.0.18.250:/data/ora11g/product/11.2.0/db_1/dbs
这里可能有权限问题,使用chown修改权限
6. 开始使用rman创建standby
*先对Primary数据库进行归档。
-
alter system switch logfile
*进入rman
*备份主库:(primary操作)
-
run
-
{
-
allocate channel c1 device type disk format '/data/backup/rman/%U';
-
backup database plus archivelog;
-
}
*生成备库控制文件(primary操作)
-
SQL> alter database create standby controlfile as '/data/backup/rman/control01.ctl';
*将主库的备份copy到备库中。
-
scp /data/backup/rman/* root@10.0.18.250:/data/backup/rman/
-
(细节,拷贝过来的备份是不可用的,由于权限的原因,所以要使用chown修改权限)
-
chown oracle:oinstall /data/backup/rman/*
-
使用rman恢复备库的控制文件.
使用rman恢复备库的控制文件.
-
rman target /
-
-
Rman> set DBID= 1538857847(DBID的获得方法,自己寻找,可以在主库使用rman target /即可看到)
-
-
Rman> RESTORE CONTROLFILE FROM '/data/backup/rman/control01.ctl';
7.将备库至于mount状态
-
SQL> alter database mount standby database;
*在这里可以查看一下standby online log 。
-
select GROUP#,TYPE,MEMBER FROM V$LOGFILE;
-
-
alter database drop standby logfile group 4;
-
alter database drop standby logfile group 5;
-
alter database drop standby logfile group 6;
-
-
alter database add standby logfile group 4 ('/data/ora11g/oradata/dgfile/std01.log') size 256M;
-
alter database add standby logfile group 5 ('/data/ora11g/oradata/dgfile/std02.log') size 256M;
-
alter database add standby logfile group 6 ('/data/ora11g/oradata/dgfile/std03.log') size 256M;
8.恢复备库:
-
rman target /
-
restore database
9.修复成功后,开始开启archive_dest_state_2进程(primary操作)
-
Alter system set log_archive_dest_state_2=enable;10.
*复制密码文件
-
scp -P 50718 /data/ora11g/product/11.2.0/db_1/dbs/orapwuserdb oracle@192.168.31.7:/data/ora11g/product/11.2.0/db_1/dbs/
10.将备库置于自动恢复状态
-
SQL〉recover managed standby database disconnect from session;(这里就开始自动修复了)
-
-
*恢复数据完毕后,你可以关闭Redo应用
-
SQL>alter database recover managed standby database cancel;
-
-
*然后以只读的方式打开数据库
-
SQL>alter database open read only
-
-
*最后一条命令很关键(这可是新功能哦,可以一边在打开的模式下,一边接受redo日志。但是你必须保证有standby redolog这个文件。要不然可是打不开的)
-
alter database recover managed standby database using current logfile disconnect from session;
到这里大功告成。你可以选择喝杯咖啡,验证后续的正常情况。
-
查询当前库的角色和保护模式:
-
SQL> select name,database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status,supplemental_log_data_pk,supplemental_log_data_ui from v$database;
-
查看进程的活动状态:
-
SQL> select process,client_process,sequence#,status from v$managed_standby;
-
检查REDO应用进度:
-
select dest_name,archived_thread#,archived_seq#,applied_thread#,applied_seq#,db_unique_name from v$archive_dest_status where status='VALID';
-
查看同步情况
-
-
select MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
-
-
select sequence#,applied from v$archived_log;
-
-
查看online&standby日志
-
-
select GROUP#,TYPE,MEMBER FROM V$LOGFILE;
阅读(4706) | 评论(1) | 转发(0) |