Begin at AM 8:30
End at PM 7:00
Database Name :- demo
Primary db_unique_name :- demo
standby db_unique_name :- racdbbak
########################################################standby asm作为存储,单节点dg#################################################
****主库:****
确保数据库是 archivelog 模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch/demo
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
开启forcing logging;
ALTER DATABASE FORCE LOGGING;
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
FORCE LOGGING参考--http://book.51cto.com/art/200912/170703.htm
如果没有密码文件,则创建密码文件
参考
http://blog.csdn.net/tianlesoftware/article/details/7383110
http://blog.sina.com.cn/s/blog_544f18310100ylrm.html
[oracle@oracletest ~]$ cd $ORACLE_HOME/dbs
[oracle@oracletest dbs]$ rm -rf orapwdemo
[oracle@oracletest dbs]$ orapwd file=orapwdemo password=oracle entries=5
[oracle@oracletest dbs]$ ls
hc_DBUA0.dat hc_demo.dat init.ora lkDEMO orapwdemo snapcf_demo.f spfiledemo.ora
创建备用 redo 日志
SQL> col file_name for a50
SQL> set pagesize 1000 linesize 400
SQL> select lf.group#,member as file_name,bytes/1024/1024 as size_MB from v$logfile lf,v$log l where lf.group#=l.group# order by group#;
GROUP# FILE_NAME SIZE_MB
---------- -------------------------------------------------- ----------
1 /u01/app/oracle/oradata/demo/redo01.log 50
2 /u01/app/oracle/oradata/demo/redo02.log 50
3 /u01/app/oracle/oradata/demo/redo03.log 50
注意:创建的standby redo大小需要和主库的redo大小保持一致
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/demo/standby04.log') size 50m;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/demo/standby05.log') size 50m;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/demo/standby06.log') size 50m;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/demo/standby07.log') size 50m;
SQL> col member for a50
SQL> select type,member from v$logfile;
TYPE MEMBER
------- --------------------------------------------------
ONLINE /u01/app/oracle/oradata/demo/redo03.log
ONLINE /u01/app/oracle/oradata/demo/redo02.log
ONLINE /u01/app/oracle/oradata/demo/redo01.log
STANDBY /u01/app/oracle/oradata/demo/standby04.log
STANDBY /u01/app/oracle/oradata/demo/standby05.log
STANDBY /u01/app/oracle/oradata/demo/standby06.log
STANDBY /u01/app/oracle/oradata/demo/standby07.log
修改主要初始化参数,使其适用于主数据库的 dataguard
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(demo,racdbbak)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arch/demo/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=demo';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=racdbbak LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdbbak';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set FAL_SERVER=racdbbak;
alter system set FAL_CLIENT=demo;
alter system set DB_FILE_NAME_CONVERT='+ODATA/racdbbak/datafile/','/u01/app/oracle/oradata/demo/' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='+ODATA/racdbbak/ONLINELOG/','/u01/app/oracle/oradata/demo/' scope=spfile;
修改spfile参数之前做一次spfile导出pfile 备份
SQL> create pfile='/home/oracle/demoinit.ora' from spfile;
SQL> ! ls /home/oracle
demoinit.ora demo.ora
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(demo,racdbbak)';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arch/demo/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=demo';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=racdbbak LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdbbak';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.
SQL> alter system set FAL_SERVER=racdbbak;
System altered.
SQL> alter system set FAL_CLIENT=demo;
System altered.
SQL> alter system set DB_FILE_NAME_CONVERT='+ODATA/racdbbak/datafile/','/u01/app/oracle/oradata/demo/' scope=spfile;
System altered.
SQL> alter system set LOG_FILE_NAME_CONVERT='+ODATA/racdbbak/ONLINELOG/','/u01/app/oracle/oradata/demo/' scope=spfile;
System altered.
******修改主库和备库的listener.ora 和 tnsname.ora******
主库tnsname.ora
[oracle@oracletest admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DEMO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.88)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = demo)
)
)
RACDBBAK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.81)(PORT = 1551))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdbbak)
)
)
备库listener.ora
使用oracle用户在本地netca创建监听LISTENERNEW1 端口1551,使用netmgr在LISTENERNEW1增加racdbbak的静态注册
[oracle@node1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENERNEW1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1551))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1551))
)
)
ADR_BASE_LISTENERNEW1 = /u01/app/oracle
SID_LIST_LISTENERNEW1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = racdbbak)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = racdbbak)
)
)
备库tnsname.ora
添加完静态注册之后,在tnsname.ora中添加racdbbak的监听信息
[oracle@node1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDBBAK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.81)(PORT = 1551))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdbbak)
)
)
DEMO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.88)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = demo)
)
)
在主库上sys/oracle as sysdba登录
[oracle@oracletest admin]$ sqlplus sys/oracle@racdbbak as sysdba
****备用数据库****
gird用户登录
创建目录
ASMCMD> mkdir RACDBBAK
cd RACDBBAK
mkdir CONTROLFILE
mkdir DATAFILE
mkdir ONLINELOG
mkdir PARAMETERFILE
mkdir TEMPFILE
归档路径
ASMCMD> cd OARCH
ASMCMD> ls
DEMO/
RACDB/
RACDBBAK/
ASMCMD> pwd
+OARCH/RACDBBAK/ARCHIVELOG
oracle用户下
创建 /u01/app/oracle/diag/rdbms/racdbbak下对应目录 (复制别的数据库过来,改一个名字)
创建 /u01/app/oracle/admin/racdbbak/adump
从主数据库 $ORACLE_HOME/dbs 中复制密码文件,并将其重命名为备用数据库名称。
主库上复制
[oracle@oracletest dbs]$ scp orapwdemo 192.168.80.81:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
备库上修改name
[oracle@node1 dbs]$ mv orapwdemo orapwracdbbak
[oracle@node1 dbs]$ ll
total 28
-rw-rw---- 1 oracle oinstall 1544 Jun 14 06:04 hc_DBUA0.dat
-rw-rw---- 1 oracle asmadmin 1544 Jun 14 06:08 hc_racdb1.dat
-rw-rw---- 1 oracle asmadmin 1544 Jul 22 19:52 hc_racdbbak.dat
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r--r-- 1 oracle oinstall 50 Jul 1 19:12 initracdb1.ora
-rw-r----- 1 oracle oinstall 1536 Jul 1 17:40 orapwracdb1
-rw-r----- 1 oracle oinstall 2048 Jul 23 11:31 orapwracdbbak
建立备库临时初始化参数文件
设置备库ORACLE_SID环境变量,启动racdbbak到nomount状态
cat /home/oracle/initracdbbak.ora
*.db_create_file_dest='+ODATA'
*.db_name='demo'
*.db_unique_name='racdbbak'
*.diagnostic_dest='/u01/app/oracle'
*.open_cursors=300
*.processes=150
*.db_block_size=8192
SQL> startup nomount pfile='/home/oracle/initracdbbak.ora';
****到主库上进行dupliate****
保证数据库是open的
oracle @ node1:
rman target / auxiliary sys/oracle@racdbbak
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'demo','racdbbak'
set db_unique_name='racdbbak'
set db_file_name_convert='/u01/app/oracle/oradata/demo/','+ODATA/racdbbak/datafile/'
set log_file_name_convert='/u01/app/oracle/oradata/demo/','+ODATA/racdbbak/ONLINELOG/'
set control_files='+ODATA/RACDBBAK/CONTROLFILE/control01.ctl'
set log_archive_max_processes='5'
set fal_client='racdbbak'
set fal_server='demo'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(demo,racdbbak)'
set log_archive_dest_1 = 'LOCATION=+OARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdbbak'
set log_archive_dest_2='service=demo ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=demo'
;
}
在备库上打开realtime-apply
SQL> alter database recover managed standby database using current logfile disconnect from session;
############################################################################################################################################################
####################################################standby 使用文件系统的dg###############################################################################
*****主库上操作**********
确保数据库是 archivelog 模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch/demo
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
开启forcing logging;
ALTER DATABASE FORCE LOGGING;
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
如果没有密码文件,则创建密码文件
http://blog.sina.com.cn/s/blog_544f18310100ylrm.html
[oracle@oracletest ~]$ cd $ORACLE_HOME/dbs
[oracle@oracletest dbs]$ rm -rf orapwdemo
[oracle@oracletest dbs]$ orapwd file=orapwdemo password=oracle entries=5
创建备用 redo 日志
SQL> col file_name for a50
SQL> set pagesize 1000 linesize 400
SQL> select lf.group#,member as file_name,bytes/1024/1024 as size_MB from v$logfile lf,v$log l where lf.group#=l.group# order by group#;
GROUP# FILE_NAME SIZE_MB
---------- -------------------------------------------------- ----------
1 /u01/app/oracle/oradata/demo/redo01.log 50
2 /u01/app/oracle/oradata/demo/redo02.log 50
3 /u01/app/oracle/oradata/demo/redo03.log 50
注意:创建的standby redo大小需要和主库的redo大小保持一致
SQL> col member for a50
SQL> select type,member from v$logfile;
alter database add standby logfile group 4 ('/u01/app/oracle/oradata/demo/standby04.log') size 50m;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/demo/standby05.log') size 50m;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/demo/standby06.log') size 50m;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/demo/standby07.log') size 50m;
修改主要初始化参数,使其适用于主数据库的 dataguard
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(demo,racdbbak)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arch/demo/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=demo';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=racdbbak LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdbbak';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set FAL_SERVER=racdbbak;
alter system set FAL_CLIENT=demo;
alter system set DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/racdbbak/','/u01/app/oracle/oradata/demo/' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/racdbbak/','/u01/app/oracle/oradata/demo/' scope=spfile;
修改spfile参数之前做一次spfile导出pfile 备份
SQL> create pfile='/home/oracle/demoinit.ora' from spfile;
SQL> ! ls /home/oracle
demoinit.ora demo.ora
******修改主库和备库的listener.ora 和 tnsname.ora******
主库tnsname.ora
[oracle@oracletest admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DEMO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.88)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = demo)
)
)
RACDBBAK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.81)(PORT = 1551))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdbbak)
)
)
备库listener.ora
使用oracle用户在本地netca创建监听LISTENERNEW1 端口1551,使用netmgr在LISTENERNEW1增加racdbbak的静态注册
[oracle@node1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENERNEW1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1551))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1551))
)
)
ADR_BASE_LISTENERNEW1 = /u01/app/oracle
SID_LIST_LISTENERNEW1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = racdbbak)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = racdbbak)
)
)
备库tnsname.ora
添加完静态注册之后,在tnsname.ora中添加racdbbak的监听信息
[oracle@node1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDBBAK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.81)(PORT = 1551))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdbbak)
)
)
DEMO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.88)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = demo)
)
)
在主库上sys/oracle as sysdba登录
[oracle@oracletest admin]$ sqlplus sys/oracle@racdbbak as sysdba
****备用数据库****
创建需要的目录
oracle用户操作:
mkdir -p /u01/app/oracle/oradata/racdbbak
mkdir -p /u01/app/oracle/diag/rdbms/racdbbak/racdbbak 【创建本目录下的trace\alter等文件--建议从别的地方 直接复制过来】
mkdir -p /u01/app/oracle/arch/racdbbak
mkdir -p /u01/app/oracle/admin/racdbbak/adump
从主数据库 $ORACLE_HOME/dbs 中复制密码文件,并将其重命名为备用数据库名称。
主库上复制
[oracle@oracletest dbs]$ scp orapwdemo 192.168.80.81:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
备库上修改name
[oracle@node1 dbs]$ mv orapwdemo orapwracdbbak
建立备库临时初始化参数文件
设置备库ORACLE_SID环境变量,启动racdbbak到nomount状态
cat /home/oracle/initracdbbak.ora
*.db_name='demo'
*.db_unique_name='racdbbak'
*.diagnostic_dest='/u01/app/oracle'
*.open_cursors=300
*.processes=150
*.db_block_size=8192
SQL> startup nomount pfile='/home/oracle/initracdbbak.ora';
****到主库上进行dupliate****
保证数据库是open的
oracle @ node1:
rman target / auxiliary sys/oracle@racdbbak
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'demo','racdbbak'
set db_unique_name='racdbbak'
set db_file_name_convert='/u01/app/oracle/oradata/demo/','/u01/app/oracle/oradata/racdbbak/'
set log_file_name_convert='/u01/app/oracle/oradata/demo/','/u01/app/oracle/oradata/racdbbak/'
set control_files='/u01/app/oracle/oradata/racdbbak/control01.ctl'
set log_archive_max_processes='5'
set fal_client='racdbbak'
set fal_server='demo'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(demo,racdbbak)'
set log_archive_dest_1 = 'LOCATION=/u01/app/oracle/arch/racdbbak VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdbbak'
set log_archive_dest_2='service=demo ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=demo'
;
}
在备库上打开realtime-apply
SQL> alter database recover managed standby database using current logfile disconnect from session;
###########################################################################################################################################################
################################################dg 文件系统到rac ##################################################################
*****主库上操作**********
确保数据库是 archivelog 模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch/demo
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
开启forcing logging;
ALTER DATABASE FORCE LOGGING;
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
如果没有密码文件,则创建密码文件
[oracle@oracletest ~]$ cd $ORACLE_HOME/dbs
[oracle@oracletest dbs]$ rm -rf orapwdemo
[oracle@oracletest dbs]$ orapwd file=orapwdemo password=oracle entries=5
创建备用 redo 日志
SQL> col file_name for a50
SQL> set pagesize 1000 linesize 400
SQL> select lf.group#,member as file_name,bytes/1024/1024 as size_MB from v$logfile lf,v$log l where lf.group#=l.group# order by group#;
GROUP# FILE_NAME SIZE_MB
---------- -------------------------------------------------- ----------
1 /u01/app/oracle/oradata/demo/redo01.log 50
2 /u01/app/oracle/oradata/demo/redo02.log 50
3 /u01/app/oracle/oradata/demo/redo03.log 50
创建undotbs2
SQL> select bytes/(1024*1024) MB,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';
SQL>
注意:创建的standby redo大小需要和主库的redo大小保持一致
SQL> col member for a50
SQL> select type,member from v$logfile;
create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/demo/undotbs02.dbf' size 110M autoextend on maxsize 10G;
alter database add logfile thread 2 group 4 '/u01/app/oracle/oradata/demo/redo04.log' size 50M;
alter database add logfile thread 2 group 5 '/u01/app/oracle/oradata/demo/redo05.log' size 50M;
alter database add logfile thread 2 group 6 '/u01/app/oracle/oradata/demo/redo06.log' size 50M;
alter database add standby logfile thread 1 group 7 ('/u01/app/oracle/oradata/demo/standby07.log') size 50m;
alter database add standby logfile thread 1 group 8 ('/u01/app/oracle/oradata/demo/standby08.log') size 50m;
alter database add standby logfile thread 1 group 9 ('/u01/app/oracle/oradata/demo/standby09.log') size 50m;
alter database add standby logfile thread 1 group 10 ('/u01/app/oracle/oradata/demo/standby10.log') size 50m;
alter database add standby logfile thread 2 group 11 ('/u01/app/oracle/oradata/demo/standby11.log') size 50m;
alter database add standby logfile thread 2 group 12 ('/u01/app/oracle/oradata/demo/standby12.log') size 50m;
alter database add standby logfile thread 2 group 13 ('/u01/app/oracle/oradata/demo/standby13.log') size 50m;
alter database add standby logfile thread 2 group 14 ('/u01/app/oracle/oradata/demo/standby14.log') size 50m;
修改主要初始化参数,使其适用于主数据库的 dataguard
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(demo,demobak)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/arch/demo/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=demo';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=demobak LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=demobak';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set FAL_SERVER=demobak;
alter system set FAL_CLIENT=demo;
alter system set standby_file_management=auto;
alter system set DB_FILE_NAME_CONVERT='+ODATA/DEMOBAK/DATAFILE/','/u01/app/oracle/oradata/demo/' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='+ODATA/DEMOBAK/ONLINELOG/','/u01/app/oracle/oradata/demo/' scope=spfile;
【
创建demobak在ASM上的一下目录
grid:
asmcmd
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> pwd
+ODATA/DEMOBAK
】
修改spfile参数之前做一次spfile导出pfile 备份
SQL> create pfile='/home/oracle/demoinit.ora' from spfile;
SQL> ! ls /home/oracle
demoinit.ora demo.ora
在standby库上的操作
传递密码文件到两个节点
[oracle@oracletest dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@oracletest dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@oracletest dbs]$ scp orapwdemo 192.168.80.81:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
The authenticity of host '192.168.80.81 (192.168.80.81)' can't be established.
RSA key fingerprint is d7:01:48:55:32:43:49:32:f8:c6:4b:36:2c:f5:29:9b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.80.81' (RSA) to the list of known hosts.
oracle@192.168.80.81's password:
orapwdemo 100% 1536 1.5KB/s 00:00
[oracle@oracletest dbs]$ scp orapwdemo 192.168.80.86:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
The authenticity of host '192.168.80.86 (192.168.80.86)' can't be established.
RSA key fingerprint is 7d:29:a5:a7:58:e0:33:e8:16:f6:06:d5:e7:c4:88:e0.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.80.86' (RSA) to the list of known hosts.
oracle@192.168.80.86's password:
orapwdemo
node1上:修改orapwdemo 为 orapwdemobak1
node1上:修改orapwdemo 为 orapwdemobak2
在两个节点上创建audit_dest
[oracle@node1 adump]$ pwd
/u01/app/oracle/admin/demobak/adump
[oracle@node2 adump]$ pwd
/u01/app/oracle/admin/demobak/adump
配置主库和备库的监听
在standby(节点1)\主库上,同样配置tnsnames.ora文件
DEMO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.88)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = demo)
)
)
DEMOBAK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.80.81)(PORT = 1551))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = demobak1)
(SERVICE_NAME = demobak)
)
)
在备库standby 节点上 创建静态监听 新的建立 LISTENERNEW1 1551
netca
LISTENERNEW1 1551
netmgr
demobak demobak1
vim tnsname.ora
lsnrctl stop listenernew1
lsnrctl start listenernew1
在备库上使用sys/oracle登录测试
[oracle@oracletest dbs]$ sqlplus sys/oracle@demobak as sysdba 成功
*.db_name='demo'
*.db_unique_name='demobak'
*.diagnostic_dest='/u01/app/oracle'
*.open_cursors=300
*.processes=150
*.db_block_size=8192
****到主库上进行dupliate****
保证数据库是open的
oracle @ node1:
rman target / auxiliary sys/oracle@demobak
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'demo','demobak'
set db_unique_name='demobak'
set db_file_name_convert='/u01/app/oracle/oradata/demo/','+ODATA/DEMOBAK/DATAFILE/'
set log_file_name_convert='/u01/app/oracle/oradata/demo/','+ODATA/DEMOBAK/ONLINELOG/'
set control_files='+ODATA/DEMOBAK/CONTROLFILE/control01.ctl'
set log_archive_max_processes='10'
set fal_client='demobak'
set fal_server='demo'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(demo,demobbak)'
set log_archive_dest_1 = 'LOCATION=+OARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=demobak'
set log_archive_dest_2='service=demo ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=demo'
;
}
SQL> alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database cancel;
shutdown immediate;
生成临时的init文件
[oracle@node1 ~]$ cat initnew.ora
demobak1.__db_cache_size=121634816
demobak1.__java_pool_size=4194304
demobak1.__large_pool_size=4194304
demobak1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
demobak1.__pga_aggregate_target=167772160
demobak1.__sga_target=251658240
demobak1.__shared_io_pool_size=0
demobak1.__shared_pool_size=113246208
demobak1.__streams_pool_size=0
demobak2.__db_cache_size=121634816
demobak2.__java_pool_size=4194304
demobak2.__large_pool_size=4194304
demobak2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
demobak2.__pga_aggregate_target=167772160
demobak2.__sga_target=251658240
demobak2.__shared_io_pool_size=0
demobak2.__shared_pool_size=113246208
demobak2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/demobak/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+ODATA/DEMOBAK/CONTROLFILE/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/demo/','+ODATA/DEMOBAK/DATAFILE/'
*.db_name='demo'
*.db_unique_name='demobak'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=demobakXDB)'
*.fal_client='demobak'
*.fal_server='demo'
*.log_archive_config='dg_config=(demo,demobbak)'
*.log_archive_dest_1='LOCATION=+OARCH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=demobak'
*.log_archive_dest_2='service=demo ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=demo'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=10
*.log_file_name_convert='/u01/app/oracle/oradata/demo/','+ODATA/DEMOBAK/ONLINELOG/'
*.memory_target=419430400
*.cluster_database=true
*.cluster_database_instances=2
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
demobak1.undo_tablespace='UNDOTBS1'
demobak2.undo_tablespace='UNDOTBS2'
demobak1.instance_number=1
demobak2.instance_number=2
demobak1.thread=1
demobak2.thread=2
生成spfile:
SQL> create spfile='+ODATA/demobak/PARAMETERFILE/spfiledemobak.ora' from pfile='/home/oracle/initnew.ora';
两个节点创建initdemobak.ora 指向真正的spfile位置
[oracle@node2/1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
cat initracdb2.ora/initracdb1.ora
spfile='+ODATA/demobak/PARAMETERFILE/spfiledemobak.ora'
注册数据库
srvctl add database -d demobak -o $ORACLE_HOME -r physical_standby -s mount
srvctl add instance -d demobak -i demobak1 -n node1
srvctl add instance -d demobak -i demobak2 -n node2?
移除数据库
srvctl remove instance -d demobak -i demobak1
srvctl remove instance -d demobak -i demobak2
srvctl remove database -d demobak
在备库上打开realtime-apply
###########################################################################################################################################################
**********************************************************************************************
select open_mode from v$database;
因为有备重做日志,所以可以加using current logfile 语句,实现实时应用
select sequence#, applied from v$archived_log where applied='YES' order by sequence#;
alter database recover managed standby database cancel;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;
**********************************************************************************************