分类目录归档：Data Guard

win平台rman备份和删除dg备库归档日志脚本

发表于 2013 年 10 月 26 日 由 惜分飞

总觉得使用windows跑oracle是不靠谱的事情,可以这个世界上总有很多人喜欢做类似这样的事情,对于数据库比较常见的两件事情:rman和删除dg备库归档日志,在linux/unix平台上使用shell实现很简单,可是跑到win里面,就变的烦了,不是因为其麻烦,而是因为用的人少,不知道怎么下手处理该事情,我编写了简单的实现初级功能的win下面rman备份和删除备库归档日志脚本,供大家参考,也更加欢迎朋友提出来更加好的处理方法(win是真心的不懂)
rman备份脚本

--backup_oracle.bat文件
rman target / cmdfile=D:\backup\rman\backup_db.rman 
log=d:/backup/rman/logfile/rmanlog%date:~0,4%%date:~5,2%%date:~8,2%.log

--backup_db.rman文件
CONFIGURE RETENTION POLICY TO REDUNDANCY = 2;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
backup filesperset = 5 as compressed backupset database format 'd:/backup/rman/full_%U.rman';
sql 'alter system archive log current';
backup  filesperset = 50 as compressed backupset archivelog  all format 'd:/backup/rman/arch_%U.rman'  delete input;
DELETE noprompt OBSOLETE;
crosscheck backup;
delete noprompt expired backup;
backup  format 'd:/backup/rman/ctl_%U.rman' current controlfile;
backup spfile format 'd:/backup/rman/spfile_%U.rman' ;
exit;

backup_oracle.bat文件加入到计划任务即可

删除dg备库归档日志(已经应用)

--delete_dg_archivelog.bat
rem 注意修改 部署目录
cd D:\win_xifenfei
d:
rem 注意delete_archive.sql 查询是否有记录

echo delete archivelog staring > delete_archivelog.bak
sqlplus / as sysdba @delete_archive.sql
echo rman target / cmdfile=rman_checkcross.rman>>delete_archivelog.bat 
delete_archivelog.bat >>delete_dg_archivelog_%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%".log
exit

--delete_archive.sql
set lines 150
col name for a150
set pagesize 0 feedback off verify off heading off echo off
spool delete_archivelog.bat
select 'del '||name from v$archived_log where APPLIED='YES' AND NAME IS NOT NULL and DEST_ID=1;
spool off
exit;

--rman_checkcross.rman
crosscheck archivelog all;
delete noprompt expired archivelog all;
exit

delete_dg_archivelog.bat加入到计划任务即可

发表在 Data Guard | 评论关闭

ORACLE 12C Windows-Linux 部署DATAGURAD

发表于 2013 年 8 月 24 日 由 惜分飞

环境描述
win 64中的ORACLE 12C(primary)与Linux 64中的ORACLE 12C(standby)搭建datagurad

primary force logging

C:\Users\XIFENFEI>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on 星期六 8月 24 16:59:53 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB_CDB$ROOT@SYS> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

CDB_CDB$ROOT@SYS> alter database force logging ;

数据库已更改。

primary rman backup

backup filesperset = 5 as compressed backupset database format 'd:/backup/rman/full_%U.rman';

primary 生成standby controlfile

CDB_CDB$ROOT@SYS> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'c:/control01.ctl';

数据库已更改。

CDB_CDB$ROOT@SYS> create pfile='e:/pfile.txt' from spfile;

文件已创建。

standby 参数文件

DB_CREATE_FILE_DEST='+DATA'
db_create_online_log_dest_1='+DATA'
db_unique_name='cdb_dg'
service_names='cdb'
log_archive_dest_1='LOCATION=/u02/app/oracle/archivelog/ valid_for=(all_logfiles,all_roles)'
log_archive_dest_2='service=primary lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=cdb'
log_archive_config='dg_config=(cdb,cdb_dg)'
standby_file_management=auto
db_file_name_convert='E:\APP\XIFENFEI\ORADATA\','+DATA\CDB_DG\DATAFILE\'
log_file_name_convert='E:\APP\XIFENFEI\ORADATA\','+DATA\CDB_DG\LOGFILE\'
fal_server=primary

primary 修改参数

CDB_CDB$ROOT@SYS> alter system set log_archive_config='dg_config=(cdb,cdb_dg)';

系统已更改。

CDB_CDB$ROOT@SYS> alter system set log_archive_dest_2='
 2 service=standby lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=cdb_dg';

系统已更改。

CDB_CDB$ROOT@SYS> alter system set log_archive_dest_1=
  2 'LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles)';

系统已更改。

CDB_CDB$ROOT@SYS> alter system set fal_server=standby;

系统已更改。

CDB_CDB$ROOT@SYS> alter system set standby_file_management=auto;

系统已更改。

CDB_CDB$ROOT@SYS> alter system set db_file_name_convert='+DATA\CDB_DG\DATAFILE\','E:\APP\XIFENFEI\ORADATA\' scope=spfile;

系统已更改。

CDB_CDB$ROOT@SYS> alter system set log_file_name_convert='+DATA\CDB_DG\LOGFILE\','E:\APP\XIFENFEI\ORADATA\' scope=spfile;

系统已更改。

primary and standby tns

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.32 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
    )
  )

PRIMARY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.30.1 )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cdb)
    )
  )

standby restore controlfile

ASMCMD> cp /tmp/CONTROL01.CTL control01.ctl
copying /tmp/CONTROL01.CTL -> +data/cdb/control01.ctl

standby password file

[oracle@xifenfei dbs]$ pwd
/u01/app/oracle/product/12.1/db_1/dbs
[oracle@xifenfei dbs]$ cp /tmp/rman/PWDcdb.ora  orapwcdb

standby mount

SYS% cdb> create spfile from pfile='initcdb.ora';

File created.

SYS% cdb> startup mount;
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2290264 bytes
Variable Size             314576296 bytes
Database Buffers          197132288 bytes
Redo Buffers                7938048 bytes
Database mounted.

SYS% cdb> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SYS% cdb> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
E:\APP\XIFENFEI\ORADATA\CDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDBSEED\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\UNDOTBS01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\USERS01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSTEM01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\SYSAUX01.DBF
E:\APP\XIFENFEI\ORADATA\CDB\PDB\PDB_USERS01.DBF

9 rows selected.

standby rman restore

--清理控制文件中的备份集垃圾
DELETE noprompt OBSOLETE;
crosscheck backup;
delete noprompt expired backup;

--注册新备份集
catalog start with '/tmp/rman/';

--还原数据文件
run
{
set newname for database to '+data';
restore database;
switch datafile all;
}

standby clear redo

SYS% cdb> select group# from v$log;

    GROUP#
----------
         4
         6
         5

SYS% cdb> alter database clear logfile group 4;

Database altered.

SYS% cdb> alter database clear logfile group 5;

Database altered.

SYS% cdb> alter database clear logfile group 6;

Database altered.

standby add standby redolog

SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10  size 50M;

Database altered.

SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11  size 50M;

Database altered.

SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 12  size 50M;

Database altered.

SYS% cdb> ALTER DATABASE ADD STANDBY LOGFILE GROUP 13  size 50M;

Database altered.

primary add standby redolog

CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10  'E:\APP\XIFENFEI\ORADATA\CDB\std_redo10.log' size 50M;

数据库已更改。

CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 11  'E:\APP\XIFENFEI\ORADATA\CDB\std_redo11.log' size 50M;

数据库已更改。

CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 12  'E:\APP\XIFENFEI\ORADATA\CDB\std_redo12.log' size 50M;

数据库已更改。

CDB_CDB$ROOT@SYS> ALTER DATABASE ADD STANDBY LOGFILE GROUP 13  'E:\APP\XIFENFEI\ORADATA\CDB\std_redo13.log' size 50M;

数据库已更改。

standby readonly

SYS% cdb> ALTER DATABASE OPEN READ ONLY;

Database altered.

standby start mrp

SYS% cdb> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

DATAGURAD 是否正常检查

--primary
CDB_CDB$ROOT@SYS> archive log list;
数据库日志模式            存档模式
自动存档             启用
存档终点            USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列     374
下一个存档日志序列   376
当前日志序列           376

--standby 
Mon Aug 12 13:56:51 2013
All non-current ORLs have been archived.
Mon Aug 12 13:56:53 2013
Media Recovery Log /u02/app/oracle/archivelog/1_370_820595806.dbf
Mon Aug 12 13:56:57 2013
Media Recovery Log /u02/app/oracle/archivelog/1_371_820595806.dbf
Mon Aug 12 13:57:02 2013
Media Recovery Log /u02/app/oracle/archivelog/1_372_820595806.dbf
Mon Aug 12 13:57:04 2013
Media Recovery Log /u02/app/oracle/archivelog/1_373_820595806.dbf
Mon Aug 12 13:57:05 2013
Media Recovery Log /u02/app/oracle/archivelog/1_374_820595806.dbf
Media Recovery Waiting for thread 1 sequence 375
Mon Aug 12 13:57:19 2013
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process (PID:26114)
RFS[2]: No standby redo logfiles created for thread 1 
RFS[2]: Opened log for thread 1 sequence 376 dbid 1937199326 branch 820595806
Mon Aug 12 13:57:19 2013
RFS[3]: Assigned to RFS process (PID:26118)
RFS[3]: Opened log for thread 1 sequence 375 dbid 1937199326 branch 820595806
Mon Aug 12 13:57:19 2013
Archived Log entry 16 added for thread 1 sequence 375 rlc 820595806 ID 0x7377d8de dest 2:
Mon Aug 12 13:57:22 2013
Media Recovery Log /u02/app/oracle/archivelog/1_375_820595806.dbf
Media Recovery Waiting for thread 1 sequence 376 (in transit)
发表在 Data Guard, ORACLE 12C | 标签为 , | 评论关闭

11G RAC TO 11G RAC ADG配置

发表于 2013 年 6 月 25 日 由 惜分飞

RDBMS tnsnames.ora配置
在主备库的数据库的tns中均增加如下配置

q9dbdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9db01-dg-vip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9db02-dg-vip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = q9db)
    )
)

q9adgdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9adg01-dg-vip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9adg02-dg-vip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = q9db)
    )
)

主库修改参数配置

alter system set DB_UNIQUE_NAME=q9db scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(q9db,q9db_adg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+arch/q9db/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=q9db' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=q9adg1dg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=q9db_adg';
alter system set standby_file_management='AUTO' sid='*' scope=both;
alter system set fal_client='q9dbdg';
alter system set fal_server='q9adgdg';
alter system set db_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;

备库修改参数配置

alter system set DB_UNIQUE_NAME=q9db_adg scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(q9db,q9db_adg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+arch/q9db/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=q9db_adg';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=q9db1dg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=q9db';
alter system set standby_file_management='AUTO' sid='*' scope=both;
alter system set fal_client='q9adgdg';
alter system set fal_server='q9dbdg';
alter system set service_names='q9db';
alter system set db_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;

主库增加standby redo log

alter database add standby logfile THREAD 1 group 51 '+DATA' size 512m;
…………
alter database add standby logfile THREAD 1 group 61 '+DATA' size 512m;

alter database add standby logfile THREAD 2 group 71 '+DATA' size 512m;
…………
alter database add standby logfile THREAD 2 group 81 '+DATA' size 512m;

主库修改强制日志

SQL> ALTER DATABASE FROCE LOGGING;

RMAN备份主库

rman target /
run{
configure device type disk parallelism 16;
backup database filesperset = 10  format '/q9bak/df_%U';
backup current controlfile format '/q9bak/ctl_%U';
}

创建standby controlfile

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/q9bak/stb_ctl';

还原standby controlfile

ASMCMD> cp /q9bak/stb_ctl control01.ctl
copying /q9bak/stb_ctl -> +data/Q9DB/control01.ctl
ASMCMD> cp /q9bak/stb_ctl control02.ctl
copying /q9bak/stb_ctl -> +arch/q9db/control02.ctl

还原备库

SQL>startup mount;

rman target / nocatalog <<EOF
run{
configure default device type to disk;
configure device type disk parallelism 16;
restore database;
}
<<EOF

修改备库文件名

RMAN>catalog start with '+DATA/Q9DB/DATAFILE/';
RMAN> switch database to copy;

清理redo

--清理redo(所有的group#)
SQL> select group# from v$log; 
SQL> alter database clear logfile group 1; 
……

--清理standby redo(所有的group#)
SQL> select group# from v$standby_log; 
SQL> alter database clear logfile group 51; 
……

开启备库实时应用模式

SQL> alter database recover managed standby database using current logfile disconnect from session;

备库注册DB服务(oracle用户)

srvctl add database -d q9db_adg -o $ORACLE_HOME 
srvctl add instance -d q9db_adg -i q9db1 -n q9adg01
srvctl add instance -d q9db_adg -i q9db2 -n q9adg02
srvctl modify database -d q9db_adg -a DATA,ARCH
发表在 Data Guard | 2 条评论