把 Oracle 数据库从 Windows 系统迁移到 Linux 系统(虚拟机环境测试可用)

目录

一、把要导出的源数据库设置为非归档模式

-- 1、停库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- 2、启动数据库到 mount
SQL> startup mount
ORACLE instance started.

Total System Global Area 3357155328 bytes
Fixed Size                  2180384 bytes
Variable Size            1828719328 bytes
Database Buffers         1509949440 bytes
Redo Buffers               16306176 bytes
Database mounted.

-- 3、修改数据库的归档模式为非归档模式
SQL> alter database noarchivelog;
Database altered.

-- 4、打开数据库
SQL> alter database open;
Database altered.

-- 5、查看归档模式
SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           3

二、在 源数据库上执行数据库全备

1、创建存放备份文件的 目录
F:\>md rman_bak

F:\>dir da*
 驱动器 F 中的卷是 新加卷
 卷的序列号是 544F-5332

 F:\ 的目录

14/06/2022  22:50    <DIR>          rman_bak
               0 个文件              0 字节
               1 个目录 234,147,532,800 可用字节
2、查看源数据库的数据库名和实例名
SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      hisdb
db_unique_name                       string      hisdb
global_names                         boolean     FALSE
instance_name                        string      hisdb
lock_name_space                      string
log_file_name_convert                string
service_names                        string      hisdb

-- 数据库名和实例名都是 hisdb
3、停库,然后启动数据库到 mount
-- 停库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

-- 启动数据库到 mount 状态
SQL> startup mount;
ORACLE instance started.

Total System Global Area 3357155328 bytes
Fixed Size                  2180384 bytes
Variable Size            2332035808 bytes
Database Buffers         1006632960 bytes
Redo Buffers               16306176 bytes
Database mounted.

File created.
4、进入rman,执行备份操作
C:\Users\Administrator>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 14 15:41:09 2022
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: HISDB (DBID=2002805648)

RMAN>

-- 设置 rman 备份的路径及文件格式
-- configure channel device type disk format 'f:/rman_bak/%d_%I_%s_%p_%T.bkp';
RMAN> configure channel device type disk format 'f:/rman_bak/%d_%I_%s_%p_%T.bkp';

using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'f:/rman_bak/%d_%I_%s_%p_%T.bkp';
new RMAN configuration parameters are successfully stored

-- 备份数据库
-- backup as compressed backupset database;
RMAN> backup as compressed backupset database;

Starting backup at 14-JUN-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=F:\APP\ORADATA\HISDB\SYSTEM01.DBF
input datafile file number=00002 name=F:\APP\ORADATA\HISDB\SYSAUX01.DBF
input datafile file number=00003 name=F:\APP\ORADATA\HISDB\UNDOTBS01.DBF
input datafile file number=00005 name=F:\APP\ORADATA\HISDB\TS001.DBF
input datafile file number=00006 name=F:\APP\ORADATA\HISDB\TS002.DBF
input datafile file number=00004 name=F:\APP\ORADATA\HISDB\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 14-JUN-22
channel ORA_DISK_1: finished piece 1 at 14-JUN-22
piece handle=F:\RMAN_BAK\HISDB_2002805648_10_1_20220614.BKP tag=TAG20220614T155620 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 14-JUN-22
channel ORA_DISK_1: finished piece 1 at 14-JUN-22
piece handle=F:\RMAN_BAK\HISDB_2002805648_11_1_20220614.BKP tag=TAG20220614T155620 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 14-JUN-22

二、把备份文件上传到目标服务器

[oracle@wgx data-bak]$ pwd
/home/oracle/data-bak

[oracle@wgx data-bak]$ ll
总用量 298576
-rw-r--r-- 1 oracle oinstall 304627712 614 15:56 HISDB_2002805648_10_1_20220614.BKP
-rw-r--r-- 1 oracle oinstall   1114112 614 15:56 HISDB_2002805648_11_1_20220614.BKP
# 两个文件,第一个(文件较大)为数据文件
# 第二个(文件较小)为控制文件和参数文件

三、从备份集恢复参数文件

1、修改环境变量
[root@wgx ~]# su - oracle
上一次登录:二 614 23:32:18 CST 2022pts/0 上

[oracle@wgx ~]$ vi .bash_profile 

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH


export ORACLE_BASE=/usr/local/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID=hisdb                         #修改环境变量为hisdb
export ORACLE_OWNER=oracle
export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin

[oracle@wgx ~]$ source .bash_profile 
2、还原参数文件
-- 停库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- 进入rman
[oracle@wgx data-bak]$ rman  target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jun 14 23:43:02 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)

-- 启动数据库到 nomount
RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/usr/local/oracle/product/11.2.0/db_1/dbs/inithisdb.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1068937216 bytes
Fixed Size                     2260088 bytes
Variable Size                281019272 bytes
Database Buffers             780140544 bytes
Redo Buffers                   5517312 bytes

-- 恢复参数文件
RMAN> restore spfile from '/home/oracle/data-bak/HISDB_2002805648_11_1_20220614.BKP';

Starting restore at 15-JUN-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/data-bak/HISDB_2002805648_11_1_20220614.B
KPchannel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 15-JUN-22
3、修改参数文件的内容
SQL> create pfile from spfile;
File created.

[oracle@wgx data-bak]$ cd $ORACLE_HOME
[oracle@wgx db_1]$ pwd
/usr/local/oracle/product/11.2.0/db_1
[oracle@wgx db_1]$ cd dbs

[oracle@wgx dbs]$ ll
总用量 32
-rw-rw---- 1 oracle oinstall 1544 614 23:59 hc_hisdb.dat
-rw-rw---- 1 oracle oinstall 1544 614 23:49 hc_orcl.dat
-rw-r--r-- 1 oracle oinstall  877 615 00:16 inithisdb.ora   #pfile
-rw-r--r-- 1 oracle oinstall 2851 515 2009 init.ora
-rw-r----- 1 oracle oinstall   24 68 02:09 lkORCL
-rw-r----- 1 oracle oinstall 1536 68 02:10 orapworcl
-rw-r----- 1 oracle oinstall 2560 615 00:14 spfilehisdb.ora  #spfile
-rw-r----- 1 oracle oinstall 2560 614 23:32 spfileorcl.ora

[oracle@wgx dbs]$ vi inithisdb.ora 

参数文件的内容如下:

# hisdb.__db_cache_size=1241513984
# hisdb.__java_pool_size=16777216
# hisdb.__large_pool_size=16777216
# hisdb.__oracle_base='f:\app'#ORACLE_BASE set from environment
# hisdb.__pga_aggregate_target=1358954496
# hisdb.__sga_target=2013265920
# hisdb.__shared_io_pool_size=0
# hisdb.__shared_pool_size=671088640
# hisdb.__streams_pool_size=33554432
# 以上内容全部删除
#创建目录:/usr/local/oracle/admin/hisdb/adump
*.audit_file_dest='/usr/local/oracle/admin/hisdb/adump'  
*.audit_trail='db'
#更改数据库版本信息  *.compatible='11.2.0.0.0'
*.compatible='11.2.0.4.0'

#*.control_files='f:\app\oradata\hisdb\control01.ctl','f:\app\flash_recovery_area\hisdb\control02.ctl'
# 创建目录:/usr/local/oradata/hisdb
*.control_files='/usr/local/oradata/hisdb/control01.ctl','/usr/local/oracle/hisdb_bak/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='hisdb'
# 创建目录:/usr/local/oradata/fast_recovery_area
# *.db_recovery_file_dest='f:\app\flash_recovery_area'
*.db_recovery_file_dest='/usr/local/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4102029312
#*.diagnostic_dest='f:\app'
*.diagnostic_dest='/usr/local/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hisdbXDB)'
*.memory_target=3358588928
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

修改后的参数文件内容如下:

*.audit_file_dest='/usr/local/oracle/admin/hisdb/adump'  
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/usr/local/oradata/hisdb/control01.ctl','/usr/local/oracle/hisdb_bak/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='hisdb'
*.db_recovery_file_dest='/usr/local/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4102029312
*.diagnostic_dest='/usr/local/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=hisdbXDB)'
*.memory_target=3358588928
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

四、使用修改后的参数文件重启数据库实例

1、停库,生成 spfile
-- 停库
SQL> shutdown abort
ORACLE instance shut down.

-- 生成 spfile
SQL> create spfile from pfile='/usr/local/oracle/product/11.2.0/db_1/dbs/inithisdb.ora';
File created.

-- 启动数据库到 nomount
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  659730432 bytes
Fixed Size		    2256152 bytes
Variable Size		  444596968 bytes
Database Buffers	  209715200 bytes
Redo Buffers		    3162112 bytes

2、恢复控制文件,启动实例到 mount
-- 恢复控制文件
RMAN> restore controlfile from '/home/oracle/data-bak/HISDB_2002805648_11_1_20220614.BKP';

Starting restore at 15-JUN-22
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/usr/local/oradata/hisdb/control01.ctl
output file name=/usr/local/oracle/hisdb_bak/control02.ctl
Finished restore at 15-JUN-22

-- 启动实例到 mount
SQL> alter database mount;
Database altered.

3、查看控制文件中的数据文件与临时文件信息
RMAN> report schema;

released channel: ORA_DISK_1
Starting implicit crosscheck backup at 15-JUN-22
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 15-JUN-22

Starting implicit crosscheck copy at 15-JUN-22
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-JUN-22

searching for all files in the recovery area
cataloging files...
no files cataloged

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name HISDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     F:\APP\ORADATA\HISDB\SYSTEM01.DBF
2    0        SYSAUX               ***     F:\APP\ORADATA\HISDB\SYSAUX01.DBF
3    0        UNDOTBS1             ***     F:\APP\ORADATA\HISDB\UNDOTBS01.DBF
4    0        USERS                ***     F:\APP\ORADATA\HISDB\USERS01.DBF
5    0        TS001                ***     F:\APP\ORADATA\HISDB\TS001.DBF
6    0        TS001                ***     F:\APP\ORADATA\HISDB\TS002.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       F:\APP\ORADATA\HISDB\TEMP01.DBF

五、还原数据库

1、核对备份文件

CROSSCHECK 命令用于核对备份文件,以确保 RMAN 资料库与备份文件保持同步。当执行该命令时,如果 RMAN 资料库记录的状态与实际备份文件不匹配,则会更新资料库里的状态使其与实际备份文件状态一致;若备份文件处于 expired 状态,那么说明该备份已失效,可以删除。

RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BAK_20211202\HISDB_20211202_030FNB1I_1_1_1.BAK RECID=1 STAMP=1090235448
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BAK_20211202\HISDB_20211202_040FNB1O_1_1_1.BAK RECID=2 STAMP=1090235452
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BAK_20211202\HISDB_20211202_020FNB1I_1_1_1.BAK RECID=3 STAMP=1090235442
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BAK_20211202\HISDB_20211202_050FNB1R_1_1_1.BAK RECID=4 STAMP=1090235453
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BAK_20211202\HISDB_20211202_070FNB1T_1_1_1.BAK RECID=5 STAMP=1090235455
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BAK_20211202\HISDB_20211202_080FNB1V_1_1_1.BAK RECID=6 STAMP=1090235456
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BAK_20211202\HISDB_20211202_060FNB1T_1_1_1.BAK RECID=7 STAMP=1090235456
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BAK_20211202\HISDB_20211202_010FNB1H_1_1_1.BAK RECID=8 STAMP=1090235442
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=D:\BAK_20211202\CTL_HISDB_20211202_1090235463_9_1 RECID=9 STAMP=1090235465
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=F:\RMAN_BAK\HISDB_2002805648_10_1_20220614.BKP RECID=10 STAMP=1107359781
Crosschecked 10 objects

2、删除失效的备份文件
RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1       1       1   1   EXPIRED     DISK        D:\BAK_20211202\HISDB_20211202_030FNB1I_1_1_1.BAK
2       2       1   1   EXPIRED     DISK        D:\BAK_20211202\HISDB_20211202_040FNB1O_1_1_1.BAK
3       3       1   1   EXPIRED     DISK        D:\BAK_20211202\HISDB_20211202_020FNB1I_1_1_1.BAK
4       4       1   1   EXPIRED     DISK        D:\BAK_20211202\HISDB_20211202_050FNB1R_1_1_1.BAK
5       5       1   1   EXPIRED     DISK        D:\BAK_20211202\HISDB_20211202_070FNB1T_1_1_1.BAK
6       6       1   1   EXPIRED     DISK        D:\BAK_20211202\HISDB_20211202_080FNB1V_1_1_1.BAK
7       7       1   1   EXPIRED     DISK        D:\BAK_20211202\HISDB_20211202_060FNB1T_1_1_1.BAK
8       8       1   1   EXPIRED     DISK        D:\BAK_20211202\HISDB_20211202_010FNB1H_1_1_1.BAK
9       9       1   1   EXPIRED     DISK        D:\BAK_20211202\CTL_HISDB_20211202_1090235463_9_1
10      10      1   1   EXPIRED     DISK        F:\RMAN_BAK\HISDB_2002805648_10_1_20220614.BKP

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=D:\BAK_20211202\HISDB_20211202_030FNB1I_1_1_1.BAK RECID=1 STAMP=1090235448
deleted backup piece
backup piece handle=D:\BAK_20211202\HISDB_20211202_040FNB1O_1_1_1.BAK RECID=2 STAMP=1090235452
deleted backup piece
backup piece handle=D:\BAK_20211202\HISDB_20211202_020FNB1I_1_1_1.BAK RECID=3 STAMP=1090235442
deleted backup piece
backup piece handle=D:\BAK_20211202\HISDB_20211202_050FNB1R_1_1_1.BAK RECID=4 STAMP=1090235453
deleted backup piece
backup piece handle=D:\BAK_20211202\HISDB_20211202_070FNB1T_1_1_1.BAK RECID=5 STAMP=1090235455
deleted backup piece
backup piece handle=D:\BAK_20211202\HISDB_20211202_080FNB1V_1_1_1.BAK RECID=6 STAMP=1090235456
deleted backup piece
backup piece handle=D:\BAK_20211202\HISDB_20211202_060FNB1T_1_1_1.BAK RECID=7 STAMP=1090235456
deleted backup piece
backup piece handle=D:\BAK_20211202\HISDB_20211202_010FNB1H_1_1_1.BAK RECID=8 STAMP=1090235442
deleted backup piece
backup piece handle=D:\BAK_20211202\CTL_HISDB_20211202_1090235463_9_1 RECID=9 STAMP=1090235465
deleted backup piece
backup piece handle=F:\RMAN_BAK\HISDB_2002805648_10_1_20220614.BKP RECID=10 STAMP=1107359781
Deleted 10 EXPIRED objects
3、更新备份文件
--catalog start with '/home/oracle/data-bak/';  -- 注意此处的目录

RMAN> catalog start with '/home/oracle/data-bak/';

searching for all files that match the pattern /home/oracle/data-bak/

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/data-bak/HISDB_2002805648_10_1_20220614.BKP
File Name: /home/oracle/data-bak/HISDB_2002805648_11_1_20220614.BKP
File Name: /home/oracle/data-bak/pfile0614.ora

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/data-bak/HISDB_2002805648_10_1_20220614.BKP
File Name: /home/oracle/data-bak/HISDB_2002805648_11_1_20220614.BKP

List of Files Which Where Not Cataloged
=======================================
File Name: /home/oracle/data-bak/pfile0614.ora
  RMAN-07517: Reason: The file header is corrupted
4、查看备份片信息
RMAN> list backup;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11      Full    290.51M    DISK        00:00:00     14-JUN-22      
        BP Key: 11   Status: AVAILABLE  Compressed: YES  Tag: TAG20220614T155620
        Piece Name: /home/oracle/data-bak/HISDB_2002805648_10_1_20220614.BKP
  List of Datafiles in backup set 11
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 8146403    14-JUN-22 F:\APP\ORADATA\HISDB\SYSTEM01.DBF
  2       Full 8146403    14-JUN-22 F:\APP\ORADATA\HISDB\SYSAUX01.DBF
  3       Full 8146403    14-JUN-22 F:\APP\ORADATA\HISDB\UNDOTBS01.DBF
  4       Full 8146403    14-JUN-22 F:\APP\ORADATA\HISDB\USERS01.DBF
  5       Full 8146403    14-JUN-22 F:\APP\ORADATA\HISDB\TS001.DBF
  6       Full 8146403    14-JUN-22 F:\APP\ORADATA\HISDB\TS002.DBF

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12      Full    1.05M      DISK        00:00:00     14-JUN-22      
        BP Key: 12   Status: AVAILABLE  Compressed: YES  Tag: TAG20220614T155620
        Piece Name: /home/oracle/data-bak/HISDB_2002805648_11_1_20220614.BKP
  SPFILE Included: Modification time: 14-JUN-22
  SPFILE db_unique_name: HISDB
  Control File Included: Ckp SCN: 8146403      Ckp time: 14-JUN-22
5、恢复数据库
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
run{
set newname for datafile 1 to '/usr/local/oradata/hisdb/SYSTEM01.DBF';
set newname for datafile 2 to '/usr/local/oradata/hisdb/SYSAUX01.DBF';
set newname for datafile 3 to '/usr/local/oradata/hisdb/UNDOTBS01.DBF';
set newname for datafile 4 to '/usr/local/oradata/hisdb/USERS01.DBF';
set newname for datafile 5 to '/usr/local/oradata/hisdb/TS001.DBF';
set newname for datafile 6 to '/usr/local/oradata/hisdb/TS002.DBF';
set newname for tempfile 1 to '/usr/local/oradata/hisdb/TEMP01.DBF';
restore database;
switch datafile all;
switch tempfile all;
}
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------

RMAN> run{
set newname for datafile 1 to '/usr/local/oradata/hisdb/SYSTEM01.DBF';
set newname for datafile 2 to '/usr/local/oradata/hisdb/SYSAUX01.DBF';
set newname for datafile 3 to '/usr/local/oradata/hisdb/UNDOTBS01.DBF';
set newname for datafile 4 to '/usr/l2> 3> 4> 5> ocal/oradata/hisdb/USERS01.DBF';
set newname for datafile 5 to '/usr/local/oradata/hisdb/TS001.DBF';
set newname for datafile 6 to '/usr/local/oradata/hisdb/TS002.DBF';
set newname for tempfile 1 to '/usr/local/oradata/hisdb/TEMP01.DBF';
restore database;
6> 7> 8> 9> 10> switch datafile all;
switch tempfile all;
}11> 12> 

executing command: SET NEWNAME

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 restore at 15-JUN-22
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /usr/local/oradata/hisdb/SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to /usr/local/oradata/hisdb/SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to /usr/local/oradata/hisdb/UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to /usr/local/oradata/hisdb/USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to /usr/local/oradata/hisdb/TS001.DBF
channel ORA_DISK_1: restoring datafile 00006 to /usr/local/oradata/hisdb/TS002.DBF
channel ORA_DISK_1: reading from backup piece /home/oracle/data-bak/HISDB_2002805648_10_1_20220614.BKP
channel ORA_DISK_1: piece handle=/home/oracle/data-bak/HISDB_2002805648_10_1_20220614.BKP tag=TAG20220614T155620
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:09:49
Finished restore at 15-JUN-22

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1107394027 file name=/usr/local/oradata/hisdb/SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1107394027 file name=/usr/local/oradata/hisdb/SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1107394027 file name=/usr/local/oradata/hisdb/UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=1107394027 file name=/usr/local/oradata/hisdb/USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=1107394027 file name=/usr/local/oradata/hisdb/TS001.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=1107394028 file name=/usr/local/oradata/hisdb/TS002.DBF

renamed tempfile 1 to /usr/local/oradata/hisdb/TEMP01.DBF in control file

六、启动数据库

SQL> alter database open resetlogs upgrade;

Database altered.

编译无效对象:

SQL> @?/rdbms/admin/utlrp.sql

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2022-06-15 21:32:50

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>	  number should decrease with time.
DOC>	     SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>	  should increase with time.
DOC>	     SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>	     SELECT job_name FROM dba_scheduler_jobs
DOC>		WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>	     SELECT job_name FROM dba_scheduler_running_jobs
DOC>		WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
DECLARE
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_SQLTUNE" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SQLTUNE"
ORA-06512: at "SYS.UTL_RECOMP", line 783
ORA-06512: at line 4



TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2022-06-15 21:32:59

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
		  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
			  0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

七、修改日志文件信息

1、查看日志文件信息
SQL> col member for a40

SQL> select GROUP#, STATUS, TYPE, MEMBER from v$logfile;
    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ----------------------------------------
	 3	   ONLINE  F:\APP\ORADATA\HISDB\REDO03.LOG
	 2	   ONLINE  F:\APP\ORADATA\HISDB\REDO02.LOG
	 1	   ONLINE  F:\APP\ORADATA\HISDB\REDO01.LOG
2、添加日志组和日志文件
-- 提前创建目录:/usr/local/oradata/hisdb_bak/
alter database add logfile group 5 '/usr/local/oradata/hisdb/redo05a.log' size 300m;
alter database add logfile group 6 '/usr/local/oradata/hisdb/redo06a.log' size 300m;
alter database add logfile group 7 '/usr/local/oradata/hisdb/redo07a.log' size 300m;

alter database add logfile member '/usr/local/oradata/hisdb_bak/redo05b.log' to group 5;
alter database add logfile member '/usr/local/oradata/hisdb_bak/redo06b.log' to group 6;
alter database add logfile member '/usr/local/oradata/hisdb_bak/redo07b.log' to group 7;
3、查看日志文件信息
SQL> select GROUP#, STATUS, TYPE, MEMBER from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ----------------------------------------
	 3	   ONLINE  F:\APP\ORADATA\HISDB\REDO03.LOG
	 2	   ONLINE  F:\APP\ORADATA\HISDB\REDO02.LOG
	 1	   ONLINE  F:\APP\ORADATA\HISDB\REDO01.LOG
	 5	   ONLINE  /usr/local/oradata/hisdb/redo05a.log
	 6	   ONLINE  /usr/local/oradata/hisdb/redo06a.log
	 7	   ONLINE  /usr/local/oradata/hisdb/redo07a.log
	 5 INVALID ONLINE  /usr/local/oradata/hisdb_bak/redo05b.log
	 6 INVALID ONLINE  /usr/local/oradata/hisdb_bak/redo06b.log
	 7 INVALID ONLINE  /usr/local/oradata/hisdb_bak/redo07b.log

9 rows selected.


SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,STATUS from v$log;

    GROUP#  SEQUENCE#	   BYTES    MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
	 1	    340	        52428800	  1     INACTIVE
	 2	    341	        52428800	  1     INACTIVE
	 7	      0         314572800	  2     UNUSED
	 5	      0         314572800	  2     UNUSED
	 6	      0         314572800	  2     UNUSED
	 3	    342	        52428800	  1     CURRENT

6 rows selected.
4、删除失效的日志组
(1)切换日志
SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,STATUS from v$log;

    GROUP#  SEQUENCE#	   BYTES    MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
	 1	    1	52428800	  1 INACTIVE
	 2	    2	52428800	  1 ACTIVE
	 3	    3	52428800	  1 ACTIVE
	 5	    4  314572800	  2 CURRENT   --当前日志组为 4
	 6	    0  314572800	  2 UNUSED
	 7	    0  314572800	  2 UNUSED

6 rows selected.
(2)打检查点
SQL> alter system checkpoint;

System altered.

SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,STATUS from v$log;

    GROUP#  SEQUENCE#	   BYTES    MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
	 1	    1	52428800	  1 INACTIVE    -- 日志组1 变为非活动状态(INACTIVE)
	 2	    2	52428800	  1 INACTIVE    -- 日志组2 变为非活动状态(INACTIVE)
	 3	    3	52428800	  1 INACTIVE    -- 日志组3 变为非活动状态(INACTIVE)
	 5	    4  314572800	  2 CURRENT
	 6	    0  314572800	  2 UNUSED
	 7	    0  314572800	  2 UNUSED

6 rows selected.
(3)删除日志组:1、2、3
SQL> alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
Database altered.
(4)查看日志组信息
SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,STATUS from v$log;

    GROUP#  SEQUENCE#	   BYTES    MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
	 5	    4  314572800	  2 CURRENT
	 6	    0  314572800	  2 UNUSED
	 7	    0  314572800	  2 UNUSED


SQL> select GROUP#, STATUS, TYPE, MEMBER from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ----------------------------------------
	 5	   ONLINE  /usr/local/oradata/hisdb/redo05a.log
	 6	   ONLINE  /usr/local/oradata/hisdb/redo06a.log
	 7	   ONLINE  /usr/local/oradata/hisdb/redo07a.log
	 5	   ONLINE  /usr/local/oradata/hisdb_bak/redo05b.log
	 6	   ONLINE  /usr/local/oradata/hisdb_bak/redo06b.log
	 7	   ONLINE  /usr/local/oradata/hisdb_bak/redo07b.log

6 rows selected.

八、升级数据字典

1、通过 startup upgrade 启动实例
-- 停库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- 启动数据库
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area  659730432 bytes
Fixed Size		    2256152 bytes
Variable Size		  444596968 bytes
Database Buffers	  209715200 bytes
Redo Buffers		    3162112 bytes
Database mounted.
Database opened.

2、升级数据字典
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

九、重启实例

-- 停库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- 启动实例
SQL> startup
ORACLE instance started.

Total System Global Area  659730432 bytes
Fixed Size		    2256152 bytes
Variable Size		  444596968 bytes
Database Buffers	  209715200 bytes
Redo Buffers		    3162112 bytes
Database mounted.
Database opened.

十、把数据库设置为归档模式

1、停库,然后启动数据库到 mount 状态
-- 停库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- 启动数据库到 mount 状态
SQL> startup mount
ORACLE instance started.

Total System Global Area  659730432 bytes
Fixed Size		    2256152 bytes
Variable Size		  444596968 bytes
Database Buffers	  209715200 bytes
Redo Buffers		    3162112 bytes
Database mounted.
2、打开数据库的归档模式
SQL> alter database archivelog;

Database altered.
3、打开数据库,查看归档状态
SQL> alter database open;

Database altered.

SQL>  archive log list;
Database log mode	       Archive Mode
Automatic archival	       Enabled
Archive destination	       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     10
Next log sequence to archive   12
Current log sequence	       12
4、配置归档的位置
(1)查看当前归档日志文件的位置
-- 切日志,生成归档日志文件
SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

-- 查看归档日志文件的位置
SQL> select sequence#, name from v$archived_log;

 SEQUENCE#
----------
NAME
--------------------------------------------------------------------------------
	13
/usr/local/oracle/fast_recovery_area/HISDB/archivelog/2022_06_16/o1_mf_1_13_kbnt
lmm7_.arc

	14
/usr/local/oracle/fast_recovery_area/HISDB/archivelog/2022_06_16/o1_mf_1_14_kbnt
ltrm_.arc
(2)修改归档日志的位置为:/home/oracle/archivelog
-- 操作之前先创建目录:/usr/local/oradata/hisdb_bak/archivelog/
SQL> alter system set log_archive_dest_1 = 'location=/usr/local/oradata/hisdb_bak/archivelog/' scope = spfile sid='*';

System altered.

-- 停库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- 启动数据库
SQL> startup
ORACLE instance started.

Total System Global Area  659730432 bytes
Fixed Size		    2256152 bytes
Variable Size		  444596968 bytes
Database Buffers	  209715200 bytes
Redo Buffers		    3162112 bytes
Database mounted.
Database opened.
5、重新查看归档日志信息
-- 切日志,生成归档日志文件
SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

-- 查看归档日志文件的位置
SQL> select sequence#, name from v$archived_log;

 SEQUENCE#                   NAME
--------------------------------------------------------------------------------
	13
/usr/local/oracle/fast_recovery_area/HISDB/archivelog/2022_06_16/o1_mf_1_13_kbnt
lmm7_.arc

	14
/usr/local/oracle/fast_recovery_area/HISDB/archivelog/2022_06_16/o1_mf_1_14_kbnt
ltrm_.arc

	12
/usr/local/oracle/fast_recovery_area/HISDB/archivelog/2022_06_16/o1_mf_1_12_kbnt
ljkj_.arc

	15
/usr/local/oracle/fast_recovery_area/HISDB/archivelog/2022_06_16/o1_mf_1_15_kbnt
zobz_.arc

	16
/usr/local/oracle/fast_recovery_area/HISDB/archivelog/2022_06_16/o1_mf_1_16_kbnt
zrj2_.arc

	17
/usr/local/oracle/fast_recovery_area/HISDB/archivelog/2022_06_16/o1_mf_1_17_kbnt
zwoj_.arc

	18
/usr/local/oradata/hisdb_bak/archivelog/1_18_1107466318.dbf

	19
/usr/local/oradata/hisdb_bak/archivelog/1_19_1107466318.dbf

	20
/usr/local/oradata/hisdb_bak/archivelog/1_20_1107466318.dbf


9 rows selected.

参数文件文件

Logo

华为开发者空间,是为全球开发者打造的专属开发空间,汇聚了华为优质开发资源及工具,致力于让每一位开发者拥有一台云主机,基于华为根生态开发、创新。

更多推荐