ORA-00353: during startup of instance from archiver process

When starting one of the databases we received the ORA-00353 error. It indicates that archiver process couldn’t archive one of the redo logs due to corruption. Oracle tried to read both the logmembers from the same group but they both were corrupted and instance crashes when trying to open it. ARC0: STARTING ARCH PROCESSES COMPLETE…

How to see the oldest flashback available?

Using the following query one can see the flashback data available. SELECT to_char(sysdate,'YYYY-MM-DD HH24:MI') current_time, to_char(f.oldest_flashback_time, 'YYYY-MM-DD HH24:MI') OLDEST_FLASHBACK_TIME, (sysdate – f.oldest_flashback_time)*24*60 HIST_MIN FROM v$database d, V$FLASHBACK_DATABASE_LOG f; CURRENT_TIME OLDEST_FLASHBACK HIST_MIN —————- —————- ———- 2012-04-25 07:34 2012-04-25 05:48 106.066667

How to stop a job scheduled in DBMS_SCHEDULER?

Using sys.dbms_scheduler.STOP_JOB one can stop scheduled job. SQL> exec sys.dbms_scheduler.STOP_JOB(job_name=>'SYS.ORA$AT_OS_OPT_SY_12856', force=>true); Output: PL/SQL procedure successfully completed. If it can’t find the job then you may see the following error, in the example below I hadn’t specified the user to it was looking for the job as the current user. ORA-27475: “DBAUSER.ORA$AT_OS_OPT_SY_12856” must be a job…

dba_jobs and interval

Few examples of some scenarios of setting intervals for dba_jobs. Note: Updating the interval will not update the NEXT_DATE run of the job until the job runs. Setting a job to run every monday at 10:00 am SQL> exec dbms_job.interval(5, 'TRUNC(NEXT_DAY(SYSDATE, ''MONDAY''))+(10/24)'); SQL> commit; — assume job was run and this SQL was run after…

Using sys.dbms_iob one can manage jobs in dba_jobs.

DBMS_JOB allows one to create/manage jobs under user who has logged but using sys.dbms_ijob one can manage jobs all jobs scheduled in DBA_JOBS. Here are some of the functions available in sys.dbms_ijob. To execute/run job: You don’t have to be an owner of the job SQL> exec sys.dbms_ijob.run(5); If one tried executing the job not…

How to tell/find if the instance is Primary/Standby?

By querying v$database one can tell if the host is primary or standby For primary testdb> select database_role from v$database; DATABASE_ROLE —————- PRIMARY For Standby – Note you may need to connect to as sys as sysdba if the instance is in mount state testd> select database_role from v$database; DATABASE_ROLE —————- PHYSICAL STANDBY OR On…

Dataguard ORA-16817

If you have an issue where the server where standby resides had to be restarted and when you try to enable Fast Start Failover, it wouldn’t enable due to ORA-16608 and when checking the primary database in dataguard it would return ORA-16817 “unsynchronized fast-start failover configuration” Example: $ dgmgrl / DGMGRL> enable configuration Enabled. DGMGRL>…