SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
X1 TABLE
SQL> show user;
USER is "N"
SQL> desc flashback_transaction_query
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
XID RAW(8)
START_SCN NUMBER
START_TIMESTAMP DATE
COMMIT_SCN NUMBER
COMMIT_TIMESTAMP DATE
LOGON_USER VARCHAR2(30)
UNDO_CHANGE# NUMBER
OPERATION VARCHAR2(32)
TABLE_NAME VARCHAR2(256)
TABLE_OWNER VARCHAR2(32)
ROW_ID VARCHAR2(19)
UNDO_SQL VARCHAR2(4000)
SQL> select * from flashback_transaction_query where table_name='X1';
no rows selected
SQL> select * from x1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select * from flashback_transaction_query where table_owner='N';
no rows selected
SQL> insert into x1 select * from x1 where empno=7369;
1 row created.
SQL> select count(*) from x1;
COUNT(*)
----------
15
SQL> commit;
Commit complete.
SQL> select * from flashback_transaction_query where table_name='X1';
XID START_SCN START_TIM COMMIT_SCN COMMIT_TI LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID
---------------- ---------- --------- ---------- --------- ------------------------------ ------------ -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------- -------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
05002C0013030000 1454729 13-NOV-07 1456180 13-NOV-07 N 1 INSERT X1 N AAAMr2AAAAAAAAAAAA
delete from "N"."X1" where ROWID = 'AAAMr2AAAAAAAAAAAA';
SQL> insert into x1 select * from x1 where empno=7934;
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from x1;
COUNT(*)
----------
16
SQL> select * from flashback_transaction_query where table_name='X1';
XID START_SCN START_TIM COMMIT_SCN COMMIT_TI LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID
---------------- ---------- --------- ---------- --------- ------------------------------ ------------ -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------- -------------------
UNDO_SQL
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
05002C0013030000 1454729 13-NOV-07 1456180 13-NOV-07 N 1 INSERT X1 N AAAMr2AAAAAAAAAAAA
delete from "N"."X1" where ROWID = 'AAAMr2AAAAAAAAAAAA';
0800000014030000 1456180 13-NOV-07 1456222 13-NOV-07 N 1 INSERT X1 N AAAMr2AAAAAAAAAAAA
delete from "N"."X1" where ROWID = 'AAAMr2AAAAAAAAAAAA';
SQL> spool off
Tuesday, March 31, 2009
Sunday, March 29, 2009
ASM to Non ASM
connected to target database: ORCL (DBID=1185757350)
ASM TO NON ASM
1.
RMAN> run
2> {
3> allocate channel c1 device type disk format='E:\backup\db_%U';
4> backup database;
5> }
released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=141 devtype=DISK
Starting backup at 08-AUG-08
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DBFILES/orcl/datafile/system.263.659715057
input datafile fno=00003 name=+DBFILES/orcl/datafile/sysaux.265.659715107
input datafile fno=00002 name=+DBFILES/orcl/datafile/undotbs1.264.659715093
input datafile fno=00005 name=+DBFILES/orcl/datafile/example.267.659715139
input datafile fno=00004 name=+DBFILES/orcl/datafile/users.266.659715133
channel c1: starting piece 1 at 08-AUG-08
channel c1: finished piece 1 at 08-AUG-08
piece handle=E:\BACKUP\DB_1LJNH6L3_1_1 tag=TAG20080808T124211 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:26
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel c1: starting piece 1 at 08-AUG-08
channel c1: finished piece 1 at 08-AUG-08
piece handle=E:\BACKUP\DB_1MJNH6NP_1_1 tag=TAG20080808T124211 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 08-AUG-08
released channel: c1
2. Shutdown Immediate
3.startup Nomount
4.Change the intialization parametres
*alter system set db_create_file_dest=' ' scope=spfile
*alter system set db_create_online_log_dest_1=' ' scope=spfile;
*alter system set control_files='E:/oracle/product/10.2.0/oradata/control01.ctl' scope=spfile;
5.(Optional)
SQL> drop diskgroup 'DBFILES' including contents;
Diskgroup dropped.
5.
run
{
shutdown immediate;
startup nomount
}
RMAN>set DBID=1185757350;
RMAN> run
2> {
3> restore controlfile from "E:\BACKUP\DB_1MJNH6NP_1_1";
4> startup mount;
5> set newname for datafile 1 to 'E:/oracle/product/10.2.0/oradata/system.dbf' ;
6> set newname for datafile 2 to 'E:/oracle/product/10.2.0/oradata/undotbs1.dbf' ;
7> set newname for datafile 3 to 'E:/oracle/product/10.2.0/oradata/sysaux.dbf' ;
8> set newname for datafile 4 to 'E:/oracle/product/10.2.0/oradata/users.dbf' ;
9> set newname for datafile 5 to 'E:/oracle/product/10.2.0/oradata/example.dbf' ;
10> restore database;
11> switch datafile all;
12> recover database until logseq 63;
13> sql"alter database mount";
14> sql"alter database open resetlogs";
15> }
Logfile created automatically in Flash recovery area for the same size and database opened with resetlogs.
ASM TO NON ASM
1.
RMAN> run
2> {
3> allocate channel c1 device type disk format='E:\backup\db_%U';
4> backup database;
5> }
released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=141 devtype=DISK
Starting backup at 08-AUG-08
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DBFILES/orcl/datafile/system.263.659715057
input datafile fno=00003 name=+DBFILES/orcl/datafile/sysaux.265.659715107
input datafile fno=00002 name=+DBFILES/orcl/datafile/undotbs1.264.659715093
input datafile fno=00005 name=+DBFILES/orcl/datafile/example.267.659715139
input datafile fno=00004 name=+DBFILES/orcl/datafile/users.266.659715133
channel c1: starting piece 1 at 08-AUG-08
channel c1: finished piece 1 at 08-AUG-08
piece handle=E:\BACKUP\DB_1LJNH6L3_1_1 tag=TAG20080808T124211 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:26
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel c1: starting piece 1 at 08-AUG-08
channel c1: finished piece 1 at 08-AUG-08
piece handle=E:\BACKUP\DB_1MJNH6NP_1_1 tag=TAG20080808T124211 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:03
Finished backup at 08-AUG-08
released channel: c1
2. Shutdown Immediate
3.startup Nomount
4.Change the intialization parametres
*alter system set db_create_file_dest=' ' scope=spfile
*alter system set db_create_online_log_dest_1=' ' scope=spfile;
*alter system set control_files='E:/oracle/product/10.2.0/oradata/control01.ctl' scope=spfile;
5.(Optional)
SQL> drop diskgroup 'DBFILES' including contents;
Diskgroup dropped.
5.
run
{
shutdown immediate;
startup nomount
}
RMAN>set DBID=1185757350;
RMAN> run
2> {
3> restore controlfile from "E:\BACKUP\DB_1MJNH6NP_1_1";
4> startup mount;
5> set newname for datafile 1 to 'E:/oracle/product/10.2.0/oradata/system.dbf' ;
6> set newname for datafile 2 to 'E:/oracle/product/10.2.0/oradata/undotbs1.dbf' ;
7> set newname for datafile 3 to 'E:/oracle/product/10.2.0/oradata/sysaux.dbf' ;
8> set newname for datafile 4 to 'E:/oracle/product/10.2.0/oradata/users.dbf' ;
9> set newname for datafile 5 to 'E:/oracle/product/10.2.0/oradata/example.dbf' ;
10> restore database;
11> switch datafile all;
12> recover database until logseq 63;
13> sql"alter database mount";
14> sql"alter database open resetlogs";
15> }
Logfile created automatically in Flash recovery area for the same size and database opened with resetlogs.
Saturday, March 28, 2009
General Syntax RMAN
RMAN> run
2> {
3> shutdown immediate;
4> startup mount;
5> restore database;
6> recover database until logseq 14;
7> sql"alter database open";
8> }
The command has no syntax errors
RMAN> run
2> {
3> shutdown immediate;
4> startup mount;
5> restore database;
6> recover database until logseq 14;
7> sql"alter database open resetlogs";
8> }
The command has no syntax errors
RMAN> run
2> {
3> sql"alter tablespace user offline";
4> restore tablespace users;
5> recover tablespace users until logseq 15;
6> sql"alter tablespace users online";
7> }
The command has no syntax errors
RMAN> run
2> {
3> sql"alter tablespace user offline";
4> restore tablespace users;
5> recover tablespace users until logseq 15 AUXILIARY DESTINATION 'e:\test\';
6> sql"alter tablespace users online";
7> }
The command has no syntax errors
RMAN> exit
RUN
{
SET UNTIL SCN 1000;
# Alternatives:
# SET UNTIL TIME 'Nov 15 2004 09:00:00';
# SET UNTIL SEQUENCE 9923;
RESTORE DATABASE;
RECOVER DATABASE;
}
TSPITR
>Run
{
Restore tablespace users;
RECOVER TABLESPACE users UNTIL LOGSEQ 1300 AUXILIARY DESTINATION 'c:\auxdest';
}
2> {
3> shutdown immediate;
4> startup mount;
5> restore database;
6> recover database until logseq 14;
7> sql"alter database open";
8> }
The command has no syntax errors
RMAN> run
2> {
3> shutdown immediate;
4> startup mount;
5> restore database;
6> recover database until logseq 14;
7> sql"alter database open resetlogs";
8> }
The command has no syntax errors
RMAN> run
2> {
3> sql"alter tablespace user offline";
4> restore tablespace users;
5> recover tablespace users until logseq 15;
6> sql"alter tablespace users online";
7> }
The command has no syntax errors
RMAN> run
2> {
3> sql"alter tablespace user offline";
4> restore tablespace users;
5> recover tablespace users until logseq 15 AUXILIARY DESTINATION 'e:\test\';
6> sql"alter tablespace users online";
7> }
The command has no syntax errors
RMAN> exit
RUN
{
SET UNTIL SCN 1000;
# Alternatives:
# SET UNTIL TIME 'Nov 15 2004 09:00:00';
# SET UNTIL SEQUENCE 9923;
RESTORE DATABASE;
RECOVER DATABASE;
}
TSPITR
>Run
{
Restore tablespace users;
RECOVER TABLESPACE users UNTIL LOGSEQ 1300 AUXILIARY DESTINATION 'c:\auxdest';
}
Transport Tablespace
1. Make the tablespace read only.
2.export tablespace using TRANSPORT_TABLESPACE option
3. copy the datafile to the destination database server.
4.transfer the dumpfile to the destination server.
5. import tablespace using TRANSPORT_TABLESPACE option.
6. Make the tablespace read write
********** On Source database **************
ALTER TABLESPACE NV READ ONLY;
select file_name from dba_data_files where tablespace_name = 'NV';
exp "sys/databaseadmin@NVDB" transport_tablespace=y tablespaces=NV
triggers=y constraints=y grants=y file=test.dmp
copy NV01.dbf to the filesystem for the destination database. (Copy all the datafiles of the NV tablespace to the destination database).
ALTER TABLESPACE NV READ WRITE;
************* On Destination database ***********
imp "sys/databaseadmin@NVDB1" transport_tablespace=y datafiles='NV01.dbf'
file=test.dmp
ALTER TABLESPACE NV READ WRITE;
select file_name from dba_data_files where tablespace_name = 'NV';
Its done ...
2.export tablespace using TRANSPORT_TABLESPACE option
3. copy the datafile to the destination database server.
4.transfer the dumpfile to the destination server.
5. import tablespace using TRANSPORT_TABLESPACE option.
6. Make the tablespace read write
********** On Source database **************
ALTER TABLESPACE NV READ ONLY;
select file_name from dba_data_files where tablespace_name = 'NV';
exp "sys/databaseadmin@NVDB" transport_tablespace=y tablespaces=NV
triggers=y constraints=y grants=y file=test.dmp
copy NV01.dbf to the filesystem for the destination database. (Copy all the datafiles of the NV tablespace to the destination database).
ALTER TABLESPACE NV READ WRITE;
************* On Destination database ***********
imp "sys/databaseadmin@NVDB1" transport_tablespace=y datafiles='NV01.dbf'
file=test.dmp
ALTER TABLESPACE NV READ WRITE;
select file_name from dba_data_files where tablespace_name = 'NV';
Its done ...
Restoring database to another machine using RMAN Backup
Restoring and Recovering a New Database using a Different database backup.
1.>> Destination database must be same version as the target database and also it must be of same platform.
2.>> Backup the source database using normal backup operation.
3.>> Transfer the backup files to new host (different host).
4.>> Create passwordfile for the new database same as source database.
5.>> Create pfile same as source database and change parameters according to the requirement.(Dont change the DB_ID value).
6.>> Create the instance same as Source database instance.
7.>> Configure Network services (TNS and Listener) for New DB.
8.>> Set DB_ID parameter as same as Source Database.
SET DBID 1340752057;
9.>> STARTUP NOMOUNT;
10.>> Restore and edit the server parameter file.Because you enabled the control file autobackup feature when making your backups,
the server parameter file is included in the backup sets.Allocate a channel to the media manager, then restore the server
parameter file (SPFILE) as a client-side pararameter file (PFILE).
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
RESTORE SPFILE TO PFILE '?/oradata/test/inittrgta.ora' FROM AUTOBACKUP;
SHUTDOWN ABORT;
}
Next, edit the restored PFILE . Change any location-specific parameters, for example, those ending in _DEST and _PATH,
to reflect the new directory structure. For example, edit the following parameters:
- IFILE
- *_DUMP_DEST
- LOG_ARCHIVE_DEST*
- CONTROL_FILES
11.>>Then restart the instance, using the edited PFILE:
STARTUP FORCE NOMOUNT PFILE='?/oradata/test/inittrgta.ora';
12.>>Restore the control file from an autobackup and then mount the database. RMAN restores the control file
to whatever location you specified in the CONTROL_FILES initialization parameter. For example:
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
}
13.>>For example, consider the following RMAN script to perform these steps, which is contained in text file reco_test.rman:
RUN
{
# allocate a channel to the tape device
ALLOCATE CHANNEL c1 DEVICE TYPE sbt PARMS='...';
# rename the datafiles and online redo logs
SET NEWNAME FOR DATAFILE 1 TO '?/oradata/test/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '?/oradata/test/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '?/oradata/test/cwmlite01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '?/oradata/test/drsys01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '?/oradata/test/example01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '?/oradata/test/indx01.dbf';
SET NEWNAME FOR DATAFILE 7 TO '?/oradata/test/tools01.dbf';
SET NEWNAME FOR DATAFILE 8 TO '?/oradata/test/users01.dbf';
SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo01.log''
TO ''?/oradata/test/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/dev3/oracle/dbs/redo02.log''
TO ''?/oradata/test/redo02.log'' ";
# Do a SET UNTIL to prevent recovery of the online logs
SET UNTIL SCN 123456;
# restore the database and switch the datafile names
RESTORE DATABASE;
SWITCH DATAFILE ALL;
# recover the database
RECOVER DATABASE;
}
EXIT
14.>> Alter database open resetlogs;
*******************Sucessfully Completed********************************
ASM Migration
1.step
C:\Documents and Settings\Navaneeth.v>e:\oracle\product\10.2.0\db_1\bin\localconfig.bat add
Step 1: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'kgisl\Navaneeth.v', privgrp ''..
Operation successful.
Step 2: creating new CSS service
successfully created local CSS service
successfully added CSS to home
2 .step
C:\>asmtool -create E:\ASM_Disk\asm_disk1 1000
C:\>set oracle_sid=+asm
C:\>sqlplus
selectDiskUIDataSource - You need at least one disk to create a Disk Group with external redundancy
Reference Document :
http://www.oracle.com/technology/obe/obe10gdb/storage/asm/asm.htm
http://www.oracle.com/technology/products/database/asm/pdf/asm_10gr2_bptwp_sept05.pdf
For ASM Configuration :
step1 : Creating Partition
For Creatign RAW partations Step by step :
http://download.oracle.com/docs/cd/B19306_01/install.102/b15660/pre_install.htm#sthref591 **
Microsoft DiskPart version 5.1.3553
Copyright (C) 1999-2001 Microsoft Corporation.
On computer: KGSOFT148
DISKPART> select disk 0
Disk 0 is now the selected disk.
DISKPART> list partition
Partition ### Type Size Offset
------------- ---------------- ------- -------
Partition 1 Primary 15 GB 32 KB
Partition 2 Primary 20 GB 15 GB
Partition 3 Primary 29 GB 34 GB
DISKPART> create partition primary size=11000
DiskPart succeeded in creating the specified partition.
DISKPART> list partition
Partition ### Type Size Offset
------------- ---------------- ------- -------
Partition 1 Primary 15 GB 32 KB
Partition 2 Primary 20 GB 15 GB
Partition 3 Primary 29 GB 34 GB
* Partition 4 Primary 11 GB 63 GB
DISKPART>
Step 2:
Run "asmtoolg" tool For Creating disks.
+
Do needed For creating the disks.
Step 3: Creating Disk group
SQL> create diskgroup 'DBFILES' external redundancy disk '\\.\ORCLDISKDATA0' SIZE 10997 M ;
Diskgroup created.
C:\Documents and Settings\Navaneeth.v>asmcmd -p
ASMCMD [+] > ls
DBFILES/
ASMCMD [+] > cd DBFILES
ASMCMD [+DBFILES] > ls
Migrate Existing Database to ASM using Database Control.
Migrating to Oracle ASM using Oracle Enterprise Manager 10g Database Control
http://www.oracle.com/technology/pub/notes/technote_rego.html
http://www.oracle-base.com/articles/10g/ASMUsingASMLibAndRawDevices.php
For Configuring ASM
http://www.oracledba.org/10g/asm/dbca_asm_crte.html
http://www.oracle.com/technology/obe/obe10gdb/manage/asm/10g_asmlab3/viewlet/asmlab3_viewlet_swf.html
***ASM conversion using RMAN***
1.
run
{
allocate channel c1 device type disk format='+dbfiles';
backup as copy database;
}
2.
SQL> alter system set db_create_file_dest='+DBFILES';
System altered.
3.
SQL> alter system set db_create_online_log_dest_1='+DBFILES';
System altered.
3a. Alter system set control_files='+DBFILES'
4. Including all datafiles and tempfiles.
run
{
shutdown immediate;
startup mount;
set newname for datafile 1 to '+dbfiles';
set newname for datafile 2 to '+dbfiles';
set newname for datafile 3 to '+dbfiles';
set newname for datafile 4 to '+dbfiles';
set newname for datafile 5 to '+dbfiles';
restore controlfile from copy '+DBFILES' ---- > check not confirmed yet
restore database;
switch datafile 1;
switch datafile 2;
switch datafile 3;
switch datafile 4;
switch datafile 5;
recover database;
sql" alter database open resetlogs;
}
Transfer logfiles to ASM by creating new files and drop existing ones.
5. Alter database add logfile group 3 '+DBFILES' size 10m;
6. Alter database add logifle group 4 '+DBFILES' size 10m;
7. alter system switch logfile;
8. Alter system switch logfile;
9. Alter system checkpoint;
10.Alter database drop logfile group 1;
11. Alter database drop logfile group 2;
Check the logfiles for ASM location
12.select * from v$logfile;
Completed sucessfully.
C:\Documents and Settings\Navaneeth.v>e:\oracle\product\10.2.0\db_1\bin\localconfig.bat add
Step 1: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'kgisl\Navaneeth.v', privgrp ''..
Operation successful.
Step 2: creating new CSS service
successfully created local CSS service
successfully added CSS to home
2 .step
C:\>asmtool -create E:\ASM_Disk\asm_disk1 1000
C:\>set oracle_sid=+asm
C:\>sqlplus
selectDiskUIDataSource - You need at least one disk to create a Disk Group with external redundancy
Reference Document :
http://www.oracle.com/technology/obe/obe10gdb/storage/asm/asm.htm
http://www.oracle.com/technology/products/database/asm/pdf/asm_10gr2_bptwp_sept05.pdf
For ASM Configuration :
step1 : Creating Partition
For Creatign RAW partations Step by step :
http://download.oracle.com/docs/cd/B19306_01/install.102/b15660/pre_install.htm#sthref591 **
Microsoft DiskPart version 5.1.3553
Copyright (C) 1999-2001 Microsoft Corporation.
On computer: KGSOFT148
DISKPART> select disk 0
Disk 0 is now the selected disk.
DISKPART> list partition
Partition ### Type Size Offset
------------- ---------------- ------- -------
Partition 1 Primary 15 GB 32 KB
Partition 2 Primary 20 GB 15 GB
Partition 3 Primary 29 GB 34 GB
DISKPART> create partition primary size=11000
DiskPart succeeded in creating the specified partition.
DISKPART> list partition
Partition ### Type Size Offset
------------- ---------------- ------- -------
Partition 1 Primary 15 GB 32 KB
Partition 2 Primary 20 GB 15 GB
Partition 3 Primary 29 GB 34 GB
* Partition 4 Primary 11 GB 63 GB
DISKPART>
Step 2:
Run "asmtoolg" tool For Creating disks.
+
Do needed For creating the disks.
Step 3: Creating Disk group
SQL> create diskgroup 'DBFILES' external redundancy disk '\\.\ORCLDISKDATA0' SIZE 10997 M ;
Diskgroup created.
C:\Documents and Settings\Navaneeth.v>asmcmd -p
ASMCMD [+] > ls
DBFILES/
ASMCMD [+] > cd DBFILES
ASMCMD [+DBFILES] > ls
Migrate Existing Database to ASM using Database Control.
Migrating to Oracle ASM using Oracle Enterprise Manager 10g Database Control
http://www.oracle.com/technology/pub/notes/technote_rego.html
http://www.oracle-base.com/articles/10g/ASMUsingASMLibAndRawDevices.php
For Configuring ASM
http://www.oracledba.org/10g/asm/dbca_asm_crte.html
http://www.oracle.com/technology/obe/obe10gdb/manage/asm/10g_asmlab3/viewlet/asmlab3_viewlet_swf.html
***ASM conversion using RMAN***
1.
run
{
allocate channel c1 device type disk format='+dbfiles';
backup as copy database;
}
2.
SQL> alter system set db_create_file_dest='+DBFILES';
System altered.
3.
SQL> alter system set db_create_online_log_dest_1='+DBFILES';
System altered.
3a. Alter system set control_files='+DBFILES'
4. Including all datafiles and tempfiles.
run
{
shutdown immediate;
startup mount;
set newname for datafile 1 to '+dbfiles';
set newname for datafile 2 to '+dbfiles';
set newname for datafile 3 to '+dbfiles';
set newname for datafile 4 to '+dbfiles';
set newname for datafile 5 to '+dbfiles';
restore controlfile from copy '+DBFILES' ---- > check not confirmed yet
restore database;
switch datafile 1;
switch datafile 2;
switch datafile 3;
switch datafile 4;
switch datafile 5;
recover database;
sql" alter database open resetlogs;
}
Transfer logfiles to ASM by creating new files and drop existing ones.
5. Alter database add logfile group 3 '+DBFILES' size 10m;
6. Alter database add logifle group 4 '+DBFILES' size 10m;
7. alter system switch logfile;
8. Alter system switch logfile;
9. Alter system checkpoint;
10.Alter database drop logfile group 1;
11. Alter database drop logfile group 2;
Check the logfiles for ASM location
12.select * from v$logfile;
Completed sucessfully.
Database Cloning
1.Backup the target database.
RMAN> run
2> {
3> allocate channel c1 device type disk format 'D:\test\db_%U';
4> backup database;
5> }
1.a. Create an Oracle Password File for the Auxiliary Instance
>orapwd file=e:\oracle\product\10.2.0\db_1\database\PWDDUP.ora password=oracle entries=2
Task 2: Establish Oracle Net Connectivity to the Auxiliary Instance
(listener and tnsname configuration)
-add database to the listener using netmanager.
-add servicename to tnsnames file.
Task 3: Create an Initialization Parameter File for the Auxiliary Instance
Task 4: create the service using oradim for auxilary database
>oradim -NEW -SID DPAX -PFILE e:\oracle\product\10.2.0\admin\DUPI\pfile\initdupi.ora -startmode a
Task 5: connect to the auxilary instance
c:\>sqlplus sys/*** as sysdba
connected to an idle instance
sql>startup nomount
.
.
.
Task 6: connect using rman to target and auxilary database.
c:\>set oracle_sid=orcl
c:\>rman target sys/***@orcl
RMAN> connect auxiliary sys/adminkurnia@dpax
connected to auxiliary database: DPAX (not mounted)
RMAN> run
2> {
3> allocate auxiliary channel c1 type disk;
4> set newname for datafile 1 to 'd:\dupdb\SYSTEM01.DBF';
5> set newname for datafile 2 to 'd:\dupdb\UNDOTBS01.DBF';
6> set newname for datafile 3 to 'd:\dupdb\SYSAUX01.DBF';
7> set newname for datafile 4 to 'd:\dupdb\USERS01.DBF';
8> set newname for tempfile 1 to 'd:\dupdb\TEMP01.DBF';
9> duplicate target database to dupdb
10> logfile group 1 ('d:\dupdb\log01.log') size 50m reuse,
11> group 2 ('d:\dupdb\log02.log') size 50m reuse,
12> group 3 ('d:\dupdb\log03.log') size 50m reuse;
13> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=156 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 08-JAN-08
contents of Memory Script:
{
set until scn 340124246;
set newname for datafile 1 to
"D:\DUPDB\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\DUPDB\UNDOTBS01.DBF";
set newname for datafile 3 to
"D:\DUPDB\SYSAUX01.DBF";
set newname for datafile 4 to
"D:\DUPDB\USERS01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 08-JAN-08
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\DUPDB\SYSTEM01.DBF
restoring datafile 00002 to D:\DUPDB\UNDOTBS01.DBF
restoring datafile 00003 to D:\DUPDB\SYSAUX01.DBF
restoring datafile 00004 to D:\DUPDB\USERS01.DBF
channel c1: reading from backup piece E:\BACKUP\LEVEL_0\DEC07\DB_7FJ5IMMP_1_239
channel c1: restored backup piece 1
piece handle=E:\BACKUP\LEVEL_0\DEC07\DB_7FJ5IMMP_1_239 tag=TAG20080107T152928
channel c1: restore complete, elapsed time: 00:11:30
Finished restore at 08-JAN-08
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'd:\dupdb\log01.log' ) SIZE 50 M REUSE,
GROUP 2 ( 'd:\dupdb\log02.log' ) SIZE 50 M REUSE,
GROUP 3 ( 'd:\dupdb\log03.log' ) SIZE 50 M REUSE
DATAFILE
'D:\DUPDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=643466915 filename=D:\DUPDB\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=643466915 filename=D:\DUPDB\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=643466915 filename=D:\DUPDB\USERS01.DBF
contents of Memory Script:
{
set until scn 340124246;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 08-JAN-08
starting media recovery
archive log thread 1 sequence 168 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\LOGARCHIVE\ARC00168_0639404466.001
channel c1: starting archive log restore to default destination
channel c1: restoring archive log
archive log thread=1 sequence=167
channel c1: reading from backup piece E:\BACKUP\ARCHIVEBACKUP\JAN07\ARC_7HJ5IN28_1_241
channel c1: restored backup piece 1
piece handle=E:\BACKUP\ARCHIVEBACKUP\JAN07\ARC_7HJ5IN28_1_241 tag=TAG20080107T153535
channel c1: restore complete, elapsed time: 00:00:16
archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\DUPDB\ARCHIVELOG\2008_01_08\O1_MF_1_167_3R68YJ5C_.ARC thread=1 seq
uence=167
channel clone_default: deleting archive log(s)
archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\DUPDB\ARCHIVELOG\2008_01_08\O1_MF_1_167_3R68YJ5C_.ARC recid=1 stam
p=643466927
archive log filename=E:\ORACLE\PRODUCT\10.2.0\LOGARCHIVE\ARC00168_0639404466.001 thread=1 sequence=168
media recovery complete, elapsed time: 00:00:09
Finished recover at 08-JAN-08
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 92275392 bytes
Database Buffers 188743680 bytes
Redo Buffers 7139328 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'd:\dupdb\log01.log' ) SIZE 50 M REUSE,
GROUP 2 ( 'd:\dupdb\log02.log' ) SIZE 50 M REUSE,
GROUP 3 ( 'd:\dupdb\log03.log' ) SIZE 50 M REUSE
DATAFILE
'D:\DUPDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"d:\dupdb\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "D:\DUPDB\UNDOTBS01.DBF";
catalog clone datafilecopy "D:\DUPDB\SYSAUX01.DBF";
catalog clone datafilecopy "D:\DUPDB\USERS01.DBF";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to d:\dupdb\TEMP01.DBF in control file
cataloged datafile copy
datafile copy filename=D:\DUPDB\UNDOTBS01.DBF recid=1 stamp=643467074
cataloged datafile copy
datafile copy filename=D:\DUPDB\SYSAUX01.DBF recid=2 stamp=643467075
cataloged datafile copy
datafile copy filename=D:\DUPDB\USERS01.DBF recid=3 stamp=643467075
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=643467074 filename=D:\DUPDB\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=643467075 filename=D:\DUPDB\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=643467075 filename=D:\DUPDB\USERS01.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 08-JAN-08
RMAN>
For manual cloning Refer this for step by step document:
***http://forums.oracle.com/forums/thread.jspa?threadID=245436
RMAN> run
2> {
3> allocate channel c1 device type disk format 'D:\test\db_%U';
4> backup database;
5> }
1.a. Create an Oracle Password File for the Auxiliary Instance
>orapwd file=e:\oracle\product\10.2.0\db_1\database\PWDDUP.ora password=oracle entries=2
Task 2: Establish Oracle Net Connectivity to the Auxiliary Instance
(listener and tnsname configuration)
-add database to the listener using netmanager.
-add servicename to tnsnames file.
Task 3: Create an Initialization Parameter File for the Auxiliary Instance
Task 4: create the service using oradim for auxilary database
>oradim -NEW -SID DPAX -PFILE e:\oracle\product\10.2.0\admin\DUPI\pfile\initdupi.ora -startmode a
Task 5: connect to the auxilary instance
c:\>sqlplus sys/*** as sysdba
connected to an idle instance
sql>startup nomount
.
.
.
Task 6: connect using rman to target and auxilary database.
c:\>set oracle_sid=orcl
c:\>rman target sys/***@orcl
RMAN> connect auxiliary sys/adminkurnia@dpax
connected to auxiliary database: DPAX (not mounted)
RMAN> run
2> {
3> allocate auxiliary channel c1 type disk;
4> set newname for datafile 1 to 'd:\dupdb\SYSTEM01.DBF';
5> set newname for datafile 2 to 'd:\dupdb\UNDOTBS01.DBF';
6> set newname for datafile 3 to 'd:\dupdb\SYSAUX01.DBF';
7> set newname for datafile 4 to 'd:\dupdb\USERS01.DBF';
8> set newname for tempfile 1 to 'd:\dupdb\TEMP01.DBF';
9> duplicate target database to dupdb
10> logfile group 1 ('d:\dupdb\log01.log') size 50m reuse,
11> group 2 ('d:\dupdb\log02.log') size 50m reuse,
12> group 3 ('d:\dupdb\log03.log') size 50m reuse;
13> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=156 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 08-JAN-08
contents of Memory Script:
{
set until scn 340124246;
set newname for datafile 1 to
"D:\DUPDB\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\DUPDB\UNDOTBS01.DBF";
set newname for datafile 3 to
"D:\DUPDB\SYSAUX01.DBF";
set newname for datafile 4 to
"D:\DUPDB\USERS01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 08-JAN-08
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\DUPDB\SYSTEM01.DBF
restoring datafile 00002 to D:\DUPDB\UNDOTBS01.DBF
restoring datafile 00003 to D:\DUPDB\SYSAUX01.DBF
restoring datafile 00004 to D:\DUPDB\USERS01.DBF
channel c1: reading from backup piece E:\BACKUP\LEVEL_0\DEC07\DB_7FJ5IMMP_1_239
channel c1: restored backup piece 1
piece handle=E:\BACKUP\LEVEL_0\DEC07\DB_7FJ5IMMP_1_239 tag=TAG20080107T152928
channel c1: restore complete, elapsed time: 00:11:30
Finished restore at 08-JAN-08
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'd:\dupdb\log01.log' ) SIZE 50 M REUSE,
GROUP 2 ( 'd:\dupdb\log02.log' ) SIZE 50 M REUSE,
GROUP 3 ( 'd:\dupdb\log03.log' ) SIZE 50 M REUSE
DATAFILE
'D:\DUPDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=643466915 filename=D:\DUPDB\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=643466915 filename=D:\DUPDB\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=643466915 filename=D:\DUPDB\USERS01.DBF
contents of Memory Script:
{
set until scn 340124246;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 08-JAN-08
starting media recovery
archive log thread 1 sequence 168 is already on disk as file E:\ORACLE\PRODUCT\10.2.0\LOGARCHIVE\ARC00168_0639404466.001
channel c1: starting archive log restore to default destination
channel c1: restoring archive log
archive log thread=1 sequence=167
channel c1: reading from backup piece E:\BACKUP\ARCHIVEBACKUP\JAN07\ARC_7HJ5IN28_1_241
channel c1: restored backup piece 1
piece handle=E:\BACKUP\ARCHIVEBACKUP\JAN07\ARC_7HJ5IN28_1_241 tag=TAG20080107T153535
channel c1: restore complete, elapsed time: 00:00:16
archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\DUPDB\ARCHIVELOG\2008_01_08\O1_MF_1_167_3R68YJ5C_.ARC thread=1 seq
uence=167
channel clone_default: deleting archive log(s)
archive log filename=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\DUPDB\ARCHIVELOG\2008_01_08\O1_MF_1_167_3R68YJ5C_.ARC recid=1 stam
p=643466927
archive log filename=E:\ORACLE\PRODUCT\10.2.0\LOGARCHIVE\ARC00168_0639404466.001 thread=1 sequence=168
media recovery complete, elapsed time: 00:00:09
Finished recover at 08-JAN-08
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 92275392 bytes
Database Buffers 188743680 bytes
Redo Buffers 7139328 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'd:\dupdb\log01.log' ) SIZE 50 M REUSE,
GROUP 2 ( 'd:\dupdb\log02.log' ) SIZE 50 M REUSE,
GROUP 3 ( 'd:\dupdb\log03.log' ) SIZE 50 M REUSE
DATAFILE
'D:\DUPDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"d:\dupdb\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "D:\DUPDB\UNDOTBS01.DBF";
catalog clone datafilecopy "D:\DUPDB\SYSAUX01.DBF";
catalog clone datafilecopy "D:\DUPDB\USERS01.DBF";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to d:\dupdb\TEMP01.DBF in control file
cataloged datafile copy
datafile copy filename=D:\DUPDB\UNDOTBS01.DBF recid=1 stamp=643467074
cataloged datafile copy
datafile copy filename=D:\DUPDB\SYSAUX01.DBF recid=2 stamp=643467075
cataloged datafile copy
datafile copy filename=D:\DUPDB\USERS01.DBF recid=3 stamp=643467075
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=643467074 filename=D:\DUPDB\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=643467075 filename=D:\DUPDB\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=643467075 filename=D:\DUPDB\USERS01.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 08-JAN-08
RMAN>
For manual cloning Refer this for step by step document:
***http://forums.oracle.com/forums/thread.jspa?threadID=245436
Monday, March 23, 2009
Standby Database Configuration in Oracle 10g
check this document from Oracle for Dataguard Configuration and standby database configuration.
*** http://www.oracle.com/technology/pub/articles/smiley-fsfo.html?msgid=7569547
check this link for Switchover from primary to standby :
*** http://www.orafaq.com/node/2078
1.Check primary database in archivelog mode.
2.Alter database force logging.
BACKUP DATABASE
3.RMAN> run
2> {
3> allocate channel c1 device type disk format='E:\bkp\db_%U';
4> backup database;
5> }
allocated channel: c1
channel c1: sid=131 devtype=DISK
Starting backup at 22-FEB-08
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00004 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
input datafile fno=00002 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
channel c1: starting piece 1 at 22-FEB-08
channel c1: finished piece 1 at 22-FEB-08
piece handle=E:\BKP\DB_8GJ9BGMF_1_1 tag=TAG20080222T110903 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:34
Finished backup at 22-FEB-08
Starting Control File and SPFILE Autobackup at 22-FEB-08
piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2008_02_22\O1_MF_S_647349097_3VWR4LQG_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 22-FEB-08
released channel: c1
4. BACKUP CURRENT CONTROLFILE FOR STANDBY
RMAN> run
2> {
3> allocate channel c1 device type disk format='E:\bkp\crtl\ctrl01.ctl';
4> shutdown immediate;
5> startup mount;
6> backup current controlfile for standby;
7> }
allocated channel: c1
channel c1: sid=131 devtype=DISK
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 113246912 bytes
Database Buffers 167772160 bytes
Redo Buffers 7139328 bytes
Starting backup at 22-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 22-FEB-08
channel ORA_DISK_1: finished piece 1 at 22-FEB-08
piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_02_22\O1_MF_NCNNF_TAG20080222T111356_3VWR8YL2_.BKP tag
=TAG20080222T111356 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-FEB-08
Starting Control File and SPFILE Autobackup at 22-FEB-08
piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2008_02_22\O1_MF_S_647349215_3VWR914W_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 22-FEB-08
5. alter database open;
6. CREATED PASSWORD FILE FOR STANDBY DATABASE
>> orapwd file=E:\oracle\product\10.2.0\db_1\database\pwdstdby.ora password=adminkurnia entries=5 << db_unique_name="orcl" log_archive_config="'DG_CONFIG=" log_archive_dest_1="'location=" log_archive_dest_2 ="'SERVICE=" db_unique_name="stdby'" log_file_name_convert="'C:\stdby_db\file\REDO03.LOG','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG','C:\stdby_db\file\REDO02.LOG','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG','C:\stdby_db\file\REDO01.LOG','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'" log_archive_dest_state_1="ENABLE" log_archive_dest_state_2="ENABLE" fal_server="stdby" fal_client="orcl" standby_file_management="AUTO" pfile="'PRIMARY">> TO IMPLEMENT THE CHANGE IN SPFILE << pfile="'">> TO CREATE THE PFILE FOR STANDBY DATABASE << __db_cache_size="205520896" __java_pool_size="4194304" __large_pool_size="4194304" __shared_pool_size="67108864" __streams_pool_size="0" lock_name_space="'stdby'" audit_file_dest="'E:\oracle\product\10.2.0/admin/stdby/adump'" background_dump_dest="'E:\oracle\product\10.2.0/admin/stdby/bdump'" compatible="'10.2.0.1.0'" control_files="'C:\stdby_db\file\control01.ctl','C:\stdby_db\file\control02.ctl','C:\stdby_db\file\control03.ctl'" core_dump_dest="'E:\oracle\product\10.2.0/admin/stdby/cdump'" db_block_size="8192" db_domain="''" db_file_multiblock_read_count="16" db_name="'orcl'" instance_name="'stdby'" db_recovery_file_dest="'C:\stdby_db\flash_recovery_area'" db_recovery_file_dest_size="2147483648" db_unique_name="'stdby'" dispatchers="'(PROTOCOL=" service="orclXDB)'" fal_client="'stdby'" fal_server="'orcl'" job_queue_processes="10" log_archive_config="'DG_CONFIG=" log_archive_dest_1="'location=" log_archive_dest_2="'SERVICE=" db_unique_name="orcl'" log_archive_dest_state_1="'ENABLE'" log_archive_dest_state_2="'ENABLE'" log_file_name_convert="'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG','C:\stdby_db\file\REDO03.LOG','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG','C:\stdby_db\file\REDO02.LOG','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG','C:\stdby_db\file\REDO01.LOG'" open_cursors="300" pga_aggregate_target="95420416" processes="150" remote_login_passwordfile="'EXCLUSIVE'" sga_target="287309824" standby_file_management="'AUTO'" undo_management="'AUTO'" undo_tablespace="'UNDOTBS1'" user_dump_dest="'E:\oracle\product\10.2.0/admin/stdby/udump'">>oradim -new -sid stdby -pfile E:\oracle\product\10.2.0\admin\stdby\pfile\initstdby.ora -startmode a <<> run
2> {
3> allocate auxiliary channel c1 type disk;
4> set newname for datafile 1 to 'C:\stdby_db\file\SYSTEM01.DBF';
5> set newname for datafile 2 to 'C:\stdby_db\file\UNDOTBS01.DBF';
6> set newname for datafile 3 to 'C:\stdby_db\file\SYSAUX01.DBF';
7> set newname for datafile 4 to 'C:\stdby_db\file\USERS01.DBF';
8> set newname for tempfile 1 to 'C:\stdby_db\file\TEMP01.DBF';
9> duplicate target database for standby
10> ;
11> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=157 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 22-FEB-08
contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting restore at 22-FEB-08
channel c1: starting datafile backupset restore
channel c1: restoring control file
channel c1: reading from backup piece E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_02_22\O1_MF_NCNNF_TAG20080222
T111356_3VWR8YL2_.BKP
channel c1: restored backup piece 1
piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_02_22\O1_MF_NCNNF_TAG20080222T111356_3VWR8YL2_.BKP tag
=TAG20080222T111356
channel c1: restore complete, elapsed time: 00:00:05
output filename=C:\STDBY_DB\FILE\CONTROL01.CTL
output filename=C:\STDBY_DB\FILE\CONTROL02.CTL
output filename=C:\STDBY_DB\FILE\CONTROL03.CTL
Finished restore at 22-FEB-08
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"C:\stdby_db\file\TEMP01.DBF";
switch clone tempfile all;
set newname for datafile 1 to
"C:\STDBY_DB\FILE\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\STDBY_DB\FILE\UNDOTBS01.DBF";
set newname for datafile 3 to
"C:\STDBY_DB\FILE\SYSAUX01.DBF";
set newname for datafile 4 to
"C:\STDBY_DB\FILE\USERS01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to C:\stdby_db\file\TEMP01.DBF in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 22-FEB-08
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\STDBY_DB\FILE\SYSTEM01.DBF
restoring datafile 00002 to C:\STDBY_DB\FILE\UNDOTBS01.DBF
restoring datafile 00003 to C:\STDBY_DB\FILE\SYSAUX01.DBF
restoring datafile 00004 to C:\STDBY_DB\FILE\USERS01.DBF
channel c1: reading from backup piece E:\BKP\DB_8GJ9BGMF_1_1
channel c1: restored backup piece 1
piece handle=E:\BKP\DB_8GJ9BGMF_1_1 tag=TAG20080222T110903
channel c1: restore complete, elapsed time: 00:01:55
Finished restore at 22-FEB-08
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=32 stamp=647364559 filename=C:\STDBY_DB\FILE\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy recid=33 stamp=647364560 filename=C:\STDBY_DB\FILE\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=34 stamp=647364560 filename=C:\STDBY_DB\FILE\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=35 stamp=647364560 filename=C:\STDBY_DB\FILE\USERS01.DBF
Finished Duplicate Db at 22-FEB-08
released channel: c1
RMAN>
13.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL>
14.SQL> alter database open read only;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup standby database
SP2-0714: invalid combination of STARTUP options
SQL> startup mount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 75498176 bytes
Database Buffers 205520896 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered.
SQL> select count(*) from navaneeth;
COUNT(*)
----------
13
Reference:
http://www.dbapool.com/articles/043005.html
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm#g88234
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/rcmbackp.htm#i636427
http://www.orafaq.com/node/957
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_apply.htm
*** http://www.oracle.com/technology/pub/articles/smiley-fsfo.html?msgid=7569547
check this link for Switchover from primary to standby :
*** http://www.orafaq.com/node/2078
1.Check primary database in archivelog mode.
2.Alter database force logging.
BACKUP DATABASE
3.RMAN> run
2> {
3> allocate channel c1 device type disk format='E:\bkp\db_%U';
4> backup database;
5> }
allocated channel: c1
channel c1: sid=131 devtype=DISK
Starting backup at 22-FEB-08
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00001 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00004 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
input datafile fno=00002 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
channel c1: starting piece 1 at 22-FEB-08
channel c1: finished piece 1 at 22-FEB-08
piece handle=E:\BKP\DB_8GJ9BGMF_1_1 tag=TAG20080222T110903 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:34
Finished backup at 22-FEB-08
Starting Control File and SPFILE Autobackup at 22-FEB-08
piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2008_02_22\O1_MF_S_647349097_3VWR4LQG_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 22-FEB-08
released channel: c1
4. BACKUP CURRENT CONTROLFILE FOR STANDBY
RMAN> run
2> {
3> allocate channel c1 device type disk format='E:\bkp\crtl\ctrl01.ctl';
4> shutdown immediate;
5> startup mount;
6> backup current controlfile for standby;
7> }
allocated channel: c1
channel c1: sid=131 devtype=DISK
database closed
database dismounted
Oracle instance shut down
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 113246912 bytes
Database Buffers 167772160 bytes
Redo Buffers 7139328 bytes
Starting backup at 22-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 22-FEB-08
channel ORA_DISK_1: finished piece 1 at 22-FEB-08
piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_02_22\O1_MF_NCNNF_TAG20080222T111356_3VWR8YL2_.BKP tag
=TAG20080222T111356 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 22-FEB-08
Starting Control File and SPFILE Autobackup at 22-FEB-08
piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2008_02_22\O1_MF_S_647349215_3VWR914W_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 22-FEB-08
5. alter database open;
6. CREATED PASSWORD FILE FOR STANDBY DATABASE
>> orapwd file=E:\oracle\product\10.2.0\db_1\database\pwdstdby.ora password=adminkurnia entries=5 << db_unique_name="orcl" log_archive_config="'DG_CONFIG=" log_archive_dest_1="'location=" log_archive_dest_2 ="'SERVICE=" db_unique_name="stdby'" log_file_name_convert="'C:\stdby_db\file\REDO03.LOG','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG','C:\stdby_db\file\REDO02.LOG','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG','C:\stdby_db\file\REDO01.LOG','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG'" log_archive_dest_state_1="ENABLE" log_archive_dest_state_2="ENABLE" fal_server="stdby" fal_client="orcl" standby_file_management="AUTO" pfile="'PRIMARY">> TO IMPLEMENT THE CHANGE IN SPFILE << pfile="'">> TO CREATE THE PFILE FOR STANDBY DATABASE << __db_cache_size="205520896" __java_pool_size="4194304" __large_pool_size="4194304" __shared_pool_size="67108864" __streams_pool_size="0" lock_name_space="'stdby'" audit_file_dest="'E:\oracle\product\10.2.0/admin/stdby/adump'" background_dump_dest="'E:\oracle\product\10.2.0/admin/stdby/bdump'" compatible="'10.2.0.1.0'" control_files="'C:\stdby_db\file\control01.ctl','C:\stdby_db\file\control02.ctl','C:\stdby_db\file\control03.ctl'" core_dump_dest="'E:\oracle\product\10.2.0/admin/stdby/cdump'" db_block_size="8192" db_domain="''" db_file_multiblock_read_count="16" db_name="'orcl'" instance_name="'stdby'" db_recovery_file_dest="'C:\stdby_db\flash_recovery_area'" db_recovery_file_dest_size="2147483648" db_unique_name="'stdby'" dispatchers="'(PROTOCOL=" service="orclXDB)'" fal_client="'stdby'" fal_server="'orcl'" job_queue_processes="10" log_archive_config="'DG_CONFIG=" log_archive_dest_1="'location=" log_archive_dest_2="'SERVICE=" db_unique_name="orcl'" log_archive_dest_state_1="'ENABLE'" log_archive_dest_state_2="'ENABLE'" log_file_name_convert="'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG','C:\stdby_db\file\REDO03.LOG','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG','C:\stdby_db\file\REDO02.LOG','E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG','C:\stdby_db\file\REDO01.LOG'" open_cursors="300" pga_aggregate_target="95420416" processes="150" remote_login_passwordfile="'EXCLUSIVE'" sga_target="287309824" standby_file_management="'AUTO'" undo_management="'AUTO'" undo_tablespace="'UNDOTBS1'" user_dump_dest="'E:\oracle\product\10.2.0/admin/stdby/udump'">>oradim -new -sid stdby -pfile E:\oracle\product\10.2.0\admin\stdby\pfile\initstdby.ora -startmode a <<> run
2> {
3> allocate auxiliary channel c1 type disk;
4> set newname for datafile 1 to 'C:\stdby_db\file\SYSTEM01.DBF';
5> set newname for datafile 2 to 'C:\stdby_db\file\UNDOTBS01.DBF';
6> set newname for datafile 3 to 'C:\stdby_db\file\SYSAUX01.DBF';
7> set newname for datafile 4 to 'C:\stdby_db\file\USERS01.DBF';
8> set newname for tempfile 1 to 'C:\stdby_db\file\TEMP01.DBF';
9> duplicate target database for standby
10> ;
11> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=157 devtype=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 22-FEB-08
contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting restore at 22-FEB-08
channel c1: starting datafile backupset restore
channel c1: restoring control file
channel c1: reading from backup piece E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_02_22\O1_MF_NCNNF_TAG20080222
T111356_3VWR8YL2_.BKP
channel c1: restored backup piece 1
piece handle=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_02_22\O1_MF_NCNNF_TAG20080222T111356_3VWR8YL2_.BKP tag
=TAG20080222T111356
channel c1: restore complete, elapsed time: 00:00:05
output filename=C:\STDBY_DB\FILE\CONTROL01.CTL
output filename=C:\STDBY_DB\FILE\CONTROL02.CTL
output filename=C:\STDBY_DB\FILE\CONTROL03.CTL
Finished restore at 22-FEB-08
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"C:\stdby_db\file\TEMP01.DBF";
switch clone tempfile all;
set newname for datafile 1 to
"C:\STDBY_DB\FILE\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\STDBY_DB\FILE\UNDOTBS01.DBF";
set newname for datafile 3 to
"C:\STDBY_DB\FILE\SYSAUX01.DBF";
set newname for datafile 4 to
"C:\STDBY_DB\FILE\USERS01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to C:\stdby_db\file\TEMP01.DBF in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 22-FEB-08
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\STDBY_DB\FILE\SYSTEM01.DBF
restoring datafile 00002 to C:\STDBY_DB\FILE\UNDOTBS01.DBF
restoring datafile 00003 to C:\STDBY_DB\FILE\SYSAUX01.DBF
restoring datafile 00004 to C:\STDBY_DB\FILE\USERS01.DBF
channel c1: reading from backup piece E:\BKP\DB_8GJ9BGMF_1_1
channel c1: restored backup piece 1
piece handle=E:\BKP\DB_8GJ9BGMF_1_1 tag=TAG20080222T110903
channel c1: restore complete, elapsed time: 00:01:55
Finished restore at 22-FEB-08
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=32 stamp=647364559 filename=C:\STDBY_DB\FILE\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy recid=33 stamp=647364560 filename=C:\STDBY_DB\FILE\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=34 stamp=647364560 filename=C:\STDBY_DB\FILE\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=35 stamp=647364560 filename=C:\STDBY_DB\FILE\USERS01.DBF
Finished Duplicate Db at 22-FEB-08
released channel: c1
RMAN>
13.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> recover managed standby database cancel;
Media recovery complete.
SQL>
14.SQL> alter database open read only;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup standby database
SP2-0714: invalid combination of STARTUP options
SQL> startup mount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 75498176 bytes
Database Buffers 205520896 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;
Database altered.
SQL> select count(*) from navaneeth;
COUNT(*)
----------
13
Reference:
http://www.dbapool.com/articles/043005.html
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm#g88234
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/rcmbackp.htm#i636427
http://www.orafaq.com/node/957
http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_apply.htm
Subscribe to:
Posts (Atom)