Undo Tablespace recovery

Undo tablespaceFrom my understanding i amjust elobrating howards outstanding contribution

Day:FridayBackup the UNDO tablespace on Friday
2:PMSTMT:update table After 2: PM
Action 1: Generates UNDO for the OLD VALUES and inserts into UNDO TABLESPACE…Action 2: Generates REDO for the update stmt and stored in REDOLOG files which will be archived later…Need for Recovery…This will have committed and uncommitted data where committed data will have SCN to it where as uncommitted redo will not have SCN…

Changes happening for next days…Undo Tablespace corrupted…
Scenario starts here
We restore the Friday Undo Backup here…
Recover the UNDO Tablespace to bring the DB upApplying Archive logs starts here which will have commited and uncommitted data hereSince the log has the SCN for every commit

1. it starts inserting all the data Here is the thing which may be crucial as far as I have understoodIt has both commited and uncommitted data…

The Undo will be generated for those statements and corresponding redo will be generated but the internal transaction table will be updated for every committed transaction with the help of SCN from the archived logs so here the inserted stmts which have the SCn will be commited which is roll forward and the internal transaction tables gets updated with the respective SCN…

Now all the statement were executed and UNDO will have some uncommitted transactions after recovery in fact during the recovery. Those are the one which will not have an entry n the internal transaction table.

Now SMON starts rollback with the help of internal transaction table.That statement which does not have commit entry in the Internal Transactions table will all be rolled back.


So the Internal transaction tables uncommitted entry and the undo generated during the recovery will help in rolling back.This is what I have understood …

Hope this helps

Undo tablespace Concepts

Hi ,

I would like to share my experience about the UNDO tablespace problem i faced while running a customised masking scripts

I was involved in resynching the UAT with Production.

The Production was restored on UAT database after recreating the control file.We have a script file which masks the sensitive data on the UAT before bringing to the office.

Its has serious of update statements which updates the column values to zero but unfortunately we had only one commit statement which will be fired at the end of the script

Since the number of records were very high i started hitting the undo tablespace reaching more 6gigs and almost we ran out of space in the system.

se we changed the script to have commit after every DML which resulted in reusing the UNDOTBS

Now i have clearly understood what undo serves for

Regards
Elamaran

Redo Log Thread Number

I was just wondering for few days why the thread number is always constant in the archive log but i am working with the single instance database so i was unaware of that ..

Now in RAC database each instance has its own thread so if we two have nodes we will have 2 threads numbers representing each instances..

How to increase the Redolog File Size

Hi

I encountered some problems with the huge number archiving files being generated (ie seconds)during peak hours.
So i decided to increase the size of the redologs

The redolog size cannot be increased ,In order to do that We have to add new log groups with bigger size and drop the older smaller ones...


This is how i did it...




Procedure to Increase the REDOLOG size in ORACLE

The Redolog file size cannot be increased online it can be done by creating new Redolog groups
A bigger size and then drop the exiting old smaller log files.

Login to Database as SYSDBA

1.sys/pwd@TNS as sysdba

Note: We got 3 redolog groups in the DB with 3 members each

2.Create 3 new redolog groups with 3 members each for a group

SQL> alter database add logfile group 4
(E:\oradata\SID\redo41.log, E:\oradata\SID\redo42.log, D:\oradata\SID\redo43.log) size 100M;

Here we have multiplexed the group member for data protection, which is already in place

SQL> alter database add logfile group 5
(E:\oradata\SID\redo51.log, E:\oradata\SID\redo52.log, D:\oradata\SID\redo53.log) size 100M;

SQL> alter database add logfile group 6
(E:\oradata\SID\redo61.log, E:\oradata\SID\redo62.log, D:\oradata\SID\redo63.log) size 100M;


Now the bigger redolog files are created.

3.Query from the views and make sure that status of any of the group is not ‘current’

SQL>select group#, status from v$log;


4.If any group found to be ‘current’ do the following

SQL>alter system switch logfile;
Repeat this step and make sure nothing is current.

Once all the group become inactive we can drop the existing older smaller files.




5.Drop the log files

SQL>alter database drop logfile group 1;
SQL>alter database drop logfile group 2;
SQL>alter database drop logfile group 3;

6.Query the status of the log files.

7.Backup the DB.

8.Delete the files physically from the DB


That’s it..


Regards
Elamaran

Prevent password change

Normally the Passwords are very sensitive and should be protected and prevented from changing by the users.It is trivial but this is what i found from the net and how i deployed in oracle


REM CONNECT AS SYSDBA
CREATE OR REPLACE FUNCTION verify_function_pwd_nochange (username VARCHAR2, password VARCHAR2, old_password VARCHAR2)
RETURN boolean
ISBEGIN
raise_application_error(-20900, 'SORRY, PASSWORD CANNOT BE CHANGED') ;
END ;
/
drop profile pass_profile cascade;

create profile pass_profile limitfailed_login_attempts unlimited;

alter profile pass_profile limitpassword_verify_function verify_function_pwd_nochange;

alter user system profile pass_profile;

Once the above command is executed the SYSTEM user password cannot be changed..

Drawback

but if you have a user who has dba privilege then he can drop the profile


Comments are welcome
Maran

Database Cloning

1.1 Purpose
This Oracle clone procedure can be use to quickly migrate a system from one Windows server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy an Oracle database.

1.2 Scope
The document covers Database related information

1.3 Audience
database administrator prepares this document for so and so




2 CLONING PROCEDURE

PROD system, go into SQL*plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”.

This will create a create controlfile text in the udump directory

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database Altered

SQL>show parameter user_dump_test

.TRC file will be under the directory that is displayed by the above command

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MUAT" NORESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'D:\ORADATA\MUAT\REDO01.LOG’ SIZE 100M,
GROUP 2 'D:\ORADATA\MUAT\REDO02.LOG’ SIZE 100M,
GROUP 3 'D:\ORADATA\MUAT\REDO03.LOG’ SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'D:\ORADATA\MUAT\SYSTEM01.DBF',
'D:\ORADATA\MUAT\SYSTEM02.DBF',
'D:\ORADATA\MUAT\DRSYS01.DBF',
'D:\ORADATA\MUAT\INDX01.DBF',
'D:\ORADATA\MUAT\TOOLS01.DBF',
'D:\ORADATA\MUAT\USERS01.DBF',
'D:\ORADATA\MUAT\USER_DATA.DBF',
'D:\ORADATA\MUAT\USER_INDEX.DBF',
'D:\ORADATA\MUAT\ROLLBACK_DATA.ORA',
'D:\ORADATA\MUAT\USER_INDEX2.DBF',
'D:\ORADATA\MUAT\USER_DATA2.DBF',
'D:\ORADATA\MUAT\RMAN01.DBF',
'D:\ORADATA\MUAT\USER_DATA3.DBF',
'D:\ORADATA\MUAT\USER_INDEX3.DBF',
'D:\ORADATA\MUAT\USER_DATA4.DBF',
'D:\ORADATA\MUAT\USER_INDEX4.DBF',
'D:\ORADATA\MUAT\USER_INDEX5.DBF',
'D:\ORADATA\MUAT\USER_DATA5.DBF',
'D:\ORADATA\MUAT\USERDATA\USER_DATA.DBF',
'D:\ORADATA\MUAT\USER_INDEX6.DBF'
CHARACTER SET UTF8
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
# No tempfile entries found to add.


This will be the script used to recreate the control file in the Target Machine .



Shutdown the OLD Database.

SQL>SHUTDOWN IMMEDIATE


Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.
Files to be copied
Datafiles
Controlfiles (For the safer side)
Redolog File
Init.ora File
PWD file




Copy and Edit the Control file – Using the output syntax from 2.2, modify the controlfile creation script by changing the following:Old: CREATE CONTROLFILE REUSE DATABASE "MUAT" NORESETLOGS
New: CREATE CONTROLFILE REUSE DATABASE "MUATDEV" NORESETLOGS

Note: The above step is needed only if we are going to change the Instance Name.



Remove the “recover database” and “alter database open” syntax
# Recovery is required if any of the datafiles are restored# backups, or if the last shutdown was not normal or immediate.RECOVER DATABASE# Database can now be opened normally.ALTER DATABASE OPEN;


Copy the files to any directory and edit the control file where the file has been moved physically. This is needed while starting the DB or else you will get ora- error stating missing file.
Save as Create_Control.sql.Old:
DATAFILE'D:\oradata\muat\system01.dbf','D:\oradata\muat\mydatabase.dbf'


New:
DATAFILE'G: \oradata\muat\system01.dbf','G: \oradata\muat\mydatabase.dbf’


Start the database in nomount state and recreate the control file created from the earlier script in 2.2.This is will create the control file and to opened.
SQL>Startup mount
SQL>@create_controlfile.sql

SQL>Alter database open

Put the Database in archivelog mode


This will help us to recover the Database if a file has been corrupted. Since the Database runs in ARCHIVELOG MODE we can recover in any kind of situation.




Hope this helps a bit