Following up on the previous post, now suppose the table is lost but you do not want to rollback the entire database to prior to the loss.
This is a clever way of doing this but it means living with the truncated table until some downtime is available.
Flashback until the appropriate time as outlined in the previous post. Then open the database read-only and not open resetlogs. Export the table before the loss occured. After the export is completed issue a RECOVER DATABASE command, so Oracle fast forwards to the time of clean shutdown. Open the database and then import back the Table. Thus you have the table before data was lost without having flashed back the entire database.
Monday 20 April 2009
Restore the Entire Database with Flashback
Don't know how this will work on a large database, but looks like the easiest way to restore the entire Oracle database. On a home grown database, 3GB, it took just a few seconds.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance
shut down.
SQL> startup mount exclusive;
ORACLE instance started.
Database mounted.
SQL> FLASHBACK DATABASE to timestamp to_timestamp('20-04-2009 10:00', 'DD-MM-YYYY HH24:MI:SS');
Flashback complete.
SQL> alter database open resetlogs;
If on the other hand you are ok to just recover the database to the last scn, for example a Table has been accidentally truncated in a DWH, and no transactional activity had taken place before the truncate, you can recover the database back to the scn, using Flashback by
sql> select current_scn from v$database;
Current SCN
------------
2592544
Table truncated
sql> Connect / as sysdba
connected.
sql>shutdown immediate;
Database closed.
Database dismounted
ORACLE instance shutdown.
sql> Startup Mount;
sql>flashback database to scn 2592544;
Flashback complete
sql> alter database open resetlogs;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance
shut down.
SQL> startup mount exclusive;
ORACLE instance started.
Database mounted.
SQL> FLASHBACK DATABASE to timestamp to_timestamp('20-04-2009 10:00', 'DD-MM-YYYY HH24:MI:SS');
Flashback complete.
SQL> alter database open resetlogs;
If on the other hand you are ok to just recover the database to the last scn, for example a Table has been accidentally truncated in a DWH, and no transactional activity had taken place before the truncate, you can recover the database back to the scn, using Flashback by
sql> select current_scn from v$database;
Current SCN
------------
2592544
Table truncated
sql> Connect / as sysdba
connected.
sql>shutdown immediate;
Database closed.
Database dismounted
ORACLE instance shutdown.
sql> Startup Mount;
sql>flashback database to scn 2592544;
Flashback complete
sql> alter database open resetlogs;
Friday 17 April 2009
RMAN Correct Syntax for Until Time Restore
As brilliant as the book by Matthew Hart and Scott Jesse on High Availability with Oracle 10g RAC is, there are some unfortunate syntax errors which shouldn't be there.
For example on pp 304, the correct syntax for when specifying a point in time to recover from with RMAN should read as follows:
RMAN> run {
set until time "to_date('04-NOV-2003 12:00:00', 'DD-MON-YYYY hh24:mi:ss')";
restore database;
recover database;
}
and not the way it is printed in the book.
For example on pp 304, the correct syntax for when specifying a point in time to recover from with RMAN should read as follows:
RMAN> run {
set until time "to_date('04-NOV-2003 12:00:00', 'DD-MON-YYYY hh24:mi:ss')";
restore database;
recover database;
}
and not the way it is printed in the book.
Tuesday 17 February 2009
Installing Oracle 10g on SUSE10
Had problem installing Oracle10g on a Linux server using SUSE10 O/S. The installation file only seems to think Oracle is certified to be installed on SUSE9. To get round the problem run the install file with the following option:
./runInstaller -ignoreSysPrereqs
./runInstaller -ignoreSysPrereqs
What Metadata is the MicroStrategy Project Source Connected to?
There is nothing from the GUI inetrface that tells you what metadata DSN your Microstrategy Project Source is connected to. This is one way of finding out however:
In order to determine which DSN is being used by Intelligence Server for metadata connectivity, navigate to the following registry key shown below:
HKEY_LOCAL_MACHINE>SOFTWARE>MicroStrategy>Data Sources>Castor Server>Location
and see the value.
This has to be done on the machine where the Intelligence Server is.
In order to determine which DSN is being used by Intelligence Server for metadata connectivity, navigate to the following registry key shown below:
HKEY_LOCAL_MACHINE>SOFTWARE>MicroStrategy>Data Sources>Castor Server>Location
and see the value.
This has to be done on the machine where the Intelligence Server is.
Monday 30 June 2008
Narrowcast Server Does Not Start After Password Change
After changing the windows account password, a user cannot the start the MicroStrategy Narrowcast Server 8.x system.
The following two services have to be updated with the new password:
MicroStrategy Distribution Manager and the MicroStrategy Execution Engine services
Right click on the above services and chose properties. Then update the passwords under the Log On tab.
The following two services have to be updated with the new password:
MicroStrategy Distribution Manager and the MicroStrategy Execution Engine services
Right click on the above services and chose properties. Then update the passwords under the Log On tab.
Tuesday 5 February 2008
MicroStrategy Narrowcast Tips
Examining the DELog*.txt files in:
C:\Program Files\MicroStrategy\Narrowcast Server\Delivery Engine
is slightly easier than the entire message log. Copy the service id of the Service name you are investigating and search the relevant file in the above folder to trace the events associated with the service.
If there are pre/post SQL scripts, open up the Subscription Set editor, then from the menu
Edit -> Subscription Set properties.
pre/post SQL scripts on the subscription sets can also be used to determine for example if an ETL Completion flag is set so that NC Services can be run.
C:\Program Files\MicroStrategy\Narrowcast Server\Delivery Engine
is slightly easier than the entire message log. Copy the service id of the Service name you are investigating and search the relevant file in the above folder to trace the events associated with the service.
If there are pre/post SQL scripts, open up the Subscription Set editor, then from the menu
Edit -> Subscription Set properties.
pre/post SQL scripts on the subscription sets can also be used to determine for example if an ETL Completion flag is set so that NC Services can be run.
Subscribe to:
Posts (Atom)