两个服务器的oracle 11g的主从同步怎么配啊。standby的方法没成功
解决方案 »
- 在oracle的存储过程中用事务如何实现.net中的try/catch语句?如何写啊?
- 求教数据库方法如何写.
- sql 2000中有个@@ROWCOUNT 在oracle中对应什么呀
- 遗留session阻塞问题(求访问私有sql存储区的方法)
- 存储过程传字符串的问题
- 象select count(*) from (select hpid from jfytk group by hpid);这样的语句在pl/sql中不能用吗?
- 求救:ORACLE 中用sql进行分页在两个数据库中结果不一样!
- 把oracle8.0.5一个用户的所有数据存到另一个用户下怎么做?
- 如何使保存的记录的主键字段的值唯一?高手请进,常见问题
- 在oracle里,怎么设自增的列呀!---急,急!
- oracle触发器调用http接口
- ORA-12170 TNS 连接超时 (同一网段用plsql无法连接,不同网段可以)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1580796.1
我是按照文档一步一步来的,我从来没配过oracle主从。具体步骤是
2.设置主库为归档模式:
SQL> archive log list
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list
3.检查主机是否有口令文件,如没有需建立
orapwd file='/opt/oracle/product/10.2.0/dbs/orawdlottery.ora' password=sys entries=5
4.为主数据库添加"备用联机日志文件"
SQL> alter database add standby logfile group 4
('/opt/oracle/oradata/lottery/redo04.log') size 50m;
SQL> alter database add standby logfile group 5
('/opt/oracle/oradata/lottery/redo05.log') size 50m;
SQL> alter database add standby logfile group 6
('/opt/oracle/oradata/lottery/redo06.log') size 50m;
SQL> alter database add standby logfile group 7
('/opt/oracle/oradata/lottery/redo07.log') size 50m;
5.修改主库参数文件
SQL> create pfile='/opt/oracle/admin/lottery/pfile/init.ora' from spfile;
lottery.__db_cache_size=100663296
lottery.__java_pool_size=4194304
lottery.__large_pool_size=4194304
lottery.__shared_pool_size=54525952
lottery.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/lottery/adump'
*.background_dump_dest='/opt/oracle/admin/lottery/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/oracle/oradata/lottery/control01.ctl','/opt/oracle/oradata/lottery/control02.ctl','/opt/oracle/oradata/lottery/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/lottery/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='lottery'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=lotteryXDB)'
*.job_queue_processes=10
*.log_archive_format='%T%S%r.ARC'
*.log_archive_max_processes=3
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/lottery/udump'
#add below parameter for standy database
*.DB_UNIQUE_NAME='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=/opt/oracle/oradata/lottery/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.log_archive_dest_2='SERVICE=standby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='standby'
*.FAL_CLIENT='primary'
6.主库用PFILE建立SPFILE
[oracle@host127 pfile]$ sqlplus '/ as sysdba'
SQL> create spfile from pfile='/opt/oracle/admin/lottery/pfile/init.ora';
7.建立备用库的控制文件
SQL> alter database create standby controlfile as '/tmp/standby_ctl01.ctl';
8.配置主数据库listener及tnsnames
[oracle@host127 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = lottery)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(SID_NAME = lottery)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host127)(PORT = 1521))
)
)
#加1522端口供以后做switchover
SID_LIST_LISTENER1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = lottery)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(SID_NAME = lottery)
)
)
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host127)(PORT = 1522))
)
)
[oracle@host127 admin]$ cat tnsnames.ora
#1521和1522端口都能连上主机和备机,这样在做switchover时就不需要改这里的设置了
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.127)(PORT = 1522))
)
(CONNECT_DATA =
(SID = lottery)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.127)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))
)
(CONNECT_DATA =
(SID = lottery)
)
)
9.复制文件到备机
[oracle@host128 ~]cd /opt/oracle/oradata/lottery
[oracle@host128 lottery]$rcp host127:/opt/oracle/oradata/lottery/*.dbf .
[oracle@host128 lottery]$rcp host127:/tmp/standby_ctl01.ctl .
[oracle@host128 lottery]$mv standby_ctl01.ctl control01.ctl
[oracle@host128 lottery]$cp control01.ctl control02.ctl
[oracle@host128 lottery]$cp control01.ctl control03.ctl
10.复制并修改备机的参数文件
[oracle@host128 ~]cd /opt/oracle/admin/lottery/pfile
[oracle@host128 pfile]$rcp host127:/opt/oracle/admin/lottery/pfile/init.ora .
修改为如下:
lottery.__db_cache_size=100663296
lottery.__java_pool_size=4194304
lottery.__large_pool_size=4194304
lottery.__shared_pool_size=54525952
lottery.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/lottery/adump'
*.background_dump_dest='/opt/oracle/admin/lottery/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/opt/oracle/oradata/lottery/control01.ctl','/opt/oracle/oradata/lottery/control02.ctl','/opt/oracle/oradata/lottery/control03.ctl'
*.core_dump_dest='/opt/oracle/admin/lottery/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='lottery'
*.db_recovery_file_dest='/opt/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=lotteryXDB)'
*.job_queue_processes=10
*.log_archive_format='%T%S%r.ARC'
*.log_archive_max_processes=3
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/opt/oracle/admin/lottery/udump'
*.DB_UNIQUE_NAME='standby'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=/opt/oracle/oradata/lottery/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primary arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
11.生成备用库密码文件
orapwd file='/opt/oracle/product/10.2.0/dbs/orawdlottery.ora' password=sys entries=5
12.修改备机的listener及tnsnames
[oracle@host128 admin]$ cat listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = lottery)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(SID_NAME = lottery)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host128)(PORT = 1521))
)
)
#加一个1522的端口供以后做switchover
SID_LIST_LISTENER1 =
(SID_LIST =
)
(SID_DESC =
(GLOBAL_DBNAME = lottery)
(ORACLE_HOME = /opt/oracle/product/10.2.0)
(SID_NAME = lottery)
)
)
LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host128)(PORT = 1522))
)
)
[oracle@host128 admin]$ cat tnsnames.ora
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.127)(PORT = 1521))
)
(CONNECT_DATA =
(SID = lottery)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.127)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.128)(PORT = 1522))
)
(CONNECT_DATA =
(SID = lottery)
)
)
13.测试主备之间网络连通
[oracle@host127 admin]$ lsnrctl start
[oracle@host127 admin]$ tnsping standby
[oracle@host128 admin]$ lsnrctl start
[oracle@host128 admin]$ tnsping primary
14.打开备库
SQL> startup mount pfile='/opt/oracle/admin/lottery/pfile/init.ora';
SQL> create spfile from pfile='/opt/oracle/admin/lottery/pfile/init.ora';
SQL> alter database recover managed standby database disconnect from session;
15.打开主库
SQL> startup
16.测试是否OK
主库:
SQL> alter system switch logfile;
从库:
SQL> select FIRST_TIME,NEXT_TIME, APPLIED,SEQUENCE# from v$archived_log order by SEQUENCE#;