Set pagesize 300
Set linesize 300
col file_name format a60
select file_id,tablespace_name,file_name,bytes/1024/1024,status,autoextensible,maxbytes/1024/1024 from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024 STATUS AUT MAXBYTES/1024/1024
---------- ---------------------- ------------------------------------------ --------------- --------- --- ------------------
1 SYSTEM +DATA1/pdb/datafile/system.263.949941747 32760 AVAILABLE NO 0
2 SYSAUX +DATA1/pdb/datafile/sysaux.264.949941811 16380 AVAILABLE NO 0
3 UNDOTBS1 +DATA1/pdb/datafile/undotbs1.265.949941843 32760 AVAILABLE NO 0
4 UNDOTBS2 +DATA1/pdb/datafile/undotbs2.267.949941913 32760 AVAILABLE NO 0
5 USERS +DATA1/pdb/datafile/users.268.949941975 5110 AVAILABLE NO 0
6 TABLE +DATA1/pdb/datafile/table.276.950884821 61440 AVAILABLE YES 102400
7 INDEX +DATA1/pdb/datafile/index.277.950885251 573440 AVAILABLE YES 716800
8 APP +DATA1/pdb/datafile/app.278.950886647 7434240 AVAILABLE YES 9420800
9 UNDOTBS1 +DATA1/pdb/datafile/undotbs1.279.964343175 32000 AVAILABLE NO 0
源端获取USER和权限，根据具体情况改查询条件
--CREATE_USER_DDL
select to_char(dbms_metadata.get_ddl('USER',USERNAME)) CREATE_USER_DDL from dba_users where ACCOUNT_STATUS in ('OPEN','EXPIRED(GRACE)' and USERNAME not in ('SYS','SYSTEM');
--GRANT_ROLE_DDL
select to_char(dbms_metadata.get_granted_ddl('ROLE_GRANT',GRANTEE)) GRANT_ROLE_DDL from dba_role_privs where GRANTEE in (select USERNAME from dba_users where ACCOUNT_STATUS in ('OPEN','EXPIRED(GRACE)' and USERNAME not in ('SYS','SYSTEM'));
--default_tablespace
select 'alter user '||username||' default tablespace '||default_tablespace||';' from dba_users where ACCOUNT_STATUS in ('OPEN','EXPIRED(GRACE)') and USERNAME not in ('SYS','SYSTEM');
建议源端清理并关闭回收站
select OWNER,TYPE,count(1) from dba_recyclebin group by OWNER,TYPE order by OWNER;
show parameter recyclebin
alter systemn set recyclebin='off' scope=both;
purge recyclebin;
源端自包含检查
execute sys.dbms_tts.transport_set_check('USERS,1,2,3', true);
select * from sys.transport_set_violations;
源端创建测试数据，可用于验证同步情况
create user xttst identified by oracle default tablespace USERS;
grant connect,resource to xttst;
create table xttst.xtts (aa date);
insert into xttst.xtts values (sysdate);
commit;
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
select * from xttst.xtts;
源端开启块跟踪
alter database enable block change tracking using file '+DATA1/pdb/block_change_tracking.log';
**目标端创建PDB **
SQL> alter pluggable database pdb close instances=all;
SQL> drop pluggable database pdb including datafiles;
SQL> create pluggable database pdb admin user admin identified by admin;
SQL> alter pluggable database pdb open instances=all;
目标端配置NFS server挂载到源端，用于存放源端的RMAN备份文件
# upRescan --华为多路径，系统重新识别新挂载的磁盘
# upadmin show vlun --查看新磁盘
56 sdbg 0604_shujuku0001 6d4b1101003bc9dae2d1e6b6 Normal 500.00GB 0B/0B Huawei.Storage 356 24/24
57 sdbh 0604_shujuku0002 6d4b1101003bc9dae2d1e70e Normal 500.00GB 0A/0A Huawei.Storage 357 24/24
58 sdbi 0604_shujuku0003 6d4b1101003bc9dae2d1e754 Normal 500.00GB 0B/0B Huawei.Storage 358 24/24
59 sdbj 0604_shujuku0004 6d4b1101003bc9dae2d1e84b Normal 500.00GB 0A/0A Huawei.Storage 359 24/24
60 sdbk 0604_shujuku0005 6d4b1101003bc9dae2d1e899 Normal 500.00GB 0B/0B Huawei.Storage 360 24/24
61 sdbl 0604_shujuku0006 6d4b1101003bc9dae2d1e8ec Normal 500.00GB 0A/0A Huawei.Storage 361 24/24
62 sdbm 0604_shujuku0007 6d4b1101003bc9dae2d1e939 Normal 500.00GB 0B/0B Huawei.Storage 362 24/24
63 sdbn 0604_shujuku0008 6d4b1101003bc9dae2d1e984 Normal 500.00GB 0A/0A Huawei.Storage 363 24/24
64 sdbo 0604_shujuku0009 6d4b1101003bc9dae2d1e9e7 Normal 500.00GB 0B/0B Huawei.Storage 364 24/24
65 sdbp 0604_shujuku0010 6d4b1101003bc9dae2d1ea33 Normal 500.00GB 0A/0A Huawei.Storage 365 24/24
66 sdbq 0604_shujuku0011 6d4b1101003bc9dae2d1ea7f Normal 500.00GB 0B/0B Huawei.Storage 366 24/24
67 sdbr 0604_shujuku0012 6d4b1101003bc9dae2d1eae4 Normal 500.00GB 0A/0A Huawei.Storage 367 24/24
68 sdbs 0604_shujuku0013 6d4b1101003bc9dae2d1eb2a Normal 500.00GB 0B/0B Huawei.Storage 368 24/24
69 sdbt 0604_shujuku0014 6d4b1101003bc9dae2d1eb7a Normal 500.00GB 0A/0A Huawei.Storage 369 24/24
70 sdbu 0604_shujuku0015 6d4b1101003bc9dae2d1ebe3 Normal 500.00GB 0B/0B Huawei.Storage 370 24/24
71 sdbv 0604_shujuku0016 6d4b1101003bc9dae2d1ec24 Normal 500.00GB 0A/0A Huawei.Storage 371 24/24
72 sdbw 0604_shujuku0017 6d4b1101003bc9dae2d1ec88 Normal 500.00GB 0B/0B Huawei.Storage 372 24/24
73 sdbx 0604_shujuku0018 6d4b1101003bc9dae2d1ece7 Normal 500.00GB 0A/0A Huawei.Storage 373 24/24
vi /etc/udev/rules.d/99-oracle-asmdevices.rules
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1e6b6", SYMLINK+="asm-tts01", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1e70e", SYMLINK+="asm-tts02", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1e754", SYMLINK+="asm-tts03", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1e84b", SYMLINK+="asm-tts04", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1e899", SYMLINK+="asm-tts05", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1e8ec", SYMLINK+="asm-tts06", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1e939", SYMLINK+="asm-tts07", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1e984", SYMLINK+="asm-tts08", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1e9e7", SYMLINK+="asm-tts09", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1ea33", SYMLINK+="asm-tts10", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1ea7f", SYMLINK+="asm-tts11", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1eae4", SYMLINK+="asm-tts12", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1eb2a", SYMLINK+="asm-tts13", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1eb7a", SYMLINK+="asm-tts14", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1ebe3", SYMLINK+="asm-tts15", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1ec24", SYMLINK+="asm-tts16", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1ec88", SYMLINK+="asm-tts17", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", PROGRAM=="/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/$name", RESULT=="36d4b1101003bc9dae2d1ece7", SYMLINK+="asm-tts18", OWNER="grid", GROUP="asmadmin", MODE="0660"
/sbin/udevadm trigger --type=devices --action=change
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts01 -> sdbg
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts02 -> sdbh
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts03 -> sdbi
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts04 -> sdbj
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts05 -> sdbk
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts06 -> sdbl
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts07 -> sdbm
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts08 -> sdbn
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts09 -> sdbo
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts10 -> sdbp
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts11 -> sdbq
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts12 -> sdbr
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts13 -> sdbs
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts14 -> sdbt
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts15 -> sdbu
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts16 -> sdbv
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts17 -> sdbw
lrwxrwxrwx 1 root root 4 Jun 4 17:28 /dev/asm-tts18 -> sdbx
parted /dev/asm-pdb01 --磁盘分区
mklabel gpt
print
mkpart primary 0 537GB
Ignore
set 1 lvm on
lrwxrwxrwx 1 root root 5 Jun 4 17:31 /dev/asm-tts01 -> sdbg1
lrwxrwxrwx 1 root root 5 Jun 4 17:32 /dev/asm-tts02 -> sdbh1
lrwxrwxrwx 1 root root 5 Jun 4 17:32 /dev/asm-tts03 -> sdbi1
lrwxrwxrwx 1 root root 5 Jun 4 17:33 /dev/asm-tts04 -> sdbj1
lrwxrwxrwx 1 root root 5 Jun 4 17:33 /dev/asm-tts05 -> sdbk1
lrwxrwxrwx 1 root root 5 Jun 4 17:33 /dev/asm-tts06 -> sdbl1
lrwxrwxrwx 1 root root 5 Jun 4 17:34 /dev/asm-tts07 -> sdbm1
lrwxrwxrwx 1 root root 5 Jun 4 17:34 /dev/asm-tts08 -> sdbn1
lrwxrwxrwx 1 root root 5 Jun 4 17:35 /dev/asm-tts09 -> sdbo1
lrwxrwxrwx 1 root root 5 Jun 4 17:35 /dev/asm-tts10 -> sdbp1
lrwxrwxrwx 1 root root 5 Jun 4 17:35 /dev/asm-tts11 -> sdbq1
lrwxrwxrwx 1 root root 5 Jun 4 17:36 /dev/asm-tts12 -> sdbr1
lrwxrwxrwx 1 root root 5 Jun 4 17:36 /dev/asm-tts13 -> sdbs1
lrwxrwxrwx 1 root root 5 Jun 4 17:37 /dev/asm-tts14 -> sdbt1
lrwxrwxrwx 1 root root 5 Jun 4 17:37 /dev/asm-tts15 -> sdbu1
lrwxrwxrwx 1 root root 5 Jun 4 17:37 /dev/asm-tts16 -> sdbv1
lrwxrwxrwx 1 root root 5 Jun 4 17:37 /dev/asm-tts17 -> sdbw1
lrwxrwxrwx 1 root root 5 Jun 4 17:38 /dev/asm-tts18 -> sdbx1
--创建PV
pvcreate /dev/sdbg1
pvcreate /dev/sdbh1
pvcreate /dev/sdbi1
pvcreate /dev/sdbj1
pvcreate /dev/sdbk1
pvcreate /dev/sdbl1
pvcreate /dev/sdbm1
pvcreate /dev/sdbn1
pvcreate /dev/sdbo1
pvcreate /dev/sdbp1
pvcreate /dev/sdbq1
pvcreate /dev/sdbr1
pvcreate /dev/sdbs1
pvcreate /dev/sdbt1
pvcreate /dev/sdbu1
pvcreate /dev/sdbv1
pvcreate /dev/sdbw1
pvcreate /dev/sdbx1
--创建VG
vgcreate -s 512M xtts_vg /dev/sdbg1 /dev/sdbh1 /dev/sdbi1 /dev/sdbj1 /dev/sdbk1 /dev/sdbl1 /dev/sdbm1 /dev/sdbn1 /dev/sdbo1 /dev/sdbp1 /dev/sdbq1 /dev/sdbr1 /dev/sdbs1 /dev/sdbt1 /dev/sdbu1 /dev/sdbv1 /dev/sdbw1 /dev/sdbx1
[root@bdqsdb10 ~]# vgdisplay
--- Volume group ---
VG Name xtts_vg
System ID
Format lvm2
Metadata Areas 18
Metadata Sequence No 1
VG Access read/write
VG Status resizable
MAX LV 0
Cur LV 0
Open LV 0
Max PV 0
Cur PV 18
Act PV 18
VG Size 8.78 TiB
PE Size 512.00 MiB
Total PE 17982
Alloc PE / Size 0 / 0
Free PE / Size 17982 / 8.78 TiB
VG UUID 02yA4A-kDhW-BjdC-J37v-AVrc-d53J-Y2a15O
--创建LV
lvcreate -l 17982 -n xtts_lv xtts_vg
[root@bdqsdb10 ~]# lvdisplay
--- Logical volume ---
LV Path /dev/xtts_vg/xtts_lv
LV Name xtts_lv
VG Name xtts_vg
LV UUID ChrKQh-PCkA-Wshl-idGO-xN8l-VRBj-b3fsO8
LV Write Access read/write
LV Creation host, time bdqsdb10, 2020-06-04 17:45:44 +0800
LV Status available
# open 0
LV Size 8.78 TiB
Current LE 17982
Segments 18
Allocation inherit
Read ahead sectors auto
- currently set to 8192
Block device 253:6
[root@bdqsdb10 ~]# cat /etc/fstab
#
# /etc/fstab
# Created by anaconda on Fri Jan 10 12:16:24 2020
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/rhel-root / ext4 defaults 1 1
UUID=80679ec1-7f23-49f9-9366-85b1d8283010 /boot ext4 defaults 1 2
/dev/mapper/rhel-home /home ext4 defaults 1 2
/dev/mapper/rhel-u01 /u01 ext4 defaults 1 2
/dev/mapper/rhel-swap swap swap defaults 0 0
UUID=1f0e2e24-7eed-4bc6-9122-7dc35b481dfc /data xfs defaults 0 0
/dev/mapper/rhel-u02 /u02 ext4 defaults 1 2
tmpfs /dev/shm tmpfs defaults,size=378G 0 0
/dev/mapper/rhel-osw /osw ext4 defaults 1 2
/dev/xtts_vg/xtts_lv /data/xtts ext4 defaults 1 2
[root@bdqsdb10 ~]# mkfs.ext4 /dev/xtts_vg/xtts_lv
[root@bdqsdb10 ~]# mkdir -p /data/xtts
[root@bdqsdb10 ~]# mount /data/xtts
[root@bdqsdb10 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/rhel-root 50G 30G 17G 65% /
devtmpfs 378G 4.0K 378G 1% /dev
tmpfs 378G 7.7G 371G 3% /dev/shm
tmpfs 378G 869M 377G 1% /run
tmpfs 378G 0 378G 0% /sys/fs/cgroup
/dev/mapper/rhel-osw 30G 21G 7.1G 75% /osw
/dev/sda1 976M 141M 769M 16% /boot
/dev/mapper/rhel-home 79G 1.7G 73G 3% /home
/dev/mapper/rhel-u02 99G 16G 79G 17% /u02
/dev/sdb1 1.8T 131G 1.7T 8% /data
/dev/mapper/rhel-u01 99G 18G 76G 20% /u01
tmpfs 76G 8.0K 76G 1% /run/user/42
tmpfs 76G 64K 76G 1% /run/user/0
/dev/mapper/xtts_vg-xtts_lv 8.8T 84M 8.3T 1% /data/xtts
--配置NFS
[root@bdqsdb10 ~]# cat /etc/exports
/data/xtts 10.216.11.70(rw,sync,no_root_squash,insecure)
[root@bdqsdb10 ~]# systemctl start rpcbind.service
[root@bdqsdb10 ~]# systemctl start nfs.service
--客户端（源端）挂载
mount -t nfs -o rw,bg,hard,nointr,rsize=32768,wsize=32768,proto=tcp,noac,vers=3,timeo=600 <ip>:/data/xtts /data/xtts
源端创建数据文件副本
SQL> select * from v$block_change_tracking;
STATUS FILENAME BYTES
---------- -------------------------------------------------- ----------
ENABLED +DATA1/pdb/block_change_tracking.log 43057152
--源端查询SCN并记录，用于下次增量备份从此SCN开始
SQL> set numw 20
SQL> select min(checkpoint_change#) from v$datafile;
MIN(CHECKPOINT_CHANGE#)
-----------------------
8038806666507
--源端执行0级备份
$ more rman_cmd_full.cmd
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
allocate channel d7 type disk;
allocate channel d8 type disk;
backup incremental level 0 format '/data/xtts/full_%U' database section size 32G;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
}
nohup rman target / cmdfile=rman_cmd_full.cmd > rman_full.log 2>&1 &
目标端创建辅助实例
$ vi /u02/app/oracle/product/19.0.0/db_1/dbs/initpdb.ora
*.db_name=pdb
*.compatible=19.0.0
*.control_files='+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/current01.ctl'
*.sga_target=60212254720
*.pga_aggregate_target=12884901888
$ export ORACLE_SID=pdb
$ sqlplus / as sysdba
SQL> startup nomount force;
目标端辅助实例还原控制文件
channel d8: specifying datafile(s) in backup set
including current control file in backup set
channel d8: starting piece 1 at 06-JUN-20
channel d8: finished piece 1 at 06-JUN-20
piece handle=/data/xtts/full_cbv21mqq_1_1 tag=TAG20200605T161342 comment=NONE
channel d8: backup set complete, elapsed time: 00:00:08
restore controlfile from '/data/xtts/full_cbv21mqq_1_1';
目标端辅助实例注册备份集
catalog start with '/data/xtts'; --不太好用
catalog backuppiece '/data/xtts/full_4bv20glm_100_1';
catalog backuppiece '/data/xtts/full_4bv20glm_10_1';
目标端辅助实例还原数据文件
more rman_restore_full.cmd
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
allocate channel d7 type disk;
allocate channel d8 type disk;
set newname for datafile 1 to '+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/syst01.dbf';
set newname for datafile 2 to '+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/sysa01.dbf';
set newname for datafile 3 to '+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/undot101.dbf';
set newname for datafile 4 to '+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/undot201.dbf';
set newname for datafile 5 to '+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/use01.dbf';
set newname for datafile 6 to '+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/table01.dbf';
set newname for datafile 7 to '+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/index01.dbf';
set newname for datafile 8 to '+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/app01.dbf';
set newname for datafile 9 to '+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/undot102.dbf';
restore database;
switch datafile all;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
}
nohup rman target / cmdfile=rman_restore_full.cmd > rman_restore_full.log 2>&1 &
源端增量备份
--源端查询SCN并记录，用于下次增量备份从此SCN开始
set numw 20
select min(checkpoint_change#) from v$datafile;
MIN(CHECKPOINT_CHANGE#)
-----------------------
8038806666507 --0
8038823776619 --1 1
8038824416691 --1 2
8038839449861 --1 3
8038848949225 --1 4
8038849071638 --1 end
rman_cmd_incr_0612_end.cmd
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
allocate channel d7 type disk;
allocate channel d8 type disk;
backup incremental from scn 8038848949225 database format '/data/xtts/incr/incr_20200612_end_%U';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
}
nohup rman target / cmdfile=rman_cmd_incr_0612_end.cmd > rman_cmd_incr_0612_end.log 2>&1 &
注册备份集
catalog backuppiece '/data/xtts/incr/incr_20200612_end_eav2j5p0_1_1';
catalog backuppiece '/data/xtts/incr/incr_20200612_end_ebv2j5p0_1_1';
catalog backuppiece '/data/xtts/incr/incr_20200612_end_ecv2j5p1_1_1';
catalog backuppiece '/data/xtts/incr/incr_20200612_end_edv2j5p1_1_1';
catalog backuppiece '/data/xtts/incr/incr_20200612_end_eev2j5p1_1_1';
catalog backuppiece '/data/xtts/incr/incr_20200612_end_efv2j5p1_1_1';
恢复增量备份
rman_recover_incr_0612_zs.cmd
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
allocate channel d7 type disk;
allocate channel d8 type disk;
recover database noredo;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
}
nohup rman target / cmdfile=rman_recover_incr_0612_end.cmd > rman_recover_incr_0612_end.log 2>&1 &
数据库切割
insert test data
insert into xttst.xtts values (sysdate);
commit;
源端将表空间设置为只读
select TABLESPACE_NAME,STATUS from dba_tablespaces;
alter tablespace USERS read only;
alter tablespace TABLE read only;
alter tablespace INDEX read only;
alter tablespace APP read only;
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
UNDOTBS2 ONLINE
USERS READ ONLY
TABLE READ ONLY
INDEX READ ONLY
APP READ ONLY
执行最后一次增量备份和恢复
目标端创建用户
ALTER SESSION SET CONTAINER=pdb;
CREATE USER "USER" IDENTIFIED BY VALUES 'S:69C6467F45974A79F37D;9F29E4' DEFAULT TABLESPACE "SYSTEM" TEMPORARY TABLESPACE "TEMP";
... ...
create user xttst identified by oracle default tablespace SYSTEM;
目标端为用户赋权
GRANT "CONNECT" TO "USER1";
GRANT "RESOURCE" TO "USER1";
GRANT "DBA" TO "USER1";
GRANT "CONNECT" TO "USER2";
GRANT "RESOURCE" TO "USER2";
目标端创建DBLINK
SELECT to_char(dbms_metadata.get_ddl('DB_LINK',DB_LINK,OWNER)) DB_LINK_DDL FROM dba_db_links;
源端导出元数据
SQL> col OBJECT_PATH for a60
SQL> col COMMENTS for a60
SQL> set line 200
SQL> select object_path,comments from transportable_export_objects where object_path like 'TRANSPORTABLE_EXPORT%' and object_path not like '%PLUGTS%';
OBJECT_PATH COMMENTS
------------------------------------------------------------ ------------------------------------------------------------
TRANSPORTABLE_EXPORT/CLUSTER Clusters and their indexes
TRANSPORTABLE_EXPORT/CLUSTERING Table clustering
TRANSPORTABLE_EXPORT/CLUSTER_INDEX/INDEX Indexes on tables and clusters
TRANSPORTABLE_EXPORT/COMMENT Comments on tables
TRANSPORTABLE_EXPORT/CONSTRAINT Constraints (including referential constraints)
TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT Referential constraints
TRANSPORTABLE_EXPORT/FGA_POLICY Fine-grained auditing policies
TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS Precomputed index statistics
TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW Materialized views
TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW_LOG Materialized view logs
TRANSPORTABLE_EXPORT/MATERIALIZED_ZONEMAP Materialized zonemaps
TRANSPORTABLE_EXPORT/PARTITION/INDEX_PARTITION index Partitions
TRANSPORTABLE_EXPORT/POST_INSTANCE/GRANT/PROCDEPOBJ_GRANT Grants on instance procedural objects
TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ Instance procedural objects
TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ_AUDIT Audits on instance procedural objects
TRANSPORTABLE_EXPORT/POST_TABLE_ACTION Post-table actions
TRANSPORTABLE_EXPORT/PRE_TABLE_ACTION Pre-table actions
TRANSPORTABLE_EXPORT/PROCACT_INSTANCE Instance procedural actions
TRANSPORTABLE_EXPORT/RLS_CONTEXT Fine-grained access control contexts
TRANSPORTABLE_EXPORT/RLS_GROUP Fine-grained access control policy groups
TRANSPORTABLE_EXPORT/RLS_POLICY/RLS_POLICY Fine-grained access control policies
TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Precomputed table statistics
TRANSPORTABLE_EXPORT/TABLE Tables
TRANSPORTABLE_EXPORT/TRIGGER Table triggers
TRANSPORTABLE_EXPORT/USER_PREF_STATISTICS User pref statistics
TRANSPORTABLE_EXPORT/XMLSCHEMA XMLSCHEMAS
26 rows selected.
create directory PUMP_DIR2 as '/data/xtts/incr';
Grant read,write on directory PUMP_DIR2 to system;
$ cat xtts_pdb_expdp.par
transport_full_check=y
directory=PUMP_DIR2
EXCLUDE=TABLE_STATISTICS,INDEX_STATISTICS
dumpfile=full_pdb_20200612.dmp
logfile=expdp_full_pdb_20200612.log
cluster=N
transport_tablespaces=USERS,TABLE,INDEX,APP
expdp userid=\"/ as sysdba\" parfile=xtts_pdb_expdp.par
目标端导入元数据
create directory PUMP_DIR as '/data/xtts/incr';
Grant read,write on directory PUMP_DIR to system;
$ cat xtts_pdb_impdp.par
directory=PUMP_DIR
dumpfile=full_pdb_20200612.dmp
logfile=impdp_full_pdb_20200612.log
cluster=N
transport_datafiles='+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/use01.dbf','+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/TABLE01.dbf','+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/INDEX01.dbf','+DATA/WGDB5/A72B83C078ED9F33E053CB56D80A6C8C/DATAFILE/APP01.dbf'
impdp system/oracle@pdb parfile=xtts_pdb_impdp.par
目标端更改用户默认表空间
select 'alter user '||username||' default tablespace '||default_tablespace||';' from dba_users where ACCOUNT_STATUS in ('OPEN','EXPIRED(GRACE)' and USERNAME not in ('SYS','SYSTEM');
目标端将表空间设置为读写模式
select TABLESPACE_NAME,STATUS from dba_tablespaces;
alter tablespace USERS read write;
alter tablespace TABLE read write;
alter tablespace INDEX read write;
alter tablespace APP read write;
SQL> ALTER SESSION SET CONTAINER=pdb;
SQL> select * from xttst.xtts;
源端导出其他对象元数据
$ cat pdb_metadata_only_expdp.par
directory=PUMP_DIR
dumpfile=pdb_metadata_only_20200612.dmp
schemas='USER','USER1','USER2'
content=metadata_only
EXCLUDE=table,index
parallel=16
logfile=pdb_metadata_only_expdp_20200612.log
cluster=N
$ expdp userid=\"/ as sysdba\" parfile=pdb_metadata_only_expdp.par
目标端导入其他对象元数据
impdp system/oracle@pdb directory=PUMP_DIR dumpfile=pdb_metadata_only_20200612.dmp full=y parallel=16 logfile=pdb_metadata_only_impdp_20200612.log cluster=N
检查PUBLIC SYNONYM
set pagesize 6000
select 'CREATE or replace PUBLIC SYNONYM '||synonym_name||' FOR '||table_owner||'.'||table_name||';' FROM dba_synonyms where table_owner in ('USER','USER1','USER2') and owner='PUBLIC';
同步tnsnames.ora
统计信息
exec dbms_stats.gather_database_stats(estimate_percent => 10, degree => 32, granularity => 'ALL', cascade => TRUE);
无效对象
对象数量对比
select owner,object_type,count(*) from dba_objects where owner in ('USER','USER1','USER2') group by owner,object_type order by owner,object_type;
检查文件头
select FILE#,STATUS,TABLESPACE_NAME,ERROR from v$datafile_header;
FILE# STATUS TABLESPACE_NAME ERROR
---------- ------- ------------------------------ -----------------------------------------------------------------
557 ONLINE SYSTEM
558 ONLINE SYSAUX
559 ONLINE UNDOTBS1
560 ONLINE UNDO_2
588 ONLINE APP
590 ONLINE TABLE
591 ONLINE USERS
592 ONLINE INDEX
593 ONLINE INDEX
594 ONLINE INDEX
595 ONLINE INDEX
11 rows selected.
SQL> select TABLESPACE_NAME,PLUGGED_IN from dba_tablespaces;
TABLESPACE_NAME PLU
------------------------------ ---
SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
UNDO_2 NO
APP YES
INDEX NO
TABLE YES
USERS YES
9 rows selected.
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
