Skip to main content

ORA-27046: file size is not a multiple of logical block size and SP2-0714: invalid combination of STARTUP options

My DB Configuration :

Proddb: Source GoldenGate Database
Testdb: Target GoldenGate Database
DB Backups: None
SPFILE/PFILE Backup: None

What Happened:

The SPFILE in the source database (Proddb) got corrupted.

I attempted to increase sga_max_size and streams_pool_size. After making these changes and issuing a STARTUP FORCE command to bounce the database, my Proddb (source) database failed to start.

Unfortunately, there were no backups of the database, SPFILE, or PFILE.

Solution (What I Did):

Since the target server (Testdb) had valid parameter values and was similar to Proddb:

  1. I generated a PFILE from the SPFILE on the Testdb server.
  2. Transferred the PFILE to the Proddb server.
  3. Edited the PFILE with proddb values and Started the Proddb database using the PFILE.
  4. Created a new SPFILE from the PFILE.
  5. Shut down the database and started it again using the newly created SPFILE.
  6. The database is now up and running with the newly created SPFILE.

STEPS AND COMMANDS USED : 

Source Server :

SQL>
SQL> startup spfile=/u01/app/oracle/product/12.1.0/db_1/dbs/spfileproddb.ora';
SP2-0714: invalid combination of STARTUP options
SQL>

SQL> create pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initproddb.ora' from spfile= '/u01/app/oracle/product/12.1.0/db_1/dbs/spfileproddb.ora';
create pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initproddb.ora' from spfile=      '/u01/app/oracle/product/12.1.0/db_1/dbs/spfileproddb.ora'
*
ERROR at line 1:
ORA-01565: error in identifying file
'/u01/app/oracle/product/12.1.0/db_1/dbs/spfileproddb.ora'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

Target Server :

[oracle@ggreplicat dbs]$ db
SQL*Plus: Release 12.1.0.2.0 Production on Sun Dec 29 21:29:59 2024
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create pfile from spfile;
File created.
SQL> exit

-rw-r--r--. 1 oracle oinstall 1.1K Dec 29 21:30 inittestdb.ora
[oracle@ggreplicat dbs]$
[oracle@ggreplicat dbs]$
[oracle@ggreplicat dbs]$
[oracle@ggreplicat dbs]$ date
Sun Dec 29 21:30:17 +04 2024
[oracle@ggreplicat dbs]$
[oracle@ggreplicat dbs]$
[oracle@ggreplicat dbs]$
[oracle@ggreplicat dbs]$
[oracle@ggreplicat dbs]$ scp inittestdb.ora target_ip:/u01/app/oracle/product/12.1.0/db_1/dbs
oracle@target_ip's password:
inittestdb.ora                                                                                                              100% 1061     1.0KB/s   00:00
[oracle@ggreplicat dbs]$
[oracle@ggreplicat dbs]$

Source Server :

Make sure to replace testdb (Target Database's name) to proddb (Source Database's name) also check if the path destinations mentioned in the pfile are valid. If not you need to edit it accordingly.

[oracle@ggextract dbs]$
[oracle@ggextract dbs]$ vi initproddb.ora
[oracle@ggextract dbs]$
[oracle@ggextract dbs]$

[oracle@ggextract dbs]$ ls -ld /u01/app/oracle/oradata/proddb/control01.ctl
-rw-r-----. 1 oracle oinstall 10043392 Dec 29 17:09 /u01/app/oracle/oradata/proddb/control01.ctl
[oracle@ggextract dbs]$
[oracle@ggextract dbs]$
[oracle@ggextract dbs]$ ls -ld /u01/app/oracle/fast_recovery_area/proddb/control02.ctl
-rw-r-----. 1 oracle oinstall 10043392 Dec 29 17:09 /u01/app/oracle/fast_recovery_area/proddb/control02.ctl
[oracle@ggextract dbs]$
[oracle@ggextract dbs]$
[oracle@ggextract dbs]$ ls -ld /u01/app/oracle/admin/proddb/adump
drwxr-x---. 2 oracle oinstall 24576 Dec 29 21:09 /u01/app/oracle/admin/proddb/adump
[oracle@ggextract dbs]$
[oracle@ggextract dbs]$ ls -ld /u01/app/oracle/fast_recovery_area
drwxrwxr-x. 4 oracle oinstall 4096 Jun 18  2024 /u01/app/oracle/fast_recovery_area
[oracle@ggextract dbs]$


[oracle@ggextract dbs]$ db
SQL*Plus: Release 12.1.0.2.0 Production on Sun Dec 29 21:34:42 2024
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initproddb.ora';
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             905973664 bytes
Database Buffers          587202560 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.

SQL>
SQL> !mv spfileproddb.ora spfileproddb.ora_old

SQL> create spfile='/u01/app/oracle/product/12.1.0/db_1/dbs/spfileproddb.ora' from pfile='/u01/app/oracle/product/12.1.0/db_1/dbs/initproddb.ora';

File created.

SQL>

[oracle@ggextract dbs]$ db
SQL*Plus: Release 12.1.0.2.0 Production on Sun Dec 29 21:37:16 2024
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             905973664 bytes
Database Buffers          587202560 bytes
Redo Buffers               13848576 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL>
SQL> sho parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.1.0
                                                 /db_1/dbs/spfileproddb.ora
SQL>


Labels:

Comments

Post a Comment

Popular posts from this blog

HOW TO START A SINGLE INSTANCE OR ALL NODES OF A RAC DATABASE ? DEFINITION: IN RAC (REAL APPLICATION CLUSTERS), INSTEAD OF THE USUAL SETUP WITH ONE INSTANCE AND ONE DATABASE, WE HAVE MULTIPLE INSTANCES CONNECTING TO A SINGLE DATABASE. USAGE: LOAD BALANCING, HIGH AVAILABILITY ASSUMPTION : ASM AND CRS SERVICES ARE ALREADY UP AND RUNNING 1). STARTING ONLY ONE INSTANCE OF A RAC DATABASE SCENARIO :  ASM and CRS SERVICES ARE UP, DATABASE AND INSTANCES ARE DOWN               su - grid (or) su - root               cd $GRID_HOME/bin               ./crsctl check crs                    su - oracle                   srvctl status database -d db_name               srvctl start instance -d db_name -i instance_name     ...
2 comments
Read more

ORACLE DATA GUARD COMMANDS

Hey Guys!! I have provided some important and useful commands that is used during monitoring and troubleshooting Oracle Data Guard related Issues. PFB. TO CHECK THE STATUS OF THE DATABASE - PRIMARY AND STANDBY: ========================================================== SET LINESIZE 189 PAGES 2000 COL NAME FOR A10 COL HOST_NAME FOR A30 SELECT NAME, INSTANCE_NAME, DB_UNIQUE_NAME, HOST_NAME, STATUS, OPEN_MODE, DATABASE_ROLE AS DB_ROLE, LOGINS FROM V$DATABASE, GV$INSTANCE; TO CHECK STANDBY INFO : ======================= SET LINESIZE 189 PAGES 2000 COL NAME FOR A10 COL HOST_NAME FOR A20 COL DB_UNIQUE_NAME FOR A20 COL OPEN_MODE FOR A10 COL DG_BROKER FOR A15 SELECT NAME, DB_UNIQUE_NAME, HOST_NAME, DATABASE_ROLE AS DB_ROLE, OPEN_MODE, PROTECTION_MODE AS PROTECT_MODE, PROTECTION_LEVEL AS PROTECT_LEVEL, DATAGUARD_BROKER AS DG_BROKER, SWITCHOVER_STATUS FROM V$DATABASE, GV$INSTANCE; TO CHECK CURRENT DG CONFIGURATION : =================================== SHOW PARAMETER LOG_ARCHIVE_CONFIG; SHOW PARA...
Post a Comment
Read more