Backup and Recovery -2

1. Difference between differential and cumulative backup?
   => Differential backup – Any changes from previous level 1 backup (Differential backup)
   RMAN > backup incremental level 1 database; (Differential backup query)
   => Cumulative backup – Any changes from Level 0 backup (backup database level 1 cumulative)
   RMAN > backup incremental level 1 cumulative database; (Cumulative backup query)
2. What is the use of BCT?
   Block changing tracking improves the performance of incremental backups by recording changed blocks in the block change tracking file. During an incremental backup, instead of scanning all data blocks to identify which blocks have changed, RMAN uses this file to identify the changed blocks that need to be backed up.
   RMAN> alter database enable block change tacking;
   RMAN> alter database disable block change tacking;
3. How to increase the performance of RMAN backup?
   • By using Channel
   • By using BCT (Block change tracking) performance parameter we can increase the performance of Rman backup.
   RMAN > alter database enable block change tracking;
   RMAN > alter database disable block change tracking;
4. How will recover a table using RMAN? Explain the steps with commands?
   Step 1: By using Rman First we need to restore the backup of the database.
   Rman > restore tablespace; (Better option instead of restoring the database)
   Step 2:
   RMAN> run
   {
   recover table
   scott.emp
   until time ”to_date(’24/06/2021 02:00:00′,’dd/mm/yyyy hh24:mi:ss’)”
   Auxilaiary destination (‘/tempdbs’)
   Datapump destination ‘/data/archive11/’
   Dump file emp.dmp
   Notableimport;
   }
5. What is schema refresh? what are the post refresh activities after schema refresh?
   a) Schema refresh is one of the routine tasks in DBA’s, for moving the objects from one schema to another schema or one database to another database, normally we use expdp /impdp or exp/imp
   b) Post refresh, we need to gather stat the table
   SQL> @?/rdbms/admin/utlrp.sql (It recompile the invalid objects)
   SQL> exec dbms_stats.gather_schema_stats(‘Gops’,cascade=true);
6. Given an exp/expdp full dump (without export logfile) how will you create a database with it?
   Nologfile = Yes
7. What is the use of compress, consistent for exp?
   => Compress=y (Multiple extents into single extends)
   => Consistent-
   set transaction=readonly and consistent=y (At the point of time changes/commited transaction in DB only export)
8. What is the performance parameters for exp/imp/expdp/impdp?
   • exp/imp paramters: direct=y, buffer=6000, statistics=none
   • expdp/impdp parameters: paralle=4 (depends upon cpu), estimate=statistics or blocks (dictionary reads)
9. What are the performance parameters for SQL loader?
   • sqlldr control =first.ctl
   • parallel=true
   • direct=true
10. How will you take export of 20 tables? explain steps with commands?
    => To create a parfile and run parfile
    Steps:
    1. vi exportparfile.par
    2. exp file=testdump.dmp log=test.log
    tables=tab1 tab2 tab3 tab4 tab5 tab6 tab7 tab8 tab9 tab10 tab11 tab12 tab13 tab14 tab15 tab16 tab17 tab18 tab19 tab20
    direct=y
    statistics=none
    3. Run the parfile:
    exp parfile=exportparfile.par
    …….it gets export of 20 tables………
11. Given a 100gb table dump, but having 5 gb of undo, how will you import the table?
    *commit=y
12. On which oracle utility you will get snapshot too old error?
    In Export Only –> Consistent=y, Undo Tablespace – ORA -01555
13. What is use of resumable_timeout parameter in DB as well as EXP/expdp?
    Resumable_timeout is an initialization parameter introduced in Oracle 9i. This parameter defines the duration in seconds, for which the statements will be suspended if there is no space available in the tablespace for that operation. Once we add space to that tablespace, those transactions will resume their operation.
    SQL> alter system set resumable_timeout=900 scope=both;
14. How will you do schema refresh without removing grants from target schema?
    SQL> select ‘truncate table scott.’||table_name’;’ from dba_tables where owner=’scott’;
    table_exist_action=append
15. How to export set of record from a table?
    By using query level EXP/ EXPDP
    SQL> exp file=query.dmp log=query.log tables=emp query=\”where sal between 1000 and 3000\”
16. A datapump export is running with parllel=2. Now you are asked to increase the parallel=4 from another terminal, how will you do it?
    => Control + C
    => It goes into interactive mode
    => Then add parallel=4
    => continuous_client
17. Having the DB size of 600GB. How will you take a database dumpfiles and copy that to another server. Explain the steps
    Steps:
    1. expdp directory=dir1 dumpfile=test_%U.dmp logfile=test.log full=y estimate=statistics parallel=6(depend upon CPU) filesize=100g
    2. GZIP then dump files then Using SCP we can copy that to another server

    3. scp *.dmp [email protected]:/home/oracle
    4. impdp directory=dir1 dumpfile=test_%U.dmp logfile=test.log full=y

18. Is it possible to do a schema copy to another db without dumpfile?
    Yes …!! => Using network_link Parameter
19. Explain the steps to transport tablespace?
    *Transport_Tablespace (Imp parameter)
    Transportable tablespaces and transportable tables transport data that resides in user-defined tablespaces. Use the transportable tablespaces feature to move a set of tablespaces between databases. Transporting Data Across Platforms. You can transport data across platforms.
20. What is the advantage of datapump over exp/imp?
    *Datapump advantages:
    It is 50 times faster than Exp/Imp
    It will run in server side
    we can start,stop and restart the job
    we can increase the performance by using parallel
    It will automatically create the user
    It is in block mode
    It has parallel execution