首页 > 代码库 > 搭建物理备库
搭建物理备库
Primary db configure：
1. 开启主库强制日志
查询当前是否强制日志：
SQL> select force_logging from v$database;
FOR
---
NO
开启强制日志
SQL> alter database force logging;
Database altered.
查询
SQL> select force_logging from v$database;
FOR
---
YES
2. 开启归档模式
SQL> alter system set log_archive_dest_1=’/oradata/arch’;
System altered.
SQL> alter system set log_archive_format=’arch_%t_%s_%r’ scope=spfile;
System altered.
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/arch
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15
3. 创建pfile，设置DG参数
SQL> create pfile=‘/tmp/pfile‘ from spfile;
File created.
$ vi /tmp/pfile
设置以下参数
*.db_name=‘orcl‘
*.remote_login_passwordfile=‘EXCLUSIVE‘
*.db_unique_name=primary
*.log_archive_config=‘DG_CONFIG=(primary,standby)‘
*.log_archive_dest_1=‘LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary‘
*.log_archive_dest_2=‘SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby‘
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_format=‘arch_%t_%s_%r‘
*.log_archive_max_processes=30
*.fal_server=standby
*.fal_client=primary
*.standby_file_management=auto
--db file and log file 在主备库的存储位置不一样，需要加以下参数
*.db_file_name_convert=‘primary‘,‘standby‘
*.log_file_name_convert=‘/oradata/orcl‘,‘/oradata/orcl‘
SQL> create spfile from pfile=‘/tmp/pfile‘;
File created.
4. 创建standby logfile，最好在主备库都创建
1) 确保主和备数据库上的日志文件大小是相同的
2) 确定备重做日志文件组的适当数目
Standby logfile数 =（每个线程的日志文件的最大数目＋1）×线程最大数目
3) 检查create database时指定的MAXLOGFILES和MAXLOGMEMBERS参数，可以通过trace controlfile出来查看
4) 创建standby logfile,rac环境指定线程创建，单机环境group#不重复即可
查看主库的group#，thread#，size
SQL> col member for a25
SQL> select a.group#,a.thread#,b.member,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group#;
GROUP# THREAD# MEMBER MB
---------- ---------- ------------------------- ----------
1 1 /oradata/orcl/redo01.log 50
2 1 /oradata/orcl/redo02.log 50
3 1 /oradata/orcl/redo03.log 50
主库添加standby logfile
SQL> alter database add logfile group 4 ‘/oradata/orcl/standby04.log‘ size 50M,group 5 ‘/oradata/orcl/standby05.log‘ size 50M,
group 6 ‘/oradata/orcl/standby06.log‘ size 50M,group 7 ‘/oradata/orcl/standby07.log‘ size 50M;
5. 主库创建监听文件，密码文件，网络服务名
1）监听文件（listener.ora）
$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.100)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orcl)
(SID_NAME=orcl)
(ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1)
(PRESPAWN_MAX=20)
(PRESPAWN_LIST=
(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
)
)
)
2) 网络服务名（tnsnames.ora）
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))
)
(CONNECT_DATA =http://www.mamicode.com/
(SERVICE_NAME = orcl)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
)
(CONNECT_DATA =http://www.mamicode.com/
(SERVICE_NAME = orcl)
)
3) 密码文件
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapworcl password=oracle
4) 如果是在windows平台，则需要通过oradim工具创建一个基于windows的服务
oradim -NEW -SID orcl -INTPWD oracle -STARTMODE manual
启动监听
$ lsnrctl start
6. rman 备份主库
我是在修改完DG参数后做了次全备，此时delete input参数不能删除archivelog，所以加了force强制删除archivelog,在修改DG参数前备份则不需要加force 就可以删除archivelog
$ rman target / log=/oradata/bak/rman.log
RMAN> run
{allocate channel c1 type disk;
allocate channel c2 type disk;
backup filesperset 2 database format ‘/oradata/bak/full_%d_%T_%s_%p‘;
sql ‘alter system archive log current‘;
backup archivelog all format ‘/oradata/bak/arch_%d_%T_%s_p‘ delete input force;
backup current controlfile for standby format ‘/oradata/bak/c_%d_%T_s_%p‘;
}
standby db configure：
1. 创建备库参数文件
拷贝主库pfile过来，修改以下参数设置
$ scp primary:/tmp/pfile /tmp
$ vi /tmp/pfile
*.db_name=‘orcl‘
*.remote_login_passwordfile=‘EXCLUSIVE‘
*.db_unique_name=standby
*.log_archive_config=‘DG_CONFIG=(primary,standby)‘
*.log_archive_dest_1=‘LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby‘
*.log_archive_dest_2=‘SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary‘
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_format=‘arch_%t_%s_%r‘
*.log_archive_max_processes=30
*.fal_server=primary
*.fal_client=standby
*.standby_file_management=auto
--db file and log file 在主备库的存储位置不一样，需要加以下参数
*.db_file_name_convert=‘primary‘,‘standby‘
*.log_file_name_convert=‘/oradata/orcl‘,‘/oradata/orcl‘
2. 创建备库监听文件，密码文件，网络服务名
1）监听文件（listener.ora）
$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.101)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orcl)
(SID_NAME=orcl)
(ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db_1)
(PRESPAWN_MAX=20)
(PRESPAWN_LIST=
(PRESPAWN_DESC=(PROTOCOL=tcp)(POOL_SIZE=2)(TIMEOUT=1))
)
)
)
2) 网络服务名（tnsnames.ora）
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))
)
(CONNECT_DATA =http://www.mamicode.com/
(SERVICE_NAME = orcl)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
)
(CONNECT_DATA =http://www.mamicode.com/
(SERVICE_NAME = orcl)
)
3) 密码文件
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapworcl password=oracle
4) 如果是在windows平台，则需要通过oradim工具创建一个基于windows的服务
oradim -NEW -SID orcl -INTPWD oracle -STARTMODE manual
启动监听
$ lsnrctl start
3. 把主库备份集传到备库上
为方便起见，备库使用与主库同样的目录，可以使用NFS挂载，我的测试库比较下，就用scp了
$ cd /oradata/bak
$ ll
$ ll /oradata/bak/
total 280208
-rw-r----- 1 oracle oinstall 512512 Sep 28 16:50 arch_ORCL_20140928_42_p
-rw-r----- 1 oracle oinstall 45056 Sep 28 16:50 arch_ORCL_20140928_43_p
-rw-r----- 1 oracle oinstall 2560 Sep 28 16:50 arch_ORCL_20140928_44_p
-rw-r----- 1 oracle oinstall 9895936 Sep 28 16:50 c_ORCL_20140928_s_1
-rw-r----- 1 oracle oinstall 208125952 Sep 28 16:50 full_ORCL_20140928_38_1
-rw-r----- 1 oracle oinstall 58040320 Sep 28 16:50 full_ORCL_20140928_39_1
-rw-r----- 1 oracle oinstall 9895936 Sep 28 16:50 full_ORCL_20140928_40_1
-rw-r----- 1 oracle oinstall 98304 Sep 28 16:50 full_ORCL_20140928_41_1
$ scp * standby:/oradata/bak
arch_ORCL_20140928_42_p 100% 501KB 500.5KB/s 00:00
arch_ORCL_20140928_43_p 100% 44KB 44.0KB/s 00:00
arch_ORCL_20140928_44_p 100% 2560 2.5KB/s 00:00
c_ORCL_20140928_s_1 100% 9664KB 9.4MB/s 00:01
full_ORCL_20140928_38_1 100% 198MB 22.1MB/s 00:09
full_ORCL_20140928_39_1 100% 55MB 13.8MB/s 00:04
full_ORCL_20140928_40_1 100% 9664KB 9.4MB/s 00:00
full_ORCL_20140928_41_1 100% 96KB 96.0KB/s 00:00
4. 还原备库控制文件
SQL> create spfile from pfile=‘/tmp/pfile‘;
File created.
SQL> startup nomount
RMAN> restore standby controlfile from ‘/oradata/bak/c_ORCL_20140928_s_1‘;
5. 启动备库到mount，还原备库，恢复备库到指定的序列号的归档
RMAN> sql ‘alter database mount standby database‘;
RMAN> restore database;
查看当前备份集里的最大归档序列号
RMAN> list backup of archivelog all;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
38 500.00K DISK 00:00:00 28-SEP-14
BP Key: 38 Status: AVAILABLE Compressed: NO Tag: TAG20140928T164416
Piece Name: /oradata/bak/arch_ORCL_20140928_42_p
List of Archived Logs in backup set 38
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 43 253551 28-SEP-14 254459 28-SEP-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
39 43.50K DISK 00:00:00 28-SEP-14
BP Key: 39 Status: AVAILABLE Compressed: NO Tag: TAG20140928T164416
Piece Name: /oradata/bak/arch_ORCL_20140928_43_p
List of Archived Logs in backup set 39
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 44 254459 28-SEP-14 254465 28-SEP-14
1 45 254465 28-SEP-14 254469 28-SEP-14
1 46 254469 28-SEP-14 254603 28-SEP-14
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
40 2.00K DISK 00:00:00 28-SEP-14
BP Key: 40 Status: AVAILABLE Compressed: NO Tag: TAG20140928T164416
Piece Name: /oradata/bak/arch_ORCL_20140928_44_p
List of Archived Logs in backup set 40
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 47 254603 28-SEP-14 254611 28-SEP-14
最大归档序列号为47，指定归档序列号，恢复备库，也可以不指定，则在应用完备份集里最后一组归档时，会报错找不到下一个归档，实际上已经恢复完成，因为备份集本来就没有下一个归档
RMAN> recover database until sequence 48;
6. 打开备库至恢复模式，开始应用归档
SQL> alter database recover managed standby database disconnect from session;
Database altered.
测试：
主库切一次归档，查看备库应用情况
primary:
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
49
standby:
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
49
搭建物理备库