(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.
Tuesday, August 30, 2011
Thursday, August 25, 2011
SHARED POOL MEMORY USAGE in bytes
Memory UsageV$DB_OBJECT_CACHEThis 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 activeobjects in the shared pool. Useful Columns for V$DB_OBJECT_CACHEMost of the columns of this table provide current state information.
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( ). --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.
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 thetransaction layer and the data layer only (software corrupt blocks).Blocks with physical corruption (ex. fractured block) are marked asthe block is read into the buffer cache and DBMS_REPAIR ignores allblocks 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.trcOracle8 Enterprise Edition Release 8.1.5.0.0 - BetaWith the Partitioning option *** 1998.12.16.15.53.02.000*** SESSION ID:(7.6) 1998.12.16.15.53.02.000kdbchk: row locked by non-existent transaction table=0 slot=0 lockid=32 ktbbhitc=1Block 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/Fsc0x01 xid: 0x0002.011.00000121 uba: 0x008018fb.0345.0d --U- 3 fsc 0x0000.0001cf60 data_block_dump===============tsiz: 0x7b8hsiz: 0x18pbl: 0x28088044bdba: 0x01800003flag=-----------ntab=1nrow=3frre=-1fsbo=0x18fseo=0x19davsp=0x185tosp=0x1850xe:pti[0] nrow=3 offs=00x12:pri[0] offs=0x5ff0x14:pri[1] offs=0x3a60x16:pri[2] offs=0x19dblock_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;
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
S tep 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.
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
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 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>
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>
Subscribe to:
Posts (Atom)