Repository navigation
43. Incomplete Recovery, Backup Script
When the recovery of a database remains incomple not complete this is called incomplete recovery.
Like for example we have created a table some hours back and it got deleted by mistake. We can then restore that deleted table by recovering our database to the state as it was before the time of deletion. There are 2 ways of doing it.
We need to know when the table got deleted and we can use that time to restore our database as it was 1 second before.
We can also use SCN number to do the incomplete recovery. When we commit the background process CKPT takes the SCN number of the time of commit and put it in the header of datafile. We can use that for the recovery.
For this recovery to work there needs to be an active backup of the database. The restoration will use the archive logs to do the recovery.
As for why this is called incomplete restoration. In case of complete restoration or recovery we recover the whole thing, the things happened between the creation of the database uoto current time everything is recovered. But this will complete our objective of recovering an important table if its deleted by mistakenly. This is possible with the incomplete recovery which recovers the database to the state it was before the table got deleted. So this is called incomplete recovery as the whole thing (the deletion of the table) doesn't get recovered.
We can do this incomplete recovery by 2 ways. First is by using the timestamp before deletion.
Now don't forget to commit the table.
We have created an user and a table with it. Now lets see what is the current timestamp so that we can use it for our incomplete recovery.
Now lets delete the table.
Now we have to take the database to mount state and go to the rman tool.
run
{
set until time "to_date('22-06-26 22:40:55','dd-mm-yy hh24:mi:ss')";
restore database;
recover database;
alter database open resetlogs;
}
This line set until time tells the process to do the recovery until this timestamp.
Also we need to reset the logs after recovery. This reset logs is also required after incomplete recovery just like after recovering comtrol file.
See the table is back again.
2nd is by using the SCN number of the last commit before the deletion of the deletion of the table.
We will use this SCN number to recover. This time lets delete the whole user itself.
run
{
set until scn 2401342;
restore database;
recover database;
alter database open resetlogs;
}
This line set until scn tells the process to do the recovery until the time this SCN number was in the header of the datafile.
See the user and its tables are back.
Now in industry they don't run backup by typing code. There are scripts for that. And there is a scheduler crontab in linux. This scheduler is used to schedule backup scripts to run a specific time in a regular basis. This scheduler is not oracle's application but its Linux's tool. And for this scheduler to work the backup script will have to be a shell script.
export ORACLE_SID=prod
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LOG_DIR=/u01/backup/
rman target / log=$LOG_DIR/full_backup_$(date +%d%m%Y).log <<EOF
run
{
delete noprompt expired backup;
backup database plus archivelog;
}
exit;
EOF
~
This backup script will store a log. Like how to we understand what backups were taken and what happened. Through the logs.
export ORACLE_SID=prod
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LOG_DIR=/u01/backup/
This 4 lines to set the paths the script will need in linux environment variables. Oracle sid and home is already known. Path variable is for getting the sql and rman prompt. Log directory is where the log will be stored.
rman target / log=$LOG_DIR/full_backup_$(date +%d%m%Y).log <<EOF
This line is so that what happens in the rman tool will be stored in the log. Everytime the script runs it will create a new log file with current date and store the logs in it.
run
{
delete noprompt expired backup;
This line is for like when we try to delete expired backup it always asks our permission right? To enyer yes or no. This line forces to not ask that and to delete the expired backup without asking anything.
backup database plus archivelog;
}
exit;
EOF
Deleted previous backups.
Run the script:
See the backups are back. Lets check the log.
