Pages: 1/13 First page 1 2 3 4 5 6 7 8 9 10 Next page Final page [ View by Articles | List ]

configuration oracle data guard for testing

[不指定 2010/03/11 14:39 | by askwan ]
OS: CentOS 5.4 x86_64
Oracle: Oracle 10.2.0.1 64bit for linux
  
题外话：有些东西长时间不接触，而理解又不深刻的话，慢慢就淡忘了。。。
这个笔记是边参考文档oracle 10g《Data Guard Concepts and Administration》，边验证记录下的
仅供参考

单机环境配置oracle 10g dataguard (physical standby)
IP：192.168.0.21
预期规划如下：

主库：
db_name=dg1
sid=dg1  
db_unique_name=primary  
network_service_name=primary_21

ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/product/10.2.0/db_1
数据文件路径=/u01/oracle/oradata/dg1
归档日志路径=/u01/oracle/oradata/dg1/archive
警告跟踪等日志文件路径=/u01/oracle/admin/dg1/

从库：
dg_name=dg1
sid=standby  
db_unique_name=standby  
network_service_name=standby_21

ORACLE_BASE=/u01/oracle
ORACLE_HOME=/u01/oracle/product/10.2.0/db_1
数据文件路径=/u01/oracle/oradata/standby
归档日志路径=/u01/oracle/oradata/standby/archive
警告跟踪等日志文件路径=/u01/oracle/admin/standby



1 准备工作

      安装完oracle软件后，创建主库

1.1 登录主库，将主库置于归档模式下：


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/oracle/oradata/dg1/archive
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2



打开force logging

SQL>alter database force logging;

SQL>select dbid,log_mode,force_logging from v$database;

      DBID LOG_MODE     FOR
---------- ------------ ---
1694483490 ARCHIVELOG   YES


1.2 建立密码文件

$ orapwd file=/u01/oracle/product/10.2.0/db_1/dbs/orapwdg1 password=askwan.com entries=8
$ orapwd file=/u01/oracle/product/10.2.0/db_1/dbs/orapwstandby password=askwan.com entries=8


1.3 为从库建立相应目录
$ mkdir /u01/oracle/oradata/standby
$ mkdir /u01/oracle/admin/standby/
$ mkdir /u01/oracle/admin/standby/{a,b,c,dp,u}dump
$ mkdir /u01/oracle/admin/standby/pfile


1.4 配置standby redo log

SQL> alter database add standby logfile
        group 4 ('/u01/oracle/oradata/dg1/standby_redo04.log') size 50m,
        group 5 ('/u01/oracle/oradata/dg1/standby_redo05.log') size 50m,
        group 6 ('/u01/oracle/oradata/dg1/standby_redo06.log') size 50m,
        group 7 ('/u01/oracle/oradata/dg1/standby_redo07.log') size 50m;

Database altered.



2  配置监听LISTENER和网络服务名NETWORK SERVICE NAME

   2.1 配置 listener.ora
#Network Configuration File: #/u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = dg1)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
    )
    (SID_DESC =
      (SID_NAME = standby)
      (ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )



2.2 配置网络服务器名
# tnsnames.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PRIMARY_21 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = primary)
    )
  )


STANDBY_21 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = standby)
    )
  )
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )


2.3 启动监听
$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 10-MAR-2010 11:10:27

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.21)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.21)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                10-MAR-2010 11:10:27
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.21)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "dg1" has 1 instance(s).
  Instance "dg1", status UNKNOWN, has 1 handler(s) for this service...
Service "standby" has 1 instance(s).
  Instance "standby", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


3 配置主库初始化参数文件
SQL> create pfile from spfile;

File created.

在/u01/oracle/product/10.2.0/db_1/dbs路径下编辑主库刚生成的pfile文件initdg1.ora

3.1 配置主库参数文件initdg1.ora
未编辑前：

dg1.__db_cache_size=134217728
dg1.__java_pool_size=4194304
dg1.__large_pool_size=4194304
dg1.__shared_pool_size=67108864
dg1.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/dg1/adump'
*.background_dump_dest='/u01/oracle/admin/dg1/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/oradata/dg1/control01.ctl','/u01/oracle/oradata/dg1/control02.ctl','/u01/oracle/oradata/dg1/control03.c
tl'
*.core_dump_dest='/u01/oracle/admin/dg1/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dg1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/u01/oracle/oradata/dg1/archive'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=70254592
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=211812352
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/dg1/udump'


编辑后：

dg1.__db_cache_size=134217728
dg1.__java_pool_size=4194304
dg1.__large_pool_size=4194304
dg1.__shared_pool_size=67108864
dg1.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/dg1/adump'
*.background_dump_dest='/u01/oracle/admin/dg1/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/oradata/dg1/control01.ctl','/u01/oracle/oradata/dg1/control02.ctl','/u01/oracle/oradata/dg1/control03.c
tl'
*.core_dump_dest='/u01/oracle/admin/dg1/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dg1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=70254592
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=211812352
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/dg1/udump'
DB_UNIQUE_NAME=primary
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
DB_FILE_NAME_CONVERT='standby','dg1'
LOG_FILE_NAME_CONVERT='standby','dg1'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/oracle/oradata/dg1/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_2='SERVICE=standby_21 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=standby_21
FAL_CLIENT=primary_21


3.2 配置从库初始化参数文件initstandby.ora
  从库文件则依据主库文件修改，修改后：

standby.__db_cache_size=134217728
standby.__java_pool_size=4194304
standby.__large_pool_size=4194304
standby.__shared_pool_size=67108864
standby.__streams_pool_size=0
*.audit_file_dest='/u01/oracle/admin/standby/adump'
*.background_dump_dest='/u01/oracle/admin/standby/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/u01/oracle/oradata/standby/control01.ctl','/u01/oracle/oradata/standby/control02.ctl','/u01/oracle/oradata/standby/control03.ctl'
*.core_dump_dest='/u01/oracle/admin/standby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='dg1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dg1XDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=70254592
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=211812352
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/oracle/admin/standby/udump'
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
DB_FILE_NAME_CONVERT='dg1','standby'
LOG_FILE_NAME_CONVERT='dg1','standby'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/oracle/oradata/standby/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
LOG_ARCHIVE_DEST_2='SERVICE=primary_21 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_CLIENT=standby_21
FAL_SERVER=primary_21


4 备份主库数据文件到从库
SQL> alter database begin backup;

Database altered.


$ cp -frp /u01/oracle/oradata/dg1/* /u01/oracle/oradata/standby/
$ rm -fr /u01/oracle/oradata/standby/control0*.ctl

建立从库控制文件
SQL> alter database create standby controlfile as '/u01/oracle/oradata/standby/control01.ctl';

Database altered.


SQL> alter database end backup;

Database altered.


$ cp /u01/oracle/oradata/standby/control01.ctl /u01/oracle/oradata/standby/control02.ctl

$ cp /u01/oracle/oradata/standby/control01.ctl /u01/oracle/oradata/standby/control03.ctl



SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> create spfile from pfile;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  213909504 bytes
Fixed Size                  2019672 bytes
Variable Size              75501224 bytes
Database Buffers          134217728 bytes
Redo Buffers                2170880 bytes
Database mounted.
Database opened.


5 数据库启动
5.1 从库打开redo apply

$ export ORACLE_SID=standby


SQL>startup pfile='/u01/oracle/product/10.2.0/db_1/dbs/initstandby.ora' nomount
ORACLE instance started.

Total System Global Area  213909504 bytes
Fixed Size                  2019672 bytes
Variable Size              75501224 bytes
Database Buffers          134217728 bytes
Redo Buffers                2170880 bytes
Database mounted.

SQL>alter database mount standby database

SQL> alter database recover managed standby database disconnect from session;


主库：

SQL> select max(sequence#) from v$archived_Log;

MAX(SEQUENCE#)
--------------
             4

从库：

SQL> select max(sequence#) from v$archived_Log;

MAX(SEQUENCE#)
--------------
             4

切换操作不做了

参考文档:《Data Guard Concepts and Administration》
Tags: ,
DataBase | Comments(0) | Trackbacks(0) | Reads(31)

a simple oracle rman backup script

[不指定 2010/03/05 16:17 | by askwan ]
Note:this backup script is suitable for db size < 200G,
and for backup file safety ,may be you should use an independent disk to store backup files


1  configuration target database to archive log mode

2 configuration or check log_archive_dest and db_recovery_file_dest or log_archive_dest_n

3 write a  rman script  

oracle@dbtest:~$ vim backup.rman

run{
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 15 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/oracle/orabak/ctfile_%F';
ALLOCATE CHANNEL C1 DEVICE TYPE DISK FORMAT '/opt/oracle/orabak/DBFULL_%U';
BACKUP DATABASE SKIP INACCESSIBLE PLUS ARCHIVELOG FILESPERSET 20 DELETE ALL INPUT;
RELEASE CHANNEL C1;
}


4 write backup shell script

oracle@dbtest:~$ vim backup.sh
#!/bin/bash
export ORACLE_BASE=/opt/oracle
export ORACLE_SID=capitalv
export ORACLE_HOME=$ORACLE_BASE/oracle/product/10.2.0/db_1
PATH=/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/bin:/sbin:$PATH;
export PATH=$ORACLE_HOME/bin:$PATH
rman TARGET /  log='/opt/oracle/orabak/rman.log' cmdfile='/opt/oracle/backup.rman'



5 set cronjob for user oracle

30 02 * * 0,3  bash /opt/oracle/backup.sh
Tags: , ,
DataBase | Comments(0) | Trackbacks(0) | Reads(56)

how to create a rman catalog

[不指定 2010/03/02 12:07 | by askwan ]

SQL> create tablespace rmantest datafile '/u01/oracle/oradata/askwan/rmantest01.dbf' size 100M autoextend on ;

Tablespace created.

SQL> create user rman identified by rman;

User created.

SQL> alter user rman default tablespace rmantest temporary tablespace temp;

User altered.

SQL> alter user rman quota unlimited on rmantest;

User altered.

SQL> grant recovery_catalog_owner,connect,resource to rman;

Grant succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


login with user rman to create catalog

[oracle@oracle ~]$ rman catalog rman/rman

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 2 12:10:44 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to recovery catalog database

RMAN>
RMAN> create catalog tablespace rmantest;

recovery catalog created


create rman catalog done!
Tags: ,
DataBase | Comments(0) | Trackbacks(0) | Reads(34)
Pages: 1/13 First page 1 2 3 4 5 6 7 8 9 10 Next page Final page [ View by Articles | List ]