oracle10g迁移到11g并配置dataguard

Oracle11g dataguard安装配置

角色	Ip	Sid	Service_name
primary	172.18.83.251	nis	nis
standby	172.18.83.252	nis	nisdg

创建用户和组

groupadd oinstall #创建用户组oinstall

groupadd dba #创建用户组dba

useradd -g oinstall -g dba -m oracle #创建用户oracle，并加入oinstall和dba组

passwd oracle #设置用户oracle的登录密码，根据提示输入两次密码

创建oracle安装目录

mkdir -p /oracle/oraInventory #oracle数据库配置目录

设置目录所有者

chown -R oracle:oinstall /oracle #设置目录所有者为oinstall用户组的oracle用户

chown -R oracle:oinstall /oracle/oraInventory

修改系统标识

vim /etc/redhat-release #编辑

把

CentOS Linux release 7.0.1406 (Core)

修改为

#CentOS Linux release 7.0.1406 (Core)

Red Hat Enterprise Linux 6

修改系统内核

vim /etc/sysctl.conf #编辑，在最后添加以下代码

net.ipv4.icmp_echo_ignore_broadcasts = 1

net.ipv4.conf.all.rp_filter = 1

fs.file-max = 6815744 #设置最大打开文件数

fs.aio-max-nr = 1048576

kernel.shmall = 2097152 #共享内存的总量，8G内存设置：2097152*4k/1024/1024

kernel.shmmax = 2147483648 #最大共享内存的段大小

kernel.shmmni = 4096 #整个系统共享内存端的最大数

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500 #可使用的IPv4端口范围

net.core.rmem_default = 262144

net.core.rmem_max= 4194304

net.core.wmem_default= 262144

net.core.wmem_max= 1048576

net.ipv4.icmp_echo_ignore_broadcasts = 1

net.ipv4.conf.all.rp_filter = 1

fs.file-max = 6815744

fs.aio-max-nr = 1048576

kernel.shmall = 2097152

kernel.shmmax = 2147483648

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= 1048576

设置oracle用户限制，提高软件运行性能

vim /etc/security/limits.conf #在末尾添加以下代码

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

配置oracle用户环境变量

vim /home/oracle/.bash_profile #在最后添加以下代码

export ORACLE_BASE=/oracle #oracle数据库安装目录

export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 #oracle数据库路径

export ORACLE_SID=nis #oracle启动数据库实例名

export ORACLE_UNQNAME=$ORACLE_SID

export PATH=$ORACLE_HOME/bin:/user/sbin:$PATH #添加系统环境变量

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH #添加系统环境变量

source /home/oracle/.bash_profile #使设置立刻生效

安装oracle数据库需要的软件包

yum install -y gcc* gcc-* gcc-c++-* glibc-devel-* glibc-headers-* compat-libstdc* libstdc* elfutils-libelf-devel* libaio-devel* sysstat* unixODBC-* pdksh-* libXp.i686 libXp-devel.i686 libXt.i686 libXt-devel.i686 libXtst.i686 libXtst-devel.i686 make.x86_64 gcc.x86_64 libaio.x86_64 glibc-devel.i686 libgcc.i686 glibc-devel.x86_64 compat-libstdc++-33 glibc* gcc* make* compat-db* libstdc* libXp* libXtst* compat-libstdc++*

--centos6.5需要安装

yum install binutils compat-libstdc++-33 compat-libstdc++-33.i686 elfutils-libelf elfutils-libelf-devel gcc gcc-c++ glibc glibc.i686 glibc-common glibc-devel glibc-devel.i686 glibc-headers ksh libaio libaio.i686 libaio-devel libaio-devel.i686 libgcc libgcc.i686 libstdc++ libstdc++.i686 libstdc++-devel make sysstat unixODBC unixODBC-devel -y

重启下系统以oracle的身份登录系统

解压oracle下载的两个文件

unzip linux.x64_11gR2_database_1of2.zip

unzip linux.x64_11gR2_database_2of2.zip

cd database

为防止乱码，运行

export LANG=en_US

运行oracle安装程序

./runInstaller

xhost +

access control disabled, clients can connect from any host

[[email protected] ~]# su - oracle

Last login: Wed Mar 29 10:15:50 CST 2017 on pts/1

[[email protected] ~]$ bash

[[email protected] ~]$ export DISPLAY=display_xwindow_ip:0.0

[[email protected] ~]$ csh

[[email protected] ~]$ setenv DISPLAY display_xwindow_ip:0.0

安装的时候直接用oracle用户登录,不要su – oracle

报错：Exception String: Error in invoking target 'install' of makefile '/data/product/11.2.0/db_1/ctx/lib/ins_ctx.mk'. See '/data/oraInventory/logs/installActions2017-04-13_01-08-11PM.log' for details.

yum -y install glibc-static.x86_64

yum -y install glibc-static.i686

修改/data/product/11.2.0/dbhome_1/ctx/lib/ins_ctx.mk，将

ctxhx: $(CTXHXOBJ)

$(LINK_CTXHX) $(CTXHXOBJ) $(INSO_LINK)

修改为：

ctxhx: $(CTXHXOBJ)

-static $(LINK_CTXHX) $(CTXHXOBJ) $(INSO_LINK) /usr/lib64/stdc.a

点击Retry继续安装。接着又提示”Error in invoking target 'agent nmhs' of makefile '/data/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk.' ，解决方法：在makefile中添加链接libnnz11库的参数

修改/data/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk，将

$(MK_EMAGENT_NMECTL)修改为：$(MK_EMAGENT_NMECTL) -lnnz11

点击Retry继续安装。

安装成功！

使用root用户执行以下命令后再点击ok

/oracle/oraInvertory/orainstRoot.sh

/oracle/product/11.2.0/dbhome_1/root.sh

更改字符集：

SQL> shutdown immediate

SQL> startup

SQL> alter session set sql_trace=true;

SQL> alter system enable restricted session;

SQL> show parameter job_queue_processes;

SQL> alter system set job_queue_processes=0;

SQL> alter system set aq_tm_processes=0;

SQL> alter database open;

SQL> alter database character set INTERNAL_USE AL32UTF8;

SQL> update props$ set VALUE$='UTF8' where NAME='NLS_NCHAR_CHARACTERSET';

维护完以后需要

SQL>ALTER SYSTEM DISABLE RESTRICTED SESSION;

shutdown immediate;

startup;

/*第1步：创建临时表空间 */

create temporary tablespace iyunv_temp

tempfile '/data/oradata/nis/iyunv_temp/iyunv_temp.dbf'

size 50m

autoextend on

next 50m maxsize unlimited

extent management local;

/*第2步：创建数据表空间 */

create tablespace b2b2table

logging

datafile '/data/oradata/nis//b2b/b2b2table.dbf'

size 50m

autoextend on

next 50m maxsize unlimited

extent management local

segment space management auto;

删除表空间

drop tablespace b2b2table;

drop tablespace b2b2table including contents and datafiles;

/*第3步：创建用户并指定表空间 */

create user iyunv identified by iyunv

default tablespace b2b2table

temporary tablespace iyunv_temp;

用户已存在：

alter user iyunv temporary tablespace iyunv_temp;

alter user iyunv default tablespace b2b2table;

/*第4步：给用户授予权限 */

grant connect,resource,dba to iyunv;

创建expdp

create directory dump_dir as '/data/temp/';

grant read,write on directory dump_dir to iyunv;

导出数据

expdp iyunv/[email protected] schemas=iyunv dumpfile=iyunv20170330.dmp directory=dump_dir;

数据导入

impdp iyunv/[email protected] directory=dump_dir dumpfile=iyunv20170330.dmp schemas=iyunv;

主备机都更改一下db_files

Shutdown immediate;

startup nomount;

alter system set db_files=500 scope=spfile;

shutdown immediate;

startup

主库：

配置为归档模式

startup mount

alter database archivelog;

查看是否配置成功

archive log list;

配置强制记录日志

alter database force logging;

检查状态(YES为强制)：

sql>select name,force_logging from v$database;

创建standby log files(备用日志文件）

alter database add standby logfile group 11 '/data/dg/standby11.log' size 50M;

alter database add standby logfile group 12 '/data/dg/standby12.log' size 50M;

alter database add standby logfile group 13 '/data/dg/standby13.log' size 50M;

alter database add standby logfile group 14 '/data/dg/standby14.log' size 50M;

show parameter REMOTE_LOGIN_PASSWORDFILE

设置db_unique_name:

show parameter db_unique_name;

alter system set db_unique_name=nisdg scope=spfile;

配置闪回区

查看是否启用，默认是不开启的

sql>select flashback_on from v$database;

开启：

sql>alter database flashback on;

alter system set db_recovery_file_dest='/data/fast_recovery_area';

更改大小：

sql>alter system set db_recovery_file_dest_size=100G;

配置监听

[[email protected] admin]$ cat listener.ora

# listener.ora Network Configuration File: /data/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER=

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = nis)

(ORACLE_HOME = /data/product/11.2.0/db_1)

(SID_NAME = nis)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

ADR_BASE_LISTENER = /data

配置tnsnames

主

[[email protected] admin]$ cat listener.ora

# listener.ora Network Configuration File: /data/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER=

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = nis)

(ORACLE_HOME = /data/product/11.2.0/db_1)

(SID_NAME = nis)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

ADR_BASE_LISTENER = /data

[[email protected] admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /data/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

NIS =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = nis)

)

NISDG =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = nisdg)

)

备机

[[email protected] admin]# cat listener.ora

# listener.ora Network Configuration File: /data/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER=

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = nisdg)

(ORACLE_HOME = /data/product/11.2.0/db_1)

(SID_NAME = nis)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

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

)

ADR_BASE_LISTENER = /data

[[email protected] admin]# cat tnsnames.ora

# tnsnames.ora Network Configuration File: /data/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

NIS =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = nis)

)

NISDG =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = nisdg)

)

测试连通性

先关闭防火墙

systemctl stop firewalld.service

systemctl disable firewalld.service

systemctl status firewalld.service

[[email protected] ~]$ tnsping nis

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 05-APR-2017 14:21:48

Used parameter files:

/data/product/11.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbzhu)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nis)))

OK (0 msec)

[[email protected] ~]$ tnsping nisdg

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 05-APR-2017 14:21:52

Used parameter files:

/data/product/11.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dbbak)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = nisdg)))

OK (10 msec)

重做日志传输配置

alter system set log_archive_dest_1='LOCATION=/data/dg valid_for=(all_logfiles,primary_role) db_unique_name=nis' scope=spfile;

配置重做日志到备份库：

alter system set log_archive_dest_2='SERVICE=nisdg lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=nisdg ' scope=spfile;

配置FAL_SERVER

alter system set FAL_SERVER='nisdg';

Data Guard 配置里的另外一个库的名字

alter system set log_archive_config = 'dg_config=(nis,nisdg)';

在主库创建控制文件并复制到备机

alter database create standby controlfile as '/data/oradata/nis/standby_control01.ctl';

分别复制到备机并重命名

/data/oradata/nis/

control01.ctl

/data/flash_recovery_area/nis

control02.ctl

把主机的密码控制文件传输到备机

[[email protected] dbs]$ pwd

/data/product/11.2.0/db_1/dbs

scp orapwnis [email protected]:/data/product/11.2.0/db_1/dbs/

手工修改pfile

create pfile='/tmp/2.txt' from spfile;

shutdown immediate

修改pfile文件的内容

[[email protected] flash_recovery_area]$ cat /tmp/2.txt

nis.__db_cache_size=654311424

nis.__java_pool_size=16777216

nis.__large_pool_size=16777216

nis.__oracle_base='/data'#ORACLE_BASE set from environment

nis.__pga_aggregate_target=637534208

nis.__sga_target=956301312

nis.__shared_io_pool_size=0

nis.__shared_pool_size=251658240

nis.__streams_pool_size=0

*.audit_file_dest='/data/admin/nis/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/data/oradata/nis/control01.ctl','/data/flash_recovery_area/nis/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='nis'

*.db_recovery_file_dest='/data/fast_recovery_area'

*.db_recovery_file_dest_size=107374182400

*.diagnostic_dest='/data'

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

*.fal_server='nisdg'

*.log_archive_config='dg_config=(nis,nisdg)'

*.log_archive_dest_1='LOCATION=/data/dg valid_for=(all_logfiles,all_roles) db_unique_name=nis'

*.log_archive_dest_2='SERVICE=nisdg lgwr sync valid_for=(online_logfile, all_roles) db_unique_name=nisdg '

*.memory_target=1582301184

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.db_unique_name='nis'

*.archive_lag_target=1800

*.fal_client='nis'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_archive_format='%t_%s_%r.dbf'

*.standby_file_management='auto'

*.db_file_name_convert='/data/flash_recovery_area',' /data/flash_recovery_area'

*.log_file_name_convert='/data/flash_recovery_area','/data/flash_recovery_area'

利用pfile文件创建spfile文件

create spfile from pfile='/tmp/2.txt';

startup

在备机操作：

[[email protected] data]$ cat /tmp/2.txt

nis.__db_cache_size=654311424

nis.__java_pool_size=16777216

nis.__large_pool_size=16777216

nis.__oracle_base='/data'#ORACLE_BASE set from environment

nis.__pga_aggregate_target=637534208

nis.__sga_target=956301312

nis.__shared_io_pool_size=0

nis.__shared_pool_size=251658240

nis.__streams_pool_size=0

*.audit_file_dest='/data/admin/nis/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/data/oradata/nis/control01.ctl','/data/flash_recovery_area/nis/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='nis'

*.db_recovery_file_dest='/data/fast_recovery_area'

*.db_recovery_file_dest_size=107374182400

*.diagnostic_dest='/data'

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

*.fal_server='nis'

*.log_archive_config='dg_config=(nis,nisdg)'

*.log_archive_dest_1='LOCATION=/data/dg valid_for=(all_logfiles,all_roles) db_unique_name=nisdg'

*.log_archive_dest_2='SERVICE=nis lgwr sync valid_for=(online_logfile,all_roles db_unique_name=nis '

*.memory_target=1582301184

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.db_unique_name='nisdg'

*.archive_lag_target=1800

*.fal_client='nisdg'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='enable'

*.log_archive_format='%t_%s_%r.dbf'

*.standby_file_management='auto'

*.db_file_name_convert='/data/flash_recovery_area',' /data/flash_recovery_area'

*.log_file_name_convert='/data/flash_recovery_area','/data/flash_recovery_area'

利用pfile文件创建spfile文件

shutdown immediate

create spfile from pfile='/tmp/2.txt';

startup

启动备库到standby管理模式

shutdown immediate

startup nomount;

alter database mount standby database;

alter database recover managed standby database disconnect from session;

dataguard启动关闭顺序

<1> 监听先启从库再起主库 #lsnrctl start

<2> 启动

先启从库：

sql>startup nomount

sql>alter database mount standby database;

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

再启主库

sql>startup

<3> 关闭：和开启正好相反

先关主库：

sql>shutdown immediate

再关从库：

sql>alter database recover managed standby database cancel;

sql>shutdown immediate;

删除某用户下所有表的数据

Select 'Truncate Table USER.' || Table_Name || ''

From Dba_Tables

Where Owner = 'IYUNV';

导入数据

impdp iyunv/[email protected] directory=dump_dir dumpfile=iyunv20170330.dmp schemas=iyunv table_exists_action=replace;

主库rman备份

rman target /

shutdown immediate

startup mount

run
{
allocate channel c1 device type disk format '/data/dg/datafile/full_%u_%s_%p';
backup database plus archivelog;
}

run
{
allocate channel c1 device type disk format '/data/dg/archive/control%u_%s_%p';
backup current controlfile for standby;
}

备份数据的时候如果出现归档日志被删除的情况可以先检查归档日志

change archivelog all crosscheck;

在备库恢复数据

恢复控制文件

shutdown immediate

startup nomount

restore controlfile from '/data/dg/archive/control1js1e03a_51_1';

备份启动到mount状态下，恢复数据文件

alter database mount;

run{

restore database;

recover database;

};

或者这样备份：

备份数据库(备份文件在闪恢复区)

[[email protected] dbs]$ rman target /

RMAN> backup database plus archivelog;

RMAN> backup current controlfile for standby;

恢复数据库：

恢复前查询一下db_files

show parameter db_files;

修改db_files

alter system set db_files=1000 scope=spfile;

Shutdown immediate

Startup

恢复是主机是startup状态，备机是startup nomount状态。

[[email protected] ~]$ rman target sys/[email protected] auxiliary /

RMAN> duplicate target database for standby nofilenamecheck;

错误解决：

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/oracle/app/oradata/phoneclient/system01.dbf'

解决方法：

SQL> RECOVER DATAFILE '/oracle/app/oradata/phoneclient/system01.dbf '

Media recovery complete.

SQL> recover tablespace system;

Media recovery complete.

SQL> RECOVER DATABASE;

Media recovery complete.

SQL> ALTER DATABASE OPEN;

Database altered.

问题：

ORACLE Instance nis - Archival Error

ORA-16014: log 12 sequence# 31 not archived, no available destinations

ORA-00312: online log 12 thread 1: '/data/dg/standby12.log'

Errors in file /data/diag/rdbms/nisdg/nis/trace/nis_arc1_100783.trc:

ORA-16014: log 12 sequence# 31 not archived, no available destinations

ORA-00312: online log 12 thread 1: '/data/dg/standby12.log'

Fri Apr 14 19:13:42 2017

Archiver process freed from errors. No longer stopped

解决方法

log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=nisdg';

----生产库配置

log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,primary_role) db_unique_name=nisdg;

----我的配置

更改序列的起始值。

现在的值13985285更改成13985371（13985371-13985285）

Alter Sequence HITDETAIL_SEQ Increment By 86;

Select HITDETAIL_SEQ.nextval FROM dual;

Alter Sequence HITDETAIL_SEQ Increment By 1;

oracle10g迁移到11g并配置dataguard

Fastdfs安装配置

SQL Server 2012 AllwaysOn高可用部署

sky