Pages: 1/61 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(15)

A friend company's Linux server ext3 filesystem becomes read only

[不指定 2010/03/08 11:12 | by askwan ]
System has been restarted everything seemed normal, the application starts normally! View the system boot log, there is no exception, the specific reasons for this situation also unknown now! It seems  need to continue to observe.

However, open system, found that swap partition 0
Swap:        0k total,        0k used,        0k free,   300496k cached

Is it did not installed the system swap partition?
[root@youke2 log]# cat /proc/swaps

output Nothing, there is no swap partition indeed !
this System has 4G mem, so additional 8G swap space manually
[root@youke2 log]# dd if=/dev/zero of=/tmp/swapfree bs=1024K count=8192
8192+0 records in 8192+0 records out 8589934592 bytes (8.6 GB) copied, 111.247 seconds, 77.2 MB/s


[root@youke2 log]# mkswap /tmp/swapfree
Setting up swapspace version 1, size = 8589930 kB

[root@youke2 log]# swapon /tmp/swapfree

Re-examine swap partition:

[root@youke2 log]# cat /proc/swaps
Filename                                Type            Size    Used    Priority
/tmp/swapfree                           file            8388600 0       -1

USE top TO CHECK  
Tasks: 145 total,   1 running, 144 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.1%us,  0.2%sy,  0.0%ni, 94.4%id,  5.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4138172k total,  3834908k used,   303264k free,    28220k buffers
Swap:  8388600k total,        0k used,  8388600k free,  3605544k cached

OK! write this to fstab file,so it can valid at system start time !
/tmp/swapfree           swap                    swap    defaults        0 0
Oprating System | Comments(0) | Trackbacks(0) | Reads(17)

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(49)
Pages: 1/61 First page 1 2 3 4 5 6 7 8 9 10 Next page Final page [ View by Articles | List ]