Can't allocate log, archival required - alert log file in Oracle

-- Troubleshoot "Can't allocate log, archival required"
-- Found Oracle 11gR2 RAC environment
-- Found from alert log

< ORACLE Instance PRODDB2 - Can not allocate log, archival required
< Thread 2 cannot allocate new log, sequence 12265
< All online logs needed archiving

-- Cause:
It seems that all the online redo logs are full and you need to empty them. This can happeen due to two reasons
1) archivelog destination is full
2)archiving is disabled although the database is in archive log mode

-- Diagnosis: - a sample result

SQL> show parameter log_archive_max_process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     4


SQL> show parameter LOG_ARCHIVE_START;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     FALSE


-- solution:

If redo log size is too small to handle current redo log switch frequency then bigger redo log size will work in this case. Increase redo log size to 500M.

OTHERWISE

If automatic archival is disabled, run the following command

sql> ALTER SYSTEM ARCHIVE LOG ALL;


it is better if you set as well LOG_ARCHIVE_START parameter into init.ora

LOG_ARCHIVE_START=TRUE


Consider increasing either or both of the initialisation parameters LOG_ARCHIVE_BUFFER_SIZE and LOG_ARCHIVE_BUFFERS.

Sure it will help.

ORA-00600: internal error code, arguments: [15851]

-- ORA-00600: internal error code, arguments: [15851], [3], [2], [1], [1], [], [], [], [], [], [], []
-- Issue faced in Oracle 11gR2

sql> EXEC dbms_stats.gather_schema_stats('HR', cascade=>TRUE);

ORA-00600: internal error code, arguments: [15851], [3], [2], [1], [1], [], [], [], [], [], [], []

*Cause: 

> This is the generic internal error number for Oracle program exceptions.
> This indicates that a process has encountered an exceptional condition.
> Due to some tables stats failure, the entire schema gather failed.

*Action: 

> Report as a bug - the first argument is the internal error number.
> Create dynamic script to gather all tables for the schema and find what tables stats not gathered and failed with 'ORA-00600'.                                                                                                   

-- Dynamic script: ( Asume schema name/ owner of tables is 'HR'):
SQL > select ' exec dbms_stats.gather_table_stats('''||d.owner||''','''||d.table_name||''','
||'cascade => true'||');' from all_tables d where owner='HR' order by table_name;

-- Sample example to show which table failed with error.

SQL> exec dbms_stats.gather_table_stats('HR','EMPLOYEE');

BEGIN dbms_stats.gather_table_stats('HR','EMPLOYEE'); END;
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [15851], [3], [2], [1], [1], [], [],[], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1

*Solution:
Try-1:

SQL> EXEC dbms_stats.gather_TABLE_stats(OWNNAME=>'HR',TABNAME=>'EMPLOYEE');

-- if above/ same error came then,
Try-2:
SQLl> exec DBMS_STATS.gather_table_stats(ownname=> 'HR',tabname=>'EMPLOYEE', estimate_percent => 5,cascade=>TRUE, degree=>1,granularity=>'AUTO',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

-- if above/ same error came then,
Try-3:

SQL> exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL> exec DBMS_STATS.gather_table_stats(ownname=> 'HR',tabname=>'EMPLOYEE',
estimate_percent => 50,cascade=>TRUE,degree=>1,granularity=>'AUTO',method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO');

Note: Increase estimate % with some larger value. It will work

-- if above/ same error came then,
Try-4:
SQL> exec DBMS_STATS.gather_table_stats(ownname=> 'HR',tabname=>'EMPLOYEE', estimate_percent => 10, cascade=>TRUE,degree=>1,granularity=>'AUTO',method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1');

Click here to view solution for Other ORA-00600

Sure it will work.
Don't forget to post comments.

when standby DB not in synch due to ORA-00333/00334/27069 ( Incomplete read from log member )- solution

-- Troubleshoot standby  ORA-00333/00334/27069 issue

-- When archive logs are not applying ( scenario based)
-- Applied in Oracle 11gR2 ( applicable to Oracle 10g onwards)
-- observed standby DB some times out of synch due to following error

Error Message: ( from alert log)

Errors in file /u03/app/oracle/diag/rdbms/PROD/PROD/trace/PROD_pr00_10867.trc:
ORA-00333: redo log read error block 30720 count 2048
ORA-00334: archived log: '/u03/ARCBKP/1_918_829075801.dbf'
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 30720
Additional information: 2048
Additional information: 32320
Incomplete read from log member '/u03/ARCBKP/1_918_829075801.dbf'. Trying next member.
Errors with log /u03/ARCBKP/1_918_829075801.dbf
MRP0: Detected read corruption! Retry recovery once log is re-fetched...


Solution:

Don't disturb recovery mode. If down, then bring the DB into recovery managed mode again. Register the archive log(s) which is/are not recovered.

SQL> alter database register logfile '/u03/ARCBKP/1_918_829075801.dbf';

Sure recover will be started.
Sure archive logs will start applying if cataloged already.

Troubleshoot "Can not allocate log, archival required" in alert log

-- Troubleshoot "Can not allocate log, archival required"
-- In Oracle 9i/10g/11g
-- Found from alert log

< ORACLE Instance PROD2 - Can not allocate log, archival required
< Thread 2 cannot allocate new log, sequence 12265
< All online logs needed archiving

-- Cause:
It seems that all the online redo logs are full and you need to empty them. This can happeen due to two reasons
1) archivelog destination is full
2)archiving is disabled although the database is in archive log mode

-- Diagnosis:


SQL> show parameter log_archive_max_process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     4


SQL> show parameter LOG_ARCHIVE_START;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     FALSE


-- solution:

If redo log size is too small to handle current redo log switch frequency then bigger redo log size will work in this case. Increase redo log size to 500M.

OTHERWISE

If automatic archival is disabled, run the following command

ALTER SYSTEM ARCHIVE LOG ALL;

it is better if you set as well LOG_ARCHIVE_START parameter into init.ora

LOG_ARCHIVE_START=TRUE

Consider increasing either or both of the initialisation parameters LOG_ARCHIVE_BUFFER_SIZE and LOG_ARCHIVE_BUFFERS.

Troubleshoot 'ora.ctssd' RAC services 'OBSERVER' status in Oracle 11gR2

-- Troubleshoot 'ora.ctssd' RAC services 'OBSERVER' status
-- In Oracle 11gR2
-- crsctl status resource -t -init in 11.2.0.2 grid infrastructure

-- Some times it may lead scan listener bounce

-- Status when issue found

$ crsctl status res -t -init
ora.ctssd
1 ONLINE ONLINE prddb1 OBSERVER

Node:2
$ crsctl status res -t -init

ora.ctssd
1 ONLINE ONLINE prddb2 OBSERVER

-- Issue find and troubleshoot

-- Collect all resource status and re-look

$ crsctl stat res -p

Rename '/etc/ntp.conf' file or moved to any other location to change "ora.ctssd" status to "online" in both nodes.
After change, now check

$ crsctl status res -t -init
ora.ctssd
      1        ONLINE  ONLINE       prddb1                    ACTIVE:0

Node:2
$ crsctl status res -t -init
ora.ctssd
      1        ONLINE  ONLINE       prddb2                    ACTIVE:0

-- Why ntp.conf not required some scenarios:

NTP (Network Time Protocol) is automatically configured and enabled to ensure time synchronization. Oracle VM automatically configures Oracle VM Manager as the NTP source for all Oracle VM Servers under its control.


- Configuring the NTP Service
To provide time services to the Oracle VM Servers, NTP must first be installed and configured on the Oracle VM Manager host server. Make sure that your Oracle VM Manager host is either registered with the Unbreakable Linux Network (ULN) or configured to use Oracle's public YUM service.

for details follow from Oracle document
link : http://docs.oracle.com/cd/E26996_01/E18548/html/manager_ntpconfig.html
or Click here to open

Restore Oracle database RAC to RAC (ASM) with creating fresh RAC services

-- Implemented IBM-AIX 6.1 flatform
-- Upgrade from 11.2.0.2 to 11.2.0.3
-- Oracle Clusterware 11.2.0.4
-- Source Oracle Software verison = 11.2.0.2
-- Target Oracle Sofware version = 11.2.0.3

About Document:
This document can be used to migrate data using RMAN from one cluster database to another cluster database with lower version to higher version of Oracle 11gR2. It is helpful to create all required clusterware services for newly restored database.

Basic Checks:
1) OS and Oracle version
2) Check transportable_platform ( endian format)
SQL> select * from v$transportable_platform

3) all user password like root, oracle and grid (if)

# export ORACLE_HOME=/oracle/app/11.2.0/grid
# export PATH=$ORACLE_HOME/bin:$PATH

=========================Created pfile in new server=======================

========================// initPROD1.ora //==============================

*._keep_remote_column_size=TRUE
*.audit_file_dest='/oracle/app/oracle/admin/PROD/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+DATA/PROD/controlfile/current.272.759561861','+FRA/PROD/controlfile/current.388.759561863'
*.cursor_sharing='EXACT'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='PROD'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=268435456000
*.diagnostic_dest='/oracle/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODXDB)'
PROD2.instance_number=2
PROD1.instance_number=1
*.log_archive_start=TRUE
*.memory_target=6871318528
*.nls_date_format='DD-MON-YYYY HH24:MI:SS'
*.open_cursors=300
*.processes=500
*.remote_listener='PRDDB-SCAN-IP:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.sec_case_sensitive_logon=FALSE
*.service_names='PROD'
*.session_cached_cursors=100
*.sessions=555
PROD2.thread=2
PROD1.thread=1
PROD1.undo_tablespace='UNDOTBS1'
PROD2.undo_tablespace='UNDOTBS2'

===========================// initPROD1.ora //========================
Steps:
=================

1) Take RMAN backup from source side and transfer to target side. (make sure controlfile autobackup is on)
2) create pfile in source side and move to target side.
3) Create required directory structures in ASM for C.R.D. files.
4) Change required parameters in pfile( audit, controlfiles, FRA etc,)
5) Be sure of following changes:
*.cluster_database=false #( default true)
*.audit_file_dest='/oracle/app/oracle/admin/PROD/adump'
*.control_files='+DATA/PROD/controlfile/current.272.759561861','+FRA/PROD/controlfile/current.388.759561863'
*.remote_listener='test-cluster-scan:1521'
*.diagnostic_dest='/oracle/app/oracle'

etc.

6) Stop cluster services in node-2 ...(stop crs in node 2)
7) start with nomount
SQL> create spfile from pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD1.ora';
File created.
SQL> startup nomount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
.....
SQL> quit
bash-4.2$ rman target /

RMAN> set DBID=52099846; (Collect from source database)

executing command: SET DBID
RMAN> restore controlfile from '/oradb/PROD_bkp/c-62083845-20131129-01';
RMAN> startup mount;
RMAN> catalog start with '/oradb/PROD_bkp'; ( Path where all backup pieces are kept to restore)

-- run your switch datafile script.( Collect from source database)

RMAN> RUN
{
allocate channel ch1 type disk;
SET NEWNAME FOR DATAFILE 1 TO '+DATA/PROD/datafile/system.278.769561767';
SET NEWNAME FOR DATAFILE 2 TO '+DATA/PROD/datafile/sysaux.279.769561767';

....
}
RMAN>

8)  Recover database
RMAN> catalog start with '/oradb/PROD_bkp/archivelogs';
 ( Path where all archive logs are kept to recover)

SQL> recover database ;

9) Open with upgrade / follow the upgrade process
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> alter database open resetlogs upgrade;
( if not work), then
SQL> shut immediate;
SQL> startup upgrade;

-- download following file from oracle.com and runn immediate after startup upgrade.
-- utlu112i_3.sql

SQL> @utlu112i_3.sql
SQL> @catupgrd.sql
SQL> shut immediate;
SQL> startup;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
...
Database mounted.
Database opened.

SQL> @utlu112s.sql
SQL> @?/rdbms/admin/catuppst.sql
SQL> @?/rdbms/admin/utlrp.sql

SQL> select count(1) from dba_objects where status='INVALID';

Note: Collect from source and target databases. Target database Valid object counts should be equal or more or invalid object counts of target database should be same or lower.

10) create common spfile:

do the changes in pfile(add) :

SPFILE='+DATA/PROD/spfilePROD.ora'
*.cluster_database=true

11) SQL> shut immediate;
12) Create parameter file in ASM
SQL> create spfile='+DATA/PROD/spfilePROD.ora' from pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD1.ora';

File created.

create pfile in node 2 in $ORACLE_HOME/dbs location and keep only below parameter
$cd $ORACLE_HOME/dbs
$ cat initPROD2.ora
SPFILE='+DATA/PROD/spfilePROD.ora'

13) Try to startup
SQL> startup;
....
Database mounted.
Database opened.
SQL>
SQL> show parameter spfile;
NAME           TYPE        VALUE
------------- ----------- -------------------
spfile         string      +DATA/PROD/spfilePROD.ora

14) start crs services in Node-2, goto root and start
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
# export ORACLE_HOME=/oracle/app/11.2.0/grid
# export PATH=$ORACLE_HOME/bin:$PATH

# ./crsctl start crs
# crsctl check crs

CRS-4638: Oracle High Availability Services is online
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4529: Cluster Synchronization Services is online
CRS-4534: Cannot communicate with Event Manager

( wait it will take time to start all services as 'online')

# crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

15) add database and instance to cluster

$ srvctl add database -d PROD -o /oracle/app/oracle/product/11.2.0/dbhome_1 ( oracle home)
$ srvctl config database -d PROD
$ srvctl add instance -d PROD -i PROD1 -n testclient1  
( testclient1 is the hostname of node-1)
$ srvctl add instance -d PROD -i PROD2 -n testclient2
$ srvctl add service -d PROD -s node1.db -r PROD1   
( Use hostname in case of node1)
$ srvctl add service -d PROD -s node2.db -r PROD2
$ srvctl start database -d PROD

Now confirm:

$ srvctl status database -d PROD
Instance PROD1 is running on node testclient1
Instance PROD2 is running on node testclient2

16) Do the following activities
a)create password file in both node
go dbs location
$ orapwd force=y file=orapwPROD1 password=sys$
$ orapwd force=y file=orapwPROD2 password=sys$

b) add in host file of client and collect scan IP(s)
$ cat /etc/hosts
test-cluster-scan  ( scan IP hostname )

c) Gather schema stats & Dictionary stats
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME => SC.USERNAME);
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS:

17) Final verification of all RAC services

# crsctl stat res -t
--------------------------------------------------------------------------------
NAME      TARGET  STATE        SERVER      STATE_DETAILS     
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.CRS.dg
               ONLINE  ONLINE       testclient1                                
               ONLINE  ONLINE       testclient2                                
ora.DATA.dg
               ONLINE  ONLINE       testclient1                                
               ONLINE  ONLINE       testclient2                                
ora.FRA.dg
               ONLINE  ONLINE       testclient1                                
               ONLINE  ONLINE       testclient2                                
ora.LISTENER.lsnr
               ONLINE  ONLINE       testclient1                                
               ONLINE  ONLINE       testclient2                                
ora.asm
               ONLINE  ONLINE       testclient1              Started           
               ONLINE  ONLINE       testclient2              Started           
ora.gsd
               OFFLINE OFFLINE      testclient1                                
               OFFLINE OFFLINE      testclient2                                
ora.net1.network
               ONLINE  ONLINE       testclient1                                
               ONLINE  ONLINE       testclient2                                
ora.ons
               ONLINE  ONLINE       testclient1                                
               ONLINE  ONLINE       testclient2                                
ora.registry.acfs
               ONLINE  UNKNOWN      testclient1                                
               ONLINE  ONLINE       testclient2                                
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       testclient1                                
ora.PROD.db
      1        ONLINE  ONLINE       testclient1              Open              
      2        ONLINE  ONLINE       testclient2              Open              
ora.PROD.node1.db.svc
      1        ONLINE  ONLINE       testclient1                                
ora.PROD.node2.db.svc
      1        ONLINE  ONLINE       testclient2                                
ora.cvu
      1        ONLINE  ONLINE       testclient1                                
ora.oc4j
      1        ONLINE  ONLINE       testclient1                                
ora.scan1.vip
      1        ONLINE  ONLINE       testclient1                                
ora.testclient1.vip
      1        ONLINE  ONLINE       testclient1                                
ora.testclient2.vip
      1        ONLINE  ONLINE       testclient2     

-- One sample example when I upgraded from 11.2.0.3 to 11.2.0.4
-- Ugrading catalog and dba registry

SQL> startup upgrade;
ORACLE instance started.
.....
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
PROD     READ WRITE

SQL>

-- Now see the dba registry values of all database components:

set line 120;
column COMP_NAME format a35;
column VERSION format a10;
column status format a10;

select COMP_NAME, VERSION,status from dba_registry;

COMP_NAME                           VERSION    STATUS
----------------------------------- ---------- ----------
Oracle Enterprise Manager           11.2.0.3.0 VALID
Oracle XML Database                 11.2.0.3.0 VALID
Oracle Text                         11.2.0.3.0 VALID
Oracle Expression Filter            11.2.0.3.0 VALID
Oracle Rules Manager                11.2.0.3.0 VALID
Oracle Workspace Manager            11.2.0.3.0 VALID
Oracle Database Catalog Views       11.2.0.3.0 VALID
Oracle Database Packages and Types  11.2.0.3.0 VALID
JServer JAVA Virtual Machine        11.2.0.3.0 VALID
Oracle XDK                          11.2.0.3.0 VALID
Oracle Database Java Packages       11.2.0.3.0 VALID
Oracle Real Application Clusters    11.2.0.3.0 VALID

12 rows selected.

Note: Here database components are not upgraded.

-- Upgrade the catalog

SQL>@?/rdbms/admin/catupgrd.sql

Note: After catalog upgrade database will be down automatically. Again do startup upgrade.

SQL>startup upgrade;

-- Now see the version of database components:

SQL> startup upgrade;
ORACLE instance started.
......
Database mounted.
Database opened.
SQL>

SQL> set line 120;
column COMP_NAME format a35;
column VERSION format a10;
column status format a10;

select COMP_NAME, VERSION,status from dba_registry;

COMP_NAME                           VERSION    STATUS
----------------------------------- ---------- ----------
Oracle Enterprise Manager           11.2.0.4.0 VALID
Oracle XML Database                 11.2.0.4.0 VALID
Oracle Text                         11.2.0.4.0 VALID
Oracle Expression Filter            11.2.0.4.0 VALID
Oracle Rules Manager                11.2.0.4.0 VALID
Oracle Workspace Manager            11.2.0.4.0 VALID
Oracle Database Catalog Views       11.2.0.4.0 VALID
Oracle Database Packages and Types  11.2.0.4.0 VALID
JServer JAVA Virtual Machine        11.2.0.4.0 VALID
Oracle XDK                          11.2.0.4.0 VALID
Oracle Database Java Packages       11.2.0.4.0 VALID

COMP_NAME                           VERSION    STATUS
----------------------------------- ---------- ----------
Oracle Real Application Clusters    11.2.0.4.0 VALID

12 rows selected.

A common issue:

After restore and recover database in two node RAC, node2 instance failed to start with below error message:

Example:

$ srvctl start database -d PROD
PRCR-1079 : Failed to start resource ora.prod.db
CRS-5017: The resource action "ora.prod.db start" encountered the following error:
ORA-01078: failure in processing system parameters
LRM-00123: invalid character 0 found in the input file
. For details refer to "(:CLSN00107:)" in "/u01/app/11.2.0/grid/log/racdb5/agent/crsd/oraagent_oracle//oraagent_oracle.log".

CRS-2674: Start of 'ora.ehishyd.db' on 'racdb5' failed
CRS-2632: There are no more servers to try to place resource 'ora.ehishyd.db' on that would satisfy its placement policy

$ srvctl status database -d PROD
Instance PROD2 is not running on node racdb5
Instance PROD1 is running on node racdb6
$

I verified and found spfile is in ASM and it is common to all nodes. But local parameter file not set properly in node 2. I changed like below and started node2 instance and started also.

Change :

$vi initPROD2.ora

SPFILE='+DATA/PROD/spfilePROD.ora' 

     

Hope this document will help more
Hope this document will help. Don't forget to post comments....