Best Practice to enable Flash back logging.
Section 1
SQL> select instance_name, host_name from v$instance;
INSTANCE_NAME HOST_NAME
--------------- -------------------------
orcl01 rachost01
SQL> select name, db_unique_name, open_mode, DATABASE_ROLE, current_scn from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN
--------------- ------------------------------ -------------------- ------------------------- -----------
orcl ORCL_LIVE1 READ WRITE PRIMARY 1.0741E+13
SQL> col name form a60
SQL> select * from v$sgainfo;
NAME BYTES RESIZEABL
------------------------------------------------------------ ---------- ---------
Fixed SGA Size 2197352 No
Redo Buffers 536895488 No
Buffer Cache Size 1.9327E+11 Yes
Shared Pool Size 4.2413E+10 Yes
Large Pool Size 5368709120 Yes
Java Pool Size 5368709120 Yes
Streams Pool Size 1.0737E+10 Yes
Shared IO Pool Size 0 Yes
Granule Size 536870912 No
Maximum SGA Size 2.5770E+11 No
Startup overhead in Shared Pool 1.4683E+10 No
Free SGA Memory Available 0
12 rows selected.
SQL> l
1* select * from v$sgainfo
SQL> select name, BYTES/1024/1024/1024 from v$sgainfo;
NAME BYTES/1024/1024/1024
------------------------------------------------------------ --------------------
Fixed SGA Size .002046444
Redo Buffers .500022888
Buffer Cache Size 180
Shared Pool Size 39.5
Large Pool Size 5
Java Pool Size 5
Streams Pool Size 10
Shared IO Pool Size 0
Granule Size .5
Maximum SGA Size 240.002075
Startup overhead in Shared Pool 13.6742718
Free SGA Memory Available 0
12 rows selected.
SQL> show parameter _memory_imm_mode_without_autosga
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
_memory_imm_mode_without_autosga boolean FALSE
SQL> show parameter block_size
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_block_size integer 8192
SQL> show parameter &x
Enter value for x: sga_target
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
sga_target big integer 0
SQL> select name, db_unique_name, FLASHBACK_ON, DATABASE_ROLE from v$database;
NAME DB_UNIQUE_NAME FLASHBACK_ON DATABASE_ROLE
--------------- ------------------------------ ------------------------------------------------------ -------------------------
orcl ORCL_LIVE1 NO PRIMARY
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_flashback_retention_target integer 1440
SQL>
SQL> column name format a30
SQL> select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB, FREE_MB from v$asm_diskgroup;
NAME ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB
------------------------------ -------------------- --------------------------------- ------------------ ---------- ----------
orcl_LIVE1_ARC 4194304 CONNECTED EXTERN 3342336 3212292
orcl_LIVE1_DATA 4194304 CONNECTED EXTERN 28966912 7184328
orcl_LIVE1_FRA 4194304 CONNECTED EXTERN 557056 556244
orcl_LIVE1_REDO1A 67108864 CONNECTED EXTERN 69568 26752
orcl_LIVE1_REDO1B 67108864 CONNECTED EXTERN 69568 26752
orcl_LIVE1_REDO2A 67108864 CONNECTED EXTERN 69568 26752
orcl_LIVE1_REDO2B 67108864 CONNECTED EXTERN 69568 5760
orcl_LIVE1_REDO3A 67108864 CONNECTED EXTERN 69568 11008
orcl_LIVE1_REDO3B 67108864 CONNECTED EXTERN 69568 11008
orcl_LIVE1_REDO4A 67108864 CONNECTED EXTERN 69568 5760
orcl_LIVE1_REDO4B 67108864 CONNECTED EXTERN 69568 5760
OGG 4194304 MOUNTED EXTERN 2228224 66312
12 rows selected.
SQL> show parameter &x
Enter value for x: db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string +orcl_LIVE1_FRA
db_recovery_file_dest_size big integer 6144G
SQL>
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=360 scope=both sid='*';
System altered.
SQL> alter system set db_recovery_file_dest_size=510G scope=both sid='*';
System altered.
SQL> show parameter &x
Enter value for x: db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string +orcl_LIVE1_FRA
db_recovery_file_dest_size big integer 510G
SQL>
SQL> COL NAME FORMAT A32
SQL> COL VALUE FORMAT A40
SQL> SPOOL SGAPARAMS.TXT
SQL> /* Database Identification */
SQL> select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
NAME PLATFORM_ID DATABASE_ROLE
-------------------------------- ----------- -------------------------
orcl 2 PRIMARY
SQL> select * from V$version where banner like 'Oracle Database%';
BANNER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL> /* Shared Pool parameters */
SQL> select nam.ksppinm NAME, val.KSPPSTVL VALUE
2 from x$ksppi nam, x$ksppsv val
3 where nam.indx = val.indx and (nam.ksppinm like '%shared_pool%' or nam.ksppinm like '_4031%' or nam.ksppinm in ('_kghdsidx_count','_ksmg_granule_size','_memory_imm_mode_without_autosga'))
4 order by 1;
NAME VALUE
-------------------------------- ----------------------------------------
_4031_dump_bitvec 67194879
_4031_dump_interval 300
_4031_max_dumps 100
_4031_sga_dump_interval 3600
_4031_sga_max_dumps 10
__shared_pool_size 42412802048
_dm_max_shared_pool_pct 1
_enable_shared_pool_durations FALSE
_io_shared_pool_size 4194304
_kghdsidx_count 7
_ksmg_granule_size 536870912
_memory_imm_mode_without_autosga FALSE
_shared_pool_max_size 0
_shared_pool_minsize_on FALSE
_shared_pool_reserved_min_alloc 4400
_shared_pool_reserved_pct 5
shared_pool_reserved_size 4194304000
shared_pool_size 42412802048
18 rows selected.
SQL> SPOOL OFF
SQL>
SQL> SET PAGESIZE 900
SQL> SET LINESIZE 255
SQL> COL BYTES FORMAT 999999999999999
SQL> COL CURRENT_SIZE FORMAT 999999999999999
SQL> COL 'Total Shared Pool Usage' FORMAT 99999999999999999999999
SQL> set echo on
SQL>
SQL> SPOOL SPINFO.TXT
SQL>
SQL> /* Database Identification */
SQL> select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
NAME PLATFORM_ID DATABASE_ROLE
-------------------------------- ----------- -------------------------
orcl 2 PRIMARY
SQL> select * from V$version where banner like 'Oracle Database%';
BANNER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL>
SQL> /* Shared Pool 4031 information */
SQL> select REQUEST_FAILURES, LAST_FAILURE_SIZE from V$SHARED_POOL_RESERVED;
REQUEST_FAILURES LAST_FAILURE_SIZE
---------------- -----------------
0 0
SQL>
SQL> /* Shared Pool Reserved 4031 information */
SQL> select REQUESTS, REQUEST_MISSES, free_space, avg_free_size, free_count, max_free_size from V$SHARED_POOL_RESERVED;
REQUESTS REQUEST_MISSES FREE_SPACE AVG_FREE_SIZE FREE_COUNT MAX_FREE_SIZE
---------- -------------- ---------- ------------- ---------- -------------
0 0 2085171840 47390269.1 40 149991456
SQL>
SQL> /* Shared Pool Memory Allocations by Size */
SQL> select name, bytes from v$sgastat
2 where pool = 'shared pool' and (bytes > 999999 or name = 'free memory')
3 order by bytes desc;
NAME BYTES
-------------------------------- ----------------
free memory 21706067344
gcs resources 5435423424
gcs shadows 3762985536
KGLH0 1615442296
FileOpenBlock 1546876960
db_block_hash_buckets 1493176320
SQLA 1488176552
PRTMV 842208664
Checkpoint queue 655392768
dbktb: trace buffer 424116224
gcs res hash bucket 402653184
gc name table 301989888
SQLP 296812680
KGLHD 271742152
ges enqueues 222679424
ges resource 211524720
ASH buffers 134217728
gc read wait freelist 131040000
event statistics per sess 99323904
ksunfy : SSO free list 94913536
KQR L PO 94783184
ges big msg buffers 90561208
KGLDA 82415672
KQR X PO 69032056
KGLS 67494440
simulator hash buckets 67239936
Multiblock Index SO 61986816
dbwriter coalesce buffer 50380800
ASM extent pointer array 49736856
state objects 43433000
FileIdentificatonBlock 38029984
dirty object counts array 33554432
PX msg pool 30922312
PLDIA 25881112
procs: ksunfy 24560000
PRTDS 23963568
enqueue 21630808
transaction 21324224
object queue 18478720
PLMCD 17889176
buffer handles 17600008
XDBSC 14827552
db_files 14628048
write state object 13977456
ges regular msg buffers 13305208
DML lock 11855784
ges resource hash table 11534336
ksdhng: els blkrs cache 10874880
KSK VT POOL 10758656
object queue hash buckets 9437184
ktlbk state objects 8971560
row cache 8641136
ges reserved msg buffers 8440008
Wait History Segment 7854080
file state object 7799232
ges process array 7604560
enqueue resources 6440208
SGA - SWRF Metric CHBs 6285552
ksr message pool free que 6054192
init_heap_kfsg 5300208
KGLSG 5267216
KEWS sesstat values 4893696
JOXLP 4583128
KKSSP 4580968
KGLNA 4289968
KCB Table Scan Buffer 4198400
kglsim hash table bkts 4194304
KSFD SGA I/O b 4190328
JOXLE 3850488
branch 3226544
KOKTD 2830328
obj stats allocation chun 2703360
keswx:plan en 2665792
pso tbs: ksunfy 2600000
Temporary Tables State Ob 2448224
constraints 2428592
SGA - SWRF Metric Eidbuf 2364336
parameter table block 2330416
channel handle 2181016
KSXR receive buffers 2123776
ksws msg buffers 2056192
file # translation table 2000112
KTI SGA freea 1884160
txncallback 1753256
SEQ S.O. 1752064
KGSK scheduler 1695376
UNDO INFO SEGMENTED ARRAY 1681536
Sort Segment 1655488
keswx:batch o 1640296
PX subheap 1530712
KSKQ SGA NODEINFO 1359872
message pool freequeue 1353368
ksdhng: el wtr cache 1329152
KTC latch subh 1282336
gc lock contexts 1280632
KGLA 1242376
messages 1200000
dummy 1163848
locator state object 1154088
kjdrrmreq freelist elemen 1120000
KUPP subheap 1048536
101 rows selected.
SQL>
SQL>
SQL> /* Current SGA Buffer & Pool sizes */
SQL>
SQL> col name for a40
SQL> col value for a10
SQL>
SQL> select component, current_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------
shared pool 42412802048
large pool 5368709120
java pool 5368709120
streams pool 10737418240
DEFAULT buffer cache 193273528320
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache 0
14 rows selected.
SQL>
SQL> select name,value from v$system_parameter where name in ( 'memory_max_target', 'memory_target', 'sga_max_size', 'sga_target', 'shared_pool_size', 'db_cache_size', 'large_pool_size', 'java_pool_size', 'pga_aggregate_target', 'workarea_size_policy', 'streams_pool_size' ) ;
NAME VALUE
---------------------------------------- ----------
sga_max_size 2587717795
84
shared_pool_size 4241280204
8
large_pool_size 5368709120
java_pool_size 5368709120
streams_pool_size 1073741824
0
sga_target 0
memory_target 0
memory_max_target 3344705781
76
db_cache_size 1932735283
20
pga_aggregate_target 7516192768
0
workarea_size_policy AUTO
11 rows selected.
SQL>
SQL>
SQL> /* Shared pool Changes */
SQL>
SQL> ALTER SESSION SET nls_date_format = 'DD/MM/YYYY HH:MI:SS';
Session altered.
SQL>
SQL> COL COMPONENT FORMAT A25
SQL> COL INITIAL_SIZE FORMAT A10
SQL> COL FINAL_SIZE FORMAT A10
SQL>
SQL>
SQL> select component,oper_type,initial_size,target_size,final_size,status,to_char(start_time,'dd-mm-yyyy hh:mi:ss'),to_char(end_time,'dd-mm-yyyy hh:mi:ss') from v$sga_resize_ops;
COMPONENT OPER_TYPE INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS TO_CHAR(START_TIME,'DD-MM-YYYYHH:MI:SS') TO_CHAR(END_TIME,'DD-MM-YYYYHH:MI:SS')
------------------------- --------------------------------------- ------------ ----------- ---------- --------------------------- --------------------------------------------------------- ---------------------------------------------------------
shared pool STATIC ########## 4.2413E+10 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
large pool STATIC ########## 5368709120 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
java pool STATIC ########## 5368709120 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
streams pool STATIC ########## 1.0737E+10 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
DEFAULT buffer cache INITIALIZING ########## 1.9327E+11 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:52:33
DEFAULT buffer cache STATIC ########## 1.9327E+11 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
ASM Buffer Cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
RECYCLE buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
DEFAULT 2K buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
DEFAULT 4K buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
DEFAULT 8K buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
DEFAULT 16K buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
DEFAULT 32K buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
KEEP buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
14 rows selected.
SQL>
SQL>
SQL> SELECT COMPONENT ,OPER_TYPE,INITIAL_SIZE "Initial",FINAL_SIZE "Final",to_char(start_time,'dd-mon hh24:mi:ss') Started FROM V$MEMORY_RESIZE_OPS;
COMPONENT OPER_TYPE Initial Final STARTED
------------------------- --------------------------------------- ---------- ---------- ------------------------------------------------------------------------
shared pool STATIC 0 4.2413E+10 28-mar 10:51:11
ASM Buffer Cache STATIC 0 0 28-mar 10:51:11
java pool STATIC 0 5368709120 28-mar 10:51:11
streams pool STATIC 0 1.0737E+10 28-mar 10:51:11
SGA Target STATIC 0 0 28-mar 10:51:11
DEFAULT buffer cache INITIALIZING 1.9327E+11 1.9327E+11 28-mar 10:51:11
DEFAULT buffer cache STATIC 0 1.9327E+11 28-mar 10:51:11
KEEP buffer cache STATIC 0 0 28-mar 10:51:11
RECYCLE buffer cache STATIC 0 0 28-mar 10:51:11
DEFAULT 2K buffer cache STATIC 0 0 28-mar 10:51:11
DEFAULT 4K buffer cache STATIC 0 0 28-mar 10:51:11
DEFAULT 8K buffer cache STATIC 0 0 28-mar 10:51:11
DEFAULT 16K buffer cache STATIC 0 0 28-mar 10:51:11
DEFAULT 32K buffer cache STATIC 0 0 28-mar 10:51:11
PGA Target STATIC 0 7.5162E+10 28-mar 10:51:11
large pool STATIC 0 5368709120 28-mar 10:51:11
16 rows selected.
SQL>
SQL>
SQL> select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
2 from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
3 where a.indx = b.indx and a.indx = c.indx and a.ksppinm in
4 ('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size','__pga_aggregate_target','__sga_target','memory_target');
Parameter
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Session Value
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Instance Value
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
__shared_pool_size
42412802048
42412802048
__large_pool_size
5368709120
5368709120
__java_pool_size
5368709120
5368709120
__streams_pool_size
10737418240
10737418240
__sga_target
0
0
memory_target
0
0
__db_cache_size
193273528320
193273528320
__pga_aggregate_target
75161927680
75161927680
8 rows selected.
SQL>
SQL>
SQL> /* Library Cache Stats */
SQL> select NAMESPACE,GETHITRATIO,PINHITRATIO,RELOADS,INVALIDATIONS from v$librarycache;
NAMESPACE GETHITRATIO PINHITRATIO RELOADS INVALIDATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ---------- -------------
SQL AREA .998286153 .888272235 31893251 31970307
TABLE/PROCEDURE .999822254 .999651821 215412 447
BODY .9997454 .999807994 2072 778
TRIGGER .999989321 .999792996 1 0
INDEX .995363051 .965833253 4288 0
CLUSTER .99886647 .99887095 0 0
DIRECTORY 1.0000003 .999996372 4 0
QUEUE .999953784 .999858694 230 0
JAVA SOURCE .996742671 .996742671 0 0
JAVA RESOURCE .998599641 .999293164 0 0
APP CONTEXT .99839099 .999918514 0 0
RULESET .720316623 .921192758 0 0
XML SCHEMA .996770026 .996763754 0 0
SUBSCRIPTION .950160772 .96727899 135 0
LOCATION .963458618 .958333333 54 0
JAVA DATA .996749729 .997442455 2 0
TRANSFORMATION 0 0 0 0
RULE .631578947 .631578947 0 0
XDB CONFIG 0 0 0 0
USER AGENT .981481481 .962962963 0 0
HINTSET OBJECT .994145199 .988290398 0 0
TEMPORARY TABLE .999284692 0 2794 0
EDITION .999999717 .999999713 0 0
DBLINK .999999153 1 0 0
OBJECT ID 0 1 0 0
SCHEMA .999997421 1 0 1
DBINSTANCE 0 1 0 0
SQL AREA STATS .996274255 .996274193 0 0
ACCOUNT_STATUS .999994834 1 0 0
SQL AREA BUILD .996438709 1 0 0
30 rows selected.
SQL> spool off
SQL>
SQL>
SQL>
SQL> select * from v$sgainfo
2 ;
NAME BYTES RESIZEABL
---------------------------------------- ---------------- ---------
Fixed SGA Size 2197352 No
Redo Buffers 536895488 No
Buffer Cache Size 193273528320 Yes
Shared Pool Size 42412802048 Yes
Large Pool Size 5368709120 Yes
Java Pool Size 5368709120 Yes
Streams Pool Size 10737418240 Yes
Shared IO Pool Size 0 Yes
Granule Size 536870912 No
Maximum SGA Size 257700265984 No
Startup overhead in Shared Pool 14682637568 No
Free SGA Memory Available 0
12 rows selected.
SQL> elect * from v$sgastat where name like ‘free%’;
SP2-0734: unknown command beginning "elect * fr..." - rest of line ignored.
SQL> select * from v$sgastat where name like ‘free%’;
select * from v$sgastat where name like ‘free%’
*
ERROR at line 1:
ORA-00911: invalid character
SQL> select * from v$sgastat where name like 'free%';
POOL NAME BYTES
------------------------------------ ---------------------------------------- ----------------
shared pool free memory 21705777032
large pool free memory 5368315904
java pool free memory 5338436864
streams pool free memory 10737391032
SQL>
SQL> alter database flashback on;
Database altered.
SQL>
SQL> /* This is alert log information from both the rac nodes
SQL> FROM NODE 01
SQL>
SQL> ALTER SYSTEM SET db_flashback_retention_target=360 SCOPE=BOTH SID='*';
SQL> lThu Aug 30 09:07:31 2018
SQL> ALTER SYSTEM SET db_recovery_file_dest_size='510G' SCOPE=BOTH SID='*';
SQL> llThu Aug 30 09:19:55 2018
SQL> alter database flashback on
SQL> Thu Aug 30 09:19:56 2018
SQL> RVWR started with pid=101, OS id=26127
SQL> Thu Aug 30 09:20:07 2018
SQL> Allocated 268435456 bytes in shared pool for flashback generation buffer
SQL> Thu Aug 30 09:20:30 2018
SQL> Flashback Database Enabled at SCN 10741220712251
SQL> Completed: alter database flashback on
SQL>
SQL>
SQL>
SQL>
SQL> FROM NODE 119
SQL>
SQL> LNS: Standby redo logfile selected for thread 3 sequence 18150 for destination LOG_ARCHIVE_DEST_7
SQL> Thu Aug 30 06:49:26 2018
SQL> LNS: Standby redo logfile selected for thread 3 sequence 18150 for destination LOG_ARCHIVE_DEST_5
SQL> Thu Aug 30 06:49:29 2018
SQL> Archived Log entry 1217747 added for thread 3 sequence 18149 ID 0x10c5e65e dest 1:
SQL> lThu Aug 30 09:06:39 2018
SQL> Suspending MMON slave action kewfmadsa_ for 82800 seconds
SQL> lllThu Aug 30 09:19:55 2018
SQL> RVWR started with pid=111, OS id=24914
SQL> Thu Aug 30 09:20:28 2018
SQL> Allocated 268435456 bytes in shared pool for flashback generation buffer
SQL>
SQL> */
SQL>
=== LAST SECTION :
SQL> alter database flashback on;
^C
alter database flashback on
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
in this scenario, try below on the same node or the other RAC node :
SQL> show parameter _memory_imm_mode_without_autosga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_memory_imm_mode_without_autosga boolean FALSE
SQL> alter system set "_memory_imm_mode_without_autosga"=TRUE;
System altered.
SQL> show parameter _memory_imm_mode_without_autosga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_memory_imm_mode_without_autosga boolean TRUE
===Errors from alert log
/u01/app/oracle/diag/rdbms/orcl_live2/ORCL_2/incident/incdir_880617/ORCL_2_ora_59097_i880617.trc
Errors in file /u01/app/oracle/diag/rdbms/orcl_live2/ORCL_2/incident/incdir_880617/ORCL_2_ora_59097_i880617.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
Sun Aug 30 03:58:53 2018
Errors in file /u01/app/oracle/diag/rdbms/orcl_live2/ORCL_2/trace/ORCL_2_ora_49545.trc:
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
Sun Aug 30 03:58:58 2018
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl_live2/ORCL_2/trace/ORCL_2_ora_59762.trc (incident=807844):
ORA-00600: internal error code, arguments: [4414], [0], [0], [7997], [4], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4414], [13019], [1], [7997], [4], [], [], [], [], [], [], []
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
Incident details in: /u01/app/oracle/diag/rdbms/orcl_live2/ORCL_2/incident/incdir_807844/ORCL_2_ora_59762_i807844.trc
Sun Aug 30 03:59:14 2018
- Some of the prep work on looking at the Shared pool parameters is to avoid getting ora-04031 while enabling flashback on
- this 1st section gives what needs to be done followed by what things need to be considered such as the storage allocation, retention, etc.,
- last section will give you info on what to do if you hit ora-04031
Section 1
SQL> select instance_name, host_name from v$instance;
INSTANCE_NAME HOST_NAME
--------------- -------------------------
orcl01 rachost01
SQL> select name, db_unique_name, open_mode, DATABASE_ROLE, current_scn from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN
--------------- ------------------------------ -------------------- ------------------------- -----------
orcl ORCL_LIVE1 READ WRITE PRIMARY 1.0741E+13
SQL> col name form a60
SQL> select * from v$sgainfo;
NAME BYTES RESIZEABL
------------------------------------------------------------ ---------- ---------
Fixed SGA Size 2197352 No
Redo Buffers 536895488 No
Buffer Cache Size 1.9327E+11 Yes
Shared Pool Size 4.2413E+10 Yes
Large Pool Size 5368709120 Yes
Java Pool Size 5368709120 Yes
Streams Pool Size 1.0737E+10 Yes
Shared IO Pool Size 0 Yes
Granule Size 536870912 No
Maximum SGA Size 2.5770E+11 No
Startup overhead in Shared Pool 1.4683E+10 No
Free SGA Memory Available 0
12 rows selected.
SQL> l
1* select * from v$sgainfo
SQL> select name, BYTES/1024/1024/1024 from v$sgainfo;
NAME BYTES/1024/1024/1024
------------------------------------------------------------ --------------------
Fixed SGA Size .002046444
Redo Buffers .500022888
Buffer Cache Size 180
Shared Pool Size 39.5
Large Pool Size 5
Java Pool Size 5
Streams Pool Size 10
Shared IO Pool Size 0
Granule Size .5
Maximum SGA Size 240.002075
Startup overhead in Shared Pool 13.6742718
Free SGA Memory Available 0
12 rows selected.
SQL> show parameter _memory_imm_mode_without_autosga
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
_memory_imm_mode_without_autosga boolean FALSE
SQL> show parameter block_size
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_block_size integer 8192
SQL> show parameter &x
Enter value for x: sga_target
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
sga_target big integer 0
SQL> select name, db_unique_name, FLASHBACK_ON, DATABASE_ROLE from v$database;
NAME DB_UNIQUE_NAME FLASHBACK_ON DATABASE_ROLE
--------------- ------------------------------ ------------------------------------------------------ -------------------------
orcl ORCL_LIVE1 NO PRIMARY
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_flashback_retention_target integer 1440
SQL>
SQL> column name format a30
SQL> select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB, FREE_MB from v$asm_diskgroup;
NAME ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB
------------------------------ -------------------- --------------------------------- ------------------ ---------- ----------
orcl_LIVE1_ARC 4194304 CONNECTED EXTERN 3342336 3212292
orcl_LIVE1_DATA 4194304 CONNECTED EXTERN 28966912 7184328
orcl_LIVE1_FRA 4194304 CONNECTED EXTERN 557056 556244
orcl_LIVE1_REDO1A 67108864 CONNECTED EXTERN 69568 26752
orcl_LIVE1_REDO1B 67108864 CONNECTED EXTERN 69568 26752
orcl_LIVE1_REDO2A 67108864 CONNECTED EXTERN 69568 26752
orcl_LIVE1_REDO2B 67108864 CONNECTED EXTERN 69568 5760
orcl_LIVE1_REDO3A 67108864 CONNECTED EXTERN 69568 11008
orcl_LIVE1_REDO3B 67108864 CONNECTED EXTERN 69568 11008
orcl_LIVE1_REDO4A 67108864 CONNECTED EXTERN 69568 5760
orcl_LIVE1_REDO4B 67108864 CONNECTED EXTERN 69568 5760
OGG 4194304 MOUNTED EXTERN 2228224 66312
12 rows selected.
SQL> show parameter &x
Enter value for x: db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string +orcl_LIVE1_FRA
db_recovery_file_dest_size big integer 6144G
SQL>
SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=360 scope=both sid='*';
System altered.
SQL> alter system set db_recovery_file_dest_size=510G scope=both sid='*';
System altered.
SQL> show parameter &x
Enter value for x: db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
db_recovery_file_dest string +orcl_LIVE1_FRA
db_recovery_file_dest_size big integer 510G
SQL>
SQL> COL NAME FORMAT A32
SQL> COL VALUE FORMAT A40
SQL> SPOOL SGAPARAMS.TXT
SQL> /* Database Identification */
SQL> select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
NAME PLATFORM_ID DATABASE_ROLE
-------------------------------- ----------- -------------------------
orcl 2 PRIMARY
SQL> select * from V$version where banner like 'Oracle Database%';
BANNER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL> /* Shared Pool parameters */
SQL> select nam.ksppinm NAME, val.KSPPSTVL VALUE
2 from x$ksppi nam, x$ksppsv val
3 where nam.indx = val.indx and (nam.ksppinm like '%shared_pool%' or nam.ksppinm like '_4031%' or nam.ksppinm in ('_kghdsidx_count','_ksmg_granule_size','_memory_imm_mode_without_autosga'))
4 order by 1;
NAME VALUE
-------------------------------- ----------------------------------------
_4031_dump_bitvec 67194879
_4031_dump_interval 300
_4031_max_dumps 100
_4031_sga_dump_interval 3600
_4031_sga_max_dumps 10
__shared_pool_size 42412802048
_dm_max_shared_pool_pct 1
_enable_shared_pool_durations FALSE
_io_shared_pool_size 4194304
_kghdsidx_count 7
_ksmg_granule_size 536870912
_memory_imm_mode_without_autosga FALSE
_shared_pool_max_size 0
_shared_pool_minsize_on FALSE
_shared_pool_reserved_min_alloc 4400
_shared_pool_reserved_pct 5
shared_pool_reserved_size 4194304000
shared_pool_size 42412802048
18 rows selected.
SQL> SPOOL OFF
SQL>
SQL> SET PAGESIZE 900
SQL> SET LINESIZE 255
SQL> COL BYTES FORMAT 999999999999999
SQL> COL CURRENT_SIZE FORMAT 999999999999999
SQL> COL 'Total Shared Pool Usage' FORMAT 99999999999999999999999
SQL> set echo on
SQL>
SQL> SPOOL SPINFO.TXT
SQL>
SQL> /* Database Identification */
SQL> select NAME, PLATFORM_ID, DATABASE_ROLE from v$database;
NAME PLATFORM_ID DATABASE_ROLE
-------------------------------- ----------- -------------------------
orcl 2 PRIMARY
SQL> select * from V$version where banner like 'Oracle Database%';
BANNER
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SQL>
SQL> /* Shared Pool 4031 information */
SQL> select REQUEST_FAILURES, LAST_FAILURE_SIZE from V$SHARED_POOL_RESERVED;
REQUEST_FAILURES LAST_FAILURE_SIZE
---------------- -----------------
0 0
SQL>
SQL> /* Shared Pool Reserved 4031 information */
SQL> select REQUESTS, REQUEST_MISSES, free_space, avg_free_size, free_count, max_free_size from V$SHARED_POOL_RESERVED;
REQUESTS REQUEST_MISSES FREE_SPACE AVG_FREE_SIZE FREE_COUNT MAX_FREE_SIZE
---------- -------------- ---------- ------------- ---------- -------------
0 0 2085171840 47390269.1 40 149991456
SQL>
SQL> /* Shared Pool Memory Allocations by Size */
SQL> select name, bytes from v$sgastat
2 where pool = 'shared pool' and (bytes > 999999 or name = 'free memory')
3 order by bytes desc;
NAME BYTES
-------------------------------- ----------------
free memory 21706067344
gcs resources 5435423424
gcs shadows 3762985536
KGLH0 1615442296
FileOpenBlock 1546876960
db_block_hash_buckets 1493176320
SQLA 1488176552
PRTMV 842208664
Checkpoint queue 655392768
dbktb: trace buffer 424116224
gcs res hash bucket 402653184
gc name table 301989888
SQLP 296812680
KGLHD 271742152
ges enqueues 222679424
ges resource 211524720
ASH buffers 134217728
gc read wait freelist 131040000
event statistics per sess 99323904
ksunfy : SSO free list 94913536
KQR L PO 94783184
ges big msg buffers 90561208
KGLDA 82415672
KQR X PO 69032056
KGLS 67494440
simulator hash buckets 67239936
Multiblock Index SO 61986816
dbwriter coalesce buffer 50380800
ASM extent pointer array 49736856
state objects 43433000
FileIdentificatonBlock 38029984
dirty object counts array 33554432
PX msg pool 30922312
PLDIA 25881112
procs: ksunfy 24560000
PRTDS 23963568
enqueue 21630808
transaction 21324224
object queue 18478720
PLMCD 17889176
buffer handles 17600008
XDBSC 14827552
db_files 14628048
write state object 13977456
ges regular msg buffers 13305208
DML lock 11855784
ges resource hash table 11534336
ksdhng: els blkrs cache 10874880
KSK VT POOL 10758656
object queue hash buckets 9437184
ktlbk state objects 8971560
row cache 8641136
ges reserved msg buffers 8440008
Wait History Segment 7854080
file state object 7799232
ges process array 7604560
enqueue resources 6440208
SGA - SWRF Metric CHBs 6285552
ksr message pool free que 6054192
init_heap_kfsg 5300208
KGLSG 5267216
KEWS sesstat values 4893696
JOXLP 4583128
KKSSP 4580968
KGLNA 4289968
KCB Table Scan Buffer 4198400
kglsim hash table bkts 4194304
KSFD SGA I/O b 4190328
JOXLE 3850488
branch 3226544
KOKTD 2830328
obj stats allocation chun 2703360
keswx:plan en 2665792
pso tbs: ksunfy 2600000
Temporary Tables State Ob 2448224
constraints 2428592
SGA - SWRF Metric Eidbuf 2364336
parameter table block 2330416
channel handle 2181016
KSXR receive buffers 2123776
ksws msg buffers 2056192
file # translation table 2000112
KTI SGA freea 1884160
txncallback 1753256
SEQ S.O. 1752064
KGSK scheduler 1695376
UNDO INFO SEGMENTED ARRAY 1681536
Sort Segment 1655488
keswx:batch o 1640296
PX subheap 1530712
KSKQ SGA NODEINFO 1359872
message pool freequeue 1353368
ksdhng: el wtr cache 1329152
KTC latch subh 1282336
gc lock contexts 1280632
KGLA 1242376
messages 1200000
dummy 1163848
locator state object 1154088
kjdrrmreq freelist elemen 1120000
KUPP subheap 1048536
101 rows selected.
SQL>
SQL>
SQL> /* Current SGA Buffer & Pool sizes */
SQL>
SQL> col name for a40
SQL> col value for a10
SQL>
SQL> select component, current_size from v$sga_dynamic_components;
COMPONENT CURRENT_SIZE
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------
shared pool 42412802048
large pool 5368709120
java pool 5368709120
streams pool 10737418240
DEFAULT buffer cache 193273528320
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache 0
14 rows selected.
SQL>
SQL> select name,value from v$system_parameter where name in ( 'memory_max_target', 'memory_target', 'sga_max_size', 'sga_target', 'shared_pool_size', 'db_cache_size', 'large_pool_size', 'java_pool_size', 'pga_aggregate_target', 'workarea_size_policy', 'streams_pool_size' ) ;
NAME VALUE
---------------------------------------- ----------
sga_max_size 2587717795
84
shared_pool_size 4241280204
8
large_pool_size 5368709120
java_pool_size 5368709120
streams_pool_size 1073741824
0
sga_target 0
memory_target 0
memory_max_target 3344705781
76
db_cache_size 1932735283
20
pga_aggregate_target 7516192768
0
workarea_size_policy AUTO
11 rows selected.
SQL>
SQL>
SQL> /* Shared pool Changes */
SQL>
SQL> ALTER SESSION SET nls_date_format = 'DD/MM/YYYY HH:MI:SS';
Session altered.
SQL>
SQL> COL COMPONENT FORMAT A25
SQL> COL INITIAL_SIZE FORMAT A10
SQL> COL FINAL_SIZE FORMAT A10
SQL>
SQL>
SQL> select component,oper_type,initial_size,target_size,final_size,status,to_char(start_time,'dd-mm-yyyy hh:mi:ss'),to_char(end_time,'dd-mm-yyyy hh:mi:ss') from v$sga_resize_ops;
COMPONENT OPER_TYPE INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS TO_CHAR(START_TIME,'DD-MM-YYYYHH:MI:SS') TO_CHAR(END_TIME,'DD-MM-YYYYHH:MI:SS')
------------------------- --------------------------------------- ------------ ----------- ---------- --------------------------- --------------------------------------------------------- ---------------------------------------------------------
shared pool STATIC ########## 4.2413E+10 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
large pool STATIC ########## 5368709120 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
java pool STATIC ########## 5368709120 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
streams pool STATIC ########## 1.0737E+10 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
DEFAULT buffer cache INITIALIZING ########## 1.9327E+11 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:52:33
DEFAULT buffer cache STATIC ########## 1.9327E+11 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
ASM Buffer Cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
RECYCLE buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
DEFAULT 2K buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
DEFAULT 4K buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
DEFAULT 8K buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
DEFAULT 16K buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
DEFAULT 32K buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
KEEP buffer cache STATIC ########## 0 ########## COMPLETE 28-03-2018 10:51:11 28-03-2018 10:51:11
14 rows selected.
SQL>
SQL>
SQL> SELECT COMPONENT ,OPER_TYPE,INITIAL_SIZE "Initial",FINAL_SIZE "Final",to_char(start_time,'dd-mon hh24:mi:ss') Started FROM V$MEMORY_RESIZE_OPS;
COMPONENT OPER_TYPE Initial Final STARTED
------------------------- --------------------------------------- ---------- ---------- ------------------------------------------------------------------------
shared pool STATIC 0 4.2413E+10 28-mar 10:51:11
ASM Buffer Cache STATIC 0 0 28-mar 10:51:11
java pool STATIC 0 5368709120 28-mar 10:51:11
streams pool STATIC 0 1.0737E+10 28-mar 10:51:11
SGA Target STATIC 0 0 28-mar 10:51:11
DEFAULT buffer cache INITIALIZING 1.9327E+11 1.9327E+11 28-mar 10:51:11
DEFAULT buffer cache STATIC 0 1.9327E+11 28-mar 10:51:11
KEEP buffer cache STATIC 0 0 28-mar 10:51:11
RECYCLE buffer cache STATIC 0 0 28-mar 10:51:11
DEFAULT 2K buffer cache STATIC 0 0 28-mar 10:51:11
DEFAULT 4K buffer cache STATIC 0 0 28-mar 10:51:11
DEFAULT 8K buffer cache STATIC 0 0 28-mar 10:51:11
DEFAULT 16K buffer cache STATIC 0 0 28-mar 10:51:11
DEFAULT 32K buffer cache STATIC 0 0 28-mar 10:51:11
PGA Target STATIC 0 7.5162E+10 28-mar 10:51:11
large pool STATIC 0 5368709120 28-mar 10:51:11
16 rows selected.
SQL>
SQL>
SQL> select a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value"
2 from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
3 where a.indx = b.indx and a.indx = c.indx and a.ksppinm in
4 ('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size','__pga_aggregate_target','__sga_target','memory_target');
Parameter
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Session Value
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Instance Value
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
__shared_pool_size
42412802048
42412802048
__large_pool_size
5368709120
5368709120
__java_pool_size
5368709120
5368709120
__streams_pool_size
10737418240
10737418240
__sga_target
0
0
memory_target
0
0
__db_cache_size
193273528320
193273528320
__pga_aggregate_target
75161927680
75161927680
8 rows selected.
SQL>
SQL>
SQL> /* Library Cache Stats */
SQL> select NAMESPACE,GETHITRATIO,PINHITRATIO,RELOADS,INVALIDATIONS from v$librarycache;
NAMESPACE GETHITRATIO PINHITRATIO RELOADS INVALIDATIONS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ---------- -------------
SQL AREA .998286153 .888272235 31893251 31970307
TABLE/PROCEDURE .999822254 .999651821 215412 447
BODY .9997454 .999807994 2072 778
TRIGGER .999989321 .999792996 1 0
INDEX .995363051 .965833253 4288 0
CLUSTER .99886647 .99887095 0 0
DIRECTORY 1.0000003 .999996372 4 0
QUEUE .999953784 .999858694 230 0
JAVA SOURCE .996742671 .996742671 0 0
JAVA RESOURCE .998599641 .999293164 0 0
APP CONTEXT .99839099 .999918514 0 0
RULESET .720316623 .921192758 0 0
XML SCHEMA .996770026 .996763754 0 0
SUBSCRIPTION .950160772 .96727899 135 0
LOCATION .963458618 .958333333 54 0
JAVA DATA .996749729 .997442455 2 0
TRANSFORMATION 0 0 0 0
RULE .631578947 .631578947 0 0
XDB CONFIG 0 0 0 0
USER AGENT .981481481 .962962963 0 0
HINTSET OBJECT .994145199 .988290398 0 0
TEMPORARY TABLE .999284692 0 2794 0
EDITION .999999717 .999999713 0 0
DBLINK .999999153 1 0 0
OBJECT ID 0 1 0 0
SCHEMA .999997421 1 0 1
DBINSTANCE 0 1 0 0
SQL AREA STATS .996274255 .996274193 0 0
ACCOUNT_STATUS .999994834 1 0 0
SQL AREA BUILD .996438709 1 0 0
30 rows selected.
SQL> spool off
SQL>
SQL>
SQL>
SQL> select * from v$sgainfo
2 ;
NAME BYTES RESIZEABL
---------------------------------------- ---------------- ---------
Fixed SGA Size 2197352 No
Redo Buffers 536895488 No
Buffer Cache Size 193273528320 Yes
Shared Pool Size 42412802048 Yes
Large Pool Size 5368709120 Yes
Java Pool Size 5368709120 Yes
Streams Pool Size 10737418240 Yes
Shared IO Pool Size 0 Yes
Granule Size 536870912 No
Maximum SGA Size 257700265984 No
Startup overhead in Shared Pool 14682637568 No
Free SGA Memory Available 0
12 rows selected.
SQL> elect * from v$sgastat where name like ‘free%’;
SP2-0734: unknown command beginning "elect * fr..." - rest of line ignored.
SQL> select * from v$sgastat where name like ‘free%’;
select * from v$sgastat where name like ‘free%’
*
ERROR at line 1:
ORA-00911: invalid character
SQL> select * from v$sgastat where name like 'free%';
POOL NAME BYTES
------------------------------------ ---------------------------------------- ----------------
shared pool free memory 21705777032
large pool free memory 5368315904
java pool free memory 5338436864
streams pool free memory 10737391032
SQL>
SQL> alter database flashback on;
Database altered.
SQL>
SQL> /* This is alert log information from both the rac nodes
SQL> FROM NODE 01
SQL>
SQL> ALTER SYSTEM SET db_flashback_retention_target=360 SCOPE=BOTH SID='*';
SQL> lThu Aug 30 09:07:31 2018
SQL> ALTER SYSTEM SET db_recovery_file_dest_size='510G' SCOPE=BOTH SID='*';
SQL> llThu Aug 30 09:19:55 2018
SQL> alter database flashback on
SQL> Thu Aug 30 09:19:56 2018
SQL> RVWR started with pid=101, OS id=26127
SQL> Thu Aug 30 09:20:07 2018
SQL> Allocated 268435456 bytes in shared pool for flashback generation buffer
SQL> Thu Aug 30 09:20:30 2018
SQL> Flashback Database Enabled at SCN 10741220712251
SQL> Completed: alter database flashback on
SQL>
SQL>
SQL>
SQL>
SQL> FROM NODE 119
SQL>
SQL> LNS: Standby redo logfile selected for thread 3 sequence 18150 for destination LOG_ARCHIVE_DEST_7
SQL> Thu Aug 30 06:49:26 2018
SQL> LNS: Standby redo logfile selected for thread 3 sequence 18150 for destination LOG_ARCHIVE_DEST_5
SQL> Thu Aug 30 06:49:29 2018
SQL> Archived Log entry 1217747 added for thread 3 sequence 18149 ID 0x10c5e65e dest 1:
SQL> lThu Aug 30 09:06:39 2018
SQL> Suspending MMON slave action kewfmadsa_ for 82800 seconds
SQL> lllThu Aug 30 09:19:55 2018
SQL> RVWR started with pid=111, OS id=24914
SQL> Thu Aug 30 09:20:28 2018
SQL> Allocated 268435456 bytes in shared pool for flashback generation buffer
SQL>
SQL> */
SQL>
=== LAST SECTION :
SQL> alter database flashback on;
^C
alter database flashback on
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
in this scenario, try below on the same node or the other RAC node :
SQL> show parameter _memory_imm_mode_without_autosga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_memory_imm_mode_without_autosga boolean FALSE
SQL> alter system set "_memory_imm_mode_without_autosga"=TRUE;
System altered.
SQL> show parameter _memory_imm_mode_without_autosga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_memory_imm_mode_without_autosga boolean TRUE
===Errors from alert log
/u01/app/oracle/diag/rdbms/orcl_live2/ORCL_2/incident/incdir_880617/ORCL_2_ora_59097_i880617.trc
Errors in file /u01/app/oracle/diag/rdbms/orcl_live2/ORCL_2/incident/incdir_880617/ORCL_2_ora_59097_i880617.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
Sun Aug 30 03:58:53 2018
Errors in file /u01/app/oracle/diag/rdbms/orcl_live2/ORCL_2/trace/ORCL_2_ora_49545.trc:
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
Sun Aug 30 03:58:58 2018
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/app/oracle/diag/rdbms/orcl_live2/ORCL_2/trace/ORCL_2_ora_59762.trc (incident=807844):
ORA-00600: internal error code, arguments: [4414], [0], [0], [7997], [4], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [4414], [13019], [1], [7997], [4], [], [], [], [], [], [], []
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
ORA-00379: no free buffers available in buffer pool DEFAULT for block size 8K
Incident details in: /u01/app/oracle/diag/rdbms/orcl_live2/ORCL_2/incident/incdir_807844/ORCL_2_ora_59762_i807844.trc
Sun Aug 30 03:59:14 2018
