ASM+RAC spfile 恢复改名

最新推荐文章于 2024-06-24 10:52:58 发布

jnrjian

最新推荐文章于 2024-06-24 10:52:58 发布

阅读量678

点赞数 17

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/jnrjian/article/details/139397195

版权

ASMCMD> cd +DATA/CDB/PARAMETERFILE
ASMCMD> ls
spfile.290.1148481317
ASMCMD> rm -rf *
ASMCMD> ls

srvctl config database -db 'cdb' ''不用也可以
[oracle@rac1 dbs]$ srvctl modify database -db cdb -spfile ''----注释掉
[oracle@rac1 dbs]$ srvctl modify database -db cdb -spfile '+DATA/CDB/PARAMETERFILE/spfile';

srvctl config database -d cdb |grep Spfile

– srvctl修改使用最新的spfile
srvctl modify database -db cdb -spfile ‘+DATA_DG/xxooQBQA/PARAMETERFILE/spfile.1123.1134548913’

-----------

– 环境: ORACLE RAC Version 19.15.0.0.0 ASM
– 背景: 因为修改错了sga参数导致sga_target的值过小, 数据库启动不了报错信息如下
– 重点: 本过程适用于spfile存放在ASM里面的创建, 如果存放在文件系统中,会简单很多,这里不列举

– srvctl start database -d xxooqbqa 启动报错
PRCR-1079 : Failed to start resource ora.xxooqbqa.db
CRS-5017: The resource action “ora.xxooqbqa.db start” encountered the following error:
ORA-01078: failure in processing system parameters
. For details refer to “(:CLSN00107:)” in “/oracle_grid/app/oracle/diag/crs/xxoo180085/crs/trace/crsd_oraagent_ot12c.trc”.

CRS-2674: Start of ‘ora.xxooqbqa.db’ on ‘xxoo180085’ failed
CRS-2632: There are no more servers to try to place resource ‘ora.xxooqbqa.db’ on that would satisfy its placement policy
CRS-5017: The resource action “ora.xxooqbqa.db start” encountered the following error:
ORA-01078: failure in processing system parameters
. For details refer to “(:CLSN00107:)” in “/oracle_grid/app/oracle/diag/crs/xxoo180086/crs/trace/crsd_oraagent_ot12c.trc”.

CRS-2674: Start of ‘ora.xxooqbqa.db’ on ‘xxoo180086’ failed

– sqlplus / as sysdba
SYS@xxooqbqa1> startup
ORA-00821: Specified value of sga_target 10240M is too small, needs to be at least 13600M
ORA-01078: failure in processing system parameters

– 因为spfile存放在ASM,数据库启动不了的情况下, 不能通过下面这种方式进行创建pfile
SYS@xxooqbqa1> create pfile=’/tmp/initfgzcdb.ora.220101’ from spfile;
create pfile=’/tmp/initfgzcdb.ora.220101’ from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file ‘?=/dbs/spfile@.ora’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

– 查看使用的spfile
[oracle@xxoo180085 ~]$ srvctl config database -d xxooqbqa|grep Spfile
Spfile: +DATA_DG/xxooQBQA/PARAMETERFILE/spfile.1035.1134491161

–

– 在oracle里面可以读取ASM磁盘里面的二进制文件spfile,将其转为文本格式的pfile
SYS@xxooqbqa1> create pfile=’/tmp/initxxooqbqa1.ora’ from spfile=’+DATA_DG/xxooQBQA/PARAMETERFILE/spfile.1035.1134491161’;

File created.

SYS@xxooqbqa1> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
xxoo180085:xxooqbqa1 >

– 修改文本格式的pfile里面的sga_target为20g,原来是10g
vi /tmp/initxxooqbqa1.ora
*.sga_target=20g

– 这是oracle里面看spfile的值还是空值
SYS@xxooqbqa1> show parameter spfile

NAME TYPE VALUE

spfile string
SYS@xxooqbqa1>
SYS@xxooqbqa1>

– 关闭数据库实例
SYS@xxooqbqa1> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.

– 通过修改后的pfile启动
SYS@xxooqbqa1> startup pfile=’/tmp/initxxooqbqa1.ora’ nomount;

– 因为ASM是自动管理的不能直接在ASM里面指定spfile的文件名
SYS@xxooqbqa1> create spfile=’+DATA_DG/xxooQBQA/PARAMETERFILE/spfile.1036.1134491161’ from pfile=’/tmp/initxxooqbqa1.ora’;
create spfile=’+DATA_DG/xxooQBQA/PARAMETERFILE/spfile.1036.1134491161’ from pfile=’/tmp/initxxooqbqa1.ora’
*
ERROR at line 1:
ORA-17502: ksfdcre:4 Failed to create file +DATA_DG/xxooQBQA/PARAMETERFILE/spfile.1036.1134491161
ORA-15046: ASM file name ‘+DATA_DG/xxooQBQA/PARAMETERFILE/spfile.1036.1134491161’ is not in single-file creation form

– 直接通过pfile创建spfile
SYS@xxooqbqa1> create spfile from pfile=’/tmp/initxxooqbqa1.ora’;

---啥都不带自动命名

File created.

– 这时候在oracle里面spfile还是为空
SYS@xxooqbqa1> show parameter pfile

NAME TYPE VALUE

spfile string

– srvctl查看配置信息里的spfile还是旧的spfile文件
[oracle@xxoo180085 ~]$ srvctl config database -d xxooqbqa|grep Spfile
Spfile: +DATA_DG/xxooQBQA/PARAMETERFILE/spfile.1035.1134491161

– 实际在ASM里面已将创建了新的spfile文件
[grid@xxoo180085 ~]$ asmcmd ls -l +DATA_DG/xxooQBQA/PARAMETERFILE/
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE APR 19 08:00:00 Y spfile.1035.1134491161
PARAMETERFILE UNPROT COARSE APR 19 08:00:00 Y spfile.1123.1134548913

– srvctl修改使用最新的spfile
srvctl modify database -db xxooqbqa -spfile ‘+DATA_DG/xxooQBQA/PARAMETERFILE/spfile.1123.1134548913’ – 指定最新的参数文件

– 确认最新的spfile文件
[oracle@xxoo180085 ~]$ srvctl config database -d xxooqbqa|grep Spfile
Spfile: +DATA_DG/xxooQBQA/PARAMETERFILE/spfile.1123.1134548913

– 停实例
SYS@xxooqbqa1> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.

– srvctl 重启两个实例
srvctl start database xxooqbqa
xxoo180085:xxooqbqa1 > srvctl status database -d xxooqbqa
Instance xxooqbqa1 is running on node xxoo180085
Instance xxooqbqa2 is running on node xxoo180086

-----

在Oracle中，有一个名叫DUMMY的假实例。

可以用作spfile恢复。

1、export ORACLE_SID=DUMMY

2、rman target /

3、RMAN>startup nomount;

4、使用srvctl工具查找spfile文化路径

su - grid

$srvctl config database -d orcl -a

5、su - oracle

RMAN>restore spfile to '+ASMDATA/orcl/spfileorcl.ora' from '/rmanbak/spfile.bak';

6、恢复完成后，停止掉DUMMY实例

RMAN>shutdown immediate;

RMAN>quit

7、su - grid

$srvctl start database -d orcl

8、正常启动，恢复完成。

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 2 16:04:01 2024
Version 19.20.0.0.0

connected to target database (not started)

RMAN> restore spfile from '+DATA1/CDB/AUTOBACKUP/2024_06_02/s_1170604877.496.1170561677';

Starting restore at 02-JUN-24
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/02/2024 16:04:06
RMAN-12010: automatic channel allocation initialization failed
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4476
Additional information: -996112533

RMAN> exit

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 2 16:04:32 2024
Version 19.20.0.0.0

connected to target database: CDB (DBID=2265125454)

RMAN> shutdown abort

using target database control file instead of recovery catalog
Oracle instance shut down

RMAN> startup nomount pfile=dummy;

connected to target database (not started)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 06/02/2024 16:05:00
RMAN-04014: startup failed: ORA-01078: failure in processing system parameters
RMAN-04017: startup error description: LRM-00109: could not open parameter file 'dummy'

RMAN> set dbid 2265125454

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 02-JUN-24
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/02/2024 16:05:51
RMAN-12010: automatic channel allocation initialization failed
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4476
Additional information: -996112533

RMAN> set dbid 2265125454

executing command: SET DBID

RMAN> set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+DATA1/CDB/AUTOBACKUP/2024_06_02/s_1170604877.496.1170561677';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 06/02/2024 16:07:09
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4476
Additional information: -996112533

RMAN> show all;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of show command at 06/02/2024 16:07:35
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 4476
Additional information: -996112533

[oracle@rac1 ~]$ export ORACLE_SID=DUMMY
[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 2 16:09:33 2024
Version 19.20.0.0.0

connected to target database (not started)

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/19.0.0/db_1/dbs/initDUMMY.ora'

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

Total System Global Area 1073737776 bytes

Fixed Size 8933424 bytes
Variable Size 276824064 bytes
Database Buffers 780140544 bytes
Redo Buffers 7839744 bytes

RMAN> restore spfile from autobackup;

Starting restore at 02-JUN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=185 device type=DISK

RMAN> set dbid 2265125454

executing command: SET DBID

RMAN> exit

Recovery Manager complete.
[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 2 16:10:27 2024
Version 19.20.0.0.0

connected to target database: DUMMY (not mounted)

RMAN> restore spfile from '+DATA1/CDB/AUTOBACKUP/2024_06_02/s_1170604877.496.1170561677';

Starting restore at 02-JUN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=162 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP +DATA1/CDB/AUTOBACKUP/2024_06_02/s_1170604877.496.1170561677
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 02-JUN-24

RMAN> exit

Recovery Manager complete.
[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Jun 2 16:10:55 2024
Version 19.20.0.0.0

connected to target database: DUMMY (not mounted)

RMAN> set dbid 2265125454

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 02-JUN-24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=185 device type=DISK

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240602
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240601
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240531
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240530
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240529
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240528
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240527
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/02/2024 16:11:49
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

RMAN> restore spfile from '+DATA1/CDB/AUTOBACKUP/2024_06_02/s_1170604877.496.1170561677';