Blocking Oracle user access by IP Address.

We are able to block clients based on their IP address or host name using oracle sqlnet.ora file which exist in  D:\IN\Oracle\product\10.2.0\db_1\network\ADMIN this path.

1. Go to D:\IN\Oracle\product\10.2.0\db_1\network\ADMIN and open  sqlnet.ora file.

2. Insert the following lines.

# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

tcp.validnode_checking = yes             
                          
#This turns on the hostname/IP checking for your listeners. After this you can select list of host/IP # which you are allow to database connection.
 
tcp.invited_nodes = (localhost, 10.11.201.150, 10.11.201.200)

# This the list of host name or IP Address.

3. Now restart your listener.


Now you can try to connect your database from another IP Like 10.11.201.204 you can get error message.

Flashback DROP Table In Oracle 10g. (Recover Your Droped Table )

In Oracle database 10g default DROP option to move table in the recycle bin (or rename it). The DROP TABLE ... PURGE option can be used to permanently drop a table. If you drop table without  using DROP TABLE RAJIB PURGE you can recover your table using Oracle flashback Technology.


SQL> CREATE TABLE FLASHBACK_TEST(ID NUMBER, NAME VARCHAR2(20));

Table created.

SQL> INSERT INTO FLASHBACK_TEST(ID, NAME) VALUES(10,'RAJIB');

1 row created.

SQL> INSERT INTO FLASHBACK_TEST(ID, NAME) VALUES(20,'KING');

1 row created.

SQL> INSERT INTO FLASHBACK_TEST(ID, NAME) VALUES(30,'MORGAN');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM FLASHBACK_TEST;

        ID NAME
---------- --------------------
        10 RAJIB
        20 KING
        30 MORGAN

SQL> SELECT TO_TIMESTAMP(SYSDATE) FROM DUAL;

TO_TIMESTAMP(SYSDATE)
---------------------------------------------------------------------------
27-JUL-13 12.00.00 AM

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    2047418

SQL> SELECT * FROM FLASHBACK_TEST;

        ID NAME
---------- --------------------
        10 RAJIB
        20 KING
        30 MORGAN

SQL> DROP TABLE  FLASHBACK_TEST;

Table dropped.

SQL> SELECT * FROM FLASHBACK_TEST;
SELECT * FROM FLASHBACK_TEST
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> SHOW RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_TEST   BIN$JTA6PK8nRqeAuj79hlI9dQ==$0 TABLE        2013-07-27:12:40:36

SQL> FLASHBACK TABLE  FLASHBACK_TEST TO BEFOURE DROP;
FLASHBACK TABLE  FLASHBACK_TEST TO BEFOURE DROP
                                   *
ERROR at line 1:
ORA-00905: missing keyword


SQL> FLASHBACK TABLE  FLASHBACK_TEST TO BEFORE DROP;

Flashback complete.

SQL> SELECT * FROM FLASHBACK_TEST;

        ID NAME
---------- --------------------
        10 RAJIB
        20 KING
        30 MORGAN

SQL>

ORA-01033: ORACLE initialization or shutdown in progress

Cause : Your database are not in open stage. May be your database in startup/mount stage.

Action : Open database the following way.

SQL> CONN MICR/MICR;
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress


Warning: You are no longer connected to ORACLE.
SQL> ALTER DATABASE OPEN;
SP2-0640: Not connected
SQL> CONN /AS SYSDBA
Connected.
SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT STATUS FROM V$INSTANCE;

STATUS
------------
OPEN

SQL> CONN MICR/MICR;
Connected.
SQL>

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

Cause : Your media recovery in not enable (Your database in not in archive log mode). In the case of enable Oracle database flashback on you can get this error message if your database is not in archive log mode.

Action : To raise then error set alter your database in archive log mode.

SQL> ALTER DATABASE FLASHBACK ON;
ALTER DATABASE FLASHBACK ON
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.


SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

Oracle Flashback Technology.

Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.
Using flashback features, we can do the following:

    1. Perform queries that return past data.
    2. Perform queries that return metadata that shows a detailed history of changes to the database.
    3. Recover tables or rows to a previous point in time.
    4. Automatically track and archive transactional data changes.
    5. Roll back a transaction and its dependent transactions while the database remains online.

Enable Flashback Database:  To enable Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement.

DB_FLASHBACK_RETENTION_TARGET specifies the upper limit (in minutes) on how far back in time the database may be flashed back.

To Enable Flashback Database log in your oracle database as sysdba. And ensure your database in mounting stage.

SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

FLASHBACK_ON
------------------
NO

SQL> SHOW RECYCLEBIN;
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area  717225984 bytes
Fixed Size                  1292684 bytes
Variable Size             293602932 bytes
Database Buffers          415236096 bytes
Redo Buffers                7094272 bytes
Database mounted.
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=1440;   # 24*60=
1440 (For 1 Day)
System altered.

SQL> ALTER DATABASE FLASHBACK ON;
ALTER DATABASE FLASHBACK ON
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.


SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

SQL>
 

Generat XML File From Oracle database.

1. Create table for insert data which can use to generate XML File. 

CREATE TABLE XML_DATA
(
  C1  VARCHAR2(10 BYTE),
  C2  VARCHAR2(100 BYTE)
)

2. Inserting data in table.

Insert into XML_DATA
   (C1, C2)
 Values
   ('100', 'KING');
Insert into XML_DATA
   (C1, C2)
 Values
   ('500', 'RAJIB');
Insert into XML_DATA
   (C1, C2)
 Values
   ('200', 'MORGAN');
COMMIT;

3. Create an directory for create an XML File

CREATE OR REPLACE DIRECTORY
BB_ICE AS
'/u01/XML/ICE/';


GRANT READ, WRITE ON DIRECTORY BB_ICE TO BEFTN;

4. Create an procedure for generate XML File.

CREATE OR REPLACE PROCEDURE DPR_GENERAT_XML_FILE
(P_DIRECTORY VARCHAR2)
IS
    V_FILE UTL_FILE.FILE_TYPE;
    V_DATA   dbms_xmlgen.ctxtype;
    v_xml       CLOB;
BEGIN
       V_FILE := utl_file.fopen (P_DIRECTORY, 'TESAT.XML', 'w');
     
    FOR I IN (SELECT C1 FROM XML_DATA) LOOP 
         V_DATA :=dbms_xmlgen.newcontext
               ('SELECT C1, C2 FROM XML_DATA WHERE C1 = '||I.C1);
         --dbms_xmlgen.setrowsettag (V_DATA, ABC);
         dbms_xmlgen.setnullhandling (V_DATA, dbms_xmlgen.EMPTY_TAG);
         dbms_xmlgen.setrowtag (V_DATA, NULL);
         v_xml := dbms_xmlgen.getxml (V_DATA);
         utl_file.put_line (V_FILE,v_xml);
         dbms_xmlgen.closecontext (V_DATA);
    END LOOP;
       utl_file.fclose   (V_FILE);  
END;

ORA-39726: unsupported add/drop column operation on compressed tables

Cause : In this error occurred because the table your trying to drop columns from are compressed. Table compression is usually applied to partitioned tables, to minimized spaced based on columns with repeating values


SQL> ALTER TABLE OUTWDMST COMPRESS;

Table altered.

SQL> ALTER TABLE OUTWDMST DROP COLUMN CCU_CODE;
ALTER TABLE OUTWDMST DROP COLUMN CCU_CODE
                                 *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables


SQL> ALTER TABLE OUTWDMST SET UNUSED COLUMN CCU_CODE;

Table altered.

SQL> ALTER TABLE OUTWDMST DROP UNUSED COLUMNS;

Table altered.

SQL> SELECT CCU_CODE FROM OUTWDMST;
SELECT CCU_CODE FROM OUTWDMST
       *
ERROR at line 1:
ORA-00904: "CCU_CODE": invalid identifier


SQL>

ORA-12547: TNS:lost contact

Cause : This error occur when oracle database resource owner in not oracle user.

Action : Connect your server as root user and perform the following.

[root@micrsvr db_1]# chown -R oracle:oinstall /u01
[root@micrsvr db_1]# chmod -R 775 /u01
[root@micrsvr db_1]# su oracle

[oracle@micrsvr ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 18 13:18:20 2013

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

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  935317504 bytes
Fixed Size                  2218912 bytes
Variable Size             692061280 bytes
Database Buffers          234881024 bytes
Redo Buffers                6156288 bytes
Database mounted.
Database opened.
SQL>

Mounting Linux Path With Another Server/Host Shared Locatation

[root@micrsvr ~]# mount //10.11.201.200/Share/ /u01/MICR/ -o username=Rajib,password=Admin

Monitoring OS Resources In Red hat Linux.

[root@micrsvr ~]# top
top - 12:21:15 up 13 days, 15:53,  5 users,  load average: 0.24, 0.25, 0.19
Tasks: 262 total,   1 running, 261 sleeping,   0 stopped,   0 zombie
Cpu(s):  3.8%us,  1.4%sy,  0.0%ni, 91.7%id,  3.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2043324k total,  1972360k used,    70964k free,   101808k buffers
Swap:  4192248k total,    31944k used,  4160304k free,  1275172k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND           
 8739 root      20   0  288m  12m 9496 S  1.0  0.6   0:00.35 gnome-terminal    
   12 root      20   0     0    0    0 S  0.3  0.0   5:40.41 events/1          
 6875 root      20   0 98860 4712 2908 S  0.3  0.2   0:01.95 sshd              
 8933 root      20   0 15220 1360  944 R  0.3  0.1   0:00.02 top               
    1 root      20   0 19396 1292 1080 S  0.0  0.1   0:09.82 init              
    2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd          
    3 root      RT   0     0    0    0 S  0.0  0.0   0:00.04 migration/0       
    4 root      20   0     0    0    0 S  0.0  0.0   0:00.01 ksoftirqd/0       
    5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0       
    6 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/0        
    7 root      RT   0     0    0    0 S  0.0  0.0   0:00.05 migration/1       
    8 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/1       
    9 root      20   0     0    0    0 S  0.0  0.0   0:00.29 ksoftirqd/1       
   10 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/1        
   11 root      20   0     0    0    0 S  0.0  0.0   0:00.03 events/0          
   13 root      20   0     0    0    0 S  0.0  0.0   0:00.00 cpuset            
   14 root      20   0     0    0    0 S  0.0  0.0   0:00.00 khelper           

Sort top Output command
=============================================
We can sort top output command by pressing O while it is running.

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND           
 8973 oracle    20   0 1126m  23m  21m R  4.3  1.2   0:00.13 oracle            
 6875 root      20   0 98860 4712 2908 S  1.0  0.2   0:02.01 sshd              
 8739 root      20   0  288m  12m 9496 S  0.7  0.6   0:00.42 gnome-terminal    
   12 root      20   0     0    0    0 S  0.3  0.0   5:40.44 events/1          
 2859 oracle    20   0 1110m  16m  14m S  0.3  0.8   2:25.11 oracle            
 6921 root      20   0  239m  10m 7564 S  0.3  0.5   0:00.26 metacity          
 8950 root      20   0 15220 1356  944 R  0.3  0.1   0:00.10 top               
    1 root      20   0 19396 1288 1076 S  0.0  0.1   0:09.82 init              
    2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd          
    3 root      RT   0     0    0    0 S  0.0  0.0   0:00.04 migration/0       
    4 root      20   0     0    0    0 S  0.0  0.0   0:00.01 ksoftirqd/0       
    5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0       
    6 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/0        
    7 root      RT   0     0    0    0 S  0.0  0.0   0:00.05 migration/1       
    8 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/1       
    9 root      20   0     0    0    0 S  0.0  0.0   0:00.30 ksoftirqd/1       
   10 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/1        
Current Sort Field:  A  for window 1:Def

[root@micrsvr ~]# top
Select sort field via field letter, type any other key to return

* A: PID        = Process Id              v: nDRT       = Dirty Pages count
  b: PPID       = Parent Process Pid      w: S          = Process Status
  c: RUSER      = Real user name          x: COMMAND    = Command name/line
  d: UID        = User Id                 y: WCHAN      = Sleeping in Function
  e: USER       = User Name               z: Flags      = Task Flags <sched.h>
  f: GROUP      = Group Name
  g: TTY        = Controlling Tty       Note1:
  h: PR         = Priority                If a selected sort field can't be
  i: NI         = Nice value              shown due to screen width or your
  j: P          = Last used cpu (SMP)     field order, the '<' and '>' keys
  k: %CPU       = CPU usage               will be unavailable until a field
  l: TIME       = CPU Time                within viewable range is chosen.
  m: TIME+      = CPU Time, hundredths
  n: %MEM       = Memory usage (RES)    Note2:
  o: VIRT       = Virtual Image (kb)      Field sorting uses internal values,
  p: SWAP       = Swapped size (kb)       not those in column display.  Thus,
  q: RES        = Resident size (kb)      the TTY & WCHAN fields will violate
  r: CODE       = Code size (kb)          strict ASCII collating sequence.
  s: DATA       = Data+Stack size (kb)    (shame on you if WCHAN is chosen)
  t: SHR        = Shared Mem size (kb)
  u: nFLT       = Page Fault count
top - 12:22:37 up 13 days, 15:54,  5 users,  load average: 0.33, 0.28, 0.20
Tasks: 263 total,   2 running, 261 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.0%us,  0.5%sy,  0.0%ni, 98.3%id,  0.2%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2043324k total,  1972384k used,    70940k free,    97596k buffers
Swap:  4192248k total,    31944k used,  4160304k free,  1278756k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND           
16672 root      18  -2 11292  696  264 S  0.0  0.0   0:00.00 udevd             
16663 root      18  -2 12480 2352  544 S  0.0  0.1   0:00.02 udevd             
11415 oracle    20   0 90320 2424 1964 S  0.0  0.1   0:00.00 gconf-helper      
11412 oracle     9 -11  429m 3848 2496 S  0.0  0.2   0:00.11 pulseaudio        
11159 oracle    20   0  207m 5960 2688 S  0.0  0.3   0:24.42 tnslsnr           
 8986 oracle    20   0 1109m  12m  10m R  1.7  0.6   0:00.05 oracle            
 8950 root      20   0 15220 1360  948 R  0.3  0.1   0:00.15 top               
 8741 root      20   0  105m 1804 1444 S  0.0  0.1   0:00.01 bash              
 8740 root      20   0  8280  692  596 S  0.0  0.0   0:00.00 gnome-pty-helpe   
 8739 root      20   0  288m  12m 9496 S  0.0  0.6   0:00.44 gnome-terminal    
 7412 root      20   0  280m  11m 8552 S  0.0  0.6   0:00.07 notification-da   
 7331 root      20   0  211m 2868 2416 S  0.0  0.1   0:00.00 gvfsd-computer    
 7325 root      20   0  129m 1768 1460 S  0.0  0.1   0:00.00 gvfsd-metadata    
 7322 root      20   0  131m 2176 1868 S  0.0  0.1   0:00.00 gvfsd-burn        
 7320 root      20   0  225m 5820 4656 S  0.0  0.3   0:00.00 ibus-x11          
 7318 root      20   0  335m  23m  11m S  0.0  1.2   0:00.29 python            
 7316 root      20   0  150m 3164 2648 S  0.0  0.2   0:00.00 ibus-gconf 


Kill a process.
==================================================
If you want to kill a process then first note down the PID and then while running top press "k" which will ask you for the process id. Type the process ID.You can see killed successfully.
[root@micrsvr ~]# top
top - 12:27:56 up 13 days, 15:59,  5 users,  load average: 0.05, 0.11, 0.14
Tasks: 262 total,   1 running, 261 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.2%us,  0.1%sy,  0.0%ni, 99.4%id,  0.3%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2043324k total,  1976272k used,    67052k free,    96232k buffers
Swap:  4192248k total,    31944k used,  4160304k free,  1288384k cached
PID to kill: 2589
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND           
 2589 oracle    20   0 1109m  15m  13m S  2.0  0.8   0:40.34 oracle            
 9157 root      20   0 15216 1232  832 R  2.0  0.1   0:00.01 top               
    1 root      20   0 19396 1276 1064 S  0.0  0.1   0:09.84 init              
    2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd          
    3 root      RT   0     0    0    0 S  0.0  0.0   0:00.04 migration/0       
    4 root      20   0     0    0    0 S  0.0  0.0   0:00.01 ksoftirqd/0       
    5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0       
    6 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/0        
    7 root      RT   0     0    0    0 S  0.0  0.0   0:00.05 migration/1       
    8 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/1       
    9 root      20   0     0    0    0 S  0.0  0.0   0:00.30 ksoftirqd/1       
   10 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 watchdog/1        
   11 root      20   0     0    0    0 S  0.0  0.0   0:00.03 events/0          
   12 root      20   0     0    0    0 S  0.0  0.0   5:40.60 events/1          
   13 root      20   0     0    0    0 S  0.0  0.0   0:00.00 cpuset            
   14 root      20   0     0    0    0 S  0.0  0.0   0:00.00 khelper           
   15 root      20   0     0    0    0 S  0.0  0.0   0:00.00 netns             

ORA-01103: database name 'BCBL' in control file is not 'TEST'

SQL> STARTUP

ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             239077628 bytes
Database Buffers          364904448 bytes
Redo Buffers                7094272 bytes
ORA-01103: database name 'BCBL' in control file is not 'TEST'

SQL> SHUT IMMEDIATE;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> STARTUP NOMOUNT;

ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             243271932 bytes
Database Buffers          360710144 bytes
Redo Buffers                7094272 bytes

SQL> ALTER SYSTEM SET DB_NAME=BCBL SCOPE=SPFILE;

System altered.

SQL> SHUT IMMEDIATE;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1292036 bytes
Variable Size             247466236 bytes
Database Buffers          356515840 bytes
Redo Buffers                7094272 bytes
Database mounted.
Database opened.

SQL>

Redo Logs Files And Control Files Activity.

Redo Logs:- Redo logs record all changes made to a database's data files. Each time data is changed in the database, that change is recorded in the online redo log first, before it is applied to the data files. An Oracle database requires at least two online redo log groups , and in each group there is at least one online redo log member , an individual redo log file where the changes are recorded. At intervals, the database rotates through the online redo log groups, storing changes in the current online redo log.

Control Files:-  The control file contains the record of the ph ysical structures of the database and their status. Several types of information stored in the control file are related to backup and recovery:

1. Database information (RESETLOGS SCN and time stamp)

2. Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline ranges)

3. Information about redo threads (current online redo log)

4. Log records (log sequence numbers, SCN range in each log)

5. A record of past RMAN backups

6. Information about corrupt datafile blocks

Undo Segments:- In general, when data in a datafile is upda ted, "before images" of that data are written into undo segments . If a transaction is rolled back, this undo information can be used to restore the original datafile contents. In the context of recovery, the undo information is used to undo the effects of uncommitted transactions, once all the datafile changes from the redo logs have been applied to the datafiles. The database is actually opened before the undo is applied.

ORA-08189: cannot flashback row movement is not enabled

Whenever I tried to Flashback Table in Oracle. The following error occurs.

FLASHBACK TABLE OUTWDCLR TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' minute);

Cause :
========================
Whenever you performed flashback operation(flashback table, flashback version query,flashback transaction query) of a table except flashback drop feature(which don't need to enable row movement as whole objects come from recycle bin) then above error will come.

Solution :
========================
Row movement of the table need to be enabled in order to perform flashback table.

See Row Movement Status
 ========================
SELECT TABLE_NAME,ROW_MOVEMENT FROM USER_TABLES WHERE TABLE_NAME='OUTWDCLR';

Enable  Row Movement Status
========================
ALTER TABLE OUTWDCLR ENABLE ROW MOVEMENT;

Now You can FLASHBACK Your Table.

FLASHBACK TABLE OUTWDCLR
  TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' minute);