oracle RMAN同机迁移数据库从ASM至文件系统（同机）
RMAN同机迁移数据库从ASM至文件系统
环境
数据库是asm环境，同机迁移数据库到文件系统的步骤记录。
检查数据文件、日志文件、控制文件、临时文件
SQL> select file_name from dba_data_files;
FILE_NAME
\--------------------------------------------------------------------------------
+DGSYSTEM/HFWMSDB/DATAFILE/system.260.1130788415
+DGSYSTEM/HFWMSDB/DATAFILE/sysaux.261.1130788423
+DGSYSTEM/HFWMSDB/DATAFILE/undotbs1.262.1130788427
+DGSYSTEM/HFWMSDB/DATAFILE/users.264.1130788451
SQL> select member from v$logfile;
MEMBER
\--------------------------------------------------------------------------------
+DGSYSTEM/HFWMSDB/ONLINELOG/group_1.257.1130788409
+DGDATA01/HFWMSDB/ONLINELOG/group_1.257.1130788411
+DGSYSTEM/HFWMSDB/ONLINELOG/group_2.258.1130788411
+DGDATA01/HFWMSDB/ONLINELOG/group_2.258.1130788413
+DGSYSTEM/HFWMSDB/ONLINELOG/group_3.259.1130788413
+DGDATA01/HFWMSDB/ONLINELOG/group_3.259.1130788415
SQL> select file_name from dba_temp_files;
FILE_NAME
\--------------------------------------------------------------------------------
+DGSYSTEM/HFWMSDB/TEMPFILE/temp.263.1130788429
SQL> select name from v$controlfile;
NAME
\-------------------------------------------------------------------------------
+DGSYSTEM/HFWMSDB/CONTROLFILE/current.256.1130788409
+DGDATA01/HFWMSDB/CONTROLFILE/current.256.1130788409
SQL> show parameter recover;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DGRECOVERY
db_recovery_file_dest_size big integer 100G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
做备份
rman target /
backup database format '/backup/fgwmsdb_%s_%p_%t';
sqlplus / as sysdba
alter database backup controlfile to '/backup/fgwmsdb_control.ctl';
alter database backup controlfile to trace as '/backup/fgwmsdb_control.trc';
准备目录
mkdir -p /oracle/oradata/hfwmsdb
mkdir -p /oracle/arch
开始迁移
filesystem to asm （RMAN）
–/*RMAN同机迁移数据库从ASM至文件系统
ASM
crsctl check has
crsctl start has
sqlplus / as sysdba
startup
配置归档
show parameter recover
alter system set db_recovery_file_dest_size=100g;
alter system set db_recovery_file_dest=‘+DGRECOVERY’;
shutdown immediate
startup mount
alter database archivelog;
alter database open;
select member from v$logfile;
archive log list
创建测试数据
CREATE TABLESPACE hfedu01 DATAFILE ‘+dgdata01’ SIZE 50M autoextend off;
create user hfedu01 identified by hfedu01 default tablespace hfedu01;
conn /as sysdba
grant dba to hfedu01;
conn hfedu01/hfedu01
create table hfedu01(id number,name varchar2(20));
insert into hfedu01 values(1,‘itpux1’);
commit;
alter system switch logfile;
alter system checkpoint;
使用rman备份数据库
rman target /
backup database format ‘/backup/fgwmsdb_%s_%p_%t’;
备份控制文件和参数文件
sqlplus / as sysdba
alter database backup controlfile to ‘/backup/fgwmsdb_control.ctl’;
alter database backup controlfile to trace as ‘/backup/fgwmsdb_control.trc’;
mkdir -p /oracle/oradata/hfwmsdb – su - oracle
mkdir -p /oracle/arch
sqlplus / as sysdba
show parameter spfile;
create pfile=‘/backup/pfile.ora’ from spfile;
exit
创建单机pfile文件
sqlplus / as sysdba
create spfile=‘/oracle/app/oracle/product/19c/db_1/dbs/spfilehfwmsdb.ora’ from pfile=‘/backup/pfile.ora’;
shutdown immediate
startup mount
show parameter spfile;
--NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
--spfile string /oracle/app/oracle/product/19c/db_1/dbs/spfilehfwmsdb.ora
show parameter control;
--NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
--control_file_record_keep_time integer 7
--control_files string +DGSYSTEM/HFWMSDB/CONTROLFILE/current.256.1130788409, +DGDATA01/HFWMSDB/CONTROLFILE/current.256.1130788409
--control_management_pack_access string DIAGNOSTIC+TUNING
修改控制文件路径
alter system set control_files=‘/oracle/oradata/hfwmsdb/control01.ctl’,‘/oracle/oradata/hfwmsdb/contrl02.ctl’ scope=spfile;
SQL> show parameter db_create
--NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
--db_create_file_dest string +DGSYSTEM
--db_create_online_log_dest_1 string +DGSYSTEM
--db_create_online_log_dest_2 string +DGDATA01
--db_create_online_log_dest_3 string
--db_create_online_log_dest_4 string
--db_create_online_log_dest_5 string
修改数据文件路径
SQL> alter system set db_create_file_dest=‘/oracle/oradata/hfwmsdb’ scope=spfile;
SQL> alter system set db_create_online_log_dest_1=‘/oracle/oradata/hfwmsdb’ scope=spfile;
SQL> alter system set db_create_online_log_dest_2=‘/oracle/oradata/hfwmsdb’ scope=spfile;
修改归档日志存储路径
SQL> show parameter recover
--NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
--db_recovery_file_dest string +DGRECOVERY
--db_recovery_file_dest_size big integer 100G
--db_unrecoverable_scn_tracking boolean TRUE
alter system set db_recovery_file_dest=‘/oracle/arch’;
show parameter recover
--NAME TYPE VALUE
-------------------------------------- ----------- ------------------------------
--db_recovery_file_dest string /oracle/arch
shutdown immediate
恢复控制文件
rman target/
RMAN> restore controlfile from '+DGRECOVERY/HFWMSDB/AUTOBACKUP/2023_04_03/s_1133219146.258.1133219147';
复制数据库
RMAN> alter database mount;
RMAN> backup as copy database format '/oracle/oradata/hfwmsdb/%U';
RMAN> switch database to copy;
启动数据库
sqlplus / as sysdba
alter database open resetlogs;
--ERROR at line 1:
--ORA-01152: file 1 was not restored from a sufficiently old backup
--ORA-01110: data file 1:
--'/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-SYSTEM_FNO-1_041on4ep'
recover database using backup controlfile until cancel;
--ORA-00279: change 865705 generated at 04/03/2023 23:03:55 needed for thread 1
--ORA-00289: suggestion :
--/oracle/arch/HFWMSDB/archivelog/2023_04_03/o1_mf_1_14_%u_.arc
--ORA-00280: change 865705 for thread 1 is in sequence #14
SQL> exit
sqlplus / as sysdba
SQL> select * from v$log;
SQL> select * from v$logfile; --查询14号文件的redo日志在哪 --+DGSYSTEM/HFWMSDB/ONLINELOG/group_2.258.1130788411
SQL> recover database using backup controlfile until cancel;
--ORA-00279: change 865705 generated at 04/03/2023 23:03:55 needed for thread 1
--ORA-00289: suggestion :
--/oracle/arch/HFWMSDB/archivelog/2023_04_03/o1_mf_1_14_%u_.arc
--ORA-00280: change 865705 for thread 1 is in sequence #14
--Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DGSYSTEM/HFWMSDB/ONLINELOG/group_2.258.1130788411
alter database open resetlogs;
修改临时文件
SQL> select file_name from dba_temp_files;
--FILE_NAME
\----------------------------------------------------------------------------------
--+DGSYSTEM/HFWMSDB/TEMPFILE/temp.263.1130788429
SQL> alter database tempfile ‘+DGSYSTEM/HFWMSDB/TEMPFILE/temp.263.1130788429’ drop including datafiles;
SQL> alter tablespace temp add tempfile’/oracle/oradata/hfwmsdb/temp01.dbf’ size 50M autoextend off;
SQL> select file_name from dba_temp_files; --临时文件
修改日志文件
SQL> select member from v$logfile; --日志文件
SQL> select * from v$log; --日志切换
SQL> alter database drop logfile group 2;
SQL> alter database add logfile group 2 (‘/oracle/oradata/hfwmsdb/redo02.log’) size 100M;
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3 (‘/oracle/oradata/hfwmsdb/redo03.log’) size 100M;
SQL> alter system switch logfile;
SQL> alter system checkpoint;
SQL> select * from v$log;
SQL> alter database drop logfile group 1;
SQL> alter database add logfile group 1 (‘/oracle/oradata/hfwmsdb/redo01.log’) size 100M;
SQL> alter system switch logfile;
SQL> /
SQL> select member from v$logfile;
--MEMBER
\--------------------------------------------------------------------------------
--/oracle/oradata/hfwmsdb/redo01.log
--/oracle/oradata/hfwmsdb/redo02.log
--/oracle/oradata/hfwmsdb/redo03.log
查看数据文件目录
SQL> select file_name from dba_data_files; --数据文件
--FILE_NAME
\----------------------------------------------------------------------------------
--/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-SYSTEM_FNO-1_041on4ep
--/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-SYSAUX_FNO-2_051on4f1
--/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-UNDOTBS1_FNO-3_061on4f8
--/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-USERS_FNO-4_081on4fo
--/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-HFEDU01_FNO-5_071on4fn
SQL> select name from v$controlfile; --控制文件
--NAME
\----------------------------------------------------------------------------------
--/oracle/oradata/hfwmsdb/control01.ctl
--/oracle/oradata/hfwmsdb/control02.ctl
SQL> shutdown immediate
[root@hfwmsdb98 ~]# crsctl stop has --停止asm
[oracle@hfwmsdb98:/home/oracle]$sqlplus / as sysdba --启动文件系统数据库
SQL> startup
[oracle@hfwmsdb98:/home/oracle]$tail -200f /oracle/app/oracle/diag/rdbms/hfwmsdb/hfwmsdb/trace/alert_hfwmsdb.log
–结束*/
操作记录：
[root@hfwmsdb98 ~]# crsctl check has
CRS-4639: Could not contact Oracle High Availability Services
[root@hfwmsdb98 ~]# crsctl start has
CRS-4123: Oracle High Availability Services has been started.
[oracle@hfwmsdb98:/home/oracle]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 3 22:43:42 2023
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 671088640 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /oracle/app/oracle/product/19c/db_1/dbs/arch
Oldest online log sequence 10
Current log sequence 12
SQL> alter system set db_recovery_file_dest_size=100g;
System altered.
SQL> alter system set db_recovery_file_dest='+DGRECOVERY';
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 671088640 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open
2 ;
Database altered.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DGSYSTEM/HFWMSDB/ONLINELOG/group_1.257.1130788409
+DGDATA01/HFWMSDB/ONLINELOG/group_1.257.1130788411
+DGSYSTEM/HFWMSDB/ONLINELOG/group_2.258.1130788411
+DGDATA01/HFWMSDB/ONLINELOG/group_2.258.1130788413
+DGSYSTEM/HFWMSDB/ONLINELOG/group_3.259.1130788413
+DGDATA01/HFWMSDB/ONLINELOG/group_3.259.1130788415
6 rows selected.
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
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
SQL> show parameter recover;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DGRECOVERY
db_recovery_file_dest_size big integer 100G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> CREATE TABLESPACE hfedu01 DATAFILE '+dgdata01' SIZE 50M autoextend off;
Tablespace created.
SQL> create user hfedu01 identified by hfedu01 default tablespace hfedu01；
2 ;
create user hfedu01 identified by hfedu01 default tablespace hfedu01；
*
ERROR at line 1:
ORA-00911: invalid character
SQL> create user hfedu01 identified by hfedu01 default tablespace hfedu01;
User created.
SQL> conn hfedu01/hfedu01
ERROR:
ORA-01045: user HFEDU01 lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> grant dba to hfedu01;
SP2-0640: Not connected
SQL>
SQL> conn /as sysdba
Connected.
SQL> grant dba to hfedu01;
Grant succeeded.
SQL> conn hfedu01/hfedu01
Connected.
SQL> create table hfedu01(id number,name varchar2(20));
Table created.
SQL> insert into hfedu01 values(1,'itpux1');
insert into hfedu01 values(2,'itpux2');
1 row created.
2
SQL> commit;
select * from hfedu01;
Commit complete.
SQL
2
SQL>
SQL> select * from hfedu01;
ID NAME
---------- --------------------
1 itpux1
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint
2 ;
System altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@hfwmsdb98:/home/oracle]$rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Apr 3 23:04:56 2023
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: HFWMSDB (DBID=244624565)
RMAN> backup database format '/backup/fgwmsdb_%s_%p_%t';
Starting backup at 2023-04-03 23:05:17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3416 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DGSYSTEM/HFWMSDB/DATAFILE/system.260.1130788415
input datafile file number=00002 name=+DGSYSTEM/HFWMSDB/DATAFILE/sysaux.261.1130788423
input datafile file number=00003 name=+DGSYSTEM/HFWMSDB/DATAFILE/undotbs1.262.1130788427
input datafile file number=00005 name=+DGDATA01/HFWMSDB/DATAFILE/hfedu01.260.1133218733
input datafile file number=00004 name=+DGSYSTEM/HFWMSDB/DATAFILE/users.264.1130788451
channel ORA_DISK_1: starting piece 1 at 2023-04-03 23:05:20
channel ORA_DISK_1: finished piece 1 at 2023-04-03 23:05:45
piece handle=/backup/fgwmsdb_1_1_1133219120 tag=TAG20230403T230520 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2023-04-03 23:05:45
Starting Control File and SPFILE Autobackup at 2023-04-03 23:05:45
piece handle=+DGRECOVERY/HFWMSDB/AUTOBACKUP/2023_04_03/s_1133219146.258.1133219147 comment=NONE
Finished Control File and SPFILE Autobackup at 2023-04-03 23:05:47
RMAN> exit
Recovery Manager complete.
[oracle@hfwmsdb98:/home/oracle]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 3 23:06:17 2023
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
SQL> alter database backup controlfile to '/backup/fgwmsdb_control.ctl';
alter database backup controlfile to trace as '/backup/fgwmsdb_control.trc';
Database altered.
SQL>
Database altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@hfwmsdb98:/home/oracle]$mkdir -p /oracle/oradata/hfwmsdb
[oracle@hfwmsdb98:/home/oracle]$mkdir -p /oracle/arch
[oracle@hfwmsdb98:/home/oracle]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 3 23:07:50 2023
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DGSYSTEM/HFWMSDB/PARAMETERFIL
E/spfile.265.1130792273
SQL> create pfile='/backup/pfile.ora' from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@hfwmsdb98:/home/oracle]$cd $ORACLE_HOME/dbs
[oracle@hfwmsdb98:/oracle/app/oracle/product/19c/db_1/dbs]$ls
hc_hfwmsdb.dat init.ora lkHFWMSDB orapwhfwmsdb snapcf_hfwmsdb.f
[oracle@hfwmsdb98:/oracle/app/oracle/product/19c/db_1/dbs]$pwd
/oracle/app/oracle/product/19c/db_1/dbs
[oracle@hfwmsdb98:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 3 23:10:04 2023
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
SQL> create spfile='/oracle/app/oracle/product/19c/db_1/dbs/spfilehfwmsdb.ora' from pfile='/backup/pfile.or a';
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 671088640 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/19c
/db_1/dbs/spfilehfwmsdb.ora
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DGSYSTEM/HFWMSDB/CONTROLFILE/
current.256.1130788409, +DGDAT
A01/HFWMSDB/CONTROLFILE/curren
t.256.1130788409
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> alter system set control_files='/oracle/oradata/hfwmsdb/control01.ctl','/oracle/oradata/hfwmsdb/contro l02.ctl' scope=spfile;
System altered.
SQL> show parameter db_create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DGSYSTEM
db_create_online_log_dest_1 string +DGSYSTEM
db_create_online_log_dest_2 string +DGDATA01
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter system set db_create_file_dest='/oracle/oradata/hfwmsdb' scope=spfile;
System altered.
SQL> alter system set db_create_online_log_dest_1='/oracle/oradata/hfwmsdb' scope=spfile;
System altered.
SQL> alter system set db_create_online_log_dest_2='/oracle/oradata/hfwmsdb' scope=spfile;
System altered.
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +DGRECOVERY
db_recovery_file_dest_size big integer 100G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> set system set db_recovery_file_dest='/oracle/arch';
SP2-0158: unknown SET option "system"
SQL> alter syetem set db_recovery_file_dest='/oracle/arch';
alter syetem set db_recovery_file_dest='/oracle/arch'
*
ERROR at line 1:
ORA-00940: invalid ALTER command
SQL> alter system set db_recovery_file_dest='/oracle/arch';
System altered.
SQL> show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oracle/arch
db_recovery_file_dest_size big integer 100G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 671088640 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@hfwmsdb98:/oracle/app/oracle/product/19c/db_1/dbs]$rman target/
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Apr 3 23:21:31 2023
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: HFWMSDB (not mounted)
RMAN> restore controlfile from '+DGRECOVERY/HFWMSDB/AUTOBACKUP/2023_04_03/s_1133219146.258.1133219147';
Starting restore at 2023-04-03 23:23:07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1142 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
output file name=/oracle/oradata/hfwmsdb/control01.ctl
output file name=/oracle/oradata/hfwmsdb/control02.ctl
Finished restore at 2023-04-03 23:23:15
RMAN> alter database mount
2> ;
released channel: ORA_DISK_1
Statement processed
RMAN> backup as copy database format '/oracle/oradata/hfwmsdb/%U';
Starting backup at 2023-04-03 23:25:11
Starting implicit crosscheck backup at 2023-04-03 23:25:11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1145 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 2023-04-03 23:25:12
Starting implicit crosscheck copy at 2023-04-03 23:25:12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2023-04-03 23:25:12
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DGSYSTEM/HFWMSDB/DATAFILE/system.260.1130788415
output file name=/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-SYSTEM_FNO-1_041on4ep tag=TAG2023040 3T232513 RECID=1 STAMP=1133220320
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DGSYSTEM/HFWMSDB/DATAFILE/sysaux.261.1130788423
output file name=/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-SYSAUX_FNO-2_051on4f1 tag=TAG2023040 3T232513 RECID=2 STAMP=1133220326
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DGSYSTEM/HFWMSDB/DATAFILE/undotbs1.262.1130788427
output file name=/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-UNDOTBS1_FNO-3_061on4f8 tag=TAG20230 403T232513 RECID=3 STAMP=1133220337
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DGDATA01/HFWMSDB/DATAFILE/hfedu01.260.1133218733
output file name=/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-HFEDU01_FNO-5_071on4fn tag=TAG202304 03T232513 RECID=4 STAMP=1133220344
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DGSYSTEM/HFWMSDB/DATAFILE/users.264.1130788451
output file name=/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-USERS_FNO-4_081on4fo tag=TAG20230403 T232513 RECID=5 STAMP=1133220345
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 2023-04-03 23:25:45
RMAN-06497: warning: control file is not current, control file AUTOBACKUP skipped
RMAN> switch database to copy;
datafile 1 switched to datafile copy "/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-SYSTEM_FNO-1_04 1on4ep"
datafile 2 switched to datafile copy "/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-SYSAUX_FNO-2_05 1on4f1"
datafile 3 switched to datafile copy "/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-UNDOTBS1_FNO-3_ 061on4f8"
datafile 4 switched to datafile copy "/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-USERS_FNO-4_081 on4fo"
datafile 5 switched to datafile copy "/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-HFEDU01_FNO-5_0 71on4fn"
RMAN> exit
Recovery Manager complete.
[oracle@hfwmsdb98:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 3 23:26:47 2023
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
SQL>
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
'/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-SYSTEM_FNO-1_041on4ep'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
'/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-SYSTEM_FNO-1_041on4ep'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 865705 generated at 04/03/2023 23:03:55 needed for thread 1
ORA-00289: suggestion :
/oracle/arch/HFWMSDB/archivelog/2023_04_03/o1_mf_1_14_%u_.arc
ORA-00280: change 865705 for thread 1 is in sequence #14
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
^C
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1:
'/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-SYSTEM_FNO-1_041on4ep'
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@hfwmsdb98:/oracle/app/oracle/product/19c/db_1/dbs]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 3 23:32:28 2023
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME CON_ID
------------------- ----------
1 1 13 209715200 512 2 YES
INACTIVE 864498 2023-04-03 22:54:12 865581
2023-04-03 23:03:55 0
3 1 12 209715200 512 2 YES
INACTIVE 784897 2023-03-06 22:02:28 864498
2023-04-03 22:54:12 0
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME CON_ID
------------------- ----------
2 1 14 209715200 512 2 NO
CURRENT 865581 2023-04-03 23:03:55 9.2954E+18
0
SQL> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_ CON_ID
--- ----------
1 ONLINE
+DGSYSTEM/HFWMSDB/ONLINELOG/group_1.257.1130788409
NO 0
1 ONLINE
+DGDATA01/HFWMSDB/ONLINELOG/group_1.257.1130788411
NO 0
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_ CON_ID
--- ----------
2 ONLINE
+DGSYSTEM/HFWMSDB/ONLINELOG/group_2.258.1130788411
NO 0
2 ONLINE
+DGDATA01/HFWMSDB/ONLINELOG/group_2.258.1130788413
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_ CON_ID
--- ----------
NO 0
3 ONLINE
+DGSYSTEM/HFWMSDB/ONLINELOG/group_3.259.1130788413
NO 0
3 ONLINE
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
IS_ CON_ID
--- ----------
+DGDATA01/HFWMSDB/ONLINELOG/group_3.259.1130788415
NO 0
6 rows selected.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 865705 generated at 04/03/2023 23:03:55 needed for thread 1
ORA-00289: suggestion :
/oracle/arch/HFWMSDB/archivelog/2023_04_03/o1_mf_1_14_%u_.arc
ORA-00280: change 865705 for thread 1 is in sequence #14
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DGSYSTEM/HFWMSDB/ONLINELOG/group_2.258.1130788411
Log applied.
Media recovery complete.
SQL> alter database open
2 ;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
+DGSYSTEM/HFWMSDB/TEMPFILE/temp.263.1130788429
SQL> alter database tempfile '+DGSYSTEM/HFWMSDB/TEMPFILE/temp.263.1130788429' drop including datafile;
alter database tempfile '+DGSYSTEM/HFWMSDB/TEMPFILE/temp.263.1130788429' drop including datafile
*
ERROR at line 1:
ORA-00905: missing keyword
SQL> alter database tempfile '+DGSYSTEM/HFWMSDB/TEMPFILE/temp.263.1130788429' drop including datafiles;
Database altered.
SQL> alter tablespace temp add tempfile '/oracle/oradata/hfwmsdb/temp01.dbf size 50M autoextend off;
alter tablespace temp add tempfile '/oracle/oradata/hfwmsdb/temp01.dbf size 50M autoextend off
*
ERROR at line 1:
ORA-01756: quoted string not properly terminated
SQL> alter tablespace temp add tempfile'/oracle/oradata/hfwmsdb/temp01.dbf' size 50M autoextend off;
Tablespace altered.
SQL> select file_name from dba_temp_files;
FILE_NAME
--------------------------------------------------------------------------------
/oracle/oradata/hfwmsdb/temp01.dbf
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DGSYSTEM/HFWMSDB/ONLINELOG/group_1.257.1130788409
+DGDATA01/HFWMSDB/ONLINELOG/group_1.257.1130788411
+DGSYSTEM/HFWMSDB/ONLINELOG/group_2.258.1130788411
+DGDATA01/HFWMSDB/ONLINELOG/group_2.258.1130788413
+DGSYSTEM/HFWMSDB/ONLINELOG/group_3.259.1130788413
+DGDATA01/HFWMSDB/ONLINELOG/group_3.259.1130788415
6 rows selected.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME CON_ID
------------------- ----------
1 1 1 209715200 512 2 NO
CURRENT 868379 2023-04-03 23:34:38 9.2954E+18
0
2 1 0 209715200 512 2 YES
UNUSED 0 0
0
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME CON_ID
------------------- ----------
3 1 0 209715200 512 2 YES
UNUSED 0 0
0
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 ('/oracle/oradata/hfwmsdb/redo02.log') size 100M;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 ('/oracle/oradata/hfwmsdb/redo03.log') size 100M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint
2 ;
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME CON_ID
------------------- ----------
1 1 1 209715200 512 2 YES
INACTIVE 868379 2023-04-03 23:34:38 870908
2023-04-03 23:43:08 0
2 1 2 104857600 512 1 NO
CURRENT 870908 2023-04-03 23:43:08 9.2954E+18
0
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME CON_ID
------------------- ----------
3 1 0 104857600 512 1 YES
UNUSED 0 0
0
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 ('/oracle/oradata/hfwmsdb/redo01.log') size 100M;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oracle/oradata/hfwmsdb/redo01.log
/oracle/oradata/hfwmsdb/redo02.log
/oracle/oradata/hfwmsdb/redo03.log
SQL> select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-SYSTEM_FNO-1_041on4ep
/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-SYSAUX_FNO-2_051on4f1
/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-UNDOTBS1_FNO-3_061on4f8
/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-USERS_FNO-4_081on4fo
/oracle/oradata/hfwmsdb/data_D-HFWMSDB_I-244624565_TS-HFEDU01_FNO-5_071on4fn
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oracle/oradata/hfwmsdb/control01.ctl
/oracle/oradata/hfwmsdb/control02.ctl
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@hfwmsdb98:/oracle/app/oracle/product/19c/db_1/dbs]$exit
logout
[root@hfwmsdb98 ~]# crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'hfwmsdb98'
CRS-2673: Attempting to stop 'ora.DGDATA02.dg' on 'hfwmsdb98'
CRS-2673: Attempting to stop 'ora.DGGRID.dg' on 'hfwmsdb98'
CRS-2673: Attempting to stop 'ora.DGRECOVERY.dg' on 'hfwmsdb98'
CRS-2673: Attempting to stop 'ora.DGDATA01.dg' on 'hfwmsdb98'
CRS-2673: Attempting to stop 'ora.DGSYSTEM.dg' on 'hfwmsdb98'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'hfwmsdb98'
CRS-2677: Stop of 'ora.DGDATA01.dg' on 'hfwmsdb98' succeeded
CRS-2677: Stop of 'ora.DGGRID.dg' on 'hfwmsdb98' succeeded
CRS-2677: Stop of 'ora.DGRECOVERY.dg' on 'hfwmsdb98' succeeded
CRS-2677: Stop of 'ora.DGSYSTEM.dg' on 'hfwmsdb98' succeeded
CRS-2677: Stop of 'ora.DGDATA02.dg' on 'hfwmsdb98' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'hfwmsdb98'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'hfwmsdb98' succeeded
CRS-2677: Stop of 'ora.asm' on 'hfwmsdb98' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'hfwmsdb98'
CRS-2677: Stop of 'ora.evmd' on 'hfwmsdb98' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'hfwmsdb98'
CRS-2677: Stop of 'ora.cssd' on 'hfwmsdb98' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'hfwmsdb98' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@hfwmsdb98 ~]# su - oracle
Last login: Mon Apr 3 22:43:25 CST 2023 on pts/0
[oracle@hfwmsdb98:/home/oracle]$sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 3 23:47:01 2023
Version 19.7.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 671088640 bytes
Database Buffers 1744830464 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@hfwmsdb98:/home/oracle]$tail -200f /oracle/app/oracle/diag/rdbms/hfwmsdb/hfwmsdb/trace/alert_hfwmsdb.log