从0开始，手把手教你如何静默安装oracle以及搭建dataguard

最新推荐文章于 2025-10-13 18:22:32 发布

原创最新推荐文章于 2025-10-13 18:22:32 发布 · 1.1k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

本文详细介绍了如何在Linux环境下，通过静默安装方式部署Oracle数据库11gR2，并设置DataGuard，包括基础环境准备、安装依赖、创建数据库、配置监听、数据导入、日志同步等步骤，以及主备库之间的切换操作和RMAN全量加增量备份的配置方法。

Oracle 静默部署DG

基础准备

服务器信息

10.9.25.4       primary

10.9.23.220     standby

11Gr2安装包

链接：https://pan.baidu.com/s/1oemWK5ux27OGBsAsf65HOQ

提取码：wsxd

环境准备(primary、standby共同操作)

添加用户

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle

修改内核参数

vim /etc/sysctl.conf

##内容如下

### oracle

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmall =7864320 #内存大小/4096分页大小

kernel.shmmax = 52451655680 #内存大小

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

vm.nr_hugepages = sga/pagesize #开启hugepage，oracle锁定内存防止换出

应用:

sysctl -p

修改/etc/security/limits.conf

*           soft    nproc   10000

*          hard    nproc   16384

*           soft    nofile   65536

*           hard    nofile 65536

修改/etc/pam.d/login

session required pam_limits.so

修改ulimit参数，添加到profile里，防止重启失效

if [ $SHELL = "/bin/ksh" ]; then

    ulimit -p 16384

    ulimit -n 65536

    ulimit -c 1000000

else

    ulimit -u 16384 -n 65536 -c 1000000

fi

修改/etc/hosts

##内容如下

10.9.25.4       rac1

10.9.23.220     rac2

10.9.25.4       primary

10.9.23.220     standby

增加swap分区

dd if=/dev/zero of=/usr/local/swapfile bs=1M count=1024

mkswap /usr/local/swapfile

swapon /usr/local/swapfile

vim /etc/fstab

##增加以下内容，开机自动挂载

/usr/local/swapfile swap swap defaults 0 0

修改oracle用户变量

##内容如下

umask 022

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1

export ORACLE_SID=orcl

export PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

export TNS_ADMIN=$ORACLE_HOME/network/admin

export LANG=en_US.UTF-8

export DISPLAY=10.9.25.4:1.0

export dbs=/u01/app/oracle/product/11.2.0/db_1/dbs/

export tns=$ORACLE_HOME/network/admin

安装依赖

yum install -y gcc libaio-devel libstdc++-devel sysstat elfutils-libelf-devel glibc-devel gcc-c++ compat-libstdc++-33 unixODBC unixODBC-devel glibc  zlib.i686

yum install binutils compat-libcap1 compat-libstdc++-33 compat-libstdc++-33.i686 gcc gcc-c++ glibc glibc.i686 glibc-devel glibc-devel.i686 ksh libgcc libgcc.i686 libstdc++ libstdc++.i686 libstdc++-devel libstdc++-devel.i686 libaio libaio.i686 libaio-devel libaio-devel.i686 make sysstat

yum install -y gcc libaio-devel libstdc++-devel sysstat elfutils-libelf-devel glibc-devel gcc-c++ compat-libstdc++-33 unixODBC unixODBC-devel

安装oracle （主备都要操作）

创建响应文件

su - oracle
mkdir responsefile
cd responsefile

##编辑响应文件

vim db_install.rsp

##内容如下

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0

oracle.install.option=INSTALL_DB_SWONLY

ORACLE_HOSTNAME=primary ##注意这里不同的服务器要进行修改

UNIX_GROUP_NAME=oinstall

INVENTORY_LOCATION=/u01/app/oracle/oraInventory

SELECTED_LANGUAGES=en,zh_CN

ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

ORACLE_BASE=/u01/app/oracle

oracle.install.db.InstallEdition=EE

oracle.install.db.isCustomInstall=false

oracle.install.db.customComponents=oracle.server:11.2.0.1.0,oracle.sysman.ccr:10.2.7.0.0,oracle.xdk:11.2.0.1.0,oracle.rdbms.oci:11.2.0.1.0,oracle.network:11.2.0.1.0,oracle.network.listener:11.2.0.1.0,oracle.rdbms:11.2.0.1.0,oracle.options:11.2.0.1.0,oracle.rdbms.partitioning:11.2.0.1.0,oracle.oraolap:11.2.0.1.0,oracle.rdbms.dm:11.2.0.1.0,oracle.rdbms.dv:11.2.0.1.0,orcle.rdbms.lbac:11.2.0.1.0,oracle.rdbms.rat:11.2.0.1.0

oracle.install.db.DBA_GROUP=dba

oracle.install.db.OPER_GROUP=oinstall

oracle.install.db.config.starterdb.type=GENERAL_PURPOSE

oracle.install.db.config.starterdb.globalDBName=orcl

oracle.install.db.config.starterdb.SID=orcl

oracle.install.db.config.starterdb.characterSet=AL32UTF8

oracle.install.db.config.starterdb.memoryOption=true

oracle.install.db.config.starterdb.memoryLimit=40960

oracle.install.db.config.starterdb.installExampleSchemas=false

oracle.install.db.config.starterdb.enableSecuritySettings=true

oracle.install.db.config.starterdb.password.ALL=Capgemini123

oracle.install.db.config.starterdb.password.SYS=Capgemini123

oracle.install.db.config.starterdb.password.SYSTEM=Capgemini123

oracle.install.db.config.starterdb.password.SYSMAN=Capgemini123

oracle.install.db.config.starterdb.password.DBSNMP=Capgemini123

oracle.install.db.config.starterdb.control=DB_CONTROL

oracle.install.db.config.starterdb.gridcontrol.gridControlServiceURL=

oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false

oracle.install.db.config.starterdb.dbcontrol.emailAddress=

oracle.install.db.config.starterdb.dbcontrol.SMTPServer=

oracle.install.db.config.starterdb.automatedBackup.enable=false

oracle.install.db.config.starterdb.automatedBackup.osuid=

oracle.install.db.config.starterdb.automatedBackup.ospwd=

oracle.install.db.config.starterdb.storageType=

oracle.install.db.config.starterdb.fileSystemStorage.dataLocation=

oracle.install.db.config.starterdb.fileSystemStorage.recoveryLocation=

oracle.install.db.config.asm.diskGroup=

oracle.install.db.config.asm.ASMSNMPPassword=

MYORACLESUPPORT_USERNAME=

MYORACLESUPPORT_PASSWORD=

SECURITY_UPDATES_VIA_MYORACLESUPPORT=

DECLINE_SECURITY_UPDATES=true

PROXY_HOST=

PROXY_PORT=

PROXY_USER=

PROXY_PWD=

执行静默安装

./runInstaller -silent -ignoreSysPrereqs -ignorePrereq -responseFile /home/oracle/responsefile/db_install.rsp

直到提示以root执行两个脚本，新开一窗口，切换到root用户，执行以下两个脚本

/u01/app/oracle/oraInventory/orainstRoot.sh

/u01/app/oracle/product/11.2.0/db_1/root.sh

再回到刚才的窗口，回车结束

创建监听

##创建响应文件

cd /home/oracle/responsefile

vim netca.rsp

##内容如下

[GENERAL]

RESPONSEFILE_VERSION="11.2"

CREATE_TYPE="CUSTOM"

[oracle.net.ca]

INSTALLED_COMPONENTS={"server","net8","javavm"}

INSTALL_TYPE=""typical""

LISTENER_NUMBER=1

LISTENER_NAMES={"LISTENER"}

LISTENER_PROTOCOLS={"TCP;1521"}

LISTENER_START=""LISTENER""

NAMING_METHODS={"TNSNAMES","ONAMES","HOSTNAME"}

NSN_NUMBER=1

NSN_NAMES={"EXTPROC_CONNECTION_DATA"}

NSN_SERVICE={"PLSExtProc"}

NSN_PROTOCOLS={"TCP;HOSTNAME;1521"}

##创建监听服务

netca /silent /responseFile /home/oracle/responsefile/netca.rsp

###查看监听服务

lsnrctl status

##输出如下

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 30-DEC-2022 11:29:13

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date                30-DEC-2022 11:26:36

Uptime                    0 days 0 hr. 2 min. 36 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/standby/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standby)(PORT=1521)))

The listener supports no services

The command completed successfully

数据导入（本次部署是以rman异机恢复的方式，进行的数据迁移式导入。主库操作）

基础准备

Rman备份集
归档日志
控制文件
参数文件（后来发现原系统是windows系统，并且是spfile文件，修改起来比较麻烦，便舍弃不用，用dbca创建一个同名数据库代替使用）
数据库名称,即oracle_sid
数据库dbid ( select dbid from v$database; ##查询dbid)

创建建库响应文件

vim dbca.rsp

##内容如下

[GENERAL]

RESPONSEFILE_VERSION = "11.2.0"

OPERATION_TYPE = "createDatabase"

[CREATEDATABASE]

GDBNAME = "orcl"

SID = "orcl"

DB_UNIQUE_NAME = "orcl"

INSTANCENAME = "orcl"

TEMPLATENAME = "General_Purpose.dbc"

CHARACTERSET = "AL32UTF8"

TOTALMEMORY = "1024"

###静默建库

$ORACLE_HOME/bin/dbca  -silent -cloneTemplate  -responseFile dbca.rsp

注意：建库的时候会要求输入sys和system用户密码，注意记录好。本机是123456，dg的时候要用到。

###创建参数文件，关闭数据库

sqlplus  / as sysdba


SQL> create pfile from spfile ;

SQL> shutdown immediate ;

##根据pfile文件中控制文件的路径，用准备好的控制文件做相应的替换

cd $dbs ; mv spfile.ora spfile.ora.bak

vim initorcl.ora

##修改control_files参数为以下内容

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl'

###将控制文件放到相应目录

cd /u01/rman/

cp CONTROL01.CTL '/u01/app/oracle/oradata/orcl/control01.ctl'

cp CONTROL02.CTL '/u01/app/oracle/oradata/orcl/control02.ctl'

###Rman登陆数据库操作恢复数据

export ORACLE_SID=orcl

rman target /

RMAN> set dbid 1651710487;  ##此处是由之前提供的dbid

RMAN> startup nomount;

RMAN> alter database mount;

RMAN> crosscheck backup;

RMAN> delete expired backup;   ## 输入yes ,把旧的备份集删除。

RMAN> catalog start with '/u01/rman/2022_12_28/';

RMAN> list backup;

##输出如下

…………

List of Datafiles in backup set 7

File LV Type Ckp SCN    Ckp Time            Name

---- -- ---- ---------- ------------------- ----

1       Full 3329820    2022-12-28 18:20:05 D:\DCDT\ORACLE\ORADATA\ORCL\SYSTEM01.DBF

2       Full 3329820    2022-12-28 18:20:05 D:\DCDT\ORACLE\ORADATA\ORCL\SYSAUX01.DBF

3      Full 3329820    2022-12-28 18:20:05 D:\DCDT\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF

4       Full 3329820    2022-12-28 18:20:05 D:\DCDT\ORACLE\ORADATA\ORCL\USERS01.DBF

…………

##查看scn ,当前为 3329820

RMAN> RUN{    

    set newname for database to '/u01/app/oracle/oradata/orcl/%U.dbf';

    restore database;

    switch datafile all;

    switch tempfile all ;

}


RMAN> recover database until scn 3329820;           ###为上条命令所查出的scn

RMAN> alter database open resetlogs;

Oracle dataguard 部署之主库操作

###查看归档模式是不是已经开启

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   3

Current log sequence             3

###为备库创建redo日志

SQL> ALTER DATABASE ADD STANDBY LOGFILE   ('/u01/app/oracle/redolog/orcl/slog/slog1.rdo') SIZE 256M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE   ('/u01/app/oracle/redolog/orcl/slog/slog2.rdo') SIZE 256M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE   ('/u01/app/oracle/redolog/orcl/slog/slog3.rdo') SIZE 256M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE   ('/u01/app/oracle/redolog/orcl/slog/slog4.rdo') SIZE 256M;

###如果报错路径不存在，mkdir -p /u01/app/oracle/redolog/orcl/slog/

###修改主库参数文件

SQL> create pfile from spfile;

cd $dbs

vim initorcl.ora  ##增加以下内容

DB_UNIQUE_NAME=orcl_primary

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_primary,orcl_standby)'

LOG_ARCHIVE_DEST_2= 'SERVICE=orcl_sdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_standby'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=orcl_sdb

FAL_CLIENT=orcl

STANDBY_FILE_MANAGEMENT=AUTO

###把二进制spfile 重命，使之失效。

mv spfileorcl.ora spfileorcl.orabak

###重启数据库，使修改生效

sqlplus / as sysdba

SQL> shutdown immediate ;

SQL> starup  ;

###看到database opened 正常。数据库的状态有shutdown , nomount , mount , open 四种状态。

###正常启动以后，为数据库创建spfile

SQL> create spfile from pfile ;

###为备库创建参数文件

SQL> create pfile='/u01/standby02.ora' from spfile;

###为备库创建控制文件

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/control01.ctl';

###为备库创建密码文件(dbca创建数据库的时候，让要求输入密码，此文件就对应的此密码)

orapwd file=orapworcl password=123456 entries=2 force=y

cp $dbs/orapworcl /u01

###把参数文件，控制文件，密码文件传送到备库上面

cd /u01

scp control01.ctl orapworcl standby02.ora rac2:/home/oracle    ##密码：oracle

###主库监听服务配置

vim listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = rac1)(PORT = 1521))

)

)

ADR_BASE_LISTENER = /u01/app/oracle

##################################### tnsname #######################################################

vim tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl_primary)

)

)

orcl_sdb =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl_standby)

)

)

重启监听服务

lsnrctl stop

lsnrctl start

Oracle dataguard之备库操作

修改参数文件将传过来的参数文件进行修改，主要校验一下以下参数。

vim initorcl.ora

DB_UNIQUE_NAME=orcl_standby

LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl_primary,orcl_standby)'

LOG_ARCHIVE_DEST_2= 'SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_primary'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_MAX_PROCESSES=30

FAL_SERVER=orcl

FAL_CLIENT=orcl_sdb

STANDBY_FILE_MANAGEMENT=AUTO

###创建归档目录

mkdir -p /u01/app/oracle/flash_recovery_area

###将密码文件，参数文件，控制文件放到相应位置

su - oracle

cp orapworcl $dbs

cp standby02.ora $dbs/initorcl.ora

cp control01.ctl  /u01/app/oracle/oradata/orcl/control01.ctl

cp control01.ctl  /u01/app/oracle/oradata/orcl/control02.ctl

###创建audit_file_dest 目录，根据参数文件里的配置进行创建

mkdir -p /u01/app/oracle/admin/orcl/adump

###将备库启动到nomount 状态

sqlplus  / as sysdba

SQL> startup nomount ;

###配置监听服务

cd $tns

vim listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = orcl_primary)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl)

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = orcl_standby)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

(SID_NAME = orcl)

)

)

ADR_BASE_LISTENER = /u01/app/oracle

##################tnsnames###############################

vim tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

ORCL =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl_primary)

)

)

orcl_sdb =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl_standby)

)

)

##内容如下

lsnrctl stop

lsnrctl start

通过rman把数据从主库复制到从库主库操作

将主库之前在windows服务器的一些信息从控制文件里删除

##sqlplus操作

SQL> execute sys.dbms_backup_restore.resetCfileSection( 11);

SQL> select name from v$archived_log;

##切换到rman操作

rman target /

RMAN> catalog start with '/u01/app/oracle/flash_recovery_area';

###在主库先进行备份

mkdir -p /u01/rman/backup/

rman target /

RMAN> configure channel device type disk format '/u01/rman/backup/%d_%I_%s_%p.%T.bkp';

RMAN> backup as compressed backupset database include current controlfile for standby plus archivelog;

###将备份集scp到从库

cd /u01/rman

scp -r backup rac2:/u01/rman/              #密码：oracle

###将新的归档日志scp到从库

cd /u01/app/oracle/flash_recovery_area

scp -r ORCL* rac2:/u01/app/oracle/flash_recovery_area/

###重新为备库生成控制文件，并scp

sqlplus / as sysdba

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/control.ctl';

cd /u01

scp control.ctl rac2:/u01/         ##密码 oracle

注意：此步从库操作，将从库更新控制文件，并重新启动到 nomount 状态

sqlplus / as sysdba

SQL> shutdown ;

##启动数据库到nomount 状态

sqlplus / as sysdba

SQL> startup nomount ;

注意：恢复主库操作，把数据在从库进行恢复

rman target sys/123456@orcl auxiliary sys/123456@orcl_sdb      ##红色字体为建库时的密码或者创建密码文件时候使用的密码

##输出如下

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 30 15:33:57 2022

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORCL (DBID=1651710487)

connected to auxiliary database: ORCL (not mounted)

RMAN>

##执行同步命令

RMAN> duplicate target database for standby nofilenamecheck dorecover;

==结束输出如下==

………………

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL_PRIMARY/archivelog/2022_12_30/o1_mf_1_4_ktx8zv3x_.arc thread=1

media recovery complete, elapsed time: 00:00:00

Finished recover at 2022-12-30 16:54:46

Finished Duplicate Db at 2022-12-30 16:54:53

启用数据持续同步并校验

备库操作

sqlplus / as sysdba

SQL> alter database add standby logfile group 5 size 256M;
SQL> alter database add standby logfile group 6 size 256M;
SQL> alter database add standby logfile group 7 size 256M;
SQL> alter database add standby logfile group 8 size 256M;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;  
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE  DISCONNECT;

校验日志在备库的应用状态

#手动归档日志主库操作

SQL> ALTER SYSTEM SWITCH LOGFILE;  可以多执行几次

#查看备库上收到redo日志 备库操作

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;  #查看备库上应用的redo日志 备库操作

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG  where APPLIED='YES'  ORDER BY SEQUENCE# ;    #查看备库上没有应用的redo日志  备库操作

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG  where APPLIED='NO'  ORDER BY SEQUENCE# ; 
   #如果有记录，代表日志没有应用完，数据未完全同步。需要等待。

建表测试

#创建测试表，主库操作

SQL> create table test1(id,owner,object_id) as    select rownum,owner,object_id from all_objects where rownum<=10 ;

Table created.

SQL> select * from test1 ; 

ID OWNER                           OBJECT_ID
---------- ------------------------------ ----------
1 SYS                                    20
2 SYS                                    46
3 SYS                                    28
4 SYS                                    15
5 SYS                                    29
6 SYS                                     3
7 SYS                                    25
8 SYS                                    41
9 SYS                                    54
10 SYS                                    40

10 rows selected.

#备库查询test1 表，看是不是已经正常同步到备库

sqlplus / as sysdba

SQL> select * from test1 ;

1 SYS                                    20
2 SYS                                    46
3 SYS                                    28
4 SYS                                    15
5 SYS                                    29
6 SYS                                     3
7 SYS                                    25
8 SYS                                    41
9 SYS                                    54
10 SYS                                    40

10 rows selected.

DATAGUARD 关机操作

关机

##在主库上执行

SQL> shutdown immediate;

##在备库上执行

SQL> alter database recover managed standby database cancel;--这句很关键，如果不执行这句，备库基本关不掉，最后只能 shutdown abort

SQL> shutdown immediate;

##alter database recover managed standby database cancel;是用来关闭MRP进程，这个进程是用来还原主库日志到备库上的操作，如果不关了他，实例会认为还有日志文件未处理，一直在等MRP进程的反馈

开机

##在备库上执行

SQL> startup mount;

SQL> alter database open read only;

SQL> alter database recover managed standby database disconnect from session;

##在主库上执行

SQL> startup mount;

SQL> alter database open;

注：如果你是要做硬件维护，把服务器关了，则要关注主备服务器上的监听是否启动

检查监听状态

su – oracle

lsnrctl status
lsnrctl start

主备切换

主切备，主库操作：

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY SESSIONS ACTIVE

SQL> alter database commit to switchover to physical standby with session shutdown;  （主切备，后台进程关掉了数据库）

ERROR:

ORA-01034: ORACLE not available

Process ID: 17761

Session ID: 106 Serial number: 1421

Database altered.

SQL> conn / as sysdba

SQL> startup mount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size                  2257800 bytes

Variable Size             503319672 bytes

Database Buffers          322961408 bytes

Redo Buffers                2392064 bytes

Database mounted.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY RECOVERY NEEDED

##需应用日志：

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

###此时转换后的备库一直在应用日志，如果需要再次转换为主库需结束应用日志：

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY TO PRIMARY

备切主，备库操作

sqlplus / as sysdba

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY NOT ALLOWED (没有主库，状态显示不正常，正常应为to primary)

###切换一下

SQL> alter database commit to switchover to primary with session shutdown;

alter database commit to switchover to primary with session shutdown

*

ERROR at line 1:

ORA-16139: media recovery required

###根据提示，应用日志：

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

Media recovery complete.

再次查看状态：

SQL> select database_role,switchover_status from v$databases;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PHYSICAL STANDBY TO PRIMARY（正常了）

（因为有主库，则状态为not allowed；如果没有主库，则状态都为to primary）

SQL> alter database commit to switchover to primary with session shutdown;
SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY NOT ALLOWED （备切主后数据库状态为mount）

启库：

SQL> alter database open;
Database altered.

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY RESOLVABLE GAP

##转换后的备库正在应用日志，过会再看一下转换后的主库状态：

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY TO STANDBY（正常）

##过会再看一下转换后的主库状态：

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS

---------------- --------------------

PRIMARY SESSIONS ACTIVE（正常）

RMAN全量加增量备份

预设备份参数

###更改控制文件自动备份目录

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/rman/backup/auto_%F';

###更改备份过期时间

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;            ##此命令为保留7天

RMAN> DELETE NOPROMPT OBSOLETE;                                         ##删除过期备份

###按备份集份数来保留目前采用些策略，保留两份

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;                ##保留两份完整备份集  

RMAN> DELETE NOPROMPT OBSOLETE;                                  ##删除多余备份集

编写备份脚本

vim oracle_rman.sh

###内容如下

###############################################
##  oracle_rman.sh                           ##
##   created by  snmfkrqw                    ##
##        2023-1-3                           ##
###############################################


#!/bin/ksh


export LANG=en_US

BACKUP_DATE=`date +%d`

RMAN_LOG_FILE=/u01/rman/backup.out

TODAY=`date`

USER=`id|cut -d "(" -f2|cut -d ")" -f1`

echo "-----------------$TODAY-------------------">$RMAN_LOG_FILE


ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

export ORACLE_HOME


RMAN=$ORACLE_HOME/bin/rman
export RMAN


ORACLE_SID=orcl
export ORACLE_SID


ORACLE_USER=oracle
export ORACLE_USER

echo "ORACLE_SID: $ORACLE_SID">>$RMAN_LOG_FILE
echo "ORACLE_HOME:$ORACLE_HOME">>$RMAN_LOG_FILE
echo "ORACLE_USER:$ORACLE_USER">>$RMAN_LOG_FILE
echo "==========================================">>$RMAN_LOG_FILE
echo "BACKUP DATABASE BEGIN......">>$RMAN_LOG_FILE
echo "                   ">>$RMAN_LOG_FILE


chmod 666 $RMAN_LOG_FILE
WEEK_DAILY=`date +%a`
case  "$WEEK_DAILY" in
"Mon")
    BAK_LEVEL=1
    ;;
"Tue")
    BAK_LEVEL=1
    ;;
"Wed")
    BAK_LEVEL=1
    ;;
"Thu")
    BAK_LEVEL=1
    ;;
"Fri")
    BAK_LEVEL=1
    ;;
"Sat")
    BAK_LEVEL=0                         ###注意：周六为0级备份，其他时间为1级增量备份
    ;;
"Sun")
    BAK_LEVEL=1
    ;;
"*")
    BAK_LEVEL=error
esac


export BAK_LEVEL=$BAK_LEVEL
echo "Today is : $WEEK_DAILY  incremental level= $BAK_LEVEL">>$RMAN_LOG_FILE





RUN_STR="
BAK_LEVEL=$BAK_LEVEL
export BAK_LEVEL
ORACLE_HOME=$ORACLE_HOME
export ORACLE_HOME
ORACLE_SID=$ORACLE_SID
export ORACLE_SID


$RMAN nocatalog TARGET /  msglog $RMAN_LOG_FILE append <<EOF

run {

    allocate channel c1 type disk;
    allocate channel c2 type disk;
    backup  incremental level= $BAK_LEVEL  skip inaccessible filesperset 5 Database     format='/u01/rman/backup/orcl_lev"$BAK_LEVEL"_%U_%T'  tag='orcl_lev"$BAK_LEVEL"' ;
    sql 'alter system archive log current';
    backup archivelog all tag='arc_bak' format='/u01/rman/backup/arch_%U_%T' skip inaccessible  filesperset 5 not  backed up 1 times  delete input;
    backup current controlfile tag='ctlfile' format='/u01/rman/backup/ctl_file_%U_%T';
    backup spfile tag='spfile' format='/u01/rman/backup/ORCL_spfile_%U_%T';
    release channel c2;
    release channel c1;
}

    report obsolete;
    delete noprompt obsolete;
    crosscheck backup;
    delete noprompt expired backup;
    list backup summary;

EOF
"

# Initiate the command string

if [ "$USER" = "root" ]
then
    echo "Root Command String: $RUN_STR" >> $RMAN_LOG_FILE
    su - $ORACLE_USER -c "$RUN_STR" >> $RMAN_LOG_FILE
    RSTAT=$?
else
    echo "User Command String: $RUN_STR" >> $RMAN_LOG_FILE
    /bin/sh -c "$RUN_STR" >> $RMAN_LOG_FILE
    RSTAT=$?
fi


# ---------------------------------------------------------------------------
# Log the completion of this script.
# ---------------------------------------------------------------------------

if [ "$RSTAT" = "0" ]
then
    LOGMSG="ended successfully"
else
    LOGMSG="ended in error"
fi

echo >> $RMAN_LOG_FILE
echo Script $0 >> $RMAN_LOG_FILE
echo ==== $LOGMSG on `date` ==== >> $RMAN_LOG_FILE
echo >> $RMAN_LOG_FILE

exit $RSTAT

更改脚本权限

chown oracle:oinstall oracle_rman.sh

chmod +x oracle_rman.sh

加入oracle用户计划任务

##每天夜里2点备份数据库

0 2 * * * /u01/rman/oracle_rman.sh

从0开始，手把手教你如何静默安装oracle以及搭建dataguard

Oracle 静默部署DG

基础准备

环境准备(primary、standby共同操作)

Oracle dataguard之备库操作

通过rman把数据从主库复制到从库 主库操作

启用数据持续同步并校验

主备切换

RMAN全量加增量备份

通过rman把数据从主库复制到从库主库操作