#####################################
ERRORS IN MY ALERT-LOG FILE
#####################################
Errors in file C:\APP\MOIN\diag\rdbms\kkprod\kkprod\trace\kkprod_ora_5720.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 2147483648 bytes is 99.02% used, and has 20971520 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
ARCH: Error 19809 Creating archive log file to '+DATA1'
ARC0: STARTING ARCH PROCESSES
Mon Jan 01 15:48:40 2018
ARC1 started with pid=26, OS id=2984
Mon Jan 01 15:48:40 2018
ARC2 started with pid=27, OS id=5724
Mon Jan 01 15:48:40 2018
ARC3 started with pid=28, OS id=1764
Errors in file C:\APP\MOIN\diag\rdbms\kkprod\kkprod\trace\kkprod_ora_5720.trc:
ORA-16038: log 3 sequence# 33 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '+DATA/kkprod/onlinelog/group_3.259.963052735'
ORA-00312: online log 3 thread 1: 'E:\FRA\KKPROD\ONLINELOG\O1_MF_3_F3GW4JG3_.LOG'
USER (ospid: 5720): terminating the instance due to error 16038
Mon Jan 01 15:48:41 2018
System state dump requested by (instance=1, osid=5720), summary=[abnormal instance termination].
System State dumped to trace file C:\APP\MOIN\diag\rdbms\kkprod\kkprod\trace\kkprod_diag_3272.trc
Dumping diagnostic data in directory=[cdmp_20180101154841], requested by (instance=1, osid=5720), summary=[abnormal instance termination].
ARC1: Archival started
ARC0: Detected ARCH process failure
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Archival disabled due to shutdown: 1092
Shutting down archive processes
Archiving is disabled
ARC0: Detected ARCH process failure
Mon Jan 01 15:48:49 2018
Instance terminated by USER, pid = 5720
#############################################################################
Below is the output generated when i exit Datapump job full=y by pressing CTRL+C job was hanging since 2 hrs.....
UDE-31626: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-39086: cannot retrieve job information
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551
ORA-06512: at line 1
ASM started sucessfully ....
When i started Database it shows ORA-03113
SQL> startup
ORACLE instance started.
Total System Global Area 1.0689E+10 bytes
Fixed Size 2264616 bytes
Variable Size 9160360408 bytes
Database Buffers 1509949440 bytes
Redo Buffers 16900096 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5132
Session ID: 193 Serial number: 3
C:\Windows\system32>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 1 15:53:09 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.0689E+10 bytes
Fixed Size 2264616 bytes
Variable Size 9160360408 bytes
Database Buffers 1509949440 bytes
Redo Buffers 16900096 bytes
Database mounted.
SQL> show parameter db_reco
NAME TYPE VALUE
--------------------------------------------------------------------------------
db_recovery_file_dest string +DATA1
db_recovery_file_dest_size big integer 2G
SQL> alter system set db_recovery_file_dest_size = 20G scope=both;
System altered.
SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
C:\Windows\system32> rman
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 1 16:12:21 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target /
connected to target database: KKPROD (DBID=1757363720, not open)
RMAN> delete backup of archivelog all;
RMAN> crosscheck backup;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963661884.274.963661885 RECID=12 STAMP=963661885
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963663274.268.963663275 RECID=13 STAMP=963663275
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963664178.275.963664179 RECID=14 STAMP=963664178
Crosschecked 3 objects
RMAN> delete expired backup;
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------------------------------------------------------------------------------------
12 12 1 1 EXPIRED DISK +DATA1/kkprod/autobackup/2017_12_25/s_963661884.274.963661885
13 13 1 1 EXPIRED DISK +DATA1/kkprod/autobackup/2017_12_25/s_963663274.268.963663275
14 14 1 1 EXPIRED DISK +DATA1/kkprod/autobackup/2017_12_25/s_963664178.275.963664179
Do you really want to delete the above objects (enter YES or NO)? y
deleted backup piece
backup piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963661884.274.963661885 RECID=12 STAMP=963661885
deleted backup piece
backup piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963663274.268.963663275 RECID=13 STAMP=963663275
deleted backup piece
backup piece handle=+DATA1/kkprod/autobackup/2017_12_25/s_963664178.275.963664179 RECID=14 STAMP=963664178
Deleted 3 EXPIRED objects
C:\Windows\system32> sqlplus / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> show parameter db_reco
NAME TYPE VALUE
-------------------------------------------------------------------
db_recovery_file_dest string +DATA1
db_recovery_file_dest_size big integer 20G
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
-------------------------------------------------------
kkprod MOUNTED
SQL> alter database open;
Database altered.
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
-------------------------------------------------
kkprod OPEN
Now check any previous expdp jobs are running.
SQL> set lines 200
SQL> SELECT owner_name, job_name, operation, job_mode,state, attached_sessions FROM dba_datapump_jobs ORDER BY 1,2;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
------------------------------ ------------------------------ ----------------------------------------------------------------------------
SYSTEM SYS_EXPORT_FULL_01 EXPORT FULL NOT RUNNING 0
SYSTEM SYS_IMPORT_FULL_01 IMPORT FULL NOT RUNNING 0
SQL> drop table system.SYS_EXPORT_FULL_01;
Table dropped.
SQL> drop table SYSTEM.SYS_IMPORT_FULL_01;
Table dropped.
>>> INCREASE SGA SIZE <<<<
SQL> select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;
TOTAL SGA (GB)
--------------
9.95534897
SQL> select sum(bytes)/1024/1024/1024 " GB" from v$sgastat where name!='free memory';
GB
----------
1.55478944
SQL> alter system set sga_max_size=20g scope=spfile;
SQL> alter system set memory_max_target=25g scope=spfile;
SQL> alter system set memory_target=25g scope=spfile;
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2.1379E+10 bytes
Fixed Size 2264616 bytes
Variable Size 1.9931E+10 bytes
Database Buffers 1409286144 bytes
Redo Buffers 36073472 bytes
Database mounted.
Database opened.
SQL> select sum(value)/1024/1024/1024 "TOTAL SGA (GB)" from v$sga;
TOTAL SGA (GB)
--------------
19.9107056
SQL> select sum(bytes)/1024/1024/1024 " GB" from v$sgastat where name!='free memory';
GB
----------
1.36548476
SQL> select owner, count(1), object_type from dba_objects group by owner, object_type having owner in
(select username from dba_users where default_tablespace = 'USERS') and object_type = 'TABLE';
OWNER COUNT(1) OBJECT_TYPE
----------------------------------------------------------------------
HMIS 1998 TABLE
SCOTT 4 TABLE
ARCHIVEDB 1 TABLE
CMIS 410 TABLE
IDMS7 1978 TABLE
All the tables restored as soon i increase the size db_recovery_file_dest and SGA size.
And Import Run Successfully.
E:\imp_kkprod>impdp directory=imp_kkprod log=kkprod_impfull full=y dumpfile=IECLIVE_01JAN2018FULL.DMP
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/PRE_TABLE_ACTION
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "HMIS"."SHSHTDTA" 3.524 GB 41666374 rows
. . imported "IDMS7"."TBL_HIJRI_CONTROLS" 2.403 GB 4982267 rows
. . imported "HMIS"."OTOCURNC" 1.446 GB 10604454 rows
100 % Executed Successfully.
Happy Learning....
