Flashback database / New Feature in oracle 10g

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 »

Oracle 10g Installation & Configuration On Solaris

Dear Friend, due to busy schedule writing new post after long time and extremely sorry for that.

Step 1
Install the Operating System
Note: No specified operating system patches are required with Solaris 10 OS.

Step 2
Make sure that following software packages has been installed.
SUNWarc
SUNWbtool
SUNWhea
SUNWlibm
SUNWlibms
SUNWsprot
SUNWtoo
SUNWilof
SUNWxwfnt
SUNWilcs
SUNWsprox
SUNWil5cs

Note: We can verify that packages are installed or not by using following command: $pkginfo -i.

Step 3
Check following executable file must be presents in /usr/ccs/bin
make
ar
il
nm

Step 4
Checks swap space. Swap space should be 512MB or Twice the size of RAM. Use following command to know about Physical Memory and Swap space:

$ /usr/sbin/prtconf grep size
$ /usr/sbin/swap –l

Step 5
Need at least 400 MB of free space in /tmp directory.

Step 6
Set following kernel parameter in /etc/system file and reboot the server.

Set shmsys:shminfo_shmmax=4294967295
Set shmsys:shminfo_shmmni=100
Set semsys:seminfo_semmsl=256
Set semsys:seminfo_semmni=100

Step 7
Create a group.

$ groupadd –g 300 dba *
$ groupadd –g 301 oinstall **

* “DBA” group will be use by the Oracle Software Owner and Database Administrators.

** “OINSTALL” group will be use when you installing multiple copies of the oracle software on one server and you will want some logins to be able to log onto some databases with DBA privileges but not others.

Step 8
Create a Unix user that will be the Oracle Software Owner.

$ useradd –c ‘Oracle Software Owner’ –d /oracle –g oinstall –G dba –m –u 300 –s /usr/bin/ksh oracle

$passwd oracle

Step 9
Create directory for oracle software and Database.

$ mkdir /oracle/app /oracle/oradata
$chown oracle:dba /oracle
$chmod 775 /oracle

Step 10
Create the /var/opt/oracle directory

$mkdir /var/opt/oracle
$chown oracle:dba /var/opt/oracle
$chmod 755 /var/opt/oracle

Step 11
edit .profile file and type following endearments

export ORACLE_BASE=/oracle

export ORACLE_HOME=$ORACLE_BASE/app

export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

Step 12
Set X window enveiroment.

Log in as a root with CDE (Common Desktop Environment Session)
$ DISPLAY=:0.0
$export DISPLAY
$xhost +
$ su – oracle
$DISPLAY=:0.0
$export DISPLAY
$/usr/openwin/bin/xclock

Step 13

Log in as a ORACLE user and execute run installer.

$./runInstaller

Next process from here onwards I will come up with new post including snap shot.

 

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 »

ora-12505 connection refused the specified sid

Hi,today one developer got this error while connecting to new production server.

Problem:

User was not able to connect to production server throght the application the error was,

ORA-12505 Connection refused, the specified SID (retaildb) was not recognized by the Oracle server.”

C:Documents and SettingsAdministrator>tnsping Global

TNS Ping Utility for 32-bit Windows: Version 9.2.0.6.0 – Production on 03-JUN-20
11 12:43:33

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
E:oracleRILDEVnetworkadminsqlnet.ora

TNS-03505: Failed to resolve name

C:Documents and SettingsAdministrator>
C:Documents and SettingsAdministrator>
C:Documents and SettingsAdministrator>
C:Documents and SettingsAdministrator>
C:Documents and SettingsAdministrator>lsnrctl services

LSNRCTL for 32-bit Windows: Version 9.2.0.6.0 – Production on 03-JUN-2011 12:44:
26

Copyright (c) 1991, 2002, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=romavser)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
  Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
    Handler(s):
      “DEDICATED” established:0 refused:0
         LOCAL SERVER
The command completed successfully

C:Documents and SettingsAdministrator>

 

And Listener.ora entry was :

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = romavser)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
    )
  )

Solution:

 had a service name “SAMDB”. When I tried connect to that service it always gave
ORA-12505, TNS:listener does not currently know of SID given in connect …

So I edited the listener.ora, added one more SID_DESC, restarted the listener. Now I can connect to the “SAMDB” database. Below is the updated listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = E:oracleSAMDEV)
      (PROGRAM = extproc)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = SAMDB)
      (ORACLE_HOME = E:oracleSAMDEV)
      (SID_NAME = SAMDB)
     )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
     (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
     )
     (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.231.38.15)(PORT = 1521))
     )
 
 )
)

Restarted the listener & error got resolved.

Read Full Post »

ORA-01157: cannot identify/lock data file / ORA-01110: data file/ EXP-00056: ORACLE error 1157 encountered

Dear all, yesterday I face small issue which I would like to share with you guy’s.It was temporary tablespace issue.This was development server for storage was recently changed.As mount point was changed but control file was having the previous temp file details so got the error while exporting schema.Just drop the old tempfile and created new one. Issue got resolved after that.

bash-2.05$ ls -lrt

total 562420

-rw-r–r–   1 oracle10g oinstall    1729 Jan 30 14:57 abc.sh

-rw-r—–   1 oracle10g oinstall 77631488 Jan 30 23:50 reporter_sam_exp30JAN2011.dmp

-rw-r—–   1 oracle10g oinstall 77635584 Apr  9 23:19 reporter_sam_exp09042011.dmp

-rw-r—–   1 oracle10g oinstall 132513792 Jun  1 04:01 SAM1_exp31052011.dmp

bash-2.05$ exp

Export: Release 10.2.0.3.0 – Production on Wed Jun 1 04:13:42 2011

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

Username: / as sysdba

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 4096 >

Export file: expdat.dmp > SAM1_orig_exp31052011.dmp

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2

Export grants (yes/no): yes > y

Export table data (yes/no): yes > y

Compress extents (yes/no): yes > y

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users …

User to be exported: (RETURN to quit) > SAM1

User to be exported: (RETURN to quit) >

 . exporting pre-schema procedural objects and actions

. exporting foreign function library names for user SAM1

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user SAM1

About to export SAM1’s objects …

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

EXP-00056: ORACLE error 1157 encountered

ORA-01157: cannot identify/lock data file 201 – see DBWR trace file

ORA-01110: data file 201: ‘/oradata/oracle10g/oradata/temp/temp2.dbf’

EXP-00000: Export terminated unsuccessfully

bash-2.05$ sqlplus / as sysdba

 SQL*Plus: Release 10.2.0.3.0 – Production on Wed Jun 1 04:16:46 2011

 Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

 SQL> select * from v$tempfile;

      FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED

———- —————- ——— ———- ———- ——- ———-

     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE

———- ———- ———— ———-

NAME

——————————————————————————–

         1       8.2892E+12 07-FEB-11         24          2 ONLINE  READ WRITE

         0          0   2147483648       8192

/oradata/oracle10g/oradata/temp/temp2.dbf

          3       8.2892E+12 07-FEB-11         24          1 ONLINE  READ WRITE

         0          0   3221225472       8192

/oradata/oracle10g/oradata/temp/temp1.dbf

      FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED

———- —————- ——— ———- ———- ——- ———-

     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE

———- ———- ———— ———-

NAME

——————————————————————————–

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files

                                                          *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 203 – see DBWR trace file

ORA-01110: data file 203: ‘/oradata/oracle10g/oradata/temp/temp1.dbf’

 SQL> select file#,status,name from v$tempfile;

      FILE# STATUS

———- ——-

NAME

——————————————————————————–

         1 ONLINE

/oradata/oracle10g/oradata/temp/temp2.dbf

          3 ONLINE

/oradata/oracle10g/oradata/temp/temp1.dbf

 SQL> !ls -lrt /oradata/oracle10g/oradata/temp/temp1.dbf

/oradata/oracle10g/oradata/temp/temp1.dbf: No such file or directory

 SQL> !ls -lrt /oradata/oracle10g/oradata/temp/temp2.dbf

/oradata/oracle10g/oradata/temp/temp2.dbf: No such file or directory

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files

                                                           *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 203 – see DBWR trace file

ORA-01110: data file 203: ‘/oradata/oracle10g/oradata/temp/temp1.dbf’

 SQL> alter database tempfile ‘/oradata/oracle10g/oradata/temp/temp1.dbf’ drop;

 Database altered.

 SQL> select file#,status,name from v$tempfile;

      FILE# STATUS

———- ——-

NAME

——————————————————————————–

         1 ONLINE

/oradata/oracle10g/oradata/temp/temp2.dbf

 SQL> ALTER TABLESPACE TEMP1 add tempfile ‘/DATA1/temp1.dbf’ size 10G;

 Tablespace altered.

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

ERROR:

ORA-01157: cannot identify/lock data file 201 – see DBWR trace file

ORA-01110: data file 201: ‘/oradata/oracle10g/oradata/temp/temp2.dbf’

 SQL> alter database tempfile ‘/oradata/oracle10g/oradata/temp/temp2.dbf’ drop;

 Database altered.

 SQL> ALTER TABLESPACE TEMP1 add tempfile ‘/DATA1/temp2.dbf’ size 10G;

 Tablespace altered.

 SQL> select tablespace_name, file_name, bytes/1048576 “MB” from dba_temp_files;

 TABLESPACE_NAME

——————————

FILE_NAME

——————————————————————————–

        MB

———-

TEMP1

/DATA1/temp1.dbf

     10240

 TEMP1

/DATA1/temp2.dbf

     10240

 SQL> select file#,status,name from v$tempfile;

      FILE# STATUS

———- ——-

NAME

——————————————————————————–

         1 ONLINE

/DATA1/temp2.dbf

          2 ONLINE

/DATA1/temp1.dbf

 SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

bash-2.05$ pwd

/DATA1

bash-2.05$ cd –

/DATA

bash-2.05$ cd sam_cxl

bash-2.05$ ls -lrt

total 562420

-rw-r–r–   1 oracle10g oinstall    1729 Jan 30 14:57 abc.sh

-rw-r—–   1 oracle10g oinstall 77631488 Jan 30 23:50 sam_cxl_exp30JAN2011.dmp

-rw-r—–   1 oracle10g oinstall 77635584 Apr  9 23:19 sam_cxl_exp09042011.dmp

-rw-r—–   1 oracle10g oinstall 132513792 Jun  1 04:01 SAM1_exp31052011.dmp

bash-2.05$ exp

 Export: Release 10.2.0.3.0 – Production on Wed Jun 1 04:35:18 2011

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

 Username: / as sysdba

 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production

With the Partitioning, OLAP and Data Mining options

Enter array fetch buffer size: 4096 >

 Export file: expdat.dmp > SAm1_orig_exp31052011.dmp

 (1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > 2

 Export grants (yes/no): yes >

 Export table data (yes/no): yes >

 Compress extents (yes/no): yes >

 Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses WE8ISO8859P1 character set (possible charset conversion)

 About to export specified users …

User to be exported: (RETURN to quit) > SAM1

 User to be exported: (RETURN to quit) >

 . exporting pre-schema procedural objects and actions

. exporting foreign function library names for user SAM1

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user SAM1

About to export ISCM’s objects …

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export SAM1’s tables via Conventional Path …

. . exporting table       SAM_BERTHING_GRADE_ARMS          5 rows exported

.

.

.

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting materialized views

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully with warnings.

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 »