Moving a Single Datafile from File System to ASM System (METHOD 1)

Moving a Single Datafile from File System to ASM System 

Connect to sqlplus and create a new tablespace

SQL> select name from v$datafile;

NAME

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

+DATA/myasm/datafile/system.302.907085491

+DATA/myasm/datafile/sysaux.303.907085493

+DATA/myasm/datafile/undotbs1.304.907085495

+DATA/myasm/datafile/users.305.907085495

SQL> create tablespace demo datafile '/u01/demo2.dbf' size 10M;

Tablespace created.

SQL> select name from v$datafile;

NAME

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

+DATA/myasm/datafile/system.302.907085491

+DATA/myasm/datafile/sysaux.303.907085493

+DATA/myasm/datafile/undotbs1.304.907085495

+DATA/myasm/datafile/users.305.907085495

/u01/demo2.dbf

SQL> alter tablespace demo offline;

Tablespace altered.

SQL> !rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 21 21:57:43 2016

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

connected to target database: MYASM (DBID=36010415)

RMAN> copy datafile '/u01/demo2.dbf' to '+DATA';

Starting backup at 21-MAR-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=42 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/u01/demo2.dbf

output file name=+DATA/myasm/datafile/demo.319.907106321 tag=TAG20160321T215829 RECID=1 STAMP=907106329

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26

Finished backup at 21-MAR-16

RMAN> exit

Recovery Manager complete.

SQL> alter database rename file '/u01/demo2.dbf' to '+DATA/myasm/datafile/demo.319.907106321' ;

Database altered.

SQL> alter tablespace demo online;

Tablespace altered.

SQL> select name from v$datafile;

NAME

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

+DATA/myasm/datafile/system.302.907085491

+DATA/myasm/datafile/sysaux.303.907085493

+DATA/myasm/datafile/undotbs1.304.907085495

+DATA/myasm/datafile/users.305.907085495

+DATA/myasm/datafile/demo.319.907106321

6 rows selected.

SQL>

Moving a Single Datafile from File System to ASM System (METHOD 2)

Condition ,

-  If you want to move SYSTEM & UNDO tablespace, DB stage is mounted.

-  If you want to other tablespace datafiles , Tablespace should be OFFLINE

Step 1:

Check datafile & ASM diskgroup size

SQL> col FILE_NAME format a40

SQL> select tablespace_name , file_name from dba_data_files;

TABLESPACE   FILE_NAME

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

USERS                   +DATA/myasm/datafile/users.305.907085495

UNDOTBS1         +DATA/myasm/datafile/undotbs1.304.907085495

SYSAUX                +DATA/myasm/datafile/sysaux.303.907085493

SYSTEM                +DATA/myasm/datafile/system.302.90708541

USERS                  /u01/demo.dbf

TABLESPACE   FILE_NAME

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

DEMO                  +DATA/myasm/datafile/demo.319.907106321

6 rows selected.

Step 2: Tablespace offline

SQL> alter tablespace users offline;

Tablespace altered.

Step 3:

Here i just moved Users tablespace datafile to ‘+DATA’ diskgroup.

SQL> !rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 21 22:15:46 2016

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

connected to target database: MYASM (DBID=36010415)

RMAN> copy datafile '/u01/demo.dbf' to '+DATA';

Starting backup at 21-MAR-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=47 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/demo.dbf

output file name=+DATA/myasm/datafile/users.320.907107373 tag=TAG20160321T221611 RECID=2 STAMP=907107378

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08

Finished backup at 21-MAR-16

Step 4 :

Updataing the controlfile

RMAN> switch datafile '/u01/demo.dbf' to copy;

datafile 5 switched to datafile copy "+DATA/myasm/datafile/users.320.907107373"

RMAN> exit

Recovery Manager complete.

Step 5:

Check datafile location

SQL> select file_name from dba_data_files;

TABLESPACE   FILE_NAME

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

USERS                   +DATA/myasm/datafile/users.305.907085495

UNDOTBS1         +DATA/myasm/datafile/undotbs1.304.907085495

SYSAUX                +DATA/myasm/datafile/sysaux.303.907085493

SYSTEM              +DATA/myasm/datafile/system.302.90708541

USERS                  +DATA/myasm/datafile/users.320.907107373

TABLESPACE   FILE_NAME

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

DEMO                  +DATA/myasm/datafile/demo.319.907106321

6 rows selected.

SQL>

Redundancy and Recovery window in RMAN

Redundancy and Recovery window in RMAN

There is two mutually exclusive options for setting a retention policy; redundancy and recovery window.

To set Recovery Window of 3 days,

RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

Whenever retention policy is set to recovery window of 3 days then rman retains all information and backup data though which it can go to any point within 3 days from current date.

To set Recovery Window of redundancy 3 copies,

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

Whenever retention policy is set to redundancy of 3 copies the rman at least retains latest 3 copies of each datafile. If I took 4th backup of datafile 1 then 1st backup of datafile 1 become obsolete.

RMAN> CONFIGURE RETENTION POLICY TO NONE;

You can disable the retention policy by using this command

Note : If you don’t have flash recovery configured then the database does not delete any file even they are obsolete.

·        Obsolete means “not needed,” whereas expired means “not found.”

·        A backup becomes obsolete based on retention policy, that it is not needed for recovery.

·        A Backup becomes expired only when RMAN perform CROSSCHECK and can’t find the file. (Assume file is deleted by OS).

 RESTORE Command In RMAN

•RMAN always restore data file as image copy.

•If we simply use RESTORE command then RMAN directs a server session to restore the file to default location that is overwrite the existing file.

•If we use RESTORE command with SET NEWNAME option then RMAN restore the file with specified location.

Example: RUN {

SET NEWNAME FOR DATAFILE ‘/oradata/mydb/system01.dbf’ TO ‘/standby/system01.dbf’;

RESTORE

}

·        RMAN SWITCH command is equivalent to SQL statement ALTER DATABASE RENAME FILE command.

SWITCH command updates the control file. (This is helpful after restoring any data: so that we can notify our Controlfile for New Location)

Example: SWITCH DATAFILE ‘/oradata/mydb/system01.dbf’ TO DATAFILECOPY  ‘/standby/system01.dbf’

Points to Remember :

Ø  If RMAN has a choice between archived redo logs and incremental backups then RMAN always choice incremental backups during recovery.

Ø  RMAN can restore more quickly from image copies than from backup sets.

Ø  If there is no backup exist (During RESTORE) , then RMAN try to re‑create the datafile., It automatically searches other copies of Backup.

Ø  OPTIMIZATION : If a datafile is already present in the correct location and its header contains the expected information, then RMAN does not restore the datafile from backup.

      You can override this by FORCE option.

Ø  RMAN RESTORE … VALIDATE before restore database or any datafile you can verify whether database can successfully restored or not

Ø  An example, Before Performing Backup Check Any Physical or Logical Corruption.

Things to consider while adding new disk/ Rebalancing existing Disk Group

Take care for the following things while adding new disk OR Rebalancing existing Disk Group

1)         Make sure the disk are visible on all the cluster nodes; Disk readiness purely depend on the Os & version;

For ex:

Linux 5 – we will be using oracleasm as utility

Linux 6 – we will be using udev utility

2)         When you are planning to add disk to the disk group on the primary database, try to do it when database is having less load.

3)         Increase the ASM power limit to 9 or 10, depending on your environment. Once the rebalancing is completed, bring it to the original value.

Parameter è asm_power_limit

To increase or decrease the asm_power_limit

SQL> alter system set asm_power_limit=9;                 -- you need to issue it in the ASM instance.

4)         Add disks to one disk group at a time because rebalancing might cause the slowness to the database.

5)         Have a eye on the alert log of the ASM instance. If you are using cluster databases then you need to have a eye on all the instances in the cluster.

6)         You can monitor the rebalancing on the ASM instance using the below query

select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from gv$asm_operation;

7)         you can check the size of the diskgroup before & after adding the Diskgroup in the ASM.

$ asmcmd –p

ASMCMD> lsdg NIK_B_SYSTEM

8)         Messages in the alert, when the rebalancing starts & when it completes

Disk added on the Node#1 and final status updates on the Node#2

-On Node One

SQL> ALTER DISKGROUP NIK_B_SYSTEM ADD  DISK '/dev/mapper/NIK_B_SYSTEM_05' SIZE 51200M /* ASMCA */

NOTE: GroupBlock outside rolling migration privileged region

NOTE: Assigning number (21,4) to disk (/dev/mapper/NIK_B_SYSTEM_05)

NOTE: requesting all-instance membership refresh for group=21

NOTE: initializing header on grp 21 disk NIK_B_SYSTEM_0004

NOTE: requesting all-instance disk validation for group=21

Wed Aug 06 17:09:45 2014

NOTE: skipping rediscovery for group 21/0xdb38ead3 (NIK_B_SYSTEM) on local instance.

NOTE: requesting all-instance disk validation for group=21

NOTE: skipping rediscovery for group 21/0xdb38ead3 (NIK_B_SYSTEM) on local instance.

NOTE: initiating PST update: grp = 21

Wed Aug 06 17:09:51 2014

GMON updating group 21 at 9546 for pid 73, osid 16865

NOTE: PST update grp = 21 completed successfully

NOTE: membership refresh pending for group 21/0xdb38ead3 (NIK_B_SYSTEM)

GMON querying group 21 at 9547 for pid 18, osid 101970

NOTE: cache opening disk 4 of grp 21: NIK_B_SYSTEM_0004 path:/dev/mapper/NIK_B_SYSTEM_05

Wed Aug 06 17:09:56 2014

NOTE: Attempting voting file refresh on diskgroup NIK_B_SYSTEM

GMON querying group 21 at 9548 for pid 18, osid 101970

SUCCESS: refreshed membership for 21/0xdb38ead3 (NIK_B_SYSTEM)

Wed Aug 06 17:09:56 2014

SUCCESS: ALTER DISKGROUP NIK_B_SYSTEM ADD  DISK '/dev/mapper/NIK_B_SYSTEM_05' SIZE 51200M /* ASMCA */

NOTE: starting rebalance of group 21/0xdb38ead3 (NIK_B_SYSTEM) at power 9

Starting background process ARB0

Wed Aug 06 17:09:56 2014

ARB0 started with pid=74, OS id=91751

NOTE: assigning ARB0 to group 21/0xdb38ead3 (NIK_B_SYSTEM) with 9 parallel I/Os

NOTE: Attempting voting file refresh on diskgroup NIK_B_SYSTEM

Wed Aug 06 17:11:01 2014

-On Second One

NOTE: disk validation pending for group 21/0xdb38cf46 (NIK_B_SYSTEM)

SUCCESS: validated disks for 21/0xdb38cf46 (NIK_B_SYSTEM)

NOTE: disk validation pending for group 21/0xdb38cf46 (NIK_B_SYSTEM)

NOTE: Assigning number (21,4) to disk (/dev/mapper/NIK_B_SYSTEM_05)

SUCCESS: validated disks for 21/0xdb38cf46 (NIK_B_SYSTEM)

NOTE: membership refresh pending for group 21/0xdb38cf46 (NIK_B_SYSTEM)

Wed Aug 06 17:09:54 2014

GMON querying group 21 at 9721 for pid 18, osid 84704

NOTE: cache opening disk 4 of grp 21: NIK_B_SYSTEM_0004 path:/dev/mapper/NIK_B_SYSTEM_05

GMON querying group 21 at 9722 for pid 18, osid 84704

SUCCESS: refreshed membership for 21/0xdb38cf46 (NIK_B_SYSTEM)

Wed Aug 06 17:09:58 2014

NOTE: Attempting voting file refresh on diskgroup NIK_B_SYSTEM

Adding disk

1)         I am using asmca to add the disk to the existing Diskgroup.

2)         You need to plan what kind of redundancy you are opting for, if you are going for a new diskgroup.

3)   As soon as you added the disk in the asmca, you will be shown an output as “Disks added successfully to disk group DISK_GROUP_NAME”.
You need to see it in the sql prompt(on ASM instance) to know about the rebalancing status

What happens actually in case of instance Recovery?

While Oracle instance fails, Oracle performs an Instance Recovery when the associated database is being re-started.

Instance recovery always occurs in 2 steps:

Cache recovery:

Changes being made to a database are recorded in the database buffer cache as well as redo log files simultaneously. When there are enough data in the database buffer cache, they are written to data files.

Assume,  If an Oracle instance fails before these data are written to data files, Oracle uses online redo log files to recover the lost data when the associated database is re-started. This process is called cache recovery.

Transaction recovery:

 When a transaction modifies data in a database (the before image of the modified data is stored in an undo segment which is used to restore the original values in case the transaction is rolled back). At the time of an instance failure, the database may have uncommitted transactions.

It is possible that changes made by these uncommitted transactions have gotten saved in data files. To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started.

Oracle uses the undo data stored in undo segments to accomplish this. This process is called transaction recovery.

How to recover database if system datafile lost using RMAN

If system datafile removed from disk then you can  recover system datafile only in mount  state of database.

SQL> alter tablespace system offline;

alter tablespace system offline

*

ERROR at line 1:

ORA-01541: system tablespace cannot be brought offline; shut down if necessary

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

Startup database mount stage and bring system datafile offline. And start recovery

SQL> startup mount

ORACLE instance started.

Total System Global Area  313860096 bytes

Fixed Size                                1336232 bytes

Variable Size                        197135448 bytes

Database Buffers               109051904 bytes

Redo Buffers                         6336512 bytes

Database mounted.

SQL> !rman target /

RMAN> restore datafile 1;

Starting restore at 16-MAR-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/rman/system01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/RMAN/backupset/2016_03_16/o1_mf_nnndf_TAG20160316T141740_cgl7ffcx_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/RMAN/backupset/2016_03_16/o1_mf_nnndf_TAG20160316T141740_cgl7ffcx_.bkp tag=TAG20160316T141740

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:56

Finished restore at 16-MAR-16

RMAN> recover datafile 1;

Starting recover at 16-MAR-16

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 16-MAR-16

RMAN> exit

Recovery Manager complete.

SQL>

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01147: SYSTEM tablespace file 1 is offline

ORA-01110: data file 1: '/u01/app/oracle/oradata/rman/system01.dbf'

SQL> alter database datafile 1 online;

Database altered.

SQL> alter database open;

Database altered.

SQL>

See Also:  Recovery from Complete Loss of RedoLogs

OS relates interview questions for DBA Professional

1. How do you list the files in an UNIX directory while also showing hidden files?

ls -ltra

2. How do you execute a UNIX command in the background? 

Use the “&”

3. What UNIX command will control the default file permissions when files are created? 

Umask

4. Explain the read, write, and execute permissions on a UNIX directory.

Read allows you to see and list the directory contents.

Write allows you to create, edit and delete files and subdirectories in the directory.

Execute gives you the previous read/write permissions plus allows you to change into the directory and execute programs or shells from the directory.

5. the difference between a soft link and a hard link? 

A symbolic (soft) linked file and the targeted file can be located on the same or different file system while for a hard link they must be located on the same file system.

6. Give the command to display space usage on the UNIX file system.

df -lk

7. Explain iostat, vmstat and netstat.

 Iostat reports on terminal, disk and tape I/O activity.

Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity.

Netstat reports on the contents of network data structures.

8. How would you change all occurrences of a value using VI?

 Use :%s///g

9. Give two UNIX kernel parameters that effect an Oracle install

 SHMMAX & SHMMNI

10. Briefly, how do you install Oracle software on UNIX.

Basically, set up disks, kernel parameters, and run orainst..

Data Guard Modes

Oracle Data Guard can operate in 3 different modes:

o    Maximum Protection: Transactions are not allowed to commit until all redo data are written to the online redo logs and propagated to at least one synchronized secondary database. If for any reason, the primary database cannot propagate its redo stream to one secondary database, the primary will shutdown to ensure maximum protection mode.

o    Maximum Performance: Transactions are not allowed to commit as soon as the redo are written to the online redo logs. The redo stream is asynchronously propagated to the secondary databases to ensure maximum performance mode.

o    Maximum Availability: Transactions are not allowed to commit until all redo data are written to the online redo logs and propagated to at least one synchronized secondary database. If for any reason, the primary database cannot propagate its redo stream to one secondary database, the primary will NOT shutdown and operates as it it were in maximum performance mode until issues are fixed.

Summary

o    Role transition: Changing the role of each database component from primary database to the secondary database or from secondary database to the primary database.

o    Switchover: Planned role transition for testing. Manual intervention.

o    Fail Over: Unplanned failure. Manual or Automatic intervention. Automatic role transition is the recommended.

o    Primary database: Where the users are connected to access to the database.

o    Standby database: Exists in the the disaster recovery (DR) site. Where the users are connected in the case of planned role transition (Switchover) or in the case of unplanned failure (Fail Over).

o    Data Guard Observer: Process monitors both primary and standby databases and performs an automatic fail over when necessary.

o    The Broker: The management framework for Oracle Data Guard. It comes integrated into the oracle database enterprise edition.

Fast Start Fail Over (FSFO): Automatic fail over to the standby database occurs in case of failure. FSFO requires the broker

To perform point-in-time recovery to the older incarnation

 To perform point-in-time recovery to the older incarnation, use the following steps:

 

There are two requirements for this type of a point-in-time recovery:

• You must reset the current incarnation of the database back to the incarnation to which your

target SCN belongs.

• You must use the control file from the older incarnation that contains the target SCN.

1. Find out the incarnation key for the incarnation that was current at the time you want to

recover your database to.

 You can find it in the incarnation key column of the output of RMAN’s  LIST INCARNATION command.

 Let’s say our incarnation key value for this example is 5.

2. Start the database in the following way:

RMAN> STARTUP FORCE NOMOUNT;

3. Reset the current incarnation to the incarnation that was current at the point in time that

you want to recover to:

RMAN> RESET DATABASE TO INCARNATION 5;

4. Restore the old control file from a backup and mount the database with the following

commands:

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

RMAN> ALTER DATABASE MOUNT;

5. Restore and recover the database until the point in time or the SCN:

RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE UNTIL SCN 1000;

6. Open the database after resetting the online log files:

RMAN> ALTER DATABASE OPEN RESETLOGS;

This feature comes in handy when you perform a point-in-time recovery or a recovery using a backup control file. 

In these cases, you can still use the backup from before the RESETLOGS operation.

:)