Oracle: Recover Database Using RMAN Until a Log Sequence Number

In this scenario we will recover database using RMAN until a log sequence number SCN.

First create a table called customers and count the number of rows.

SQL> select count(*) from customers;

  COUNT(*)
----------
         4

SQL> alter system switch logfile;

System altered.

Check whether database is in archivelog mod or not.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\app\SantoshTiwari\oradata\arc
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2

Now delete table customers.

SQL> delete customers;

4 rows deleted.

SQL> commit;

Commit complete.


SQL> select count(*) from customers;

  COUNT(*)
----------
         0

Now check the sequence no to apply so that we can recover table as it was before deletion.

SQL> select sequence#,first_change#, to_char(first_time,'HH24:MI:SS') from v$log order by
3;

 SEQUENCE# FIRST_CHANGE# TO_CHAR(
---------- ------------- --------
         1       1025275 18:31:53
         2       1025651 18:34:56
         3        1025944 18:37:59

Shut down the database and start in mount state.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             398462964 bytes
Database Buffers          201326592 bytes
Redo Buffers                5640192 bytes
Database mounted.


Recover using SQL prompt.

SQL> recover database UNTIL CHANGE 2;
Media recovery complete.

-----------------------------------------------
Recover using RMAN

RMAN> run {
2> set until sequence=2;
3> restore database;
4> recover database;
5>alter database open resetlogs;
6> }

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

SQL> select count(*) from customers;

  COUNT(*)

----------

         4

READ MORE ...

Oracle: How to Get the DBID in NOMOUNT State

DBID is the most important parameter to restore and recover controlfile using Rman. You can easily find DBID in open state but what you will do in nomount stat.

Steps to find DBID in NOMOUNT state:

First, start the database in nomount state:

SQL>startup nomount;

Now set the label for identification of tracefile that will be generated during this process. 

SQL>alter session set tracefile_identifier =santosh;

Get datafile name and location using v$datafile and dump some blocks of that datafile.

SQL> select name from v$datafile;

NAME

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

D:\APP\SANTOSH\ORADATA\TEST11\SYSTEM01.DBF

D:\APP\SANTOSH\ORADATA\TEST11\SYSAUX01.DBF

D:\APP\SANTOSH\ORADATA\TEST11\UNDOTBS01.DBF

D:\APP\SANTOSH\ORADATA\TEST11\USERS01.DBF

D:\APP\SANTOSH\ORADATA\TEST11\USERS02.DBF

SQL> alter system dump datafile 'D:\APP\SANTOSH\ORADATA\TEST11\USERS02.DBF' 

          2  block min 1 block max10;

                System altered.

Now, check trace file using tracefile_identifier(Label) in my case it is santosh. To see the the trace file location we have:

SQL> show parameter user_dump_dest

Now open the trace file and you will get Db ID =XXXXX

 Start dump data block from file D:\APP\SANTOSHTIWARI\ORADATA\TEST11\USERS02.DBF minblk 1 maxblk 10

 V10 STYLE FILE HEADER:

Compatibility Vsn = 186646528=0xb200000

Db ID=3561501508=0xd4483344, Db Name='TEST11'

Activation ID=0=0x0

Control Seq=932=0x3a4, File size=1280=0x500

File Number=5, Blksiz=8192, File Type=3 DATA

Note: Take sufficient backup before dumping blocks because you may loose data, so perform this operation on your own risk.

READ MORE ...

RMAN-05541: No Archived Logs Found in Target Database

Issue: RMAN-05541: no archived logs found in target database.

When performing a RMAN Database duplication, I get the following error:

RMAN-03002: failure of Duplicate Db command at 07/30/2013 09:39:05
RMAN-05501: aborting duplication of target database
RMAN-05541: no archived logs found in target database

Cause:

• Target database may not be in the archivelog mode before taking backup so first check it.
• Sometimes Database in archived log mode, but control file did not have any archived log records. 

Solution:

Enable archivelog mode and perform log switch before backup.

sql> alter database archivelog;


SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            D:\app\SantoshTiwari\oradata\arc
Oldest online log sequence  
Next log sequence to archive  
Current log sequence        


SQL> alter system archive log current;

System altered.

READ MORE ...

Oracle: Find Default Permanent Tablespace using Database_Properties

To prevent people from accidentally having their own database objects in the SYSTEM tablespace Oracle introduced a default permanent tablespace because it is not healthy in terms of usability point of view to keep your own objects in SYSTEM tablespace.

Every database users allocated a default permanent tablespace as USERS to store their own objects like tables and indexes.

Sometimes Default Permanent Tablespace Set to a System Tablespace, it means that default tablespace for newly created user will assign this tablespace.

To find Default Permanent Tablespace:

SQL> select PROPERTY_VALUE from database_properties
  2  where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
---------------
USERS

 If it is system tablespace then change it as:

sql>alter database default tablespace users;

READ MORE ...

Oracle: How to Find the Table Size

There are different ways to find table size in oracle, views associated to check size of tables are:

• DBA_EXTENTS
• USER_EXTENTS
• DBA_SEGMENTS

Method 1:

To find size of tables using view DBA_SEGMENTS:

SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024 MB, TABLESPACE_NAME

  2  FROM DBA_SEGMENTS

  3  WHERE SEGMENT_TYPE='TABLE' AND SEGMENT_NAME='CUSTOMERS';

SEGMENT_NAME    SEGMENT_TY         MB TABLESPACE_NAME

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

CUSTOMERS       TABLE           .0625 SYSTEM

Method 2:

To find size of tables using view DBA_EXTENTS:

SQL>  SELECT SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024 MB, TABLESPACE_NAME

  2   FROM DBA_EXTENTS

  3   WHERE SEGMENT_TYPE='TABLE' AND SEGMENT_NAME='CUSTOMERS';

SEGMENT_NAME    SEGMENT_TY         MB TABLESPACE_NAME

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

CUSTOMERS       TABLE           .0625 SYSTEM

As a user you can also use user_extents.

READ MORE ...

Oracle: Find Total Size of The Database

The size of the oracle database is the total size of the data files, redo log files and temp files that make up the tablespaces of the database. These details are found in the dba_extents view. Type the following lines at the SQL prompt:

Method 1: To find used space within the database.

SQL>select sum(bytes/1024/1024/1024) as SIZE from   dba_extents;

Method 2: To find overall database size:

The biggest portion of a database's size comes under the datafiles.
To get size of all datafiles in MB:

SQL>select sum(bytes)/1024/1024 "Meg" from dba_data_files;
To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;
To get the size of the on-line redo-logs:

select sum(bytes)/1024/1024 "Meg" from sys.v_$log;


Combining all together we will get overall size: 

SQL> select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual

Check Oracle database faq:
http://www.orafaq.com/ 

READ MORE ...

How to Enable/Disable Archive Log Mode in 10g/11g

By default when you enter CREATE DATABASE statement, it is in noarchive log mode, to change the archiving mode of the database, use the ALTER DATABASE statement with the ARCHIVELOG or NOARCHIVELOG clause using administrator privileges (AS SYSDBA).

By default, Oracle database store archive logs data into the flash recovery area. If you want to set  new location for archive logs, you can set using the parameter LOG_ARCHIVE_DEST_n to the location in which you wish to write archive logs.

SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 2782M
SQL>




SQL> alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl01/arch01' scope = both;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/oradata/orcl01/arch01
Oldest online log sequence     26
Current log sequence           28
SQL> 

How to Enable Archive log Mode

Shut down the database instance and startup in mount stage:

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             394268660 bytes
Database Buffers          205520896 bytes
Redo Buffers                5640192 bytes
Database mounted.


SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/app/oracle/oradata/orcl01/arch01
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28

How to Disable Archive log Mode

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28

Shut down the oracle database instance and start in mount stage.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             394268660 bytes
Database Buffers          205520896 bytes
Redo Buffers                5640192 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/oradata/orcl01/arch01
Oldest online log sequence     26
Current log sequence           28

You can also check official oracle docs for enabling/disabling archivelog mode here:

Controlling Archive

READ MORE ...

Oracle: How to Find the Database ID (DBID)

Every Oracle database has it's unique identifier called DBID which is necessary in most critical situations like recovery from loss of all control file using rman autobackup.

How to find Oracle DBID ?

Method 1:

You can easily find using V$database view:

SQL> select dbid from v$database;

      DBID

----------

3561501577

Method 2:

Find using RMAN:

C:\windows\system32>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 1 18:06:14 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST11 (DBID=3561501508)

READ MORE ...

How to Restore/Recover Lost of Control File Using Rman

Oracle database maintain three copies of Controlfile at three different locations. There are some situations arises when you loses all your controlfile. So in this case how to restore and recover controlfile using RMAN autobackup option.

One day when I tried to mount the Database I encountered with an error code:
ORA-00205: error in identifying control file, check alert log for more info

SQL> startup mount
ORACLE instance started.

Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             394268660 bytes
Database Buffers          205520896 bytes
Redo Buffers                5640192 bytes
ORA-00205: error in identifying control file, check alert log for more info
=============================================================
How to restore control file using RMAN

C:\windows\system32>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 1 15:45:17 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST11 (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 01-AUG-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/01/2014 15:46:27
RMAN-06495: must explicitly specify DBID with SET DBID command

RMAN> restore controlfile;

Starting restore at 01-AUG-14
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/01/2014 15:47:03
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

STEP 1: You need to set DBID before recovery

 SQL> host rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 1 17:26:13 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST11 (DBID=3561501508)

SQL> select dbid from v$database;

      DBID
----------

3561501508

But if your database is not mounted, you cannot find DBID. So set ID to any number you want.
ex: 

RMAN> set DBID=3561501508


executing command: SET DBID

STEP2: Restore control file from autobackup:

RMAN> restore controlfile from autobackup;

Starting restore at 01-AUG-14
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20140801
channel ORA_DISK_1: AUTOBACKUP found: c-3561501508-20140801-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP c-3561501508-20140801-00
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=D:\APP\SANTOSHTIWARI\ORADATA\TEST11\CONTROL01.CTL
Finished restore at 01-AUG-14

STEP3: 

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

STEP4
RMAN> recover database;

Starting recover at 01-AUG-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file D:\APP\SANTOSHTIWARI\ORADATA\TEST11\REDO03.LOG
archived log file name=D:\APP\SANTOSHTIWARI\ORADATA\TEST11\REDO03.LOG thread=1 sequence=3
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-AUG-14

RMAN> alter database open resetlogs;

database opened

READ MORE ...

ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1], [65], [52510], [52529], []

ORA-00600 internal error code:

This error code is native to internal exceptions from the Oracle programs. It
indicates that the process has encountered an unexpected problem. It may be
due to:
Timeout
A corrupted file
A crash that has affected stored data
I/O issue "(physical memory)"

ORA-00600: internal error code resolved:



SQL> alter database open
  2  ;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_tha
[65], [52510], [52529], [], [], [], [], [], [], []

THIS ERROR IS DUE TO INVALID ENTRY IN THE CONTROL FILE:

SOLUTION TO  ORA-00600:

IT IS BETTER TO CREATE A NEW CONTROL FILE.

FIRST CHECK IF CONTROL FILE EXIST OR NOT?

SQL>STARTUP NOMOUNT

SQL> ed a.sql


IF THERE WILL BE A CONTROL FILE THEN IT WILL OPEN. NOW INSIDE CONTROL FILE CHECK THE LOCATION OF:
REDO LOGS
SYSTEM FILE
SYSAUX FILE
UDO FILE
USER FILE

FINALLY DELETE OR BACKUP OLDER CONTROL FILE AND RECREATE NEW ONE USING:
SQL>@ a.sql

CASE 2: IF CONTROL FILE DOESN'T EXIST THEN RECREATE NEW ONE:

SQL>SHUT IMMEDIATE
SQL>STARTUP MOUNT
SQL> alter database backup controlfile to trace
  2  ;

Database altered.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             385880052 bytes
Database Buffers          213909504 bytes
Redo Buffers                5640192 bytes
SQL> ed a.sql
DELETE OLDER CONTROL FILE BEFORE CREATING NEW ONE:
SQL> @ a.sql

Control file created.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> SELECT OPEN_MODE FROM V$DATABASE;

OPEN_MODE
--------------------
READ WRITE

READ MORE ...

How to List all Users Account in Oracle Database

In Oracle sometimes it is necessary to view the list of all users for retrieving some attributes or settings of passwords using sql. So here you will get all the methods to Show all users in Oracle Database using SQL and MySQL.

To List current user in SQL:

Method 1:
SQL>SHOW USER

Method2:
SQL>SELECT USERNAME FROM DBA_USERS;


To describe users:
SQL>DESC ALL_USERS;

To Show All Users:
SQL>SELECT * FROM ALL_USERS;

How to show the list of MySQL users and their privileges
mysql> select * from mysql.user;

To describe MySQL users:
MYSQL> desc mysql.user;

READ MORE ...

How to Reset Forgotten MySQL Root Password

For every database administrator it is essential to know the methods of  resetting root password in case if it is forgotten by them. For mysql, the system administrator is called root. You will use the mysqladmin utility from a command line to set the new password. Notice that there are two commands to be run.

Syntax:

mysqladmin -u root password “newpassword”

mysqladmin -u root -h host_name password “newpassword”

Example:

mysqladmin -u root password ws8dr8as3

mysqladmin -u root -h localhost password ws8dr8as3

You will also want to restart the database server after running this command

sudo /etc/init.d/mysql restart

How to change MySQL user password using UPDATE command in MySQL?

mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='santosh';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Change MySQL root user password using mysql command:

MySQL stores username and passwords in user table inside MySQL database. You can directly update or change the password using the following method for the user santosh.

Login to mysql server, type the following command at shell prompt:
$ mysql -u root -p

Use mysql database (type command at mysql> prompt):

mysql> use mysql;
Change password for user santosh, enter:

mysql> update user set password=PASSWORD("NEWPASSWORD") where User='santosh';
Finally, reload the privileges:

mysql> flush privileges;
mysql> quit

READ MORE ...