一、 环境主库:安装grid软件及创建磁盘组;安装数据库软件并创建数据库,
备库:仅安装grid软件并创建asm磁盘组,同时安装数据库软件即可。
主机名
| 数据库版本
| dbname
| ORACLE_SID
| ip地址
| 系统版本
| server1(主)
|
oracle11204
|
Jason
| jason
| 192.168.1.250
|
rhel6.6_x86_64
| server2(备)
| jason
| 192.168.1.252
| 二、 主库配置1. 开启归档SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA Oldest online log sequence 24 Next log sequence to archive 26 Current log sequence 26 2. 配置监听及tnsname grid用户创建监听,如下: [grid@server1 ~]$ cd /u01/app/11.2.0/grid/network/admin/ [grid@server1 admin]$ cat listener.ora # listener.ora Network Configuration File:/u01/app/11.2.0/grid/network/admin/listener.ora # Generated by Oracle configuration tools.
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL =IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL =TCP)(HOST = server1)(PORT = 1521)) ) )
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent oracle用户下tnsname.ora配置如下 [oracle@server1 ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/ [oracle@server1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File:/u01/app/11.2.0/grid/network/admin/tnsnames.ora # Generated by Oracle configuration tools.
JASONPRI = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.250)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = JASON) ) )
JASONSTD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.252)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = JASON) ) )
[oracle@server1 admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-AUG-201622:43:25 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNRfor Linux: Version 11.2.0.4.0 - Production Start Date 12-AUG-2016 17:56:24 Uptime 0 days 4 hr. 47 min. 0 sec Trace Level off Security ON:Local OS Authentication SNMP OFF Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/server1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM",status READY, has 1 handler(s) for this service... Service "JASON" has 1 instance(s). Instance "JASON",status READY, has 1 handler(s) for this service... Service "JASONXDB" has 1 instance(s). Instance "JASON",status READY, has 1 handler(s) for this service... The command completed successfully [oracle@server1 admin]$ 3. 生成pfile文件,同步相应文件至备库在主数据库生成pfile文件。 SQL> create pfile=’/home/oracle/pfile.ora’ from spfile; File created. 同步密码认证文件至备机。 [oracle@jason1 dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@jason1 dbs]$ scp initJASON.ora orapwJASON 192.168.1.252:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/ The authenticity of host '192.168.1.100 (192.168.1.100)' can't beestablished. RSA key fingerprint is 25:ca:65:90:d3:30:fa:68:ed:11:64:b2:0e:b0:39:a7. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.1.100' (RSA) to the list of knownhosts. oracle@192.168.1.252's password: initJASON.ora 100%1415 1.4KB/s 00:00 orapwJASON 100% 1536 1.5KB/s 00:00 [oracle@jason1 dbs] 三、 备库配置4. 创建对应目录备库上创建相关目录 [oracle@server2 oracle]$ mkdir -p /u01/app/oracle/admin/JASON/adump 5. 备库参数文件配置备库上修改初始参数文件,参数如下。 JASON.__db_cache_size=67108864 JASON.__java_pool_size=4194304 JASON.__large_pool_size=8388608 JASON.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment JASON.__pga_aggregate_target=209715200 JASON.__sga_target=201326592 JASON.__shared_io_pool_size=0 JASON.__shared_pool_size=113246208 JASON.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/JASON/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='+DATA' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_name='JASON' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=JASONXDB)' *.log_archive_dest_1='LOCATION=+DATA' *.log_archive_dest_state_1='ENABLE' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=411041792 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1'
6.配置监听 grid用户创建监听,备库监听必须配置为静态监听。如下: [grid@server2 admin]$ cat listener.ora # listener.ora Network Configuration File:/u01/app/11.2.0/grid/network/admin/listener.ora # Generated by Oracle configuration tools.
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = JASON2) (ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = JASON) ) )
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL =IPC)(KEY = EXTPROC1521)) ) (DESCRIPTION = (ADDRESS = (PROTOCOL =TCP)(HOST = server2)(PORT = 1521)) ) )
ADR_BASE_LISTENER = /u01/app/grid ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON oracle用户下tnsname.ora配置如下 [oracle@server1 ~]$ cd/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/ [oracle@server1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File:/u01/app/11.2.0/grid/network/admin/tnsnames.ora # Generated by Oracle configuration tools.
JASONPRI = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.250)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = JASON) ) )
JASONSTD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.252)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = JASON) ) ) 7. 创建备库将备库启动到nomount状态,然后在备机连接主库进行duplicate操作。 [oracle@server2 ~]$ rman target sys/system@JASONPRI auxiliarysys/system@JASONSTD
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Aug 22 03:00:342016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: JASON (DBID=2143845850) connected to auxiliary database: JASON (not mounted)
RMAN> duplicate target database to JASON from active databasenofilenamecheck;
Starting Duplicate Db at 22-AUG-16 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=24 device type=DISK
contents of Memory Script: { sql clone "create spfilefrom memory"; } executing Memory Script
sql statement: create spfile from memory
contents of Memory Script: { shutdown clone immediate; startup clone nomount; } executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started) Oracle instance started
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes Variable Size 318770256 bytes Database Buffers 83886080 bytes Redo Buffers 4284416 bytes
contents of Memory Script: { sql clone "alter systemset control_files = ''+DATA/jason/controlfile/current.256.920517337'' comment= ''Set by RMAN'' scope=spfile"; sql clone "alter systemset db_name = ''JASON'' comment= ''Modified by RMAN duplicate''scope=spfile"; sql clone "alter systemset db_unique_name = ''JASON'' comment= ''Modified by RMAN duplicate''scope=spfile"; shutdown clone immediate; startup clone force nomount backup as copy currentcontrolfile auxiliary format '+DATA/jason/controlfile/current.257.920517339'; sql clone "alter systemset control_files = ''+DATA/jason/controlfile/current.257.920517339'' comment= ''Set by RMAN''scope=spfile"; shutdown clone immediate; startup clone nomount; alter clone database mount; } executing Memory Script
sql statement: alter system set control_files = ''+DATA/jason/controlfile/current.256.920517337'' comment= ''Set byRMAN'' scope=spfile
sql statement: alter system set db_name = ''JASON'' comment=''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''JASON''comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes Variable Size 318770256 bytes Database Buffers 83886080 bytes Redo Buffers 4284416 bytes
Starting backup at 22-AUG-16 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=31 device type=DISK channel ORA_DISK_1: starting datafile copy copying current control file output filename=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_JASON.ftag=TAG20160822T031556 RECID=2 STAMP=920517390 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08 Finished backup at 22-AUG-16
sql statement: alter system set control_files = ''+DATA/jason/controlfile/current.257.920517339'' comment= ''Set byRMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started) Oracle instance started
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes Variable Size 318770256 bytes Database Buffers 83886080 bytes Redo Buffers 4284416 bytes
database mounted
contents of Memory Script: { set newname for clonedatafile 1 to new; set newname for clonedatafile 2 to new; set newname for clonedatafile 3 to new; set newname for clonedatafile 4 to new; backup as copy reuse datafile 1 auxiliary format new datafile 2 auxiliary format new datafile 3 auxiliary format new datafile 4 auxiliary format new ; sql 'alter system archive logcurrent'; } executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 22-AUG-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001name=+DATA/jason/datafile/system.256.919631481 output file name=+DATA/jason/datafile/system.258.920517425tag=TAG20160822T031700 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56 channel ORA_DISK_1: starting datafile copy input datafile file number=00002name=+DATA/jason/datafile/sysaux.257.919631481 output file name=+DATA/jason/datafile/sysaux.259.920517485tag=TAG20160822T031700 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=+DATA/jason/datafile/undotbs1.258.919631481 output file name=+DATA/jason/datafile/undotbs1.260.920517533tag=TAG20160822T031700 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+DATA/jason/datafile/users.259.919631483 output file name=+DATA/jason/datafile/users.261.920517537tag=TAG20160822T031700 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 22-AUG-16
sql statement: alter system archive log current
contents of Memory Script: { backup as copy reuse archivelog like "+DATA/jason/archivelog/2016_08_22/thread_1_seq_29.294.920517565"auxiliary format "+DATA" ; catalog clone start with "+DATA"; switch clone datafile all; } executing Memory Script
Starting backup at 22-AUG-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log copy input archived log thread=1 sequence=29 RECID=41 STAMP=920517576 output file name=+DATA/jason/archivelog/2016_08_22/thread_1_seq_29.262.920517585RECID=0 STAMP=0 channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01 Finished backup at 22-AUG-16
searching for all files that match the pattern +DATA
List of Files Unknown to the Database ===================================== File Name:+data/JASON/ARCHIVELOG/2016_08_22/thread_1_seq_29.262.920517585 File Name: +data/JASON/DATAFILE/SYSTEM.258.920517425 File Name: +data/JASON/DATAFILE/SYSAUX.259.920517485 File Name: +data/JASON/DATAFILE/UNDOTBS1.260.920517533 File Name: +data/JASON/DATAFILE/USERS.261.920517537 File Name: +data/JASON/CONTROLFILE/Current.256.920517337 File Name: +data/ASM/ASMPARAMETERFILE/REGISTRY.253.919634957 cataloging files... cataloging done
List of Cataloged Files ======================= File Name:+data/JASON/ARCHIVELOG/2016_08_22/thread_1_seq_29.262.920517585 File Name: +data/JASON/DATAFILE/SYSTEM.258.920517425 File Name: +data/JASON/DATAFILE/SYSAUX.259.920517485 File Name: +data/JASON/DATAFILE/UNDOTBS1.260.920517533 File Name: +data/JASON/DATAFILE/USERS.261.920517537
List of Files Which Where Not Cataloged ======================================= File Name: +data/JASON/CONTROLFILE/Current.256.920517337 RMAN-07517: Reason: The file headeris corrupted File Name: +data/ASM/ASMPARAMETERFILE/REGISTRY.253.919634957 RMAN-07518: Reason: Foreigndatabase file DBID: 0 Database Name:
datafile 1 switched to datafile copy input datafile copy RECID=6 STAMP=920517585 file name=+DATA/jason/datafile/system.258.920517425 datafile 2 switched to datafile copy input datafile copy RECID=7 STAMP=920517585 filename=+DATA/jason/datafile/sysaux.259.920517485 datafile 3 switched to datafile copy input datafile copy RECID=8 STAMP=920517585 file name=+DATA/jason/datafile/undotbs1.260.920517533 datafile 4 switched to datafile copy input datafile copy RECID=9 STAMP=920517585 filename=+DATA/jason/datafile/users.261.920517537
contents of Memory Script: { set until scn 1100818; recover clone database delete archivelog ; } executing Memory Script
executing command: SET until clause
Starting recover at 22-AUG-16 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=23 device type=DISK
starting media recovery
archived log for thread 1 with sequence 29 is already on disk as file+DATA/jason/archivelog/2016_08_22/thread_1_seq_29.262.920517585 archived log filename=+DATA/jason/archivelog/2016_08_22/thread_1_seq_29.262.920517585 thread=1sequence=29 media recovery complete, elapsed time: 00:00:03 Finished recover at 22-AUG-16 Oracle instance started
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes Variable Size 318770256 bytes Database Buffers 83886080 bytes Redo Buffers 4284416 bytes
contents of Memory Script: { sql clone "alter systemset db_name = ''JASON'' comment= ''Reset to original value byRMAN'' scope=spfile"; sql clone "alter systemreset db_unique_name scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script
sql statement: alter system set db_name = ''JASON'' comment=''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started) Oracle instance started
Total System Global Area 409194496 bytes
Fixed Size 2253744 bytes Variable Size 318770256 bytes Database Buffers 83886080 bytes Redo Buffers 4284416 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "JASON"RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 SIZE 50 M , GROUP 2 SIZE 50 M , GROUP 3 SIZE 50 M DATAFILE '+DATA/jason/datafile/system.258.920517425' CHARACTER SET ZHS16GBK
contents of Memory Script: { set newname for clonetempfile 1 to new; switch clone tempfile all; catalog clone datafilecopy "+DATA/jason/datafile/sysaux.259.920517485", "+DATA/jason/datafile/undotbs1.260.920517533", "+DATA/jason/datafile/users.261.920517537"; switch clone datafile all; } executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
cataloged datafile copy datafile copy file name=+DATA/jason/datafile/sysaux.259.920517485RECID=1 STAMP=920517623 cataloged datafile copy datafile copy file name=+DATA/jason/datafile/undotbs1.260.920517533RECID=2 STAMP=920517623 cataloged datafile copy datafile copy file name=+DATA/jason/datafile/users.261.920517537 RECID=3STAMP=920517623
datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=920517623 filename=+DATA/jason/datafile/sysaux.259.920517485 datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=920517623 filename=+DATA/jason/datafile/undotbs1.260.920517533 datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=920517623 filename=+DATA/jason/datafile/users.261.920517537 Reenabling controlfile options for auxiliary database Executing: alter database force logging
contents of Memory Script: { Alter clone database openresetlogs; } executing Memory Script
database opened Finished Duplicate Db at 22-AUG-16 RMAN> 备注:创建备库后,数据库处于OPEN状态. 8. 创建spfile文件备库创建结束后,需要创建spfile文件。
修改pfile文件control_files参数,指定备库的控制文件,查看asm磁盘组获取控制文件名及位置。如下:
control_files='+DATA/jason/controlfile/current.257.920517339',
创建spfile.
SQL> create SPFILE='+DATA/JASON/spfileJASON.ora' frompfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initJASON.ora';
File created.
SQL>
创建pfile文件指向
[oracle@server2dbs]$ cat initJASON.ora
SPFILE='+DATA/JASON/spfileJASON.ora'
[oracle@server2dbs]$
spfile文件创建成功后,备库重启将使用spfile文件启动数据库
SQL> showparameter spfile
NAME TYPE VALUE
----------------------------------------------- ------------------------------
spfile string +DATA/jason/spfilejason.ora
SQL>
四、 注册CRS注册CRS
备库未注册入CRS,同时备机重启仅ASM实例与crs启动,CRS无法启动数据库。因此注册备库至CRS中。 以oracle用户执行 [oracle@server2 dbs]$ srvctl add database -d JASON -o/u01/app/oracle/product/11.2.0/dbhome_1 -p +DATA/JASON/spfileJASON.ora -i jason -r PHYSICAL_STANDBY -n jason [oracle@server2 dbs]$ srvctl modify database -d JASON -a 'data' [oracle@server2 dbs]$ srvctl config database -d jason -a Database unique name: JASON Database name: jason Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1 Oracle user: oracle Spfile: +DATA/JASON/spfileJASON.ora Domain: Start options: open Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Database instance: jason Disk Groups: DATA Services: Database is enabled [oracle@server2 dbs]$
|