JC Jaffray-Architect/Oracle DBA - Tips

Data Modeling - Logical & Physical design

2011-03-14T09:18:00.000-07:00

We use Erwin data modeler to create data dictionary , then we create a main data Model representing the logical world , It include functional, process flow and all other info regarding the business and does satisfy the business requirement.

Then we validate the main model with stake holder and data owners, once that's done , it is time to generate the final Data Dictionary including field definitions and comments.

Next phase is to create an Oracle Schema owner to hold the Physical representation of the model.

Sql*loader, Microsoft Access, Excel could be used to test some data, from Excel to Oracle we could use some third party tools to load the tables.

The logical model could be divided into many physical models, such as representation for operational data store and final Data Warehouse System.

The continuos collaboration with front end developers and team lead is essential to project success.

Killing a session in Oracle RAC 11G

2011-03-09T10:23:00.000-08:00

Oracle has added a very useful option in 11gR2 RAC to kill a session on instance B from instance 1. They have added instance name as a parameter in kill session syntax
See following example
sys@ARAC> select username ,status,inst_id from gv$session where username='REMEDY';
USERNAME STATUS INST_ID
------------------------------ -------- ----------
REMEDY INACTIVE 2
sys@ARAC> alter system kill session '787,1113,@2' immediate;
System altered.
sys@ARAC> select username ,status,inst_id from gv$session where username='REMEDY';
no rows selected

Speed up Data Load

2009-02-04T12:32:00.000-08:00

To accelerate processing, for example for quick data load or improved query you might try this:

- Put your table in parallel mode.
the operation run in parallel also this suggest your Hardware has multiple cpu for example 4 CPU's.

For this sample the chosen table name is CAR:

create table car(
car_id char(4),
make varchar2(18),
year char(4),
price number)

parallel 4;

alter table car
add constraint xpkcar primary key (car_id);
this just create the primary key. in compatible sql,

In Oracle sqlplus syntax you might just write:
--
create table car(
car_id char(4) primary key,
make varchar2(18),
year char(4),
price number)

parallel 4;

Or if the table is already created just alter it.

SQL> alter table CAR parallel 4;
SQL> alter session enable parallel DML;
DML is for Data manipulation such as insert, update, delete , if you are creating lots of object substitue DML by DDL.

SQL> alter session enable parallel DDL;
You might also force it.
SQL> alter session force parallel DDL;

I read somewhere that insert with syntax of insert into table values (A,B,C,..) does not work on parallel so I will check on it and will eventually post it.

You also might use hint such as
insert /*+ parallel (CAR) */
--

--
Need Oracle consultant contact us at service@jaffrayconsulting.com

Flash recovery Area

2008-10-02T08:59:00.000-07:00

This is from my older site oraclemiracledba , that I thought might still be useful here.

What is the Flash Recovery Area ?

The Flash Recovery Area is an area for storing certain types of files on disk. These files typically consist of flashback database logs and RMAN related files. It can be a regular file system or an ASM group. Oracle does NOT need to use a Flash Recovery Area to operate the database. It is recommended for easy of use and simplification for Backup and Recovery purposes and the associated space management. By placing files in the Flash Recovery Area, Oracle will manage the space usage, deleting files that are no longer required, when the space is required. However, if space is not required, Oracle will maintain the files on disk (as a form of cache for files backed up to tape).

Components of the Flash Recovery Area

The flash recover area can contain permanent or transient files. It can contain any or all of the following types of files:

Multiplexed copy of control file.
Multiplexed copy of online redo log.
Archived redo log files
RMAN backup sets, backup copies, etc.
Flashback logs
Of these files ONLY the flashback logs are required to be stored in the flash recovery area.

Configure Flash Recovery Area

Configuring the Flash Recovery Area is simple. Once the disk file system is mounted, or the ASM group created, all that is required is setting is to configure it by setting the two init.ora parameters related to the configuration :

· DB_RECOVERY_FILE_DEST_SIZE

· DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE specifies the total size of all files that can be stored in the Flash Recovery Area. This value must be set first. It is NOT the actual size available, but the space which Oracle will use to limit the space of files in the Flash Recovery Area.

DB_RECOVERY_FILE_DEST specifies the physical disk location where the Flash Recovery Area will be stored. The Flash Recovery Area can be stored on a regular file system or can be an ASM group. As the files in the FRA are members of the redundancy set ( backups, archived redo logs and flashback logs), they should be stored in a separate physical location from the database operating components (data files, online redo).

Enable the Flash Recovery Area

To enable the Flash Recovery Area, either restart the database with the related parameters set, or issue the following statements:

ALTER SYSTEM SET db_recovery_file_dest_size = ‘1G’ SCOPE=BOTH SID=’*’;

ALTER SYSTEM SET db_recovery_file_dest=’/u06/bkup/flash_recovery_area’;

ALTER SYSTEM SET db_flashback_retention_target=1440;

Data Pump

2008-09-23T13:36:00.000-07:00

Some day-to-day DBA tips:
DATA LOAD & IMPORT
Well I recommend starting using Data Pump and using import, exporting utilities just for backward compatibilities, or for structure usages only (create user, grants, etc..).
Now I answer another question.
Question: In the case you imported an existing schemas to a new schemas in the same database or another database instance, if these tables and indexes are created in the wrong tablespace, what to do??
Answer: For indexes it is very easy, you should not even bother modifying the “create script”, just create it where ever it is, then move them to the right location using
Alter index index_name rebuild new_tablespace_name
For tables,
If the original tablespace does not exit, the user have quota on a specific tablespace let’s call it APP_DATA, the tables will be created in user’s default tablespace APP_DATA, BUT If the original tablespace exit in the database (in this case OLD_DATA) then the tables will be imported to old_data, so in this case it will be need to modify the “creation script” and then pre-create all the tables and then move, import or load the data.

Question : Regarding the imp utilities and data pump utilities for Oracle 10g & 11g.
In some case the import is very slow and hang, this might happen if the tables are very large, I usually recommend the use of data pump rather than import, export.
Also have the adequate memory, increae the PGA if not set correctly, go to my memory setting section in this BLOG for memory tuning.
Also have the tablespace files for target in auto extend mode, locally managed.
Also use parallel exuction parameter (parfile), set status =10 (10- second ) to se the progress.
Also alter the table you are going to import to.
Alter table table_name parallel degree 4 ( 4 or any other number related to number of cpu’s).
Make sure you have granted the read and write.
SQL> grant read, write on directory export_dir to expuser, jaffray, userx, userx;
Also you must have EXP_FULL_DATABASE role.
If you get the utl.file error some thing like this:
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

The export_dir has or might have issues, it is not writing to the right place,
Check it out by:
select directory_name, directory_path from dba_directories;
Drop directory export_dir;
Create directory export_dir as ‘/u01/app/oracle/export’;
Grant read, write to export_dir

To be continued……

Ethic & Business

2008-09-19T08:01:00.000-07:00

The right Oracle project management takes the guesswork out of your system!
It is not enough to have some good Database administrators or system admin in one place; then many developers or thousand of users in another place, then some middle manager in exactly the middle, that is just called an average project with not so average problems; but very serious issues.
In my past experience for about 28 years in IT, I have worked as consultant for at least 30 large and medium size businesses all over the world, in private as well as in public sector, Very often I came to realize that all the high- end solutions, best practices, Use of right technology it might be just another sales pitch by somebody or a group of coordinated people in some managerial position or technical position which are just fighting for the job and not for overall interest of the organization.
So the dilemma now for a real professional is not only to do his job the best way possible, but also how to reconcile that with internal politics and all the non-technical constraints, all without getting in middle of the mess or becoming a target.
That’s not always happening, because people might just decide to go for the easy way out, They do not implement the best solution, every body is happy, but that is not in long term interest of any business or organization, so ethic and professionalism should be always part of every body job, we do not want to copy wall street and all it’s failure and self-indulgence, IT professional are not in this only for the money.

The expertise is seeing the big pictures, the knowing of all side of Database solutions, the correct (business process, the data model, data, OS/Hardware) in respect to cost effectiveness and implementation ease.
The most complicated and advanced technologies are not always the best solution for the customer, what good it can make if they could not maintain or use it correctly.
Taking good care of customers is simply good business model; it creates long-term fruitful relationship.

Memory Management Oracle 11G - Overview

2008-07-09T13:35:00.000-07:00

This is a quick overview, for situation when there is no time left reading document, it just need to be fixed then reviewed later on.
Two-memory structure A+B

A include:
1-DB_CACHE_SEIZE important, sample value 1Giga
2-JAVA_POOL_SEIZE 32 M
3-LARGE_POOL_SEIZE 16M
4-SHARED_POOL_SEIZE important, 2Giga

SGA_TARGET = 1+2+3+4 (So any of these 1,2,... if increased , that amount should be added to SGA_TARGET

B include
PGA_AGGREGATE_TARGET very important, 10Giga
Total memory is called memory target

MEMORY_TARGET= A + B

Also Oracle 11g provide automatic memory management and through enterprise manager memory adviser is good tool to monitor memory.

I will append to this article, this is not complete...

Export and Pipe

2008-07-08T09:04:00.000-07:00

Export and Pipe (To compress at the same Time!)
Step 1,2,3,4
1- is creating the usual parfile with filename=file.exp inside parfile
2- is creating the pipe
3- is compressing
4- is exporting

1- Create parfile parf ( with what you want to export parameters)
2- mknod file.exp p
3- gzip <file.exp> fullfile.exp.gz &

4- exp parfile=parf

file.exp is your export file name ( as scott.dmp, tab.exp, etc..)

Cloning of Agent

2008-07-08T08:36:00.000-07:00

This is an example on how to clone agent, Install it on all other nodes that you need to manage.
******************************************

tar -cvf agent10g_bin.tar agent10g

scp agent10g_bin.tar.gz /Dest-Server/u01/oracle

cd /u01/oracle gunzip agent10g_bin.tar.gz

tar -xvf agent10g_bin.tar

cd /u01/oracle/agent10g/oui/bin ./runInstaller -clone -forceClone ORACLE_HOME=/u01/oracle/agent10g ORACLE_HOME_NAME=agent10g -noconfig -silent

/u01/oracle/agent10g/root.sh

vi $AGENT_HOME/sysman/config/emd.properties --->as described in previous post
/u01/oracle/agent10g/bin/agentca -f

/u01/oracle/agent10g/bin/emctl secure agent

Changing Ports for Em agent..

2008-07-04T09:39:00.000-07:00

There may be times when we have to change the port for EM agents.
Use the following to do so.
Stop the Management Agent.

Locate the emd.properties file in the Agent home directory:
AGENT_HOME/sysman/config/emd.properties
Use a text editor to open the file and locate the EMD_URL property.
For example:
EMD_URL=http://managed_host1.acme.com:1813/emd/main
Modify the port number in the EMD_URL property so the Agent uses a new unused port on the managed host.
For example:
EMD_URL=http://managed_host1.acme.com:1913/emd/main
Start the Management Agent.

You can verify that with checking the status of the agent.
emctl status agent

RMAN Catalog - Recovery manager

2008-06-26T11:08:00.000-07:00

Quick RMAN Catalog Setup....
Enjoy.....

SQL> create tablespace rman_tbs
datafile '/u01/test/rman_01.dbf' size 200M;

Tablespace created.

SQL> create user rman identified by pass123
temporary tablespace temp
default tablespace rman_tbs
quota unlimited on rman_tbs;

User created.

SQL> grant recovery_catalog_owner,connect,resource to rman;

Grant succeeded.

SQL> EXIT

$ rman catalog rman@test

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 12 11:21:00 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

recovery catalog database Password:
connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

exit
$

$ rman target / catalog rman@test

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 12 11:29:33 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: TEST (DBID=3068100200)
recovery catalog database Password:
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> show all;

starting full resync of recovery catalog
full resync complete
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u07/rmanbk/test/%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u07/rmanbk/test/%U' MAXPIECESIZE 100 G;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' MAXPIECESIZE 100 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/app/oracle/product/10gR2/dbs/snapcf_test.f'; # default

RMAN>

RMAN Duplicate Database

2008-06-24T12:36:00.000-07:00

RMAN duplicate standby
I think this is the good one, I will check into it to see if I have a better one, so for now enjoy, it might be of great help.

The correct backup of primary database have been performed using

RMAN> configure channel device type disk format '/u03/rman/bkup/rmanbk_%d%s%t';

new RMAN configuration parameters:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u03/rman/bkup/rmanbk_%d%s%t';

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> configure device type disk parallelism 2;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> backup database include current controlfile for standby archivelog all;

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1.5032E+10 bytes

Fixed Size 2203608 bytes

Variable Size 2006736936 bytes

Database Buffers 1.3019E+10 bytes

Redo Buffers 4325376 bytes

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition
RMAN duplicate standby

The correct backup of primary database have been performed using

RMAN> configure channel device type disk format '/u03/rman/bkup/rmanbk_%d%s%t';

new RMAN configuration parameters:

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u03/rman/bkup/rmanbk_%d%s%t';

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> configure device type disk parallelism 2;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

starting full resync of recovery catalog

full resync complete

RMAN> backup database include current controlfile for standby archivelog all;

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1.5032E+10 bytes

Fixed Size 2203608 bytes

Variable Size 2006736936 bytes

Database Buffers 1.3019E+10 bytes

Redo Buffers 4325376 bytes

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

[oracle@testbox /u03/rman/bkup]$ rman auxiliary / target sys@test1

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Nov 20 11:52:29 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

target database Password:

connected to target database: TEST1 (DBID=4550100)

connected to auxiliary database: TEST1 (not mounted)

RMAN> duplicate target database for standby nofilenamecheck dorecover;

Starting Duplicate Db at 20-NOV-07

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=169 devtype=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: sid=45 devtype=DISK

contents of Memory Script:

{

set until scn 1232983;

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

executing command: SET until clause

Starting restore at 20-NOV-07

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: restoring control file

ORA-19600: input file is control file (/u01/standby.ctl)
ORA-19601: output file is control file (/u01/oradata/test2/control01.ctl)
failover to previous backup

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u03/rman/bkup/rmanbk_TEST14638548937

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u03/rman/bkup/rmanbk_TEST14638548937 tag=TAG20071113Txxxx

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output filename=/u01/oradata/test2/control01.ctl

output filename=/u02/oradata/test2/control02.ctl

output filename=/u03/oradata/test2/control03.ctl

Finished restore at 20-NOV-07

sql statement: alter database mount standby database

released channel: ORA_AUX_DISK_1

released channel: ORA_AUX_DISK_2

contents of Memory Script:

{

set until scn 1232983;

set newname for tempfile 1 to

"/u01/oradata/test1/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/u03/oradata/test1/system01.dbf";

set newname for datafile 2 to

"/u04/oradata/test1/undotbs01.dbf";

set newname for datafile 3 to

"/u03/oradata/test1/sysaux01.dbf";

set newname for datafile 4 to

"/u01/oradata/test1/users01.dbf";

set newname for datafile 5 to

"/u03/oradata/test1/rman_tbs.dbf";

restore

check readonly

clone database

;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to /u01/oradata/test1/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-NOV-07

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=45 devtype=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: sid=104 devtype=DISK

skipping datafile 4; already restored to file /u01/oradata/test1/users01.dbf

skipping datafile 5; already restored to file /u03/oradata/test1/rman_tbs.dbf

skipping datafile 1; already restored to file /u03/oradata/test1/system01.dbf

skipping datafile 2; already restored to file /u04/oradata/test1/undotbs01.dbf

skipping datafile 3; already restored to file /u03/oradata/test1/sysaux01.dbf

restore not done; all files readonly, offline, or already restored

Finished restore at 20-NOV-07

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=1 stamp=639143579 filename=/u03/oradata/test1/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=2 stamp=639143579 filename=/u04/oradata/test1/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=3 stamp=639143580 filename=/u03/oradata/test1/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=4 stamp=639143580 filename=/u01/oradata/test1/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=5 stamp=639143580 filename=/u03/oradata/test1/rman_tbs.dbf

contents of Memory Script:

{

set until scn 1232983;

recover

standby

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 20-NOV-07

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

starting media recovery

archive log thread 1 sequence 36 is already on disk as file /u04/arch1/test1/1_36_637945588.arc

archive log thread 1 sequence 37 is already on disk as file /u04/arch1/test1/1_37_637945588.arc

archive log thread 1 sequence 38 is already on disk as file /u04/arch1/test1/1_38_637945588.arc

archive log filename=/u04/arch1/test1/1_36_637945588.arc thread=1 sequence=36

archive log filename=/u04/arch1/test1/1_37_637945588.arc thread=1 sequence=37

archive log filename=/u04/arch1/test1/1_38_637945588.arc thread=1 sequence=38

channel ORA_AUX_DISK_1: starting archive log restore to default destination

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=39

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=40

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=41

channel ORA_AUX_DISK_1: reading from backup piece /u03/rman/bkup/rmanbk_TEST111639142479

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u03/rman/bkup/rmanbk_TEST111639142479 tag=TAG20071120T113439

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08

archive log filename=/opt/app/oracle/product/10.2.0/dbs/arch1_39_637945588.arc thread=1 sequence=39

channel clone_default: deleting archive log(s)

archive log filename=/opt/app/oracle/product/10.2.0/dbs/arch1_39_637945588.arc recid=1 stamp=639143604

archive log filename=/opt/app/oracle/product/10.2.0/dbs/arch1_40_637945588.arc thread=1 sequence=40

channel clone_default: deleting archive log(s)

archive log filename=/opt/app/oracle/product/10.2.0/dbs/arch1_40_637945588.arc recid=3 stamp=639143607

archive log filename=/opt/app/oracle/product/10.2.0/dbs/arch1_41_637945588.arc thread=1 sequence=41

channel clone_default: deleting archive log(s)

archive log filename=/opt/app/oracle/product/10.2.0/dbs/arch1_41_637945588.arc recid=2 stamp=639143606

media recovery complete, elapsed time: 00:00:04

Finished recover at 20-NOV-07

Finished Duplicate Db at 20-NOV-07

RMAN>

Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

[oracle@testbox /u03/rman/bkup]$ rman auxiliary / target sys@test1

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Nov 20 11:52:29 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

target database Password:

connected to target database: TEST1 (DBID=4550100)

connected to auxiliary database: TEST1 (not mounted)

RMAN> duplicate target database for standby nofilenamecheck dorecover;

Starting Duplicate Db at 20-NOV-07

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=169 devtype=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: sid=45 devtype=DISK

contents of Memory Script:

{

set until scn 1232983;

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

executing command: SET until clause

Starting restore at 20-NOV-07

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: restoring control file

ORA-19600: input file is control file (/u01/standby.ctl)
ORA-19601: output file is control file (/u01/oradata/test2/control01.ctl)
failover to previous backup

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u03/rman/bkup/rmanbk_TEST14638548937

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u03/rman/bkup/rmanbk_TEST14638548937 tag=TAG20071113xxxxx

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output filename=/u01/oradata/test2/control01.ctl

output filename=/u02/oradata/test2/control02.ctl

output filename=/u03/oradata/test2/control03.ctl

Finished restore at 20-NOV-07

sql statement: alter database mount standby database

released channel: ORA_AUX_DISK_1

released channel: ORA_AUX_DISK_2

contents of Memory Script:

{

set until scn 1232983;

set newname for tempfile 1 to

"/u01/oradata/test1/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/u03/oradata/test1/system01.dbf";

set newname for datafile 2 to

"/u04/oradata/test1/undotbs01.dbf";

set newname for datafile 3 to

"/u03/oradata/test1/sysaux01.dbf";

set newname for datafile 4 to

"/u01/oradata/test1/users01.dbf";

set newname for datafile 5 to

"/u03/oradata/test1/rman_tbs.dbf";

restore

check readonly

clone database

;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed temporary file 1 to /u01/oradata/test1/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-NOV-07

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=45 devtype=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: sid=104 devtype=DISK

skipping datafile 4; already restored to file /u01/oradata/test1/users01.dbf

skipping datafile 5; already restored to file /u03/oradata/test1/rman_tbs.dbf

skipping datafile 1; already restored to file /u03/oradata/test1/system01.dbf

skipping datafile 2; already restored to file /u04/oradata/test1/undotbs01.dbf

skipping datafile 3; already restored to file /u03/oradata/test1/sysaux01.dbf

restore not done; all files readonly, offline, or already restored

Finished restore at 20-NOV-07

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=1 stamp=639143579 filename=/u03/oradata/test1/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=2 stamp=639143579 filename=/u04/oradata/test1/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=3 stamp=639143580 filename=/u03/oradata/test1/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=4 stamp=639143580 filename=/u01/oradata/test1/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=5 stamp=639143580 filename=/u03/oradata/test1/rman_tbs.dbf

contents of Memory Script:

{

set until scn 1232983;

recover

standby

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 20-NOV-07

using channel ORA_AUX_DISK_1

using channel ORA_AUX_DISK_2

starting media recovery

archive log thread 1 sequence 36 is already on disk as file /u04/arch1/test1/1_36_637945588.arc

archive log thread 1 sequence 37 is already on disk as file /u04/arch1/test1/1_37_637945588.arc

archive log thread 1 sequence 38 is already on disk as file /u04/arch1/test1/1_38_637945588.arc

archive log filename=/u04/arch1/test1/1_36_637945588.arc thread=1 sequence=36

archive log filename=/u04/arch1/test1/1_37_637945588.arc thread=1 sequence=37

archive log filename=/u04/arch1/test1/1_38_637945588.arc thread=1 sequence=38

channel ORA_AUX_DISK_1: starting archive log restore to default destination

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=39

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=40

channel ORA_AUX_DISK_1: restoring archive log

archive log thread=1 sequence=41

channel ORA_AUX_DISK_1: reading from backup piece /u03/rman/bkup/rmanbk_TEST111639142479

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/u03/rman/bkup/rmanbk_TEST111639142479 tag=TAG20071120Txxxx

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08

archive log filename=/opt/app/oracle/product/10.2.0/dbs/arch1_39_637945588.arc thread=1 sequence=39

channel clone_default: deleting archive log(s)

archive log filename=/opt/app/oracle/product/10.2.0/dbs/arch1_39_637945588.arc recid=1 stamp=639143604

archive log filename=/opt/app/oracle/product/10.2.0/dbs/arch1_40_637945588.arc thread=1 sequence=40

channel clone_default: deleting archive log(s)

archive log filename=/opt/app/oracle/product/10.2.0/dbs/arch1_40_637945588.arc recid=3 stamp=639143607

archive log filename=/opt/app/oracle/product/10.2.0/dbs/arch1_41_637945588.arc thread=1 sequence=41

channel clone_default: deleting archive log(s)

archive log filename=/opt/app/oracle/product/10.2.0/dbs/arch1_41_637945588.arc recid=2 stamp=639143606

media recovery complete, elapsed time: 00:00:04

Finished recover at 20-NOV-07

Finished Duplicate Db at 20-NOV-07

RMAN>

Data Guard Hints

2008-06-23T13:29:00.000-07:00

BUILD:
Have a template ready for the database, check the /u01,/u02 , etc..
Should have same exact Structure on both servers, pre-create all necessary directories including OACLE_ADMIN/sid/audump, bdump, cdump, and so on.
Create the pfile for primary site (the sid =prod) DB_unique_name = prod_pri
Create the pfile for secondary site (the sid=prod) , exact same sid , DB_unique_name = prod_SBY (create spfile from pfile on both sites).
Create password file with user sys identic on both site.
Create exact same directories structure on the secondary site.
Create database prod using dbca on the secondary side.
Shutdown the primary and Startup mount; alter db crate controlfile for standby as '/tmp/stndby.ctl'.
Move the standby controlfile to secondary site /tmp.
Alter database add standby redolog…3 groups.(on the primary, if not already there)
Create rman account and catalog on primary (optional, rman can use controlfile too).
Configure rman channel, disk location ( /u04/rman/bkup), etc…
Do full RMAN backup including all logfiles+ controlfile for standby
Scp all the RMAN files to the secondary site
On the secondary site
Connect to target (check the sbid and be careful to connect to right DB)
Connect to auxiliary /
Run a duplicate database command with standby option, it should be successful (other wise standby database will not work), should not get any tablespace or media recovery error.
If issue with RMAN (manually copy all db files, control files, redo and archived ones to secondary)
Modify network files tnsnames.ora and listener.ora , reload listener on secondary

TEST:
Test order:
On the primary crate table and insert into it, wait few minutes
Archive log list to see the log seq number

1- startup mount (not needed is already mounted)
2- alter database recover managed database disconnect from session; should not get any errors
3- archive log list, see the sequences , should be the same last seq number, if not wait few minutes
4- alter database recover managed database cancel; should not get any error.
5- Alter database open read only; should not get any error.
6- Query the table, should see same as on the other site.
7- Go back to: 2 (disconnect from session) if error go back to step 1 , then (disconnect from session).
8- On the seconday standby side always check for mrp , do pps mrp , you should see ora_mrp0_sid
9- If not able to alter database recover managed database disconnect from session then shutdown, startup nomount, alter database mount; then do alter database recover managed database disconnect from session ; this should work fine.

Switch over:
P is current primary (is currently open for all operations)
1- from P , shutdown and startup (this is to disconnect all connections)
2- from S, alter database recover managed standby database cancel;
3- from P, alter system set log_archive_dest_state_2=defer;
4- from P, alter database commit to switchover to physical standby with session shutdown;
a. shutdown immediate
b. startup nomount
c. alter database mount (standby database);
d. check v$database and v$instance and archivelog list
5- go to standby site
6- recover managed standby database cancel;
7- alter database recover managed standby database cancel;
8- alter database commit to switchover to primary with session shutdown;
9- check the v$database and v$instance
10- Shutdown immediate
11- startup
12- alter system set log_archive_dest_state_2=enable;

go back to P,
alter database recover managed database disconnect from session ;
this should work fine., this enable the mrp0-sid process on the now standby side server.
goto TEST section and verify.

Caution : All materials are provided as research paper , we do not accept any reliability or responsibility in any case.
Use it at your own risks.

DBA Tips - Main Page

2008-06-22T13:05:00.000-07:00

Jay and myself(JC.) We have more than 50 years of Hands on Oracle Database Administration.
Disaster and recovery strategy, Performance Tuning, Data Guard, RAC and clustering, Oracle DBA 101, Best Practice database administration, and more....
If you are in process of creating new DBA group for your organization with large scale database system, We might be able to help you , With not to do things......