Jan 06 2014

Restore And Recover Oracle 11g R2 Database on Windows 2008 R2 Server

Category: Administration,Backup And Recovery,Errors and SolutionsFatih Acar @ 15:43

You can restore and recover Oracle 11g R2 Database from RMAN backup on Windows 2008 R2 Server with below operations. You have to execute Command Prompt as Administrator for all operations.

Restore and Recover Database

Install Oracle software
Install Patch

Create ORACLE_HOME and ORACLE_SID in Server Environment Path.

Copy backup files to C:\orabackup

Create Oracle service with below command

cmd> oradim -NEW -SID YOURDBSID -STARTMODE manual -PFILE “$ORACLE_HOME\dbs\init.ora”

cmd> rman target /

RMAN> set dbid=12412312 Note : DBID writes in Control File backup name, if you use %F file name parameter.

RMAN> startup nomount force;

RMAN> restore spfile from ‘C:\orabackup\controlfilebackupfile’;

RMAN> exit;

cmd> sqlplus / as sysdba

SQL> create pfile=’C:\orabackup\pfile.ora’ from spfile;

You have to create archivelog directory and other directories in pfile.ora. You have to copy archivelog file to archivelog directory.

SQL> create spfile from pfile=’C:\orabackup\pfile.ora’;

SQL> shu abort;

SQL> startup nomount;

cmd> rman target /

RMAN> restore controlfile from ‘C:\orabackup\controlfilebackupfile’;

Control files restored to directories that are in pfile.ora

SQL> alter database mount;

If you use parallel backup to take backup, you can select backups like below.

RMAN> catalog backuppiece ‘C:\orabackup\backupfile1.bck’,’C:\orabackup\backupfile2.bck’;

RMAN> list backup;

You have to create directory for datafiles. Directories show in list backup;

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open resetlogs;

ORA-00392 log 3 of thread 1 is being cleared, operation not allowed
ORA-00312 online log 3 thread 1: ‘path’

If you get above error, you have to create path that writes in error. You get error because system can not create directory for redo logs.
You have to execute below SQL to clear logfile, you can open database.

SQL> alter database clear unarchived logfile group 3;

SQL> alter database open resetlogs;

SQL> select status from v$instance;

87,520 total views, 10 views today

Tags: Backup and Recovery, Database Administration, Oracle, Oracle Administration, Oracle Backup and Restore, Oracle Rman Backup


Sep 15 2013

MySQL Database Import And Export Operations

Category: Administration,Backup And RecoveryFatih Acar @ 20:43

Take Backup With Data

Open Command Prompt (cmd)
# mysqldump -u dbusername -p db_name > /tmp/db_fullbackup.sql

Take Only Metadata Backup

# mysqldump -u dbusername -p –no-data db_name > /tmp/db_backupmetadata.sql
or
# mysqldump -u dbusername -p -d db_name > /tmp/db_backupmetadata.sql

Restore Database Metadata From Backup

# mysql -u dbusername -p db_name < /tmp/db_backupmetadata.sql

Restore Database From Backup

# mysql -u dbusername -p db_name < /tmp/db_fullbackup.sql

9,908 total views, no views today

Tags: Backup and Recovery, MySQL, MySQL Administration


Jul 08 2013

Oracle Flashback Database Technology in Oracle 11g

Category: Administration,Backup And RecoveryFatih Acar @ 17:12

FLASHBACK DATABASE Technology to rewind the database to a target time, SCN or log sequence number. This command works by undoing changes made by Oracle Database to the datafiles that exist when you run the command. Flashback can fix logical failures, but not physical failures. Thus, you cannot use the command to recover from disk failures or the accidental deletion of datafiles.

FLASHBACK DATABASE is usually much faster than a RESTORE operation followed by point-in-time recovery, because the time needed to perform FLASHBACK DATABASE depends on the number of changes made to the database since the desired flashback time. On the other hand, the time needed to do a traditional point-in-time recovery from restored backups depends on the size of the database.

Requirements

  • Database must run in ARCHIVELOG mode.
  • Database must contain no online tablespaces for which flashback functionality was disabled with the SQL statement ALTER TABLESPACE … FLASHBACK OFF.

Open Flashback Database Mode

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

Continue reading “Oracle Flashback Database Technology in Oracle 11g”

18,517 total views, no views today

Tags: Backup and Recovery, Database Administration, Oracle, Oracle Administration


May 08 2012

How to Take Export Backup in Oracle on Windows Servers

Category: Backup And RecoveryFatih Acar @ 10:56

If you want to take export backup on windows servers, you can take with batch scripts and windows schedule tasks. You can create backup directory for this scripts and backups. We will use EXPORTDIR directory and below data, scripts, daily directories. You have to create three scripts file below scripts directory for this issue.

Firstly, you have to create export script file. The extension can be “.txt” for this file.

Export Script File : SchemaName.txt

USERID=sysbackup/password
LOG=D:\EXPORTDIR\data\data_SchemaName_exp.log
FILE=D:\EXPORTDIR\data\data_SchemaName_expdat.dmp
GRANTS=y
INDEXES=y
ROWS=y
CONSTRAINTS=y
OWNER=SchemaName

Secondly, you have to create execute script file. The extension can be “.bat” for this file. This file will execute SchemaName.txt file.

Execute Export Script File : Export_SchemaName.bat

set SHELL_DIR=D:\EXPORTDIR\scripts\
set BACKUP_DIR=D:\EXPORTDIR\data\
set BACKUP_DAY=%date:~10,4%%date:~4,2%%date:~7,2%
set BACKUP_DAILY=D:\EXPORTDIR\daily\
set ORACLE_SID=YOURDBSID

exp parfile=%SHELL_DIR%SchemaName.txt
date /T >> %BACKUP_DIR%log_SchemaName_exp.log

Thirdly, you have to create general executable batch file to execute export scripts of all schemas. This file will add windows schedule tasks. The extension can be “.bat” for this file. Also this script make zip file this backups.

Export File : Execute_Exports.bat

set SHELL_DIR=D:\EXPORTDIR\scripts\
set BACKUP_DIR=D:\EXPORTDIR\data\
set BACKUP_DAY=%date:~10,4%%date:~4,2%%date:~7,2%
set BACKUP_DAILY=D:\EXPORTDIR\daily\
set ORACLE_SID=YOURDBSID

REM Deletin Old Files

del %BACKUP_DAILY%* /Q

exp parfile=%SHELL_DIR%SchemaName.txt
date /T >> %BACKUP_DIR%log_SchemaName_exp.log

copy %BACKUP_DIR%data_SchemaName_expdat.dmp %BACKUP_DIR%data_SchemaName_expdat_%BACKUP_DAY%.dmp
“C:\Program Files (x86)\WinRAR\winrar.exe” a -p”yourwinrarpassword_.MKY” %BACKUP_DIR%data_SchemaName_expdat_%BACKUP_DAY%.rar %BACKUP_DIR%data_SchemaName_expdat_%BACKUP_DAY%.dmp
del %BACKUP_DIR%data_SchemaName_expdat_%BACKUP_DAY%.dmp
move %BACKUP_DIR%log_SchemaName_exp.log %BACKUP_DIR%log_SchemaName_exp_%BACKUP_DAY%.log
copy %BACKUP_DIR%data_SchemaName_expdat_%BACKUP_DAY%.rar %BACKUP_DAILY%

Finally, you have to add windows schedule tasks for Execute_Exports.bat as daily, weekly…
Continue reading “How to Take Export Backup in Oracle on Windows Servers”

32,361 total views, 2 views today

Tags: Backup and Recovery, Database Administration, Oracle


Dec 12 2011

Export and Import Data in Oracle

Category: Administration,Backup And RecoveryFatih Acar @ 00:26

Oracle’s export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting, database objects are dumped to a binary file which can then be imported into another Oracle database. These utilities can be used to move data between different machines, databases or schema. However, as they use a proprietary binary file format, they can only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database.

The export/import utilities are commonly used to perform the following tasks:

  • Backup and recovery (small databases only, say < +50GB, if bigger, use RMAN instead)
  • Move data between Oracle databases on different platforms (for example from Solaris to Windows)
  • Reorganization of data/ eliminate database fragmentation (export, drop and re-import tables)
  • Upgrade databases from extremely old versions of Oracle (when in-place upgrades are not supported by the Database Upgrade Assistant any more)
  • Detect database corruption. Ensure that all the data can be read
  • Transporting tablespaces between databases

Export Examples

$> exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no
$> exp scott/tiger file=emp.dmp tables=(emp,dept)
$> exp scott/tiger tables=emp query=”where deptno=10″
$> exp scott/tiger file=abc.dmp tables=abc query=\”where sex=\’f\’\” rows=yes

Import Examples

$> imp scott/tiger file=emp.dmp full=yes
$> imp scott/tiger file=emp.dmp fromuser=scott touser=scott tables=dept

Note : It is generally advised not to use exports as the only means of backing-up a database. Physical backup methods (for example, when you use RMAN) are normally much quicker and supports point in time based recovery (apply archivelogs after recovering a database). Also, exp/imp is not practical for large database environments.

11,717 total views, no views today

Tags: Backup and Recovery, Database Administration, Oracle