Tuesday, August 30, 2011

Snarky Review: L.A. Noire

(This review contains a few L.A. Noire spoilers and slightly more adult humor than normal. If you are below the age of forty, do not read it. Instead, go to a more family-friendly web site.)


I played a lot of L.A. Noire recently. I got about twenty hours in, had a decent amount of fun, and realized that my parental, old-person life doesn't really encourage playing long games any more. Which is worrying, as that is the sort of game I write. Whenever I look at my work and go, "I have no interest in playing games like this," I get to worry. But that's another story.

L.A. Noire is a fairly fun and reasonably innovative game, published by Rockstar Games and developed by Team Bondi, a bunch of Australians. It's a combination of investigation and interrogation mechanics that have appeared in adventure games in the past, combined with the gigantic open world setting of Rockstar games like Grand Theft Auto and Red Dead: Redemption. It's seemed like Rock Star has been trying to see in recent years how much you can stretch this format and still make a good game (e.g. Bully, Red Dead: Redemption). L.A. Noire may be on the outside edge of how far you can push this.

There was a bunch of things I liked and a bunch of places that were rough. I always feel bad mocking those who tried to push the envelope, but hey, they're huge and sell millions of copies, so they can survive my little blog.

You play a detective in late forties Los Angeles, who, if I recall correctly, has a name. Something virile, like Blake Manfulness. His voice and motion capture were done by Ken Cosgrove, Accounts.

You are told to investigate crimes. You go places and search for clues. Then you interrogate subjects and persons of interest. You have to pay very close attention to what these people say, how they say it, and what facial expressions they show (the facial animation software being the big breakout feature of this game), and, when they lie or hedge the truth, confront them. Then, when the case is solved and someone is arrested, you get a star rating to determine how well you did. So that's the game.

I'm a natural target for this sort of thing. I am a huge sucker for a police procedural. Homicide: A Year On the Killing Streets is one of my all-time favorite books, and, in the manner of all fans of The Wire, I am really annoying when talking about The Wire.

So, for me, the clue-hunting and interrogation were very much the fun of the game. When I was interrogating people, I found I was really concentrating and thinking. When I put a few clues together and caught some rat bastard in a lie, I really felt that my brain power enabled me to do something cool, and that's something that I rarely experience in games these days. So that part of the game was awesome. I didn't even really mind the fact that you can't ever actually lose a mission or let the suspect get away.

But, alas, there are also lots of driving, chasing, shooting, and sneaking sequences straight out of Grand Theft Auto. They are, you know, fine. But very rote and familiar, not the sort of thing I want to spent tens of hours doing anymore. Also, while their enormous rendition of Los Angeles is lavish and awesome, there are very few things you can actually do in it. When playing Red Dead: Redemption, I was constantly being distracted by cool stuff to do. But here, apart from a fixed set of side quests, L.A. seemed a little dry. And if you can't let off steam by going on a rampaging, horrifying kill spree before being gunned down in the street by tanks like the rabid dog you are, what's the point of playing a Rockstar game?

And, double alas, the farther you get into the game, the less important the questioning becomes. The game ends not with an awesome Prime Suspect-style battle of wits between detective and suspect, but just another gunfight. In a sewer. I've been a gamer for a long time. I'm tired of gunfights in sewers.

So there is ambivalence here. I admire Rockstar greatly for spending a ton of money to make a big game that's not a sequel and actually tries to do new things. That is hugely to their credit, and there's plenty of good stuff here. I just wish they'd taken the cool stuff that works and concentrated it into a much shorter game.

A few other thoughts ...

This game is really for adults. Even by Rockstar standards. After working on the murder cases, all I wanted was for one of the dead women to have some clothes on, for God's sake. Though their pubic hair rendering engine is first rate.

Along these lines, L.A. Noire contains more checking for semen than any video game I have ever played. This is entirely to their credit. Also, the game played much faster once I went into settings and mapped Check For Semen to the right trigger.

We should take a moment to remember the lost. After L.A. Noire came out, there were allegations that Team Bondi pushed its employees to long stretches of hundred-hour weeks to make this game. Based on the standards of the industry and the obvious amount of work in this game, I entirely believe it. This sort of crap is why I am determined to stay an indie developer as long as possible. I have children. I'd like to, you know, see them.

L.A. Noire is guilty of my current least-favorite writing flaw: Having one mission completely nullify all of the story elements of the several missions before it. You see, int he part of the game where you are a homicide detective, you have six cases. In the first five, you investigate murdered women and arrest a perpetrator. In the sixth case, following a series of tedious puzzle-solving and climbing sequences, you learn that the previous five men you arrested are all innocent and the murders were committed by some crazy guy that you shoot in a tunnel or something.

If there is any flaw that really bugs me about this game, it's that it says, "You can be a badass detective and investigate crimes and outwit criminals." And then it makes you spend a huge swath of the middle game arresting the wrong guys for crime after crime. What a waste. It shows a lack of respect for the player and the premise.

But still. It's something different. It's ambitious. It's had decent sales. Everyone involved deserves applause for making this thing, and I'm really glad it didn't tank. At this point, we gamers have to take all the innovation we can get.

Thursday, August 25, 2011

SHARED POOL MEMORY USAGE in bytes


Memory Usage

V$DB_OBJECT_CACHE
This view provides object level statistics for objects in the library cache (shared pool).
This view provides more details than V$LIBRARYCACHE and is useful for finding active
objects in the shared pool.

Useful Columns for V$DB_OBJECT_CACHE


Most of the columns of this table provide current state information.
  • OWNER: Object owner
  • NAME: Object name (First 1000 characters of SQL text for anonymous blocks/cursors)
  • TYPE: Type of object (for example, sequence, procedure, function, package, package body, trigger)
  • KEPT: Tells if the object is pinned in the shared pool (yes, no)
  • SHARABLE_MEM: Amount of sharable memory used
  • PINS: Sessions currently executing this object
  • LOCKS: Sessions currently locking this object



QuickSql:
--generate sql to pin objects in the shared_pool which are not currently pinned.  
select 'exec DBMS_SHARED_POOL.keep('||chr(39)||owner||'.'||NAME||chr(39)||','||chr(39)||'P'||chr(39)||');' as sql_to_run 
from  V$DB_OBJECT_CACHE where TYPE in ('PACKAGE','FUNCTION','PROCEDURE') and loads > 50 and kept='NO' and executions > 50;   
 
SQL_TO_RUN --------------
exec dbms_shared_pool.keep('SYS.DBMS_JAVA','P'); 
exec dbms_shared_pool.keep('SYS.DBMS_OUTPUT','P'); 
exec dbms_shared_pool.keep('SYS.DBMS_PIPE','P'); 
exec dbms_shared_pool.keep('SYS.DBMS_REGISTRY','P'); 
exec dbms_shared_pool.keep('SYS.DBMS_RLS','P'); 
exec dbms_shared_pool.keep('SYS.OWA_MATCH','P'); 
exec dbms_shared_pool.keep('SYS.OWA_SEC','P'); 
exec dbms_shared_pool.keep('SYS.OWA_UTIL','P'); 
exec dbms_shared_pool.keep('SYS.PLITBLM','P'); 
exec dbms_shared_pool.keep('SYS.STANDARD','P'); 
exec dbms_shared_pool.keep('SYS.SYSEVENT','P');
 

--show distribution of shared pool memory across different types of objects.
--show if any of the objects have been pinned using the procedure DBMS_SHARED_POOL.KEEP(). 
col type for a20 
col kept for a4 
select type,count(*),kept,round(SUM(sharable_mem)/1024,0) share_mem_kilo 
from V$DB_OBJECT_CACHE where sharable_mem != 0 
GROUP BY type, kept order by 3,4; 
 
TYPE                   COUNT(*) KEPT SHARE_MEM_KILO 
-------------------- ---------- ---- -------------- 
APP CONTEXT                   1 NO                1 
SEQUENCE                      2 NO                3 
NON-EXISTENT                  3 NO                3 
PIPE                          5 NO                6 
PUB_SUB                       5 NO                8 
TRIGGER                       4 NO               14 
FUNCTION                      4 NO               21 
SYNONYM                      12 NO               56 
VIEW                         29 NO               66 
TABLE                        78 NO              161 
PACKAGE BODY                 11 NO              166 
PACKAGE                      12 NO              623 
CURSOR                    42270 NO           332424 
INDEX                         4 YES               5 
CLUSTER                       6 YES              12 
TABLE                        20 YES              43
 
--find objects with large number of loads  
col name for a80 trunc 
 
SELECT owner,sharable_mem,kept,loads,name 
from V$DB_OBJECT_CACHE WHERE loads > 2 ORDER BY loads DESC; 
OWNER                SHARABLE_MEM KEP      LOADS NAME 
-------------------- ------------ --- ---------- ---------------------------------------- 
SYS                         29304 NO          89 DBMS_SESSION 
GENERAL                      2567 NO          84 GJBPRUN 
BANSECR                     22471 NO          78 G$_SECURITY_PKG 
BANINST1                    27690 NO          66 GB_COMMON 
BANINST1                    27005 NO          61 GB_MESSAGING 
SYS                         16496 NO          61 DUAL 
GENERAL                      2127 NO          52 GUBINST 
BANSECR                     22464 NO          48 G$_VPDI_SECURITY
--find objects using large amounts of memory. pin using DBMS_SHARED_POOL.KEEP( ).  
 
SELECT owner,name,sharable_mem,kept FROM V$DB_OBJECT_CACHE 
WHERE sharable_mem > 102400 AND kept = 'NO' ORDER BY sharable_mem DESC; 
 
OWNER      NAME                                     SHARABLE_MEM KEP 
---------- ---------------------------------------- ------------ --- 
select /*+ Rule */ sum(f.bytes)/1024, fl      1463864 NO 
select /*+ Rule */ sum(f.bytes)/1024, fl      1461928 NO 
select /*+ Rule */ sum(f.bytes)/1024, fl      1357936 NO 
select /*+ Rule */ sum(f.bytes)/1024, fl      1353400 NO 
SELECT       PHVTIME_ID,PHVTIME_LAST_NAM      1249000 NO 
insert into SMTCRSE (SMTCRSE_PIDM,SMTCRS      1215256 NO 
insert into SMTCRSE (SMTCRSE_PIDM,SMTCRS      1189144 NO 
SELECT PHVTIME_ID,PHVTIME_LAST_NAME,PHVT      1182456 NO
 
 
--sharable memory in shared pool consumed by the object 
col name for a40 
col type for a30 
 
select OWNER,NAME,TYPE,SHARABLE_MEM from V$DB_OBJECT_CACHE 
where SHARABLE_MEM > 10000 
and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE') 
order by SHARABLE_MEM desc; 
 
OWNER      NAME                                     TYPE                           SHARABLE_MEM 
---------- ---------------------------------------- ------------------------------ ------------ 
SYS        STANDARD                                 PACKAGE                              499812 
SYS        DBMS_JAVA                                PACKAGE                               82685 
SYS        OWA_UTIL                                 PACKAGE BODY                          66732 
BANINST1   BWCKFRMT                                 PACKAGE BODY                          62009 
BANINST1   RB_AWARD_DISBURSEMENT                    PACKAGE BODY                          60606 
WTAILOR    TWBKBSSF                                 PACKAGE BODY 
35152
--determine which objects to pin execute when database is in steady state. 
 
set linesize 150 
col Oname for a40 
col owner for a15 
col Type for a20 
 
SELECT owner||'.'||name Oname,substr(type,1,12) "Type", sharable_mem "Size",executions,loads, kept 
FROM V$DB_OBJECT_CACHE 
WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE') 
AND executions > 0  ORDER BY executions desc,loads desc,  sharable_mem desc; 
 
ONAME                                    Type                       Size EXECUTIONS      LOADS KEP 
---------------------------------------- -------------------- ---------- ---------- ---------- --- 
BANINST1.DML_COMMON                      PACKAGE BODY               7651    1361387          1 NO 
SYS.STANDARD                             PACKAGE BODY              32684     994931          1 NO 
SYS.PLITBLM                              PACKAGE                    7971     742622          6 NO 
BANSECR.G$_VPDI_SECURITY                 PACKAGE BODY               9472     339687          1 NO 
BANINST1.ROKLOGS                         PACKAGE BODY              10568      78872          1 NO 
BANINST1.GB_COMMON                       PACKAGE BODY              15978      45413          8 NO 
SYS.DBMS_STANDARD                        PACKAGE                   41969      40198         44 NO 
BANSECR.G$_SECURITY_PKG                  PACKAGE BODY              26807      37695         11 NO 
BANINST1.GB_MESSAGING                    PACKAGE BODY              14053      24138          8 NO 
SYS.DBMS_SESSION                         PACKAGE BODY              10472      14484          8 NO 
SYS.DBMS_PIPE                            PACKAGE BODY               8229      11156          1 NO 
BANINST1.ROKPVAL                         PACKAGE BODY              95696      10021          1 NO 
SYS.DBMS_APPLICATION_INFO                PACKAGE BODY               4641       9568         10 NO 
WTAILOR.TWBKBSSF                         PACKAGE BODY              35152       7820          1 NO 
SYS.HTP                                  PACKAGE BODY              24679       7108          1 NO 
BANINST1.RB_AWARD_DISBURSEMENT           PACKAGE BODY              60606       6122          1 NO
--list large, un-pinned objects. 
 
set linesize 150 
col sz for a10 
col name for a100 
col keeped for a6 
 
select to_char(sharable_mem / 1024,'999999') sz_in_K, decode(kept, 'yes','yes  ','') keeped,
owner||','||name||lpad(' ',29 - (length(owner) + length(name))) || '(' ||type||')'name,
null extra, 0 iscur 
from v$db_object_cache v where sharable_mem > 1024 * 1000; 
 

 
--list large, un-pinned procedures, packages, functions. 
 
col type for a25 
col name for a40 
col owner for a25 
 
select owner,name,type,round(sum(sharable_mem/1024),1) sharable_mem_K 
from v$db_object_cache  where kept = 'NO' 
and (type = 'PACKAGE' or type = 'FUNCTION' or type = 'PROCEDURE')
group by owner,name,type order by 4;
 
OWNER                     NAME                                     TYPE                      SHARABLE_MEM_K 
------------------------- ---------------------------------------- ------------------------- -------------- 
SYS                       DICTIONARY_OBJ_NAME                      FUNCTION                            16.1 
SYS                       DICTIONARY_OBJ_TYPE                      FUNCTION                            16.2 
SYS                       SYSEVENT                                 FUNCTION                            16.6 
SYS                       DBMS_APPLICATION_INFO                    PACKAGE                             20.5 
SYS                       DBMS_OUTPUT                              PACKAGE                             21.2 
SYS                       DBMS_STANDARD                            PACKAGE                             36.8 
SYS                       STANDARD                                 PACKAGE                            428.2

Thursday, August 18, 2011

I Don't Finish Games Because I Am Old

This article is getting a lot of play today. Short version: Very few people finish really long games.

Duh. I've pretty much given up on playing any long game (say, over 20 hours) that I'm not sure is completely awesome. I just quit L.A. Noire a good eight cases from the end, and I feel like I played it for too long.

I mean, a game that takes 40 hours? A whole workweek? In this day and age? Who can do that? People don't leave games unfinished because they're weak or dumb or lazy or bad people. It's because, unless the game is really awesomesauce, playing it for 40 hours just isn't a worthwhile use of one's time.

Which puts me in a weird position, because I write long games. I try to put a lot of work into the endings, even though I know most people won't see them. Maybe this is a warning that I should start doing something else, but I don't know what. I tried to make Avadon: The Black Fortress a shorter game but a higher quality experience. Dunno if I succeeded, but that's the direction I wanted to go.

It's one of the reasons the industry is moving towards shorter, cheaper games. And I'm moving along with the trend, a tiny bit. But I'll be writing longish games as long as there is a market for them. The main strength of my games is the sprawling, epic stories. I can't really do that in a ten hour game. So I'll keep doing basically what I do, even though I hear the distant rumbling of impending doom.

Wednesday, August 17, 2011

Avadon Is Out On Steam!


Today, Avadon: The Black Fortress goes live on Steam.

Unsurprisingly, I'm pretty excited about it. After 16 years of being a tiny, invisible, basement-dwelling bottom feeder, for a few precious weeks, I get to act like I'm a real developer. With a real distributor, a nice trailer video, and everything. Yes, there will be money, and that's always nice, but it's the recognition I'm sort of focused on now.

Writing Indie games has provided me with a very good living, and I don't have the right to complain about anything. I wrote games. I sold 8-10 thousand games a year. (Having a big back catalog is awesome.) I was content.

But then the Indie boom took off. Indie devs were getting famous. Many could make a living, and some got rich. Amazingly, people stopped acting like I wasn't a total loser for doing what I do. (This change happened about the time the word 'shareware' disappeared.) After all these years, it was impossible to watch all of this excitement and not want to be a part of it.

And now, thanks to Valve, I'm going to be visible. I'm getting a shot at the spotlight. Avadon: The Black Fortress is a very good game. It's got a great story, interesting, epic battles, and a lot of cool stuff. It's simply a fun game. Will its retro old-school action take the world by storm? Maybe a lot. Maybe a little. And I'll do all I can to be content with what comes.

The Steam Thing does mean that we are embarking on a great experiment, something that we never planning on doing. But, the way the online games market is moving, something that seems like the right choice.

Avadon: The Black Fortress Is $9.99 On Steam

I've written a lot about how I think it's important to not price niche games too cheaply, and I stand by that. However, at the same time, Avadon will be only ten bucks on Steam, the cheapest we've ever made our newest game for PC/Mac. Why?

1. Steam felt it was the best price. I went into this trusting their judgment, because they know a lot more about selling Indie games than I do. When you're an Indie and Steam comes knocking, you don't say no.

2. The whole game industry is shifting. These days, a huge proportion of games online are sold for a low price without demos. People buy games on impulse, sight unseen. That way, if they don't like it they aren't out a lot of money.

In these markets, charging $15 or $20 for games, like I want to, isn't feasible. It's too much money to pay for a game you aren't sure about. If someone buys my game for $10 and hates it, I'm a little unhappy. But $20? I don't want to take kids' allowance money that way.

So I'm charging $10 on Steam and for the iPad. By the standards of that market, it's a hefty price, enough for me to earn my living. It's cheap enough to work as an impluse buy. It isn't the $1 or $2 price that I'm still sure would put me out of business.

This means I need to adjust the prices I charge on my own web site. I have changed the price of Avadon to $20, and in the future we will very likely reduce the prices of our earlier games as well. Our next game, Avernum: Escape From the Pit will start out at $20. If this grand experiment works well, we may make future games cheaper still, though I doubt any new game on our own web site will ever go below $15.

I'm expecting that some of our users who paid $25 on our site will be angry. I can totally understand this. However, all computer games get cheaper as they get older, even games that have only been to a few months. (Check out Best Buy of any other decently sized electronics store if you don't believe me.) Also, until we had access to mass-market outlets like iTunes, we were never going to generate enough sales to survive at a lower price.

I don't like making my fans angry, but, again, when Steam comes knocking, you don't say no. And our future games will be cheaper, so everyone is getting something out of it.

Now I'll sit on my edge of chair and wait to see how Avadon does. Fortunately, there's not much suspense. We're being released opposite Bastion, so hope may not be warranted at this point.

A Question a Lot of People Asked Below:

Why is the game still $20 on our web site?

Short answer: Charging this little is an experiment. I believe that Indie devs who write niche products need to charge more for their work than the more mass market, casual, $0.99 app market. The question is whether a $10 price works. If going onto Steam for ten bucks turns out to not be a good idea (or if they don't want any more of our games), we need to maintain a higher baseline price on our site.

I know this seems odd, but I assure you that it makes sense from where I sit. And, by the way, we are FAR from the only developer who does this. For example, World of Goo is $20 on their site but $10 on Steam. And they are far smarter than we are.

Saturday, August 6, 2011

DBMS_REPAIR example

Refrence from Metelink Doc [ID 68013.1]
 
Checked for relevance on 12-SEP-2010 
 
PURPOSE
 
 This document provides an example of DBMS_REPAIR as introduced in Oracle 8i.
 Oracle provides different methods for detecting and correcting data block
 corruption - DBMS_REPAIR is one option. 
 
 WARNING: Any corruption that involves the loss of data requires analysis to 
 understand how that data fits into the overall database system. Depending on 
 the nature of the repair, you may lose data and logical inconsistencies can 
 be introduced; therefore you need to carefully weigh the gains and losses
 associated with using DBMS_REPAIR.
 
 
SCOPE & APPLICATION
 
 This article is intended to assist an experienced DBA working with an Oracle
 Worldwide Support analyst only.  This article does not contain general
 information regarding the DBMS_REPAIR package, rather it is designed to provide
 sample code that can be customized by the user (with the assistance of
 an Oracle support analyst) to address database corruption.  The 
 "Detecting and Repairing Data Block Corruption" Chapter of the Oracle8i 
 Administrator's  Guide should be read and risk assessment analyzed prior to 
 proceeding.
 
 
RELATED DOCUMENTS
 
  Oracle 8i Administrator's Guide,  DBMS_REPAIR Chapter
 
 
Introduction
=============
 
Note: The DBMS_REPAIR package is used to work with corruption in the
transaction layer and the data layer only (software corrupt blocks).
Blocks with physical corruption (ex. fractured block) are marked as
the block is read into the buffer cache and DBMS_REPAIR ignores all
blocks marked corrupt.
 
The only block repair in the initial release of DBMS_REPAIR is to 
*** mark the block software corrupt ***.
 
 
A backup of the file(s) with corruption should be made before using package.
 
 
 
Database Summary
===============
 
A corrupt block exists in table T1.  
 
SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                      NOT NULL NUMBER(38)
 COL2                                               CHAR(512)
 
 
SQL> analyze table t1 validate structure;
analyze table t1 validate structure
*
ERROR at line 1:
ORA-01498: block check failure - see trace file
 
---> Note: In the trace file produced from the ANALYZE, it can be determined
---        that the corrupt block contains 3 rows of data (nrows = 3).
---        The leading lines of the trace file follows:
 
Dump file /export/home/oracle/product/8.1.5/admin/V815/udump/v815_ora_2835.trc
Oracle8 Enterprise Edition Release 8.1.5.0.0 - Beta
With the Partitioning option
 
*** 1998.12.16.15.53.02.000
*** SESSION ID:(7.6) 1998.12.16.15.53.02.000
kdbchk: row locked by non-existent transaction
        table=0   slot=0
        lockid=32   ktbbhitc=1
Block header dump:  0x01800003
 Object id on Block? Y
 seg/obj: 0xb6d  csc: 0x00.1cf5f  itc: 1  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   xid:  0x0002.011.00000121    uba: 0x008018fb.0345.0d  --U-    3  fsc 
0x0000.0001cf60
 
data_block_dump
===============
tsiz: 0x7b8
hsiz: 0x18
pbl: 0x28088044
bdba: 0x01800003
flag=-----------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x19d
avsp=0x185
tosp=0x185
0xe:pti[0]      nrow=3  offs=0
0x12:pri[0]     offs=0x5ff
0x14:pri[1]     offs=0x3a6
0x16:pri[2]     offs=0x19d
block_row_dump:
 
[... remainder of file not included]
 
end_of_block_dump
 
 

Recovering Datafiles in ARCHIVELOG Mode

Recovering Database when the database is running in ARCHIVELOG Mode.


Recovering from the lost of Damaged Datafile.

If you have lost one datafile. Then follow the steps shown below.
STEP 1. Shutdown the Database if it is running.

STEP 2. Restore the datafile from most recent backup.

STEP 3. Then Start sqlplus and connect as SYSDBA.
$sqlplus
Enter User:/ as sysdba
SQL>Startup mount;
SQL>Set autorecovery on;
SQL>alter database recover;

 If all archive log files are available then recovery should go on smoothly. After you get the "Media Recovery Completely" statement. Go on to next step.

STEP 4. Now open the database
SQL>alter database open;

Upon Startup DB Instance one of Datafile is missing or corrupted

SQL> connect / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.
Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.

ORA-01157: cannot identify/lock data file 4 - see DBWR trace file

ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'

The error message tells us that file# 4 is missing. Note that although the startup command has failed, the database is in the mount state

Step 1 Copy the missing Datafile from last taken Backup.

Note : Here you need all the archive log files after last taken backup

Step 2 sql> recover datafile 4.

When Media recovery completed messages shown you can open the database

Step 3 sql> alter database open.

Time Based Recovery (INCOMPLETE RECOVERY).

Suppose a user has a dropped a crucial table accidentally and you have to recover the dropped table.
You have taken a full backup of the database on Monday night and the table was created on Tuesday and thousands of rows were inserted into it. Some user accidentally drop the table on Thursday and nobody notice this until Saturday.

Now to recover the table follow these steps.

STEP 1. Shutdown the database and take a full offline backup.

STEP 2. Restore all the datafiles, logfiles and control file from the full offline backup which was taken on Monday.

STEP 3. Start SQLPLUS and start and mount the database.

STEP 4. Then give the following command to recover database until specified time.

SQL> recover database until time '2011:08:16:13:55:00' using backup controlfile;

STEP 5. Open the database and reset the logs. Because you have performed a Incomplete Recovery, like this
SQL> alter database open resetlogs;

STEP 6. After database is open. Export the table to a dump file using Export Utility.

STEP 7. Restore from the full database backup which you have taken before this activity.

STEP 8. Open the database and Import the table.

RECOVERING THE DATABASE IN NOARCHIVELOG MODE.

Option 1: When you don’t have a backup.

If you have lost one datafile and if you don't have any backup and if the datafile does not contain important objects then, you can drop the damaged datafile and open the database. You will loose all information contained in the damaged datafile.

The following are the steps to drop a damaged datafile and open the database.
(UNIX)

STEP 1: First take full backup of database for safety.

STEP 2: Start the sqlplus and give the following commands.

$sqlplus
Enter User:/ as sysdba
SQL> STARTUP MOUNT

SQL> ALTER DATABASE DATAFILE  '/u01/ica/usr1.dbf '  offline drop;

SQL>alter database open;

Option 2: When you have the Backup.

If the database is running in Noarchivelog mode and if you have a full backup. Then there are two options for you.

1. Either you can drop the damaged datafile, if it does not contain important information which you can  afford to loose.

2 . Or you can restore from full cold backup. You will loose all the changes made to the database since last full backup.

STEP 1: Take a full backup of current database.

STEP 2: Restore from full database backup i.e. copy all the files from backup to their original locations.
(UNIX)

Suppose the backup is in  "/u2/oracle/backup" directory. Then do the following.

sqlplus>Shutdown Abort

$cp /u02/backup/*  /u01/ica

Note: This will copy all the files from backup directory to original destination. Also remember to copy the control files and redologfiles to all the mirrored locations.

sqlplus> startup




How to take user managed Database Backups

TAKING OFFLINE BACKUPS. ( UNIX )

Shutdown the database if it is running. Then start SQL Plus and connect as SYSDBA.
$sqlplus
SQL> connect / as sysdba

SQL> Shutdown immediate

SQL> Exit

After Shutting down the database. Copy all the datafiles, logfiles, controlfiles, parameter file and password file to your backup destination.

TIP:
To identify the datafiles, Logfiles query the data dictionary tables V$DATAFILE and V$LOGFILE before shutting down.
Lets suppose all the files are in "/u01/ica" directory. Then the following command copies all the files to the backup destination /u02/backup.

$cd /u01/ica

$cp * /u02/backup/

Be sure to remember the destination of each file. This will be useful when restoring from this backup. You can create text file and put the destinations of each file for future use. Now you can open the database.

TAKING ONLINE (HOT) BACKUPS.(UNIX)

To take online backups the database should be running in Archivelog mode. To check whether the database is running in  Archivelog mode or Noarchivelog mode. Start sqlplus and then connect as SYSDBA.
After connecting give the command "archive log list" this will show you the status of archiving.
$sqlplus

Enter User:/ as sysdba

SQL> ARCHIVE LOG LIST

If the database is running in archive log mode then you can take online backups.
Let us suppose we want to take online backup of  "USERS" tablespace. You can query the V$DATAFILE view to find out the name of datafiles associated with this tablespace. Lets suppose the file is  
"/u01/ica/usr1.dbf ".

Give the following series of commands to take online backup of USERS tablespace.

$sqlplus

Enter User:/ as sysdba

SQL> alter tablespace users begin backup;

SQL> host cp /u01/ica/usr1.dbf   /u02/backup

SQL> alter tablespace users end backup;

SQL> exit;

ALTER DATABASE BEGIN BACKUP ON OPEN MODE

AS SYSDBA
sql>alter database begin backup;
Database altered.
sql>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE

NOTE: All the datafiles are in backup mode, now you can copy your all datafiles to backup location.


sql>alter database end backup;
Database altered.
sql>select file#,status from v$backup;
FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE

Bringing the database in Archivelog /No Archive Mode

Opening or Bringing the database in Archivelog mode.

To open the database in Archive log mode. Follow these steps:
STEP 1: Shutdown the database if it is running.
STEP 2: Take a full offline backup.
STEP 3: Set the following parameters in parameter file.

LOG_ARCHIVE_FORMAT=ica%s.%t.%r.arc
LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
If you want you can specify second destination also
LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc1”

Step 3: Start and mount the database.

SQL> STARTUP MOUNT
STEP 4: Give the following command
SQL> ALTER DATABASE ARCHIVELOG;
STEP 5: Then type the following to confirm.
SQL> ARCHIVE LOG LIST;
STEP 6: Now open the database
SQL>alter database open;
Step 7: It is recommended that you take a full backup after you brought the database in archive log mode.

To again bring back the database in NOARCHIVELOG mode.

STEP 1: Shutdown the database if it is running.
STEP 2: Comment the following parameters in parameter file by putting " # " .
 # LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
# LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc2”
# LOG_ARCHIVE_FORMAT=ica%s.%t.%r.arc
 STEP 3: Startup and mount the database.
 SQL> STARTUP MOUNT;
STEP 4: Give the following Commands
SQL> ALTER DATABASE NOARCHIVELOG;
STEP 5: Shutdown the database and take full offline backup.

Flashback Of DATABASE/TABLE with Normal Restore Point

Flashback Of DATABASE with Normal Restore Point

Flashback Database enables you to rewind your entire database backward in time, reversing the effects of unwanted database
changes within a given time window. The effects are similar to database point-in-time recovery.

Oracle Flashback Database, accessible from both RMAN (by means of the FLASHBACK DATABASE command) and SQL*Plus
(by means of the FLASHBACK DATABASE statement), lets you quickly recover the entire database from logical data corruptions or user errors.

About Normal Restore Points

Creating a normal restore point assigns the restore point name to a specific point in time or SCN, as a kind of bookmark or alias you can use with commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.

Before performing any operation that you may have to reverse, you can create a normal restore point. The name of the restore point  and the SCN are recorded in the control file. Then, if you later need to use Flashback Database, Flashback Table, or point-in-time recovery,
you can refer to the target time using the name of the restore point instead of a time expression or SCN. Defining a normal restore point before an operation to be reversed later eliminates the need to manually record an SCN in advance, or investigate the correct SCN after the fact using features such as Flashback Query.

Normal restore points are very lightweight. The control file can maintain a record of thousands of normal restore points with no significant impact upon database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.

Commands Supporting the Use of Restore Points

Restore points can be used to specify the target SCN in the following contexts:

    The RECOVER DATABASE and FLASHBACK DATABASE commands in RMAN

    The FLASHBACK TABLE statement in SQL*Plus


====================  PRACTICAL EXAMPLE ========================

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1367343104 bytes
Fixed Size                  1302492 bytes
Variable Size             335544356 bytes
Database Buffers         1023410176 bytes
Redo Buffers                7086080 bytes
Database mounted.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.

SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.

SQL> alter database open;

Database altered.

SQL> show parameter db_recover

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
SQL>
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            E:\oracle\product\10.2.0\db_1\RDBMS
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence           25

SQL> alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'  ;
System altered.

SQL> alter system set db_recovery_file_dest_size=2000M;

System altered.

SQL> alter system set db_recovery_file_dest='E:\oracle\product\10.2.0\flash_recovery_area';

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence           25
SQL>
SQL>
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1367343104 bytes
Fixed Size                  1302492 bytes
Variable Size             335544356 bytes
Database Buffers         1023410176 bytes
Redo Buffers                7086080 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.

SQL> alter database open;
Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     23
Next log sequence to archive   25
Current log sequence           25
SQL>
SQL> alter system switch logfile;
System altered.

SQL> show parameter db_flashback_retention_target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL>
SQL>
SQL> create table scott.sales as select * from sh.sales;

Table created.

SQL>
SQL> create restore point b4_change;

Restore point created.

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,  GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
    FROM V$RESTORE_POINT;


NAME            SCN        TIME                                          DATABASE_INCARNATION#  GUA   STORAGE_SIZE
--------------------- --- ------------ ----------------------------------------------------------------------------------------------------------------------------
B4_CHANGE 1515344 05-AUG-11 11.18.48.000000000 PM   2                 NO             0


SQL> alter user scott identified by tiger account unlock;

User altered.

SQL> conn scott/tiger
Connected.

SQL> drop table emp;

Table dropped.

SQL> truncate table sales;
Table truncated.

SQL> select count(*) from sales;

  COUNT(*)
----------
         0

SQL> select count(*) from emp;
select count(*) from emp
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn / as sysdba
Connected.

SQL> flashback database to restore point b4_change;
flashback database to restore point b4_change
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1367343104 bytes
Fixed Size                  1302492 bytes
Variable Size             335544356 bytes
Database Buffers         1023410176 bytes
Redo Buffers                7086080 bytes
Database mounted.
SQL>
SQL>
SQL> flashback database to restore point b4_change;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> conn scott/tiger
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> select count(*) from sales;

  COUNT(*)
----------
    918843

SQL> conn / as sysdba
Connected.
SQL> SELECT NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;

NAME    SCN             TIME              GUARANTEE_FLASHBACK_DATABASE
---------- -------------------------------------------------------------------------------------------------------------------------
B4_CHANGE  1515344 05-AUG-11 11.18.48.000000000 PM      NO


SQL> drop restore point b4_change;

Restore point dropped.

SQL> SELECT NAME, SCN, TIME, GUARANTEE_FLASHBACK_DATABASE FROM V$RESTORE_POINT;

no rows selected


Flashback Of DATABASE with Normal Restore Point via RMAN

SQL> create restore point b4_change ;
Restore point created.

SQL> truncate table scott.sales;

Table truncated.

Now open new command prompt window

C:\Documents and Settings\Administrator>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Aug 5 23:47:08 2011

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

connected to target database: ORCL (DBID=1285180341)

RMAN>
RMAN> shutdown immediate;
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area    1367343104 bytes

Fixed Size                     1302492 bytes
Variable Size                335544356 bytes
Database Buffers            1023410176 bytes
Redo Buffers                   7086080 bytes

RMAN> flashback database to restore point b4_change;
Starting flashback at 05-AUG-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=542 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished flashback at 05-AUG-11

RMAN> alter database open resetlogs;

database opened

RMAN>exit

Flashback Of TABLE with Normal Restore Point

SQL> create restore point b4_delrec;

Restore point created.

SQL> alter table scott.sales  enable row movement;

Table altered.

SQL> select count(*) from scott.sales;
  COUNT(*)
----------
    918843

SQL> delete from scott.sales where rownum < 100;

99 rows deleted.

SQL> select count(*) from scott.sales;
  COUNT(*)
----------
    918744

SQL> commit;
Commit complete.


SQL> flashback table scott.sales to restore point b4_delrec;

Flashback complete.

SQL> select count(*) from scott.sales;

  COUNT(*)
----------
    918843

SQL>