Feeds:
Posts
Comments

Posts Tagged ‘oracle 10g’

Hi Guys recently we had issue while starting the DB of our Hyperion application.  Just wanted to share this with you guys which will help you.

Background:

Since 2 weeks while stopping DB it was taking more than 40 mins. This time it took more than 40 min. While checking the pending oracle process only one it was showing so I killed that one.

$ ps -ef | grep -i hyq

  orasid  3342396        1   0   Sep 16      –  0:15 /oracle/SID/112_64/bin/tnslsnr LISTENER -inherit

  orasid 36372574 51970254   1 06:14:07      –  0:07 oracleSID (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

$ kill -9 51970254

 

Problem:

Now while starting the DB instance.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 25 07:23:45 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected.

SQL> startup

ORA-01012: not logged on

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup

ORA-01012: not logged on

So whenever we forcefully shut down the DB this issue may occurs

“SYSRESV”  shows a shared memory segment for a non-existing instance

 

Solution:

At OS level remove the orphaned shared memory segment using this utility

$ sysresv

IPC Resources for ORACLE_SID “SID” :

Shared Memory:

ID              KEY

1048579         0xffffffff

4               0xffffffff

5               0x6767020c

Oracle Instance not alive for sid “SID”

$ ipcrm -m 1048579

$ ipcrm -m 4

$ ipcrm -m 5

 

Now tried to start DB

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Sep 25 07:29:17 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 7482626048 bytes

Fixed Size                  2236048 bytes

Variable Size            3271557488 bytes

Database Buffers         4194304000 bytes

Redo Buffers               14528512 bytes

Database mounted.

Database opened.

SQL> exit

Now good to see that instance getting stop & start immediately without any delay. 

Hope this article helped to you. I am expecting your suggestions/feedback.

It will help to motivate me to write more articles….!!!!

Thanks & Regards,

Samadhan

https://samadhandba.wordpress.com/

“Key for success, always fight even knowing your defeat is certain….!!!!

Advertisements

Read Full Post »

Dear Friens back to my Oracle journey after long time. This was interview question ask to my friend during the interview last week.Lots of questions relate with flashback and 10g / 11g new features.

      As requested by him decided to put new post on  Flashback database.

Enable Flash Recovery Area / Flashback database

Flashback functionalities provide fast and flexible data recovery.Type of flashback recovery:
  • Flashback Database (We can revert database at a past time)
  • Flashback Drop (Reverses the effects of a DROP TABLE statement)
  • Flashback Table (Reverses a table to its state at a previous point in time)
  • Flashback Query (We can specify a target time and then run queries, viewing results and recover from an unwanted change)
  • Flashback Transaction Query (We can view changes made by a transaction during a period of time.)

Requirement for Flashback:

  • Database must be in Archive log mode
  • Must have flash recovery area enable

Why database must be in archive log mode? Because archive logs are used in the Flashback operation.

Why flash back recovery area required? Because flashback logs can only be store in this area.

Important: In RAC Database, flashback recovery area must be store is clustered file system or in ASM.

How to Enable Flash Recovery Area?

Set two parameter db_recovery_file_dest and db_recovery_file_dest_size.

SQL> alter system set db_recovery_file_dest=”;

SQL> alter system set db_recovery_file_dest_size=2048m

How to Enable Flashback database?

Mount the database (not open), set parameter DB_FLASHBACK_RETENTION_TARGET and issue the ALTER DATABASE FLASHBACK ON command.

SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;

SQL> ALTER DATABASE FLASHBACK ON;

Note :

  • Default retation target is 1440 (One Days).
  • If we want to retain flashback logs to perform a 48 hour flashback, set the retention target to 2880 minutes (2 days x 24 hours/day x 60 minutes/hour)
  • By default, flashback logs are generated for all permanent tablespaces
  • We can reduce overhead by disabling flashback logging specific tablespaces:

SQL> ALTER TABLESPACE users FLASHBACK OFF;

We can re-enable flashback logging for a tablespace later with this command:

SQL> ALTER TABLESPACE users FLASHBACK ON;

Important:

  • If we disable Flashback Database for a tablespace, then we must take its datafiles offline before running FLASHBACK DATABASE.
  • We can enable Flashback Database not only on a primary database, but also on a standby database.

Prerequisites for Flashback recovery:

  • Must have FLASHBACK ANY TABLE system privilege or must have FLASHBACK object privilege on the table.
  • Must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
  • Row movement must be enabled on the table.
    SQL>ALTER TABLEENABLE ROW MOVEMENT;

Flashback Drop:( Reverses the effects of a DROP TABLE statement)

Reverse the Drop table ->
SQL> FLASHBACK TABLE EMP TO BEFORE DROP;

Assign a new name to the restored table->
SQL> FLASHBACK TABLE EMP TO BEFORE DROP RENAME TO;

  • We can drop it immediately and permanently with purge option (DROP TABLE emp PURGE)
  • We can view the dropped objects in the recycle bin from two dictionary views:
    USER_RECYCLEBIN — list all dropped user objects.
    DBA_RECYCLEBIN — list all dropped system-wide objects

Viewing the original name of dropped objects?
SQL> SELECT object_name as recycle_name, original_name, object_type FROM recyclebin;
How to remove table from recycle bin?
SQL> purge table “BIN$0+ktoVChEmXgNAAADiUEHQ==$0”;
How to purge recycle bin?
SQL> purge recyclebin;
How to purge all objects from the recycle bin?
SQL> purge dba_recyclebin;
How to purge all objects from tablespace in the recycle bin? SQL> purge tablespace users; Monitor logging in the Flashback Database logs?      SQL>select begin_time, flashback_data, db_data, redo_data, ESTIMATED_FLASHBACK_SIZE from v$flashback_database_stat;    Monitor the Flashback Database retention target?     SQL>select * from v$flashback_database_log; Hope you guy’s enjoy this post.Very soon I wil come up with Parsing & execution as well.Experts are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »