主库备库在两台机器上配置 (完美2)
主库备库在两台机器上配置
2012年4月16日
13:23
|
|
-----------------主库备库在两台机器上配置 |
|
|
1、配置tnsnames （主库）
bjdb = (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp)(HOST=192.168.8.240)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=bjdb)))
shdb = (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp)(HOST=192.168.8.249)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=shdb)))
2、拷贝主库datafile 到备库主机 [oracle@work admin]$ cd /u01/app/oracle/oradata/oradb [oracle@work oradb]$ ls control01.ctl redo01a.log redo03a.log stdredo04a.log stdredo06a.log sysaux01.dbf temp01.dbf lxtbs01.dbf redo02a.log rtbs01.dbf stdredo05a.log stdredo07a.log system01.dbf users01.dbf
[oracle@work oradb]$ scp *.dbf oraocm:/u01/app/oracle/oradata/oradbdg
3、生成备库的控制文件，并拷贝到备库主机
SQL> startup mount ORACLE instance started.
Total System Global Area 419430400 bytes Fixed Size 1219760 bytes Variable Size 58721104 bytes Database Buffers 356515840 bytes Redo Buffers 2973696 bytes Database mounted.
----------生成备库的控制文件 SQL> alter database create standby controlfile as '/home/oracle/control01.ctl';
Database altered.
[oracle@work oradb]$ scp /home/oracle/control01.ctl oraocm:/u01/app/oracle/oradata/oradbdg/
4、传送instance 初始化文件到备库主机
SQL> create pfile from spfile;
File created.
[oracle@work oradb]$ scp /u01/app/oracle/product/10.2.0/db_1/dbs/initoradb.ora oraocm:/u01/app/oracle/product/10.2.0/db_1/dbs/initoradbdg.ora
5、在备库修改初始化文件，并建立相关目录
[oracle@oraocm dbs]$ mkdir -p $ORACLE_BASE/admin/oradbdg/bdump [oracle@oraocm dbs]$ mkdir -p $ORACLE_BASE/admin/oradbdg/cdump [oracle@oraocm dbs]$ mkdir -p $ORACLE_BASE/admin/oradbdg/udump [oracle@oraocm dbs]$ mkdir -p /disk1/arch/oradbdg [oracle@oraocm dbs]$ mkdir -p /disk1/oradata/oradbdg
编辑初始化文件initoradbdg.ora
oradbdg.__db_cache_size=356515840 oradbdg.__java_pool_size=4194304 oradbdg.__large_pool_size=4194304 oradbdg.__shared_pool_size=50331648 oradbdg.__streams_pool_size=0 *.background_dump_dest='$ORACLE_BASE/admin/oradbdg/bdump' *.control_files='/u01/app/oracle/oradata/oradbdg/control01.ctl' *.core_dump_dest='$ORACLE_BASE/admin/oradbdg/cdump' *.db_block_size=8192 *.db_cache_size=30M#DEMO *.db_file_multiblock_read_count=16 *.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/oradb','/u01/app/oracle/oradata/oradbdg' *.db_name='oradb' *.DB_UNIQUE_NAME='bjdb' *.FAL_CLIENT='bjdb' *.FAL_SERVER='shdb' *.LOG_ARCHIVE_CONFIG='DG_CONFIG=(bjdb,shdb)' *.LOG_ARCHIVE_DEST_1='LOCATION=/disk1/arch/oradbdg VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bjdb' *.LOG_ARCHIVE_DEST_2='SERVICE=shdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=shdb' *.LOG_ARCHIVE_DEST_STATE_1='ENABLE' *.LOG_ARCHIVE_DEST_STATE_2='ENABLE' *.log_archive_format='arch_%t_%s_%r.log' *.LOG_ARCHIVE_MAX_PROCESSES=10 *.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/oradb','/u01/app/oracle/oradata/oradbdg','/disk1/oradata/oradb','/disk1/oradata/oradbdg' *.optimizer_mode='choose' *.parallel_threads_per_cpu=4#SMALL *.pga_aggregate_target=30M#DEMO *.query_rewrite_enabled='true' *.query_rewrite_integrity='trusted' *.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE' *.sga_target=400m *.shared_pool_size=20M#DEMO *.STANDBY_FILE_MANAGEMENT='AUTO' *.star_transformation_enabled='true' *.undo_management='auto' *.undo_tablespace='rtbs' *.user_dump_dest='$ORACLE_BASE/admin/oradbdg/udump' ~
6、配置备库网络 oracle net （netca配置） tnsnames.ora
BJDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.240)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = bjdb) ) )
SHDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.249)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = shdb) ) )
listener.ora
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1) (PROGRAM = extproc) ) )
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oraocm)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )
7、配置备库口令文件（sys口令保持和主库一致）
[oracle@oraocm dbs]$ orapwd file=orapworadbdg password=oracle entries=3
8、启动备库实例到mount
9、分别在两台机器测试oracle net
---------备库
SQL> show parameter name
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string /u01/app/oracle/oradata/oradb, /u01/app/oracle/oradata/oradb dg db_name string oradb db_unique_name string shdb global_names boolean FALSE instance_name string oradbdg lock_name_space string log_file_name_convert string /u01/app/oracle/oradata/oradb, /u01/app/oracle/oradata/oradb dg, /disk1/oradata/oradb, /dis
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ k1/oradata/oradbdg service_names string shdb
[oracle@oraocm dbs]$ sqlplus sys/oracle@bjdb as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 15 10:14:56 2011
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>
------- 主库
23:03:35 SQL> show parameter name
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string /u01/app/oracle/oradata/oradbd g, /u01/app/oracle/oradata/ora db db_name string oradb db_unique_name string bjdb global_names boolean FALSE instance_name string oradb lock_name_space string log_file_name_convert string /u01/app/oracle/oradata/oradbd g, /u01/app/oracle/oradata/ora db, /disk1/oradata/oradbdg, /d isk1/oradata/oradb service_names string bjdb 23:03:39 SQL>
[oracle@work oradb]$ sqlplus sys/oracle@shdb as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Nov 13 23:04:18 2011
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
10、应用
-------主库
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 70 10485760 2 YES INACTIVE 236298 13-NOV-11 3 1 69 10485760 2 YES INACTIVE 236295 13-NOV-11 2 1 71 10485760 2 NO CURRENT 236300 13-NOV-11
23:09:11 SQL> col file_name for a50 23:10:06 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME ---------- -------------------------------------------------- ------------------------------ 1 /u01/app/oracle/oradata/oradb/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/oradb/rtbs01.dbf RTBS 3 /u01/app/oracle/oradata/oradb/sysaux01.dbf SYSAUX 4 /u01/app/oracle/oradata/oradb/users01.dbf USERS 5 /u01/app/oracle/oradata/oradb/lxtbs01.dbf LXTBS1
23:10:15 SQL> create tablespace lxtbs02 23:10:22 2 datafile '/u01/app/oracle/oradata/oradb/lxtbs02.dbf' size 100m;
Tablespace created.
23:10:35 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME ---------- -------------------------------------------------- ------------------------------ 1 /u01/app/oracle/oradata/oradb/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/oradb/rtbs01.dbf RTBS 3 /u01/app/oracle/oradata/oradb/sysaux01.dbf SYSAUX 4 /u01/app/oracle/oradata/oradb/users01.dbf USERS 5 /u01/app/oracle/oradata/oradb/lxtbs01.dbf LXTBS1 6 /u01/app/oracle/oradata/oradb/lxtbs02.dbf LXTBS02
6 rows selected.
23:10:39 SQL> conn scott/tiger Connected. 23:10:53 SQL> 23:10:53 SQL> create table lxtb2 (id int) tablespace lxtbs02;
Table created.
23:11:04 SQL> insert into lxtb2 values (1);
1 row created.
23:11:11 SQL> insert into lxtb2 values (2);
1 row created.
23:11:12 SQL> insert into lxtb2 values (3);
1 row created.
23:11:13 SQL> commit;
Commit complete.
23:11:14 SQL> select * from lxtb1;
ID ---------- 1 2 3 4 5
23:11:18 SQL> select * from lxtb2;
ID ---------- 1 2 3
23:11:19 SQL> conn /as sysdba Connected. 23:11:22 SQL> 23:11:22 SQL> alter system switch logfile;
System altered.
23:11:27 SQL> /
System altered.
23:11:27 SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 73 10485760 2 NO CURRENT 236394 13-NOV-11 3 1 72 10485760 2 YES ACTIVE 236392 13-NOV-11 2 1 71 10485760 2 YES ACTIVE 236300 13-NOV-11
23:11:31 SQL>
---------备库
SQL> alter database recover managed standby database disconnect from session;
Database altered.
[oracle@oraocm dbs]$ ls /disk1/arch/oradbdg/ arch_1_66_767058760.log arch_1_68_767058760.log arch_1_70_767058760.log arch_1_67_767058760.log arch_1_69_767058760.log arch_1_71_767058760.log [oracle@oraocm dbs]$ exit exit
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- 1 1 70 10485760 2 YES CLEARING 236298 13-NOV-11 3 1 69 10485760 2 YES CLEARING 236295 13-NOV-11 2 1 71 10485760 2 YES CLEARING_CURRENT 236300 13-NOV-11
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME ---------- -------------------------------------------------- ------------------------------ 1 /u01/app/oracle/oradata/oradbdg/system01.dbf SYSTEM 2 /u01/app/oracle/oradata/oradbdg/rtbs01.dbf RTBS 3 /u01/app/oracle/oradata/oradbdg/sysaux01.dbf SYSAUX 4 /u01/app/oracle/oradata/oradbdg/users01.dbf USERS 5 /u01/app/oracle/oradata/oradbdg/lxtbs01.dbf LXTBS1 6 /u01/app/oracle/oradata/oradbdg/lxtbs02.dbf LXTBS02
6 rows selected.
SQL> select * from scott.lxtb1;
ID ---------- 1 2 3 4 5
SQL> select * from scott.lxtb2;
ID ---------- 1 2 3 |
|
|
|
