Case_study__7_______backup_recovery.docx

Case study 7

Backup Recovery

Oracle Data Pump (expdp, impdp) Pre requisites 1. Create folder as per space availability or 'E:\guru\dmp 2. Create service name for connecting database on network. 3. Connect as sysdba and run the following statement create a user dmp with default tablespace dmp of size 20m

CREATE OR REPLACE DIRECTORY test_dir AS 'E:\guru\dmp; GRANT READ, WRITE ON DIRECTORY test_dir TO dmp; Grant EXP_FULL_DATABASE to dmp; Create 2 more users ttest1 and test2 for practice purpose also create some objects in it.

Table Exports/Imports The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax. 1. Go to command prompt and execute datapump export command after completion of export check the logfile for any errors. expdp dmp/dmp@gaja tables=test.test,admin_emp directory=TEST_DIR dumpfile=gajatab1.dmp logfile=expdpgaja1.log 2. Connect to database base in another session and drop table test. 3. Go to command prompt and execute datapump import command,after completion of import check the logfile for any errors. impdp dmp/dmp@gaja tables=test.test directory=TEST_DIR dumpfile=gajatab1.dmp logfile=impdptest.log 4.Check for recovery of test table.

Schema Exports/Imports The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax. 1. Go to command prompt and execute datapump export command after completion of export check the logfile for any errors. expdp dmp/dmp@gaja schemas=test directory=TEST_DIR dumpfile=test.dmp logfile=expdptest.log 2. Connect to database base in another session and drop user test. 3. Go to command prompt and execute datapump import command, after completion of import check the logfile for any errors. impdp dmp/dmp@gaja schemas=test directory=TEST_DIR dumpfile=test.dmp logfile=impdpSCOTT.log 4. Connect as test user to confirm user is recovered.

Database Exports/Imports The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax. 1. Go to command prompt and execute datapump export command after completion of export check the logfile for any errors. expdp dmp/dmp@gaja full=Y directory=TEST_DIR dumpfile=gaja10G.dmp logfile=expdpgaja10G.log 2. Connect to database base in another session and drop user test. 3. Go to command prompt and execute datapump import command,after completion of import check the logfile for any errors. impdp dmp/dmp@gaja schemas=test directory=TEST_DIR dumpfile=gaja10G.dmp logfile=impdpSCOTT.log 4. Connect as test user to confirm user is recovered.

INCLUDE and EXCLUDE The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export/import. When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export/import. The two parameters are mutually exclusive, so use the parameter that requires the least entries to give you the result you require. The basic syntax for both parameters is the same. expdp dmp/dmp@gaja schemas=test include=TABLE:"IN ('test')" directory=TEST_DIR dumpfile=test.dmp logfile=expdptest.log expdp dmp/dmp@gaja schemas=test EXCLUDE=TABLE in ('test','vijay')" directory=TEST_DIR dumpfile=test2.dmp logfile=expdptest1.log expdp dmp/dmp@gaja full=y EXCLUDE=schema test directory=TEST_DIR dumpfile=test3.dmp logfile=expdptest2.log

Exporting /Importing Tablespace expdp dmp/dmp@gaja DIRECTORY=TEST_DIR DUMPFILE=tbs.dmp TABLESPACES= test logfile=exptbs.log impdp dmp/dmp@gaja DIRECTORY=TEST_DIR DUMPFILE=tbs.dmp TABLESPACES= test logfile=exptbs.log hear it exports all objects in the tablespace not tablespace itself.

Transportable Tablespace At source database CONN / AS SYSDBA 1. CREATE TABLESPACE test_data DATAFILE 'e:\guru\test_data01.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;

2. CREATE USER test_user IDENTIFIED BY test_user DEFAULT TABLESPACE test_data TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON test_data;

3. GRANT CREATE SESSION, CREATE TABLE TO test_user;

4. CONN test_user/test_user 5. CREATE TABLE test_tab ( id NUMBER, description VARCHAR2(50), CONSTRAINT test_tab_pk PRIMARY KEY (id) ); 6. INSERT /*+ APPEND */ INTO test_tab (id, description) SELECT level, 'Description for ' || level FROM dual CONNECT BY level <= 10000; COMMIT; 7. For a tablespace to be transportable it must be totally self-contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure CONN / AS SYSDBA EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST_DATA', incl_constraints => TRUE); 8. The TRANSPORT_SET_VIOLATIONS view is used to check for any violations. SELECT * FROM transport_set_violations; 9. ALTER TABLESPACE test_data READ ONLY; 10. CONN / AS SYSDBA CREATE OR REPLACE DIRECTORY temp_dir AS 'e:\dump'; GRANT READ, WRITE ON DIRECTORY temp_dir TO dmp1; 11. Now export the tablespace metadata. expdp dmp1/dmp1@gaja directory=temp_dir transport_tablespaces=test_data dumpfile=test_data.dmp logfile=test_data_exp.log 12. Create new folder in another drive like e:\trans\dump Copy dump file and all data file associated to the tablespace to this location. 13. Bring the tablesapce in read write mode ALTER TABLESPACE test_data READ WRITE;

At destination database 1. CONN / AS SYSDBA CREATE USER test_user IDENTIFIED BY test_user; GRANT CREATE SESSION, CREATE TABLE TO test_user;

2. CONN / AS SYSDBA CREATE USER dmp1 IDENTIFIED BY DMP1; GRANT DBA TO DMP1; Grant IMP_FULL_DATABASE to DMP1; CREATE OR REPLACE DIRECTORY temp_dir AS 'E:\guru\test'; GRANT READ, WRITE ON DIRECTORY temp_dir TO dmp1;

3. We can now import the tablespace metadata. impdp dmp1/dmp1 directory=temp_dir dumpfile=test_data.dmp logfile=test_data_imp.log transport_datafiles='e:\trans\dump\test_data01.dbf' 4. Bring imported tablesapce in read write mode. Alter tablespace test_data read write; 5. Check availibilty of tablesapce , tables data in the tables.

Syntax of Common RMAN Command-line Options RMAN [ TARGET connectStringSpec | { CATALOG connectStringSpec } | LOG ['] filename ['] [ APPEND ] . .

. ]...

connectStringSpec::= ['] [userid] [/ [password]] [@net_service_name] [']

The following example appends the output from an RMAN session to a text file at /tmp/msglog.log rman TARGET / LOG /tmp/msglog.log APPEND

Default RMAN Configuration The RMAN backup and recovery environment is preconfigured for each target database. The configuration is persistent and applies to all subsequent operations on this target database, even if you exit and restart RMAN. RMAN configured settings can specify backup devices, configure a connection to a backup device (known as a channel), policies affecting backup strategy, and others. The default configuration is adequate for most purposes.

Starting RMAN and Connecting to a Database Ensure database is up and running and service name is created for connecting to datbase C:\WINDOWS\system32>rman RMAN> connect target sys/cat1@cat connected to target database: CAT (DBID=811768807)

To show the current configuration for a database: 1. Start RMAN and connect to a target database. 2. Run the SHOW ALL command. For example, enter the command at the RMAN prompt as follows: RMAN> SHOW ALL;

The output lists the CONFIGURE commands to re-create this configuration.

RMAN> show all; RMAN configuration parameters for database with db_unique_name CATALOG are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\APP\VIJAY\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFCATALOG.ORA'; # default To define the path for backup file add following parameter in pfile or spfile DB_RECOVERY_FILE_DEST = 'E:\cat\omfrc' DB_RECOVERY_FILE_DEST_SIZE = 2000m Bounce the database.

Throgh RMAN again connect to target database. C:\WINDOWS\system32>rman RMAN> connect target sys/cat1@cat

Specifying Backup Set or Copy for an RMAN Backup to Disk RMAN> backup as backupset device type disk database; RMAN> backup as copy device type disk database; RMAN> backup as copy device type sbt database; RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

RMAN> BACKUP DATABASE FORMAT "e:\cat\omfrc\backup_%U"; In this case, backups are stored with generated unique file names with the prefix /disk1/backup_. The %U substitution variable, used to generate a unique string at this point in the file name, is required.

Using multiple channel RMAN> RUN { ALLOCATE CHANNEL disk1 DEVICE TYPE DISK ; ALLOCATE CHANNEL disk2 DEVICE TYPE DISK ; ALLOCATE CHANNEL disk3 DEVICE TYPE DISK ; backup database; } RMAN> RUN { ALLOCATE CHANNEL disk1 DEVICE TYPE DISK ; ALLOCATE CHANNEL disk2 DEVICE TYPE DISK ; ALLOCATE CHANNEL disk3 DEVICE TYPE DISK ; BACKUP AS COPY DATABASE; }

Making Compressed Backups

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

To back up tablespaces or datafiles: RMAN> BACKUP DEVICE TYPE disk TABLESPACE vijay, cat10; RMAN> BACKUP DEVICE TYPE disk DATAFILE 1,2,3,4 ;

Backup of the Control File RMAN> BACKUP DEVICE TYPE disk TABLESPACE cat10 INCLUDE CURRENT CONTROLFILE; RMAN> BACKUP AS COPY CURRENT CONTROLFILE ; RMAN> BACKUP DEVICE TYPE disk CONTROLFILECOPY 'E:\CAT\OMFRC\CAT\CONTROLFILE\O1_MF_TAG20190303T160641_G7QCKSRB_.CTL';

Backing Up Server Parameter Files Ensure instance is started with spfile for this option RMAN> BACKUP DEVICE TYPE disk SPFILE;

Backing Up a Database in NOARCHIVELOG Mode Shutdown the database and startup database in mount stage. And take Rman backup RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG 'BACKUP_1';

back up archived redo log files RMAN> BACKUP DATABASE PLUS ARCHIVELOG; You can also specify a range of archived redo logs by time, SCN, or log sequence number BACKUP ARCHIVELOG FROM TIME 'SYSDATE-10' UNTIL TIME 'SYSDATE-7'; BACKUP ARCHIVELOG FROM sequence=200 until sequence=230;

Deleting Archived Redo Logs After Backups

BACKUP DEVICE TYPE disk ARCHIVELOG ALL DELETE INPUT; BACKUP DEVICE TYPE disk ARCHIVELOG ALL DELETE ALL INPUT;

Making Incremental Backups After starting RMAN, run the BACKUP INCREMENTAL command at the RMAN prompt. By default incremental backups are differential.

To make an incremental backup: 1. Start RMAN and connect to a target database and a recovery catalog (if used). 2. Ensure that the target database is mounted or open. 3. Execute the BACKUP INCREMENTAL command with the desired options. Use the LEVEL parameter to indicate the incremental level. The following example makes a level 0 incremental database backup.

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

BACKUP INCREMENTAL LEVEL 1 DATABASE; Analyse the error….. Create new tablesapce in the database. Exit from Rman and reconnect to target database. RMAN>Exit RMAN>connect target sys/cat1@cat Ake the icrimental level backup BACKUP INCREMENTAL LEVEL 1 DATABASE;