User Managed Backups (Cold Backup)

User Managed Backup Overview:

A user-managed backup is made by performing a physical copy of data files using the OS commands. These copies are moved to a separate location using OS commands. The user maintains a record of the backups. For the recovery operation we have to move back (or to the new location of the database) the files and perform the recovery.  

The user-managed backups could be take at the following levels:

·         Data file level

·         Tablespace level

·         Database level 

Offline Backup (Cold Backup) steps :

A cold backup, also called an offline backup, is a database backup when the database is offline and thus not accessible for updating. This is the safest way to back up because it avoids the risk of copying data that may be in the process of being updated. However, a cold backup involves downtime because users cannot use the database while it is being backed up.

Below are steps for Cold Backup:

Step 1: Check the locations of  Datafiles, Redolog files, Temp files & controlfiles

SQL>select name from v$datafile;

SQL>select name from v$tempfile;
SQL>select name from v$controlfile;
SQL>select member from v$logfile;

Step2: Backup the control file and perform a trace of the control file.

SQL>alter database backup controlfile to 'u01/BACKUP/control_trace.ctl';

SQL>alter database backup controlfile to trace;

SQL>create pfile from spfile;

Init.ora and config.ora Located under $ORACLE_HOME/dbs directory (UNIX / LINUX)

On Windows %ORACLE_HOME/database directory

Step 3: Shut down the database.

SQL>shutdown

Copy all data files into the new directories on the new server Using with OS command. 

scp /u01/app/oradata/orcl/* host@192.168.1.1:/u01/oradata/backup (UNIX)
copy E:\app\oradata\orcl\* \\192.168.1.1\oradata\backup (Windows)
Copy Controlfile & Pfile backup files.
Once copied all the necessary files start the database.



Step 4: start the database


$sqlplus / as sysdba

SQL>startup


 

Starting and Stopping the Database

When an Oracle database is started, it goes through four different and distinct stages:

Shutdown: When the database and instance are shut down, they are at rest. There are no processes present, no memory allocated, nothing is going on. It is important to note that even though the database/instance may be shut down and closed, other Oracle processes (like the listener or OEM agents) maybe still running.

Nomount: When the database is in nomount mode, the instance has been started. Thus, processes have been started and memory allocated.

Mount: When the database is in mount mode (or mounted), the instance is started and the database has opened the control file. The control file is read, but is contents are not validated.

Open: When the database is opened, the control-file contents have been validated against the physical database. The datafiles are all confirmed to be present, and they are opened. Oracle will then analyze the datafiles to determine if the database is in a consistent state.If the database is not in a consistent state, some form of recovery will be required.Typically, the form of recovery required, crash or instance recovery, does not require any DBA involvement. If instance recovery is not possible, then media recovery is required.Media recovery requires the application of backups and recovery operations to bring the database current to the point of failure (if this is possible). The principal determining factors for media recovery is the presence of the needed datafiles and the availability in the online redo logs of the redo needed to bring those files current. If either of these conditions does not exist, then media recovery is required.

Database shutdowns occur in much the same way as startups, except in reverse. There are two different kinds of shutdowns, however: consistent and inconsistent.

Consistent shutdown: If your database shutdown is a consistent shutdown, then the database datafiles and the database control file will be synchronized upon shutdown. The dirty buffers in the database buffer cache will be flushed out to the database datafiles, making them consistent. A consistent shutdown is a nice, tidy shutdown.

Inconsistent shutdown: An inconsistent shutdown is another term for a mess. When our database is shut down in an inconsistent manner, it is in a indeterminate state and will require some form of recovery (typically instance recovery, which requires no DBA intervention) when it is restarted. Inconsistent shutdowns, however bad they might sound, often are the only way to shut down a database in a timely manner.

Starting and Stopping the Database

During backup and recovery operations, you will need to know how to start up and shut down your database correctly. To start up the database in any of the modes described in the previous section, you will use the startup command or the alter database command,as required. To stop the database, you will use the shutdown command. Typically, database startup operations are performed from SQL*Plus or Oracle Enterprise Manager.

The startup command This command is used to start the instance and/or database when the database is in a shutdown state only. The startup command can be used to completely open the database, as shown in this code snippet:

SQL> startup

The startup command also has options that you can use to indicate that you want Oracle to start the startup operation at a certain point. For example, you can indicate that you want the instance to be started only by using the startup nomount command:

SQL> startup nomount

Or perhaps you want to start the instance and mount the database. In this case, the command would be as follows:

SQL> startup mount

Sometimes you want to shut down the database and start it up in one command. You can use the startup force command to perform this action. Note that the startup force command will shut down your database in an inconsistent manner , and some operations (such as putting the database in ARCHIVELOG mode) will not complete successfully if the database was shut down in an inconsistent manner. Here is an example of the startup force command:

SQL> startup force

The shutdown command The shutdown command does what it says; it shuts down the database.

As with the startup command, it comes with a few options. First there is the plain-Jane shutdown command, which will shut down the database if absolutely nothing is going on and if absolutely no one is logged in. You can guess how often those conditions happen in reality! Until its conditions are met, the shutdown command will just sit there, waiting for its opportunity to shut down the database.

Here is an example of the shutdown command:

SQL> shutdown

If waiting is not your forte, then you may want to try the shutdown immediate command. The shutdown immediate command will prevent new logons, roll back any uncommitted transactions, and then bring the database down. It’s a consistent-shutdown, no-waiting approach to stopping the database and lots of DBAs like it. Here is an example:

SQL> shutdown immediate

The cousin of shutdown immediate is shutdown transactional (we are not sure if it’s a first cousin or second cousin; Oracle has not defined this within the body of the Oracle documentation yet). The main difference here is that the shutdown transactional command will wait for active transactions to complete (commit) before shutting down those sessions. As a result, the shutdown transactional command can take a while longer to complete its task, but on the positive side, users might be a little bit happier (if they are actually able to be happy anytime the database comes down). Here is an example of the shutdown transactional command:

SQL> shutdown transactional

The bad boy of database shutdowns is the shutdown abort command. If you want your database

to come down without debate, this is the way to do it. This is like pulling the power cord on your database; it is a crash of the database, shutting it down in an inconsistent manner.

Here is an example of the shutdown abort command:

SQL>shutdown abort

The alter database command The alter database command is used to move the instance/database from one state to another. For example, if the instance was started with the startup nomount command, you may want to mount the database. To do so, you would use the alter database mount command, as shown here:

SQL>alter database mount;

Understanding the Oracle Database as It Relates to Backup and Recovery. If the database is already mounted and you want to open it, then the alter database mount command would be appropriate, as shown in this example:

SQL>alter database open;

Performing Backup and Recovery Operations and Getting Database Status

Performing Backup and Recovery Operations and Getting Database Status:

So, what kind of operations would you do given the different open or closed combinations

of the database and instance? Here are some examples:

Operations while the instance is down and the database is not open:

ü  Copy the spfile to a pfile.

ü  Copy the pfile to a spfile.

ü  Perform manual cold backups.

Operations while the instance is open and the database is not open:

ü  Create a database.

ü  Create a database control file.

ü  Restore the database control file or spfile from RMAN.

Operations while the instance is mounted and the database is not open:

ü  Cold backup with RMAN

ü  Recovery of critical datafiles (SYSTEM, UNDO tablespaces).

ü  Offline recovery of entire database.

Operations while the instance is mounted and the database is open:

ü  Online datafile or tablespace recovery of noncritical tablespaces.

ü  Online backups of the database.

ORA-39171: Job is experiencing a resumable wait.

ERROR CODE:

ORA-39171: Job is experiencing a resumable wait.
ORA-01653: unable to extend table
schema_name.Tablename by 8192 in tablespace AXPERT.

Reason:
              While Importing the backup file If you get above error that means That particular default Tablespace datafile is reached max size.In Oracle Database 11g version every individual data file  max size is 31 GB.


Solution :
                    You  should be added new datafile to Which is default Tablespace.

Check the Tablespace  Available space with below script :

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name
and df.tablespace_name = 'AXPERT';

Tablespace                        Used MB    Free MB   Total MB  Pct. Free
------------------------------ ---------- ---------- ---------- ----------
AXPERT                             213829      10621     224450          5

Find the file name/location of your tablespace

SELECT file_name, tablespace_name FROM dba_data_files
where TABLESPACE_NAME = 'AXPERT';

FILE_NAME                                                                              TABLESPACE_NAME
-----------                                                                                      ---------------
F:\APP\ADMINISTRATOR\ORADATA\ORCL\AXPERT01.DBF    AXPERT

F:\APP\ADMINISTRATOR\ORADATA\ORCL\AXPERT02.DBF    AXPERT

F:\APP\ADMINISTRATOR\ORADATA\ORCL\AXPERT03.DBF    AXPERT

Increase the table space Size

ALTER DATABASE DATAFILE 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\AXPERT03.DBF' RESIZE 5000M;

If above all Datafiles have max size then,

ALTER TABLESPACE AXPERT ADD DATAFILE 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\AXPERT04.DBF' SIZE 1024M REUSE AUTOEXTEND ON MAXSIZE 10240M ;  (It will increase the size upto 10GB)

(OR) 

ALTER TABLESPACE AXPERT ADD DATAFILE 'F:\APP\ADMINISTRATOR\ORADATA\ORCL\AXPERT04.DBF' SIZE 1024M REUSE AUTOEXTEND ON; (It will increase upto 31GB)

Now start the fresh IMPDP job.

If you have comment/suggestion please post.

HOW TO CHANGE DATABASE ID

HOW TO CHANGE DATABASE ID (DBID)

    You can choose a DBID when you rename your Oracle database. This is probably a bad, unsupported, and useless idea. I assume this hidden feature can help you to mess up all your backups. So my advice would be: “don’t use it.” But as DBA we should aware of this.

I performed this test with Oracle 11.1.0.6 on Windows Server 2008. It consists in using dbms_backup_restore instead of nid to rename the database. You’ll find below the few steps require to get to it.

Step 1. Open the database in read-only mode

First, stop the instance with an immediate shutdown. If we were to use nid, we would mount the instance, but with dbms_backup_restore, we need to access the package. For this reason, we have to open the database in read-only mode. Here are the commands I ran:

sqlplus / as sysdba

shutdown immediate;

startup open read only;

Step 2. Get the old values; set the new ones…

Once we can access the database, we can check its NAME and DBID. The script below does these checks and prompts the user for the new NAME and DBID. To that result, it queries V$DATABASE:

Step 2.1

Selection 1

-----------------------------------------------------------------------------

var old_name varchar2(20)

var old_dbid number

var new_name varchar2(20)

var new_dbid number

exec select name, dbid -

       into :old_name,:old_dbid -

       from v$database

print old_name

accept new_name prompt "Enter the new Database Name:" 
-----------------------------------------------------------------------------------------

Enter the new Database Name:CORPORATE

Step 2.2

Execute below step:-

Selection 2
----------------------------------------------------------------------------------------

accept new_dbid prompt "Enter the new Database ID:"

------------------------------------------------------------------------------

I will ask New DBID:-

Enter the new Database ID:1255844286

Step 2.3

Selection 3
-------------------------------------------------------------------------------

exec :new_name:='&&new_name'

exec :new_dbid:=&&new_dbid
-------------------------------------------------------------------------------

Step 3. Make the changes

I won’t go into all the details of the package. Instead, here is PL/SQL block you can run to make the change:

Selection 4

------------------------------------------------------------------------------

set serveroutput on

exec dbms_output.put_line('Convert '||:old_name||  -

     '('||to_char(:old_dbid)||') to '||:new_name|| -

     '('||to_char(:new_dbid)||')')

declare

  v_chgdbid   binary_integer;

  v_chgdbname binary_integer;

  v_skipped   binary_integer;

begin

  dbms_backup_restore.nidbegin(:new_name,

       :old_name,:new_dbid,:old_dbid,0,0,10);

  dbms_backup_restore.nidprocesscf(

       v_chgdbid,v_chgdbname);

  dbms_output.put_line('ControlFile: ');

  dbms_output.put_line('  => Change Name:'

       ||to_char(v_chgdbname));

  dbms_output.put_line('  => Change DBID:'

       ||to_char(v_chgdbid));

  for i in (select file#,name from v$datafile)

     loop

     dbms_backup_restore.nidprocessdf(i.file#,0,

       v_skipped,v_chgdbid,v_chgdbname);

     dbms_output.put_line('DataFile: '||i.name);

     dbms_output.put_line('  => Skipped:'

       ||to_char(v_skipped));

     dbms_output.put_line('  => Change Name:'

       ||to_char(v_chgdbname));

     dbms_output.put_line('  => Change DBID:'

       ||to_char(v_chgdbid));

     end loop;

  for i in (select file#,name from v$tempfile)

     loop

     dbms_backup_restore.nidprocessdf(i.file#,1,

       v_skipped,v_chgdbid,v_chgdbname);

     dbms_output.put_line('DataFile: '||i.name);

     dbms_output.put_line('  => Skipped:'

       ||to_char(v_skipped));

     dbms_output.put_line('  => Change Name:'

       ||to_char(v_chgdbname));

     dbms_output.put_line('  => Change DBID:'

       ||to_char(v_chgdbid));

     end loop;

  dbms_backup_restore.nidend;

end;

/

-----------------------------------------------------------------------------

It Will give output as below,

ControlFile:

=> Change Name:1

=> Change DBID:1

DataFile: E:\app\oracle\oradata\BLACK\system01.dbf

=> Skipped:0

=> Change Name:1

=> Change DBID:1

DataFile: E:\app\oracle\oradata\BLACK\sysaux01.dbf

=> Skipped:0

=> Change Name:1

=> Change DBID:1

DataFile: E:\app\oracle\oradata\BLACK\undotbs01.dbf

=> Skipped:0

=> Change Name:1

=> Change DBID:1

DataFile: E:\app\oracle\oradata\BLACK\users01.dbf

=> Skipped:0

=> Change Name:1

=> Change DBID:1

DataFile: E:\app\oracle\oradata\BLACK\streams_tbs.dbf

=> Skipped:0

=> Change Name:1

=> Change DBID:1

DataFile: E:\app\oracle\oradata\BLACK\temp01.dbf

=> Skipped:0

=> Change Name:1

=> Change DBID:1

Step 4. Change db_name and open the database

Before you can open the database, you have to change the db_name parameter in the spfile. Once you’ve done so, you should be able to open it with resetlogs. That’s the script I ran to get to that result:

shutdown immediate;

startup mount;

alter database open resetlogs;

startup force;

create spfile from pfile=’e:\initcorporate.ora’

startup force; 

Note: Dear Friends above method  try to execute  on Test Environment.