This section describes how to troubleshoot common Oracle database problems
1. The database password is locked
su -l oracle
source/home/oracle/.bashprofilesource /home/oracle/.bash_profilesource/home/oracle/.bashprofile sqlplus / as sysdba SQL> Alter user User name account unlock; SQL> alter user identified by; SQL> ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 2. Delete the Oracle account
su -l oracle
$ sqlplus /nolog SQL> connect / as sysdba; SQL> drop user user name cascade; SQL> drop TABLESPACE including contents and datafiles cascade constraint; 3, the solution of zobah
SQL> select username,sid,serial# from v$session where username=’SDE’; SQL> alter system kill session’532,4562′; 4. Change the user name and password
SQL> alter user name rename to new user name; SQL> alter user identified by new password; Up sqlplus/nolog; Connect/as sysdba alter user sys identified by newpassword; alter user system identified by newpassword; SQL > alter tablespace autogrowth
SQL> alter database datafile ‘/home/oracle/oradata/orcltar_index.dbf’autoextend on; SQL>alter database datafile ‘/home/oracle/oradata/orcltar.dbf’autoextend on; SQL>alter database datafile ‘/home/oracle/oradata/orcltar.dbf’ size 64m autoextend on next 64m maxsize unlimited; Increase the TABLESPACE SQL > ALTER in TABLESPACE orcl_data ADD DATAFILE ‘/ home/oracle/oradata/orcl_data01 DFG’ size 64 m autoextend on next 64 m maxsize unlimited; 6. Local_listener has no value
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SQL>show parameter instance_name; SQL> echo
ORACLE_HOME SQL> show parameter local listener NAME TYPE VALUE
Local_listener string LOG_ARCHIve_LOCAL_FIRST Boolean TRUE PARALLEL_force_local Boolean FALSE Cause: Local_LISTENER does not have a value
Solution: Set the local_listener parameter
SQL> alter system set local_listener='(ADDRESS =(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)(SID=orcl))’; SQL> alter system register; 7. An error occurs when the IMPDP is executed
Ora-39002: The operation is invalid
Ora-39070: unable to open log file.
Ora-29283: File operation invalid
Ora-06512: sys. UTL_FILE, line 488
Ora-29283: invalid file operation and similar error.
[oracle@localhost oracle]$ mkdir /u01/oracle/backup SQL> create or replace directory dir_dump as ‘/u01/oracle/backup’; SQL> grant read,write on directory dir_dump to system; SQL> select * from directories; SQL > select * from expdp where version =10.2.0.1.0; SQL > select * from expdp where version =10.2.0.1.0
ImpdpUSERID = ‘SYS/cuc2009 @ cucfassysdba’ schemas. = sybjdirectory = DATAPUMPDIRdumpfile = aa dmplogfile = aa. Logversion = 10.2.0.1.0 im pdp USERID=’SYS/cuc2009@cucf as sysdba’ schemas=sybj directory=DATA_PUMP_DIR dumpfile=aa.dmp logfile=aa.log Version = 10.2.0.1.0 impdpUSERID = ‘SYS/cuc2009 @ cucfassysdba’ schemas = sybjdirectory = DATAPUMPDIRdumpfile = aa. Dmplogfile = aa. Logve Rsion =10.2.0.1.0 impdp eii/eii123@eii DUMPFILE=orcl. DMP DIRECTORY=dir_dump REMap_schema = ORCL: EII Remap_tablespace = ORCL_DATA :eii_data 8
UnsatisfiedLinkError exception loading native library: njni12
java.lang.UnsatisfiedLinkError: /u01/oracle/product/12c/dbhome_1/lib/libnjni12.so: / u01 / oracle/product / 12 c/dbhome_1 / lib/libclntsh. So. 12.1: the file too short
LDD ‘whichsysresv’ check whichsysresv dependency Linux − Gate.so. 1=>(0x00ecF000) libClntsh.so.12.1 => NotFoundliBNnz10.so => NotFoundlibdel.so .2=>/lib/ libdl-so.2(0x0037c000) copy the prompt notFound to LDD ‘which sysresv’ check which sysresv dependencies linux-gate-so.1 => (0x00ecF000) Libclntsh.so.12.1 => not found libnnz10.so => not found libdl-so.2 => /lib/ libdl-so.2 (0x0037c000 Found copy to LDD ‘whichsysresv’ check whichsysresv dependency Linux − Gate.so. 1=>(0x00ecF000)libclntsh.so.12.1=> NotFoundliBNnz10.so => NotFoun Dlibdl. So. 2 = > / lib/libdl. So. 2 (0 x0037c000) copy of tip notfound to ORACLE_HOME/lig directory cdcdcdORACLE_HOME/inventory/Scripts/ext/lib / 12.1 cp libclntsh cplibclntsh. So.. So. 12.1 cplibclntsh. So. 12.1 ORACLE_HOME/lib/ldconfig again
ldconfig
9. Glicb is absent
cd /media/cdrom/RedHat/RPMS
rpm -Uvh glibc-.i686.rpm glibc-devel-.i386.rpm
Yum install -y glibc glibc-devel ORACLE_HOME/bin/relink all
Ora-06502 LPX-00225 orA-39126 LPX-00225
Add the parameter EXCLUDE=STATISTICS
$ impdp srmsfcs/srmsfcs2018@srmsfcs DUMPFILE=srmsf20181014.dmp DIRECTORY=dir_dump remap_schema=srmsf:srmsfcs remap_tablespace=srmsf_data:srmsfcs_data EXCLUDE=STATISTICS sqlplus / as sysdba SQL> exec Dbms_stats. Gather_schema_stats (ownname = > ‘users’ estimate_percent = > 10, degree = > 8, cascade = > true, granularity = >’ ALL ‘); Ora-01102 orA-01102
An orA-01102 error occurs when the Oracle database is installed.
$sqlplus “/as sysdba” SQL> startup ORACLE instance started. Total System Global Area 1.7103E+10 bytes Fixed Size 2243608 bytes Variable Size 8455717864 bytes Database Buffers 8623489024 bytes Redo Buffers 21712896 bytes ORA-01102: cannot mount database in EXCLUSIVE mode
Ora-1102 error:
(1) Sgadef. DBF files or LK files exist under ORACLE_HOME/ DBS /. These two files are used to lock memory.
(2) Oracle pmon, SMon, LGWR and DBWR processes are not shut down.
(3) After the database is closed, the shared memory or semaphore is still occupied.
Note The DATABASE is already in the MOUNT state and need not be mounted again. When a DATABASE is unmounted, it is automatically deleted. If the DATABASE is not mounted, the problem still exists and you have to manually delete it.
Ora-01102 OrA-01102
The PWD/apsarapangu/disk1 / opt/oracle/products / 11.2.0 PWD / apsarapangu/disk1 / opt/oracle/products / 11.2.0 PWD/apsarapangu/disk1 / opt/oracle/products/DBS ll lk * 11.2.0 CD – rw – r — — — — – 1 Oracle oinstall 24 Apr 15 15:43 lkORCL # fuser -u lkORCL -u specifies the login name of the local process in the parentheses following the process number. /sbin/fuser -u lkORCL lkORCL: 21007(oracle) 21009(oracle) 21015(oracle) 21019(oracle) 21023(oracle) 21025(oracle) 21027(oracle) 21029(oracle) 21031(Oracle) 21033(Oracle) 21035(Oracle) 21037(Oracle) 21039(Oracle) 21041(Oracle) # Use fuser -k lkORCL Kill the processes that are accessing lkORCL -k kills the processes that are accessing these files. / sbin/the fuser – klkORCLlkORCL: 2100721009210152101921023210252102721029210312103321035210372103921041 confirmed: all related processes are terminated. /sbin/fuser -k lkORCL lkORCL: 21007 21009 21015 21019 21023 21025 21027 21029 21031 21033 21035 21037 21039 21041 Confirm: All related processes are terminated. / sbin/the fuser – klkORCLlkORCL: 2100721009210152101921023210252102721029210312103321035210372103921041 confirmed: all related processes are terminated. /sbin/fuser -u lkORCL restart: $sqlplus “/as sysdba” SQL> startup ORACLE instance started. Total System Global Area 1.7103E+10 bytes Fixed Size 2243608 bytes Variable Size 8455717864 bytes Database Buffers 8623489024 bytes Redo Buffers 21712896 bytes Database 12, ORA-39346: Data loss in character set conversion for object PACKAGE_BODY
Set global character set variables for both export and import:
Export NLS_LANG= american_america. AL32UTF8 13, ORA-31631, ORA-39122 error message is displayed when impDP is importing data.
SQL> grant imp_full_database to username; Ora-28547: failed to connect to server
# extProc is an extended PROGRAM invocation interface protocol used to connect to and call external operating system programs or processes.
Ora-12514 TNS listener cannot be identified
Change the SID, host, and key values in the listener.ora file
When updating to the same library, use the table_exists_action=replace parameter
impdp orcl/orclpwd@sid DUMPFILE=orcl158demo_201811051847.dmp schemas=orcl DIRECTORY=dir_dump EXCLUDE=STATISTICS Table_exists_action =replace 17, ORA-01078: Failure in processing system parameters
LRM-00109: could not open parameter file
‘/ u01 / oracle/product / 11.2.0 dbhome_1 / DBS/initsorcl ora’
Solutions:
cd/u01/oracle/admin/srmhdl/pfile/cd /u01/oracle/admin/srmhdl/pfile/cd/u01/oracle/admin/srmhdl/pfile/ cp Init. Ora. 1092018133743 / u01 / oracle/product / 11.2.0 / dbhome_1 / DBS/initsorcl ora $up sqlplus/as sysdba SQL > create spfile from pfile; SQL> exit sqlplus / as sysdba SQL> startup
ORACLE instance started. Total System Global Area 1.7103E+10 bytes Fixed Size 2243608 bytes Variable Size 8455717864 bytes Database Buffers 8623489024 bytes Redo Buffers 21712896 bytes Database mounted. Database opened.
Ora-00821: Specified value of SGA_target 512M is too small, needs to be at least 700M
SQL> create spfile from pfile; SQL> exit $ sqlplus / as sysdba SQL> startup
ORACLE instance started. Total System Global Area 1.7103E+10 bytes Fixed Size 2243608 bytes Variable Size 8455717864 bytes Database Buffers 8623489024 bytes Redo Buffers 21712896 bytes Database mounted. Database opened.
19, Fatal NI connect error 12170
Solution:
(1) View oracle alarm logs
Database alert log path:
CD/u01 / oracle/diag/RDBMS/gongniu/gongniu/trace (2) to examine the listener log path
Cat/u01 / oracle/diag/TNSLSNR/localhost/listener/trace/listener log | grep 192.168.154.11 > / TMP/error log record a processing method of this problem:
(1) add the following parameters to sqlnet.ora: (It is recommended to back up the original file before operation)
Sqlnet. INBOUND_CONNECT_TIMEOUT = 30 SQLnet. RECV_TIMEOUT = 30 SQLnet. SEND_TIMEOUT = 30 (2), add the following parameters at the end of listener.ora:
INBOUND_CONNECT_TIMEOUT_LISTENER = 30 (3), reread the listener configuration file:
LSNRCTL reload Then check the ALTER log alert log
The alarm log file size is too large. Troubleshooting: The diagnosis tracing information is not written into the alarm log file
(Path CD $ORACLE_HOME/network/admin)
(1). Add a line to the sqlnet.ora file on the server
DIAG_ADR_ENABLED=OFF (2). Add a line to listener.ora on the server (replace listenerName with your own listenername)
DIAG_ADR_ENABLED_=OFF (3). Run the LSNRCTL command to make the configuration take effect. (Services are not interrupted.
lsnrctl reload; Pl/SQL Developer debug stored procedure error
Error message: Debugging requires the debug connect session system privilege.
Cause: The user does not have enough permissions. Run the following command to grant permissions:
GRANT debug any procedure, debug connect session TO scott