How to Restore database using Cold Backup in Noarchivelog Mode Without Online Redo Logs

Restoring a Cold Backup in Noarchivelog Mode Without Online Redo Logs
====================================================================

1. Shut down the instance.
2. Copy the control files and data files back from the backup.
3. Start up the database in mount mode.
4. Open the database with the OPEN RESETLOGS clause.



Step 1. Shut Down the Instance
--------------------------------------------------------------------------------------


$ sqlplus / as sysdba
SQL> shutdown abort;


Step 2. Copy the Files Back(datafiles and controlfiles) from the Backup without redo logfiles backup
----------------------------------------------------------------------------------------------------

$ cp <backup directory>/*.* < database file directory>


Step 3. Start Up the Database in Mount Mode
----------------------------------------------------------------------------------------------------

$ sqlplus / as sysdba
SQL> startup mount



Step 4. Open the Database with the OPEN RESETLOGS Clause
--------------------------------------------------------------------------------------------------

SQL> alter database open resetlogs;

you will get error


ORA-01139: RESETLOGS option only valid after an incomplete database recovery


{SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery}


SQL>


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> recover database until cancel;
Media recovery complete.
SQL>

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL>


Now, attempt to open your database with the OPEN RESETLOGS clause:
SQL> alter database open resetlogs;



==========================================
>>> Error you will get:-
==========================================


SQL> startup
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size                  2259840 bytes
Variable Size             654312576 bytes
Database Buffers          381681664 bytes
Redo Buffers                5632000 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 10280
Session ID: 1 Serial number: 5

***Need shut abort and go step 3

In alert log you will get below error:
---------------------------------------------
ALTER DATABASE OPEN
Errors in file /u01/app/oracle/diag/rdbms/bsnl/bsnl/trace/bsnl_lgwr_10259.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/bsnl/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/bsnl/bsnl/trace/bsnl_lgwr_10259.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/bsnl/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/bsnl/bsnl/trace/bsnl_ora_10280.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/bsnl/redo01.log'
System state dump requested by (instance=1, osid=10280), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/bsnl/bsnl/trace/bsnl_diag_10249_20160829041454.trc
USER (ospid: 10280): terminating the instance due to error 313
Dumping diagnostic data in directory=[cdmp_20160829041454], requested by (instance=1, osid=10280), summary=[abnormal instance termination].
Instance terminated by USER, pid = 10280

How to Restore database using Cold Backup in Noarchivelog Mode with Online Redo Logs

Restoring a Cold Backup in Noarchivelog Mode with Online Redo Logs

=======================================================================


1. Shut down the instance.
2. Copy the data files, online redo logs, temporary files, and control files back from the
   backup to the live database data file locations.
3. Start up your database


Step 1. Shut Down the Instance
-------------------------------------------------------------------------------------------

[oracle@mylab bsnl]$ sqlplus "/as sysdba"
SQL> shutdown abort;


Step 2. Copy the Files Back from the Backup
---------------------------------------------------------------------------------------------

[oracle@mylab bsnl]$  cp /u02/backup/bsnl_30082016/*  /u01/app/oracle/oradata/bsnl/

** must be copy files as previous location when take backup so keep location information

Step 3. Start Up the Database
----------------------------------------------------------------------------------

Connect to your database as SYS (or a user that has SYSDBA privileges), and start up your database:
[oracle@mylab bsnl]$ sqlplus "/as sysdba"

SQL> startup;



**It’s as if you set your database back to the point in time when you made the backup.

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;

ORA-00205: error in identifying control file, check alert log for more info / Removing a Control File

ORA-00205: error in identifying control file, check alert log for more info

If you recieve error then check controlfile physcial location  then remove bad controlfile location from database(control_files parameter ):



1. Identify which control file has experienced media failure by inspecting the alert.log for
information

ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/bsnl/control_files/control03.ctl'
ORA-27037: unable to obtain file status


2. Remove the unavailable control file name from the CONTROL_FILES parameter. If you’re
using an init.ora file, modify the file directly with an OS editor (such as vi). If you’re
using an spfile, modify the CONTROL_FILES parameter with the ALTER SYSTEM statement.
In this spfile example the control02.ctl control file is removed from the CONTROL_FILES
parameter:


alter system set control_files= '/u01/app/oracle/oradata/bsnl/control01.ctl','/u01/app/oracle/fast_recovery_area/bsnl/control02.ctl' scope=spfile;

3. Stop and start your database:
SQL> shutdown immediate;
SQL> startup;

How to Move/Add/Multiplex Control file

Adding controlfile in database means copy an existing controlfile and making database aware of copy by modifying control_files parameter .

Steps to Add controlfile :
==================================

Step 1:-

Alter the initialization CONTROL_FILES parameter to include new location and name of controlfile.

Step 2:-

Shutdown database

Step 3:-

Use OS command to copy  existing good control file  to new location and rename .

Step 4:-

start database



If database use pfile
============================
shutdown database,
copy one of controlfile to new locations,
manually edit pfile
startup database.




Example:
[oracle@mylab ~]$ sqlplus "/as sysdba"




**************************************************************
check current controlfiles  location information
***************************************************************

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/bsnl/control01.ctl, /u01/app/oracle/fast_recovery_area/bsnl/control02.ctl
SQL>




*****************************************************************************
 Make change CONTROL_FILES parameter  with new location using below command
*****************************************************************************

SQL > alter system set control_files= '/u01/app/oracle/oradata/bsnl/control01.ctl', '/u01/app/oracle/fast_recovery_area/bsnl/control02.ctl', '/u01/app/oracle/oradata/bsnl/control_files/control03.ctl' scope=spfile;




*******************************************
 Shut down database
*****************************************
SQL> shut immediate






**********************************************
Use OS command copy controlfile to new location
**********************************************

[oracle@mylab bsnl]$
[oracle@mylab bsnl]$ cp control01.ctl control_files/control03.ctl
[oracle@mylab bsnl]$




********************************************
Start database
********************************************
[oracle@mylab ~]$ sqlplus "/as sysdba"

SQL> startup

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/bsnl/control01.ctl, /u01/app/oracle/fast_recovery_area/bsnl/control02.ctl, /u01/app/oracle/oradata/bsnl/control_files/control03.ctl

How to Migrate SQL Profiles from One database to Another database

===============================How to Migrate SQL Profiles from One database to Another database=========================================


You can migrate SQL profile using export and import from one database to another database just like stored outline. Prior to oracle 10g you can migrate SQL profiles with the dbms_sqltune.import_sql_profile procedure where as in oracle 10g release 2 and beyond using dbms_sqltune package. In both case you have to create a staging table on the source database and populate that staging table with the relevant data. Below is the step to migrate SQL profile in 10g release 2.

Step1. Create the staging table to store SQL Profiles in source database
SQL> sys/oracle@orcl as sysdba
SQL> BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF
(table_name => ‘SQL_PROFILES’,schema_name=>TEST');
     END;
/
PL/SQL procedure successfully completed.


Step2. Now Copy SQL profiles from SYS to the Staging table
SQL> BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF
(profile_name => 'PROF_4m0c9guyqybbd_2738228381',
staging_table_name => 'SQL_PROFILES',
staging_schema_owner=>'TEST');
END;
/
PL/SQL procedure successfully completed.
Note: As you need to copy all SQL profiles on my database ‘%’ value for profile_category was the best option.


Step3. Export the staging table at source
SQL> select count(*) from TEST.sql_profiles;
COUNT(*)
-------------
3
expdp system/***** dumpfile=expdp_sql_profiles.dmp TABLES=TEST.SQL_PROFILES DIRECTORY=DPUMP



Step4. Restore the database with the backup taken before all SQL profiles were generated and import the staging table at target database.
impdp system/***** dumpfile=expdp_sql_profiles.dmp TABLES=TEST.SQL_PROFILES DIRECTORY=DPUMP TABLE_EXISTS_ACTION=REPLACE
Note: Do not forget to create staging table on destination database. Use replace = TRUE if you need to have same SQL_Profiles on both the database.


Step5. Finally Unpack the SQL profiles from the staging table on destination database.
SQL> BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF
(staging_table_name => ‘SQL_PROFILES’,
staging_schema_owner=>’TEST2’, replace=>FALSE);
END;
/
PL/SQL procedure successfully completed.