Migrating Oracle 11g R2 RAC data to a single-instance database via Rman

First, prepare the single instance database server

1. Preparing the operating system

Note: It is recommended to install an Oracle database using the Oracle Linux operating system, which is the operating system for 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. Initialize the operating system and execute the following script

#用户初始化脚本preusers. Sh

Note: The oracle_sid of a grid user must make corresponding changes to +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 Oinstall

Groupadd-g 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 have been set successfully"

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

#创建目录脚本predir. Sh

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

#!/bin/bash

#Purpose: Create The necessary directory for Oracle,grid users and a change of 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 have 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 1024x768" >>/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 1024x768" >>/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 = 32000" >>/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. Configuring ASM Disk Groups

NOTE: Because Oracle RAC uses ASM to store data files, the single-instance database also uses ASM to store database files, and disk groups need to be consistent with the RAC Disk group for easy restore

#磁盘分区

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 would configure the On-boot properties of the Oracle ASM Library

Driver. The following questions would determine whether the driver is

Loaded on boot and what permissions it would have. The current values

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

Answer would keep that current value. Ctrl-c'll 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. Install the required rpm

Yum install-y binutils elfutils-libelf glibc compat-libstdc++-33.x86_64 libgcc UnixODBC glibc.i686 elfutils-libelf-deve L gcc-c++ libaio-devel libstdc++-devel unixodbc-devel pdksh xorg-x11-* compat-libcap1 ksh-20100621

5. Installing the grid

Note: Open a new Login Xshell window, log in with a grid account, if you switch directly from root to the grid account, Xshell cannot open the install grid GUI

SSH [email protected]

CD Grid/

./runinstaller

#创建和rac数据库一样的磁盘组

Asmca

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

Note: You cannot write files to ASM if you do not modify permissions

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, install Oracle, just install software, do not create database

Note: A new open Xshell window is opened, login with Oracle account, if you switch directly from root to Oracle account, Xshell cannot open the install Oracle Image interface

SSH [email protected]

CD database/

./runinstaller

Ii. Preparing the database Rman backup

Note: Back up the database and archive logs before backing up the control files because the Rman backup information is stored in the control file

#执行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单实例数据库

Note: The directory to which the backup file is uploaded must be consistent with the Rman backup directory, and the directory/tmp/backup and the files must belong to Oracle.oinstall, otherwise it cannot be restored

Scp-r/tmp/backup [Email protected]:/tmp

Third, restore the database

1. Create a password file

Su-oracle

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

2. Create initialization parameter file

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. Restore control files and databases

Su-oracle

Rman Target/

Startup Nomount;

Restore standby controlfile from '/tmp/backup/standby_%u.ctl ';

ALTER DATABASE Mount;

Restore database;

ALTER DATABASE open;

Migrating Oracle 11g R2 RAC data to a single-instance database via Rman