About Me

Thursday 28 March 2013

Database Instance Startup Failes Due To Wrong Values Of Kernerl Parameters

When we start our database instance it can fail with the following error

ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper


Let us describe this error 

]$oerr ora 27154

ORA-27154: post/wait create failed 
Cause: internal error, multiple post/wait creates attempted simultaneously
Action: check errno and contact Oracle Support
 
You see this error when number of oracle related process exceeds the limitations set by kernel parameters semaphores. You will face this error generally when you start multiple instances on the same server .  If you set the proper value of kernel.sem then it will allow us to start desired number of processes.

Set the values of semaphores as following .

kernel.sem = 250 32000 100 128

Start the instance now  

Tuesday 26 March 2013

DATABASE POINT IN TIME RECOVERY (DBPITR) USING TIME OR SCN NUMBER

In continuity to my last post, (Read it here DATABASE POINT IN TIME RECOVERY (DBPITR) USING LOG SEQUENCE NUMBER), We can use "TIME" or "SCN number" in place of log sequence number.
Use the below query to know about log sequence number along with SCN number and corresponding time when the SCN was generated .


SQL> select sequence#,first_change#,to_char(first_time,'dd/mon/yy hh24:mi:ss') time from v$log_history;

 SEQUENCE# FIRST_CHANGE# TIME
---------- ------------- ------------------
         1        940976 20/feb/13 11:29:24
         2        947888 20/feb/13 11:29:55
         3        971744 21/feb/13 22:31:35
         4        999629 22/feb/13 12:13:47
         5       1022011 26/mar/13 10:22:03
         6       1022811 26/mar/13 10:32:09
         7       1022817 26/mar/13 10:32:21
         8       1022824 26/mar/13 10:32:32
         9       1022829 26/mar/13 10:32:41
        10       1022834 26/mar/13 10:32:49
        11       1023279 26/mar/13 10:34:21

 SEQUENCE# FIRST_CHANGE# TIME
---------- ------------- ------------------
        12       1023833 26/mar/13 10:34:31
        13       1024646 26/mar/13 10:34:37
        14       1025617 26/mar/13 10:41:04
        15       1025743 26/mar/13 10:41:08
        16       1025802 26/mar/13 10:41:11
        17       1025878 26/mar/13 10:41:14
        18       1025927 26/mar/13 10:41:17
        19       1025991 26/mar/13 10:41:20
        20       1026518 26/mar/13 10:55:40
        21       1026673 26/mar/13 10:55:44
        22       1026885 26/mar/13 10:55:48

 SEQUENCE# FIRST_CHANGE# TIME
---------- ------------- ------------------
        23       1027046 26/mar/13 10:55:52
        24       1027214 26/mar/13 10:55:54
        25       1027918 26/mar/13 10:59:06
        26       1027951 26/mar/13 10:59:09
        27       1027994 26/mar/13 10:59:12
        28       1028038 26/mar/13 10:59:15
        29       1028081 26/mar/13 10:59:18
        30       1028119 26/mar/13 10:59:21
        31       1028753 26/mar/13 11:11:54
      

        32       1028793 26/mar/13 11:11:55
        33       1028833 26/mar/13 11:11:57

 SEQUENCE# FIRST_CHANGE# TIME
---------- ------------- ------------------
        34       1028875 26/mar/13 11:12:00
        35       1028918 26/mar/13 11:12:03
        36       1028956 26/mar/13 11:12:06
        37       1028994 26/mar/13 11:12:09
        38       1029036 26/mar/13 11:12:12
        39       1029079 26/mar/13 11:12:15
        40       1029118 26/mar/13 11:12:19
        41       1029161 26/mar/13 11:12:21

41 rows selected.


We can see that Archive sequence 30 was generated at 26/mar/13 10:59:21 with scn number 1028119.

1. Using time 26/mar/13 11:11:54(for DBPTIR we should take next archive sequence number hence i am taking next time when archive 31 was generated )


RMAN> run
2> {
3> startup nomount;
4> restore controlfile from 'C:\oradata\rmanbkp\CTL0CO5G2HR_1_1.RBF';
5> mount database;
6> set until time "to_date('26/mar/13 11:11:54','dd/mon/yy hh24:mi:ss')";
7> allocate channel dsk1 type disk;
8> allocate channel dsk2 type disk;
9> allocate channel dsk3 type disk;
10> restore database;
11> recover database;
12> sql "alter database open resetlogs";
13> }

Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1373964 bytes
Variable Size                213911796 bytes
Database Buffers              41943040 bytes
Redo Buffers                   6410240 bytes

Starting restore at 26-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=C:\ORADATA\ORCL\CONTROL01.CTL
output file name=C:\ORADATA\ORCL\CONTROL02.CTL
Finished restore at 26-MAR-13

database mounted
released channel: ORA_DISK_1

executing command: SET until clause

allocated channel: dsk1
channel dsk1: SID=63 device type=DISK

allocated channel: dsk2
channel dsk2: SID=129 device type=DISK

allocated channel: dsk3
channel dsk3: SID=192 device type=DISK

Starting restore at 26-MAR-13

channel dsk1: starting datafile backup set restore
channel dsk1: specifying datafile(s) to restore from backup set
channel dsk1: restoring datafile 00002 to C:\ORADATA\ORCL\SYSAUX01.DBF
channel dsk1: restoring datafile 00004 to C:\ORADATA\ORCL\USERS01.DBF
channel dsk1: reading from backup piece C:\ORADATA\RMANBKP\05O5G2F8_1_1.RBF
channel dsk2: starting datafile backup set restore
channel dsk2: specifying datafile(s) to restore from backup set
channel dsk2: restoring datafile 00001 to C:\ORADATA\ORCL\SYSTEM01.DBF
channel dsk2: restoring datafile 00003 to C:\ORADATA\ORCL\UNDOTBS01.DBF
channel dsk2: reading from backup piece C:\ORADATA\RMANBKP\04O5G2F8_1_1.RBF
channel dsk1: piece handle=C:\ORADATA\RMANBKP\05O5G2F8_1_1.RBF tag=TAG20130326T105431
channel dsk1: restored backup piece 1
channel dsk1: restore complete, elapsed time: 00:00:55
channel dsk2: piece handle=C:\ORADATA\RMANBKP\04O5G2F8_1_1.RBF tag=TAG20130326T105431
channel dsk2: restored backup piece 1
channel dsk2: restore complete, elapsed time: 00:01:05
Finished restore at 26-MAR-13

Starting recover at 26-MAR-13

starting media recovery

archived log for thread 1 with sequence 19 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000019_0807881364.0001
archived log for thread 1 with sequence 20 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000020_0807881364.0001
archived log for thread 1 with sequence 21 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000021_0807881364.0001
archived log for thread 1 with sequence 22 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000022_0807881364.0001
archived log for thread 1 with sequence 23 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000023_0807881364.0001
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000019_0807881364.0001 thread=1 sequence=19
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000020_0807881364.0001 thread=1 sequence=20
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000021_0807881364.0001 thread=1 sequence=21
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000022_0807881364.0001 thread=1 sequence=22
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000023_0807881364.0001 thread=1 sequence=23
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000024_0807881364.0001 thread=1 sequence=24
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000025_0807881364.0001 thread=1 sequence=25
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000026_0807881364.0001 thread=1 sequence=26
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000027_0807881364.0001 thread=1 sequence=27
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000028_0807881364.0001 thread=1 sequence=28
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000029_0807881364.0001 thread=1 sequence=29
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000030_0807881364.0001 thread=1 sequence=30
media recovery complete, elapsed time: 00:00:07
Finished recover at 26-MAR-13

sql statement: alter database open resetlogs
released channel: dsk1
released channel: dsk2
released channel: dsk3


SQL> select count(*) from u1.emp;

  COUNT(*)
----------
    458752


2. Using SCN number when Archive 31 was generated (For DBPTIR we take next archive sequence number).


RMAN> run
2> {
3> startup nomount;
4> restore controlfile from 'C:\oradata\rmanbkp\CTL0CO5G2HR_1_1.RBF';
5> mount database;
6> set until scn 1028753;
7> allocate channel dsk1 type disk;
8> allocate channel dsk2 type disk;
9> allocate channel dsk3 type disk;
10> restore database;
11> recover database;
12> sql "alter database open resetlogs";
13> }

Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1373964 bytes
Variable Size                201328884 bytes
Database Buffers              54525952 bytes
Redo Buffers                   6410240 bytes

Starting restore at 26-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=C:\ORADATA\ORCL\CONTROL01.CTL
output file name=C:\ORADATA\ORCL\CONTROL02.CTL
Finished restore at 26-MAR-13

database mounted
released channel: ORA_DISK_1

executing command: SET until clause

allocated channel: dsk1
channel dsk1: SID=63 device type=DISK

allocated channel: dsk2
channel dsk2: SID=129 device type=DISK

allocated channel: dsk3
channel dsk3: SID=192 device type=DISK

Starting restore at 26-MAR-13

channel dsk1: starting datafile backup set restore
channel dsk1: specifying datafile(s) to restore from backup set
channel dsk1: restoring datafile 00002 to C:\ORADATA\ORCL\SYSAUX01.DBF
channel dsk1: restoring datafile 00004 to C:\ORADATA\ORCL\USERS01.DBF
channel dsk1: reading from backup piece C:\ORADATA\RMANBKP\05O5G2F8_1_1.RBF
channel dsk2: starting datafile backup set restore
channel dsk2: specifying datafile(s) to restore from backup set
channel dsk2: restoring datafile 00001 to C:\ORADATA\ORCL\SYSTEM01.DBF
channel dsk2: restoring datafile 00003 to C:\ORADATA\ORCL\UNDOTBS01.DBF
channel dsk2: reading from backup piece C:\ORADATA\RMANBKP\04O5G2F8_1_1.RBF
channel dsk1: piece handle=C:\ORADATA\RMANBKP\05O5G2F8_1_1.RBF tag=TAG20130326T105431
channel dsk1: restored backup piece 1
channel dsk1: restore complete, elapsed time: 00:00:55
channel dsk2: piece handle=C:\ORADATA\RMANBKP\04O5G2F8_1_1.RBF tag=TAG20130326T105431
channel dsk2: restored backup piece 1
channel dsk2: restore complete, elapsed time: 00:01:05
Finished restore at 26-MAR-13

Starting recover at 26-MAR-13

starting media recovery

archived log for thread 1 with sequence 19 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000019_0807881364.0001
archived log for thread 1 with sequence 20 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000020_0807881364.0001
archived log for thread 1 with sequence 21 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000021_0807881364.0001
archived log for thread 1 with sequence 22 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000022_0807881364.0001
archived log for thread 1 with sequence 23 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000023_0807881364.0001
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000019_0807881364.0001 thread=1 sequence=19
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000020_0807881364.0001 thread=1 sequence=20
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000021_0807881364.0001 thread=1 sequence=21
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000022_0807881364.0001 thread=1 sequence=22
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000023_0807881364.0001 thread=1 sequence=23
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000024_0807881364.0001 thread=1 sequence=24
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000025_0807881364.0001 thread=1 sequence=25
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000026_0807881364.0001 thread=1 sequence=26
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000027_0807881364.0001 thread=1 sequence=27
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000028_0807881364.0001 thread=1 sequence=28
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000029_0807881364.0001 thread=1 sequence=29
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000030_0807881364.0001 thread=1 sequence=30
media recovery complete, elapsed time: 00:00:08
Finished recover at 26-MAR-13

sql statement: alter database open resetlogs
released channel: dsk1
released channel: dsk2
released channel: dsk3

SQL> select count(*) from u1.emp;

  COUNT(*)
----------
    458752 



Please let me know your suggestions and opinion.

DATABASE POINT IN TIME RECOVERY (DBPITR)

Suppose a situation when some rows are inserted in table by mistake and you have committed also. Now You want to revert back the table to previous state when unwanted rows were not inserted.   
Lets assume that you have some 458752 rows in the emp table and you have inserted more 458752 in the same table making total rows 917504.
Later you realized that you want to revert back newly inserted 458752 rows.

Another situation may be when you have dropped a table by mistake and you want your past database state when the table was present.

Or suppose you are upgrading the database/application and upgrade failed. Now you want to bring back the database state which was before upgrade.

For all these situations we can restore the database and can do incomplete recovery up to our desired point of time known as " DATABASE POINT IN TIME RECOVERY (DBPITR) ".

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            c:\oradata\orcl\arch
Oldest online log sequence     29
Next log sequence to archive   30
Current log sequence           30
SQL> conn u1/u1
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
    458752


Note that when emp table was having only 458752 then log sequence number was 30.

Now insert the rows in the table.

SQL> insert into emp select * from emp;

458752 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from emp;

  COUNT(*)
----------
    917504

SQL> conn sys/sys as sysdba
Connected.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            c:\oradata\orcl\arch
Oldest online log sequence     40
Next log sequence to archive   41
Current log sequence           41

SQL> select sequence#,thread#,first_change#, to_char(first_time,'DD/MON/YY HH24:MI:SS') TIME from v$log order by TIME;

 SEQUENCE#    THREAD# FIRST_CHANGE# TIME
---------- ---------- ------------- ------------------
        40          1       1029118 26/MAR/13 11:12:19
        41          1       1029161 26/MAR/13 11:12:21


After inserting the news rows total Number of rows became 917504 and log sequence went from 30 to 41.
If we want to have only 458752 then we will have to restore the backup and then do incomplete recovery of database  upto archive log sequence number 30. So now lets take the rman backup of database as following.

RMAN> run
2> {
3> allocate channel dsk1 type disk;
4> allocate channel dsk2 type disk;
5> allocate channel dsk3 type disk;
6> backup database format 'c:\oradata\rmanbkp\%U.rbf';
7> sql "alter system switch logfile";
8> backup archivelog all format 'c:\oradata\rmanbkp\Arch%U.rbf';
9> backup current controlfile format 'c:\oradata\rmanbkp\CTL%U.rbf';
10> }


We have taken backup of full database with all archive logs and current controlfile. Now remove the current database and start the restore using rman.

RMAN> startup nomount

Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1373964 bytes
Variable Size                213911796 bytes
Database Buffers              41943040 bytes
Redo Buffers                   6410240 bytes

RMAN> restore controlfile from 'C:\oradata\rmanbkp\CTL0CO5G2HR_1_1.RBF';

Starting restore at 26-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=C:\ORADATA\ORCL\CONTROL01.CTL
output file name=C:\ORADATA\ORCL\CONTROL02.CTL
Finished restore at 26-MAR-13

RMAN> mount database;

database mounted
released channel: ORA_DISK_1


Now the database is mounted and and we will have to restore and recover the database upto archive log sequence 30 only . For this we will have to use "UNTIL SEQUENCE" clause of RMAN. If you want recover the database upto sequence 30 then set the command "set until sequence 31;".

RMAN> run {
2>  set until sequence 31;
3>  allocate channel dsk1 type disk;
4>  allocate channel dsk2 type disk;
5>  allocate channel dsk3 type disk;
6>  restore database;
7>  recover database;
8>  }

executing command: SET until clause

allocated channel: dsk1
channel dsk1: SID=63 device type=DISK

allocated channel: dsk2
channel dsk2: SID=129 device type=DISK

allocated channel: dsk3
channel dsk3: SID=193 device type=DISK

Starting restore at 26-MAR-13

channel dsk1: starting datafile backup set restore
channel dsk1: specifying datafile(s) to restore from backup set
channel dsk1: restoring datafile 00002 to C:\ORADATA\ORCL\SYSAUX01.DBF
channel dsk1: restoring datafile 00004 to C:\ORADATA\ORCL\USERS01.DBF
channel dsk1: reading from backup piece C:\ORADATA\RMANBKP\05O5G2F8_1_1.RBF
channel dsk2: starting datafile backup set restore
channel dsk2: specifying datafile(s) to restore from backup set
channel dsk2: restoring datafile 00001 to C:\ORADATA\ORCL\SYSTEM01.DBF
channel dsk2: restoring datafile 00003 to C:\ORADATA\ORCL\UNDOTBS01.DBF
channel dsk2: reading from backup piece C:\ORADATA\RMANBKP\04O5G2F8_1_1.RBF
channel dsk1: piece handle=C:\ORADATA\RMANBKP\05O5G2F8_1_1.RBF tag=TAG20130326T105431
channel dsk1: restored backup piece 1
channel dsk1: restore complete, elapsed time: 00:00:56
channel dsk2: piece handle=C:\ORADATA\RMANBKP\04O5G2F8_1_1.RBF tag=TAG20130326T105431
channel dsk2: restored backup piece 1
channel dsk2: restore complete, elapsed time: 00:01:06
Finished restore at 26-MAR-13

Starting recover at 26-MAR-13

starting media recovery

archived log for thread 1 with sequence 19 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000019_0807881364.0001
archived log for thread 1 with sequence 20 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000020_0807881364.0001
archived log for thread 1 with sequence 21 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000021_0807881364.0001
archived log for thread 1 with sequence 22 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000022_0807881364.0001
archived log for thread 1 with sequence 23 is already on disk as file C:\ORADATA\ORCL\ARCH\ARC0000000023_0807881364.0001
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000019_0807881364.0001 thread=1 sequence=19
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000020_0807881364.0001 thread=1 sequence=20
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000021_0807881364.0001 thread=1 sequence=21
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000022_0807881364.0001 thread=1 sequence=22
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000023_0807881364.0001 thread=1 sequence=23
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000024_0807881364.0001 thread=1 sequence=24
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000025_0807881364.0001 thread=1 sequence=25
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000026_0807881364.0001 thread=1 sequence=26
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000027_0807881364.0001 thread=1 sequence=27
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000028_0807881364.0001 thread=1 sequence=28
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000029_0807881364.0001 thread=1 sequence=29
archived log file name=C:\ORADATA\ORCL\ARCH\ARC0000000030_0807881364.0001 thread=1 sequence=30
media recovery complete, elapsed time: 00:00:06
Finished recover at 26-MAR-13
released channel: dsk1
released channel: dsk2
released channel: dsk3


If you see the RMAN log then observe that rman has used archive upto sequence 30 only for recovery and have ignored further archives because we have used SET UNTIL SEQUENCE command .

RMAN> sql "alter database open resetlogs";

sql statement: alter database open resetlogs


C:\oradata\orcl>sqlplus sys/sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 26 12:30:46 2013

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


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> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            c:\oradata\orcl\arch
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
SQL> conn u1/u1
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
    458752


Please leave your comments and suggestions for betterment of this post.