oracle系列教程

centos7搭建oracle11g

oracle11g dg1

oracle11g dg 2

oracle11g dg3

centos7调整分区

jdbc链接oracle慢

oracle归档问题

oracle最大连接数问题

开启归档

 

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)
    )
  )