How to Kill Locked Objects and Session Oracle 11g Database

select * from v$locked_object

1- SELECT sid FROM v$lock WHERE id1=51891
2- SELECT sid, serial# from v$session where sid=155
3- ALTER SYSTEM KILL SESSION '155,1098' ;


Step-1

SELECT object_id FROM dba_objects WHERE object_name='EMP';
 OBJECT_ID
----------
   7401242

Step-2 
If there are no locks present for the table ‘EMP’ this query won’t return any values.
SELECT sid FROM v$lock WHERE id1=7401242
SID
----------
3434

Step-3
SELECT sid, serial# from v$session where sid=3434
SID        SERIAL#
---------- ----------
3434       92193

Step-4
ALTER SYSTEM KILL SESSION '3434,92193' ;*/

Step By Step Oracle 11g RMAN Configuration with Incremental and whole Backup

STEP-1

Logon to SYS user

First of all make sure your database is running ARCHIVELOG mode. If it is not running then logon to sys user and follow bellow code.

SQL> conn sys as sysdba;

Enter password:

Connected.

SQL> STARTUP MOUNT EXCLUSIVE;

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> ALTER DATABASE ARCHIVELOG;

ALTER DATABASE ARCHIVELOG

*

ERROR at line 1:

ORA-01126: database must be mounted in this instance and not open in any

instance

SQL>  archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Current log sequence           3

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                  1247876 bytes

Variable Size              75498876 bytes

Database Buffers           83886080 bytes

Redo Buffers                7139328 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     1

Next log sequence to archive   3

Current log sequence           3

SQL>

SQL> create tablespace rec_catalog

     datafile ‘E:\oracle\product\10.2.0\db_1\oradata\klm\rec_catalog.dbf’

     size 10M autoextend on

     extent management local uniform size 1M;

Next we create the recovery catalog owner. The user SYS cannot be the owner of the recovery catalog.

SQL> create user rcat identified by rcat   default tablespace rec_catalog

    quota unlimited on rec_catalog;

User created.

SQL> GRANT connect, resource, recovery_catalog_owner TO rcat;

Grant succeeded.

SQL> grant recovery_catalog_owner to rcat;

Grant succeeded.

The role RECOVERY_CATALOG_OWNER has all of the privileges need to query and maintain the recovery catalog.

SQL> select privilege from dba_sys_privs where grantee = 'RECOVERY_CATALOG_OWNER';

PRIVILEGE

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

CREATE SYNONYM

CREATE CLUSTER

ALTER SESSION

CREATE DATABASE LINK

CREATE PROCEDURE

CREATE SEQUENCE

CREATE TABLE

CREATE SESSION

CREATE TYPE

CREATE VIEW

CREATE TRIGGER

STEP-2

Now open CMD or Command Prompt

C:\>rman catalog=rcat/rcat@klm

connected to recovery catalog database

RMAN> create catalog tablespace "REC_CATALOG";

recovery catalog created

RMAN> exit

Recovery Manager complete.

C:\>rman catalog=rcat/rcat@KLM target=sys/sys@KLM

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN> exit

Recovery Manager complete.

STEP-3

Configure the persistent parameters.

RMAN> configure retention policy to recovery window of 7 days;

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> configure default device type to disk;

new RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> configure channel device type disk format 'F:\ssuet_backup\rmanbackup\Backup%d_DB_%U_%S_%P';

new RMAN configuration parameters:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'F:\Rmanbackup\Backup%d_DB_%U_%S_%P';

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

STEP-4

Now We have created two .rcv files backup.rcv and backup1.rcv with two Batch file daily and weekly basis backup and add in schedule of windows 2003 server.

WEEKLY BACKUP FILE SCRIPT

Open notepad and write bellow code and save it bellow name with .bat extension.

weeklybackupRMAN.bat

echo off

rman target=sys/sys@klm cmdfile F:\Rmanbackup\backup1.rcv

Now open notepad and write bellow rman script and save it with bellow with .rcv extension.

backup1.rcv

run {

backup incremental level 0 DATABASE plus ARCHIVELOG DELETE INPUT  TAG WEEKLY_BACKUP;

sql "alter system archive log current";

DELETE NOPROMPT OBSOLETE;

backup spfile;

backup current controlfile;

}

DAILY BACKUP FILE SCRIPT

Open notepad and write bellow code and save it bellow name with .bat extension.

dailybackupRMAN.bat

echo off

rman catalog=rcat/rcat@klm target=sys/sys@klm cmdfile F:\Rmanbackup\backup.rcv

backup.rcv

Now open notepad and write bellow rman script and save it with bellow with .rcv extension.

run {

backup incremental level 1 DATABASE plus ARCHIVELOG DELETE INPUT  TAG DAILY_BACKUP;

sql "alter system archive log current";

DELETE NOPROMPT OBSOLETE;

backup spfile;

backup current controlfile;

}

Configure EM (Enterprise Manager ) in Oracle 11g

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Administrator>emctl status dbconsole
Environment variable ORACLE_SID not defined. Please define it.

C:\Users\Administrator>set ORACLE_SID=ORCL

C:\Users\Administrator>emctl status dbconsole
OC4J Configuration issue. C:\oracle\product\10.2.0\db_1/oc4j/j2ee/OC4J_DBConsole
_192.168.4.19_ORCL not found.

C:\Users\Administrator>emca -config dbcontrol db

STARTED EMCA at Mar 31, 2016 8:05:17 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: ORCL
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for DBSNMP user: Invalid username/password.
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ C:\oracle\product\10.2.0\db_1

Database hostname ................ <your PC IP Address>
Listener port number ................ 1521
Database SID ................ ORCL
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: yes
Mar 31, 2016 8:07:45 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\orcl\emca_2016-03-31_08-05-17-AM.log.
Mar 31, 2016 8:07:56 AM oracle.sysman.emcp.util.PlatformInterface executeCommand

WARNING: Error executing CMD /C C:\oracle\product\10.2.0\db_1\bin\emctl.bat depl
oy dbconsole C:\oracle\product\10.2.0\db_1\192.168.4.19_ORCL <your PC IP Address>:3938 <your PC IP Address> ORCL
Mar 31, 2016 8:07:56 AM oracle.sysman.emcp.EMConfig perform
SEVERE: Error instantiating EM configuration files
Refer to the log file at C:\oracle\product\10.2.0\db_1\cfgtoollogs\emca\orcl\emc
a_2016-03-31_08-05-17-AM.log for more details.
Could not complete the configuration. Refer to the log file at C:\oracle\product
\10.2.0\db_1\cfgtoollogs\emca\orcl\emca_2016-03-31_08-05-17-AM.log for more deta
ils.

C:\Users\Administrator>emctl stop dbconsole
Unable to determine local host from URL REPOSITORY_URL=http://<your PC IP Address>:%EM_U
PLOAD_PORT%/em/upload/
 .

C:\Users\Administrator>emctl start dbconsole
Unable to determine local host from URL REPOSITORY_URL=http://<your PC IP Address>:%EM_U
PLOAD_PORT%/em/upload/
 .

C:\Users\Administrator>emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Mar 31, 2016 8:10:46 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: ORCL
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: yes
Mar 31, 2016 8:11:01 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\orcl\emca_2016-03-31_08-10-46-AM.log.
Mar 31, 2016 8:11:04 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 31, 2016 8:11:15 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Mar 31, 2016 8:11:55 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 31, 2016 8:11:55 AM

C:\Users\Administrator>emctl stop dbconsole
OC4J Configuration issue. C:\oracle\product\10.2.0\db_1/oc4j/j2ee/OC4J_DBConsole
_<your PC IP Address>_ORCL not found.

C:\Users\Administrator>emca -config dbcontrol db -repos create

STARTED EMCA at Mar 31, 2016 8:20:58 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: ORCL
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ C:\oracle\product\10.2.0\db_1

Database hostname ................ <your PC IP Address>
Listener port number ................ 1521
Database SID ................ ORCL
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: yes
Mar 31, 2016 8:22:06 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\orcl\emca_2016-03-31_08-20-58-AM.log.
Mar 31, 2016 8:22:14 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 31, 2016 8:23:33 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 31, 2016 8:23:37 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 31, 2016 8:24:17 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 31, 2016 8:24:17 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://<your PC IP Address>:5500/em <<<<<<
<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 31, 2016 8:24:17 AM

C:\Users\Administrator>emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Mar 31, 2016 8:24:28 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: ORCL
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: yes
Mar 31, 2016 8:24:41 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at C:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\orcl\emca_2016-03-31_08-24-28-AM.log.
Mar 31, 2016 8:24:44 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 31, 2016 8:25:25 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Mar 31, 2016 8:26:01 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 31, 2016 8:26:02 AM

C:\Users\Administrator>