dg教程
------------------ORACLE 11g 主从备份DG搭建教程------------------
主库安装数据库软件和新建数据库qyq
从库安装数据库软件
主库
sqlplus / as sysdba;
shutdown immediate;
startup mount;
alter database force logging;
alter database archivelog;
alter database add standby logfile thread 1 group 4 ('/opt/oracle/oradata/qyq/redo04.log') size 50M;
alter database add standby logfile thread 1 group 5 ('/opt/oracle/oradata/qyq/redo05.log') size 50M;
alter database add standby logfile thread 1 group 6 ('/opt/oracle/oradata/qyq/redo06.log') size 50M;
alter database add standby logfile thread 1 group 7 ('/opt/oracle/oradata/qyq/redo07.log') size 50M;
create pfile from spfile;
修改
/opt/oracle/product/11.2.0/dbhome_1/dbs/initqyq.ora;
shutdown immediate;
create spfile from pfile;
startup
修改listener.ora、tnsnames.ora
lsnrctl reload;
scp -r /opt/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora oracle/192.168.9.209:/opt/oracle/product/11.2.0/dbhome_1/network/admin/
scp -r /opt/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora oracle/192.168.9.209:/opt/oracle/product/11.2.0/dbhome_1/network/admin/
scp -r /opt/oracle/product/11.2.0/dbhome_1/dbs/initqyq.ora oracle/192.168.9.209:/opt/oracle/product/11.2.0/dbhome_1/dbs
-----------------------------------
备库
修改initqyq.ora、listener.ora
lsnrctl reload;
shutdown immediate;
create spfile from pfile;
startup nomount;
或者
startup nomount pfile='/opt/oracle/product/11.2.0/dbhome_1/dbs/initqyq.ora';
-----------------------------------
主库
rman target sys/qyq@pri auxiliary sys/qyq@dg nocatalog;
duplicate target database for standby from active database dorecover nofilenamecheck;
或者
duplicate target database for standby from active database nofilenamecheck;
归档的dbf文件,会copy到initqyq.ora定义的归档目录/opt/oracle/archive
-----------------------------------
从库
SQL> shutdown immediate
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database open read only;
SQL> alter database recover managed standby database using current logfile disconnect from session;
-----------------------------------
验证数据是否同步。
主库:
SQL> create table zsx (id number);
Table created.
备库:
SQL> desc zsx
主库:
SQL> insert into zsx values(1);
1 row created.
SQL> commit;
Commit complete.
备库:
SQL> select * from zsx;
ID
----------
1
教程结束
----------------------------------------------------------
主库initqyq.ora增加
*.db_unique_name=pri
*.log_archive_config='DG_CONFIG=(pri,dg)'
*.log_archive_dest_1='LOCATION=/opt/oracle/archive/qyq/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri'
*.log_archive_dest_2='SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.fal_server='dg'
*.fal_client='pri'
*.db_file_name_convert='oradata/qyqdg','oradata/qyq'
*.log_file_name_convert='oradata/qyqdg','oradata/qyq'
*.standby_file_management=AUTO
----------------------------------------------------------
从库initqyq.ora增加
*.db_unique_name=dg
*.log_archive_config='DG_CONFIG=(pri,dg)'
*.log_archive_dest_1='LOCATION=/opt/oracle/archive/qyq VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg'
*.log_archive_dest_2='SERVICE=pri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.fal_server='pri'
*.fal_client='dg'
*.db_file_name_convert='oradata/qyqdg','oradata/qyq'
*.log_file_name_convert='oradata/qyqdg','oradata/qyq'
*.standby_file_management=AUTO
----------------------------------------------------------
主库listener.ora增加
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.208)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /opt/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = qyq)
(GLOBAL_DBNAME = pri)
(ORACLE_HOME = /opt/oracle/product/11.2.0/dbhome_1)
)
)
-----------------------------------------------------------
从库listener.ora增加
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.209)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /opt/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = qyq)
(GLOBAL_DBNAME = dg)
(ORACLE_HOME = /opt/oracle/product/11.2.0/dbhome_1)
)
)
-----------------------------------------------------------
主从库tnsnames.ora增加
pri =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.9.208 )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pri)
)
)
dg =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.9.209)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg)
)
)