좌충우돌 IT이야기

원인: /u01/app/oracle 경로에 설치된 오라클을 /sw/oracle 경로로 이동

mv oracle /sw/oracle

에러: 경로를 이관했더니 에러가 발생

oracle@homesystem:~$ su -
암호: 
-bash: /u01/app/oracle/product/11.2.0/xe/bin/oracle_env.sh: 그런 파일이나 디렉터리가 없습니다

해결방안: bash.bashrc 수정

root@homesystem:/etc# vi bash.bashrc

#oracle_env 경로 변경
. /sw/oracle/product/11.2.0/xe/bin/oracle_env.sh

작업

alter system set memory_target=0 scope=spfile;
alter system set memory_max_target=0 scope=spfile;
alter system set sga_target=1024M scope=spfile;
shutdown immediate;

(에러발생) ORA-47500

startup
ORA-47500: XE edition memory parameter invalid or not specified

(에러발생) ORA-01078

startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora'

(해결방안) init<SID> 복사

oracle@homesystem:/u01/app/oracle/product/11.2.0/xe/dbs$ cp -p init.ora initXE.ora
oracle@homesystem:/u01/app/oracle/product/11.2.0/xe/dbs$ ls -alt
합계 36
drwxr-xr-x  2 oracle dba    4096  3월 14 21:55 .
-rw-rw----  1 oracle dba    1544  3월 14 21:36 hc_XE.dat
-rw-rw-r--  1 oracle dba     823  3월 14 21:11 spfileXE.ora
-rw-rw-r--  1 oracle dba     823  3월 14 21:11 spfileXE.ora_20220314
-rw-r-----  1 oracle oracle 1536  3월 13 13:21 orapwXE
-rw-rw----  1 oracle dba      24  3월 13 13:20 lkXE
drwxr-xr-x 26 oracle dba    4096  3월 13 13:19 ..
-rwxr-xr-x  1 oracle dba    2851  8월 29  2011 init.ora
-rwxr-xr-x  1 oracle dba    2851  8월 29  2011 initXE.ora

(에러발생) ORA-01078

SQL> startup
ORA-48108: invalid value given for the diagnostic_dest init.ora parameter
ORA-48140: the specified ADR Base directory does not exist [/u01/app/oracle/product/11.2.0/xe/dbs/<ORACLE_BASE>]
ORA-48187: specified directory does not exist
Linux-x86_64 Error: 2: No such file or directory
Additional information: 1

(해결방안) initXE.ora 파일 ORACLE_BASE 변경

# Before
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='<ORACLE_BASE>/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='<ORACLE_BASE>'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'


#After
db_name='ORCL'
memory_target=1G
processes = 150
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control1, ora_control2)
compatible ='11.2.0'

(에러3) ORA-01261

SQL> startup
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux-x86_64 Error: 2: No such file or directory

(해결방안) 폴더복사

oracle@homesystem:/u01/app/oracle/admin/orcl$ cd /u01/app/oracle/diag/rdbms/orcl/XE/trace
oracle@homesystem:/u01/app/oracle/diag/rdbms/orcl/XE/trace$ vi alert*.log

# VI 내용
OS Audit file could not be created; failing after 5 retries
Could not open audit file: /u01/app/oracle/admin/orcl/adump/xe_ora_5918_1.aud

# orcl 생성
oracle@homesystem:/u01/app/oracle/admin/XE$ mkdri orcl

# XE 복사
oracle@homesystem:/u01/app/oracle/admin$ cp -r XE orcl

# 폴더 이동
oracle@homesystem:/u01/app/oracle/admin/orcl/XE$ mv adump dbs dpump pfile ../
oracle@homesystem:/u01/app/oracle/diag/rdbms/orcl/XE/trace$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on 월 3월 14 22:49:22 2022

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>