1数据库归档模式
srvctl stop database -d fyl -o immediate
SQL> alter database archivelog;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA1/FYL/ARCH1' SID='fyl1';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA1/FYL/ARCH2' SID='fyl2';
SQL> alter database force logging;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 8 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;
2初始化参数ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(fyl,fyl_std)' SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA1/FYL/ARCH1 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=fyl' SID='fyl1';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DATA1/FYL/ARCH2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=fyl' SID='fyl2';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=fyl_std LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=fyl_std' SID='*';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
--ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;
ALTER SYSTEM SET FAL_SERVER=fyl_std;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u02/app/oracle/oradata/fyl_std','+DATA1/fyl/datafile','/u02/app/oracle/oradata/fyl_std','+DATA1/fyl/tempfile' scope=spfile SID='*';
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u02/app/oracle/oradata/fyl_std','+DATA1/fyl/onlinelog','/u02/app/oracle/oradata/fyl_std','+DATA1/fyl/onlinelog' scope=spfile SID='*';
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
3备份数据库
[oracle@db1 ~]$ more rman.sh
. /home/oracle/.bash_profile
rman target / <<EOF
run
{
allocate channel c1 type disk maxpiecesize=500M;
allocate channel c2 type disk maxpiecesize=500M;
backup incremental level 0 database
format '/u02/rman_fyl1/fyl_%p_%s_%u.dbf';
sql 'alter system archive log current';
backup archivelog all
format '/u02/rman_fyl1/fyl_%p_%s_%u.arc';
backup current controlfile for standby
format '/u02/rman_fyl1/fyl_%p_%s.ctl';
}
exit;
EOF
[oracle@db1 ~]$ nohup sh rman.sh > 1.log &
[oracle@db1 rman_fyl1]$ scp * 192.168.6.99:/u02/rman
oracle@192.168.6.99's password:
fyl_1_1_01qak654.dbf 100% 273MB 10.1MB/s 00:27
fyl_1_2_02qak655.dbf 100% 333MB 9.0MB/s 00:37
fyl_1_3_03qak67k.dbf 100% 18MB 17.7MB/s 00:01
fyl_1_4_04qak67k.dbf 100% 96KB 96.0KB/s 00:00
fyl_1_5_05qak67u.arc 100% 11MB 10.5MB/s 00:01
fyl_1_6_06qak67u.arc 100% 1295KB 1.3MB/s 00:00
fyl_1_7_07qak680.arc 100% 2512KB 2.5MB/s 00:00
fyl_1_8.ctl 100% 18MB 17.7MB/s 00:01
传送密码文件--不然会报错
[oracle@db1 dbs]$ scp orapwfyl1 192.168.6.99:/u02/app/oracle/product/11.2.0/db_2/dbs/orapwfyl_std
oracle@192.168.6.99's password:
orapwfyl1 100% 1536 1.5KB/s 00:00
alter database create standby controlfile as '/home/oracle/control01.ctl';
4修改备库参数文件create pfile='/home/oracle/initfyl_std.ora' from spfile
[oracle@db1 ~]$ more initfyl_std.ora
*.audit_file_dest='/u01/app/oracle/admin/fyl_std/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/app/oracle/oradata/fyl_std/control01.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='+DATA1/fyl/datafile','/u02/app/oracle/oradata/fyl_std','+DATA1/fyl/tempfile','/u02/app/oracle/oradata/fyl_std'
*.db_unique_name='FYL_STD'
*.db_name='fyl'
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='FYL'
*.java_pool_size=0
*.log_archive_config='DG_CONFIG=(fyl,fyl_std)'
*.log_archive_dest_1='LOCATION=/u01/oracle/arch_fyl_std VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=fyl_std'
*.log_archive_dest_2='SERVICE=fyl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=fyl'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='+DATA1/fyl/onlinelog','/u02/app/oracle/oradata/fyl_std','+DATA1/fyl/onlinelog','/u02/app/oracle/oradata/fyl_std'
*.memory_target=524288000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
5恢复数据库
RMAN> startup nomount;
RMAN> restore standby controlfile from '/u02/rman/fyl_1_8.ctl';
RMAN> alter database mount;
RMAN> catalog start with '/u02/rman';
RMAN> run {
2> allocate channel c1 type disk ;
3> allocate channel c2 type disk ;
4> restore database;
5> recover database;
6> }
6配置主备TNS
FYL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.31)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.32)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fyl)
)
)
FYL_STD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.99)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = fyl_std)
)
)
7验证
备库应用日志
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNCET FROM SESSION;
主库
ALTER TABLESPACE USERS ADD DATAFILES ' +DATA1/fyl/datafile/users02.dbf' SIZE 5M AUTOEXTEND ON;
备库查看
SQL> select FILE_ID,FILE_NAME,BYTES/1024/1024/1024 GB,TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files;
FILE_ID FILE_NAME GB TABLESPACE_NAME AUT
---------- ---------------------------------------------------------------------- ---------- ------------------------------ ---
1 /u02/app/oracle/oradata/fyl_std/system.266.883172679 .68359375 SYSTEM YES
2 /u02/app/oracle/oradata/fyl_std/sysaux.265.883172755 .5859375 SYSAUX YES
3 /u02/app/oracle/oradata/fyl_std/undotbs1.258.883172809 .209960938 UNDOTBS1 YES
4 /u02/app/oracle/oradata/fyl_std/undotbs2.264.883172903 .1953125 UNDOTBS2 YES
5 /u02/app/oracle/oradata/fyl_std/users.263.883172925 .004882813 USERS YES
6 /u02/app/oracle/oradata/fyl_std/users02.dbf .004882813 USERS YES
6 rows selected.
8过程错误错误一
SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
SQL> select controlfile_type from v$database;
CONTROL
-------
BACKUP
原来使用
restore controlfile from '/u02/rman/fyl_1_8.ctl';
解决:使用如下命令
restore standby controlfile from '/u02/rman/fyl_1_8.ctl';
错误二
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
解决:copy密码文件
9备忘脚本
export ORACLE_SID=fyl_std
rman target / <<EOF
alter database nomount;
restore standby controlfile from '/u02/rman/fyl_1_8.ctl';
alter database mount;
catalog start with '/u02/rman' noprompt;
crosscheck backup;
run {
allocate channel c1 type disk ;
allocate channel c2 type disk ;
restore database;
recover database;
}
exit;
EOF
使用了db_file_name_convert log_file_name_convert参数可以省略下面信息
set newname for datafile 4 to '/oracle/oradata/prod/users01.dbf';
set newname for datafile 5 to '/oracle/oradata/prod/undotbs02.dbf';
set newname for datafile 3 to '/oracle/oradata/prod/undotbs01.dbf';
set newname for datafile 1 to '/oracle/oradata/prod/system01.dbf';
restore database;
switch datafile all;
switch tempfile all;
release channel c1;
release channel c2;