在线咨询
阅读：43
配置主机信息：
/etc/hostname
/etc/hosts
记得重启
192.168.1.99 primary
192.168.1.100 standby
然后进入安装
环境描述：
主库：
IP： 192.168.1.99
主机名：primary
ORACLE_SID=afc
ORACLE_BASE=/data/oracle
ORACLE_HOME=/data/oracle/product/11.2.0/db_1
备库：
IP： 192.168.1.100
主机名：standby
ORACLE_SID=afc
ORACLE_BASE=/data/oracle
ORACLE_HOME=/data/oracle/product/11.2.0/db_1
准备工作：分别在主库和备库都安装上oracle软件，不装数据库。
安装配置步骤：
1、运行dbca创建数据库
2、登陆到数据库上
[oracle@primary ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 3月 11 20:31:09 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn / as sysdba;
Connected.
SQL>
3、将主库设置为 FORCE LOGGING 模式
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
4、创建一个密码文件，如果数据库是用dbca创建的则会在$ORACLE_HOME/dbs/下自动创建一个叫orapwdSID的一个密码文件。否则可以用 orapwd命令创建一个。
5、检查数据库是否处于归档状态
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/oracle/flash_recovery_area
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
如果不是处于归档状态则运行下面命令
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
6、创建备库的控制文件（创建后数据库不能做结构性的改变）
SQL> alter database create standby controlfile as '/data/oracle/bak2.ctl';
7、创建pfile以便于修改配置参数
SQL> CREATE PFILE FROM SPFILE;
8、关掉数据库开始编辑参数
SQL> shutdown immediate;
9、$ vi /data/oracle/product/11.2.0/db_1/dbs/initorcl.ora （重点处）
添加下面内容
cdlp.__db_cache_size=1560281088
orcl.__db_cache_size=1560281088
cdlp.__java_pool_size=33554432
orcl.__java_pool_size=33554432
cdlp.__large_pool_size=50331648
orcl.__large_pool_size=50331648
cdlp.__oracle_base='/data/oracle'#ORACLE_BASE set from environment
orcl.__oracle_base='/data/oracle'#ORACLE_BASE set from environment
cdlp.__pga_aggregate_target=72477573120
orcl.__pga_aggregate_target=72477573120
cdlp.__sga_target=2147483648
orcl.__sga_target=2147483648
cdlp.__shared_io_pool_size=0
orcl.__shared_io_pool_size=0
cdlp.__shared_pool_size=436207616
orcl.__shared_pool_size=419430400
cdlp.__streams_pool_size=16777216
orcl.__streams_pool_size=33554432
*.aq_tm_processes=2
*.audit_file_dest='/data/oracle/admin/cdlp/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/oracle/oradata/cdlp/control01.ctl','/data/oracle/fast_recovery_area/cdlp/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/data/oracle/app/oracle/oradata/orcl/archivelog'
*.db_domain=''
*.DB_FILE_NAME_CONVERT='standby','primary'
*.db_name='cdlp'
*.db_recovery_file_dest='/data/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.DB_UNIQUE_NAME='primary'
*.diagnostic_dest='/data/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cdlpXDB)'
*.FAL_CLIENT='primary'
*.FAL_SERVER='standby'
*.global_names=TRUE
*.job_queue_processes=10
*.log_archive_format='arch%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.log_file_name_convert='/data/oracle/app/oracle/oradata/orcls','/data/oracle/app/oracle/oradata/orcl'
*.nls_date_format='YYYY-MM-DD HH24:MI:SS'
*.open_cursors=3000
*.open_links=4
*.parallel_max_servers=20
*.pga_aggregate_target=72477573120
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2147483648
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.streams_pool_size=26214400
*.undo_retention=3600
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='*'
*.DB_UNIQUE_NAME=primary
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='location=/data/oracle/flash_recovery_area
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_MAX_PROCESSES=30
*.FAL_SERVER=standby
*.FAL_CLIENT=primary
*.DB_FILE_NAME_CONVERT='standby','primary'
*.STANDBY_FILE_MANAGEMENT=AUTO
10、进入sqlplus创建spfile(NOMOUNT状态)
SQL> CREATE SPFILE from PFILE;
11、创建tnsnames.ora和listener.ora，可以手动创建也可以用netca创建，无论哪种方法都必须保证能互相畅通。
$ cd /data/oracle/product/11.2.0/db_1/network/admin/
$ vi tnsnames.ora
输入下面内容
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.90)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME = primary)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.98)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER=DEDICATED)
(SERVICE_NAME = standby)
)
)
$ vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /data/oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.90)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
ADR_BASE_LISTENER = /data/oracle
12、复制备份文件到备库
$ cd /data/oracle/
$ scp -r flash_recovery_area/ admin/ oradata/ 192.168.1.100:/u01/oracle/
$ cd /u01/oracle/10g/network/admin/
$ scp listener.ora tnsnames.ora 192.168.1.100:/u01/oracle/10g/network/admin/
$ cd /data/oracle/product/11.2.0/db_1/dbs/
$ scp orapwdafc 192.168.1.100:/data/oracle/product/11.2.0/db_1/dbs/
13、打开备库做以下操作
$ cd /data/oracle/oradata/afc
$ rm -rf control0*
$ cp stdby.ctl control01.ctl
$ cp stdby.ctl control02.ctl
$ cp stdby.ctl control03.ctl
$ vi initorcl.ora 修改备的参数文件如下（重点处）
cdlp.__db_cache_size=1560281088
orcl.__db_cache_size=1593835520
cdlp.__java_pool_size=33554432
orcl.__java_pool_size=33554432
cdlp.__large_pool_size=50331648
orcl.__large_pool_size=50331648
cdlp.__oracle_base='/data/oracle'#ORACLE_BASE set from environment
orcl.__oracle_base='/data/oracle'#ORACLE_BASE set from environment
cdlp.__pga_aggregate_target=72477573120
orcl.__pga_aggregate_target=72477573120
cdlp.__sga_target=2147483648
orcl.__sga_target=2147483648
cdlp.__shared_io_pool_size=0
orcl.__shared_io_pool_size=0
cdlp.__shared_pool_size=436207616
orcl.__shared_pool_size=419430400
cdlp.__streams_pool_size=16777216
orcl.__streams_pool_size=0
*.audit_file_dest='/data/oracle/admin/cdlp/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/data/oracle/oradata/cdlp/control01.ctl','/data/oracle/fast_recovery_area/cdlp/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/data/oracle/app/oracle/oradata/orcl/archivelog'
*.db_domain=''
*.db_file_name_convert='/data/oracle/app/oracle/oradata/orcls','/data/oracle/app/oracle/oradata/orcl'
*.db_name='CDLP'
*.db_recovery_file_dest='/data/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/data/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cdlpXDB)'
*.fal_client='lei'
*.fal_server='ORCLS'
*.log_archive_config='dg_config=(cdlp)'
*.log_archive_dest_1='LOCATION=/data/oracle/app/oracle/oradata/orcl/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=cdlp'
*.log_archive_dest_2='service=lei_standby valid_for=(online_logfiles,primary_role) db_unique_name=lei_s'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_max_processes=30
*.log_file_name_convert='/data/oracle/app/oracle/oradata/orcls','/data/oracle/app/oracle/oradata/orcl'
*.open_cursors=3000
*.pga_aggregate_target=72477573120
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2147483648
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.DB_UNIQUE_NAME=standby
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='location=/data/oracle/flash_recovery_area
valid_for=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=standby'
*LOG_ARCHIVE_DEST_2=
SERVICE=primary LGWR ASYN
CVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=primary'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER=primary
*.FAL_CLIENT=standby
*.DB_FILE_NAME_CONVERT='standby','primary'
*.STANDBY_FILE_MANAGEMENT=AUTO
$ vi listener.ora
将primary修改为standby
13、在备库中进入sqlplus
$ sqlplus /nolog
SQL> conn / as sysdba;
SQL> CREATE SPFILE from PFILE='/data/oracle/oradata/dgdemo/pfile';
14、测试oracle net是否畅通
首先启动监听服务（分别在主机和备机上进行）
$ lsnrctl stop
$ lsnrctl start
分别在主机和备机上运行下面命令来测试
$ tnsping primary
$ tnsping standby
15、启动主库（正常启动）
$ sqlplus /nolog
SQL> conn / as sysdba;
SQL> startup
16、启动备库到mount状态，并打开redo Apply
$ sqlplus /nolog
SQL> conn / as sysdba;
SQL> startup mount
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
17、在主库上做一次日志切换
SQL> ALTER SYSTEM SWITCH LOGFILE;
以下语句可以查看日志应用情况
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APP
---------- ---
2 YES
3 YES
如果出现上面情况则说明配置成功。
18、在主库建表然后在备库查询进行验证
SQL> create table haha(id integer,name char(10))
SQL> insert into haha values(1,'xiao');
SQL> commit;
SQL> select * from haha;
ID NAME
---------- ----------
1 xiao
19、在主库上做个日志归档
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
20、在备库上取消掉redo apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
SQL> select * from haha;
ID NAME
---------- ----------
1 xiao
测试成功
24、再次切换为备库的redo apply状态
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
主库备库互相切换
25、在主库上做如下sql语句
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> shutdown immediate;
SQL> startup nomount;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT
26、在备库上做switchover
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL> shutdown immediate;
SQL> startup
27、分别在新的主库和备库上查看状态，验证主库备库是否切换正常。
SQL> select name,database_role from v$database;
再次创建表在切换后的主库备库上验证
28、在新的主库上创建列
SQL> insert into haha values(2,'yang');
SQL> commit;
SQL> select * from haha;
ID NAME
---------- ----------
1 xiao
2 yang
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
29、在新的备库上做下面操作以取消redo apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE OPEN;
SQL> select * from haha;
ID NAME
---------- ----------
1 xiao
2 yang
30、恢复新备库为redo apply状态
最后将数据库切换出去主备状态，如果相关同步都正常说明配置没问题可以用于生产了；
31、主备切换时候遇到如下错误提示（主数据切换为备数据库后再切换回主系统时出现错误），解决
如下：
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required
mount模式下执行alter database recover managed standby database finish
SQL> alter database commit to switchover to primary;
Database altered.
备用信息：
数据库帐号密码
oracle
oracle123
/etc/hostname
/etc/hosts
chown -R oracle:oinstall /apps/oracle
日志目录
./oracle/diag/rdbms/standby/orcl/trace/alert_orcl.log
./oracle/diag/rdbms/cdlp/cdlp/trace/alert_cdlp.log
CREATE SPFILE from PFILE='/data/oracle/admin/cdlp/pfile';
开启备份
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
关闭
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
实例名
orcl
数据库ming
启动oracle监听
lsnrctl start
lsnrctl stop
lsnrctl status;
lsnrctl relaod;
chown -R oracle:oinstall /apps/oracle
chmod 775 /apps/oracle
开启数据库
ALTER DATABASE OPEN;
进入sql
sqlplus / as sysdba
注册数据库
alter system register;
创建用户
CREATE USER cdlp IDENTIFIED BY cdlp123;
GRANT CONNECT, RESOURCE TO cdlp;
GRANT CREATE SESSION TO cdlp;
查询数据库
SELECT name FROM v$database;
查看 oracle 实例名
SELECT INSTANCE_NAME FROM V$INSTANCE;
连接数据库
connect cdlp/cdlp123
关闭数据库
SHUTDOWN IMMEDIATE;
//不经常使用 尽量少用
shutdown abort;
STARTUP MOUNT;
sqlplus cdlp/cdlp123@//192.168.1.2:1521/orcl as sysdba
//回复数据
recover datafile '/data/oracle/oradata/cdlp/system01.dbf' ;
show parameter spfile;
错误：ORA-32001:write to spfile requested but no spfile is in use请
那么就创建spfile
create spfile from pfile;
alter system set control_files='/data/oracle/oradata/cdlp/control01.ctl','/data/oracle/oradata/cdlp/control01.ct2' scope=spfile;
alter system set control_files='/data/oracle/oradata/cdlp/control01.ctl' scope=spfile;
alter system set control_files='/data/oracle/bak2.ctl' scope=spfile;
alter database backup controlfile to trace as '/data/oracle/oradata/control01_bak.ctl';
alter database backup controlfile to '/data/oracle/control.bak';
系统用户
sys/system/sysman/dbsnmp
启动数据库
STARTUP pfile='/data/oracle/product/11.2.0/db_1/dbs/initorcl.ora';
STARTUP MOUNT pfile='/data/oracle/product/11.2.0/db_1/dbs/initorcl.ora';
startup nomount pfile='/data/oracle/product/11.2.0/db_1/dbs/spfilecdlp.ora';
STARTUP NOMOUNT pfile='/data/oracle/product/11.2.0/db_1/dbs/spfilecdlp.ora';
startup pfile='/data/oracle/pfile.ora';
startup MOUNT pfile='/data/oracle/pfile.ora';
STARTUP NOMOUNT pfile='/data/oracle/product/11.2.0/db_1/dbs/initorcl.ora';
//创建启动文件 并复制到次服务器
alter database create standby controlfile as '/data/oracle/oradata/cdlp/control03.ctl'
alter database create standby controlfile as '/data/oracle/bak2.ctl';
次服务器指定启动文件
alter system set control_files='/data/oracle/bak.ctl' scope=spfile;
启用同步服务
alter database recover managed standby database using current logfile disconnect from session;
ALTER DATABASE MOUNT pfile='/data/oracle/product/11.2.0/db_1/dbs/spfilecdlp.ora';
设置游标数量
alter system set open_cursors=3000 scope=both;
设置连接数
alter system set processes =500 scope =spfile;
启动出错
cp /data/oracle/product/11.2.0/db_1/dbs/spfilecdlp.ora /data/oracle/product/11.2.0/db_1/dbs/initorcl.ora
恢复数据
ALTER DATABASE OPEN RESETLOGS;
查看字符集
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');
日志目录
/data/oracle/diag/rdbms/cdlp/orcl/trace
查看ORACLE最大游标数：
SQL> show parameter open_cursors;
查看当前打开的游标数目：
SQL> select count(*) from v$open_cursor;
设置数据库最大游标数量：
SQL>alter system set open_cursors=3000 scope=both;
groupadd oinstall # 创建oinstall组
groupadd dba # 创建dba组
useradd -g oinstall -G dba oracle # 创建oracle用户并将其加入到两个组中
passwd oracle # 设置oracle用户的密码
安装数据库
./runInstaller
文件配置 db_install.rsp
创建密码文件
./orapwd file=/u01/app/oracle/wallets/ewallet.p12 password=welcome123 entries=10 force=y
日志目录
tail -f /data/oracle/diag/rdbms/standby/orcl/trace/alert_orcl.log
tail -f /data/oracle/diag/rdbms/cdlp/cdlp/trace/alert_cdlp.log
alter system set log_archive_dest_2 = 'SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
删除没用 GROUP
ALTER DATABASE DROP LOGFILE GROUP 13;
