Steps to restore oracle database using RMAN & resetting the incarnation.

Steps to restore oracle database using RMAN & resetting the incarnation.

Step#1
Connect to the rman & identify the backup piece
<server_name>:<OSuser_name> 49> rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Jul 17 22:21:14 2016

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

connected to target database: <DB_name> (not mounted)

RMAN> connect catalog rman/****@RCAT;

connected to recovery catalog database

Database needs to be in nomunt state.

Step#2
Restore the ctl file
RMAN> restore controlfile from '/tmp/DD_Backup/<server_name>/<DB_name>/database/<DB_name>_ctl_spfile_c-238613855-20160717-05';

Starting restore at 17-JUL-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=317 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=380 device type=DISK

channel ORA_DISK_2: skipped, AUTOBACKUP already found
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
output file name=/oracle/<DB_name>/control1/cntrl<DB_name>.dbf
output file name=/oracle/<DB_name>/control2/cntrl<DB_name>.dbf
output file name=/oracle/<DB_name>/control3/cntrl<DB_name>.dbf
Finished restore at 17-JUL-16

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1
released channel: ORA_DISK_2
step #3 check the incarnation & reset the incarnation to use old backups
RMAN> list incarnation of database <DB_name>;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
3038857 3038858 <DB_name>      238613855        PARENT  1          10-JAN-11
3038857 25197016 <DB_name>      238613855        CURRENT 220087209  17-JUL-16

RMAN> reset database to incarnation 3038858;

database reset to incarnation 3038858

RMAN>
Step#3 restore the DB where the app team wants to rollforward
RMAN> run
{
allocate channel dev1 type disk;
allocate channel dev2 type disk;
set until time "to_date('2016-07-17:09:00:00', 'yyyy-mm-dd:hh24:mi:ss')";
restore database;
recover database;
release channel dev1;
release channel dev2;
}
2> 3> 4> 5> 6> 7> 8> 9> 10>
allocated channel: dev1
channel dev1: SID=317 device type=DISK

allocated channel: dev2
channel dev2: SID=380 device type=DISK

executing command: SET until clause

Starting restore at 17-JUL-16

channel dev1: starting datafile backup set restore
channel dev1: specifying datafile(s) to restore from backup set
channel dev1: restoring datafile 00002 to /oracle/<DB_name>/sapdata888/undo_1/undo.data1
channel dev1: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6307_1_917424023_53rathsn_1_1
channel dev2: starting datafile backup set restore
channel dev2: specifying datafile(s) to restore from backup set
channel dev2: restoring datafile 00005 to /oracle/<DB_name>/sapdata1/user_1/user.data1
channel dev2: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6309_1_917424078_55rathue_1_1
channel dev2: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6309_1_917424078_55rathue_1_1 tag=TAG20160717T080023
channel dev2: restored backup piece 1
channel dev2: restore complete, elapsed time: 00:00:26
channel dev2: starting datafile backup set restore
channel dev2: specifying datafile(s) to restore from backup set
channel dev2: restoring datafile 00001 to /oracle/<DB_name>/sapdata777/system_1/system.data1
channel dev2: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6310_1_917424086_56rathum_1_1
channel dev2: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6310_1_917424086_56rathum_1_1 tag=TAG20160717T080023
channel dev2: restored backup piece 1
channel dev2: restore complete, elapsed time: 00:00:35
channel dev2: starting datafile backup set restore
channel dev2: specifying datafile(s) to restore from backup set
channel dev2: restoring datafile 00003 to /oracle/<DB_name>/sapdata777/sysaux_1/sysaux.data1
channel dev2: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6311_1_917424181_57rati1l_1_1
channel dev1: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6307_1_917424023_53rathsn_1_1 tag=TAG20160717T080023
channel dev1: restored backup piece 1
channel dev1: restore complete, elapsed time: 00:01:04
channel dev1: starting datafile backup set restore
channel dev1: specifying datafile(s) to restore from backup set
channel dev1: restoring datafile 00004 to /oracle/<DB_name>/sapdata1/sr3db_1/sr3db.data1
channel dev1: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6308_1_917424023_54rathsn_1_1
channel dev2: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6311_1_917424181_57rati1l_1_1 tag=TAG20160717T080023
channel dev2: restored backup piece 1
channel dev2: restore complete, elapsed time: 00:00:27
channel dev1: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/database/DF_<DB_name>_2016_07_17_08_00_6308_1_917424023_54rathsn_1_1 tag=TAG20160717T080023
channel dev1: restored backup piece 1
channel dev1: restore complete, elapsed time: 00:02:15
Finished restore at 17-JUL-16

Starting recover at 17-JUL-16

starting media recovery

archived log for thread 1 with sequence 16600 is already on disk as file /oracle/<DB_name>/oraarch/<DB_name>arch1_16600_740086239.dbf
channel dev1: starting archived log restore to default destination
channel dev1: restoring archived log
archived log thread=1 sequence=16598
channel dev1: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/archivelog/AL_<DB_name>_2016_07_17_08_00_6313_1_917424329_59rati69_1_1
channel dev2: starting archived log restore to default destination
channel dev2: restoring archived log
archived log thread=1 sequence=16599
channel dev2: reading from backup piece /tmp/DD_Backup/<server_name>/<DB_name>/archivelog/AL_<DB_name>_2016_07_17_08_00_6314_1_917424329_5arati69_1_1
channel dev2: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/archivelog/AL_<DB_name>_2016_07_17_08_00_6314_1_917424329_5arati69_1_1 tag=TAG20160717T080528
channel dev2: restored backup piece 1
channel dev2: restore complete, elapsed time: 00:00:02
channel dev1: piece handle=/tmp/DD_Backup/<server_name>/<DB_name>/archivelog/AL_<DB_name>_2016_07_17_08_00_6313_1_917424329_59rati69_1_1 tag=TAG20160717T080528
channel dev1: restored backup piece 1
channel dev1: restore complete, elapsed time: 00:00:08
archived log file name=/oracle/<DB_name>/oraarch/<DB_name>arch1_16598_740086239.dbf thread=1 sequence=16598
archived log file name=/oracle/<DB_name>/oraarch/<DB_name>arch1_16599_740086239.dbf thread=1 sequence=16599
archived log file name=/oracle/<DB_name>/oraarch/<DB_name>arch1_16600_740086239.dbf thread=1 sequence=16600
media recovery complete, elapsed time: 00:00:05
Finished recover at 17-JUL-16

released channel: dev1

released channel: dev2

RMAN> alter database open RESETLOGS;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

step#4
Take a full backup of database once database is open.

step by step to drop table space in oracle SAP database using BRTOOLS

SERVER_NAME:oraTEST 10> brtools
BR0651I BRTOOLS 7.00 (32)

BR0280I BRTOOLS time stamp: 2016-05-04 14.07.09
BR0656I Choice menu 1 - please make a selection
-------------------------------------------------------------------------------
BR*Tools main menu

 1 = Instance management
 2 - Space management
 3 - Segment management
 4 - Backup and database copy
 5 - Restore and recovery
 6 - Check and verification
 7 - Database statistics
 8 - Additional functions
 9 - Exit program

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
2
BR0280I BRTOOLS time stamp: 2016-05-04 14.07.10
BR0663I Your choice: '2'

BR0280I BRTOOLS time stamp: 2016-05-04 14.07.10
BR0656I Choice menu 5 - please make a selection
-------------------------------------------------------------------------------
Database space management

 1 = Extend tablespace
 2 - Create tablespace
 3 - Drop tablespace
 4 - Alter tablespace
 5 - Alter data file
 6 - Move data file
 7 - Additional space functions
 8 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2016-05-04 14.07.17
BR0663I Your choice: '3'

BR0280I BRTOOLS time stamp: 2016-05-04 14.07.17
BR0657I Input menu 83 - please check/enter input values
-------------------------------------------------------------------------------
BRSPACE options for drop tablespace

 1 - BRSPACE profile (profile) ...... [initTEST.sap]
 2 - Database user/password (user) .. [/]
 3 ~ Tablespace name (tablespace) ... []
 4 - Confirmation mode (confirm) .... [yes]
 5 - Scrolling line count (scroll) .. [20]
 6 - Message language (language) .... [E]
 7 - BRSPACE command line (command) . [-p initTEST.sap -s 20 -l E -f tsdrop]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2016-05-04 14.07.19
BR0663I Your choice: '3'

BR0280I BRTOOLS time stamp: 2016-05-04 14.07.19
BR0681I Enter string value for "tablespace" []:
PSAPTEST
BR0280I BRTOOLS time stamp: 2016-05-04 14.07.24
BR0683I New value for "tablespace": 'PSAPTEST'

BR0280I BRTOOLS time stamp: 2016-05-04 14.07.24
BR0657I Input menu 83 - please check/enter input values
-------------------------------------------------------------------------------
BRSPACE options for drop tablespace

 1 - BRSPACE profile (profile) ...... [initTEST.sap]
 2 - Database user/password (user) .. [/]
 3 ~ Tablespace name (tablespace) ... [PSAPTEST]
 4 - Confirmation mode (confirm) .... [yes]
 5 - Scrolling line count (scroll) .. [20]
 6 - Message language (language) .... [E]
 7 - BRSPACE command line (command) . [-p initTEST.sap -s 20 -l E -f tsdrop -t PSAPTEST]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
4
BR0280I BRTOOLS time stamp: 2016-05-04 14.07.39
BR0663I Your choice: '4'

BR0280I BRTOOLS time stamp: 2016-05-04 14.07.39
BR0681I Enter string value for "confirm" (yes|no|force) [yes]:

BR0280I BRTOOLS time stamp: 2016-05-04 14.07.58
BR0683I New value for "confirm": 'no'

BR0280I BRTOOLS time stamp: 2016-05-04 14.07.58
BR0657I Input menu 83 - please check/enter input values
-------------------------------------------------------------------------------
BRSPACE options for drop tablespace

 1 - BRSPACE profile (profile) ...... [initTEST.sap]
 2 - Database user/password (user) .. [/]
 3 ~ Tablespace name (tablespace) ... [PSAPTEST]
 4 - Confirmation mode (confirm) .... [no]
 5 - Scrolling line count (scroll) .. [20]
 6 - Message language (language) .... [E]
 7 - BRSPACE command line (command) . [-p initTEST.sap -c -s 20 -l E -f tsdrop -t PSAPTEST]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRTOOLS time stamp: 2016-05-04 14.08.05
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0291I BRSPACE will be started with options '-p initTEST.sap -c -s 20 -l E -f tsdrop -t PSAPTEST'

BR0280I BRTOOLS time stamp: 2016-05-04 14.08.05
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
c
BR0280I BRTOOLS time stamp: 2016-05-04 14.08.17
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...

###############################################################################

BR1001I BRSPACE 7.00 (32)
BR1002I Start of BRSPACE processing: setcdtev.tsd 2016-05-04 14.08.17
BR0484I BRSPACE log file: /oracle/TEST/sapreorg/setcdtev.tsd

BR0280I BRSPACE time stamp: 2016-05-04 14.08.17
BR1009I Name of database instance: TEST
BR1010I BRSPACE action ID: setcdtev
BR1011I BRSPACE function ID: tsd
BR1012I BRSPACE function: tsdrop
BR0126I Unattended mode active - no operator confirmation required

BR0280I BRSPACE time stamp: 2016-05-04 14.08.17
BR0657I Input menu 310 - please check/enter input values
-------------------------------------------------------------------------------
Options for dropping of tablespace PSAPTEST

 1 * Number of files in tablespace (files) . [1]
 2 * Total tablespace size in MB (size) .... [100]
 3 - Force tablespace drop (force) ......... [no]
 4 - SQL command (command) ................. [drop tablespace PSAPTEST]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2016-05-04 14.08.41
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2016-05-04 14.08.42
BR1070I Searching for segments in tablespace PSAPTEST...
BR0285I This function can take several seconds/minutes - be patient...

BR0280I BRSPACE time stamp: 2016-05-04 14.08.42
BR1071I Tablespace PSAPTEST is empty

BR0280I BRSPACE time stamp: 2016-05-04 14.08.42
BR0370I Directory /oracle/TEST/sapreorg/setcdtev created

BR0280I BRSPACE time stamp: 2016-05-04 14.08.42
BR0319I Control file copy created: /oracle/TEST/sapreorg/setcdtev/cntrlTEST.old 16302080

BR0280I BRSPACE time stamp: 2016-05-04 14.08.42
BR0768I Tablespace PSAPTEST set OFFLINE

BR0280I BRSPACE time stamp: 2016-05-04 14.08.42
BR1090I Dropping tablespace PSAPTEST...

BR0280I BRSPACE time stamp: 2016-05-04 14.08.42
BR1016I SQL statement 'drop tablespace PSAPTEST' executed successfully
BR1069I Tablespace PSAPTEST dropped successfully

BR0280I BRSPACE time stamp: 2016-05-04 14.08.42
BR0794I Database file /oracle/TEST/sapdata3/test_1/test.data1 deleted
BR0480I Directory /oracle/TEST/sapdata3/test_1 deleted

BR0280I BRSPACE time stamp: 2016-05-04 14.08.42
BR0340I Switching to next online redo log file for database instance TEST ...
BR0321I Switch to next online redo log file for database instance TEST successful

BR0280I BRSPACE time stamp: 2016-05-04 14.08.42
BR0319I Control file copy created: /oracle/TEST/sapreorg/setcdtev/cntrlTEST.new 16302080

BR0280I BRSPACE time stamp: 2016-05-04 14.08.42
BR1020I Number of tablespaces processed: 1
BR1003I BRSPACE function 'tsdrop' completed

BR1008I End of BRSPACE processing: setcdtev.tsd 2016-05-04 14.08.42
BR0280I BRSPACE time stamp: 2016-05-04 14.08.42
BR1005I BRSPACE completed successfully

###############################################################################

BR0292I Execution of BRSPACE finished with return code 0

BR0280I BRTOOLS time stamp: 2016-05-04 14.08.42
BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRTOOLS:
s
BR0280I BRTOOLS time stamp: 2016-05-04 14.09.04
BR0257I Your reply: 's'
BR0679I Do you really want to cancel BRTOOLS? Enter y[es]/n[o]:
y
BR0280I BRTOOLS time stamp: 2016-05-04 14.09.06
BR0257I Your reply: 'y'
BR0260E BRTOOLS cancelled by user

BR0280I BRTOOLS time stamp: 2016-05-04 14.09.06
BR0654I BRTOOLS terminated with errors

steps to create new table space in oracle SAP database using BRTOOLS

I have tried to create table-space with "TEST" ,but the system is NOT allowing and we have to create Table space  starting with PSAP and TBS name should be minimum 7 character length.

Here is the step to create NEW table space in Oracle SAP Database.

SERVER_NAME:oraTEST 35> brtools
BR0651I BRTOOLS 7.00 (32)

BR0280I BRTOOLS time stamp: 2015-05-04 13.46.10
BR0656I Choice menu 1 - please make a selection
-------------------------------------------------------------------------------
BR*Tools main menu

 1 = Instance management
 2 - Space management
 3 - Segment management
 4 - Backup and database copy
 5 - Restore and recovery
 6 - Check and verification
 7 - Database statistics
 8 - Additional functions
 9 - Exit program

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
2
BR0280I BRTOOLS time stamp: 2015-05-04 13.46.13
BR0663I Your choice: '2'

BR0280I BRTOOLS time stamp: 2015-05-04 13.46.13
BR0656I Choice menu 5 - please make a selection
-------------------------------------------------------------------------------
Database space management

 1 = Extend tablespace
 2 - Create tablespace
 3 - Drop tablespace
 4 - Alter tablespace
 5 - Alter data file
 6 - Move data file
 7 - Additional space functions
 8 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
2
BR0280I BRTOOLS time stamp: 2015-05-04 13.46.15
BR0663I Your choice: '2'

BR0280I BRTOOLS time stamp: 2015-05-04 13.46.15
BR0657I Input menu 82 - please check/enter input values
-------------------------------------------------------------------------------
BRSPACE options for create tablespace

 1 - BRSPACE profile (profile) ...... [initTEST.sap]
 2 - Database user/password (user) .. [/]
 3 ~ Tablespace name (tablespace) ... []
 4 - Confirmation mode (confirm) .... [yes]
 5 - Scrolling line count (scroll) .. [20]
 6 - Message language (language) .... [E]
 7 - BRSPACE command line (command) . [-p initTEST.sap -s 20 -l E -f tscreate]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2015-05-04 13.46.19
BR0663I Your choice: '3'

BR0280I BRTOOLS time stamp: 2015-05-04 13.46.19
BR0681I Enter string value for "tablespace" []:
PSAPTEST
BR0280I BRTOOLS time stamp: 2015-05-04 13.46.27
BR0683I New value for "tablespace": 'PSAPTEST'

BR0280I BRTOOLS time stamp: 2015-05-04 13.46.27
BR0657I Input menu 82 - please check/enter input values
-------------------------------------------------------------------------------
BRSPACE options for create tablespace

 1 - BRSPACE profile (profile) ...... [initTEST.sap]
 2 - Database user/password (user) .. [/]
 3 ~ Tablespace name (tablespace) ... [PSAPTEST]
 4 - Confirmation mode (confirm) .... [yes]
 5 - Scrolling line count (scroll) .. [20]
 6 - Message language (language) .... [E]
 7 - BRSPACE command line (command) . [-p initTEST.sap -s 20 -l E -f tscreate -t PSAPTEST]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRTOOLS time stamp: 2015-05-04 13.46.33
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0291I BRSPACE will be started with options '-p initTEST.sap -s 20 -l E -f tscreate -t PSAPTEST'

BR0280I BRTOOLS time stamp: 2015-05-04 13.46.33
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
c
BR0280I BRTOOLS time stamp: 2015-05-04 13.46.35
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...

###############################################################################

BR1001I BRSPACE 7.00 (32)
BR1002I Start of BRSPACE processing: setcdrgt.tsc 2015-05-04 13.46.35
BR0484I BRSPACE log file: /oracle/TEST/sapreorg/setcdrgt.tsc

BR0280I BRSPACE time stamp: 2015-05-04 13.46.35
BR1009I Name of database instance: TEST
BR1010I BRSPACE action ID: setcdrgt
BR1011I BRSPACE function ID: tsc
BR1012I BRSPACE function: tscreate

BR0280I BRSPACE time stamp: 2015-05-04 13.46.35
BR0657I Input menu 305 - please check/enter input values
-------------------------------------------------------------------------------
Main options for creation of tablespace in database TEST

 1 - Tablespace name (tablespace) ......... [PSAPTEST]
 2 - Tablespace contents (contents) ....... [data]
 3 - Segment space management (space) ..... [auto]
 4 # Database owner of tablespace (owner) . []
 5 # Table data class / tabart (class) .... []
 6 - Data type in tablespace (data) ....... [both]
 7 # Joined index/table tablespace (join) . []
 8 ~ Uniform size in MB (uniform) ......... []

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2015-05-04 13.46.40
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2015-05-04 13.46.40
BR0657I Input menu 306 - please check/enter input values
-------------------------------------------------------------------------------
Space options for creation of tablespace PSAPTEST (1. file)

 1 - Tablespace file name (file) .......... [/oracle/TEST/sapdata3/test_1/test.data1]
 2 ~ Raw disk / link target (rawlink) ..... []
 3 - File size in MB (size) ............... [100]
 4 - File autoextend mode (autoextend) .... [no]
 5 # Maximum file size in MB (maxsize) .... []
 6 # File increment size in MB (incrsize) . []
 7 - SQL command (command) ................ [create tablespace PSAPTEST extent management local autoallocate segment space management auto datafile '/oracle/TEST/sapdata3/test_1/test.data1' size 100M autoextend off]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2015-05-04 13.46.51
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2015-05-04 13.46.51
BR1091I Next data file can be specified now

BR0675I Do you want to perform this action?
BR0676I Enter 'y[es]' to perform the action, 'n[o]/c[ont]' to skip it, 's[top]' to abort:
n
BR0280I BRSPACE time stamp: 2015-05-04 13.46.54
BR0257I Your reply: 'n'
BR0678I The action will be skipped...

BR0280I BRSPACE time stamp: 2015-05-04 13.46.54
BR0370I Directory /oracle/TEST/sapreorg/setcdrgt created

BR0280I BRSPACE time stamp: 2015-05-04 13.46.54
BR0319I Control file copy created: /oracle/TEST/sapreorg/setcdrgt/cntrlTEST.old 16302080

BR0280I BRSPACE time stamp: 2015-05-04 13.46.54
BR0370I Directory /oracle/TEST/sapdata3/test_1 created

BR0280I BRSPACE time stamp: 2015-05-04 13.46.54
BR1089I Creating tablespace PSAPTEST...

BR0280I BRSPACE time stamp: 2015-05-04 13.46.55
BR1016I SQL statement 'create tablespace PSAPTEST extent management local autoallocate segment space management auto datafile '/oracle/TEST/sapdata3/test_1/test.data1' size 100M autoextend off' executed successfully
BR1060I Tablespace PSAPTEST created successfully with file: /oracle/TEST/sapdata3/test_1/test.data1 100M

BR0280I BRSPACE time stamp: 2015-05-04 13.46.55
BR0340I Switching to next online redo log file for database instance TEST ...
BR0321I Switch to next online redo log file for database instance TEST successful

BR0280I BRSPACE time stamp: 2015-05-04 13.46.55
BR0319I Control file copy created: /oracle/TEST/sapreorg/setcdrgt/cntrlTEST.new 16302080

BR0280I BRSPACE time stamp: 2015-05-04 13.46.55
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
s
BR0280I BRSPACE time stamp: 2015-05-04 13.47.02
BR0257I Your reply: 's'
BR0679I Do you really want to cancel BRSPACE? Enter y[es]/n[o]:
y
BR0280I BRSPACE time stamp: 2015-05-04 13.47.08
BR0258W Wrong reply: 'y'
BR0679I Do you really want to cancel BRSPACE? Enter y[es]/n[o]:
y
BR0280I BRSPACE time stamp: 2015-05-04 13.47.11
BR0257I Your reply: 'y'
BR0260E BRSPACE cancelled by user

BR1008I End of BRSPACE processing: setcdrgt.tsc 2015-05-04 13.47.11
BR0280I BRSPACE time stamp: 2015-05-04 13.47.11
BR1007I BRSPACE terminated with errors

###############################################################################

BR0292I Execution of BRSPACE finished with return code 4

BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action

BR0280I BRTOOLS time stamp: 2015-05-04 13.47.11
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
s
BR0280I BRTOOLS time stamp: 2015-05-04 13.47.13
BR0257I Your reply: 's'
BR0679I Do you really want to cancel BRTOOLS? Enter y[es]/n[o]:
y
BR0280I BRTOOLS time stamp: 2015-05-04 13.47.15
BR0257I Your reply: 'y'
BR0260E BRTOOLS cancelled by user

BR0280I BRTOOLS time stamp: 2015-05-04 13.47.15
BR0654I BRTOOLS terminated with errors
SERVER_NAME:oraTEST 36>

steps to add data file to table space in SAP oracle database using BRTOOLS

Here is the list of steps to follow to add data file in existing table space.
extending Tablespace size was differant in oracle SAP DB

SERVER_NAME:oraTEST 2> brtools
BR0651I BRTOOLS 7.00 (32)

BR0280I BRTOOLS time stamp: 2015-05-03 14.21.41
BR0656I Choice menu 1 - please make a selection
-------------------------------------------------------------------------------
BR*Tools main menu

 1 = Instance management
 2 - Space management
 3 - Segment management
 4 - Backup and database copy
 5 - Restore and recovery
 6 - Check and verification
 7 - Database statistics
 8 - Additional functions
 9 - Exit program

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
2
BR0280I BRTOOLS time stamp: 2015-05-03 14.21.49
BR0663I Your choice: '2'

BR0280I BRTOOLS time stamp: 2015-05-03 14.21.49
BR0656I Choice menu 5 - please make a selection
-------------------------------------------------------------------------------
Database space management

 1 = Extend tablespace
 2 - Create tablespace
 3 - Drop tablespace
 4 - Alter tablespace
 5 - Alter data file
 6 - Move data file
 7 - Additional space functions
 8 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
1
BR0280I BRTOOLS time stamp: 2015-05-03 14.21.53
BR0663I Your choice: '1'

BR0280I BRTOOLS time stamp: 2015-05-03 14.21.53
BR0657I Input menu 81 - please check/enter input values
-------------------------------------------------------------------------------
BRSPACE options for tablespace extension

 1 - BRSPACE profile (profile) ...... [initTEST.sap]
 2 - Database user/password (user) .. [/]
 3 ~ Tablespace name (tablespace) ... []
 4 - Confirmation mode (confirm) .... [yes]
 5 - Scrolling line count (scroll) .. [20]
 6 - Message language (language) .... [E]
 7 - BRSPACE command line (command) . [-p initTEST.sap -s 20 -l E -f tsextend]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2015-05-03 14.21.56
BR0663I Your choice: '3'

BR0280I BRTOOLS time stamp: 2015-05-03 14.21.56
BR0681I Enter string value for "tablespace" []:
PSAPSR3DB
BR0280I BRTOOLS time stamp: 2015-05-03 14.22.11
BR0683I New value for "tablespace": 'PSAPSR3DB'

BR0280I BRTOOLS time stamp: 2015-05-03 14.22.11
BR0657I Input menu 81 - please check/enter input values
-------------------------------------------------------------------------------
BRSPACE options for tablespace extension

 1 - BRSPACE profile (profile) ...... [initTEST.sap]
 2 - Database user/password (user) .. [/]
 3 ~ Tablespace name (tablespace) ... [PSAPSR3DB]
 4 - Confirmation mode (confirm) .... [yes]
 5 - Scrolling line count (scroll) .. [20]
 6 - Message language (language) .... [E]
 7 - BRSPACE command line (command) . [-p initTEST.sap -s 20 -l E -f tsextend -t PSAPSR3DB]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRTOOLS time stamp: 2015-05-03 14.22.15
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0291I BRSPACE will be started with options '-p initTEST.sap -s 20 -l E -f tsextend -t PSAPSR3DB'

BR0280I BRTOOLS time stamp: 2015-05-03 14.22.15
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
c
BR0280I BRTOOLS time stamp: 2015-05-03 14.22.19
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...

###############################################################################

BR1001I BRSPACE 7.00 (32)
BR1002I Start of BRSPACE processing: setbywqd.tse 2015-05-03 14.22.19
BR0484I BRSPACE log file: /oracle/TEST/sapreorg/setbywqd.tse

BR0280I BRSPACE time stamp: 2015-05-03 14.22.19
BR1009I Name of database instance: TEST
BR1010I BRSPACE action ID: setbywqd
BR1011I BRSPACE function ID: tse
BR1012I BRSPACE function: tsextend

BR0280I BRSPACE time stamp: 2015-05-03 14.22.20
BR0657I Input menu 303 - please check/enter input values
-------------------------------------------------------------------------------
Options for extension of tablespace PSAPSR3DB (1. file)

 1 * Last added file name (lastfile) ....... [/oracle/TEST/sapdata3/sr3db_3/sr3db.data3]
 2 * Last added file size in MB (lastsize) . [1000]
 3 - New file to be added (file) ........... [/oracle/TEST/sapdata3/sr3db_4/sr3db.data4]
 4 ~ Raw disk / link target (rawlink) ...... []
 5 - Size of the new file in MB (size) ..... [1000]
 6 - File autoextend mode (autoextend) ..... [no]
 7 # Maximum file size in MB (maxsize) ..... []
 8 # File increment size in MB (incrsize) .. []
 9 - SQL command (command) ................. [alter tablespace PSAPSR3DB add datafile '/oracle/TEST/sapdata3/sr3db_4/sr3db.data4' size 1000M autoextend off]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
5
BR0280I BRSPACE time stamp: 2015-05-03 14.22.51
BR0663I Your choice: '5'

BR0280I BRSPACE time stamp: 2015-05-03 14.22.51
BR0682I Enter integer value for "size" (1-32767) [1000]:
100
BR0280I BRSPACE time stamp: 2015-05-03 14.22.56
BR0683I New value for "size": '100'

BR0280I BRSPACE time stamp: 2015-05-03 14.22.56
BR0657I Input menu 303 - please check/enter input values
-------------------------------------------------------------------------------
Options for extension of tablespace PSAPSR3DB (1. file)

 1 * Last added file name (lastfile) ....... [/oracle/TEST/sapdata3/sr3db_3/sr3db.data3]
 2 * Last added file size in MB (lastsize) . [1000]
 3 - New file to be added (file) ........... [/oracle/TEST/sapdata3/sr3db_4/sr3db.data4]
 4 ~ Raw disk / link target (rawlink) ...... []
 5 - Size of the new file in MB (size) ..... [100]
 6 - File autoextend mode (autoextend) ..... [no]
 7 # Maximum file size in MB (maxsize) ..... []
 8 # File increment size in MB (incrsize) .. []
 9 - SQL command (command) ................. [alter tablespace PSAPSR3DB add datafile '/oracle/TEST/sapdata3/sr3db_4/sr3db.data4' size 100M autoextend off]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2015-05-03 14.23.00
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2015-05-03 14.23.00
BR1091I Next data file can be specified now

BR0675I Do you want to perform this action?
BR0676I Enter 'y[es]' to perform the action, 'n[o]/c[ont]' to skip it, 's[top]' to abort:
n
BR0280I BRSPACE time stamp: 2015-05-03 14.23.08
BR0257I Your reply: 'n'
BR0678I The action will be skipped...

BR0280I BRSPACE time stamp: 2015-05-03 14.23.08
BR0370I Directory /oracle/TEST/sapreorg/setbywqd created

BR0280I BRSPACE time stamp: 2015-05-03 14.23.08
BR0319I Control file copy created: /oracle/TEST/sapreorg/setbywqd/cntrlTEST.old 16302080

BR0280I BRSPACE time stamp: 2015-05-03 14.23.08
BR0370I Directory /oracle/TEST/sapdata3/sr3db_4 created

BR0280I BRSPACE time stamp: 2015-05-03 14.23.08
BR1088I Extending tablespace PSAPSR3DB...

BR0280I BRSPACE time stamp: 2015-05-03 14.23.09
BR1016I SQL statement 'alter tablespace PSAPSR3DB add datafile '/oracle/TEST/sapdata3/sr3db_4/sr3db.data4' size 100M autoextend off' executed successfully
BR1051I Tablespace PSAPSR3DB extended successfully with file: /oracle/TEST/sapdata3/sr3db_4/sr3db.data4 100M

BR0280I BRSPACE time stamp: 2015-05-03 14.23.09
BR0340I Switching to next online redo log file for database instance TEST ...
BR0321I Switch to next online redo log file for database instance TEST successful

BR0280I BRSPACE time stamp: 2015-05-03 14.23.09
BR0319I Control file copy created: /oracle/TEST/sapreorg/setbywqd/cntrlTEST.new 16302080

BR0280I BRSPACE time stamp: 2015-05-03 14.23.09
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
s
BR0280I BRSPACE time stamp: 2015-05-03 14.23.22
BR0257I Your reply: 's'
BR0679I Do you really want to cancel BRSPACE? Enter y[es]/n[o]:
y
BR0280I BRSPACE time stamp: 2015-05-03 14.23.26
BR0257I Your reply: 'y'
BR0260E BRSPACE cancelled by user

BR1008I End of BRSPACE processing: setbywqd.tse 2015-05-03 14.23.26
BR0280I BRSPACE time stamp: 2015-05-03 14.23.26
BR1007I BRSPACE terminated with errors

###############################################################################

BR0292I Execution of BRSPACE finished with return code 4

BR0668I Warnings or errors occurred - you can continue to ignore them or go back to repeat the last action

BR0280I BRTOOLS time stamp: 2015-05-03 14.23.26
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
s
BR0280I BRTOOLS time stamp: 2015-05-03 14.23.28
BR0257I Your reply: 's'
BR0679I Do you really want to cancel BRTOOLS? Enter y[es]/n[o]:
y
BR0280I BRTOOLS time stamp: 2015-05-03 14.23.30
BR0257I Your reply: 'y'
BR0260E BRTOOLS cancelled by user

BR0280I BRTOOLS time stamp: 2015-05-03 14.23.30
BR0654I BRTOOLS terminated with errors

usoak521:oraTEST 3>

command to collect full stats on oracle SAP database.

here is the command to collect full stats on Oracle SAP database.

brconnect -u / -c -f stats -t all -p 4

t---> all tables

f --> force

p-->paralellism

query eating more Temp space and throwing ORA-01652: unable to extend temp segment by even for one row

Hi All,

I had situation  when tried to fetch some amount of records from v$rman_backup_job_details,its keep eating lot of temp space and finally throws error on 10204.

ORA-01652: unable to extend temp segment by 256 in tablespace TEMP 

finally identified there was a bug and below is the work around.

you are likely to be running in to Bug 5466436

The workaround for this issue is to delete statistics from the X$KCCRSR ie:

exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR');

- this deletes the statistics on the fixed object  and

exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR');

- this locks that object so that statistics will not be collected in future.

Difference between SQL profile Vs SQL plan management baselines

BASIC INFO	SQL PROFILES	SPM BASELINES
What they are	Stored collections of Hints (plus some technical information for the optimizer)	Stored collections of Hints (plus some technical information for the optimizer)
Available from	10g	11g
They affect	Individual SQL	Individual SQL
What they do	Adjust Optimizer cardinality estimations	Direct SQL to follow specific execution plan
Motto (as far asSQL Plans are concerned)	Be the Best you can be !	Only the Worthy may Pass !
Managed by PL/SQL package	dbms_sqltune	dbms_spm
How are they created ?	Run SQL Tuning task (dbms_sqltune.execute_tuning_task) to analyze existing SQL and IF cardinality is skewed, store it as SQLProfile	Take existing execution plan from SQL that already ran and store it as SPM baseline
Can their creation be forced ?	YES, but this is not fully supported. I.e.
		YES, ifoptimizer_capture_sql_plan_baselines=TRUE
Can they be created manually for individual SQL?	YES, bydbms_sqltune.execute_tuning_task()	YES, but SQL needs to already have run:dbms_spm.load_plans_from_cursor_cache(sql_id => …)
Can they be captured for the ongoing workload ?	YES, through SQL Tuning Sets	YES, if optimizer_capture_sql_plan_baselines=TRUE
Can they be “group loaded” from SQLs in the shared pool ?	YES, through SQL Tuning Sets	YES, directly
Can they be “group loaded” from SQLs in AWRrepository ?	YES, through SQL Tuning Sets	YES, through SQL Tuning Sets
Are they “activated” upon creation ?	NO, SQL Profiles need to be explicitly accepted	MAYBE, Baseline is activated if it is the first baseline captured (for the SQL) OR if loaded from cursor cache, AWR etc
Can they be activated automatically ?	YES, if accept_sql_profiles is set forSQL Tuning AutoTask	MAYBE, SPM baseline is activated if it is the first baseline captured (for the SQL)
Can they be deactivated globally ?	NO	YES, Setoptimizer_use_sql_plan_baselines=FALSE
Can they be deactivated locally ?	YES, set sqltune_category	NO
Can they be transferred to another database ?	YES	YES
Can they “fire” for the object in different schema ?	YES	YES
Can they “fire” when object has a different structure ?	YES	YES
Can they “fire” when table is replaced withMVIEW ?	YES	NO
Can they “fire” when some objects (i.e. indexes) used in the original plan are missing for the new object ?	YES	NO
Available in Standard Edition ?	NO	NO
Available in genericENTERPRISEEdition ?	NO, you need to also licenseDIAGNOSTICS and TUNING packs	YES

Senior Oracle DBA Goldengate interview questions

Oracle DBA Goldengate interview questions:

1.       What are the important process in GG?

2.       What is difference between CSN, SCN?

3.       What is extract, replicat?

4.       What are the GG types or topologies?

5.       How to get log from source & target?

6.       Difference between classic & integral extract process?

7.       What are the pre-request for GG?

8.       Types of replication in GG?

9.       What are the areas monitoring required in GG?

10.   What is the difference between local trail / Remote Trail?

11.   What are the GG utilities used?

12.   How to define size for extract file?

13.   How to change GG writing extract file?

14.   How to purge extract file automatically?

15.   How to check how many extract file created per day?

16.   What to check, when replication steps in primary & target?

17.   How to start/stop, extract/replicat for source & target?

18.   How to sync target with source if we missed the trail file?

19.   What is the format for trail file?

20.   What is the default location for the trail file & can we change the trail file location?

21.   What is the datapump in GG?

22.   Is there any limit in extract groups / Replicat numbers?

23.   What are the GG views?

24.   What is GLOBAL file in GG & location?

25.   What are the directory in GG?

26.   What is credential store?

27.   What are the types of encryption supported by GG?

28.   What is obey usage?

29.   What is discard file & usage? Can we change from default location?

30.   How to switch writing to new trail file?

31.   What is the proper steps to shutdown GG database?

32.   How to automatically remove old trail file?

33.   How to enable automatic for replicat/ extract?

34.   What are the parameter file used by GG?

35.   What are the limitation for bidirectional GG?

36.   What is the use of REPERROR in GG?

37.   How to check latency between source & GG?

38.   What is utility used to open trail file in GG?

39.   How to check particular trail file needed or not?

40.   What are the various GG operational types?

41.   What is reverse utility?

42.   In unidirectional GG setup, steps to add a table for replication?

43.   Can we change the GG oracle DB without setting up in operating system level?

44.   What are the types extract check point positions?

45.   How to change the trail file size in GG?

46.   How to check all records processed in replicat? Info replicat group , it should return <EOF>

47.   How to calculate & allocate swap space for GG server?

48.   How to check last 5 recent extract checkpoints?

49.   How to control the checkpoint in extract & replicat?

50.   How to confirm extract in working file in GG?

51.   What are the recovery types in extract process?

52.   How to setup lag process time in GG?

53.   How to manager process to check extract/replicat lag?

54.   How to view GG error log & process report?

55.   How to check how many records processed in replication /extraction?

56.   What is discard file? How to check default location?
57. How to check ,how many updates/deletes happened on GG in particular time?
58. what is archive log only mode in GG?
59. what is the command to see the recovery status in GG?
60.what is the use of SOURCEISTABLE &SPECIALRUN keyword in GG?