RMAN: Displaying current backup progress

To check the progress of your current RMAN backup use this script:

#This is my favorite script

select recid
 , output_device_type
 , dbsize_mbytes
 , input_bytes/1024/1024 input_mbytes
 , output_bytes/1024/1024 output_mbytes
 , (output_bytes/input_bytes*100) compression
 , (mbytes_processed/dbsize_mbytes*100) complete
 , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
 from v$rman_status rs
 , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) 
 where status='RUNNING'
 and output_device_type is not null

Alert log table x$dbgalertext

Prior to 11g you had to create external table to query alert log information.

In 11g Oracle introduced X$DBGALERTEXT, which is mapped to the alert log file located at  $ORACLE_BASE/diag/rdbms/{DB_UNIQUE_NAME}/{SID}/alert/log_XX.xml.

Note that table is located in SYS schema and you need a permission to see it’s content.

So, please keep in mind this table name, it is really useful to search errors in alert log.

The DBMS_SYSTEM Package (ksdwrt,set_sql_trace_in_session)

I have found a very useful package. DBMS_SYSTEM which has several functions, but I have choose two of them ksdwrt and SET_SQL_TRACE_IN_SESSION.

Story:

I had the following problem: I have configured EM alert on ORA- errors. But EM was not catching error ORA-20018. As you see error code is between [-20000]-[-20999], so it is user-defined error.
EM catches errors that are generated in alert.log. Not all errors are going to alert.log especially user defined errors.

So let’s introduce ksdwrt that helped me to solve this problem.

ksdwrt

Used to write messages to the alertlog and/or trace files.

EXEC DBMS_System.ksdwrt(n, message);
EXEC DBMS_System.ksdwrt(2, ‘My Test Alertlog Message’);

Where the value of “n” indicates the destination.

1 – Write to trace file.
2 – Write to alertlog.
3 – Write to both.

Anther usefull method:

SET_SQL_TRACE_IN_SESSION

Used to set trace on or off in another users session.

EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid, serial#, true );
EXEC DBMS_SYSTEM.set_sql_trace_in_session(31, 97, true );

The values for SID and SERIAL# can be found using the V$SESSION view.

End of the story:

I have run:

begin
dbms_system.ksdwrt(2,’ORA-20118: myerror’);
raise_application_error(-20118, ‘myerror’);
end;

And error was placed in alert.log. So EM was able to see it.

Source: https://oracle-base.com/articles/8i/dbms_system

ORA-01184: logfile group 5 already exists

Command:  

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 11 (‘+DATA’) size 52428800;

Error:

ORA-01184: logfile group 5 already exists

Troubleshoot:

SYS> SELECT thread#, group#
FROM gv$log;

THREAD# GROUP#
———- ———-
1                 1
1                 2
1                 3
1                 4
1                 5
1                 6
1                 7
1                 8
1                 9
1                 10

As we see there are just 10 groups.

Check standby redo logs:

SYS> SELECT group#
FROM v$standby_log;

GROUP#
———-
11

So we have standby redo log with group id 11.

Solution: 

Add logfile group with number more than 11.

I prefer to save numbers for standby logs and add new log with number 22(10 redo log groups+(10+1) recommended number of standby logs+ 1)

SYS> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 22 (‘+DATA’) SIZE 52428800;

Database altered.

How to fix /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

During upgrade from 11.2.0.3 to 11.2.0.4 I got  /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory

To solve this problem you should install glibc.i686 packages.

yum install glibc.i686

 

Thanks to Giorgi Peikrishvili for this case 🙂

EM: CPU chart Error: Invalid BoundDataSource: no class, method or dataSource provided.

Error:

CPU_Chart

Also no other tabs are available to click.

Reason:

One of the reason could be that you converted single instance database to RAC.

or you created EM repository without indicating -cluster option.

run:

emca -config dbcontrol db -repos recreate -cluster

answer the questions carefully 🙂

My settings:

You have specified the following settings

Database ORACLE_HOME ……………. /u01/app/oracle/product/11.2.0/db_1

Database instance hostname ……………. Listener ORACLE_HOME ……………. /u01/app/11.2.0/grid
Listener port number ……………. 1521
Cluster name ……………. oracle-db
Database unique name ……………. orcl
Email address for notifications …………… mariam.kupa@gmail.com
Outgoing Mail (SMTP) server for notifications …………… mail.ar.ge
ASM ORACLE_HOME ……………. /u01/app/11.2.0/grid
ASM port ……………. 1521
ASM user role ……………. SYSDBA
ASM username ……………. ASMSNMP

 

DDL Trigger to audit schema changes

  1. Create sequence for assigning numbers to the events:
    -- Create sequence
    create sequence SYS.DSQ_DDLEVENTS
    minvalue 1
    maxvalue 9999999999999999999999999999
    start with 1
    increment by 1
    cache 100;
  2. Create ddl events table
    -- Create table
    create table SYS.DDL_EVENTS
    (
     eventid NUMBER,
     inst_id   NUMBER,
     eventdate DATE,
     oraloginuser VARCHAR2(100),
     oradictobjname VARCHAR2(100),
     oradictobjowner VARCHAR2(100),
     oradictobjtype VARCHAR2(100),
     orasysevent VARCHAR2(100),
     machine VARCHAR2(100),
     program VARCHAR2(100),
     osuser VARCHAR2(100)
    )
    tablespace USERS;
  3. Create table for saving SQL statements(this is necessary because triggered sql statements may have several lines)
    -- Create table
    create table SYS.DDL_EVENTS_SQL
    ( eventid NUMBER,
     sqlline NUMBER,
     sqltext VARCHAR2(4000)
    )
    tablespace USERS;
  4. Create DDL trigger(it doesn’t degrade performance at all, by my experience)
    CREATE OR REPLACE TRIGGER sys.audit_ddl_trg
    AFTER DDL ON DATABASE
    DECLARE
    l_eventId NUMBER;
     l_sqlText ORA_NAME_LIST_T;
    BEGIN
    if ORA_SYSEVENT!='TRUNCATE' then
    
     SELECT dsq_ddlEvents.NEXTVAL INTO l_eventId FROM SYS.DUAL;
    INSERT INTO ddl_events (eventId,
     inst_id,
     EVENTDATE,
     ORALOGINUSER,
     ORADICTOBJNAME,
     ORADICTOBJOWNER,
     ORADICTOBJTYPE,
     ORASYSEVENT,
     machine,
     program,
     osuser
     )
     ( SELECT l_eventId,
     inst_id,
     SYSDATE,
     ORA_LOGIN_USER,
     ORA_DICT_OBJ_NAME,
     ORA_DICT_OBJ_OWNER,
     ORA_DICT_OBJ_TYPE,
     ORA_SYSEVENT,
     machine,
     program,
     osuser
     FROM SYS.DUAL
     right outer join
     SYS.GV$SESSION s on (1=1)
     WHERE s.sid=SYS_CONTEXT('USERENV','SID')
     and SYS_CONTEXT('USERENV','INSTANCE')=s.inst_id);
     FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
     INSERT INTO ddl_events_sql
     ( eventId, sqlLine, sqlText )
     VALUES
     ( l_eventId, l, l_sqlText(l) );
     END LOOP;
     end if;
    
     exception when others then
     null;
    END;

Oracle: Audit DMLs by specific user

Ordinary auditing do not have option to indicate audit some activities done by specific user.

I mean, you cannot write the following:

audit insert on my_schema.my_table by my_user;  <<—-not possible. The right statement is:
audit insert on my_schema.my_table by access;
or
audit insert on my_schema.my_table by session;

If I want to audit only activities done by my_user, one of the way is to create audit policy like the following;

begin
dbms_fga.add_policy(
object_schema=>’my_schema‘,
object_name=> ‘my_table‘,
policy_name=> ‘my_policy’,
audit_condition => ‘sys_context(”USERENV”,”CURRENT_USER”)=”MY_USER”’,
enable => TRUE,
statement_types => ‘INSERT, UPDATE, DELETE’,
audit_column_opts => dbms_fga.all_columns);
END;

So audit_condition gives the opportunity to check something and in this case we are checking user that is running statements indicated in statement_types option.

–Logs will be located here

SELECT * FROM dba_fga_audit_trail

–To see what policies we have

SELECT * FROM dba_audit_policies

HOW TO CATALOG TAPE BACKUP PIECES

You can catalog TAPE backup piece only using automatic channel:

RMAN> configure channel device type 'SBT_TAPE' parms <mml parameters>

Example:

configure channel device type 'SBT_TAPE' parms='ENV=(NB_ORA_CLIENT=ClientHostName,NB_ORA_SERV=backupServerHostName)';
RMAN> catalog device type 'SBT_TAPE' backuppiece 'arch_dEYC_ub4qtfud9_s20836_p1_t903346601';

Linker error while installing Oracle 11g /u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk

During installing 11g on OEL7 I got error when linking libraries.

To solve you should change syntax in the following file:

vi $ORACLE_HOME/sysman/lib/ins_emagent.mk

Change the following line from

$(MK_EMAGENT_NMECTL)

to:

$(MK_EMAGENT_NMECTL) -lnnz11

and click retry on error window to continue?

Good Luck!