Oracle DBA interview Q&A in an Indian style

1- How can I get diagnostic information from a hung instance before I restart the database?

/u01/app/oracle/bin>sqlplus system/mypass -prelim /nolog

SQL*Plus: Release 10.2.0.2.0 - Production  

> oradebug setmypid

Statement processed.  

> oradebug hanganalyze 12

Hang Analysis in /u01/app/oracle/admin/mysid/udump/mysid_ora_1234.trc

========================================================================
2- How to enable tracing for a specific or current session?

In 11g:

For current: exec dbms_monitor.session_trace_enable(NULL, NULL);

For a specific: exec dbms_monitor.session_trace_enable(144, NULL); -- 144 is the SID number

10g

EXECUTE DBMS_SESSION.SESSION_TRACE_ENABLE(waits => TRUE,binds => TRUE);

========================================================================
3- How to copy schema into same database?

Using the impdp utility.

Let say we are copying SCOTT schema to SCOTT_NEW

SQL> create a directory TEST_DIR as ‘/xxxx/exp-dir/’;

SQL> create a database link SCOTT_DB_LINK connect to SYSTEM identified by  xxxxxxx using ‘DB-A’;

Verify the database link

SQL> select * from dual@SCOTT_DB_LINK;

Use the following syntax to copy schema,

impdp system/xxxxxx schemas=SCOTT directory=TEST_DIR network_link=SCOTT_DB_LINK  remap_schema=SCOTT:SCOTT_NEW logfile=TEST_DIR:SCOTT_NEW.log

This eliminates the creation of dump file, by using database link with the parameter network_link      

========================================================================
4- RAC to non-RAC cloning steps?

1- Run Preclone script on both Dbtier and AppsTier on the SOURCE system.
2- Create backup on SOURCE Databases to FILESYSTEM (/backup)
run {
allocate channel d1 type disk;
backup format '/backup/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/backup/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}

3- compress database ORACLE HOME on the source node, then move it to the
source node using GZIP utility as the following:
tar cvf – DB_Home | gzip > DB_Home.tar.gz

4- Copy the tar file of the Oracle HOME which has been taken in the previous step
to the target node and extract this file.

5- Copy the backup pieces which has been taken in step2 to the TARGET node, and in the
same location where the backup pieces has been taken in step 2 (è /backup)

6- Run adcfgclone.pl script on the TARGET db node using the following command:
perl adcfgclone.pl dbTechStack
This will create the init parameter file, context file and environment files on the target
node.

7- now on the TARGET node and as oracle user, create the directories where the
datafiles,onlinelogs and tempfiles are stored on your ASM storage:
SOURCE NODE TARGET NODE
+DATA1/prod/datafile >> $ mkdir $DATA_TOP/prod/datafile
+DATA1/prod/onlinelog >> $ mkdir $DATA_TOP/prod/onlinelog
+DATA1/prod/tempfile >> $ mkdir $DATA_TOP/prod/tempfile
Suppose that the DATA_TOP on the target node is /u01/app/CLONE/oradata

8- Change initialization parameters on the init file on the target instance:
db_file_name_convert =('+DATA1, '/u01/app/CLONE/oradata')
log_file_name_convert =('+DATA1', '/u01/app/CLONE/oradata')

9- now on the target node, startup the database in nomount mode

10- check that you can access the source database, by copying tnsEntries
from the SOURCE Node to the Target node tnsnames.ora file.
Step 11) on the target node, connect to the RMAN using the following command, where
PROD is your source database, and CLONE is the target database:
$ rman target sys/sys@PROD auxiliary /
RMAN>
run
{
allocate auxiliary channel ch1 type disk;
duplicate target database to CLONE;
release channel ch1;
}

12- After the RMAN command completed successfully, shutdown the database,
then change the following database initialization parameters, in init parameter file:
_no_recovery_through_resetlogs=TRUE
undo_management to =MANUAL
undo_tablespace=UNDOTBS1
Now open the database with RESETLOGS option;

13- Create APPS_UNDOTS1 tablespace using the following statements:
CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE
'/u01/app/CLONE/oradata/prod/datafile/undo01.dbf' SIZE 4000M AUTOEXTEND
ON NEXT 100M MAXSIZE 7000M
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;

14- now edit the init parameter file, and change the following initialization
parameters
undo_management to =AUTO
undo_tablespace= APPS_UNDOTS1
and remove the added initialization parameter_no_recovery_through_resetlogs=TRUE
Drop all unused undo tablespaces

15- Disable database archive on the TARGET database, and start the database in
open mode


========================================================================

Oracle database cross platform migration methods:
1. Export / Import to include the use of Datapump facilities. All versions support Export/Import but for Datapump 10.1.0.2 or higher is required
2. Transportable Tablespaces 10G or Later
3. RMAN Convert Database functions. 10G or Later
4. Streams Replication
5. Create Table As Select (CTAS)
6. Dataguard Heterogeneous Primary and Physical Standbys
7. Oracle Golden Gate (For assistance with Oracle Golden Gate, an SR needs opened with the correct team)

========================================================================

========================================================================