Monday 29 April 2013

ORA-19602 cannot backup or copy active file in NOARCHIVELOG mode



It's a common mistake done by the oracle beginner to take the backup of a database in noarchivelog mode and they simply got the following error:
ORA-19602 cannot backup or copy active file in NOARCHIVELOG mode
 


You attempted to use RMAN to backup your database and received the following error message:
RMAN-03009: failure of backup command on ORA_DISK_1 channel
ORA-196602: cannot backup or copy active file in NOARCHIVELOG mode
This message indicates that before you can create an RMAN online backup. you need to place your database
into archivelog mode.
Enabling Archivelog Mode :
SQL> connect sys/<password> as sysdba
SQL> shutdown immediate;
SQL> startup mount:
SQL> alter database archivelog;
SQL> alter database open;
SQL> select log_mode from v$database;
LOG_MODE
--------------------------------------------------
ARCHIVELOG
SQL> archive log list;
10g introduce new feature called FRA (flashback recovery area) so all archivelog files created in FRA.
If your not using FRA then all archivelog files created in $ORACLE_HOME/dbs.
Disabling Archivelog Mode :
SQL> connect sys/<password> as sysdba
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database noarchivelog;
SQL> alter database open;

Friday 26 April 2013

Oracle Database Manual Backup and Recovery.

Here is the most important task for a Database learner that how to Backup and Recovery .Hope you find your task.


COLD BACKUP AND RECOVERY SCENARIOS.

Cold backup of Database in Noarchivelog Mode.

1) analyze the space requirements.


SQL> select sum(sum_bytes)/1024/11024 m_bytes
2 from (
3 select sum(bytes) sum_bytes from v$datafile
4 union
5 select sum(bytes) sum_bytes from v$tempfile
6 union
7 select (sum(bytes) * members) sum_bytes from v$log
8 group by members);

2) Determine location and names of files to be backed up.
SQL> select name from v$datafile
union
select name from v$controlfile
union
select name from v$tempfile
union
select member from v$logfile;



3) shutdown database

SQL> shutdown immediate;

4) Do OS level backup

Note: I have also backed up the Online redo Log files.


cp -ivr /u01/app/oracle/oradata/orcl/* /u02/app/oracle/backup/


SQL> startup mount

Do binary backup of controlfile.

SQL> alter database backup controlfile to '/u02/app/oracle
/controlbackup/control.bkp';

Database altered.

Or trace file backup

SQL> alter database backup controlfile to trace as '/u02/app
/oracle/controlback/controltrace';

SQL> alter database open;



Restoring from cold backup in Noarchivelog.

a) when Online redo logs backup is there.


Shutdown your database if its open.

Remove the old files

restore files from backup

$ cp -ivr /u02/app/oracle/backup/* /u01/app/oracle/oradata/orcl/


startup up your database

SQL> startup


b) when Online redo logs backup is not there.


Shutdown your database if its open.


restore files from backup

$ cp -ivr /u02/app/oracle/backup/* /u01/app/oracle/oradata/orcl/


startup up your database

SQL> startup mount

SQL> recover database using backup controlfile until cancel;

when prompted

cancel

SQL> alter database open resetlogs;

This command will clear and recreate the online redo log files
and also will be a new incarnation of the database.

That means old archive logs if any cannot
be used for the purpose of recovery.

So its importatnt that you make a FRESH BACKUP of your database.


HOT BACKUP AND RECOVERY SCENARIOS.

BACKUP

1) Make sure that your database is in archivelog mode.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled


2) analyze space requirements.

SQL> select sum(sum_bytes)/1024/11024 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);


3) Determine the files that need backup;

SQL> select name from v$datafile
union
select name from v$controlfile
union
select name from v$tempfile;


4) check max sequence from online redo logfiles.
SQL> select max(sequence#)
from v$log;

MAX(SEQUENCE#)
--------------
7

5) Put the database or tablespace in begin backup mode.

For database

SQL> alter database begin backup;

For Tablespace

SQL> alter tablespace users begin backup;


SQL> select file#,status from v$backup;

FILE# STATUS
---------- ------------------
1 ACTIVE
2 ACTIVE
3 ACTIVE
4 ACTIVE
5 ACTIVE


6) Copy all the datafiles with OS utility.

7) Take the database or tablespace out of backup mode.

For database

SQL> alter database end backup;

For tablespace

SQL> alter tablespace users end backup;


SQL> select file#,status from v$backup;

FILE# STATUS
---------- ------------------
1 NOT ACTIVE
2 NOT ACTIVE
3 NOT ACTIVE
4 NOT ACTIVE
5 NOT ACTIVE



8) archive current logfile and check latest max sequence
number.

SQL> alter system archive log current;

System altered.

SQL> select max(sequence#) from v$log;

MAX(SEQUENCE#)
--------------
19


9) backup the controlfile

Binary backup

SQL> alter database backup controlfile to '/u01/app/oracle
/backup/control.bk';

Database altered.

Trace backup

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

Database altered.

10) backup all the archived logs generated during backup7
identifying them by log sequence number.





Recovery Scenarios when database is in archivelog with hot backup.


Case 1: Lost all the controlfiles.

shutdown the database if its open.

SQL> shutdown abort

Copy the controlfiles from backup.


SQL>startup mount

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 808116 generated at 01/23/2012 23:55:11
needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
/2012_01_24/o1_mf_1_12_%u_.arc
ORA-00280: change 808116 for thread 1 is in sequence #12





Open another terminal and identify the online redo log file
for the sequence number.

SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-------------------------------------------------------------
IS_
---
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO

2 ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------
IS_
---

1 ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO


SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 10 52428800 512 1 YES
INACTIVE 807834 23-JAN-12 807837 23-JAN-12

3 1 12 52428800 512 1 NO
CURRENT 808116 23-JAN-12 2.8147E+14


2 1 11 52428800 512 1 YES
ACTIVE 807837 23-JAN-12 808116 23-JAN-12


SQL>


apply the relevant path.

Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo03.log
Log applied.
Media recovery complete.

In some cases querying v$log is not helpful as it does
not show the correct sequence number, In that case apply
the online logfiles one by one until media recovery is complete.

SQL> alter database open resetlogs;

Database altered.



CASE 2: Lost System Datafile

SQL> shutdown abort;
ORACLE instance shut down.


Copy the system datafile from backup.


SQL> startup mount

SQL> recover tablespace system;
ORA-00279: change 807565 generated at 01/24/2012 01:57:07
needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
/2012_01_24/o1_mf_1_11_7kvk24yl_.arc
ORA-00280: change 807565 for thread 1 is in sequence #11


Specify log: {=suggested | filename | AUTO | CANCEL}

auto

Log applied.
Media recovery complete.

SQL> alter database open;

Database altered.

CASE 3: Lost Non-System Datafiles.

Example: Users tablespace lost
due to media failure.


Take the tablespace offline.

SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01191: file 4 is already offline - cannot do a normal offline
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'


SQL> alter tablespace users offline immediate;

Tablespace altered.

Copy apprpriate datafiles from backup for users tablespace.

SQL> select file_name from dba_data_files where
tablespace_name='USERS';

FILE_NAME
---------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf


SQL> recover tablespace users;

ORA-00279: change 807565 generated at 01/24/2012
01:57:07 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
/2012_01_24/o1_mf_1_11_7kvk24yl_.arc
ORA-00280: change 807565 for thread 1 is in sequence #11


Specify log: {=suggested | filename | AUTO | CANCEL}

auto

Log applied.
Media recovery complete.

SQL> alter tablespace users online;

Tablespace altered.

SQL> select file_name,status from dba_data_files
where tablespace_name='USERS';

FILE_NAME
------------------------------------------------------
STATUS
---------
/u01/app/oracle/oradata/orcl/users01.dbf
AVAILABLE

CASE 4: A added data file was lost after hot backup.

Example: Lost test tablespace


SQL> alter tablespace test offline;

Tablespace altered.

SQL> alter database create datafile '/u01/app/oracle
/oradata/orcl/test01.dbf';

Database altered.

SQL> alter database create datafile '/u01/app/oracle
/oradata/orcl/test02.dbf';

Database altered.

SQL> recover tablespace test;
Media recovery complete.
SQL> alter tablespace test online;

Tablespace altered.
SQL> select file_name,status from dba_data_files where
tablespace_name='TEST';

FILE_NAME
--------------------------------------------------------
STATUS
---------
/u01/app/oracle/oradata/orcl/test01.dbf
AVAILABLE

/u01/app/oracle/oradata/orcl/test02.dbf
AVAILABLE




CASE 5: Datafile lost and needs to be restored to
different location.


Example: lost my users tablespace.

SQL> alter tablespace users offline;

Tablespace altered.

SQL> select file_name from dba_data_files where
tablespace_name='USERS';

FILE_NAME
-------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf


Copy appropriate data files from backup to new location.

Rename datafiles.

1 alter database rename file '/u01/app/oracle/oradata
/orcl/users01.dbf'
2 to
3* '/u01/app/oracle/oradata/users01.dbf'


SQL> recover tablespace users;
ORA-00279: change 807565 generated at 01/24/2012 01:57:07
needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
/2012_01_24/o1_mf_1_11_7kvk24yl_.arc
ORA-00280: change 807565 for thread 1 is in sequence #11


Specify log: {=suggested | filename | AUTO | CANCEL}

auto


Log applied.
Media recovery complete.

SQL> alter tablespace users online;

Tablespace altered.


SQL> select file_name,status from dba_data_files where
tablespace_name = 'USERS';

FILE_NAME
------------------------------------------------------------
STATUS
---------
/u01/app/oracle/oradata/users01.dbf
AVAILABLE



CASE 6: Lost everything including datafiles and controlfiles
except for online redo logs.


Note: There was no backup of online redo log files.

Restore all the files from backup except for online redo log files.

SQL> startup mount
ORACLE instance started.

Total System Global Area 619360256 bytes
Fixed Size 1338280 bytes
Variable Size 398459992 bytes
Database Buffers 213909504 bytes
Redo Buffers 5652480 bytes
Database mounted.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 807805 generated at 01/24/2012 14:50:13
needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
/2012_01_24/o1_mf_1_10_7kwyqqnb_.arc
ORA-00280: change 807805 for thread 1 is in sequence #10

identify the correct online redo log file.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------------- ------------- --------- ------------ ---------
1 1 10 52428800 512 1 YES
ACTIVE 807803 24-JAN-12 808486 24-JAN-12

3 1 9 52428800 512 1 YES
INACTIVE 807800 24-JAN-12 807803 24-JAN-12

2 1 11 52428800 512 1 NO
CURRENT 808486 24-JAN-12 2.8147E+14



SQL> select * from v$logfile;

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
-------------------------------------------------------------
IS_
---
3 ONLINE
/u01/app/oracle/oradata/orcl/redo03.log
NO

2 ONLINE
/u01/app/oracle/oradata/orcl/redo02.log
NO

GROUP# STATUS TYPE
---------- ------- -------
MEMBER
---------------------------------------------------------------
IS_
---

1 ONLINE
/u01/app/oracle/oradata/orcl/redo01.log
NO




Apply logfiles sequence number wise.

First give path for sequence 10 then 11.


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo01.log
ORA-00279: change 808486 generated at 01/24/2012
15:03:43 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/ORCL/archivelog
/2012_01_24/o1_mf_1_11_%u_.arc
ORA-00280: change 808486 for thread 1 is in sequence #11
ORA-00278: log file '/u01/app/oracle/oradata/orcl
/redo01.log' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

CASE 7: Lost a read only tablespace which was read only when backup
was taken.


Suppose I lost my read only users tablespace.

SQL> alter tablespace users offline;

Tablespace altered.

Restore from Backup.

SQL> alter tablespace users online;

Tablespace altered.

CASE 8:
Creating new Control File After Losing All Current
and Backup Control Files.


SQL> shutdown abort
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 619360256 bytes
Fixed Size 1338280 bytes
Variable Size 398459992 bytes
Database Buffers 213909504 bytes
Redo Buffers 5652480 bytes
ORA-00205: error in identifying control file, check alert log for more info

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf'
CHARACTER SET WE8MSWIN1252;


Control file created.

SQL> recover database;


Media recovery complete.


-- All logs need archiving and a log switch is needed.

SQL> alter system archive log all;

System altered.


SQL> alter database open;

Database altered.

SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Tablespace altered.

Tuesday 9 April 2013

Oracle 11gR2 on Windows 7 32 bit-Operating System



Before start your journey,have a cup of tea or coffee with you ,it will make you concentrate on your job. Though I am not taking both of these,I feel boring and headache while installing it.So my suggest is to have something with you.

Now. Ready here we go.....

To install Oracle, do the following:

1) Verify that your Operating System is 32-bit by going to My Computer, System Properties



2) The Oracle Database 11g Release 2 files are broken down into two zip files on the Oracle site. That means you need to download both files, and then expand them into an installation directory. I called my installation directory C:\OracleInstall, but you can call it whatever works for you. Here’s a screen shot of the directory after expanding both compressed files (done with WinZip 15).

Here is the link I am providing you

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

and download as per your requirement ,I am talking about 32-bit operating system so just hold your nerve with it only.


3)Inside that you’ll find the database directory, and it should look like this: Double click on  
    setup.exe
Oracle11gFolder

4) If you see the following warning, click Yes.




5) Here you can take your 2nd Sip of Tea or coffee it will take some time. A command window will appear on your screen, do not click anywhere in this window. It will automatically close when it is done.



6) The result after waiting sometime,it will show a window like below
     It’s a good idea to provide an email address for security updates no matter what. You can uncheck  the box if you don’t want updates via Oracle Support Services. Click the Next button to continue.



7)  Most developers want to Create and configure a database. If that’s your desire, leave the default radio button checked. Click the Next button to continue.

http://blog.mclaughlinsoftware.com/wp-content/uploads/2011/12/Oracle11gR2_Install03.png

8  Most developers install their test instance on a desktop or laptop. If that’s your desire, leave the default Desktop Class radio button checked. Click the Next button to continue.



9) You will get the following screen:




10)  Select where on your hard disk will the installation files and program will be saved. This version
of Oracle needs at least 300 MB of free space in the C drive and a minimum of 3 GB of free
space in another drive. If you have enough space for both just install it all in the C drive.

11) Make sure that under Installation type, Enterprise Edition (3.27 GB) is selected.

12)  One of the most important fields to fill is the “Administrative Password”. This will be your main
password for accessing the software. So enter the password of your choice in both the
“Administrative Password” and “Confirm Password” fields, but make sure to remember it. (A
good password to use is admin if this is your own personal computer that no one else uses). The
user names are already predefined as SYS, SYSTEM or SYSMAN. Again, you need to
remember those in order to be able to gain access into Oracle after installation.

13) . After you’re done with steps 15-17, click next. You might get the warning message: “The ADMIN password entered does not conform to the Oracle recommended standards.”
Just ignore this and click yes.

14) If you get an error (or three) regarding invalid characters, make sure that the path you are
installing to does not contain apostrophes (‘) or spaces. Valid paths would include
C:\app\johnsmithscomputer. While an invalid path example would be C:\app\john smith’s
computer.

15) . The following screen is where the Oracle Installer checks for its requirements. Hopefully you’ll
have all the statuses of the checks as succeeded. If not check each warning or failure and see
why you’re getting that status. For example, if you have memory (RAM) on your computer of less
than 1 GB, you will get a notification as this is the minimum requirement.



16)  Assuming everything succeeded, now click on Finish to continue. This installation needs to also
install java as one of its components so if you get a message asking to either block or unblock,
choose unblock.

17) Now it's a TEA or COFFEE time. For this part just be patient while Oracle installs. You will be able to see the installation progress. The installation could take up to 30 minutes or more.

19)   When the progress bar starts configuring the instance, failures can occur. The first may occur during the network configuration, which typically happens if you didn’t preconfigure the hosts file. You don’t need to do anything but watch here unless the installer triggers an error.



20  The following progress bar is launched by the Oracle Database Configuration Assistant. It typically runs without a problem. You don’t need to do anything but watch here unless the installer triggers an error. This takes a few minutes, and unfortunately requires you to stick aroung to again authorize Java.



21.  The second popup screen requires you to authorize the Java runtime to call home. You should click the Allow Access button to proceed.




22. The third and last popup screen asks you to whether you want to open other scheme. Generally, you should click the OK button to proceed.

  1. The next dialog shows you that the network and database instances are configured. It’s also running the OEM (Oracle Enterprise Manager) installation. You can wait here but it won’t be a long wait.

  1. This is the last dialog and says you’ve installed Oracle Database 11g Release 2 successfully. You can the Close button to complete the installation.
   we are done........you can take your launch now