Ravindra's blog

Wednesday, 24 July 2013

How to install SQL Developer on Linux?

     How to install SQL Developer on Linux?

:- below is steps for installing sql Developer on linux...

Step 1: download sql developer using below link..

http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html

Step 2: Download latest Java SE Development Kit 6....
http://www.oracle.com/technetwork/java/javase/downloads/jdk6u35-downloads-1836443.html

Step 3:install sqldeveloper rpm package....
# rpm -Uvh sqldeveloper-3.2.20.09.87-1.noarch.rpm

Step 4: give permission to JDK
#chmod a+x   jdk-6u35-linux-i586-rpm.bin

step 5: execute the rpm..
# ./jdk-6u35-linux-i586-rpm.bin

Step 6: After successfully execute jdk..
set JAVA_HOME path and sqldeveloper path in oracle user bash_profile.
   EXAMPLE:
      JAVA_HOME=/usr/java/jdk1.6.0_35/; export JAVA_PATH
      PATH=$JAVA_HOME/bin:$PATH:/opt/sqldeveloper/; export PATH

Step 7: execute bash_profile...
      $. .bash_profile

Step 8: execute sqldeveloper command

      $sqldeveloper

1) click on + button for connection

2)specify you connection parameter

3)after specify connection parameter click on connect button.now you ready to execute your sql query.

################END##################################

Friday, 5 July 2013

Automating Database Startup and Shutdown while Linux OS start and shutdown.

Automating Database Startup and Shutdown on Linux

1 1) Create a file called “/etc/init.d/dbora” as the root user, containing the following

# vi /etc/init.d/dbora

#!/bin/sh

# chkconfig: 345 99 10

# description: Oracle auto start-stop script.

# Set ORA_OWNER to the user id of the owner of the

# Oracle database software.

ORA_OWNER=oracle

case "$1" in

'start')

# Start the Oracle databases:

# The following command assumes that the oracle login

# will not prompt the user for any values

su - $ORA_OWNER -c "/home/oracle/scripts/startup.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"

touch /var/lock/subsys/dbora

;;

'stop')

# Stop the Oracle databases:

# The following command assumes that the oracle login

# will not prompt the user for any values

su - $ORA_OWNER -c "/home/oracle/scripts/shutdown.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"

rm -f /var/lock/subsys/dbora

;;

esac

2)  chmod 750 /etc/init.d/dbora

3)  # mkdir -p /home/oracle/scripts

4)  # chown oracle.oinstall /home/oracle/scripts

5)  Vi /home/oracle/scripts/startup.sh

#!/bin/bash

export ORACLE_SID=DB11G       */database name/*

ORAENV_ASK=NO

. oraenv

ORAENV_ASK=YES

# Start Listener

lsnrctl start

# Start Database

sqlplus / as sysdba << EOF

STARTUP;

EXIT;

EOF

6)vi /home/oracle/scripts/shutdown.sh

#!/bin/bash

export ORACLE_SID=DB11G      */database name /*

ORAENV_ASK=NO

. oraenv

ORAENV_ASK=YES

# Stop Database

sqlplus / as sysdba << EOF

SHUTDOWN IMMEDIATE;

EXIT;

EOF

# Stop Listener

lsnrctl stop

7)# chmod u+x /home/oracle/scripts/startup.sh

  # chmod u+x /home/oracle/scripts/shutdown.sh

8)# chown oracle.oinstall /home/oracle/scripts/startup.sh

  #chown oracle.oinstall /home/oracle/scripts/shutdown.sh

9) # service dbora start

10)# service dbora stop

Thursday, 4 July 2013

how to change DBID using NID command?

step to change DBID using NID command

[oracle@oracle@example dupli]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 4 16:13:26 2013

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             293603712 bytes
Database Buffers          121634816 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle@example dupli]$ nid target=/

DBNEWID: Release 11.2.0.1.0 - Production on Thu Jul 4 16:13:51 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to database DB11G (DBID=289552766)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/db11g/control01.ctl
    /u01/app/oracle/oradata/db11g/control02.ctl

Change database ID of database DB11G? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 289552766 to 289542527
    Control File /u01/app/oracle/oradata/db11g/control01.ctl - modified
    Control File /u01/app/oracle/oradata/db11g/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/db11g/system01.db - dbid changed
    Datafile /u01/app/oracle/oradata/db11g/sysaux01.db - dbid changed
    Datafile /u01/app/oracle/oradata/db11g/undotbs01.db - dbid changed
    Datafile /u01/app/oracle/oradata/db11g/users01.db - dbid changed
    Datafile /u01/app/oracle/oradata/db11g/temp01.db - dbid changed
    Control File /u01/app/oracle/oradata/db11g/control01.ctl - dbid changed
    Control File /u01/app/oracle/oradata/db11g/control02.ctl - dbid changed
    Instance shut down

Database ID for database DB11G changed to 289542527.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

[oracle@oracle@example dupli]$
[oracle@oracle@example dupli]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 4 16:20:36 2013

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             293603712 bytes
Database Buffers          121634816 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DB11G     READ WRITE

                                                     ***********END*****************

how to start Auditing in oracle Database?

       As per organization policy we should set Auditing on particular user,oracle database objects etc.
we set this auditing policy to check if any unwanted operation has been performed on production database.
so my this post shows you how to start auditing on purticular table and user operation.

SQL> SHOW PARAMETER AUDIT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/db11g/ad
                                                 ump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }
The following list provides a description of each setting:

    none or false - Auditing is disabled.
    db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
    db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
    xml- Auditing is enabled, with all audit records stored as XML format OS files.
    xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
    os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             268437888 bytes
Database Buffers          146800640 bytes
Redo Buffers                6094848 bytes
Database mounted.
Database opened.

SQL> AUDIT ALL BY scott BY ACCESS;

Audit succeeded.

SQL> AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY scott BY ACCESS;

Audit succeeded.

SQL> AUDIT EXECUTE PROCEDURE BY scott BY ACCESS;

Audit succeeded.

SQL> conn scott/tiger
Connected.
SQL> CREATE TABLE test_tab (
id NUMBER
);

SQL>INSERT INTO test (id) VALUES (1);

SQL>UPDATE test SET id = id;

SQL>SELECT * FROM test;

SQL>DELETE FROM test;

SQL>DROP TABLE test;

Table created.

SQL>
1 row created.

SQL>
1 row updated.

SQL>
        ID
----------
         1

SQL>
1 row deleted.

SQL>

Table dropped.

SQL>conn /as sysdba

SQL>SELECT view_name
FROM   dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;

VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS

SQL>set lines 1000
COLUMN username FORMAT A10
COLUMN owner    FORMAT A10
COLUMN obj_name FORMAT A10
COLUMN extended_timestamp FORMAT A35

SQL>SELECT username,
       extended_timestamp,
       owner,
       obj_name,
       action_name
FROM   dba_audit_trail       /*** view as per your Audit_trail option for example for
WHERE owner = 'SCOTT'       xml option v$xml_audit_trail ***/
ORDER BY timestamp;

USERNAME   EXTENDED_TIMESTAMP                  OWNER      OBJ_NAME   ACTION_NAME
---------- ----------------------------------- ---------- ---------- ----------------------------
SCOTT      04-JUL-13 12.30.41.310309 PM +05:30 SCOTT      TEST_TAB   CREATE TABLE
SCOTT      04-JUL-13 12.30.41.451926 PM +05:30 SCOTT      TEST_TAB   INSERT
SCOTT      04-JUL-13 12.30.41.457726 PM +05:30 SCOTT      TEST_TAB   UPDATE
SCOTT      04-JUL-13 12.30.41.460754 PM +05:30 SCOTT      TEST_TAB   SELECT
SCOTT      04-JUL-13 12.30.41.464694 PM +05:30 SCOTT      TEST_TAB   DELETE
SCOTT      04-JUL-13 12.30.55.593675 PM +05:30 SCOTT      TEST_TAB   DROP TABLE

                                             *********END***************

Wednesday, 3 July 2013

user managed database cloning in oracle

   Some time our need is to create clone database of current database for developing purpose.
simple meaning of clone database is to create same database on same server or different server
with same name or different name.so my this blog post describe you how to create user managed database cloning in oracle ?

example:-
          ip address: 192.168.128.135 (target database ip address)
                    : 192.168.128.136 (source database ip address)

         database name: db11g

[oracle@oracle@example]$ export ORACLE_SID=db11g

[oracle@oracle@example]$ echo $ORACLE_SID

db11g

[oracle@oracle@example]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 4 10:06:56 2013

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> alter database backup controlfile to trace as '/u01/archivelog/control.sql';   (take backup of controlfile to trace using this command)

Database altered.

SQL>shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

(make directory structure on source database server same as target database server)

:- On source database (192.168.128.136)

[oracle@oracle@example]$mkdir -p /u01/app/oracle/oradata/dupli

[oracle@oracle@example]$mkdir -p /u01/app/oracle/admin/dupli

[oracle@oracle@example]$cd /u01/app/oracle/admin/dupli

[oracle@oracle@example dupli]$ mkdir -p adump dpdump pfile

(transfer data from target database to source database example all datafile,redolog file,trace file(not cotrolfile). also modify trace file as below. use SET and RESETLOGS parameter )

CREATE CONTROLFILE SET DATABASE "db11g" RESETLOGS
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/db11g/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/db11g/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/db11g/redo03.log' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/db11g/system01.dbf',
'/u01/app/oracle/oradata/db11g/sysaux01.dbf',
'/u01/app/oracle/oradata/db11g/undotbs01.dbf',
'/u01/app/oracle/oradata/db11g/users01.dbf'
CHARACTER SET WE8MSWIN1252
;

--
(save the file)

[oracle@oracle@example pfile]$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdupli force=y

Enter password for SYS:
[oracle@oracle@example pfile]$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/db_1

[oracle@oracle@example pfile]$ export ORACLE_SID=db11g

[oracle@oracle@example pfile]$ echo $ORACLE_SID
db11g

[oracle@oracle@example pfile]$sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 4 10:06:56 2013

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/admin/dupli/pfile/init.ora.6420139223';
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             260049280 bytes
Database Buffers          155189248 bytes
Redo Buffers                6094848 bytes

SQL> @'/u01/archivelog/control.sql';   (run modify tracefile)

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,OPEN_MODE from v$database;

NAME      OPEN_MODE
--------- --------------------
DB11G     READ WRITE

SQL>

   ********END*********

Certification Details:

Oracle Certified Professional, MySQL 8.0 Database Administrator MySQL Implementation Certified Associate MySQL HeatWave Implementation Certified Associate Rel 1 Oracle Cloud Infrastructure 2025 Certified Multicloud Architect Professional Snowpro Core Certified Oracle GoldenGate 12c Certified Implementation Specialist Oracle Cloud Infrastructure Foundations 2021 Oracle Autonomous Database Cloud 2021 Specialist Oracle Cloud Database Migration and Integration 2021 Certified Specialist Oracle Cloud Infrastructure Foundations 2020 Oracle Cloud Infrastructure 2019 Cloud Operations Certified Associate Oracle Cloud Infrastructure 2019 Architect professional Oracle Cloud Infrastructure 2019 Architect Associate Oracle Autonomous Database Cloud 2019 Specialist Oracle 12c OCP Oracle 11g OCP Oracle 10g OCP oracle 9i pl/sql OCA