ORA-27046: file size is not a multiple of logical block size

OS환경 : Oracle Linux 6.8 (64bit)

DB 환경 : Oracle Database 10.2.0.5, 19.3.0.0

에러 : ORA-27046: file size is not a multiple of logical block size(spfile, pfile 재생성 init.ora)

sqlplus 접속 후 spfile을 이용해 pfile을 생성하려 할 때 발생한 오류 메세지

$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 13 18:09:39 2020
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> create pfile from spfile;
create pfile from spfile
*
ERROR at line 1:
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

해결 방법 : 백업 된 파일을 이용하거나 alert log 파일을 보고 pfile을 생성

방법 1. 백업된 pfile 이용

dbs 폴더에서 백업된 pfile이 있는지 확인

$ cd $ORACLE_HOME/dbs
$ ls -al
total 60
drwxr-x---  2 oracle dba  4096 Jan 13 18:08 .
drwxr-xr-x 60 oracle dba  4096 Oct 14 23:39 ..
-rw-r-----  1 oracle dba  8385 Sep 11  1998 init.ora
-rw-r--r--  1 oracle dba   738 Jan 13 17:21 initORCL10.orabak
-rw-r-----  1 oracle dba  1536 Oct 14 23:42 orapwORCL10
-rw-r-----  1 oracle dba  2560 Jan 13 17:16 spfileORCL10.ora

못쓰는 spfile 삭제 또는 이름 변경

$ mv spfileORCL10.ora spfileORCL10.oradel

백업된 pfile로 startup 

$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 13 18:22:42 2020
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
Connected to an idle instance.
 
SQL> startup pfile='$ORACLE_HOME/dbs/initORCL10.orabak'
ORACLE instance started.
 
Total System Global Area  314572800 bytes
Fixed Size            2095992 bytes
Variable Size          255853704 bytes
Database Buffers       50331648 bytes
Redo Buffers            6291456 bytes
Database mounted.
Database opened.

해당파일로 spfile 재생성

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initORCL10.orabak';
 
File created.

db 재기동(spfile로 기동됨)

$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 13 18:23:31 2020
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup       
ORACLE instance started.
 
Total System Global Area  314572800 bytes
Fixed Size            2095992 bytes
Variable Size          255853704 bytes
Database Buffers       50331648 bytes
Redo Buffers            6291456 bytes
Database mounted.
Database opened.

pfile과 spfile 이 둘다 존재하는경우 우선순위는 spfile임

기존db에 적용되어있던 필요한 파라미터 설정

SQL> alter system set sga_target=4g scope=spfile;
SQL> alter system set ~~~;

방법 2. 최초 기동시 사용된 pfile 이용

만약 백업된 pfile이 없는 경우 아래 경로로 이동해 최초 기동시 사용된 pfile 확인

$ cd $ORACLE_BASE/admin/$ORACLE_SID/pfile
$ ls
init.ora.9142019234510

해당 pfile을 $ORACLE_HOME/dbs/로 복사 후 db 기동

$ cp init.ora.9142019234510 $ORACLE_HOME/dbs/initORCL10.ora
$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 13 18:24:42 2020
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
Connected to an idle instance.
 
SQL> startup pfile='$ORACLE_HOME/dbs/initORCL10.ora'
ORACLE instance started.
 
Total System Global Area  314572800 bytes
Fixed Size            2095992 bytes
Variable Size          255853704 bytes
Database Buffers       50331648 bytes
Redo Buffers            6291456 bytes
Database mounted.
Database opened.

해당파일로 spfile 재생성

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initORCL10.ora';
 
File created.

db 재기동(spfile로 기동됨)

$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 13 18:25:31 2020
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup       
ORACLE instance started.
 
Total System Global Area  314572800 bytes
Fixed Size            2095992 bytes
Variable Size          255853704 bytes
Database Buffers       50331648 bytes
Redo Buffers            6291456 bytes
Database mounted.
Database opened.

기존db에 적용되어있던 필요한 파라미터 설정

SQL> alter system set sga_target=4g scope=spfile;
SQL> alter system set ~~~;

방법 3. alert log을 읽어 pfile 생성

bdump 경로로 가서 alert log 확인

$ cd $ORACLE_BASE/admin/$ORACLE_SID/bdump
$ vi alert_ORCL10.log 
Mon Jan 13 18:34:47 KST 2020
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 3
Using LOG_ARCHIVE_DEST_1 parameter default value as /oracle/app/oracle/product/10.2.0/db_1/dbs/arch
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.5.0.
System parameters with non-default values:
  processes                = 150
  sga_max_size             = 314572800
  control_files            = /oracle/app/oracle/oradata/ORCL10/control01.ctl, /oracle/app/oracle/oradata/ORCL10/control02.ctl, /oracle/app/oracle/oradata/ORCL10/control03.ctl
  db_block_size            = 8192
  compatible               = 10.2.0.5.0
  db_file_multiblock_read_count= 16
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  job_queue_processes      = 10
  background_dump_dest     = /oracle/app/oracle/admin/ORCL10/bdump
  user_dump_dest           = /oracle/app/oracle/admin/ORCL10/udump
  core_dump_dest           = /oracle/app/oracle/admin/ORCL10/cdump
  audit_file_dest          = /oracle/app/oracle/admin/ORCL10/adump
  db_name                  = ORCL10
  open_cursors             = 300
PMON started with pid=2, OS id=3694
PSP0 started with pid=3, OS id=3696

기존에 존재하는 init.ora 파일을 initORCL10.ora(pfile)로 복사

$ cd $ORACLE_HOME/dbs
$ ls -al init.ora 
-rw-r----- 1 oracle dba 8385 Sep 11  1998 init.ora
$ cp init.ora initORCL10.ora

vi 로 파일 열어서 필요한 부분 수정

$ vi initORCL10.ora
db_name=DEFAULT
 
db_files = 80                                                         # SMALL  
 
db_file_multiblock_read_count = 8                                     # SMALL  
 
db_block_buffers = 100                                                 # SMALL  
 
shared_pool_size = 3500000                                            # SMALL
 
log_checkpoint_interval = 10000
 
processes = 50                                                        # SMALL  
 
parallel_max_servers = 5                                              # SMALL
 
log_buffer = 32768                                                    # SMALL
max_dump_file_size = 10240      # limit trace file size to 5 Meg each
 
global_names = TRUE
 
control_files = (ora_control1, ora_control2)

수정하는 것도 방법이지만 그냥 alert log에서 아래 부분만 복사해온 뒤

  processes                = 150
  sga_max_size             = 314572800
  control_files            = /oracle/app/oracle/oradata/ORCL10/control01.ctl, /oracle/app/oracle/oradata/ORCL10/control02.ctl, /oracle/app/oracle/oradata/ORCL10/control03.ctl
  db_block_size            = 8192
  compatible               = 10.2.0.5.0
  db_file_multiblock_read_count= 16
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  job_queue_processes      = 10
  background_dump_dest     = /oracle/app/oracle/admin/ORCL10/bdump
  user_dump_dest           = /oracle/app/oracle/admin/ORCL10/udump
  core_dump_dest           = /oracle/app/oracle/admin/ORCL10/cdump
  audit_file_dest          = /oracle/app/oracle/admin/ORCL10/adump
  db_name                  = ORCL10
  open_cursors             = 300

이 파일을 pfile로 만들어서 startup 시켜도 올라감

$ cat initORCL10.ora 
  processes                = 150
  sga_max_size             = 314572800
  control_files            = /oracle/app/oracle/oradata/ORCL10/control01.ctl, /oracle/app/oracle/oradata/ORCL10/control02.ctl, /oracle/app/oracle/oradata/ORCL10/control03.ctl
  db_block_size            = 8192
  compatible               = 10.2.0.5.0
  db_file_multiblock_read_count= 16
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  job_queue_processes      = 10
  background_dump_dest     = /oracle/app/oracle/admin/ORCL10/bdump
  user_dump_dest           = /oracle/app/oracle/admin/ORCL10/udump
  core_dump_dest           = /oracle/app/oracle/admin/ORCL10/cdump
  audit_file_dest          = /oracle/app/oracle/admin/ORCL10/adump
  db_name                  = ORCL10
  open_cursors             = 300
 
$ sqlplus / as sysdba
SQL> startup pfile='$ORACLE_HOME/dbs/initORCL10.ora'
ORACLE instance started.
 
Total System Global Area  314572800 bytes
Fixed Size            2095992 bytes
Variable Size          255853704 bytes
Database Buffers       50331648 bytes
Redo Buffers            6291456 bytes
Database mounted.
Database opened.

해당파일로 spfile 재생성

SQL> create spfile from pfile='$ORACLE_HOME/dbs/initORCL10.ora';
 
File created.

db 재기동(spfile로 기동됨)

$ sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 13 18:25:31 2020
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup       
ORACLE instance started.
 
Total System Global Area  314572800 bytes
Fixed Size            2095992 bytes
Variable Size          255853704 bytes
Database Buffers       50331648 bytes
Redo Buffers            6291456 bytes
Database mounted.
Database opened.

기존db에 적용되어있던 필요한 파라미터 설정

SQL> alter system set sga_target=4g scope=spfile;
SQL> alter system set ~~~;

방법 4. 메모리에 있는 정보로 pfile 생성(11g new feature)

방법 4는 19c 에서 테스트하였음

sqlplus 접속 후 아래 명령 실행

SQL> create pfile from memory;
 
File created.

$ORACLE_HOME/dbs 경로로 이동 후 pfile 확인

$ cat initorcl.ora 
# Oracle init.ora parameter file generated by instance orcl on 07/05/2020 12:44:56
__data_transfer_cache_size=0
__db_cache_size=992M
__inmemory_ext_roarea=0
__inmemory_ext_rwarea=0
__java_pool_size=0
__large_pool_size=16M
__oracle_base='/app/oracle' # ORACLE_BASE set from environment
__pga_aggregate_target=320M
__sga_target=1600M
__shared_io_pool_size=80M
__shared_pool_size=496M
__streams_pool_size=0
__unified_pga_pool_size=0
_always_anti_join='CHOOSE'
_always_semi_join='CHOOSE'
_ash_size=400M
_b_tree_bitmap_plans=TRUE
_bloom_serial_filter='ON'
_complex_view_merging=TRUE
_compression_compatibility='19.0.0'
_diag_adr_trace_dest='/app/oracle/diag/rdbms/orcl/orcl/trace'
_ds_xt_split_count=1
_eliminate_common_subexpr=TRUE
_fast_full_scan_enabled=TRUE
_generalized_pruning_enabled=TRUE
_gs_anti_semi_join_allowed=TRUE
_hang_resolution_scope='OFF' # _hang_resolution_scope updated by kjznhm
_improved_outerjoin_card=TRUE
_improved_row_length_enabled=TRUE
_index_join_enabled=TRUE
_key_vector_create_pushdown_threshold=20000
_ksb_restart_policy_times='0'
_ksb_restart_policy_times='60'
_ksb_restart_policy_times='120'
_ksb_restart_policy_times='240' # internal update to set default
_left_nested_loops_random=TRUE
_mv_access_compute_fresh_data='ON'
_new_initial_join_orders=TRUE
_new_sort_cost_estimate=TRUE
_nlj_batching_enabled=1
_optim_enhance_nnull_detection=TRUE
_optim_peek_user_binds=TRUE
_optimizer_ads_use_partial_results=TRUE
_optimizer_better_inlist_costing='ALL'
_optimizer_cbqt_or_expansion='ON'
_optimizer_cluster_by_rowid_control=129
_optimizer_control_shard_qry_processing=65528
_optimizer_cost_based_transformation='LINEAR'
_optimizer_cost_model='CHOOSE'
_optimizer_extended_cursor_sharing='UDO'
_optimizer_extended_cursor_sharing_rel='SIMPLE'
_optimizer_extended_stats_usage_control=192
_optimizer_join_order_control=3
_optimizer_max_permutations=2000
_optimizer_mode_force=TRUE
_optimizer_native_full_outer_join='FORCE'
_optimizer_or_expansion='DEPTH'
_optimizer_proc_rate_level='BASIC'
_optimizer_system_stats_usage=TRUE
_optimizer_try_st_before_jppd=TRUE
_optimizer_use_cbqt_star_transformation=TRUE
_or_expand_nvl_predicate=TRUE
_ordered_nested_loop=TRUE
_parallel_broadcast_enabled=TRUE
_pivot_implementation_method='CHOOSE'
_pred_move_around=TRUE
_push_join_predicate=TRUE
_push_join_union_view=TRUE
_push_join_union_view2=TRUE
_px_dist_agg_partial_rollup_pushdown='ADAPTIVE'
_px_groupby_pushdown='FORCE'
_px_partial_rollup_pushdown='ADAPTIVE'
_px_shared_hash_join=FALSE
_px_wif_dfo_declumping='CHOOSE'
_sql_model_unfold_forloops='RUN_TIME'
_sqltune_category_parsed='DEFAULT' # parsed sqltune_category
_subquery_pruning_mv_enabled=FALSE
_table_scan_cost_plus_one=TRUE
_union_rewrite_for_gs='YES_GSET_MVS'
_unnest_subquery=TRUE
_use_column_stats_for_function=TRUE
_xt_sampling_scan_granules='ON'
audit_file_dest='/app/oracle/admin/orcl/adump'
audit_trail='DB'
compatible='19.0.0'
connection_brokers='((TYPE=DEDICATED)(BROKERS=1))'
connection_brokers='((TYPE=EMON)(BROKERS=1))' # connection_brokers default value
control_files='/app/oracle/oradata/ORCL/control01.ctl'
control_files='/app/oracle/oradata/ORCL/control02.ctl'
core_dump_dest='/app/oracle/diag/rdbms/orcl/orcl/cdump'
cpu_count=1
cpu_min_count='1'
db_block_size=8192
db_name='orcl'
diagnostic_dest='/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
job_queue_processes=20 # job queue processes default tuning
local_listener='LISTENER_ORCL'
log_buffer=7360K # log buffer update
nls_language='AMERICAN'
nls_territory='AMERICA'
open_cursors=300
optimizer_mode='ALL_ROWS'
plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
processes=300
query_rewrite_enabled='TRUE'
remote_login_passwordfile='EXCLUSIVE'
resource_manager_plan='SCHEDULER[0x4D53]:DEFAULT_MAINTENANCE_PLAN'
result_cache_max_size=8M
sga_max_size=1600M # internally adjusted
sga_target=1600M
undo_tablespace='UNDOTBS1'
use_large_pages='ONLY'

이 방법의 경우 기존 적용된 파라미터 외에 히든 파라미터까지 모두 나옴

원인 : 기존 spfile의 손상으로 인한 문제

위 작업을 하기 전 사실 spfile을 미리 백업 한 뒤에 /etc/hosts 파일을 spfile에 추가한 상태였음

$ cd $ORACLE_HOME/dbs
$ ls -al
total 60
drwxr-x---  2 oracle dba  4096 Jan 13 18:08 .
drwxr-xr-x 60 oracle dba  4096 Oct 14 23:39 ..
-rw-r-----  1 oracle dba  8385 Sep 11  1998 init.ora
-rw-r--r--  1 oracle dba   738 Jan 13 17:21 initORCL10.orabak
-rw-r-----  1 oracle dba  1536 Oct 14 23:42 orapwORCL10
-rw-r-----  1 oracle dba  2560 Jan 13 17:16 spfileORCL10.ora
$ cp spfileORCL10.ora spfileORCL10.orabak
$ cat /etc/hosts >> spfileORCL10.ora

spfile 및 pfile이 손상가지 않게 관리를 잘해야함

추가내용

pfile, spfile 파일이 둘다 없는 경우에는 아래 오류가 발생함

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/app/oracle/product/10.2.0/db_1/dbs/initORCL10.ora'

참조 : https://blog.naver.com/meldin/60090587998