RMAN Data Recovery Advisor ( 11G )

dev@lala:/u01/database/dev$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 – Production on Wed Sep 3 16:02:22 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test (col1 number) tablespace users;
create table test (col1 number) tablespace users
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/u01/database/dev/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
dev@lala:/u01/database/dev$ rman target /

Recovery Manager: Release 11.1.0.6.0 – Production on Wed Sep 3 16:02:55 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: DEV (DBID=3742639916)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
182        HIGH     OPEN      03-SEP-08     One or more non-system datafiles are missing

RMAN> list failure 182 detail;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
182        HIGH     OPEN      03-SEP-08     One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 182
Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
185        HIGH     OPEN      03-SEP-08     Datafile 4: ‘/u01/database/dev/users01.dbf’ is missing
Impact: Some objects in tablespace USERS might be unavailable

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
182        HIGH     OPEN      03-SEP-08     One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 182
Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
185        HIGH     OPEN      03-SEP-08     Datafile 4: ‘/u01/database/dev/users01.dbf’ is missing
Impact: Some objects in tablespace USERS might be unavailable

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/database/dev/users01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
—— ——————
1      Restore and recover datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/dev/dev/hm/reco_3070598829.hm

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/dev/dev/hm/reco_3070598829.hm

contents of repair script:
# restore and recover datafile
sql ‘alter database datafile 4 offline’;
restore datafile 4;
recover datafile 4;
sql ‘alter database datafile 4 online’;

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/dev/dev/hm/reco_3070598829.hm

contents of repair script:
# restore and recover datafile
sql ‘alter database datafile 4 offline’;
restore datafile 4;
recover datafile 4;
sql ‘alter database datafile 4 online’;

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

sql statement: alter database datafile 4 offline

Starting restore at 03-SEP-08
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=6 STAMP=664368067 file name=/u01/app/oracle/flash_recovery_area/DEV/datafile/o1_mf_users_4ct2klrs_.dbf
destination for restore of datafile 00004: /u01/database/dev/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/u01/database/dev/users01.dbf RECID=0 STAMP=0
Finished restore at 03-SEP-08

Starting recover at 03-SEP-08
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 43 is already on disk as file /u01/app/oracle/flash_recovery_area/DEV/archivelog/2008_09_02/o1_mf_1_43_4cvbgbo7_.arc
archived log for thread 1 with sequence 44 is already on disk as file /u01/app/oracle/flash_recovery_area/DEV/archivelog/2008_09_03/o1_mf_1_44_4cw37lw7_.arc
archived log for thread 1 with sequence 45 is already on disk as file /u01/app/oracle/flash_recovery_area/DEV/archivelog/2008_09_03/o1_mf_1_45_4cx9ophk_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/DEV/archivelog/2008_09_02/o1_mf_1_43_4cvbgbo7_.arc thread=1 sequence=43
media recovery complete, elapsed time: 00:00:02
Finished recover at 03-SEP-08

sql statement: alter database datafile 4 online
repair failure complete

RMAN>

MySQL DUMP

Command to backup all the MySQL database in single shot is ,

$mysqldump -u root -p –all-databases > alldatabases.sql

Command to restore the MySQL database is ,

( Please make sure that you have database created before you can restore the data )

$mysql -u root -p database_to_restore < alldatabases.sql

ASM on Ubuntu 8.04.1 LTS Hardy

• Simulate a disk from a file?

If you have a free partition or disk to be used as an ASM disk, just skip this step. If you don’t, you can create a file with the dd command and create a device that actually loops to the file with the losetup command.

Let’s assume you’ve created a directory named /asm (and you have write access to it). Run the command below to create a file named disk1 that is 3GB in size:

$ dd if=/dev/zero of=/asm/disk1 bs=1024k count=3072
3072+0 records in
3072+0 records out
3221225472 bytes (3.2 GB) copied, 80.9113 seconds, 39.8 MB/s

Once you’ve created the file, map it to a device named loopN in /dev. You can list the used loop devices with the losetup -a command.

Once you’ve made sure the one you plan to used is free, e.g. /dev/loop1, you can map the device to the file with the following commands (you have to be root) :

# losetup /dev/loop1 /asm/disk1
# losetup -a
/dev/loop1: [0802]:7438407 (/asm/disk1)

• Create an interface to the disk that is usable by ASM?

Actually, the preferred way to access a disk from ASM on Linux is ASMLib. To be fair, I must say I didn’t even give it a try. Of course, I’d be more than interested if anybody could make it work on Ubuntu. You’ll have to recompile the source code and I doubt I’ll be able to do it myself.

So you may think, why not use Raw Devices? Because you don’t need raw devices. You can just map the ASM disks to your disk/partition or loop devices.

So the only thing you need to do to start is change the ownership of the device so that ASM can access it in read/write mode as oracle:

# chown oracle:dba /dev/loop1
# ls -l /dev/loop1
brw-rw—- 1 oracle dba 7, 1 2008-02-06 23:32 /dev/loop1

If you have a real disk partition (e.g. /dev/sdb1):

# chown oracle:dba /dev/sdb1
# ls -l /dev/sdb1

Note:
If you want losetup run and ownership set up automatically at boot time, you’ll have to define the correct rules in /etc/udev/rules.d.

• Start up the Cluster Synchronization Service Daemon?

You may not have paid attention to it before, but ASM relies on the CSS Daemon. Who cares? I do, because CSSD relies on /etc/inittab to startup and there is no such a file in Ubuntu. Nevermind, run the setup script as root :

# /u01/app/oracle/product/11.1.0/db_1/bin/localconfig reset
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘root’..
Operation successful.
Configuration for local CSS has been initialized

Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process…
Expecting the CRS daemons to be up within 600 seconds.

Once you get the message about the 600 seconds, hit CRTL+C to stop the script, and then run the command below to actually run the CSS Daemon:

# nohup /etc/init.d/init.cssd run >/dev/null 2>&1 </dev/null &

Note 3: If you want the CSSD daemon to startup automatically, you’ll have to create a service in Ubuntu.

• Create the ASM instance, add a diskgroup

Setup the PATH, ORACLE_HOME and ORACLE_BASE variables, then

$ dbca -silent -configureASM             \
       -asmSysPassword oracle            \
       -diskString "/dev/loop*"          \
       -diskList /dev/loop1              \
       -diskGroupName Data               \
       -redundancy EXTERNAL

Once that is done, you can connect to the ASM instance with SQL*Plus:

$ source oraenv
ORACLE_SID = [oracle] ?+ASM
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle

$ sqlplus / as sysdba
SQL> select NAME, TOTAL_MB from v$asm_diskgroup;

NAME TOTAL_MB
---- --------
DG1      3072

Or you can use ASMCMD as below:

$ source oraenv
ORACLE_SID = [oracle] ?+ASM
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle
$ asmcmd
ASMCMD> ls
DATA/

• Create a database that uses ASM?

I cannot resist providing the syntax for 11g (totalMemory will have to be replaced by memoryPercentage in 10g):

$ dbca -silent -createDatabase              \
       -templateName General_Purpose.dbc    \
       -gdbName asmdb                        \
       -sysPassword oracle                  \
       -systemPassword manager              \
       -emConfiguration NONE                \
       -storageType ASM                     \
          -asmSysPassword oracle            \
          -diskGroupName DATA                \
       -characterSet WE8ISO8859P15          \
       -totalMemory  1024

rpm: To install rpm packages on Debian systems, use alien. See README.Debian.
error: cannot open Packages index using db3 - No such file or directory (2)
error: cannot open Packages database in /var/lib/rpm
rpm: To install rpm packages on Debian systems, use alien. See README.Debian.
error: cannot open Packages index using db3 - No such file or directory (2)
error: cannot open Packages database in /var/lib/rpm
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
33% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
77% complete
88% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/asmdb/asm.log" for further details.

IE on Ubuntu

IEs 4 Linux needs two packages: cabextract and Wine. You can install them using your Linux package manager (synaptic, apt-get, yum, emerge etc) or go to their sites.

You have to enable universe packages first. It is also recommended that you use the official winehq ubuntu package:

1) Open a terminal

2) Open /etc/apt/sources.list

 sudo gedit /etc/apt/sources.list

3) Uncomment (or add) following lines:

 deb http://us.archive.ubuntu.com/ubuntu edgy universe

4) Add this line:

 deb http://wine.budgetdedicated.com/apt edgy main

5) Close gedit. Update and install wine and cabextract:

 wget -q http://wine.budgetdedicated.com/apt/387EE263.gpg -O- | sudo apt-key add -
 sudo apt-get update
 sudo apt-get install wine cabextract

6) Download IEs 4 Linux and install

 wget http://www.tatanka.com.br/ies4linux/downloads/ies4linux-latest.tar.gz
 tar zxvf ies4linux-latest.tar.gz
 cd ies4linux-*
 ./ies4linux

7) Run the IE from $HOME/bin directory.