CompuError: Oracle

Showing posts with label Oracle. Show all posts

Startup of HTTP LISTENER failure (00716)

ORACLE RAC 11g R2, OS: AIX 7.1

$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
https://dba03.flora.in:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ...................................................................................... failed.
------------------------------------------------------------------
Logs are generated in directory /d01/app/oracle/product/11.2.0/dbhome/dba03_ORCL/sysman/log

check em agent log
--------------------------
$ tail emagent.log
2019-05-27 12:00:06,896 Thread-1 Starting Agent 10.2.0.4.5 from /d01/app/oracle/product/11.2.0/dbhome (00701)
2019-05-27 12:00:06,900 Thread-1 : Startup of HTTP LISTENER failure (00716)

Modify the property of EMD
------------------------------------
$ vi emd.properties
#EMD_URL=https://dba03.flora.in:3938/emd/main
EMD_URL=https://dba03.flora.in:3939/emd/main << change

Now start the dbconsole
---------------------------------
$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
https://dba03.flora.in:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ...... started.
------------------------------------------------------------------
Logs are generated in directory /d01/app/oracle/product/11.2.0/dbhome/dba03_ORCL/sysman/log

$ emctl upload agent
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully

$ emctl status agent
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version     : 10.2.0.4.5
OMS Version       : 10.2.0.4.5
Protocol Version : 10.2.0.4.5
Agent Home        : /d01/app/oracle/product/11.2.0/dbhome/dba03_ORCL
Agent binaries    : /d01/app/oracle/product/11.2.0/dbhome
Agent Process ID : 17564134
Parent Process ID : 9961922
Agent URL         : https://dba03.flora.in:3939/emd/main
Repository URL    : https://dba03.flora.in:1158/em/upload/
Started at        : 2019-05-27 12:49:19
Started by user   : oracle
Last Reload       : 2019-05-27 13:09:57
Last successful upload                       : 2019-05-27 13:10:55
Total Megabytes of XML files uploaded so far :     2.74
Number of XML files pending upload           :        2
Size of XML files pending upload(MB)         :     0.00
Available disk space on upload filesystem    :    48.36%
Data channel upload directory                : /d01/app/oracle/product/11.2.0/dbhome/dba03_ORCL/sysman/recv
Last successful heartbeat to OMS             : 2019-05-27 13:10:45
---------------------------------------------------------------
Agent is Running and Ready

file 4 cannot be read at this time

ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 4 cannot be read at this time
ORA-01110: data file 4: '+DATA/orcldb/datafile/undotbs2.343.817729081'

Startup nomount

select status, file# from v$datafile where status='recover';

status file#
------- ------
recover 4

set autorecovery on
recover datafile 4
alter database datafile 4 online;

shutdown immediate

lsnrctl stop

startup

lsnrctl start

ORA-03113: end-of-file on communication channel

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 6 12:27:17 2017

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

Connected to an idle instance.

SQL> startup pfile='/home/oracle/pfile/initORADB.ora';
ORACLE instance started.

Total System Global Area 1.6034E+10 bytes
Fixed Size 2262200 bytes
Variable Size 1.1241E+10 bytes
Database Buffers 4764729344 bytes
Redo Buffers 26484736 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 16974138
Session ID: 195 Serial number: 3

SQL> shutdown immediate

ORA-24324: service handle not initialized

ORA-01041: internal error. hostdef extension doesn't exist

SQL>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 6 12:32:04 2017

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1.6034E+10 bytes

Fixed Size 2262200 bytes

Variable Size 1.1241E+10 bytes

Database Buffers 4764729344 bytes

Redo Buffers 26484736 bytes

SQL> alter database mount;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1.6034E+10 bytes

Fixed Size 2262200 bytes

Variable Size 1.1241E+10 bytes

Database Buffers 4764729344 bytes

Redo Buffers 26484736 bytes

Database mounted.

Database opened.

Note: To set the log archive destination :

SQL> startup nomount;

SQL> alter system set log_archive_start=TRUE SCOPE=SPFILE;

System altered.

SQL> alter system set log_archive_dest_1=’LOCATION=+FRA/oradb/archivelog’ SCOPE=SPFILE;

System altered.

emctl upload agent failed

$ export ORACLE_SID=orcl2
$ export ORACLE_UNQNAME=orcl
$ emctl upload agent
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error: Failed to upload file A0000243.xml: HTTP error.
Response received: ERROR-400|ORA-28239: no key provided
ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 3
ORA-06512: at "SYS.DBMS_CRYPTO", line 13
ORA-06512: at "SYSMAN.ENCRYPT", line 9
ORA-06512: at "SYSMAN.MGMT_CREDS_INS_UPD", line 7
ORA-04088: error during execution of trigger 'SYSMAN.MGMT_CREDS_INS_UPD'

$ emctl status agent
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.4.5
OMS Version : 10.2.0.4.5
Protocol Version : 10.2.0.4.5
Agent Home : /d01/app/oracle/product/11.2.0/dbhome/rac02_orcl
Agent binaries : /d01/app/oracle/product/11.2.0/dbhome
Agent Process ID : 11993508
Parent Process ID : 14745618
Agent URL : https://rac02.prod.com:1830/emd/main
Repository URL : https://rac02.prod.com:5500/em/upload/
Started at : 2017-06-20 13:01:39
Started by user : oracle
Last Reload : 2017-07-08 00:57:12
Last successful upload : 2017-07-07 09:31:27
Total Megabytes of XML files uploaded so far : 513.19
Number of XML files pending upload : 924
Size of XML files pending upload(MB) : 20.07
Available disk space on upload filesystem : 52.68%
Data channel upload directory : /d01/app/oracle/product/11.2.0/dbhome/rac02_orcl/sysman/recv
Collection Status : Disabled by Upload Manager <<<<===Upload Failed=====
Last successful heartbeat to OMS : 2017-07-14 10:21:31
---------------------------------------------------------------
Agent is Running and Ready

Solution:
========
$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
https://rac02.prod.com:5500/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
... Stopped.

Then go to the sysman/emd folder
> Delete or compress or move lastupld.xml file
Then go to the sysman/emd/upload folder
> Delete or compress or move all the file inside sysman/emd/upload folder

$ cd /d01/app/oracle/product/11.2.0/dbhome/rac02_orcl/sysman/emd
$ compress lastupld.xml
$ cd /d01/app/oracle/product/11.2.0/dbhome/rac02_orcl/sysman/emd/upload
$ rm *

$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
https://rac02.prod.com:5500/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ...... started.
------------------------------------------------------------------
Logs are generated in directory /d01/app/oracle/product/11.2.0/dbhome/rac02_orcl/sysman/log
$ emctl upload agent
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload completed successfully
$ emctl status agent
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.4.5
OMS Version : 10.2.0.4.5
Protocol Version : 10.2.0.4.5
Agent Home : /d01/app/oracle/product/11.2.0/dbhome/rac02_orcl
Agent binaries : /d01/app/oracle/product/11.2.0/dbhome
Agent Process ID : 9961834
Parent Process ID : 11402312
Agent URL : https://rac02.prod.com:1830/emd/main
Repository URL : https://rac02.prod.com:5500/em/upload/
Started at : 2017-07-14 10:33:29
Started by user : oracle
Last Reload : 2017-07-14 10:33:29
Last successful upload : 2017-07-14 10:34:28
Total Megabytes of XML files uploaded so far : 15.29
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 81.33%
Data channel upload directory : /d01/app/oracle/product/11.2.0/dbhome/rac02_orcl/sysman/recv <<<<<<< It's Working Now
Last successful heartbeat to OMS : 2017-07-14 10:34:45
---------------------------------------------------------------
Agent is Running and Ready

Check Datapump Dump Version without log file

Check ORACLE DataPump Dump Version without log file

LINUX or UNIX
strings |head -n 10

$ strings Test.dmp|head -n 10

......
"BACKUP"."SYS_EXPORT_SCHEMA_15"
IBM AIX64/RS6000 V4 - 8.1.0 <-----------------os bit="" p="">ORACLECAIX02:orcl1<-----------------hostname :="" instance_name="" p="">WE8MSWIN1252 <---------------------- p="">11.02.00.04.00 <===== dump file version
001:001:000001:000001

ASM Disk Status

SQL> select name, group_number, state from v$asm_diskgroup;

NAME GROUP_NUMBER STATE
------------------------------ ------------ -----------
FRA 1 MOUNTED
OCR 2 MOUNTED
DATA 3 MOUNTED
REDO 4 MOUNTED
DATA2 5 MOUNTED

SQL> select name, state, type, total_mb, free_mb from v$asm_diskgroup;

NAME STATE TYPE TOTAL_MB FREE_MB
------------------------------ ----------- ------ ---------- ----------
FRA MOUNTED EXTERN 1024000 294361
OCR MOUNTED NORMAL 6144 5218
DATA MOUNTED NORMAL 1228800 1060302
REDO MOUNTED NORMAL 46080 45795
DATA2 MOUNTED NORMAL 2457600 1634903

SQL> select name, path, mode_status, state, disk_number from v$asm_disk;

NAME PATH MODE_ST STATE DISK_NUMBER
------------------ ------------------- -------------- ------------------
DATA_0000 /dev/ASM_DATA_Disk1 ONLINE NORMAL 0
DATA_0001 /dev/ASM_DATA_Disk2 ONLINE NORMAL 1
DATA_0002 /dev/ASM_DATA_Disk3 ONLINE NORMAL 2
DATA2_0000 /dev/ASM_DATA_Disk4 ONLINE NORMAL 0
DATA2_0001 /dev/ASM_DATA_Disk5 ONLINE NORMAL 1
DATA2_0002 /dev/ASM_DATA_Disk6 ONLINE NORMAL 2
DATA2_0003 /dev/ASM_DATA_Disk7 ONLINE NORMAL 3
DATA2_0004 /dev/ASM_DATA_Disk8 ONLINE NORMAL 4
DATA2_0005 /dev/ASM_DATA_Disk9 ONLINE NORMAL 5
FRA_0000 /dev/ASM_FRA_Disk1 ONLINE NORMAL 0
FRA_0001 /dev/ASM_FRA_Disk2 ONLINE NORMAL 1

NAME PATH MODE_ST STATE DISK_NUMBER
------------------ ------------------- -------------- ------------------
OCR_0000 /dev/ASM_OCRVOTA_Disk1 ONLINE NORMAL 0
OCR_0001 /dev/ASM_OCRVOTA_Disk2 ONLINE NORMAL 1
OCR_0002 /dev/ASM_OCRVOTA_Disk3 ONLINE NORMAL 2
REDO_0000 /dev/ASM_REDO_Disk1 ONLINE NORMAL 0
REDO_0001 /dev/ASM_REDO_Disk2 ONLINE NORMAL 1
REDO_0002 /dev/ASM_REDO_Disk3 ONLINE NORMAL 2

SQL> select INSTANCE_NAME,DB_NAME,STATUS,SOFTWARE_VERSION,COMPATIBLE_VERSION from v$asm_client;

INSTANCE_NAME DB_NAME STATUS SOFTWARE_VERSION COMPATIBLE_VERSION
----------------------------------------------------------------------------------------------------------------------------------
+ASM1 +ASM CONNECTED 11.2.0.4.0 11.2.0.4.0
+ASM1 +ASM CONNECTED 11.2.0.4.0 11.2.0.4.0
DB1 DB CONNECTED 11.2.0.4.0 11.2.0.4.0

INSTANCE_NAME DB_NAME STATUS SOFTWARE_VERSION COMPATIBLE_VERSION
----------------------------------------------------------------------------------------------------------------------------------
DB1 DB CONNECTED 11.2.0.4.0 11.2.0.4.0
DBG1 DBG CONNECTED 11.2.0.4.0 11.2.0.4.0
DBG1 DBG CONNECTED 11.2.0.4.0 11.2.0.4.0

SQL> select file_number , sum(bytes)/(1024*1024) from v$asm_file group by file_number;

FILE_NUMBER SUM(BYTES)/(1024*1024)
----------- ----------------------
253 .001464844
255 260.121094
256 5550.01563
257 3787.26563
258 4775.97949
259 1721.26563
260 403.257813
261 100.000977
262 1634.32715
263 34745.0156
264 1321.26563

FILE_NUMBER SUM(BYTES)/(1024*1024)
----------- ----------------------
265 200.008301
266 113.782227
267 63.7861328
268 337920.012
269 24132.6875
270 12288.0156
271 67611.0791
272 3303.13281
274 13.65625
275 92.9921875
276 13.78125

SQL>

set linesize 200
col g_name format a10
col g_n format 99
col d_n format 999
col m_status format a7
col mo_status format a7
col h_status format a11
col name format a30
col path format a45
col failgroup format a15

select g.group_number g_n,
g.disk_number d_n,
g.name name,
g.failgroup,
g.mount_status m_status,
g.header_status h_status,
g.mode_status mo_status,
g.path ,
to_char(g.mount_date, 'YYYY/MM/DD HH24:MI:SS') m_date
from v$asm_disk g
order by g_n, d_n;

G_N D_N NAME FAILGROUP M_STATU H_STATUS MO_STAT PATH M_DATE
--- ---- ------------------------------ --------------- ------- ----------- ------- --------------------------------------------- -------------------
1 0 FRA_0000 FRA_0000 CACHED MEMBER ONLINE /dev/ASM_FRA_Disk1 2016/01/27 16:48:27
1 1 FRA_0001 FRA_0001 CACHED MEMBER ONLINE /dev/ASM_FRA_Disk2 2016/01/27 16:48:27
2 0 OCR_0000 OCR_0000 CACHED MEMBER ONLINE /dev/ASM_OCRVOTA_Disk1 2015/12/23 16:28:21
2 1 OCR_0001 OCR_0001 CACHED MEMBER ONLINE /dev/ASM_OCRVOTA_Disk2 2015/12/23 16:28:21
2 2 OCR_0002 OCR_0002 CACHED MEMBER ONLINE /dev/ASM_OCRVOTA_Disk3 2015/12/23 16:28:21
3 0 DATA_0000 DATA_0000 CACHED MEMBER ONLINE /dev/ASM_DATA_Disk1 2016/01/27 16:48:28
3 1 DATA_0001 DATA_0001 CACHED MEMBER ONLINE /dev/ASM_DATA_Disk2 2016/01/27 16:48:28
3 2 DATA_0002 DATA_0002 CACHED MEMBER ONLINE /dev/ASM_DATA_Disk3 2016/01/27 16:48:28
4 0 REDO_0000 REDO_0000 CACHED MEMBER ONLINE /dev/ASM_REDO_Disk1 2016/01/27 16:48:28
4 1 REDO_0001 REDO_0001 CACHED MEMBER ONLINE /dev/ASM_REDO_Disk2 2016/01/27 16:48:28
4 2 REDO_0002 REDO_0002 CACHED MEMBER ONLINE /dev/ASM_REDO_Disk3 2016/01/27 16:48:28

G_N D_N NAME FAILGROUP M_STATU H_STATUS MO_STAT PATH M_DATE
--- ---- ------------------------------ --------------- ------- ----------- ------- --------------------------------------------- -------------------
5 0 DATA2_0000 DATA2_0000 CACHED MEMBER ONLINE /dev/ASM_DATA_Disk4 2016/03/31 16:45:02
5 1 DATA2_0001 DATA2_0001 CACHED MEMBER ONLINE /dev/ASM_DATA_Disk5 2016/03/31 16:45:02
5 2 DATA2_0002 DATA2_0002 CACHED MEMBER ONLINE /dev/ASM_DATA_Disk6 2016/03/31 16:45:02
5 3 DATA2_0003 DATA2_0003 CACHED MEMBER ONLINE /dev/ASM_DATA_Disk7 2016/06/10 14:20:22

G_N D_N NAME FAILGROUP M_STATU H_STATUS MO_STAT PATH M_DATE
--- ---- ------------------------------ --------------- ------- ----------- ------- --------------------------------------------- -------------------
5 4 DATA2_0004 DATA2_0004 CACHED MEMBER ONLINE /dev/ASM_DATA_Disk8 2016/06/10 14:20:22
5 5 DATA2_0005 DATA2_0005 CACHED MEMBER ONLINE /dev/ASM_DATA_Disk9 2016/06/10 14:20:22

SQL> select name,state from v$asm_diskgroup;

NAME STATE
------------------------------ -----------
FRA MOUNTED
OCR MOUNTED
DATA MOUNTED
REDO MOUNTED
DATA2 MOUNTED

Header Status of ASM Disks

SQL> select HEADER_STATUs, path from v$asm_disk where GROUP_NUMBER = 0;

SQL> show parameter asm

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
asm_diskgroups string REDO, FRA, DATA, DATA2
asm_diskstring string /dev/ASM*
asm_power_limit integer 1
asm_preferred_read_failure_groups string

SQL> select NAME, STATE, TOTAL_MB, FREE_MB, HOT_USED_MB, COLD_USED_MB, DATABASE_COMPATIBILITY, VOTING_FILES from v$asm_diskgroup;

NAME STATE TOTAL_MB FREE_MB HOT_USED_MB COLD_USED_MB DATABASE_COMPATIBILITY V
------------------------------ ----------- ---------- ---------- ----------- ------------ ------------------------------------------------------------ -
FRA MOUNTED 1024000 294103 0 729897 10.1.0.0.0 N
OCR MOUNTED 6144 5218 0 926 10.1.0.0.0 Y
DATA MOUNTED 1228800 1060302 0 168498 10.1.0.0.0 N
REDO MOUNTED 46080 45795 0 285 10.1.0.0.0 N
DATA2 MOUNTED 2457600 1634903 0 822697 10.1.0.0.0 N

ASMCMD commands:
--------

md_backup, md_restore

lsattr, setattr

cd, cp, du, find, help, ls, lsct, lsdg, lsof, mkalias
mkdir, pwd, rm, rmalias

chdg, chkdg, dropdg, iostat, lsdsk, lsod, mkdg, mount
offline, online, rebal, remap, umount

dsget, dsset, lsop, shutdown, spbackup, spcopy, spget
spmove, spset, startup

chtmpl, lstmpl, mktmpl, rmtmpl

chgrp, chmod, chown, groups, grpmod, lsgrp, lspwusr, lsusr
mkgrp, mkusr, orapwusr, passwd, rmgrp, rmusr

volcreate, voldelete, voldisable, volenable, volinfo
volresize, volset, volstat

ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED NORMAL N 512 4096 1048576 2457600 1634903 409600 612651 0 N DATA2/
MOUNTED EXTERN N 512 4096 1048576 1024000 294361 0 294361 0 N FRA/
MOUNTED NORMAL N 512 4096 1048576 6144 5218 2048 1585 0 Y OCR/
MOUNTED NORMAL N 512 4096 1048576 1228800 1060302 409600 325351 0 N DATA/
MOUNTED NORMAL N 512 4096 1048576 46080 45795 15360 15217 0 N REDO/

Drop Database without Database Configuration Assistance (DBCA)

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 3 15:47:23 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount exclusive restrict;
ORACLE instance started.

Total System Global Area 1.2827E+10 bytes
Fixed Size 2258352 bytes
Variable Size 7046433360 bytes
Database Buffers 5771362304 bytes
Redo Buffers 7315456 bytes
Database mounted.

SQL> drop database;

Database dropped.

SQL> exit

Command to check Kernel Parameter

AIX : env
HP : /etc/sysdef
Solaris: /etc/sysdef | grep SHM
Linux: ipcs -lms
Mac OS: # /usr/sbin/sysctl -a | grep

Check version of Compiler

C/C++: $ which cc
$ cd
$ what cc

For Linux
$ rpm -qa | grep egcs

COBOL: $ cd $COBDIR
$ more cobver

Fortran: $ which f77
$ cd
$ what f77

OS Commands to find Swap, RAM, and OS Version

AIX

Swap: /usr/sbin/lsps -a
RAM: /usr/sbin/lsattr -HE -l sys0 -a realmem
OS version: oslevel

HP PA-RISC

Swap: swapinfo -a
RAM: grep "Physical:" /var/adm/syslog/syslog.log
OS version: uname -a

Solaris

Swap: swap -l
RAM:   /usr/sbin/prtconf | grep -i memory
OS version: uname -r

Linux

Swap: free
RAM:   free
OS version: uname -a

Mac OS X

Swap: # df -h /
RAM:   # /usr/sbin/system_profiler SPHardwareDataType | grep Memory
OS version: # sw_vers

Increase the size of the SGA from 4GB to 6GB by increasing memory_target

Solved : ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 6256M
Keep parameter file backup of database, which will be found in $ORACLE_HOME/dbs/init[SID].ora

These step will increase the automatically increase sga_max_size

SQL> select name from v$database;

NAME
---------
ORCL

SQL> create pfile from spfile;
File created.

SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 4688M
sga_target big integer 4688M

SQL> alter system set memory_target=6G scope=spfile sid='*';

System altered.

SQL> shutdow
ORA-01013: user requested cancel of current operation

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 6256M
SQL> alter system set memory_target=6256M scope=spfile sid='*';
alter system set memory_target=6256M scope=spfile sid='*'
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 223 Serial number: 9

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='/DATAB/home/oracle/app/oracle/product/11.2.0/dbhome/dbs/initorcl.ora'
ORACLE instance started.

Total System Global Area 4893769728 bytes
Fixed Size 2254696 bytes
Variable Size 989857944 bytes
Database Buffers 3892314112 bytes
Redo Buffers 9342976 bytes
Database mounted.
Database opened.
SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 0
memory_target big integer 0
shared_memory_address integer 0
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 4688M
sga_target big integer 4688M
SQL> create spfile from pfile;

File created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4893769728 bytes
Fixed Size 2254696 bytes
Variable Size 989857944 bytes
Database Buffers 3892314112 bytes
Redo Buffers 9342976 bytes
Database mounted.
Database opened.
SQL> alter system set memory_target=6256M scope=spfile sid='*';

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 6530596864 bytes
Fixed Size 2258632 bytes
Variable Size 2634025272 bytes
Database Buffers 3875536896 bytes
Redo Buffers 18776064 bytes
Database mounted.
Database opened.
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 6256M
sga_target big integer 4688M
SQL> show parameter memory_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 6256M
SQL>

SQL> show parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 6256M
memory_target big integer 6256M
shared_memory_address integer 0
SQL>

ORA-01105:mount is incompatible with mounts by other instances

ORA-01105: mount is incompatible with mounts by other instances

ORA-19808: recovery destination parameter mismatch

node1

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Apr 10 01:36:11 2015

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1.3262E+10 bytes

Fixed Size 2232128 bytes

Variable Size 6207570112 bytes

Database Buffers 7046430720 bytes

Redo Buffers 5398528 bytes

ORA-01105: mount is incompatible with mounts by other instances

ORA-19808: recovery destination parameter mismatch

node1

SQL> show parameter recovery

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string +DATAFRA

db_recovery_file_dest_size big integer 1600G

recovery_parallelism integer 0

node2

SQL> show parameter recovery

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string +DATAFRA

db_recovery_file_dest_size big integer 1500G

recovery_parallelism integer 0

SQL> alter system set db_recovery_file_dest_size=1600G; <<====== node 2

node1

SQL> shutdown

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1.3262E+10 bytes

Fixed Size 2232128 bytes

Variable Size 6207570112 bytes

Database Buffers 7046430720 bytes

Redo Buffers 5398528 bytes

Database mounted.

Database opened.

ORA-13516: AWR Operation failed: CATPROC not valid

DBCA fails while in the middle of creating the database with the following errors

Solution:

Do not include any spaces or special characters in the password.

Parameters inside ORACLE Server Parameter File (SPFILE)

Copy all these parameters to create pfile (init.ora) , modify necessary path and size according to your requirement.

From pfile you can create spfile by using "create spfile from pfile='location\init.ora'; "

__db_cache_size=2816M

__java_pool_size=32M

__large_pool_size=16M

__oracle_base='/d01/app/oracle' # ORACLE_BASE set from environment

__pga_aggregate_target=2512M

__sga_target=3744M

__shared_io_pool_size=0

__shared_pool_size=800M

Server Parameter File of ORACLE 11g - I

1. Default location of PFILE and SPFILE in UNIX and WINDOWS are?

For UNIX : $ORACLE_HOME/dbs or /d02/app/oracle/product/11.2.0/dbhome/dbs

For WINDOWS : %ORACLE_HOME\database or c:\app\oracle\product\11.2.0\dbhome\database

SQL> show parameter spfile

NAME TYPE VALUE

--------- ----------- ------------------------------

spfile string /d01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora

ORA-03113: end-of-file on communication channel

Problem : -

Database SID=orcl

SQL> startup
ORACLE instance started.

Total System Global Area 6530596864 bytes
Fixed Size 2219048 bytes
Variable Size 3556770776 bytes
Database Buffers 2952790016 bytes
Redo Buffers 18817024 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 753668
Session ID: 96 Serial number: 3

Data Pump export utility

The Data Pump export utility provides a mechanism for transferring data objects between Oracle databases. The utility is invoked with the following command:

Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed by various parameters. To specify parameters, you use keywords:

Format: expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)

Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott

or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed within square brackets.

ASM exists to manage file storage for the RDBMS

ASM does NOT perform I/O on behalf of the RDBMS
I/O is performed by the RDBMS processes as it does with other storage types
Thus, ASM is not an intermediary for I/O (would be a bottleneck)
I/O can occur synchronously or asynchronously depending on the value of the DISK_ASYNCH_IO parameter
Disks are RAW devices to ASM
Files that can be stored in ASM: typical database data files, control files, redologs, archivelogs, flashback logs, spfiles,

RMAN backups and incremental tracking bitmaps, datapump dumpsets.
In 11gR2, ASM has been extended to allow storing any kind of file using Oracle ACFS capability (it appears as another filesystem to clients). Note that database files are not supported within ACFS

java.lang.Exception: No Such Target or java.lang.exception: no such metric or Metric Collection Error

Symptom :
1. java.lang.Exception: No Such Target or java.lang.exception: no such metric
2. Metric Collection Error

This happens because of DBSNMP passward is expired or locked.

1. Unlock and Change the password for DBSNMP.
2. Go to EM and click on Database tab
3. Search for Monitoring Configuration

4. Change the password for DBSNMP
5. Test the connection if it's successful .
6. Press Next and Submit

cannot mount database in EXCLUSIVE mode.

Error Code : ORA-01102

This could happen during the switchover if you did not set the DB_UNIQUE_NAME parameter in the initialization parameter file that is used by the standby database (that is, the original primary database). If the DB_UNIQUE_NAME parameter of the standby database is not set, the standby and the primary databases both use the same mount lock and cause the ORA-01102 error during the startup of the second database.

Action:

Add DB_UNIQUE_NAME=unique_database_name to the initialization parameter file used by the standby database, and shut down and restart the standby and primary databases.

Pages

Labels

Search This Blog

Startup of HTTP LISTENER failure (00716)

file 4 cannot be read at this time

ORA-03113: end-of-file on communication channel

emctl upload agent failed

Check Datapump Dump Version without log file

ASM Disk Status

Drop Database without Database Configuration Assistance (DBCA)

Command to check Kernel Parameter

Check version of Compiler

OS Commands to find Swap, RAM, and OS Version

Increase the size of the SGA from 4GB to 6GB by increasing memory_target

ORA-01105:mount is incompatible with mounts by other instances

ORA-13516: AWR Operation failed: CATPROC not valid

Parameters inside ORACLE Server Parameter File (SPFILE)

Server Parameter File of ORACLE 11g - I

ORA-03113: end-of-file on communication channel

Data Pump export utility

Key concepts of ASM

java.lang.Exception: No Such Target or java.lang.exception: no such metric or Metric Collection Error

cannot mount database in EXCLUSIVE mode.

Blog Archive

My Blog List

Pages

Labels

Search This Blog

Blog Archive

My Blog List

Subscribe To