7390161af342dd5f0c.png (17.08 KB, 下载次数: 236)
2021-12-7 18:15 上传
2、磁盘处理2.1、划分磁盘空间
#fdisk/dev/sdb
为meddocbak备库划分20g.
[root@bigdata~]# partprobe
[root@bigdata~]# lsblk -p
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
/dev/sdb 8:16 0 100G 0 disk
├─/dev/sdb2 8:18 0 30G 0 part
│└─/dev/mapper/meddocdg-meddocdg 252:2 0 30G 0lvm /meddocdg
├─/dev/sdb3 8:19 0 20G 0 part
└─/dev/sdb1 8:17 0 50G 0 part
└─/dev/mapper/hisorcldg-hisorcldg 252:3 0 50G 0 lvm /hisorcldg
/dev/sr0 11:0 1 4.5G 0 rom
/dev/sda 8:0 0 100G 0 disk
├─/dev/sda2 8:2 0 98G 0 part
│├─/dev/mapper/ol-swap 252:1 0 16G 0lvm [SWAP]
│└─/dev/mapper/ol-root 252:0 0 82G 0lvm /
└─/dev/sda1 8:1 0 2G 0 part /boot
#pvcreate /dev/sdb3 #创建pv
#vgcreate -s 4M orcl152 /dev/sdb3 #创建vgorcl152
# lvcreate -l 100%vg -n orcl152 orcl152 #创建lv orcl152
#mkfs.xfs /dev/orcl152/orcl152
2.2、挂载
创建挂载点：
#mkdir /orcl152
#chown -R oracle:oinstall /orcl152
#blkid |grep orcl152
/dev/mapper/orcl152-orcl152:UUID="7cd288a4-84b9-4d2a-b5bb-7d89815cfd54" TYPE="xfs"
#vim /etc/fstab
添加以下:
UUID="7cd288a4-84b9-4d2a-b5bb-7d89815cfd54" /orcl152 xfs defaults 0 0
#mount -a
3、主库操作3.1、归档情况
> archive log list;
>selectforce_logging from v$database;
3.2、查看STANDBY日志文件SQL> selectthread#,group#,members,bytes/1024/1024 as M from v$log;
THREAD# GROUP# MEMBERS M
---------- ---------- ---------- ----------
1 1 1 50
1 2 1 50
1 3 1 50
SQL> select group#,bytes/1024/1024 as M fromv$standby_log;
GROUP# M
---------- ----------
11 50
12 50
13 50
14 50
Database altered.
3.3、修改参数文件#设置log_archive_config
> alter system setlog_archive_config='dg_config=(orcl150,orcl151,orcl152)';
#设置备库归档目的地
> alter system setlog_archive_dest_3='service=orcl152 lgwr asyncVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl152';
3.4、拷贝参数文件
用spfile文件创建pfile 文件：
> create pfile='/home/oracle/pfileorcl152.ora'from spfile;
$ ll
total 4
drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Desktop
drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Documents
drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Downloads
drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Music
-rw-r--r-- 1 oracle oinstall 2129 Dec 1 10:19 pfileorcl152.ora
drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Pictures
drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Public
drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Templates
drwxr-xr-x 2 oracle oinstall 6 Nov 6 23:46 Videos
$ scp pfileorcl152.oraoracle@192.168.133.226:/u01/app/oracle/product/11.2.0/db_1/dbs
3.5、拷贝密码文件$ scp orapworcl150oracle@192.168.133.226:/u01/app/oracle/product/11.2.0/db_1/dbs
3.6、修改TNS 配置文件$ vim tnsnames.ora
$ cat 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.
ORCL150 =
(DESCRIPTION=
(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.133.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER= DEDICATED)
(SERVICE_NAME = orcl150)
)
)
ORCL151 =
(DESCRIPTION=
(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.133.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER= DEDICATED)
(SERVICE_NAME = orcl151)
)
)
ORCL152 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.226)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl152)
)
)
#蓝色为修改部分
3.7、重启监听服务$ lsnrctl stop
$ lsnrctl start
4、备库操作4.1、添加实例名
$ vi /etc/oratab
添加以下：
orcl152:/u01/app/oracle/product/11.2.0/db_1:N
$ export ORACLE_SID=orcl152
4.2、重命名文件
[oracle@bigdata dbs]$ ll
total 60
-rw-rw---- 1 oracle oinstall 1544 Dec 1 00:15 hc_hisorcldg.dat
-rw-rw---- 1 oracle oinstall 1544 Dec 1 09:20 hc_meddocdg.dat
-rw-r--r-- 1 oracle oinstall 1744 Nov 29 22:16inithisorcldg.ora
-rw-r--r-- 1 oracle oinstall 1675 Nov 30 14:42initmeddocdg.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r----- 1 oracle oinstall 24 Nov 30 00:15 lkHISORCLDG
-rw-r----- 1 oracle oinstall 24 Nov 30 15:16 lkMEDDOCDG
-rw-r----- 1 oracle oinstall 1536 Nov 30 00:15orapwhisorcldg
-rw-r----- 1 oracle oinstall 1536 Nov 30 15:16orapwmeddocdg
-rw-r----- 1 oracle oinstall 1536 Dec 1 10:26 orapworcl150
-rw-r--r-- 1 oracle oinstall 2129 Dec 1 10:24 pfileorcl152.ora
-rw-r----- 1 oracle oinstall 5632 Nov 30 23:37spfilehisorcldg.ora
-rw-r----- 1 oracle oinstall 5632 Dec 1 09:11 spfilemeddocdg.ora
[oracle@bigdatadbs]$ mv orapworcl150 orapworcl152
4.3、修改参数文件
[oracle@bigdata dbs]$ vi pfileorcl152.ora
*._allow_resetlogs_corruption=FALSE
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl150'
*.db_recovery_file_dest_size=4385144832
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=orcl151XDB)'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.memory_target=3217031168
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.log_archive_config='dg_config=(orcl150,orcl152)'
*.control_files='/u01/app/oracle/oradata/orcl152/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl152/control02.ctl'
*.audit_file_dest='/u01/app/oracle/admin/orcl152/adump'
*.db_unique_name='orcl152'
*.fal_client='orcl152'
*.fal_server='orcl150'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/orcl152/archivelog valid_for=(all_logfiles,all_roles)db_unique_name=orcl152'
*.log_archive_dest_2='service=orcl150 lgwr asyncvalid_for=(online_logfiles,primary_role) db_unique_name=orcl150'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl150/','/orcl152/app/oracle/oradata/orcl152/','/u01/oracle/','/orcl152/oracle/'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl150/','/orcl152/app/oracle/oradata/orcl152/','/u01/app/oracle/fast_recovery_area/orcl150/','/orcl152/app/oracle/fast_recovery_area/orcl152/'
#红色为修改内容
4.4、创建目录[oracle@bigdata dbs]$ mkdir -p/u01/app/oracle/oradata/orcl152/
[oracle@bigdata dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/orcl152/
[oracle@bigdata dbs]$ mkdir -p /u01/app/oracle/admin/orcl152/adump
[oracle@bigdata dbs]$ mkdir -p /u01/app/oracle/oradata/orcl152/archivelog/
[oracle@bigdata dbs]$ mkdir -p /orcl152/app/oracle/oradata/orcl152/
[oracle@bigdata dbs]$ mkdir -p /orcl152/oracle/
[oracle@bigdata dbs]$ mkdir -p /orcl152/app/oracle/oradata/orcl152/
[oracle@bigdata dbs]$ mkdir -p /orcl152/app/oracle/fast_recovery_area/orcl152/
4.5、修改监听配置文件修改监听配置文件：
[oracle@bigdata admin]$ vi listener.ora
[oracle@bigdata admin]$ cat 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.
SID_LIST_LISTENER =
(SID_LIST=
(SID_DESC =
(GLOBAL_DBNAME = hisorcldg)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = hisorcldg)
)
(SID_DESC =
(GLOBAL_DBNAME = meddocdg)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = meddocdg)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl152)
(ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl152)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.226)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
#蓝色为添加部分
4.6、修改 TNS 配置文件[oracle@bigdataadmin]$ vi tnsnames.ora
[oracle@bigdataadmin]$ cat tnsnames.ora
#tnsnames.ora Network Configuration File:/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generatedby Oracle configuration tools.
HISORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.133.227)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hisorcl)
)
)
HISORCLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.133.226)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = hisorcldg)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.133.225)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
MEDDOCDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.133.226)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = meddocdg)
)
)
ORCL152=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.133.226)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl152)
)
)
ORCL150=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.133.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl150)
)
)
备注：蓝色为添加部分
4.7、重启监听服务[oracle@bigdata admin]$ lsnrctl stop
[oracle@bigdata admin]$ lsnrctl start
4.8、备库启动到nomount 状态
[oracle@bigdata admin]$ echo $ORACLE_SID
orcl152
[oracle@bigdata admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on WedDec 1 11:51:36 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomountpfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfileorcl152.ora';
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 1811942800 bytes
Database Buffers 1375731712 bytes
Redo Buffers 16904192 bytes
SQL> create spfile frompfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfileorcl152.ora';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size 2257520 bytes
Variable Size 1811942800 bytes
Database Buffers 1375731712 bytes
Redo Buffers 16904192 bytes
5、验证监听和TNS配置
主库：
ping 192.168.133.226
tnsping orcl152
备库：
ping 192.168.133.150
tnsping orcl150
注：测试OK后，执行以下：
5.1、主库验证$ sqlplus sys/oracle_4U@192.168.133.150:1521/orcl150as sysdba
> show parameter db_unique_name;
$ sqlplus sys/oracle_4U@192.168.133.226:1521/orcl152as sysdba
5.2、备库验证$ export ORACLE_SID=orcl152
$ sqlplus sys/oracle_4U@192.168.133.226:1521/orcl152as sysdba
$ sqlplussys/oracle_4U@192.168.133.150:1521/orcl150 as sysdba
备注：在主备库上都能测试通过的条件下才执行下面步骤
6、恢复6.1、恢复数据库
注：确认备库nomount
然后执行以下：
[oracle@bigdata dbs]$rman target sys/oracle_4U@orcl150auxiliary sys/oracle_4U@orcl152
Recovery Manager: Release 11.2.0.4.0 - Productionon Wed Dec 1 12:53:27 2021
Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.
connected to target database: ORCL150(DBID=4034131047)
connected to auxiliary database: ORCL150 (notmounted)
RMAN> duplicate target database for standbyfrom active database;
Starting Duplicate Db at 01-DEC-21
using target database control file instead ofrecovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=193 device type=DISK
contents of Memory Script:
{
backupas copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl150' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapworcl152' ;
}
executing Memory Script
Starting backup at 01-DEC-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
Finished backup at 01-DEC-21
contents of Memory Script:
{
backupas copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/orcl152/control01.ctl';
restoreclone controlfile to '/u01/app/oracle/fast_recovery_area/orcl152/control02.ctl' from
'/u01/app/oracle/oradata/orcl152/control01.ctl';
}
executing Memory Script
Starting backup at 01-DEC-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl150.ftag=TAG20211201T130321 RECID=17 STAMP=1090155801
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:01
Finished backup at 01-DEC-21
Starting restore at 01-DEC-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 01-DEC-21
contents of Memory Script:
{
sqlclone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standbydatabase
contents of Memory Script:
{
set newnamefor tempfile 1 to
"/orcl152/app/oracle/oradata/orcl152/temp01.dbf";
switchclone tempfile all;
setnewname for datafile 1 to
"/orcl152/app/oracle/oradata/orcl152/system01.dbf";
setnewname for datafile 2 to
"/orcl152/app/oracle/oradata/orcl152/sysaux01.dbf";
setnewname for datafile 3 to
"/orcl152/app/oracle/oradata/orcl152/undotbs01.dbf";
setnewname for datafile 4 to
"/orcl152/app/oracle/oradata/orcl152/users01.dbf";
set newnamefor datafile 5 to
"/orcl152/app/oracle/oradata/orcl152/example01.dbf";
setnewname for datafile 6 to
"/orcl152/oracle/audit01.dbf";
backupas copy reuse
datafile 1 auxiliary format
"/orcl152/app/oracle/oradata/orcl152/system01.dbf" datafile
2auxiliary format
"/orcl152/app/oracle/oradata/orcl152/sysaux01.dbf" datafile
3auxiliary format
"/orcl152/app/oracle/oradata/orcl152/undotbs01.dbf" datafile
4auxiliary format
"/orcl152/app/oracle/oradata/orcl152/users01.dbf" datafile
5auxiliary format
"/orcl152/app/oracle/oradata/orcl152/example01.dbf" datafile
6auxiliary format
"/orcl152/oracle/audit01.dbf" ;
sql'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /orcl152/app/oracle/oradata/orcl152/temp01.dbfin control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 01-DEC-21
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001name=/u01/app/oracle/oradata/orcl150/system01.dbf
output file name=/orcl152/app/oracle/oradata/orcl152/system01.dbftag=TAG20211201T130329
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002name=/u01/app/oracle/oradata/orcl150/sysaux01.dbf
output file name=/orcl152/app/oracle/oradata/orcl152/sysaux01.dbftag=TAG20211201T130329
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005name=/u01/app/oracle/oradata/orcl150/example01.dbf
output file name=/orcl152/app/oracle/oradata/orcl152/example01.dbftag=TAG20211201T130329
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003name=/u01/app/oracle/oradata/orcl150/undotbs01.dbf
output file name=/orcl152/app/oracle/oradata/orcl152/undotbs01.dbftag=TAG20211201T130329
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006name=/u01/oracle/audit01.dbf
output file name=/orcl152/oracle/audit01.dbftag=TAG20211201T130329
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004name=/u01/app/oracle/oradata/orcl150/users01.dbf
output file name=/orcl152/app/oracle/oradata/orcl152/users01.dbftag=TAG20211201T130329
channel ORA_DISK_1: datafile copy complete,elapsed time: 00:00:01
Finished backup at 01-DEC-21
sql statement: alter system archive log current
contents of Memory Script:
{
switchclone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=17 STAMP=1090155926file name=/orcl152/app/oracle/oradata/orcl152/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=18 STAMP=1090155926file name=/orcl152/app/oracle/oradata/orcl152/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=19 STAMP=1090155926file name=/orcl152/app/oracle/oradata/orcl152/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=20 STAMP=1090155926file name=/orcl152/app/oracle/oradata/orcl152/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=21 STAMP=1090155926file name=/orcl152/app/oracle/oradata/orcl152/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=22 STAMP=1090155926file name=/orcl152/oracle/audit01.dbf
Finished Duplicate Db at 01-DEC-21
6.2、开启实时同步
备库执行：
> alter database open;
> select status from v$instance;
> select database_role,open_mode fromv$database;
DATABASE_ROLE OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY WITH APPLY
> alter database recover managed standbydatabase using current logfile disconnect from session;
7、验证同步7.1、最大归档序号主库执行：
SQL> select max(sequence#) fromv$archived_log;
MAX(SEQUENCE#)
--------------
39
备库验证：
SQL> select max(sequence#) fromv$archived_log;
MAX(SEQUENCE#)
--------------
39
7.2、日志切换主库执行：
SQL> alter system archive log current;
System altered.
SQL> select max(sequence#) fromv$archived_log;
MAX(SEQUENCE#)
--------------
40
备库验证：
SQL> select max(sequence#) fromv$archived_log;
MAX(SEQUENCE#)
--------------
40
7.3、主备库状态主库执行：
SQL> select switchover_status,database_rolefrom v$database;
SWITCHOVER_STATUS DATABASE_ROLE
------------------------------------------------------------------------
SESSIONS ACTIVE PRIMARY
备库执行：
SQL>selectswitchover_status,database_role from v$database;
SWITCHOVER_STATUS DATABASE_ROLE
------------------------------------------------------------------------
NOT ALLOWED PHYSICAL STANDBY
7.4、创建表测试主库执行：
SQL> create table dg(id number);
SQL> insert into dg values(1);
SQL> commit;
SQL> select * from dg;
ID
----------
1
备库验证：
SQL> select * from dg;
ID
----------
1
DG 同步，DG搭建结束.
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |