active session accessing any particular table

SQL> col MODULE format A35

SQL> col PROGRAM format A35

SQL> l

  1* select sid,serial#,module,program from v$session where ROW_WAIT_OBJ# in (select object_id from dba_objects where object_type='TABLE' and object_name='TABLE_TEST')

SQL> /

       SID    SERIAL# MODULE                              PROGRAM

---------- ---------- ----------------------------------- -----------------------------------

      2033      18226 SQL*Plus                            sqlplus@testdb1 (TNS V1-V3)

SQL>

Oracle 11gR2 silent installation

If get error :

[FATAL] [INS-32038] The operating system group specified for central inventory (oraInventory) ownership is invalid .

Add UNIX_GROUP_NAME=oinstall in response file.

[oracle@test_1A database]$ export INVENTORY_LOCATION=/oravl01/oracle/oraInventory

[oracle@test_1A database]$ ./runInstaller -silent \

> -ignoreSysPrereqs \

> -ignorePrereq \

> -responseFile /oravl01/software/database/response/db_install.rsp \

> ORACLE_BASE=/oravl01/oracle \

> ORACLE_HOME=/oravl01/oracle/11.2.0 \

> ORACLE_HOME_NAME=OraDbHome11202 \

> oracle.install.option=INSTALL_DB_SWONLY \

> oracle.install.db.InstallEdition=EE \

> oracle.install.db.DBA_GROUP=dba \

> oracle.install.db.OPER_GROUP=dba \

> DECLINE_SECURITY_UPDATES=true \

> '-ignoreInternalDriverError'

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 3661 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 4095 MB    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2013-01-08_07-21-07AM. Please wait ...[oracle@test_1A database]$ [WARNING] [INS-32008] Oracle base location cant be same as the user home directory.

   CAUSE: The specified Oracle base is same as the user home directory.

   ACTION: Provide an Oracle base location other than the user home directory.

[WARNING] [INS-32055] The Central Inventory is located in the Oracle base.

   CAUSE: The Central Inventory is located in the Oracle base.

   ACTION: Oracle recommends placing this Central Inventory in a location outside the Oracle base directory.

You can find the log of this install session at:

 /oravl01/oracle/oraInventory/logs/installActions2013-01-08_07-21-07AM.log

The installation of Oracle Database 11g was successful.

Please check '/oravl01/oracle/oraInventory/logs/silentInstall2013-01-08_07-21-07AM.log' for more details.

As a root user, execute the following script(s):

        1. /oravl01/oracle/oraInventory/orainstRoot.sh

        2. /oravl01/oracle/11.2.0/root.sh

Successfully Setup Software.

[oracle@test_1A database]$

Recovering of tablespace without backup

We are not using recovery catalog for testing purpose.

C:\Users\jayva>rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Jan 2 13:07:42 2013

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

RMAN> connect target /

connected to target database: ORACLE (DBID=1662593856)

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name ORACLE are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFORACLE.ORA'; # default

Deleting all the current backup.

RMAN>

RMAN> delete noprompt backup;

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=125 device type=DISK

List of Backup Pieces

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

------- ------- --- --- ----------- ----------- ----------

4       4       1   1   AVAILABLE   DISK        C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\06NRK7ET_1_1

5       5       1   1   AVAILABLE   DISK        C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\07NRK7GL_1_1deleted backup piece

backup piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\06NRK7ET_1_1 RECID=4 STAMP=800726494 deleted backup piece

backup piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\07NRK7GL_1_1 RECID=5 STAMP=800726550 Deleted 2 objects

RMAN> list backup;

specification does not match any backup in the repository

RMAN>

Backup the fresh copy

RMAN> backup database plus archivelog;

Starting backup at 02-JAN-13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=102 RECID=99 STAMP=803654842

input archived log thread=1 sequence=103 RECID=100 STAMP=803654844

input archived log thread=1 sequence=104 RECID=101 STAMP=803654851

channel ORA_DISK_1: starting piece 1 at 02-JAN-13

channel ORA_DISK_1: finished piece 1 at 02-JAN-13

piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\0DNUDJ63_1_1 tag=TAG20130102T132731 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 02-JAN-13

Starting backup at 02-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=C:\APP\JAYVA\ORADATA\ORACLE\SYSTEM01.DBF

input datafile file number=00003 name=C:\APP\JAYVA\ORADATA\ORACLE\UNDOTBS01.DBF

input datafile file number=00002 name=C:\APP\JAYVA\ORADATA\ORACLE\SYSAUX01.DBF

input datafile file number=00004 name=C:\APP\JAYVA\ORADATA\ORACLE\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 02-JAN-13

channel ORA_DISK_1: finished piece 1 at 02-JAN-13

piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\0ENUDJ65_1_1 tag=TAG2

0130102T132733 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15

Finished backup at 02-JAN-13

Starting backup at 02-JAN-13

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=105 RECID=102 STAMP=803654929

channel ORA_DISK_1: starting piece 1 at 02-JAN-13

channel ORA_DISK_1: finished piece 1 at 02-JAN-13

piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\0FNUDJ8H_1_1 tag=TAG20130102T132849 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 02-JAN-13

Starting Control File and SPFILE Autobackup at 02-JAN-13

piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\C-1662593856-20130102-01 comment=NONE

Finished Control File and SPFILE Autobackup at 02-JAN-13

RMAN>

Create new tablespace and new table. This new tabelspace is not included in bakcup.

SQL> create tablespace jay DATAFILE 'C:\APP\JAYVA\ORADATA\ORACLE\jay01.dbf' size 1M autoextend on next 1M maxsize 100M;

Tablespace created.

SQL> create table backup_test (a number, b number) tablespace jay;

Table created.

SQL> begin

  2  for indx IN 1 .. 1000

  3  loop

  4  execute immediate 'insert into backup_test(a,b) values (:1, :2)' using indx, indx*2;

  5  end loop;

  6  commit;

  7  end;

  8  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL>

Although we have configure autobackup for controlfile but 11g onwards controfile autobackup not happening immediately after structure change.

So instead of waiting for auto backup, we are taking manual backup.

RMAN> backup current controlfile;

Starting backup at 02-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 02-JAN-13

channel ORA_DISK_1: finished piece 1 at 02-JAN-13

piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\0HNUDJCE_1_1 tag=TAG20130102T133054 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 02-JAN-13

Starting Control File and SPFILE Autobackup at 02-JAN-13

piece handle=C:\APP\JAYVA\PRODUCT\11.2.0\DBHOME_1\DATABASE\C-1662593856-20130102-02 comment=NONE

Finished Control File and SPFILE Autobackup at 02-JAN-13

RMAN>

Once done with controlfile backup, we will shutdown the DB and delete the controlfile and datafiles of 'JAY' tablespace.

C:\Users\jayva>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 2 14:49:39 2013

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

Enter user-name: / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

select open_mode from v$database

                      *

ERROR at line 1:

ORA-01507: database not mounted

SQL> conn / as sysdba

Connected.

SQL> shu immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  523108352 bytes

Fixed Size                  1375704 bytes

Variable Size             406848040 bytes

Database Buffers          109051904 bytes

Redo Buffers                5832704 bytes

ORA-00205: error in identifying control file, check alert log for more info

SQL>

Restoring the controlfile from autobackup.

RMAN> set DBID=1662593856

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 02-JAN-13

using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130102

channel ORA_DISK_1: AUTOBACKUP found: c-1662593856-20130102-02

channel ORA_DISK_1: restoring control file from AUTOBACKUP c-1662593856-20130102-02

channel ORA_DISK_1: control file restore from AUTOBACKUP complete

output file name=C:\APP\JAYVA\ORADATA\ORACLE\CONTROL01.CTL

output file name=C:\APP\JAYVA\ORADATA\ORACLE\CONTROL02.CTL

Finished restore at 02-JAN-13

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN>

Once done with restoration of controlfile, we can check for all tablespaces. Size for 'JAY' it shows here 0.

RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name ORACLE

List of Permanent Datafiles

===========================

File Size(MB) Tablespace           RB segs Datafile Name

---- -------- -------------------- ------- ------------------------

1    800      SYSTEM               ***     C:\APP\JAYVA\ORADATA\ORACLE\SYSTEM01.DBF

2    500      SYSAUX               ***     C:\APP\JAYVA\ORADATA\ORACLE\SYSAUX01.DBF

3    755      UNDOTBS1             ***     C:\APP\JAYVA\ORADATA\ORACLE\UNDOTBS01.DBF

4    5        USERS                ***     C:\APP\JAYVA\ORADATA\ORACLE\USERS01.DBF

5    0        JAY                  ***     C:\APP\JAYVA\ORADATA\ORACLE\JAY01.DBF

List of Temporary Files

=======================

File Size(MB) Tablespace           Maxsize(MB) Tempfile Name

---- -------- -------------------- ----------- --------------------

1    20       TEMP                 32767       C:\APP\JAYVA\ORADATA\ORACLE\TEMP01.DBF

RMAN>

RMAN> alter database open;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 01/02/2013 14:53:29

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 01/02/2013 14:59:14

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1: 'C:\APP\JAYVA\ORADATA\ORACLE\SYSTEM01.DBF'

RMAN>

RMAN>

RMAN>  recover database;

Starting recover at 02-JAN-13

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 01/02/2013 15:02:19

RMAN-06094: datafile 5 must be restored

RMAN> restore tablespace jay;

Starting restore at 02-JAN-13

using channel ORA_DISK_1

creating datafile file number=5 name=C:\APP\JAYVA\ORADATA\ORACLE\JAY01.DBF

restore not done; all files read only, offline, or already restored

Finished restore at 02-JAN-13

RMAN> recover tablespace jay;

Starting recover at 02-JAN-13

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 01/02/2013 15:03:31

RMAN-06067: RECOVER DATABASE required with a backup or created control file

RMAN> recover database;

Starting recover at 02-JAN-13

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 105 is already on disk as file C:\APP\JAYVA\ORADATA\ORACLE\REDO03.LOG

archived log for thread 1 with sequence 106 is already on disk as file C:\APP\JAYVA\ORADATA\ORACLE\REDO01.LOG

archived log file name=C:\APP\JAYVA\ORADATA\ORACLE\REDO03.LOG thread=1 sequence=105

archived log file name=C:\APP\JAYVA\ORADATA\ORACLE\REDO01.LOG thread=1 sequence=106

media recovery complete, elapsed time: 00:00:05

Finished recover at 02-JAN-13

RMAN> alter database open resetlogs;

database opened

RMAN>

SQL> conn / as sysdba

Connected.

SQL> select count(1) from backup_test;

  COUNT(1)

----------

      1000

SQL>