DBA Blogs

You might have already seen the blog update on Steven Chan’s site. TXK AutoConfig and Templates Rollup Patch S (6372396) was released on May 5th.

This patch differs from traditional TXK autoconfig template patch releases in that the ATG team decided to include some other important TXK patches also with this release. One of these is TXK Advanced Utilities Rollup Patch C (5011249).

This Advanced Utilities patch brings some import scripts that can be used to implement advanced topologies. The important benefit here is that these scripts can be run from command line. For complete details, refer to Metalink note 277574.1, Running Configuration Wizards from the Command Line in Oracle Applications 11i.

As a side effect of this generous inclusion of import updates, the patch size has increased from 16mb (RUP R) to 65mb (RUP S).

This Rollup Patch has also brought in some new Context variables. These new XML tags in Rollup Patch S are:

1. s_jdbc_connect_descriptor_generation: The value of this variable determines whether the jdbc connect descriptor is regenerated and whether the value of the context variable s_apps_jdbc_connect_descriptor is updated. Acceptable values for this context variable are true (the default) or false. Set this value to false when using a custom apps jdbc connect descriptor
2. s_apcprestart: This variable is used to specify the complete path of the script to be executed before the Apache service is started.
3. s_ccmmaxsyspathlen: (Windows-specific) This context variable is used for setting the maximum length of the Windows System Path.

(more…)

Today is my last day at my current employer. It has been an incredible learning experience, and I’ll be forever grateful to the man who gave me the opportunity almost 7 years ago having zero knowledge of either Oracle or database administration.

I have accepted an offer from noted consulting firm The Pythian Group to join their staff of DBAs doing remote administration. This new position should give me exposure to a diverse set of client environments and configurations and a chance to grow in this career path and learn from some of the best in the business. I will be flying out on Sunday for a 3-week stint at the worldwide headquarters in Ottawa, Ontario, Canada to immerse myself in the Pythian way. If I have free time with my nights I hope to finish porting LeagueSite to drupal.

I’ll also try to get more regular with the blogging. Things have been a bit hectic as of late.

Beware this parameter can prevent your database from starting. Indeed it can prevent your instance from starting!

There are two dynamic views v$system_fix_control and v$session_fix_control which were introduced in 10.2 and control whether fixes for bugs in the optimizer can be turned on or off. This can also be controlled using the _fix_control initialization parameter.

If the parameter _fix_control is set incorrectly i.e. with invalid bug ids then, when starting the database, you may get the following error

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Wed May 7 10:55:43 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORA-00940: invalid ALTER command

There are no error messages in the alert log and the instance has refused to start.

And look at that helpful error message. No doubt behind the scenes it doing some sort of ALTER command but still …

If you get this error then check the pfile or spfile and see if there is some spurious _fix_control setting. This is especially valid for the upgrading of Oracle. I came across this error upgrading 10.2.0.2.0 to 10.2.0.3.0.

Also beware if setting this parameter in a running database. You can prevent further logins if you get it wrong or indeed try to unset it. Do not do

ALTER SYSTEM SET "_fix_control" = '';

This will require a bounce to fix. Once, of course, the parameter has been removed from the initialization files.

The moral of the story is be very careful with undocumented parameters when upgrading!

This is the 96th edition of the weekly review of database blogs, Log Buffer.

Let’s start this one in SQL Server Land, with a question from Dennis Gobo — should SQL Server have the CREATE [OR REPLACE] PROCEDURE syntax? There are, he writes, advantages: “When scripting out a database you don’t have to generate if exists…..drop statements,” and disadvantages: “I can overwrite a proc without even knowing it.” Of course, the commenters have opinions of their own, and the piece becomes a straw poll for the desirability of that syntax as a feature.

Aaron Bertrand has one too: when was my database/table last accessed? Writes Aaron, “SQL Server does not track this information for you. SELECT triggers still do not exist. Third party tools are expensive and can incur unexpected overhead. And people continue to be reluctant or unable to constrain table access via stored procedures, which could otherwise perform simple logging.” He looks at 2008’s built-in auditing, and for those who can’t wait for that, illustrates a workaround for 2005.

Linchi Shea explores something else from 2008, Page Compression, focusing on how the number of processors affects the rebuilding a table with page compression.

Jamie Thomson, the SSIS Junkie writes that he has made a submission to Connect on the matter of absolute and relative paths in SSIS. “. . . I have always agreed that stipulating the use of absolute paths within SSIS was the right thing to do (and indeed I have championed it) however of late I have changed my mind. Support for relative paths would greatly simplify package deployment and package management . . . What do you think? Should SSIS support relative paths?” So far, it looks like a shoo-in.

Brian Knight also explains another little quirk, SSIS Case Sensitivity: “The case sensitivity can in some cases create behavior that is not expected and may give you bad results if you’re not careful.  . . . One such example is with the Lookup Transform, where comparisons against the cache are case sensitive. If you do not expect this, you may have a miss in a match that is actually a hit.”

In the MySQL ’sphere this week, there is plenty of talk about the openness or otherwise of MySQL. (more…)

It is with great pride that I am able to announce that Pythian is making a large investment in Europe. As of this month, Pythian Europe s.r.o. is fully operational and we have headquartered the company in beautiful Prague. Additional offices are planned in Paris and Malta by the end of the summer.

Pythian Europe is launching with an elite, full-fledged team and I would like to introduce the founders:

On the left is Lukas Vysusil, who joins us from Oracle where he served for 6 years in a variety of roles, including Oracle Applications DBA, DBA Team Lead, Manager of the Configuration Queue for Oracle OnDemand outsourcing services, and also Senior Technology Consultant. He brings a wealth of experience in team leadership, troubleshooting, Oracle Apps, the pressure cooker of consulting in the enterprise database and applications technology space and formal configuration and change management processes to Pythian and will serve as Service Delivery Manager.

On the right is Jan Polnicky, who joins us from Oracle where he served for 6 years in a wide variety of roles. You’ll have to check his linkedin profile for the entire list, but suffice it to say he started out as a developer for Online Services, quickly took on a leadership role in that team, moved to OnDemand where he became a services team lead, then got promoted to EMEA queue manager for configurations, and then got promoted to OnDemand Services EMEA Manager - Release Management where he led a team of up to 15 engineers across geographies (UK, ES, CZ, EG + USA & APAC indirects) doing general Oracle Database & Apps management, tons of preventative maintenance and supervised a number of Oracle Applications upgrade projects. In his spare time, Jan is working on his Ph.D., I kid you not. Jan will serve as a peer to Lukas as Service Delivery Manager.

You may think that’s enough.

You may be thinking, OK, with these guys and the teams they will soon be leading now Pythian has added so much expertise and horsepower in Europe they’ll stand pat for a while.

But oh no. Not me. That was not enough!

To lead these guys, on the centre, we have also added Peter Simecka as Vice President, Pythian Europe. Peter joins us from, you might have guessed it, Oracle Corporation where he started out in 1994. Even before joining Oracle, he had substantial expertise on Oracle/UNIX, dating back to Oracle 4 (I first worked on Oracle 5, but 6 was already out by then). Over his career at Oracle, Peter has led teams as large as 60 engineers, served as Product Support Manager for five years, served as Customer Support Manager for four years, and then built and led the Oracle OnDemand Outsourcing centre in Prague for four years. To say that he brings a wealth of leadership experience, customer support and liaison experience, and outsourced services design, development and delivery experience is a woeful understatement. I am hoping and planning to learn a lot from him.

It’s funny because the way I presented these guys, it makes it seem like I selected each of them individually, but that’s not how it happened at all. I’ll leave that story for another day, or maybe Peter will want to tell it.

So, what are we planning to do with this ambitious operation in Europe? Stay tuned.

Oracle have come up with a fantastic new initiative that gives you, the Oracle customer, a say in what sessions are available at Oracle OpenWorld 2008. It’s called Oracle Mix and it basically allows anyone to put in a submission on what one might like to present or what one might like to see presented at Oracle OpenWorld 2008. You can then also [...]

I'm taking another crack at this blogging thing. The old blog is dead and it can't be revived. Posting will resume here soon.

The RMAN documentation differentiates between "inconsistent" and "consistent" backups on the basis of whether the database is OPEN or not (respectively) during the Backup.
The nomenclature "inconsistent backup" makes me nervous. Why not call it the good old "HOT" Backup ?

To do a consistent" backup with RMAN, the database must be mounted as RMAN needs to access and update the controlfiles. With an OS scripted backup pre-RMAN, the database was truly "COLD" -- there would be no Oracle processes running.
RMAN does not backup the Online Redo Log files. With an OS scripted backup, you had the option -- you could choose to include these files in your backup if you were careful about how you planned to use Cold Backups for Roll-Forward recoveries with ArchiveLog.

Since RMAN does not backup the Online Redo Log files, you must, perforce, OPEN RESETLOGS on a Restore. With a scripted backup, if you also included your Online Redo Log files in your backup and restore (provided that you did not plan to apply any ArchiveLogs), you could simply STARTUP the database and continue LogSequenceNumbers again. (Of course, you might be duplicating LogSequenceNumbers if the database had been active in ArchiveLog mode since the backup, so you have to be careful to distinguish the two "streams" of ArchiveLogs).


Here below is the simplest "consistent" Backup and Restore using RMAN :

C:\>rman

Recovery Manager: Release 10.2.0.3.0 - Production on Thu May 8 22:20:17 2008

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

RMAN> connect target /

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     536870912 bytes

Fixed Size                     1291652 bytes
Variable Size                297798268 bytes
Database Buffers             234881024 bytes
Redo Buffers                   2899968 bytes

RMAN> backup database;

Starting backup at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=58 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\OR10G2DB\SYSTEM01.DBF
input datafile fno=00003 name=C:\OR10G2DB\SYSAUX01.DBF
input datafile fno=00002 name=F:\OR10G2DB\TEST_TBS_01.DBF
input datafile fno=00004 name=C:\OR10G2DB\USERS01.DBF
input datafile fno=00005 name=C:\OR10G2DB\EXAMPLE01.DBF
input datafile fno=00007 name=C:\OR10G2DB\UNDO.DBF
channel ORA_DISK_1: starting piece 1 at 08-MAY-08
channel ORA_DISK_1: finished piece 1 at 08-MAY-08
piece handle=C:\OR10G2DB_FLASH\OR10G2DB\BACKUPSET\2008_05_08\O1_MF_NNNDF_TAG200
0508T222041_42631X6C_.BKP tag=TAG20080508T222041 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:47
Finished backup at 08-MAY-08

Starting Control File and SPFILE Autobackup at 08-MAY-08
piece handle=C:\OR10G2DB_FLASH\OR10G2DB\AUTOBACKUP\2008_05_08\O1_MF_S_654214767
426358M1_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 08-MAY-08

RMAN>
RMAN> shutdown

database dismounted
Oracle instance shut down

RMAN>

******************* BACKUP COMPLETED *****************
******************************************************

=========  database files deleted ====================
++++++++++++++++++++++++++++++++++++++++++++++++++++++
======================================================



C:\>rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Thu May 8 22:32:07 2008

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area     536870912 bytes

Fixed Size                     1291652 bytes
Variable Size                301992572 bytes
Database Buffers             230686720 bytes
Redo Buffers                   2899968 bytes

RMAN>
RMAN> restore controlfile from autobackup;

Starting restore at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=57 devtype=DISK

recovery area destination: \OR10G2DB_FLASH
database name (or database unique name) used for search: OR10G2DB
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\OR10G2DB_FLASH\OR10G2DB\AUTOBACKUP\2008
_05_08\O1_MF_S_654214767_426358M1_.BKP
channel ORA_DISK_1: control file restore from autobackup complete
output filename=C:\OR10G2DB\CONTROL01.CTL
output filename=C:\OR10G2DB\CONTROL02.CTL
output filename=C:\OR10G2DB\CONTROL03.CTL
Finished restore at 08-MAY-08

RMAN>
RMAN> restore database;

Starting restore at 08-MAY-08
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/08/2008 22:33:28
ORA-01507: database not mounted

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN>
RMAN> restore database;

Starting restore at 08-MAY-08
Starting implicit crosscheck backup at 08-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=57 devtype=DISK
Crosschecked 45 objects
Finished implicit crosscheck backup at 08-MAY-08

Starting implicit crosscheck copy at 08-MAY-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 08-MAY-08

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\OR10G2DB_FLASH\OR10G2DB\AUTOBACKUP\2008_05_08\O1_MF_S_654214767_42
6358M1_.BKP

using channel ORA_DISK_1

skipping datafile 2; already restored to file F:\OR10G2DB\TEST_TBS_01.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\OR10G2DB\SYSTEM01.DBF
restoring datafile 00003 to C:\OR10G2DB\SYSAUX01.DBF
restoring datafile 00004 to C:\OR10G2DB\USERS01.DBF
restoring datafile 00005 to C:\OR10G2DB\EXAMPLE01.DBF
restoring datafile 00007 to C:\OR10G2DB\UNDO.DBF
channel ORA_DISK_1: reading from backup piece C:\OR10G2DB_FLASH\OR10G2DB\BACKUPS
ET\2008_05_08\O1_MF_NNNDF_TAG20080508T222041_42631X6C_.BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\OR10G2DB_FLASH\OR10G2DB\BACKUPSET\2008_05_08\O1_MF_NNNDF_TAG2008
0508T222041_42631X6C_.BKP tag=TAG20080508T222041
channel ORA_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 08-MAY-08

RMAN>
RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 05/08/2008 22:36:48
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN>
RMAN> alter database open resetlogs;

database opened

RMAN>

The OPEN RESETLOGS is necessary because RMAN does not backup and restore
Online Redo Logs.

A DBA from another team went on vacation, and asked me to do few urgent tasks for him while he is gone.
One of the tasks was to refresh test schema from production data. In his email he commented that in his team the process includes opening a change in the Change Management system for this task “in case we drop the production schema instead of the test schema by mistake”.

I was very surprised by this. In my team, the ticket asking for the refresh was considered enough documentation, and there was no need to open a change. Also, if you regularly drop the wrong schema, opening changes will not help you.

I asked around for explanation for the different procedure. Turned out that few month ago someone from the other team dropped the wrong schema by mistake. It was recovered from backup with no issues, but it still caused an hour or two of downtime for the users. Which means that we need to open an Incident, and of course, every Incident has to contain action items for preventing same issue from reoccurring.  In this case, the manager who reviewed the incident noticed that there was no Change open for the schema refresh, which means that the DBA did not follow the right procedure! The natural action item for the change was “Instruct DBAs on Change Management procedures”. DBAs were properly instructed (at least some of them) and are now opening a change before dropping the wrong schema.

Which just goes to show how procedures can’t replace common sense.

With an example I will make you understand RMAN Fully Automated TSPITR. Workaround I will truncate a table which resides on USERS tablespace and later I will recover the tablespace to get back my data.

Note that my interaction with the RMAN client is shown as BOLD.

A)Get the rows of my_table.

SQL> select count(*) from my_table;
COUNT(*)
----------
49792
B)Truncate the Table.

SQL> TRUNCATE TABLE MY_TABLE;
Table truncated.

C)Determine the Tablespace which is belong my Dropped Table.

SQL> SELECT TABLESPACE_NAME from DBA_TABLES WHERE TABLE_NAME='MY_TABLE';
TABLESPACE_NAME
------------------------------
USERS

D)Connect to rman and perform TSPITR operation on USERS Tablespace.

SQL> !rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 8 04:49:54 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)

RMAN> RECOVER TABLESPACE USERS UNTIL TIME 'SYSDATE-1/24/60*5' AUXILIARY DESTINATION '/export/home/oracle';

Starting recover at 08-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point in time

List of tablespaces expected to have UNDO segments
tablespace SYSTEM
tablespace UNDOTBS1

Creating automatic instance, with SID='jDiz'

initialization parameters used for automatic instance:
db_name=DBASE
compatible=10.2.0.1.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_DBASE_jDiz
large_pool_size=1M
shared_pool_size=110M
#No auxiliary parameter file used
db_create_file_dest=/export/home/oracle
control_files=/export/home/oracle/cntrl_tspitr_DBASE_jDiz.f


starting up automatic instance DBASE

Oracle instance started

Total System Global Area 205520896 bytes

Fixed Size 2019576 bytes
Variable Size 146804488 bytes
Database Buffers 50331648 bytes
Redo Buffers 6365184 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until time "SYSDATE-1/24/60*5";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 08-MAY-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_ncsnf_TAG20080507T222432_424s4rpy_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_ncsnf_TAG20080507T222432_424s4rpy_.bkp tag=TAG20080507T222432
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/export/home/oracle/cntrl_tspitr_DBASE_jDiz.f
Finished restore at 08-MAY-08

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until time "SYSDATE-1/24/60*5";
plsql <<<-- tspitr_2
declare
sqlstatement varchar2(512);
offline_not_needed exception;
pragma exception_init(offline_not_needed, -01539);
begin
sqlstatement := 'alter tablespace '|| 'USERS' ||' offline for recover';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
exception
when offline_not_needed then
null;
end; >>>;
# set an omf destination filename for restore
set newname for clone datafile 1 to new;
# set an omf destination filename for restore
set newname for clone datafile 2 to new;
# set an omf destination tempfile
set newname for clone tempfile 1 to new;
# set a destination filename for restore
set newname for datafile 4 to
"/oradata2/data1/dbase/users01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile 1, 2, 4;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile 1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile 4 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
# datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace USERS offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_temp_%u_.tmp in control file

Starting restore at 08-MAY-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=39 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_system_%u_.dbf
restoring datafile 00002 to /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_undotbs1_%u_.dbf
restoring datafile 00004 to /oradata2/data1/dbase/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_nnndf_TAG20080507T222432_424s30dp_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oradata2/flash_recovery_area/DBASE/backupset/2008_05_07/
o1_mf_nnndf_TAG20080507T222432_424s30dp_.bkp tag=TAG20080507T222432
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 08-MAY-08

datafile 1 switched to datafile copy
input datafile copy recid=6 stamp=654151900 filename=/export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_system_425hqsjn_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=7 stamp=654151900 filename=/export/home/oracle/TSPITR_DBASE_JDIZ/datafile/
o1_mf_undotbs1_425hqsjr_.dbf

sql statement: alter database datafile 1 online

sql statement: alter database datafile 2 online

sql statement: alter database datafile 4 online

Starting recover at 08-MAY-08
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_3_425h5ok9_.arc
archive log thread 1 sequence 4 is already on disk as file /oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_4_425hqnx5_.arc
archive log filename=/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_3_425h5ok9_.arc thread=1 sequence=3
archive log filename=/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_08/o1_mf_1_4_425hqnx5_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:07
Finished recover at 08-MAY-08

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\
(PROGRAM=/oracle/app/oracle/product/10.2.0/db_1/bin/oracle\)\
(ARGV0=oraclejDiz\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\
(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=jDiz^'\)\)\(CONNECT_DATA=\(SID=jDiz\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
USERS file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"/@ as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace USERS online";
sql "alter tablespace USERS offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script


Export: Release 10.2.0.1.0 - Production on Thu May 8 04:51:58 2008

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace USERS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table DEPT
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table EMP
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table BONUS
EXP-00091: Exporting questionable statistics.
. . exporting table SALGRADE
EXP-00091: Exporting questionable statistics.
. . exporting table MY_TABLE
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully with warnings.
host command complete

database closed
database dismounted
Oracle instance shut down


Import: Release 10.2.0.1.0 - Production on Thu May 8 04:52:19 2008

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table "DEPT"
. . importing table "EMP"
. . importing table "BONUS"
. . importing table "SALGRADE"
. importing ARJU's objects into ARJU
. . importing table "MY_TABLE"
. importing SCOTT's objects into SCOTT
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace USERS online

sql statement: alter tablespace USERS offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /export/home/oracle/cntrl_tspitr_DBASE_jDiz.f deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_system_425hqsjn_.dbf deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_undotbs1_425hqsjr_.dbf deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/datafile/o1_mf_temp_425hs9ot_.tmp deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_1_425hs5oy_.log deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_2_425hs6hl_.log deleted
auxiliary instance file /export/home/oracle/TSPITR_DBASE_JDIZ/onlinelog/o1_mf_3_425hs7kc_.log deleted
Finished recover at 08-MAY-08

E)Make the backup of Tablespace and make it Online.

RMAN> SQL'ALTER TABLESPACE USERS ONLINE';
sql statement: ALTER TABLESPACE USERS ONLINE

RMAN> exit;

Recovery Manager complete.

F)Check the Objects.

SQL> select count(*) from my_table;

COUNT(*)
----------
49792


Related Documents:
---------------------


When you use TSPITR
Limitations of TSPITR

•If you have dropped a tablespace then TSPITR can't be used to recover that tablespace.

•You cannot recover a renamed tablespace to a point in time before it was renamed.

•You cannot recover tables without their associated constraints, or constraints
without the associated tables.

•Tablespaces containing undo or rollback segments cannot be recovered.

•Tablespaces that contain objects owned by SYS can't be recovered.

•In case of partitioning table which spread over multiple tablespaces in that case a single tablespace(only a partition/subset of partition) can't be recovered. You must recover all of the tablespace where partitioning table resides.

•If a tablespace is recovered by TSPITR statistics are not gathered of recovered objects. Therefore you must manually gather statistics of the recovered objects.

•Tablespace containing tables with VARRAY columns, nested tables, or external files can't be recovered with TSPITR.

•The tablespace containing replicated master tables, snapshot logs, snapshot tables can't be recovered with TSPITR.

•If a datafile was added after the point to which RMAN is recovering, an empty datafile by the same name will be included in the tablespace after RMAN TSPITR.

RMAN tablespace point-in-time recovery (abbreviated as TSPITR) enables you to quickly recover one or more tablespace in an oracle database to a previous time, without affecting the rest of the tablespaces in your database.

When you can use TSPITR
---------------------------
We need to remember that with RMAN TSPITR the whole data set inside the tablespace will be recover to an earlier time rather than single object.

In the following cases you might choose to use TSPITR.

1)Erroneously TRUNCATE TABLE statement is execute on a table.
2)Wrong update is done on a table and committed.
3)In a database there is several schema each in different tablespace. Now I want to get back a single schema to an earlier time.

It is good to remember that if a database run on a NOARCHIVELOG mode then TSPITR can't be performed. In other word I can say if I don't have archived redo logs then TSPITR can't be performed.

All of the information that we can see in RMAN prompt by SHOW, LIST or REPORT command can also be easily seen by query recovery catalog views. Recovery catalog views are prefixed by RC_.

Most of the catalog views have a corresponding dynamic performance view (or V$view) in the database server. For example, RC_BACKUP_PIECE corresponds to V$BACKUP_PIECE. The primary difference between the recovery catalog view and corresponding server view is that each catalog view contains information about all the databases registered in the catalog, whereas the database server view contains information only about itself.

How to Find DB_KEY and DBID from Recovery Catalog View?
------------------------------------------------------------
SQL> conn catalog_user/catalog_pwd@netservice_name_of_recovery_catalog
Connected.
SQL> SELECT DB_KEY,DBID,NAME,RESETLOGS_TIME FROM RC_DATABASE;

DB_KEY DBID NAME RESETLOGS
---------- ---------- -------- ---------
88 1509380669 DBASE 06-MAY-08

Determining the Schema Version of the Recovery Catalog
------------------------------------------------------------
SQL> SELECT * FROM rcver;

VERSION
------------
10.02.00.00


You can query RC_BACKUP_SET,RC_BACKUP_CONTROLFILE,RC_BACKUP_DATAFILE,RC_BACKUP_CORRUPTION,
RC_STORED_SCRIPT,RC_BACKUP_FILES views as of your interest.

Related Documents:
---------------------
Working with Recovery Catalog
How to Use Recovery Catalog
How to Create Recovery Catalog

Within RMAN you can save commands and execute it whenever you wish. Stored scripts bring this facility where we should not bother about OS scripts whether RMAN client has proper permission on it or not.

Stored Scripts can be two types.

1)Global Stored Scripts:A global stored script can be run against any database registered in the recovery catalog, if the RMAN client is connected to the recovery catalog and a target database.

2)Local Stored Scripts:A local stored script is associated with the target database to which RMAN is connected when the script is created, and can only be executed when you are connected to that target database.

How to Created Stored Script:
---------------------------------
To create local stored script.
CREATE SCRIPT query_backup
{
SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}

To create global stored with a comment added to it,

CREATE GLOBAL SCRIPT global_query_backup
COMMENT 'This is a sample global script which returns some query'
{
SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}

Alternatively you can create script from a text file. To create local script from text file in '/oradata2' just use,

CREATE SCRIPT test_script FROM FILE '/oradata2/my_script_file.txt';

How to run Stored Scripts:
------------------------------
To run stored script use,
RUN{
EXECUTE SCRIPT query_backup;
}
It will first search for local stored script with name query_backup. If not found then will check global stored script with the name. If you have same name in both local and global script then to run global script explicitly use,

RUN{
EXECUTE GLOBAL SCRIPT global_query_backup;
}

Displaying a Stored Script:
----------------------------------
PRINT SCRIPT query_backup;
PRINT GLOBAL SCRIPT global_query_backup;

RMAN> PRINT GLOBAL SCRIPT global_query_backup;

printing stored global script: global_query_backup
{SHOW ALL;
REPORT NEED BACKUP;
REPORT OBSOLETE;
}

Listing Stored Scripts:
-----------------------------
To view both global and local, for the currently connected target database use,

LIST SCRIPT NAMES;

To view only global script names use,

LIST GLOBAL SCRIPT NAMES;

To view the names of all scripts stored in the current recovery catalog, including global scripts and local scripts for all target databases registered in the recovery catalog, use,

LIST ALL SCRIPT NAMES;

Remember that to run LIST SCRIPT NAMES RMAN must be connected to target database.

To run LIST GLOBAL SCRIPT NAMES and LIST ALL SCRIPT NAMES RMAN need not to be connected to target database.

Example:
---------------
-bash-3.00$ rman CATALOG catalog_user/catalog_pwd@saturn:1521/THERAP.SATURN.THERAPBD.COM

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 8 01:14:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to recovery catalog database

RMAN> LIST GLOBAL SCRIPT NAMES;
List of Stored Scripts in Recovery Catalog
Global Scripts
Script Name
Description
-----------------------------------------------------------------------
global_query_backup
This is a sample global script which returns some query

Updating Stored Scripts:
-------------------------
To update a script use,
REPLACE [GLOBAL]SCRIPT
{
BACKUP DATABASE PLUS ARCHIVELOG;
}
Here like,
REPLACE SCRIPT query_backup
{
BACKUP DATABASE PLUS ARCHIVELOG;
}
If the script does not already exist, then RMAN creates it.

Deleting Stored Scripts
--------------------------------
DELETE SCRIPT query_bakcup;
DELETE GLOBAL SCRIPT global_query_backup;

If you use DELETE SCRIPT without GLOBAL, and there is no stored script for the target database with the specified name, RMAN will look for a global stored script by the specified name and delete the global script if it exists.

Related Documents:
-----------------------
How to Create Recovery Catalog and Use it

Registering Multiple Databases in a Recovery Catalog
-------------------------------------------------------------
You can register multiple databases in a recovery catalog; that means you can keep multiple database repository information in a single recovery catalog. But one restriction is each database DBID must be different as RMAN distinguish one database from another by DBID.

So whenever you just copy one database with user managed copy or by RMAN restore and recover then both database DBID is same. So they can't be register in same recovery catalog. In that case if you want to do so you have to change DBID. The method of changing DBID is described here Change DBID.

Unregistering a Target Database from the Recovery Catalog
-------------------------------------------------------------------
To unregister a target database from the recovery catalog, just follow the procedure.
1)Connect to Recovery Catalog database.

-bash-3.00$ rman target / CATALOG catalog_user/catalog_pwd@saturn:1521/THERAP.SATURN.THERAPBD.COM
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 7 23:53:51 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
connected to recovery catalog database

2)issue UNREGISTER DATABASE command.

RMAN> UNREGISTER DATABASE;
database name is "DBASE" and DBID is 1509380669

Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

But remember when a database is unregistered from the recovery catalog, all RMAN repository records in the recovery catalog are lost. The database can be registered again, but the recovery catalog records for that database are then based on the contents of the control file at the
time of re-registration. Records older than the CONTROLFILE_RECORD_KEEP_TIME setting in the target database control file are lost.

Resynchronizing the Recovery Catalog
---------------------------------------
RMAN performs resynchronizations automatically as needed when you execute certain commands, including BACKUP. If you want to manually resynchronize issue RESYNC CATALOG command. In case of resynchronization RMAN compares the recovery catalog to either the current control file of the target database or a backup control file and updates the recovery catalog with information that is missing or changed.

RMAN> RESYNC CATALOG;
starting full resync of recovery catalog
full resync complete



Related Documents:
--------------------
Change DBID

A forums thread on why and how DBAs should handle the need to work long hours.