Thursday, June 30, 2011

ORA-29701 raised in ASM I/O path; terminating process

[ID 1328629.1]

Symptoms
You have logged in to the system via an OS user other than Grid and RDBMS Home Onwer. You have made an local bequeath connection to
the database and running SQL like create tablespace, creating tables, creating indexes.


bash-3.00$ id
uid=4600(u500) gid=1527(u500)
bash-3.00$ export ORACLE_SID=HA112
bash-3.00$ export ORACLE_HOME=/refresh/oracle/app/oracle/product/11.2.0
bash-3.00$ export PATH=$PATH:$ORACLE_HOME/bin

bash-3.00$ sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 7 14:59:59 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn u5/u5
Connected.
SQL> create tablespace ts5 datafile '+DATA';
create tablespace ts5 datafile '+DATA'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6291584
Session ID: 29 Serial number: 241


Database alert.log shows
Tue Jun 07 15:00:56 2011
create tablespace ts5 datafile '+DATA'
ERROR: unrecoverable error ORA-29701 raised in ASM I/O path; terminating process 6291584
The trace file shows
2011-06-07 15:00:57.349: [GIPCXCPT] gipcmodClsaAuthStart: failuring during clsaauthmsg ret clsaretOSD (8), endp 110e72290
[0000000000000018] { gipcEndpoint : localAddr 'clsc://(
ADDRESS=(PROTOCOL=ipc)(KEY=)(GIPCID=36cb13cc-0aeca9ae-6291584))', remoteAddr 'clsc://(ADDRESS=(PROTOCOL=ipc)
(KEY=OCSSD_LL_ceaixcb9_)(GIPCID=0aeca9ae-36cb13cc-9044020))', numPend
5, numReady 0, numDone 2, numDead 0, numTransfer 0, objFlags 0x0, pidPeer 9044020, flags 0x2ca712, usrFlags 0x34000 }
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&do...
1 of 2 6/29/2011 8:29 PM
2011-06-07 15:00:57.388: [GIPCXCPT] gipcmodClsaAuthStart: slos op : open
2011-06-07 15:00:57.388: [GIPCXCPT] gipcmodClsaAuthStart: slos dep : Permission denied (13)
2011-06-07 15:00:57.388: [GIPCXCPT] gipcmodClsaAuthStart: slos loc : authrespset3
2011-06-07 15:00:57.388: [GIPCXCPT] gipcmodClsaAuthStart: slos info: failed to open
2011-06-07 15:00:57.389: [ CSSCLNT]clssscConnect: gipc request failed with 22 (12)
2011-06-07 15:00:57.389: [ CSSCLNT]clsssInitNative: connect to (ADDRESS=(PROTOCOL=ipc)(KEY=OCSSD_LL_ceaixcb9_)) failed, rc
22
kgxgncin: CLSS init failed with status 3
kgxgncin: return status 3 (1311719766 SKGXN not av) from CLSS
NOTE: kfmsInit: ASM failed to initialize group services
Error ORA-29701 signaled at
ksedsts()+644<-ksf_short_stack()+88<-kge_snap_callstack()+56<-kge_sigtrace_dump()+56<-kgepop()+72<-kgeselv()+116<-ksesecl0()+80<-kfmsInit()+176<-SlvReg()+436<-kfmdSlvOpPriv()+4688<-kfmdWriteSubmitted()+1132<-kfk_process_an_ioq()+268<-kfk_submit_io()+80<-kfk_io1()+1004<-kfkRequest()+28<-)+5016<-kfioRequestPriv()+236<-kfioRequest()+620<-ksfd_kfioRequest()+576<-ksfd_osmcrt()+2500<-ksfd_create1()+1516<-ksfd_create()+224<-ksfdcre()+<-tbsafl()+1596<-ctsdrv1()+3972<-ctsdrv()+20<-opiexe()+14916<-opiosq0()+9116<-kpooprx()+400<-kpoal8()+1028<-opiodr()+3608<-ttcpip()+4628<-opitsk()+3608<-opidrv()+1200<-sou2o()+192<-opimai_real()+428<-ssthrdmain()+340<-main()+216<-__start()+112 ERROR: unrecoverable error ORA-29701 raised in ASM I/O path; terminating process 6291584

Cause

Bug 12536779 discusses this issue. On 11.2.0.2 SIHA there is removal of world read/write/execute permissions on a few directories and that is intended.

Solution

1] We recommend that the OS user that is making bequeath connection belong to oinstall group. Or you can have the connection through listener.

2] If we do not care about the security of OS user not belonging to oinstall group, modifying the following directories, then the following workaround can be used

# chmod 1777 $GRID_HOME/auth/css/
# chmod 1777 $GRID_HOME/auth/css/
# chmod 1777 $GRID_HOME/auth/
# chmod 0755 $GRID_HOME

References
BUG:12536779 - ORA-29701 OCCURRED DUE TO PERMISSION DENIED
BUG:12599647 - ORA-29701 RAISED IN ASM I/O PATH; TERMINATING PROCESS 1192124

Learn Rank(), Dense_rank() and Row_Number() functions by example



use testdb;
go
create table #test (dim char(1), v int);
insert into #test values('a',1);
insert into #test values('a',2);
insert into #test values('a',3);
insert into #test values('a',4);
insert into #test values('b',1);
insert into #test values('b',2);
insert into #test values('c',1);
go
select dim, v, ROW_NUMBER() over (order by v) as 'RowNumber'
from #test;

/*
dim v RowNumber
---- ----------- --------------------
a 1 1
b 1 2
c 1 3
b 2 4
a 2 5
a 3 6
a 4 7

(7 row(s) affected)
*/

select dim,v,ROW_NUMBER() over (partition by dim order by v desc) as 'RowNumber'
from #test;

/*
dim v RowNumber
---- ----------- --------------------
a 4 1
a 3 2
a 2 3
a 1 4
b 2 1
b 1 2
c 1 1

(7 row(s) affected)
*/

select dim,v,RANK() over (order by v) 'Rank'
from #test;

/*
dim v Rank
---- ----------- --------------------
a 1 1
b 1 1
c 1 1
b 2 4
a 2 4
a 3 6
a 4 7

(7 row(s) affected)
*/


select dim,v,RANK() over (partition by dim order by v) 'Rank'
from #test;

/*
dim v Rank
---- ----------- --------------------
a 1 1
a 2 2
a 3 3
a 4 4
b 1 1
b 2 2
c 1 1

(7 row(s) affected)

*/

select dim, v, DENSE_RANK() over (order by v) 'DenseRank'
from #test;

/*
dim v DenseRank
---- ----------- --------------------
a 1 1
b 1 1
c 1 1
b 2 2
a 2 2
a 3 3
a 4 4

(7 row(s) affected)
*/

select dim,v,NTILE(4) over (order by v) as 'QuarterGroup'
from #test;
/*
dim v QuarterGroup
---- ----------- --------------------
a 1 1
b 1 1
c 1 2
b 2 2
a 2 3
a 3 3
a 4 4

(7 row(s) affected)
*/

Examples on how to use "dbms_obfuscation_toolkit" package


drop table tbl_test purge;

create table tbl_test (
source_passwd varchar2(64),
encrypted_string varchar2(2000),
encrypted_raw raw(2000),
decrypted_string1 varchar2(64),
decrypted_string2 varchar2(64))
/



insert into tbl_test (source_passwd) values('abc');
insert into tbl_test (source_passwd) values('this is long password');
insert into tbl_test (source_passwd) values('Something_Special*');
commit;

-- below program will encrypt the password and store them into both varchar2 and raw columns.
-- the decryption will decrypt twice, one using varchar2, one using raw column

set serveroutput on
execute dbms_output.enable(10000);
declare
v_encrypted_raw RAW(2048);
v_encrypted_string VARCHAR2(2048);
v_decrypted_raw RAW(2048);
v_decrypted_string1 VARCHAR2(2048);
v_decrypted_string2 VARCHAR2(2048);
begin
-- encryption loop
for c1 in (select * from tbl_test)
loop
dbms_output.put_line(c1.source_passwd);
dbms_obfuscation_toolkit.DESEncrypt(input => UTL_RAW.CAST_TO_RAW(rpad(c1.source_passwd,64,' ')),
key => UTL_RAW.CAST_TO_RAW('abcdefgh'),
encrypted_data => v_encrypted_raw);
v_encrypted_string := UTL_RAW.CAST_TO_VARCHAR2(v_encrypted_raw);
update tbl_test
set encrypted_string=v_encrypted_string,
encrypted_raw=v_encrypted_raw
where source_passwd = c1.source_passwd;
end loop;
commit;
-- decrytion loop - using string column
for c1 in (select * from tbl_test)
loop
dbms_output.put_line(c1.source_passwd);
dbms_obfuscation_toolkit.DESDecrypt(input => UTL_RAW.CAST_TO_RAW(c1.encrypted_string),
key => UTL_RAW.CAST_TO_RAW('abcdefgh'),
decrypted_data => v_decrypted_raw);
v_decrypted_string1 := trim(UTL_RAW.CAST_TO_VARCHAR2(v_decrypted_raw));
update tbl_test
set decrypted_string1=v_decrypted_string1
where source_passwd = c1.source_passwd;
end loop;
commit;

-- decrytion loop - using raw column
for c1 in (select * from tbl_test)
loop
dbms_output.put_line(c1.source_passwd);
dbms_obfuscation_toolkit.DESDecrypt(input => c1.encrypted_raw,
key => UTL_RAW.CAST_TO_RAW('abcdefgh'),
decrypted_data => v_decrypted_raw);
v_decrypted_string2 := trim(UTL_RAW.CAST_TO_VARCHAR2(v_decrypted_raw));
update tbl_test
set decrypted_string2=v_decrypted_string2
where source_passwd = c1.source_passwd;
end loop;
commit;
end;
/

select * from tbl_test where source_passwd=decrypted_string1;
select * from tbl_test where source_passwd=decrypted_string2;



Wednesday, June 29, 2011

Create Table with datatype examples


use master;
go
drop database TestDb;
GO
CREATE DATABASE TestDB;
GO
Use TestDB;
GO
CREATE SCHEMA TEST;
GO
CREATE TABLE TEST.Customers (
CustomerId int identity(100,1) NOT NULL,
Name nvarchar(70),
CreatedDateTime DateTime2,
CreditLimit decimal(13,5));
GO

create type Test.NAME from nvarchar(70);
create type Test.CURRENCYVALUE from decimal(14,5);
GO

alter table Test.Customers alter column name Test.Name not null;
alter table Test.Customers alter column CreditLimit Test.CURRENCYVALUE NULL;
alter table Test.Customers alter column CreatedDateTime datetime2 NOT NULL;
GO

insert Test.Customers (Name, CreatedDateTime, CreditLimit)
select top (100000)
so1.name, SYSDATETIME(),case when ABS(so1.object_id) > 10000000 then null else ABS(so1.object_id) end
from sys.all_objects so1 cross join sys.all_objects as sol2;

exec sp_spaceused @objname = 'Test.Customers',@updateusage='true';
Go

alter table Test.Customers rebuild with (data_compression=none);
GO

alter table Test.Customers rebuild with (data_compression=row);
GO

alter table Test.Customers rebuild with (data_compression=page);
GO

Sunday, June 26, 2011

Applying a troubleshooting Methodology

All credit goes to Microsoft, I did not write below myself. They are taken from Microsoft materials.

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

  • Investigate

    • Clearing define the issue as perceived by the user

    • What works? What doesn't work?

    • Did it ever work? When did it last work? What elese changed?

    • How would you know if it is resolved?

  • Analyze

    • Brainstorm all potential causes

    • Which potential causes are likely? How could they be tested for and eliminated?

  • Implement

    • Eliminate potential causes in descending order of likelihood

  • Validate

    • Ensure that the issue really resolved


Key Points


You have seen in the discussion in the last topic that a key characteristic of good trouble-shooter is that they follow a clear methodology in a logical manner. There are manay different methodologies that are often applied to troubleshooting but the following list describes four phases that are common to most methodologies.


Investigation Phases


This is a critical phase. Too many people shortcut this step and start to jump directly in to finding solutions. Before you can solve any problems, you need to be very clear in your understanding of the problem that you are solving.


One very important convept in this phase is that the issue needs to be defined from the point of view of the user that is affected, not from an assumed perspective of an IT person. For example, there is no point telling a user that a system is working if the user cannot use it, for any reason, regardless of how your IT-based perspective might tell you that the system is working.


You need to understand what works and what doesn't work. A common mistake in this phase is to assume that when a user complains that something doesn'twork, that it did ever work. Make sure that there was a time when the issue didn't exist and find out when that was. Also find out abot anything, not matter how unrelated it might seem at this point, that has changed since that time.


Finally, you need to know how the user would decide that the issue is resolved. A common troubleshoting error is to find a problem, to assume that it is the cause of the issue, to resolve that problem, and to assume that the original issue is now resolved.


Analysis Phase


In the analysis phase, you need to determine all possible causes of the issue that you are trying to resolve. At this point, it is important to avoid excluding any potential causes, no matter how unlikely you consider them to be.


A brainstorming session with another person is often useful in this phase, particularly if that person is capable of constantly providing alternative viewpoints during discussions. (In many contries, theis person would be decribed as being good at playing the Devil's advocate). The analysis phase often benefits from two types of people, one of whom has excellent technical knowledge of the product, and another that constantly requires the first person to justfy their throughts and to think both logically and laterally.


Implementation Phase


In the implementation phase, you need to eliminate each potential cause. This process of elimination usually retuens the best results when the potential causes are eliminated in order from the most likely causes to the least likely cause.


The critial aspect of the implementation phase is to make sure that your reasons for eliminating portential causes are logically valid.


If you reach the end of your list of potential causes and have not yet found a solution to the issue, you need to return to the analysis phase and recheck your thinking. If you cannot find a problem in your analysis, you might even need to to back to recheck your initial assumaptions in the investigation phase.


Validation Phase


Too many people, particluarly these that are new to troubleshooting, assume that problems are resolved when they are not. Do not assu,me that because you have found the resolved a problem that it was the original problem that you have solved.


In the investigation phase, you should have determined how the user would devide if the issue is resolved. In the validation phase, you need to apply that test to see if the issue really resolved.

Thursday, June 23, 2011

Capture slow query in MySQL database

Dynamic change the setting without restarting MySQL Server

C:\mysql-advanced-5.5.13-win32\bin>mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.13-enterprise-commercial-advanced-log MySQL Enterprise Serve
r - Advanced Edition (Commercial)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'slow_query_log_file';
+---------------------+----------------------------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------------------------+
| slow_query_log_file | C:\mysql-advanced-5.5.13-win32\data\B5-Donghua1-slow.log |
+---------------------+----------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.03 sec)

mysql> -- testing query
mysql> select sleep(10) from mysql.db limit 1;
+-----------+
| sleep(10) |
+-----------+
| 0 |
+-----------+
1 row in set (10.00 sec)



---------------------------------------------
Permanent settings in my.ini (my.cnf in Unix/Linux)


# The MySQL server
[mysqld]

slow-query-log = 1
slow_query_log_file = C:\mysql-advanced-5.5.13-win32\data\Donghua1-slow.log
long_query_time = 3

Tuesday, June 21, 2011

Using ftp to transfer files in batch mode

bash-3.00# uname -a
SunOS vmxsun01 5.10 Generic_142910-17 i86pc i386 i86pc
bash-3.00#

bash-3.00# ftp -inv <<-EOD
> open localhost
> user oracle ora123
> cd ftproot
> binary
> mput test*.txt
> bye
> EOD
Connected to localhost.
220 vmxsun01 FTP server ready.
331 Password required for oracle.
230 User oracle logged in.
250 CWD command successful.
200 Type set to I.
200 PORT command successful.
150 Opening BINARY mode data connection for testa.txt.
226 Transfer complete.
200 PORT command successful.
150 Opening BINARY mode data connection for testb.txt.
226 Transfer complete.
200 PORT command successful.
150 Opening BINARY mode data connection for testc.txt.
226 Transfer complete.
200 PORT command successful.
150 Opening BINARY mode data connection for testd.txt.
226 Transfer complete.
221-You have transferred 0 bytes in 4 files.
221-Total traffic for this session was 845 bytes in 4 transfers.
221-Thank you for using the FTP service on vmxsun01.
221 Goodbye.

Monday, June 20, 2011

Fixed SQL Server 2005 surface area error in Windows 2003

Symptoms:

An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo)

Possible Solution

Please do not try this solution first, unless you have no other ways to try.

secedit /configure /cfg %windir%\repair\secsetup.inf /db secsetup.sdb /verbose


Explanation


Article ID: 313222 - Last Review: December 1, 2010 - Revision: 14.1
How do I restore security settings to a known working state?

http://support.microsoft.com/kb/313222


For Microsoft Windows 2000, Windows XP or Windows Server 2003 computers, the “secedit /configure /cfg %windir%\repair\secsetup.inf /db secsetup.sdb /verbose” command is still supported in the very few scenarios where security settings need to be restored using the secsetup.inf template. Since importing the Secsetup.inf or any other template only resets what’s defined in the template and does not restore external settings, this method may still not restore all operating system default, including those that may be causing a compatibility problem.

The use of “secedit /configure” to import the default security template, dfltbase.inf, is unsupported nor is it a viable method to restore default security permissions on Windows Vista, Windows 7, Windows Server 2008 and Windows Server 2008 R2 computers.

Beginning with Windows Vista, the method to apply the security during operating system setup changed. Specifically, security settings consisted of settings defined in deftbase.inf augmented by settings applied by the operating installation process and server role installation. Because there is no supported process to replay the permissions made by the operating system setup, the use of the “secedit /configure /cfg %windir%\inf\defltbase.inf /db defltbase.sdb /verbose” command line is no longer capable of resetting all security defaults and may even result in the operating system becoming unstable.

Thursday, June 16, 2011

Important INNODB storage related parameter innodb_file_per_table

The data files that you define in the configuration file form the InnoDB system tablespace. The files are logically concatenated to form the tablespace. There is no striping in use. Currently, you cannot define where within the tablespace your tables are allocated. However, in a newly created tablespace, InnoDB allocates space starting from the first data file.

To avoid the issues that come with storing all tables and indexes inside the system tablespace, you can turn on the innodb_file_per_table configuration option, which stores each newly created table in a separate tablespace file (with extension .ibd). For tables stored this way, there is less fragmentation within the disk file, and when the table is truncated, the space is returned to the operating system rather than still being reserved by InnoDB within the system tablespace.


[root@vmxdb01 test]# grep innodb_file_per_table /etc/my.cnf
innodb_file_per_table = 1


[root@vmxdb01 test]# mysql -uroot -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.13-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)


[root@vmxdb01 test]# ls -l /var/lib/mysql/test
total 216
-rw-rw----. 1 mysql mysql 9106 Jun 15 23:39 customers.frm
-rw-rw----. 1 mysql mysql 98304 Jun 15 23:42 customers.ibd
-rw-rw----. 1 mysql mysql 8556 Jun 16 00:02 user.frm
-rw-rw----. 1 mysql mysql 98304 Jun 16 00:02 user.ibd


This parameter can be dynamically changed:

mysql> set global innodb_file_per_table=on;
Query OK, 0 rows affected (0.00 sec)

Friday, June 10, 2011

RMAN Archival backup example


[oracle@vmxdb01 admin]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Jun 10 17:30:49 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: PRORCL (DBID=4079219725)



RMAN> backup database tag archival_backup_example keep forever restore point jun2011;

Starting backup at 10-JUN-11
current log archived

using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/10/2011 17:31:53
RMAN-06522: KEEP FOREVER option is not supported without the recovery catalog



RMAN> backup database tag archival_backup_example keep until time 'sysdate+365' restore point jun2011;

Starting backup at 10-JUN-11
current log archived

using channel ORA_DISK_1
backup will be obsolete on date 09-JUN-12
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/prorcl/users01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prorcl/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prorcl/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 10-JUN-11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/10/2011 17:32:36
ORA-19811: cannot have files in DB_RECOVERY_FILE_DEST with keep attributes


RMAN> backup database tag archival_backup_example keep until time 'sysdate+365' restore point jun2011
2> format '/u01/backup/prorcl_%U';

Starting backup at 10-JUN-11
current log archived

using channel ORA_DISK_1
backup will be obsolete on date 09-JUN-12
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00004 name=/u01/app/oracle/oradata/prorcl/users01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prorcl/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prorcl/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 10-JUN-11
channel ORA_DISK_1: finished piece 1 at 10-JUN-11
piece handle=/u01/backup/prorcl_2amei3n8_1_1 tag=ARCHIVAL_BACKUP_EXAMPLE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

using channel ORA_DISK_1
backup will be obsolete on date 09-JUN-12
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-JUN-11
channel ORA_DISK_1: finished piece 1 at 10-JUN-11
piece handle=/u01/backup/prorcl_2bmei3o1_1_1 tag=ARCHIVAL_BACKUP_EXAMPLE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01


current log archived
using channel ORA_DISK_1
backup will be obsolete on date 09-JUN-12
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=11 RECID=172 STAMP=753471234
channel ORA_DISK_1: starting piece 1 at 10-JUN-11
channel ORA_DISK_1: finished piece 1 at 10-JUN-11
piece handle=/u01/backup/prorcl_2cmei3o2_1_1 tag=ARCHIVAL_BACKUP_EXAMPLE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
backup will be obsolete on date 09-JUN-12
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 10-JUN-11
channel ORA_DISK_1: finished piece 1 at 10-JUN-11
piece handle=/u01/backup/prorcl_2dmei3o3_1_1 tag=ARCHIVAL_BACKUP_EXAMPLE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JUN-11




RMAN> list backup;


List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 42.27M DISK 00:00:15 10-JUN-11
BP Key: 69 Status: AVAILABLE Compressed: YES Tag: ARCHIVAL_BACKUP_EXAMPLE
Piece Name: /u01/backup/prorcl_2amei3n8_1_1
Keep: BACKUP_LOGS Until: 09-JUN-12
List of Datafiles in backup set 19
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 487509 10-JUN-11 /u01/app/oracle/oradata/prorcl/system01.dbf
2 Full 487509 10-JUN-11 /u01/app/oracle/oradata/prorcl/sysaux01.dbf
3 Full 487509 10-JUN-11 /u01/app/oracle/oradata/prorcl/undotbs01.dbf
4 Full 487509 10-JUN-11 /u01/app/oracle/oradata/prorcl/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
20 Full 80.00K DISK 00:00:00 10-JUN-11
BP Key: 70 Status: AVAILABLE Compressed: YES Tag: ARCHIVAL_BACKUP_EXAMPLE
Piece Name: /u01/backup/prorcl_2bmei3o1_1_1
Keep: BACKUP_LOGS Until: 09-JUN-12
SPFILE Included: Modification time: 10-JUN-11
SPFILE db_unique_name: PRORCL

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
21 16.50K DISK 00:00:00 10-JUN-11
BP Key: 71 Status: AVAILABLE Compressed: YES Tag: ARCHIVAL_BACKUP_EXAMPLE
Piece Name: /u01/backup/prorcl_2cmei3o2_1_1
Keep: BACKUP_LOGS Until: 09-JUN-12

List of Archived Logs in backup set 21
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 11 487505 10-JUN-11 487549 10-JUN-11

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
22 Full 1.03M DISK 00:00:01 10-JUN-11
BP Key: 72 Status: AVAILABLE Compressed: YES Tag: ARCHIVAL_BACKUP_EXAMPLE
Piece Name: /u01/backup/prorcl_2dmei3o3_1_1
Keep: BACKUP_LOGS Until: 09-JUN-12
Control File Included: Ckp SCN: 487554 Ckp time: 10-JUN-11


[oracle@vmxdb01 admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 10 17:37:54 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------------------------------
RESTORE_POINT_TIME PRE
--------------------------------------------------------------------------- ---
NAME
--------------------------------------------------------------------------------
487539 3 NO 0
10-JUN-11 05.33.54.000000000 PM
NO
JUN2011


SQL> exit

RMAN MultiSection backup example


RMAN> backup tablespace system section size 20M format '/tmp/%s_%p_%t';

Starting backup at 10-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 1 through 2560
channel ORA_DISK_1: starting piece 1 at 10-JUN-11
channel ORA_DISK_1: finished piece 1 at 10-JUN-11
piece handle=/tmp/37_1_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 2561 through 5120
channel ORA_DISK_1: starting piece 2 at 10-JUN-11
channel ORA_DISK_1: finished piece 2 at 10-JUN-11
piece handle=/tmp/37_2_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 5121 through 7680
channel ORA_DISK_1: starting piece 3 at 10-JUN-11
channel ORA_DISK_1: finished piece 3 at 10-JUN-11
piece handle=/tmp/37_3_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 7681 through 10240
channel ORA_DISK_1: starting piece 4 at 10-JUN-11
channel ORA_DISK_1: finished piece 4 at 10-JUN-11
piece handle=/tmp/37_4_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 10241 through 12800
channel ORA_DISK_1: starting piece 5 at 10-JUN-11
channel ORA_DISK_1: finished piece 5 at 10-JUN-11
piece handle=/tmp/37_5_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 12801 through 15360
channel ORA_DISK_1: starting piece 6 at 10-JUN-11
channel ORA_DISK_1: finished piece 6 at 10-JUN-11
piece handle=/tmp/37_6_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 15361 through 17920
channel ORA_DISK_1: starting piece 7 at 10-JUN-11
channel ORA_DISK_1: finished piece 7 at 10-JUN-11
piece handle=/tmp/37_7_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 17921 through 20480
channel ORA_DISK_1: starting piece 8 at 10-JUN-11
channel ORA_DISK_1: finished piece 8 at 10-JUN-11
piece handle=/tmp/37_8_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 20481 through 23040
channel ORA_DISK_1: starting piece 9 at 10-JUN-11
channel ORA_DISK_1: finished piece 9 at 10-JUN-11
piece handle=/tmp/37_9_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 23041 through 25600
channel ORA_DISK_1: starting piece 10 at 10-JUN-11
channel ORA_DISK_1: finished piece 10 at 10-JUN-11
piece handle=/tmp/37_10_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 25601 through 28160
channel ORA_DISK_1: starting piece 11 at 10-JUN-11
channel ORA_DISK_1: finished piece 11 at 10-JUN-11
piece handle=/tmp/37_11_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 28161 through 30720
channel ORA_DISK_1: starting piece 12 at 10-JUN-11
channel ORA_DISK_1: finished piece 12 at 10-JUN-11
piece handle=/tmp/37_12_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 30721 through 33280
channel ORA_DISK_1: starting piece 13 at 10-JUN-11
channel ORA_DISK_1: finished piece 13 at 10-JUN-11
piece handle=/tmp/37_13_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 33281 through 35840
channel ORA_DISK_1: starting piece 14 at 10-JUN-11
channel ORA_DISK_1: finished piece 14 at 10-JUN-11
piece handle=/tmp/37_14_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
backing up blocks 35841 through 38400
channel ORA_DISK_1: starting piece 15 at 10-JUN-11
channel ORA_DISK_1: finished piece 15 at 10-JUN-11
piece handle=/tmp/37_15_753469963 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 10-JUN-11
channel ORA_DISK_1: finished piece 1 at 10-JUN-11
piece handle=/tmp/52_1_753469979 tag=TAG20110610T171243 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JUN-11




BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
11 Full 175.91M DISK 00:00:15 10-JUN-11
List of Datafiles in backup set 11
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 485799 10-JUN-11 /u01/app/oracle/oradata/prorcl/system01.dbf

Backup Set Copy #1 of backup set 11
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:15 10-JUN-11 NO TAG20110610T171243

List of Backup Pieces for backup set 11 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
33 1 AVAILABLE /tmp/37_1_753469963
34 2 AVAILABLE /tmp/37_2_753469963
35 3 AVAILABLE /tmp/37_3_753469963
36 4 AVAILABLE /tmp/37_4_753469963
37 5 AVAILABLE /tmp/37_5_753469963
38 6 AVAILABLE /tmp/37_6_753469963
39 7 AVAILABLE /tmp/37_7_753469963
40 8 AVAILABLE /tmp/37_8_753469963
41 9 AVAILABLE /tmp/37_9_753469963
42 10 AVAILABLE /tmp/37_10_753469963
43 11 AVAILABLE /tmp/37_11_753469963
44 12 AVAILABLE /tmp/37_12_753469963
45 13 AVAILABLE /tmp/37_13_753469963
46 14 AVAILABLE /tmp/37_14_753469963
47 15 AVAILABLE /tmp/37_15_753469963

Learn RMAN MaxSetSize and MaxPieceSize by example


RMAN> backup tablespace system maxsetsize 20M;

Starting backup at 10-JUN-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/10/2011 16:56:46
RMAN-06183: datafile or datafile copy /u01/app/oracle/oradata/prorcl/system01.dbf (file number 1) larger than MAXSETSIZE


RMAN> run {
2> allocate channel ch0 device type disk maxpiecesize 20M format '/tmp/%s_%p_%t';
3> backup tablespace system;
4> }

released channel: ORA_DISK_1
allocated channel: ch0
channel ch0: SID=48 device type=DISK

Starting backup at 10-JUN-11
channel ch0: starting full datafile backup set
channel ch0: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
channel ch0: starting piece 1 at 10-JUN-11
channel ch0: finished piece 1 at 10-JUN-11
piece handle=/tmp/16_1_753469135 tag=TAG20110610T165855 comment=NONE
channel ch0: starting piece 2 at 10-JUN-11
channel ch0: finished piece 2 at 10-JUN-11
piece handle=/tmp/16_2_753469135 tag=TAG20110610T165855 comment=NONE
channel ch0: starting piece 3 at 10-JUN-11
channel ch0: finished piece 3 at 10-JUN-11
piece handle=/tmp/16_3_753469135 tag=TAG20110610T165855 comment=NONE
channel ch0: starting piece 4 at 10-JUN-11
channel ch0: finished piece 4 at 10-JUN-11
piece handle=/tmp/16_4_753469135 tag=TAG20110610T165855 comment=NONE
channel ch0: starting piece 5 at 10-JUN-11
channel ch0: finished piece 5 at 10-JUN-11
piece handle=/tmp/16_5_753469135 tag=TAG20110610T165855 comment=NONE
channel ch0: starting piece 6 at 10-JUN-11
channel ch0: finished piece 6 at 10-JUN-11
piece handle=/tmp/16_6_753469135 tag=TAG20110610T165855 comment=NONE
channel ch0: starting piece 7 at 10-JUN-11
channel ch0: finished piece 7 at 10-JUN-11
piece handle=/tmp/16_7_753469135 tag=TAG20110610T165855 comment=NONE
channel ch0: starting piece 8 at 10-JUN-11
channel ch0: finished piece 8 at 10-JUN-11
piece handle=/tmp/16_8_753469135 tag=TAG20110610T165855 comment=NONE
channel ch0: starting piece 9 at 10-JUN-11
channel ch0: finished piece 9 at 10-JUN-11
piece handle=/tmp/16_9_753469135 tag=TAG20110610T165855 comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:09
channel ch0: starting full datafile backup set
channel ch0: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ch0: starting piece 1 at 10-JUN-11
channel ch0: finished piece 1 at 10-JUN-11
piece handle=/tmp/17_1_753469144 tag=TAG20110610T165855 comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:01
Finished backup at 10-JUN-11
released channel: ch0


RMAN> list backupset 4;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 175.63M DISK 00:00:08 10-JUN-11
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 484234 10-JUN-11 /u01/app/oracle/oradata/prorcl/system01.dbf

Backup Set Copy #1 of backup set 4
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:00:08 10-JUN-11 NO TAG20110610T165855

List of Backup Pieces for backup set 4 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
4 1 AVAILABLE /tmp/16_1_753469135
5 2 AVAILABLE /tmp/16_2_753469135
6 3 AVAILABLE /tmp/16_3_753469135
7 4 AVAILABLE /tmp/16_4_753469135
8 5 AVAILABLE /tmp/16_5_753469135
9 6 AVAILABLE /tmp/16_6_753469135
10 7 AVAILABLE /tmp/16_7_753469135
11 8 AVAILABLE /tmp/16_8_753469135
12 9 AVAILABLE /tmp/16_9_753469135

RMAN> exit


Recovery Manager complete.
[oracle@vmxdb01 admin]$ ls -lh /tmp/16*
-rw-r-----. 1 oracle oinstall 20M Jun 10 16:58 /tmp/16_1_753469135
-rw-r-----. 1 oracle oinstall 20M Jun 10 16:58 /tmp/16_2_753469135
-rw-r-----. 1 oracle oinstall 20M Jun 10 16:58 /tmp/16_3_753469135
-rw-r-----. 1 oracle oinstall 20M Jun 10 16:58 /tmp/16_4_753469135
-rw-r-----. 1 oracle oinstall 20M Jun 10 16:58 /tmp/16_5_753469135
-rw-r-----. 1 oracle oinstall 20M Jun 10 16:59 /tmp/16_6_753469135
-rw-r-----. 1 oracle oinstall 20M Jun 10 16:59 /tmp/16_7_753469135
-rw-r-----. 1 oracle oinstall 20M Jun 10 16:59 /tmp/16_8_753469135
-rw-r-----. 1 oracle oinstall 16M Jun 10 16:59 /tmp/16_9_753469135
[oracle@vmxdb01 admin]$

Thursday, June 2, 2011

Quick way to create a standby database without backup


[oracle@vmxdb01 admin]$ cat /u01/app/oracle/product/11.2.0.2/db_1/dbs/initprorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/prorcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prorcl/control01.ctl','/u01/app/oracle/oradata/prorcl/control02.ctl','/u01/app/oracle/oradata/prorcl/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='drorcl','prorcl'
*.db_name='prorcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4196401152
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='DRORCL'
*.log_archive_config='DG_CONFIG=(prorcl,drorcl)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES DB_UNIQUE_NAME=prorcl'
*.log_archive_dest_2='SERVICE=drorcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=drorcl'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='drorcl','prorcl'
*.memory_target=400M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'



[oracle@vmxdb01 admin]$ cat /u01/app/oracle/product/11.2.0.2/db_1/dbs/initdrorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/drorcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/drorcl/control01.ctl','/u01/app/oracle/oradata/drorcl/control02.ctl','/u01/app/oracle/oradata/drorcl/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='prorcl','drorcl'
*.db_name='prorcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4196401152
*.db_unique_name='DRORCL'
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='DRORCL'
*.log_archive_config='DG_CONFIG=(prorcl,drorcl)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES DB_UNIQUE_NAME=drorcl'
*.log_archive_dest_2='SERVICE=prorcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prorcl'
*.log_archive_format='%t_%s_%r.arc'
*.log_file_name_convert='prorcl','drorcl'
*.memory_target=400M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'



[oracle@vmxdb01 admin]$ rman target / auxiliary sys/ora123@drorcl

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jun 2 21:03:45 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: PRORCL (DBID=4079219725)
connected to auxiliary database: PRORCL (not mounted)

RMAN> duplicate target database for standby from active database;

Starting Duplicate Db at 02-JUN-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0.2/db_1/dbs/orapwprorcl' auxiliary format
'/u01/app/oracle/product/11.2.0.2/db_1/dbs/orapwdrorcl' ;
}
executing Memory Script

Starting backup at 02-JUN-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
Finished backup at 02-JUN-11

contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/drorcl/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/drorcl/control02.ctl' from
'/u01/app/oracle/oradata/drorcl/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/drorcl/control03.ctl' from
'/u01/app/oracle/oradata/drorcl/control01.ctl';
}
executing Memory Script

Starting backup at 02-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0.2/db_1/dbs/snapcf_prorcl.f tag=TAG20110602T210352 RECID=4 STAMP=752792632
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 02-JUN-11

Starting restore at 02-JUN-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 02-JUN-11

Starting restore at 02-JUN-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 02-JUN-11

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/drorcl/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/drorcl/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/drorcl/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/drorcl/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/drorcl/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/drorcl/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/drorcl/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/drorcl/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/drorcl/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/drorcl/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 02-JUN-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/prorcl/users01.dbf
output file name=/u01/app/oracle/oradata/drorcl/users01.dbf tag=TAG20110602T210401
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/prorcl/system01.dbf
output file name=/u01/app/oracle/oradata/drorcl/system01.dbf tag=TAG20110602T210401
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/prorcl/sysaux01.dbf
output file name=/u01/app/oracle/oradata/drorcl/sysaux01.dbf tag=TAG20110602T210401
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/prorcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/drorcl/undotbs01.dbf tag=TAG20110602T210401
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 02-JUN-11

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=752792677 file name=/u01/app/oracle/oradata/drorcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=752792677 file name=/u01/app/oracle/oradata/drorcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=752792677 file name=/u01/app/oracle/oradata/drorcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=752792677 file name=/u01/app/oracle/oradata/drorcl/users01.dbf
Finished Duplicate Db at 02-JUN-11

RMAN> exit


Recovery Manager complete.




alter database add standby logfile group 4 ('/u01/app/oracle/oradata/drorcl/stdby04.log') size 100M;
alter database add standby logfile group 5 ('/u01/app/oracle/oradata/drorcl/stdby05.log') size 100M;
alter database add standby logfile group 6 ('/u01/app/oracle/oradata/drorcl/stdby06.log') size 100M;
alter database add standby logfile group 7 ('/u01/app/oracle/oradata/drorcl/stdby07.log') size 100M;