Monday, October 14, 2013

Duplicate Database using RMAN backup without connecting to target for oracle 10g version on windows

PURPOSE:
Duplicate oracle 10g database in new server for testing without connecting to target.

STEPS:

STEP 1. make a backup

1.1 backup database
rman target /
run
{
allocate channel c1 type disk format 'F:\rman\rman_db_%d_%T_%U.rman';
backup as compressed backupset full database;
release channel c1;
}

1.2 backup controlfile
rman target /
run
{
allocate channel c1 type disk format 'F:\rman\rman_ctl_%d_%T_%U.rman';
backup as compressed backupset current controlfile;
release channel c1;
}

1.3 backup required archive logs
run
{
allocate channel c1 type disk format 'F:\rman_test\rman_arch_%d_%T_%U.rman';
backup archivelog sequence between 100 and 200 ;
release channel c1;
}

STEP 2. Create required directory for new database in new host

2.1 data file, log file and tempfile location
select name from v$datafile;
select member from v$logfile;
select name from v$tempfile;

mkdir D:ORACLE\ORADATA\TEST

2.2 other locations ( dump dest and archivelog dest)
show parameter dest

STEP 3. copy pfile and make modification

STEP 4. create windows service

radim -new -sid test -intpwd test

STEP 5. start instance

startup mount

STEP 6. restore control file and mount database

rman target /
set dbid=123456789;
restore controlfile from 'F:\rman\RMAN_ctl_test_20131011_95OM4JNU_1_1.RMAN';
alter database mount;

STEP 7. restore database from backup
new location need be specified by using "set new name"
catalog start with 'F:\rman'

rman target /
run
{
set newname for datafile 1 to 'D:\ORACLE\ORADATA\TEST1\SYSTEM01.DBF';
set newname for datafile 2 to 'D:\ORACLE\ORADATA\TEST1\UNDOTBS01.DBF';
set newname for datafile 3 to 'D:\ORACLE\ORADATA\TEST1\SYSAUX01.DBF';
set newname for datafile 4 to 'D:\ORACLE\ORADATA\TEST1\USERS01.DBF';
allocate channel c1 type disk format 'F:\rman\\RMAN_DB_%d_%T_%U.RMAN';
restore database  ;
release channel c1;
}

STEP 8. rename datafile and logfile
alter database rename file 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF' to  'D:\ORACLE\ORADATA\TEST1\SYSTEM01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF' to 'D:\ORACLE\ORADATA\TEST1\UNDOTBS01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF' to  'D:\ORACLE\ORADATA\TEST1\SYSAUX01.DBF';
alter database rename file 'D:\ORACLE\ORADATA\TEST\USERS01.DBF' to   'D:\ORACLE\ORADATA\TEST1\USERS01.DBF';

alter database rename file 'D:\ORACLE\ORADATA\TEST\REDO01.LOG' to 'D:\ORACLE\ORADATA\TEST1\REDO01.LOG';
alter database rename file 'D:\ORACLE\ORADATA\TEST\REDO02.LOG' to 'D:\ORACLE\ORADATA\TEST1\REDO02.LOG';
alter database rename file 'D:\ORACLE\ORADATA\TEST\REDO03.LOG' to 'D:\ORACLE\ORADATA\TEST1\REDO03.LOG';
alter database rename file 'E:\ORACLE\ORADATA\TEST\REDO01B.LOG' to 'E:\ORACLE\ORADATA\TEST1\REDO01B.LOG';
alter database rename file 'E:\ORACLE\ORADATA\TEST\REDO02B.LOG' to 'E:\ORACLE\ORADATA\TEST1\REDO02B.LOG';
alter database rename file 'E:\ORACLE\ORADATA\TEST\REDO03B.LOG' to 'E:\ORACLE\ORADATA\TEST1\REDO03B.LOG';


STEP 9. recover database to consistent state
rman target /
recover database until sequence 201;

STEP 10. open database with resetlogs and drop, recreate temp file
alter database open resetlogs;
alter tablespace temp add tempfile 'D:\ORACLE\ORADATA\TEST1\TEMP01.DBF' size 3G ;

ALTER DATABASE TEMPFILE 'D:\ORACLE\ORADATA\TEST\TEMP01.DBF' DROP INCLUDING DATAFILES;

STEP 11. set up network connection
sqlplus sys/test@test as sysdba

STEP 12. copy pfile of test to pfile of test1
chagne db name from test to test1

STEP 13. rename database
orapwd FILE=pwdtest.ora PASSWORD=test
startup mount
nid TARGET=SYS/test@test DBNAME=test1

STEP 14. set oracle_sid as test1 and open database with resetlogs
startup mount
alter database open restlogs;


REFERENCE:
http://dbaregistry.blogspot.com/2011/04/rman-duplicate-without-connecting-to.html
http://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm
http://docs.oracle.com/cd/B14117_01/server.101/b10825/dbnewid.htm
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupad.htm
http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmdupdb.htm
https://forums.oracle.com/thread/2421213
http://www.dba-oracle.com/t_rename_database_oracle_sid.htm
http://docs.oracle.com/html/B13831_01/admin.htm
http://docs.oracle.com/cd/B19306_01/server.102/b14231/dfiles.htm
http://ss64.com/ora/rman_format_string.html
http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams112.htm
http://linuxtechres.blogspot.com/2011/10/how-to-create-oracle-database-service.html

Thursday, October 10, 2013

TNS-12518, TNS-12540, TNS-12582, TNS-12615 Listener ERROR caused by swingbench OLTP test

SYMPTOM:
In listener.log file, below error emssage appear.

TNS-12518: TNS:listener could not hand off client connection
 TNS-12540: TNS:internal limit restriction exceeded
09-OCT-2013 13:46:31 * establish * 12540
TNS-12540: TNS:internal limit restriction exceeded
09-OCT-2013 13:46:31 * 12582
TNS-12582: TNS:invalid operation
 TNS-12615: TNS:preempt error

CAUSE:
Too many concurrent connection requests are received by listener.

Solution:

Increase the queuesize for listener. There is no more TNS error in log file.

listener_name=
 (DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=hr-server)(PORT=1521)(QUEUESIZE=1000)))

TRACE LISTERNER in 11g:

TRACE_LEVLE_LISTENER_NAME=16 ( 0, no trace; 4, user; 10, admin; 16, support)

OTHER THOUGHT:
There is article (1399677.1) on metalink for the same set of errors. That is a bug cause by ADR check.
work around is to set _diag_adr_enabled=FALSE and/or apply Patch:9700115.
It look related to Unpublished Bug:9700115

Reference:
http://docs.oracle.com/cd/B28359_01/network.111/b28317/listener.htm
https://forums.oracle.com/thread/2520109

Thursday, October 3, 2013

create table with only data using data pump

REQUIREMENT:
Snapshot of data in schema need be stored for comparison. Data snapshot is refreshed from time to time.

EASY SOLUTION:

1. create new schema
create user test1 identified by test;
grant connect to test1;

2. data pump schema including only table
expdp userid=system/test@test schemas=test include=table dumpfile=test_table_2013_10_01.dump logfile=test_table_2013_10_01.log

3. data pump schema back excluding index, trigger, constraints
impdp userid=system/test@test schemas=test remap_schema=test:test1 exclude=trigger,constraint,index dumpfile=test_table_2013_10_01.dump logfile=test_table_2013_10_01_imp1.log


4. refresh by truncate and insert if data is some.
select ' truncate table test1.' || table_name || ';' from user_tables;
select ' insert into test1.' || table_name || ' select * from test.' || table_name || ';' from user_tables;

5. refresh by data pump data only if data is large.
expdp userid=system/test@test schemas=test include=table dumpfile=test_table_2013_10_03.dump logfile=test_table_2013_10_03.log
impdp userid=system/test@test schemas=test remap_schema=test:test1 exclude=trigger,constraint,index dumpfile=test_table_2013_10_03.dump logfile=test_table_2013_10_03_imp1.log content=data_only


reference:
http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html



Rebuild Standby database through rman restore after open it

1. remove archivelogs from default archivelog location

2. make rman backup files accessible to standby database

3. make rman know backup files
sqlplus> shutdown immediate;
sqlplus> startup mount;

rman target /
rman> catalog start with 'file location directory';

4. restore database
rman > run {
sql 'alter session set optimizer_mode=RULE';
sql 'ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YYYY HH24:MI:SS"';
ALLOCATE CHANNEL d1 DEVICE TYPE DISK FORMAT 'file location directory\DBFULL_%d_%U.bkp';
restore database from TAG "backup tag"   ;
RELEASE CHANNEL d1;
}

5. recover database
5.1 use controlfile before open database
rman target /
rman> recover database until sequence ?????;

5.2 use controlfile after open database
rman target /
rman> list incarnation;
rman> reset database to incarnation 1; ( previous number)
rman> recover database unitl seequnece ????;

5.3 test standby database
sqlplus / as sysdba
sqlplus> alter database open;
sqlplus> select name, host_name, open_mode, database_role from v$database, v$instance;

reference:
http://oracledb-admin.blogspot.com/2011/10/ora-19909-datafile-1-belongs-to-orphan.html
http://www.dba-oracle.com/bk_recover_database_until_cancel_tips.htm