我的STANDBY测试环境建立经验

Cluster

Before you want implement Standby Database, ask yourself, ”Is the standby database the right choice?“  
The main disadvantages are:
1. Lack of scalability
2. Cost
3. UNRECOVERABLE or NOLOGGING operations are not propagated
4. Data loss due to loss of online redo logs, if geo-mirroring is not leveraged
So standby database is not a panacea for all outages, usually it’s just one critical part of a full recovery plan, Although the standby database can be leveraged as a failover solution, OPS and hardware failover solutions are more appropriate. In many cases I had been through, OPS and Oracle Standby Database together provide a greater recovery plan. Also I know 90%+ of OPS not properly be implemented, as a DBA you should go pickup the skill and take this responsibility, your boss may not tell the different between  ‘standby database’ and OPS, that why you are the DBA!
After next 5 years many company’s database size will be beyond 50TB in US, and your company database also will get here eventually, do you still think use signal machine to run this kind of database?  Yes, you can tune 7, 8, 8i., 9i databases today, if 10i (under developing right now!) pushed out  it will auto diagnostic and tune the system problems. Think about this how much the knowledge you know today will still work after 5 years.

半瓶醋

i have a document about auto-standby database.
you can ask me if you have any question;

半瓶醋

Oracle 高级复制实在是是太麻烦了，还要去做什么冲突解决方案，我觉得太难用了。如果只是做备份与容灾，用高级复制好象有点得不偿失，但是在分布式数据库应用中，倒是可以考虑用复制技术来实现少量数据的同步，并且现在网络的速度可能会越来越快。

半瓶醋

其实我个人认为STANDBY　ＤＡＴＡＢＡＳＥ的架设倒不是太难，对于它运转后的维护才是我们大家都应该更关心的问题，
如primary database structure change
redo logfile的传送出问题，可能会有sequence# gaq 出现，
文件传方面军对数据库资源的占用等，这才是我们要更多关心的问题，要不你只有重建你的Ｓtandby database 了

allanliao

只有一台机器，WIN2000 SERVER的，能否练习STANDBY DB？

如果我装RED HAT LINUX呢？

allanliao

是否随便设置，只要少于9个ALPHANUMERIC字符即可？

allanliao

为什么不能归档到STANDBY的目的地？

各位请看：

OS环境：WIN2000 SERVER
ORACLE版本：8.1.6.3.0
PRIMARY DATABASE — STANDBY DATABASE在同一机器上，其它情况正常，
但归档时就是不能归档到SERVICE=STANDBY1指定的目的地。
我的归档设置如下：
log_archive_start = true
log_archive_dest_1 = "location=D:\Oracle\oradata\o816nt\archive"
log_archive_dest_2 = "service=STANDBY1"
log_archive_format = %%ORACLE_SID%%T%TS%S.ARC

SQL> alter system switch logfile;

System altered.

SQL> select dest_id,status,error from v$archive_dest;

   DEST_ID STATUS
---------- ---------
ERROR
-----------------------------------------------------------------
         1 VALID


         2 ERROR
ORA-16032: parameter  destination string cannot be translated

         3 INACTIVE


         4 INACTIVE


         5 INACTIVE



16:01:28 SQL>
---------------------------------------------------------------


LISTENER FOR STANDBY1 DATABASE 已正常启动：
D:\Oracle>lsnrctl status standby1

LSNRCTL for 32-bit Windows: Version 8.1.6.3.0 - Production on 10-DEC-2000 16:07:
47

(c) Copyright 1998, 1999, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=allan2000)(PORT=1525))(C
ONNECT_DATA=(SID=standby1)))
STATUS of the LISTENER
------------------------
Alias                     listenerstandby1
Version                   TNSLSNR for 32-bit Windows: Version 8.1.6.3.0 - Produc
tion
Start Date                10-DEC-2000 15:44:52
Uptime                    0 days 0 hr. 22 min. 56 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   D:\Oracle\ora816nt\network\admin\listener.ora
Listener Log File         D:\Oracle\ora816nt\network\log\listenerstandby1.log
Services Summary...
  standby1              has 1 service handler(s)
The command completed successfully

==========================================================================

PRIMARY DATABASE SID：O816NT

15:49:06 SQL> show parameter dest

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
background_dump_dest                 string  D:\Oracle\admin\o816nt\bdump
log_archive_dest                     string
log_archive_dest_1                   string  location=D:\Oracle\oradata\o81
                                             6nt\archive
log_archive_dest_2                   string  service=STANDBY1
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_state_1             string  enable
log_archive_dest_state_2             string  enable
log_archive_dest_state_3             string  enable
log_archive_dest_state_4             string  enable
log_archive_dest_state_5             string  enable
log_archive_duplex_dest              string
log_archive_min_succeed_dest         integer 1
standby_archive_dest                 string  %ORACLE_HOME%\RDBMS
user_dump_dest                       string  D:\Oracle\admin\o816nt\udump
15:49:30 SQL>



=================================================================================
STANDBY DATABASE SID：STANDBY1

15:48:27 SQL>   SHOW PARAMETER DEST

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
background_dump_dest                 string  D:\Oracle\admin\standby1\bdump
log_archive_dest                     string
log_archive_dest_1                   string  location=H:\oradata\standby1\a
                                             rchive
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_state_1             string  enable
log_archive_dest_state_2             string  enable
log_archive_dest_state_3             string  enable
log_archive_dest_state_4             string  enable
log_archive_dest_state_5             string  enable
log_archive_duplex_dest              string
log_archive_min_succeed_dest         integer 1
standby_archive_dest                 string  location=H:\oradata\standby1\a
                                             rchive
user_dump_dest                       string  D:\Oracle\admin\standby1\udump
15:56:20 SQL>

allanliao

原因是下面的参数设置失误：
standby_archive_dest = “location=H:\oradata\standby1\archive”
改成standby_archive_dest = “H:\oradata\standby1\archive”就OK。
谢谢诸位。

allanliao

一、        概要步骤
有了以下的INIT。ORA文件，再按照这里的步骤执行就简单多了。
Task Procedure
1 Either make a new backup of the primary database datafiles or access an old backup.
Creating the Standby Datafiles
2 Connect to the primary database and create the standby control file.
Creating the Standby Control File
3 Copy the backup datafiles and control filefrom the primary site to the standby site.
Transferring Files to the Standby Site
4 Create a service name for the standby database.
Configuring the tnsnames.ora File on the Primary Site
5 Configure the listener on the standby site so that it can receive the archived redo logs from the primary site.
Configuring the listener.ora File on the Standby Site
6 Set the initialization parameters for the primary database.
Configuring the Primary Database Initialization Parameter File
7 Create the standby initialization parameter file and set the initialization parameters for the standby database. Depending on your configuration, you may need to set filename conversion parameters.
Configuring the Standby Database Initialization Parameter File
8 Start the standby instance and mount the standby database.
Starting the Standby Instance
9 Manually change the names of the primary datafiles and redo logs in the standby control file for all files not automatically renamed using DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT in step 7. If step 7 renamed all files, skip this step.
Manually Renaming Standby Files Not Captured by Conversion Parameters
10 Manually enable initialization parameter changes on the primary database so that it can initiate archival to the standby site.
Enabling Changes to the Initialization Parameter Settings

二、        实验环境：
WIN2000 SERVER，NO PACK；
ORACLE 81630；
PRIMARY DATABASE：O816NT
STANDBY DATABASE：STANDBY1

三、        PRIMARY DATABASE的参数：
log_archive_start = true
log_archive_dest_1 = "location=D:\Oracle\oradata\o816nt\archive"
log_archive_dest_2 = "service=STANDBY1 mandatory"
log_archive_dest_state_2 = ENABLE
log_archive_format = O816NTT%TS%S.ARC
db_name = "o816nt"
instance_name = o816nt
service_names = o816nt
db_files = 256
control_files = ("D:\Oracle\oradata\o816nt\control01.ctl",
"D:\Oracle\oradata\o816nt\control02.ctl", "D:\Oracle\oradata\o816nt\control03.ctl"
open_cursors = 100
max_enabled_roles = 30
db_file_multiblock_read_count = 8
db_block_buffers = 1024
shared_pool_size = 41943040
large_pool_size = 614400
java_pool_size = 20971520
log_checkpoint_interval = 100000
log_checkpoint_timeout = 1800
processes = 50
parallel_max_servers = 5
log_buffer = 32768
max_dump_file_size = 10240  # limit trace file size to 5M each
oracle_trace_collection_name = ""
background_dump_dest = D:\Oracle\admin\o816nt\bdump
user_dump_dest = D:\Oracle\admin\o816nt\udump
db_block_size = 8192
remote_login_passwordfile = exclusive
os_authent_prefix = ""
job_queue_processes = 4
job_queue_interval = 60
open_links = 4
distributed_transactions = 10
compatible = 8.1.0
sort_area_size = 655360
sort_area_retained_size = 65536
UTL_FILE_DIR = D:\ORACLE\O816NT\RDBMS\ADMIN

四、        STANDBY DATABASE的参数：
control_files = ("H:\oradata\standby1\CONTROL01.CTL"
lock_name_space = lckname
db_file_name_convert = "D:\ORACLE\ORADATA\O816NT","H:\ORADATA\STANDBY1"
log_file_name_convert="D:\ORACLE\ORADATA\O816NT","H:\ORADATA\STANDBY1"

STANDBY_ARCHIVE_DEST = "H:\oradata\standby1\archive"

log_archive_start = true
log_archive_dest = "H:\oradata\standby1\archive"
log_archive_format = O816NTT%TS%S.ARC

db_name = "o816nt"

# instance_name = o816nt
instance_name = standby1

# service_names = o816nt
service_names = standby1

db_files = 256
open_cursors = 100
max_enabled_roles = 30
db_file_multiblock_read_count = 8
db_block_buffers = 1024
shared_pool_size = 20971520
large_pool_size = 614400
log_checkpoint_interval = 100000
log_checkpoint_timeout = 1800
processes = 50
parallel_max_servers = 5
log_buffer = 32768
max_dump_file_size = 10240  # limit trace file size to 5M each
# global_names = true

oracle_trace_collection_name = ""
# define directories to store trace and alert files
background_dump_dest = D:\Oracle\admin\standby1\bdump
#Uncomment this parameter to enable resource management for your database.
user_dump_dest = D:\Oracle\admin\standby1\udump
db_block_size = 8192
remote_login_passwordfile = exclusive
os_authent_prefix = ""
job_queue_processes = 4
job_queue_interval = 60
open_links = 4
distributed_transactions = 10
compatible = 8.1.0
sort_area_size = 655360
sort_area_retained_size = 65536
UTL_FILE_DIR = D:\ORACLE\O816NT\RDBMS\ADMIN

五、        相关NT服务：
1、        OracleServiceO816NT、OracleOra816ntTNSListener 为PRIMARY DATABASE O816NT的相关服务；
2、        OracleServicestandby1、OracleOra816ntTNSListenerlistenerstandby1为新的STANDBY DATABASE STANDBY1的相关服务；其中LISTENERSTANDBY1的服务通过配置LISTENER.ORA、然后运行LSNRCTL START LISTENERSTANDBY1命令得到；STANDBY1服务通过ORADIM –NEW –SID STANDBY1 –INTPWD ORACLE –STARTMODE M创建；

六、        启停脚本：
1、        先启动STANDBY数据库（否则要手工ENABLE  STANDBY归档目的地）：
REM  STARTSTANDBY1.BAT
set ORACLE_SID=standby1
oradim -startup -sid standby1
lsnrctl start listenerstandby1
sqlplus /nolog @d:\oracle\startstandby1

REM  STARTSTANDBY1.SQL
connect internal/oracle@standby1
startup nomount pfile=d:\oracle\admin\standby1\pfile\init.ora
alter database mount standby database;
exit
2、        后启动PRIMARY DATABASE：
REM  STARTO816NT.BAT
set ORACLE_SID=o816nt
oradim -startup -sid o816nt
lsnrctl start
REM  namesctl start
sqlplus /nolog @d:\oracle\starto816nt

REM  STARTO816NT.SQL
connect internal/oracle@o816nt
startup pfile=d:\oracle\admin\o816nt\pfile\init.ora
exit
3、        进入MANAGED STANDBY DATABASE模式：
SQL》 RECOVER MANAGED STANDBY DATABASE;

test34567

我想生成一个 Reporting Database，用 standby 是否合适？