manual process of copy an Oracle database.

Here is a simple manual process of copy an Oracle database.

Ref: http://proora.com/oracle/copy_database

1. Copy $ORACLE_HOME/dbs/initSOURCE.ora to $ORACLE_HOME/dbs/initTARGET.ora

change in initTARGET.ora:

  db_name,

  control_files,

  user_dump_dest,

  background_dump_dest,

  core_dump_dest

and may be audit_file_dest,log_archive_dest

  Create the bdump, udump and cdump directories

2. Create the script that will re-create the controlfile

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/somedir/cre_controlfile.sql';

Remove everything up to the "START NOMOUNT" statement and

everything after the semicolon at the end of the "CREATE CONTROLFILE" statement.

Edit the line starting with "CREATE CONTROLFILE" and replace the word "REUSE" with the word "SET" right before the keyword DATABASE.

On the same line, modify the database name changing it from SOURCE to TARGET.

On the same line, change the keyword NORESETLOGS to RESETLOGS.

Change datafile where necessary.

remove blank lines and comments

STARTUP NOMOUNT PFILE='/dev_orahome/oracle/10.2.0/dbs/initTARGET.ora'

or

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "TARGET" RESETLOGS  NOARCHIVELOG

    MAXLOGFILES 64

    MAXLOGMEMBERS 4

    MAXDATAFILES 512

    MAXINSTANCES 4

    MAXLOGHISTORY 10452

LOGFILE

  GROUP 1 (

    '/target/redolog01a.dbf',

    '/target/redolog01b.dbf'

  ) SIZE 512M,

  GROUP 2 (

    '/target/redolog02a.dbf',

    '/target/redolog02b.dbf'

  ) SIZE 512M,

DATAFILE

  '/target/system01.dbf',

  '/target/undotbs01.dbf',

  '/target/users01.dbf',

  '/target/sysaux01.dbf'

;

3. Copy or FTP source database

Shutdown SOURCE

Copy data files and redo log files ( v$datafile, v$logfile )

Do not copy v$controlfile -- it will be created in next step

Do not copy v$tempfile -- it will be created in step 6

SOURCE could startup

4.Create the new controlfile for TARGET using cre_controlfile.sql

sqlplus /nolog

connect / as sysdba

STARTUP NOMOUNT

@/somedir/cre_controlfile.sql

Control file created.

5.Open the TARGET database

--alter database recover database until ccanccel using backup controlfile;

--alter database recover cancel;

alter database open resetlogs;

6. Check the source and create temp files

select substr(name,1,40),BYTES from v$tempfile;

SUBSTR(NAME,1,40)                             BYTES

----------------------------------------  -----------

/source/temp01.dbf                       2147483648

ALTER TABLESPACE temp ADD TEMPFILE '/target/temp01.dbf' size 2G;

Oracle Performance Issues - Basic Checks

REF:

http://www.arunsankar.in/2013/06/performance-issue-basic-checks.html?m=1

Oracle Performance Issues - Basic Checks

What are the basic checks for a junior DBA when slowness reports in the system?

Taking user inputs:

This is the very first step for troubleshooting any performance issues, get user inputs.You may use the below tips for this.

• Is application is slow or any particular batch processing is slow?
• Slowness is observed through out the system or only few or one user
• Is it happening in some particular timing ?
• Is it slow right now?

By collecting these information we will get an outline of what needs to be checked.

Now login to system and start investigation.

Check the resource utilization:

You can check the CPU,Load,Memory utilization, use top or topas command in unix.
Check any single process is holding the CPU for long time -- note the process ID.
Press 'c' in top command, it will give you the time and process which is consuming more CPU.

Check the alert log:

First check the alert log for any error and note the error or abnormalities if any. You can check how many log switches are happening in one hour. If you have more tan 5 archives per hour we can say you may need to increase the redo log size.Trouble shoot the errors if it s critical or related to performance.

Check the Database:

Loggin to database and see any lock contention in database.You can use the below query for this.

SQL> select count(*) from v$lock where block=1;

If count is greater than one, lock is there in database.Check with application team and release the blocking sessions (Refer my Blocking Sessions in Oracle post)

CPU Intensive Queries

You can find out the sql query details using the below by taking the process id from top command.

select sql_text,a.sid,a.serial# from v$sqlarea c,v$session a,v$process b where a.paddr=b.addr and a.sql_address=c.address and b.spid=&pid;

Check with application team whether these are ad-hock queries or regular, disconnect the high CPU queries if possible.

Send these query details to application team for tuning.

Below query is an another way to find out high CPU quries:

select ss.username,se.SID,VALUE/100 cpu_usage_seconds
from v$session ss, v$sesstat se,v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID and ss.status='ACTIVE'
and ss.username is not null
order by VALUE desc;

Resource intensive Queries 

Find out the resource intensive queries and share the details with application team.

col usr for a10
set lines 500
select
s.username usr,s.module,logon_time,status,
m.session_id sid,
m.session_serial_num ssn,
round(m.cpu) cpu100, --- CPU usage 100th sec
m.physical_reads prds, --- Number of physical read
m.logical_reads, --- Number of logical reads
m.pga_memory, --- PGA size at the end of the intervel
m.physical_read_pct prp,
m.logical_read_pct lrp,
s.sql_id
from v$sessmetric m,v$session s
where (m.physical_reads >100
or m.cpu>100
or m.logical_reads>10000)
and m.session_id=s.sid
and m.session_serial_num=s.serial#
and s.type='USER'
order by m.physical_reads DESC,m.cpu desc,m.logical_reads desc;

Stale stats tables check 

Check any critical table statistics became stale

select count(*),owner from dba_tab_statistics where stale_stats='YES';

Make sure that no highly accessed tables are in stale stats and gather the stats if any.

Sample script for stats gathering:

execute dbms_stats.gather_table_stats(ownname => '<OWNER>', tabname  =>'<TABLE NAME>', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', degree => 2, cascade => TRUE);

Single user session or batch slowness:

Find out the sessions for the user and enable the trace

Enabling

alter session set tracefile_identifier=ARUN_NEW;

EXECUTE dbms_system.set_sql_trace_in_session (109,18512,TRUE);

This will generate the trace in udump

Disabling

EXECUTE dbms_system.set_sql_trace_in_session (707,49158,FALSE);

Go to trace location:(udump)

tkprof trace_file.ora trace_out.txt 

Analyse the report or share with application team

Server side checks

Check for the memory,paging, IO utilization from server side.

Paging and memory can be checked by top command and iostat will do the io statistics.

Contact the concern team for any abnormality if you see in this.

Advance performance tuning is not the scope of this blog, this is for junior DBA. Hope it helps