I am a relatively newbie here. Just want to confirm with you guys about this situation.
Oracle allow users to do manual hot backup using 'alter tablespace begin backup;'. If after a user wants to backup all the tablespaces and put the statements into one scripts while the database is still live, what is the implications afterwards?
From my understanding, If we use this way while Oracle read the script, it will read and run one statement at a time. That means from the first statement to the last one the SCN will definitely changes a lot of times and in the end the backup of those datafiles are not consistent to each other. When we do recovery, should the db is not in archivelog mode, we will not be able to recover from those backups.
On other hand, I read something about 'alter tablespace offline normal;' which shed a light towards the solution but it turns out to be a failure. This is not applicable in a live system since RBS and SYSTEM cannot be taken offline.
But I notice it is really interesting to see that Oracle claimed the 'offline' way to be a cold backup. Is that true?
A "hot" backup or "online" backup is performed is with the database open, and for this to work you MUST be running in archivelog mode
alter tablespace users begin backup;
host cp /u01/oradata/prod/users01.dbf /backup
host cp /u02/oradata/prod/users02.dbf /backup
alter tablespace users end backup;
alter system archive log current;
In addition you must also backup your controlfile. You'll probably also want to backup init.ora/spfile, archivelogs, and the password file if you use one.
On the other hand, a "cold" or "offline" backup involves shutting down the database (normal, immediate, or transactional) and making a copy of all the datafiles/init.ora etc. An offline backup is not related to the command to take a tablespace offline.
If you choose to implement offline backups you can and should still run the database in archivelog mode. You can still perform complete recovery using files backed up via an offline backup by applying all archive/online redo.