通過RMAN將Oracle 11g R2 RAC資料移轉到單一實例資料庫

標籤：oracle

一、準備單一實例資料庫伺服器

1、準備作業系統

註：建議安裝oracle資料庫使用oracle linux作業系統，本執行個體以oracle linux 6.8做為作業系統。

#關閉iptables

chkconfig iptables off

#關閉selinux

vim /etc/sysconfig/selinux 

SELINUX=disabled

#將主機名稱添加到hosts檔案

vim /etc/hosts

188.188.1.120   oracledb

#重啟伺服器

reboot

2、初始化作業系統，執行以下指令碼

#使用者初始化指令碼preusers.sh

註：grid使用者的ORACLE_SID必須為+ASM，ORACLE_HOSTNAME、ORACLE_SID、ORACLE_UNQNAME做相應的更改

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

#!/bin/bash

#Purpose:Create 6 groups named 'oinstall','dba','asmadmin','asmdba','asmoper','oper', plus 2 users named 'oracle','grid'.

#Also setting the Environment

#variable for oracle user.

#variable for grid user.

#Usage:Log on as the superuser('root'),and then execute the command:#./1preusers.sh

#Author:Asher Huang

  

echo "Now create 6 groups named 'oinstall','dba','asmadmin','asmdba','asmoper','oper'"

echo "Plus 2 users named 'oracle','grid',Also setting the Environment"

  

  

groupadd -g 1000 oinstall 

groupadd -g 1200 asmadmin 

groupadd -g 1201 asmdba 

groupadd -g 1202 asmoper 

useradd -u 502 -g oinstall -G asmadmin,asmdba,asmoper -d /home/grid -s /bin/bash -c "grid Infrastructure Owner" grid 

echo "grid" | passwd --stdin grid

  

#echo 'export PS1="`/bin/hostname -s`-> "'>> /home/grid/.bash_profile 

echo "export TMP=/tmp">> /home/grid/.bash_profile  

echo 'export TMPDIR=$TMP'>>/home/grid/.bash_profile 

echo "export ORACLE_SID=+ASM">> /home/grid/.bash_profile 

echo "export ORACLE_BASE=/u01/app/grid">> /home/grid/.bash_profile

echo "export ORACLE_HOME=/u01/app/11.2.0/grid">> /home/grid/.bash_profile

echo "export ORACLE_TERM=xterm">> /home/grid/.bash_profile

echo "export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'" >> /home/grid/.bash_profile

echo 'export TNS_ADMIN=$ORACLE_HOME/network/admin'  >> /home/grid/.bash_profile

echo 'export PATH=/usr/sbin:$PATH'>> /home/grid/.bash_profile

echo 'export PATH=$ORACLE_HOME/bin:$PATH'>> /home/grid/.bash_profile

echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib'>> /home/grid/.bash_profile

echo 'export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib'>> /home/grid/.bash_profile

echo "export EDITOR=vi" >> /home/grid/.bash_profile

echo "export" >> /home/grid/.bash_profile

echo "export NLS_LANG=american_america.AL32UTF8" >> /home/grid/.bash_profile

echo "umask 022">> /home/grid/.bash_profile

  

groupadd -g 1300 dba 

groupadd -g 1301 oper 

useradd -u 501 -g oinstall -G dba,oper,asmdba -d /home/oracle -s /bin/bash -c "Oracle Software Owner" oracle 

echo "oracle" | passwd --stdin oracle

  

#echo 'export PS1="`/bin/hostname -s`-> "'>> /home/oracle/.bash_profile 

echo "export TMP=/tmp">> /home/oracle/.bash_profile  

echo 'export TMPDIR=$TMP'>>/home/oracle/.bash_profile 

echo "export ORACLE_HOSTNAME=oracledb">> /home/oracle/.bash_profile 

echo "export ORACLE_SID=bbdg">> /home/oracle/.bash_profile 

echo "export ORACLE_BASE=/u01/app/oracle">> /home/oracle/.bash_profile

echo 'export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1'>> /home/oracle/.bash_profile

echo "export ORACLE_UNQNAME=bbdg">> /home/oracle/.bash_profile

echo 'export TNS_ADMIN=$ORACLE_HOME/network/admin'  >> /home/oracle/.bash_profile

echo "export ORACLE_TERM=xterm">> /home/oracle/.bash_profile

echo 'export PATH=/usr/sbin:$PATH'>> /home/oracle/.bash_profile

echo 'export PATH=$ORACLE_HOME/bin:$PATH'>> /home/oracle/.bash_profile

echo 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib'>> /home/oracle/.bash_profile

echo 'export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib'>> /home/oracle/.bash_profile

echo "export EDITOR=vi" >> /home/oracle/.bash_profile

echo "export" >> /home/oracle/.bash_profile

echo "export NLS_LANG=american_america.AL32UTF8" >> /home/oracle/.bash_profile

echo "export NLS_DATE_FORMAT='yyyy/mm/dd hh24:mi:ss'" >> /home/oracle/.bash_profile

echo "umask 022">> /home/oracle/.bash_profile

  

echo "The Groups and users has been created"

echo "The Environment for grid,oracle also has been set successfully"

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

#建立目錄指令碼predir.sh

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

#!/bin/bash

#Purpose:Create the necessary directory for oracle,grid users and change the authention to oracle,grid users.

#Usage:Log on as the superuser('root'),and then execute the command:#./2predir.sh

#Author:Asher Huang

  

echo "Now create the necessary directory for oracle,grid users and change the authention to oracle,grid users..."

mkdir -p /u01/app/grid

mkdir -p /u01/app/11.2.0/grid

mkdir -p /u01/app/oracle

chown -R oracle:oinstall /u01

chown -R grid:oinstall /u01/app/grid

chown -R grid:oinstall /u01/app/11.2.0

chmod -R 775 /u01

echo "The necessary directory for oracle,grid users and change the authention to oracle,grid users has been finished"

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

#修改limits指令碼prelimits.sh

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

#!/bin/bash

#Purpose:Change the /etc/security/limits.conf.

#Usage:Log on as the superuser('root'),and then execute the command:#./3prelimits.sh

#Author:Asher Huang

  

echo "Now modify the /etc/security/limits.conf,but backup it named /etc/security/limits.conf.bak before"

cp /etc/security/limits.conf /etc/security/limits.conf.bak

echo "oracle soft nproc 2047" >>/etc/security/limits.conf

echo "oracle hard nproc 16384" >>/etc/security/limits.conf

echo "oracle soft nofile 1024" >>/etc/security/limits.conf

echo "oracle hard nofile 65536" >>/etc/security/limits.conf

echo "grid soft nproc 2047" >>/etc/security/limits.conf

echo "grid hard nproc 16384" >>/etc/security/limits.conf

echo "grid soft nofile 1024" >>/etc/security/limits.conf

echo "grid hard nofile 65536" >>/etc/security/limits.conf

echo "Modifing the /etc/security/limits.conf has been succeed."

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

#修改login指令碼prelogin.sh

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

#!/bin/bash

#Purpose:Modify the /etc/pam.d/login.

#Usage:Log on as the superuser('root'),and then execute the command:#./4prelimits.sh

#Author:Asher Huang

  

echo "Now modify the /etc/pam.d/login,but with a backup named /etc/pam.d/login.bak"

cp /etc/pam.d/login /etc/pam.d/login.bak

  

echo "session required /lib/security/pam_limits.so" >>/etc/pam.d/login

echo "session required pam_limits.so" >>/etc/pam.d/login

  

echo "Modifing the /etc/pam.d/login has been succeed."

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

#修改profile指令碼preprofile.sh

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

#!/bin/bash

#Purpose:Modify the /etc/profile.

#Usage:Log on as the superuser('root'),and then execute the command:#./5preprofile.sh

#Author:Asher Huang

  

echo "Now modify the  /etc/profile,but with a backup named  /etc/profile.bak"

cp /etc/profile /etc/profile.bak

echo 'if [ $USER = "oracle" ]||[ $USER = "grid" ]; then' >>  /etc/profile

echo 'if [ $SHELL = "/bin/ksh" ]; then' >> /etc/profile

echo 'ulimit -p 16384' >> /etc/profile

echo 'ulimit -n 65536' >> /etc/profile

echo 'else' >> /etc/profile

echo 'ulimit -u 16384 -n 65536' >> /etc/profile

echo 'fi' >> /etc/profile

echo 'fi' >> /etc/profile

echo "Modifing the /etc/profile has been succeed."

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

#修改核心參數指令碼presysctl.sh

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

#!/bin/bash

#Purpose:Modify the /etc/sysctl.conf.

#Usage:Log on as the superuser('root'),and then execute the command:#./6presysctl.sh

#Author:Asher Huang

  

echo "Now modify the /etc/sysctl.conf,but with a backup named /etc/sysctl.bak"

cp /etc/sysctl.conf /etc/sysctl.conf.bak

  

echo "fs.aio-max-nr = 1048576" >> /etc/sysctl.conf

echo "fs.file-max = 6815744" >> /etc/sysctl.conf

echo "kernel.shmall = 2097152" >> /etc/sysctl.conf

echo "kernel.shmmax = 1054472192" >> /etc/sysctl.conf

echo "kernel.shmmni = 4096" >> /etc/sysctl.conf

echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf

echo "net.ipv4.ip_local_port_range = 9000 65500" >> /etc/sysctl.conf

echo "net.core.rmem_default = 262144" >> /etc/sysctl.conf

echo "net.core.rmem_max = 4194304" >> /etc/sysctl.conf

echo "net.core.wmem_default = 262144" >> /etc/sysctl.conf

echo "net.core.wmem_max = 1048586" >> /etc/sysctl.conf

echo "net.ipv4.tcp_wmem = 262144 262144 262144" >> /etc/sysctl.conf

echo "net.ipv4.tcp_rmem = 4194304 4194304 4194304" >> /etc/sysctl.conf

  

echo "Modifing the /etc/sysctl.conf has been succeed."

echo "Now make the changes take effect....."

sysctl -p

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

3、配置ASM磁碟組

註：由於oracle rac使用了asm儲存資料檔案，單一實例資料庫也使用asm儲存資料庫檔案，磁碟組需要與RAC磁碟組一致，方便還原

#磁碟分割

fdisk /dev/sd*

n-p-1-w

#安裝asm

yum install -y kmod-oracleasm-2.0.8-13.el6_8.x86_64.rpm oracleasmlib-2.0.4-1.el6.x86_64.rpm oracleasm-support-2.1.8-1.el6.x86_64.rpm

#建立asm磁碟

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

# oracleasm status

# oracleasm configure -i

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library

driver.  The following questions will determine whether the driver is

loaded on boot and what permissions it will have.  The current values

will be shown in brackets ('[]').  Hitting <ENTER> without typing an

answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: grid

Default group to own the driver interface []: asmadmin

Start Oracle ASM library driver on boot (y/n) [n]: y

Scan for Oracle ASM disks on boot (y/n) [y]: y

Writing Oracle ASM library driver configuration: done

# oracleasm init

# oracleasm configure

# oracleasm listdisks

# oracleasm createdisk VOL1 /dev/sdb1 

# oracleasm querydisk /dev/sd*

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

4、安裝必備rpm

yum install -y binutils elfutils-libelf glibc compat-libstdc++-33.x86_64 libgcc unixODBC  glibc.i686 elfutils-libelf-devel gcc-c++ libaio-devel libstdc++-devel unixODBC-devel pdksh xorg-x11-* compat-libcap1 ksh-20100621

5、安裝grid

註：新開啟一個未登入xshell視窗，使用grid賬戶登入，如果直接從root切換到grid賬戶,則xshell無法開啟安裝grid映像化介面

ssh [email protected]

cd grid/

./runInstaller

#建立和rac資料庫一樣的磁碟組

asmca

#修改u01/app/oracle/product/11.2.0/db_1/bin/oracle

註：如果不修改許可權則無法向asm寫入檔案

chown oracle.asmadmin /u01/app/oracle/product/11.2.0/db_1/bin/oracle

chmod 6555 /u01/app/oracle/product/11.2.0/db_1/bin/oracle

6、安裝oracle，只需安裝軟體，不建立資料庫

註：新開啟一個未登入xshell視窗，使用oracle賬戶登入，如果直接從root切換到oracle賬戶,則xshell無法開啟安裝oracle映像化介面

ssh [email protected]

cd database/

./runInstaller

二、準備資料庫RMAN備份

註：先備份資料庫和歸檔日誌，再備份控制檔案，因為rman備份資訊儲存在控制檔案中

#執行RMAN備份

su - grid

rman target /

backup as compressed backupset database format '/tmp/backup/Full_%U.bak';

backup as compressed backupset archivelog all format '/tmp/backup/ARC_%U.bak';

backup device type disk format '/tmp/backup/standby_%U.ctl' current controlfile;

#產生初始化參數檔案

su - oracle

sqlplus / as sysdba

create pfile='/tmp/backup/initbbdg.ora' from spfile;

#拷貝密碼檔案

/u01/app/oracle/product/11.2.0/db_1/dbs/orapwbbdg1 /tmp/backup

#將備份傳到oracle單一實例資料庫

註：備份檔案傳到的目錄必須rman備份的目錄一致，且目錄/tmp/backup及裡面的檔案必須屬於oracle.oinstall，否則無法還原

scp -r /tmp/backup [email protected]:/tmp

三、還原資料庫

1、建立密碼檔案

su - oracle

cp /tmp/backup/orapwbbdg1 /u01/app/oracle/product/11.2.0/db_1/dbs/orapwbbdg

2、建立初始化參數檔案

cp /tmp/backup /u01/app/oracle/product/11.2.0/db_1/dbs/initbbdg.ora

vim /u01/app/oracle/product/11.2.0/db_1/dbs/initbbdg.ora

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

bbdg.__db_cache_size=255852544

bbdg.__java_pool_size=4194304

bbdg.__large_pool_size=4194304

bbdg.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

bbdg.__pga_aggregate_target=419430400

bbdg.__sga_target=423624704

bbdg.__shared_io_pool_size=0

bbdg.__shared_pool_size=146800640

bbdg.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/bbdg/adump'

*.audit_trail='db'

*.cluster_database=false

*.compatible='11.2.0.4.0'

*.control_files='+data_vg/bbdg/controlfile/cont.ctl'

*.core_dump_dest='/u01/app/oracle/diag/rdbms/bbdg/cdump'

*.db_block_size=8192

*.db_create_file_dest='+data_vg'

*.db_domain=''

*.db_file_name_convert='+data_vg/bbsvr/','+data_vg/bbdg/'

*.db_name='bbsvr'

*.db_recovery_file_dest='+FRA_VG'

*.db_recovery_file_dest_size=4070572032

*.db_unique_name='bbdg'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=bbdgXDB)'

*.fal_client='bbdg'

*.fal_server='bbsvri1'

#*.log_archive_config='dg_config=(bbsvr,bbdg)'

#*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=bbdg'

#*.log_archive_dest_2='service=bbsvr valid_for=(online_logfiles,primary_role) db_unique_name=bbsvri1'

*.log_archive_format='ARC_%t_%S_%r.arc'

*.log_file_name_convert='+data_vg/bbsvr/','+data_vg/bbdg/'

*.memory_target=842006528

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='exclusive'

*.service_names='bbdg'

*.standby_file_management='auto'

*.thread=1

*.undo_management='auto'

*.undo_tablespace='UNDOTBS1'

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

3、還原控制檔案和資料庫

su - oracle

rman target /

startup nomount;

restore standby controlfile from '/tmp/backup/standby_%U.ctl';

alter database mount;

restore database;

alter database open;

通過RMAN將Oracle 11g R2 RAC資料移轉到單一實例資料庫