How to take cold backup of database in noarchivelog mode

Cold Backup of a database in noarchivelog mode
=======================================================

1. Determine where to copy the backup files and how much space is required.
2. Identify the locations and names of the database files to copy.
3. Shut down the database with the IMMEDIATE, TRANSACTIONAL, or NORMAL clause.
4. Copy the files (identified in step 2) to the backup location (determined in step 1).
5. Restart your database

Login as sys user
-----------------------------

SQL> select name,open_mode,log_mode from v$database;

NAME      OPEN_MODE            LOG_MODE
--------- -------------------- ------------
BSNL      READ WRITE           NOARCHIVELOG

SQL>

Step 1. Determine Where to Copy the Backup Files and How Much Space is Required
---------------------------------------------------------------------------------------------------------------


select sum(sum_bytes)/1024/1024 m_bytes
from(
select sum(bytes) sum_bytes from v$datafile
union
select sum(bytes) sum_bytes from v$tempfile
union
select (sum(bytes) * members) sum_bytes from v$log
group by members);



Step 2. Identify the Locations and Names of the Database Files to Copy
--------------------------------------------------------------------------------------------------------

select name from v$datafile
union
select name from v$controlfile
union
select name from v$tempfile
union
select member from v$logfile;

Step 3.Take backup of controlfile and pfile from spfile if database run using spfile or copy pfile  to backup location
-----------------------------------------------------------------------------------------------------------------

SQL> create pfile='/u01/app/oracle/u02/backup/init_bkp_30082016.ora' from spfile;

File created.

SQL> alter database backup controlfile to trace as '/u01/app/oracle/u02/backup/control_bkp_30082016';

Database altered.




** The online redo logs are required to open the database in a normal manner.
If you back up all files (including the online redo logs), then to get your database back to the state it was in at the
time of the backup, you restore all files (including the online redo logs) and start up your database

Step 4. Shut Down the Database
-----------------------------------------------------------------------------------------


[oracle@mylab bsnl]$ sqlplus "/as sysdba"

SQL> shutdown immediate


Step 5. Create Backup Copies of the Files
----------------------------------------------------------------------------------

using OS copy command (cp) to copy database files

cp /u01/app/oracle/oradata/bsnl/* /u02/backup/bsnl_30082016/



Step 6. Start Your Database
----------------------------------------------------------------------------------------
[oracle@mylab bsnl]$ sqlplus "/as sysdba"

SQL> startup
SQL>

backup sql script:
---------------------------

create pfile='/u01/app/oracle/u02/backup/init_bkp_30082016.ora' from spfile;

alter database backup controlfile to trace as '/u01/app/oracle/u02/backup/control_bkp_30082016';

select 'cp ' || name || ' ' || '/u01/app/oracle/u02/backup/' from v$datafile;

select 'cp ' || name || ' ' || '/u01/app/oracle/u02/backup/' from v$tempfile;

select 'cp ' || member || ' ' || '/u01/app/oracle/u02/backup/' from v$logfile;

select 'cp ' || name || ' ' || '/u01/app/oracle/u02/backup/' from v$controlfile;