Testing: FLASHBACK, Data Pump and RMAN

I've been having loads of fun the last few days, Learning By Breaking, Learning By...Breaking?, IMPDP and REMAP_TABLESPACE and finally How To: Clean Your Schema (which needs a little work).

All of this in an effort to test my migration script with changes in the hundreds. A quick recap:

1. Import metadata from production environment from 4 schemas. This includes mapping all the tablespaces from production to USER in my sandbox.
2. Create restore point.
3. Run build script.
4. Check for errors, fix errors
5. Flashback database to pre-deployment state.
6. Rinse and repeat as necessary.

If you have read any of those other posts, you'll know that:

a. I'm an idiot.
b. I like to guess.
3. I performed my first "recovery."
d. I learned the basics of Data Pump.
e. I like to break things.

For #1 above, import metadata, I've learned some hard lessons. I've had to repeat this step a number of times because I've either corrupted my database or dropped the restore point before flashing back.

And just now, a colleague of mine helped me out with another problem. See, the import process was extremely slow. Part of the reason (I think) I corrupted the silly thing was because I was mucking around at a level I don't quite understand...storage.

I just sent this pic to him:



I was like, "WTF? Why do I need a 33GB datafile for metadata?"

Him: "Ummm...idiot...it preallocates the space which is based on production."

Me: <crickets>

Me: "How can I change the storage characteristics?" (I sent him the link to the Data Pump docs.)

Me: "How about this?" (Link to the TRANSFORM clause)

Aha...TRANSFORM has 4 options:
1. SEGMENT_ATTRIBUTES (Y, Default, to keep them, N to toss 'em)
2. STORAGE (Y, Default, to keep them, N to toss them)
3. OID
4. PCTSPACE

Where does RMAN fit into all of this? I'm not really sure. Last night I issued

RECOVER DATABASE;

and it worked perfectly. Now, once I get an import completed, I take a backup of the tablespace. (I've since created a separate tablespace for the 2 largest schemas being imported.) That way, if I drop the restore point before flashing back, I should be able to restore it back to it's original state.

One of these days I'll get around to virtualizing all of this. I imagine that has to be easier, import the data, take a snapshot, run the script, fix, revert to previous snapshot. If I did that though, I wouldn't get to play (learn) with all of these cool tools.

FLASHBACK DATABASE

I've used Oracle's flashback technology to restore data, but I never realized that it was possible to flashback the objects as well.

Typically, I've taken an export of the database objects at the point where I want to restore them, do my testing, drop all the users, recreate them and import the dump file back in to "restore."

A colleague of mine sent me a link about GUARANTEE FLASHBACK DATABASE. Pretty cool.

Fired up the local instance and began.

SELECT log_mode, flashback_on 
FROM v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   YES

OK, I cheated and set this all up. My sandbox is typically set in NOARCHIVELOG mode and I had to figure out how to turn flashback on.

Now to create a restore point.

CREATE RESTORE POINT my_restore_point;

There are 2 types of restore points, Normal (the default) and Guarantee. Normal will age out of the control file after a set amount of time. Guarantee must be explicitly dropped. Oracle has the capability to store thousands of restore points.

So I mocked up a small example:

SYS@ELEVEN>SYS@ELEVEN>SYS@ELEVEN>DESC CJUSTICE.DEPT
 Name                       Null?    Type
 -------------------------- -------- --------------------- 
 DEPTNO                     NOT NULL NUMBER(2)
 DNAME                               VARCHAR2(14)
 LOC                                 VARCHAR2(13)

SYS@ELEVEN>ALTER TABLE CJUSTICE.DEPT ADD ( SOME_COLUMN NUMBER );

Table altered.

Elapsed: 00:00:00.87

SYS@ELEVEN>DESC CJUSTICE.DEPT

 Name                       Null?    Type
 -------------------------- -------- ---------------
 DEPTNO                     NOT NULL NUMBER(2)
 DNAME                               VARCHAR2(14)
 LOC                                 VARCHAR2(13)
 SOME_COLUMN                         NUMBER

SYS@ELEVEN>ALTER DATABASE CLOSE;

Database altered.

Elapsed: 00:00:05.57

SYS@ELEVEN>FLASHBACK DATABASE TO RESTORE POINT my_restore_point;

Flashback complete.

Elapsed: 00:00:05.84

SYS@ELEVEN>SHUTDOWN;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SYS@ELEVEN>STARTUP;
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1334380 bytes
Variable Size             327156628 bytes
Database Buffers          201326592 bytes
Redo Buffers                5844992 bytes
Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@ELEVEN>ALTER DATABASE OPEN RESETLOGS;

Database altered.

Elapsed: 00:00:25.18
SYS@ELEVEN>DESC CJUSTICE.DEPT

 Name                     Null?    Type
 ------------------------ -------- -------------------------------
 DEPTNO                   NOT NULL NUMBER(2)
 DNAME                             VARCHAR2(14)
 LOC                               VARCHAR2(13)

Voila!

Pretty amazing me thinks.

I still don't know if you can do this without shutting down the database. But it suits my purposes for the time being.

I'm constantly amazed at how much I don't know about Oracle.