Month: September 2018

DataGuard ORA-07286 ORA-16055 ORA-19809 ORA-19804 ORA-19815

SYMPTOM

On primary database  side, ORA errors in alert log:

Sat Sep 22 03:56:48 2018
Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_arc4_20835.trc:
ORA-07286: sksagdi: cannot obtain device information.
Sat Sep 22 03:56:48 2018
ARC4: FAL archive failed with error 7286. See trace for details
Sat Sep 22 03:56:48 2018
Errors in file /u01/app/oracle/diag/rdbms/ractest/RACTEST1/trace/RACTEST1_arc4_20835.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing

On standby database side :

ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 536870912 bytes disk space from 214748364800 limit
ORA-19815: WARNING: db_recovery_file_dest_size of 214748364800 bytes
           is 100.00% used, and has 0 remaining bytes available.
Sat Sep 22 04:24:24 2018
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************

CAUSES

Standby database archive log retention policy is not configured properly.

RMAN> show all;
...
..
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
..

RESOLUTION

Change  the archive log retention policy on standby side.

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
Advertisement

Migrate Oracle to AWS PostgreSQL by Using AWS DMS

This post step by step explains how to migrate on-premise Oracle database 12c data to AWS PostgreSQL RDS by using AWS DMS ( Database Migration Service). Assume the schema has been migrated by using  the AWS Schema Conversion Tool (AWS SCT).

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Long Running Operations Monitoring ( V$SESSION_LONGOPS ) in Oracle Database

List operations remaining time > 10 minutes :

SQL>select 
           username,sid, opname, target, sofar, totalwork, units, 
           to_char(start_time,'YYYYMMDD-HH24:MI:SS') StartTime, 
           time_remaining, message  
      from 
           V$SESSION_LONGOPS 
      where 
           time_remaining>600;


USERNAME     SID    OPNAME     TARGET     SOFAR   TOTALWORK  UNITS    STARTTIME         TIME_REMAINING MESSAGE

------------ ------ ---------- ---------- ------- ---------- ------- ----------------- -------------- ---------------------------------------------------------
 TETSUSER    5704   Table Scan TETSUSER.TEST  22414  39677      Blocks    20180920-10:55:49        3676  Table Scan:  JAMES.TEST: 22414 out  of 39677 Blocks done

max_dump_file_size in 12c

MAX_DUMP_FILE_SIZE specifies the maximum size of trace files (excluding the alert log).

ModifiableALTER SESSION, ALTER SYSTEM
  • A numeric value for MAX_DUMP_FILE_SIZE specifies the maximum size in operating system blocks.
  • A numeric value followed by a K or M or G suffix specifies the file size in kilobytes, megabytes, or gigabytes.
  • The special value string UNLIMITED means that there is no upper limit on trace file size. Thus, dump files can be as large as the operating system permits.

The trace file can be split into a maximum of 5 segments, and the size of each segment will typically be 1/5th of the trace file limit.

Truncate a Table of Other User

In order to truncate a table of other user, the DROP ANY TABLE system privilege is required. Without granting this powerful privilege, instead, a procedure is created and granted to the user who can truncate other user’s tables.

In this example, user B is able to truncate table TEST of user A without needing “DROP ANY TABLE” system privilege.

1) In schema A, create a procedure called “truncate_tab”:

SQL>show user

USERA

SQL>create or replace procedure truncate_tab (tab_name IN varchar2) 
as 
begin 
   execute immediate 'truncate table '||tab_name ; 
end; 
/

2) Grant execute on procedure truncate_tab to user B:

SQL>show user

USERA

SQL>grant execute on a.truncate_tab to B;

Grant succeeded.

3) Truncate table A.TEST by user A:

SQL>show user

USERB

SQL> exec a.truncate_tab('TEST');

PL/SQL procedure successfully completed.