ORA-01502 index or partition of such index is in unusable state
ERROR:-
-----
ORA-01502: index "owner.index_name" or partition of such index is in unusable state
SOLUTION:-
--------
REBUILD UNUSABLE INDEXES in Oracle Database
$sqlplus "/as sysdba"
select owner,index_name,table_name,status from dba_indexes where index_name='&index_name';
OWNER INDEX_NAME TABLE_NAME STATUS
----- ---------- ---------- ------
owner index_name table_name INVALID
select owner,segment_name,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where segment_name='&segment_name';
OWNER SEGMENT_NAME SIZE IN MB
----- ------------ ----------
owner index_name 3000
alter session set current_schema='&schema_name';
Session altered.
alter index <index_name> rebuild;
Index altered.
select owner,index_name,table_name,status from dba_indexes where index_name='&index_name';
OWNER INDEX_NAME TABLE_NAME STATUS
----- ---------- ---------- ------
owner index_name table_name VALID
select owner,segment_name,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where segment_name='&segment_name';
OWNER SEGMENT_NAME SIZE IN MB
----- ------------ ----------
owner index_name 800
------------------------------------ OR ------------------------------------------------------
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';
This will output statements for all "unusable" indexes. Run them, so that the indexes can be "usable" again.
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) - dependencies exist
ISSUE:
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) - dependencies exist
Restore TABLE backup
Schema Status
-------------
select username from dba_users where username=upper('&username');
Table Status
------------
select count(*) from SCHEMANAME.TABLENAME;
TRUNCATE TABLE
---------------------------
spool TARGET_TABLENAME_TRUNCATE.log
set echo on term on feed on timing on
truncate table SCHEMANAME.TABLENAME;
truncate table SCHEMANAME.TABLENAME
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where TABLE_NAME='&TABLE_NAME';
CONSTRAINT_NAME C TABLE_NAME STATUS
------------------------------ - ------------------------------ -------
SYS_CONSTRAINT1 C TABLENAME ENABLED
PK_TABLENAME P TABLENAME ENABLED
alter table SCHEMANAME.TABLENAME DISABLE constraint SYS_CONSTRAINT1;
alter table SCHEMANAME.TABLENAME DISABLE constraint PK_TABLENAME;
ERROR at line 1:
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) -
dependencies exist
SOLUTION:
alter table SCHEMANAME.TABLENAME DISABLE constraint PK_TABLENAME cascade;
Table altered.
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where TABLE_NAME='TABLE_NAME';
1008 TRACE file
ISSUE:-
-----
Below error received when trying to run the code having bind variables.
But,same code works correctly in other datadases maintained at same version.
Oracle Database Version: 11.2.0.2.0 - 64 bit (SQL>SELECT BANNER FROM v$version;)
PL/SQL : 11.2.0.2.0
OS Version : Linux x86_64 ($uname -ms)
OS Version
----------
$uname -ms
Linux x86_64
ERROR:
-----
ERROR at line 1:
ORA-01008:not all variables bound
ORA-06512:at line 77
Running in to Bug 14458214 (5/rdbms/partitioning) Unexpected ORA-01008 from select on composite partitioned table.
The workaround mentioned in Bug 14458214 is as follows:
Try executing this from the sqlplus session before you execute the anonymous block.
alter session set "_and_pruning_enabled"=false;
alter session set "_subquery_pruning_enabled"=false;
alter session set "_optimizer_table_expansion"=false;
--execute the anonymous PL/SQL block which fails, here
How to Generate 1008 TRACE file from Oracle Database
----------------------------------------------------
Set an 1008 ERRORSTACK event to confirm the stack.
Database Details
----------------
sqlplus "/as sysdba"
set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;
Generate 1008 TRACE file at session level
-----------------------------------------
alter session set timed_statistics=true;
alter session set statistics_level=all;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='TEST1008';
alter session set events '1008 trace name ERRORSTACK level 4';
--execute the statement which fails, here
alter session set events '1008 trace name context off';
Trace File Location
-------------------
set pages 50000 lines 32767
col NAME for a30
col VALUE for a100
select * from v$diag_info where name like '%Diag Trace'; -------------- 11G
INST_ID NAME VALUE
------- ---------- ---------
Diag Trace /trace/file/location/
exit
Listing Trace Files
-------------------
cd /trace/file/location/
ls -lrt | grep -i TEST1008
=====================================================================================================================================================
Error 1031 received logging on to the standby
ISSUE
Error 1031 received logging on to the standby
ORA-01031: insufficient privileges
PING[ARC0]: Heartbeat failed to connect to standby 'dgp'. Error is 1031.
PRIMARY
=======
Primary database Alert log error:
--------------------------------
Error 1031 received logging on to the standby
ORA-01031: insufficient privileges
PING[ARC0]: Heartbeat failed to connect to standby 'dgp'. Error is 1031.
Chech DR sync and see the difference.
THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------
set pages 999 lines 999
col MESSAGE for a100
select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message||severity from gv$dataguard_status where severity in ('Error','Fatal') order by
timestamp;
show parameter log_archive_dest_state_2;
LISTNER VERIFICATION FROM PRIMATY DB
------------------------------------
select dest_id,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
FIND GAP
--------
select thread#,low_sequence#,high_sequence# from gv$archive_log;
ps -ef |grep tns
lsnrctl status
DR
==
DR database Alert log error:
----------------------------
Error 1031 received logging on to the standby
set pages 999 lines 999
col MESSAGE for a100
select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message||severity from gv$dataguard_status where severity in ('Error','Fatal') order by
timestamp;
select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby;
PROCESS STATUS
------- ------------
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 WAIT_FOR_LOG
checking log transfer and apply
-------------------------------
SELECT SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED FROM gV$ARCHIVED_LOG ORDER BY SEQUENCE#;
select count(*) from GV$ARCHIVED_LOG where applied='NO';
Redo transfer was not happening. When we checked in the
v$managed_process data dictionary view, we could see that RFS was not starting.
Here,
ora-01031 usually appears when some sysdba session failes to authenticate
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
1.The RFS process was not started in the standby which indicates that standby is not receiving any redo information from the primary.
2.In the primary alert log file we could see the errors indicating that the primary is not able to communicate with the standby instance. The error
observed was "Error 1031 received logging on to the standby".
3.The time stamp of the password file on the primary and the standby was different. This indicated the possibility of having the wrong password file
in the standby.
SOLUTION:
=========
For the redo transfer to take place efficiently, the password file on standby should be a copy from the primary and renamed standby.We can use v
$pwd_file_users data dictionary view to check if the password file is used
1.copy the password file from the primary to the standby and renamed the password file in the following format ie orapw<sid> .
2.Restar the media recovery process on the standby.
Once the above steps are completed we could see that redo shipping and redo apply is taking place.
Copy the latest Password file from available PRIMARY Node to rest of PRIMARY and STANDBY nodes:
----------------------------------------------------------------------------------------------
Primary (Node 1)
----------------
cd $ORACLE_HOME/dbs
ls -lrt
scp orapw<sid> oracle@PROD_NODE_2_hostname:/oracle/home/dbs
scp orapw<sid> oracle@DR_NODE_1_hostname:/oracle/home/dbs
scp orapw<sid> oracle@DR_NODE_2_hostname:/oracle/home/dbs
or
DR
==
select * from gv$pwfile_users;
we have to check sec_case_sensitive_logon parameter on primary and standby.
SQL> show parameter sec_case_sensitive_logon;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL>
We have to recreate the passwd file or copy Primary server to Standby server.
In cause sec_case_sensitive_logon parameter value is true, we have to use below orapwd command.
orapwd file=$ORACLE_HOME/dbs/orapwPROD password=password123 entries=10 ignorecase=y
In cause sec_case_sensitive_logon parameter value is false, we have to use below orapwd command.
orapwd file=$ORACLE_HOME/dbs/orapwPROD password=password123 entries=10
DR
==
cancelling mrp process:
alter database recover managed standby database cancel;
starting mrp process:
alter database recover managed standby database disconnect from session;
select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby;
Chech DR sync and see the difference.
Source : Internet
How to Find Locks on Objects
ISSUE:
-----
ORA-00054: resource busy and acquire with NOWAIT specified
Locked sessions on Oracle Database Objects :-
-----------------------------------------------
Query 1:
-------
set pages 50000 lines 32767
SELECT s.inst_id,s.sid || ',' || s.serial# sess_id,
oracle_username || ' (' || s.osuser || ')' os_username,
owner || '.' || object_name,object_type,object_id,
DECODE (l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS,
DECODE (v.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lmode)) LOCK_MODE,
a.sql_text
FROM gv$locked_object v,
dba_objects d,
gv$lock l,
gv$session s
gv$sqlarea a
WHERE v.object_id = d.object_id
AND v.object_id = l.id1
AND v.session_id = s.sid
ORDER BY oracle_username, session_id;
Query 2: (More Information)
-------
set pages 50000 lines 32767
select
vlo.object_id, vlo.session_id, vlo.oracle_username, vlo.process
, DECODE(vlo.LOCKED_MODE,
0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE', NULL) LOCK_MODE,
do.owner, do.object_name, do.object_type
, vs.saddr, vs.serial#, vs.paddr, vs.username, vs.ownerid, vs.status, vs.server, vs.schemaname, vs.osuser, vs.machine, vs.program, vs.type,
vs.logon_time, vs.last_call_et, vs.blocking_session_status,
vs.event#, vs.event, vs.wait_class#, vs.wait_class, vs.wait_time, vs.seconds_in_wait, vs.state
from gv$locked_object vlo
inner join dba_objects do on (vlo.object_id = do.object_id)
left outer join gv$session vs on (vlo.session_id = vs.sid);
Query 3:
-------
set pages 50000 lines 32767
SELECT
b.inst_id,a.session_id,b.serial#,b.STATUS,b.machine,a.ORACLE_USERNAME,a.OS_USER_NAME,a.LOCKED_MODE,
c.owner,c.object_name,c.object_type,c.object_id,d.sql_text
FROM
gv$locked_object a,
gv$session b,
dba_objects c,
gv$sqlarea d
WHERE b.sid = a.session_id
AND a.object_id = c.object_id
ORDER BY a.oracle_username, a.session_id;
Query 4:
-------
set pages 50000 lines 32767
SELECT s.inst_id,OS_USER_NAME, ORACLE_USERNAME, s.sid, o.object_name,o.object_type, s.serial#, a.sql_text
FROM gv$locked_object l, dba_objects o, gv$session s, gv$sqlarea a
WHERE l.object_id = o.object_id
AND s.SQL_ADDRESS = a.address
AND l.SESSION_ID = s.sid;
Command to kill the session:
---------------------------
ALTER SYSTEM KILL SESSION 'sid, serial#';
ALTER SYSTEM KILL SESSION 'sid, serial#,@<instance_id>'; (RAC)
Script to Kill all the locks
----------------------------
set pages 50000 lines 32767
SELECT 'ALTER SYSTEM KILL SESSION "'||TO_CHAR(s.sid)||','||TO_CHAR(s.serial#)||"';'
AS "Statement to kill"
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.SESSION_ID = s.sid;
=====================================================================================================================================================
ORA-00257: archiver error. Connect internal only, until freed.
ISSUE:
ORA-00257: archiver error. Connect internal only, until freed
ARCHIVE DESTINATION FULL ORA-00257
ps -ef|grep pmon
ORACLE_SID=`ps -ef | grep asm_smon | grep -v 'grep' | grep -v 'sed' | awk '{printf $8}' | awk 'BEGIN{FS="_"} {printf $3}'`
ASM Tablespace Utilization Scripts
http://select-star-from.blogspot.in/2013/03/asm-tablespace-utilization-scripts.html
show parameter recovery;
1. DB_RECOVERY_FILE_DEST_SIZE (Specifies max space to use for FRA)
2. DB_RECOVERY_FILE_DEST (Location of FRA)
The DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.
col name format a40
select
name,
to_char(space_limit, '999,999,999,999') as space_limit,
to_char(space_limit - space_used + space_reclaimable,
'999,999,999,999') as space_available,
round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full
from
v$recovery_file_dest;
select * from V$FLASH_RECOVERY_AREA_USAGE;(see what kind of files are available in the Flash Recovery Area)
select * from V$RECOVERY_FILE_DEST; (determine actual values)
ALTER SYSTEM SET db_recovery_file_dest_size=10G scope=both;
ALTER SYSTEM SET db_recovery_file_dest='/oradata/FRA';
For example, If FRA is in an Automatic Storage Management (ASM) disk group
ALTER SYSTEM SET db_recovery_file_dest='+FLASH’ scope=both;
RAC
ALTER SYSTEM set db_recovery_file_dest_size=60G scope=both sid='*' ;
ALTER SYSTEM SET db_recovery_file_dest='+FLASH' sid='*';
In a RAC database, all instances must have the same values for these parameters. Even though there are multiple nodes they all share the same
controlfiles.
Here,if there is no space available. DBA can take the archive backup to free the space.
ls -ltr *.cmd
nohup rman target / cmdfile=archivebackup.cmd log=archivebackup_dbname_DDMONYY.log &
nohup: appending output to `nohup.out'
tail -f archivebackup_dbname_DDMONYY.log
archivebackup.cmd
-----------------
run {
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-1' BACKED UP 1 TIMES TO SBT_TAPE;
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
BACKUP ARCHIVELOG ALL FILESPERSET 10 DELETE INPUT;
}
ps -ef| grep rman
col name format a40
select
name,
to_char(space_limit, '999,999,999,999') as space_limit,
to_char(space_limit - space_used + space_reclaimable,
'999,999,999,999') as space_available,
round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full
from
v$recovery_file_dest;
select * from V$FLASH_RECOVERY_AREA_USAGE;(see what kind of files are available in the Flash Recovery Area)
select * from V$RECOVERY_FILE_DEST; (determine actual values)
NOTE:
In order to solve the above error the solutions are
1) Increase the free space where archiver archives the archivelog. The location where archiver archives the log is determined by parameter file pfile
or spfile.
This can be determined by loging into sqlplus and issuing
SQL> show parameter log_archive_dest
2) In case it is not possible to increase free space at the same location but if free space is available at other location then the parameter
log_archive_dest (or log_archive_dest_1 in some cases) can be changed so that the new archives are produced at new location specified which has free
space.
this can be done by modify init.ora file or using alter system if spfile is present
SQL> alter system set log_archive_dest_1=’
3) We can use following steps for this
1.find the location of Archive destination by
show parameter archive_dest
lets say it provide LOCATION=/u10/oradata/mydb/arch
2.move some files to some other location using os command
cd /u10/oradata/mydb/arch
mv /u10/oradata/mydb/arch/* /u11/oradata/mydb/arch-bkp/
4) The option which is often used is to take a backup of the archives from the existing place and delete those archives from that place so that new
archives can generated at that place .
the backup can be OS level backup and OS level del;etion but the recommended method which is compulsory to be used with ASM in place is taking any
RMAN backup and delete using RMAN. as shown
rman target sys/sys
RMAN> backup archive log all device type disk format ‘/oracle/arch_%U’;
RMAN> delete archive until time ‘trunc(sysdate)’;
This will delete all the archive logs until today and space will freed and the archiver will start archiving redo logs
------------------------------------------------------------------------------------------------------------
The views v$recovery_file_dest and v$flash_recovery_area_usage does not always give the true picture of exact space used due to BUG Bug 4911954 in
Oracle 10g and the versions which is confirmed to be affected is 10.2.0.2. (Reference Metalink Doc Id 4911954.8 ).
V$FLASH_RECOVERY_AREA_USAGE provides information about the flash recovery area disk space usage. Following is its main columns:
FILE_TYPE - the type of the file and can have any of the following values:
controlfile, onlinelog, archivelog, backuppiece, imagecopy, flashbacklog
PERCENT_SPACE_USED - This represents the disk space used by the file type, in percentage.
PERCENT_SPACE_RECLAIMABLE - this represents the percentage of disk space reclaimable from the file type after deleting any obsolete or redundant
files, and files backed up to a tertiary device.
OTN Notes that you can see the actual space used by joining into v$recovery_file_dest:
A new view, V$FLASH_RECOVERY_AREA_USAGE, shows what's available in the flashback area.
ERROR:-
-----
ORA-01502: index "owner.index_name" or partition of such index is in unusable state
SOLUTION:-
--------
REBUILD UNUSABLE INDEXES in Oracle Database
$sqlplus "/as sysdba"
select owner,index_name,table_name,status from dba_indexes where index_name='&index_name';
OWNER INDEX_NAME TABLE_NAME STATUS
----- ---------- ---------- ------
owner index_name table_name INVALID
select owner,segment_name,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where segment_name='&segment_name';
OWNER SEGMENT_NAME SIZE IN MB
----- ------------ ----------
owner index_name 3000
alter session set current_schema='&schema_name';
Session altered.
alter index <index_name> rebuild;
Index altered.
select owner,index_name,table_name,status from dba_indexes where index_name='&index_name';
OWNER INDEX_NAME TABLE_NAME STATUS
----- ---------- ---------- ------
owner index_name table_name VALID
select owner,segment_name,sum(bytes)/1024/1024 "SIZE in MB" from dba_segments where segment_name='&segment_name';
OWNER SEGMENT_NAME SIZE IN MB
----- ------------ ----------
owner index_name 800
------------------------------------ OR ------------------------------------------------------
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';
This will output statements for all "unusable" indexes. Run them, so that the indexes can be "usable" again.
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) - dependencies exist
ISSUE:
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) - dependencies exist
Restore TABLE backup
Schema Status
-------------
select username from dba_users where username=upper('&username');
Table Status
------------
select count(*) from SCHEMANAME.TABLENAME;
TRUNCATE TABLE
---------------------------
spool TARGET_TABLENAME_TRUNCATE.log
set echo on term on feed on timing on
truncate table SCHEMANAME.TABLENAME;
truncate table SCHEMANAME.TABLENAME
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where TABLE_NAME='&TABLE_NAME';
CONSTRAINT_NAME C TABLE_NAME STATUS
------------------------------ - ------------------------------ -------
SYS_CONSTRAINT1 C TABLENAME ENABLED
PK_TABLENAME P TABLENAME ENABLED
alter table SCHEMANAME.TABLENAME DISABLE constraint SYS_CONSTRAINT1;
alter table SCHEMANAME.TABLENAME DISABLE constraint PK_TABLENAME;
ERROR at line 1:
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) -
dependencies exist
SOLUTION:
alter table SCHEMANAME.TABLENAME DISABLE constraint PK_TABLENAME cascade;
Table altered.
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where TABLE_NAME='TABLE_NAME';
1008 TRACE file
ISSUE:-
-----
Below error received when trying to run the code having bind variables.
But,same code works correctly in other datadases maintained at same version.
Oracle Database Version: 11.2.0.2.0 - 64 bit (SQL>SELECT BANNER FROM v$version;)
PL/SQL : 11.2.0.2.0
OS Version : Linux x86_64 ($uname -ms)
OS Version
----------
$uname -ms
Linux x86_64
ERROR:
-----
ERROR at line 1:
ORA-01008:not all variables bound
ORA-06512:at line 77
Running in to Bug 14458214 (5/rdbms/partitioning) Unexpected ORA-01008 from select on composite partitioned table.
The workaround mentioned in Bug 14458214 is as follows:
Try executing this from the sqlplus session before you execute the anonymous block.
alter session set "_and_pruning_enabled"=false;
alter session set "_subquery_pruning_enabled"=false;
alter session set "_optimizer_table_expansion"=false;
--execute the anonymous PL/SQL block which fails, here
How to Generate 1008 TRACE file from Oracle Database
----------------------------------------------------
Set an 1008 ERRORSTACK event to confirm the stack.
Database Details
----------------
sqlplus "/as sysdba"
set pages 50000 lines 32767
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME, INSTANCE_NAME, HOST_NAME, DATABASE_ROLE,
OPEN_MODE, version DB_VERSION, LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"
from v$database,gv$instance;
Generate 1008 TRACE file at session level
-----------------------------------------
alter session set timed_statistics=true;
alter session set statistics_level=all;
alter session set max_dump_file_size=unlimited;
alter session set tracefile_identifier='TEST1008';
alter session set events '1008 trace name ERRORSTACK level 4';
--execute the statement which fails, here
alter session set events '1008 trace name context off';
Trace File Location
-------------------
set pages 50000 lines 32767
col NAME for a30
col VALUE for a100
select * from v$diag_info where name like '%Diag Trace'; -------------- 11G
INST_ID NAME VALUE
------- ---------- ---------
Diag Trace /trace/file/location/
exit
Listing Trace Files
-------------------
cd /trace/file/location/
ls -lrt | grep -i TEST1008
=====================================================================================================================================================
Error 1031 received logging on to the standby
ISSUE
Error 1031 received logging on to the standby
ORA-01031: insufficient privileges
PING[ARC0]: Heartbeat failed to connect to standby 'dgp'. Error is 1031.
PRIMARY
=======
Primary database Alert log error:
--------------------------------
Error 1031 received logging on to the standby
ORA-01031: insufficient privileges
PING[ARC0]: Heartbeat failed to connect to standby 'dgp'. Error is 1031.
Chech DR sync and see the difference.
THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
------ ----------- ------------- ------------ ------------------------ -------------------------
set pages 999 lines 999
col MESSAGE for a100
select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message||severity from gv$dataguard_status where severity in ('Error','Fatal') order by
timestamp;
show parameter log_archive_dest_state_2;
LISTNER VERIFICATION FROM PRIMATY DB
------------------------------------
select dest_id,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
FIND GAP
--------
select thread#,low_sequence#,high_sequence# from gv$archive_log;
ps -ef |grep tns
lsnrctl status
DR
==
DR database Alert log error:
----------------------------
Error 1031 received logging on to the standby
set pages 999 lines 999
col MESSAGE for a100
select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message||severity from gv$dataguard_status where severity in ('Error','Fatal') order by
timestamp;
select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby;
PROCESS STATUS
------- ------------
RFS IDLE
RFS IDLE
RFS IDLE
MRP0 WAIT_FOR_LOG
checking log transfer and apply
-------------------------------
SELECT SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED FROM gV$ARCHIVED_LOG ORDER BY SEQUENCE#;
select count(*) from GV$ARCHIVED_LOG where applied='NO';
Redo transfer was not happening. When we checked in the
v$managed_process data dictionary view, we could see that RFS was not starting.
Here,
ora-01031 usually appears when some sysdba session failes to authenticate
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
1.The RFS process was not started in the standby which indicates that standby is not receiving any redo information from the primary.
2.In the primary alert log file we could see the errors indicating that the primary is not able to communicate with the standby instance. The error
observed was "Error 1031 received logging on to the standby".
3.The time stamp of the password file on the primary and the standby was different. This indicated the possibility of having the wrong password file
in the standby.
SOLUTION:
=========
For the redo transfer to take place efficiently, the password file on standby should be a copy from the primary and renamed standby.We can use v
$pwd_file_users data dictionary view to check if the password file is used
1.copy the password file from the primary to the standby and renamed the password file in the following format ie orapw<sid> .
2.Restar the media recovery process on the standby.
Once the above steps are completed we could see that redo shipping and redo apply is taking place.
Copy the latest Password file from available PRIMARY Node to rest of PRIMARY and STANDBY nodes:
----------------------------------------------------------------------------------------------
Primary (Node 1)
----------------
cd $ORACLE_HOME/dbs
ls -lrt
scp orapw<sid> oracle@PROD_NODE_2_hostname:/oracle/home/dbs
scp orapw<sid> oracle@DR_NODE_1_hostname:/oracle/home/dbs
scp orapw<sid> oracle@DR_NODE_2_hostname:/oracle/home/dbs
or
DR
==
select * from gv$pwfile_users;
we have to check sec_case_sensitive_logon parameter on primary and standby.
SQL> show parameter sec_case_sensitive_logon;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean FALSE
SQL>
We have to recreate the passwd file or copy Primary server to Standby server.
In cause sec_case_sensitive_logon parameter value is true, we have to use below orapwd command.
orapwd file=$ORACLE_HOME/dbs/orapwPROD password=password123 entries=10 ignorecase=y
In cause sec_case_sensitive_logon parameter value is false, we have to use below orapwd command.
orapwd file=$ORACLE_HOME/dbs/orapwPROD password=password123 entries=10
DR
==
cancelling mrp process:
alter database recover managed standby database cancel;
starting mrp process:
alter database recover managed standby database disconnect from session;
select inst_id,process,status,thread#,sequence#,block#,blocks from gv$managed_standby;
Chech DR sync and see the difference.
Source : Internet
How to Find Locks on Objects
ISSUE:
-----
ORA-00054: resource busy and acquire with NOWAIT specified
Locked sessions on Oracle Database Objects :-
-----------------------------------------------
Query 1:
-------
set pages 50000 lines 32767
SELECT s.inst_id,s.sid || ',' || s.serial# sess_id,
oracle_username || ' (' || s.osuser || ')' os_username,
owner || '.' || object_name,object_type,object_id,
DECODE (l.block, 0, 'Not Blocking', 1, 'Blocking', 2, 'Global') STATUS,
DECODE (v.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR (lmode)) LOCK_MODE,
a.sql_text
FROM gv$locked_object v,
dba_objects d,
gv$lock l,
gv$session s
gv$sqlarea a
WHERE v.object_id = d.object_id
AND v.object_id = l.id1
AND v.session_id = s.sid
ORDER BY oracle_username, session_id;
Query 2: (More Information)
-------
set pages 50000 lines 32767
select
vlo.object_id, vlo.session_id, vlo.oracle_username, vlo.process
, DECODE(vlo.LOCKED_MODE,
0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE', NULL) LOCK_MODE,
do.owner, do.object_name, do.object_type
, vs.saddr, vs.serial#, vs.paddr, vs.username, vs.ownerid, vs.status, vs.server, vs.schemaname, vs.osuser, vs.machine, vs.program, vs.type,
vs.logon_time, vs.last_call_et, vs.blocking_session_status,
vs.event#, vs.event, vs.wait_class#, vs.wait_class, vs.wait_time, vs.seconds_in_wait, vs.state
from gv$locked_object vlo
inner join dba_objects do on (vlo.object_id = do.object_id)
left outer join gv$session vs on (vlo.session_id = vs.sid);
Query 3:
-------
set pages 50000 lines 32767
SELECT
b.inst_id,a.session_id,b.serial#,b.STATUS,b.machine,a.ORACLE_USERNAME,a.OS_USER_NAME,a.LOCKED_MODE,
c.owner,c.object_name,c.object_type,c.object_id,d.sql_text
FROM
gv$locked_object a,
gv$session b,
dba_objects c,
gv$sqlarea d
WHERE b.sid = a.session_id
AND a.object_id = c.object_id
ORDER BY a.oracle_username, a.session_id;
Query 4:
-------
set pages 50000 lines 32767
SELECT s.inst_id,OS_USER_NAME, ORACLE_USERNAME, s.sid, o.object_name,o.object_type, s.serial#, a.sql_text
FROM gv$locked_object l, dba_objects o, gv$session s, gv$sqlarea a
WHERE l.object_id = o.object_id
AND s.SQL_ADDRESS = a.address
AND l.SESSION_ID = s.sid;
Command to kill the session:
---------------------------
ALTER SYSTEM KILL SESSION 'sid, serial#';
ALTER SYSTEM KILL SESSION 'sid, serial#,@<instance_id>'; (RAC)
Script to Kill all the locks
----------------------------
set pages 50000 lines 32767
SELECT 'ALTER SYSTEM KILL SESSION "'||TO_CHAR(s.sid)||','||TO_CHAR(s.serial#)||"';'
AS "Statement to kill"
FROM gv$locked_object l, dba_objects o, gv$session s
WHERE l.object_id = o.object_id
AND l.SESSION_ID = s.sid;
=====================================================================================================================================================
ORA-00257: archiver error. Connect internal only, until freed.
ISSUE:
ORA-00257: archiver error. Connect internal only, until freed
ARCHIVE DESTINATION FULL ORA-00257
ps -ef|grep pmon
ORACLE_SID=`ps -ef | grep asm_smon | grep -v 'grep' | grep -v 'sed' | awk '{printf $8}' | awk 'BEGIN{FS="_"} {printf $3}'`
ASM Tablespace Utilization Scripts
http://select-star-from.blogspot.in/2013/03/asm-tablespace-utilization-scripts.html
show parameter recovery;
1. DB_RECOVERY_FILE_DEST_SIZE (Specifies max space to use for FRA)
2. DB_RECOVERY_FILE_DEST (Location of FRA)
The DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST.
col name format a40
select
name,
to_char(space_limit, '999,999,999,999') as space_limit,
to_char(space_limit - space_used + space_reclaimable,
'999,999,999,999') as space_available,
round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full
from
v$recovery_file_dest;
select * from V$FLASH_RECOVERY_AREA_USAGE;(see what kind of files are available in the Flash Recovery Area)
select * from V$RECOVERY_FILE_DEST; (determine actual values)
ALTER SYSTEM SET db_recovery_file_dest_size=10G scope=both;
ALTER SYSTEM SET db_recovery_file_dest='/oradata/FRA';
For example, If FRA is in an Automatic Storage Management (ASM) disk group
ALTER SYSTEM SET db_recovery_file_dest='+FLASH’ scope=both;
RAC
ALTER SYSTEM set db_recovery_file_dest_size=60G scope=both sid='*' ;
ALTER SYSTEM SET db_recovery_file_dest='+FLASH' sid='*';
In a RAC database, all instances must have the same values for these parameters. Even though there are multiple nodes they all share the same
controlfiles.
Here,if there is no space available. DBA can take the archive backup to free the space.
ls -ltr *.cmd
nohup rman target / cmdfile=archivebackup.cmd log=archivebackup_dbname_DDMONYY.log &
nohup: appending output to `nohup.out'
tail -f archivebackup_dbname_DDMONYY.log
archivebackup.cmd
-----------------
run {
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-1' BACKED UP 1 TIMES TO SBT_TAPE;
allocate channel ch1 type 'sbt_tape';
allocate channel ch2 type 'sbt_tape';
allocate channel ch3 type 'sbt_tape';
allocate channel ch4 type 'sbt_tape';
BACKUP ARCHIVELOG ALL FILESPERSET 10 DELETE INPUT;
}
ps -ef| grep rman
col name format a40
select
name,
to_char(space_limit, '999,999,999,999') as space_limit,
to_char(space_limit - space_used + space_reclaimable,
'999,999,999,999') as space_available,
round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full
from
v$recovery_file_dest;
select * from V$FLASH_RECOVERY_AREA_USAGE;(see what kind of files are available in the Flash Recovery Area)
select * from V$RECOVERY_FILE_DEST; (determine actual values)
NOTE:
In order to solve the above error the solutions are
1) Increase the free space where archiver archives the archivelog. The location where archiver archives the log is determined by parameter file pfile
or spfile.
This can be determined by loging into sqlplus and issuing
SQL> show parameter log_archive_dest
2) In case it is not possible to increase free space at the same location but if free space is available at other location then the parameter
log_archive_dest (or log_archive_dest_1 in some cases) can be changed so that the new archives are produced at new location specified which has free
space.
this can be done by modify init.ora file or using alter system if spfile is present
SQL> alter system set log_archive_dest_1=’
3) We can use following steps for this
1.find the location of Archive destination by
show parameter archive_dest
lets say it provide LOCATION=/u10/oradata/mydb/arch
2.move some files to some other location using os command
cd /u10/oradata/mydb/arch
mv /u10/oradata/mydb/arch/* /u11/oradata/mydb/arch-bkp/
4) The option which is often used is to take a backup of the archives from the existing place and delete those archives from that place so that new
archives can generated at that place .
the backup can be OS level backup and OS level del;etion but the recommended method which is compulsory to be used with ASM in place is taking any
RMAN backup and delete using RMAN. as shown
rman target sys/sys
RMAN> backup archive log all device type disk format ‘/oracle/arch_%U’;
RMAN> delete archive until time ‘trunc(sysdate)’;
This will delete all the archive logs until today and space will freed and the archiver will start archiving redo logs
------------------------------------------------------------------------------------------------------------
The views v$recovery_file_dest and v$flash_recovery_area_usage does not always give the true picture of exact space used due to BUG Bug 4911954 in
Oracle 10g and the versions which is confirmed to be affected is 10.2.0.2. (Reference Metalink Doc Id 4911954.8 ).
V$FLASH_RECOVERY_AREA_USAGE provides information about the flash recovery area disk space usage. Following is its main columns:
FILE_TYPE - the type of the file and can have any of the following values:
controlfile, onlinelog, archivelog, backuppiece, imagecopy, flashbacklog
PERCENT_SPACE_USED - This represents the disk space used by the file type, in percentage.
PERCENT_SPACE_RECLAIMABLE - this represents the percentage of disk space reclaimable from the file type after deleting any obsolete or redundant
files, and files backed up to a tertiary device.
OTN Notes that you can see the actual space used by joining into v$recovery_file_dest:
A new view, V$FLASH_RECOVERY_AREA_USAGE, shows what's available in the flashback area.
No comments:
Post a Comment