Standby Switchover demo steps

1.Script for taking backup of the primary database:
run
{
allocate channel c1 device type disk format 'd:\backup\%d_DATA_%U_%T';
allocate channel c2 device type disk format 'd:\backup\%d_DATA_%U_%T';
allocate channel c3 device type disk format 'd:\backup\%d_DATA_%U_%T';
backup database plus archivelog;
release channel c1;
release channel c2;
release channel c3;
allocate channel c1 type disk format 'D:\backup\%D_CRTL_%U_%T';
backup current controlfile for standby;
release channel c1;
}
2. Switchover the primary database to the standby mode.


3. Bringing the new standby database to the recovery mode


4. Switchover the old standby database to primary mode



5. Configure the standby archive destination in the new primary database:

Move files in windows

The below script runs continously and move files every five minutes from 1 location to another location.

:move_archives
echo %date%, %time% >> D:\move_archives.log
move D:\oradata\archive\*.* E:\ORADATA\archive >> D:\move_archives.log
echo %date%, %time% >> D:\move_archives.log
sleep 1800
goto move_archives

Database connectivity from Oracle to SQL server

By using ODBC connection:

Step 1:

Create a DSN (eg. MSSQL) entry in “/etc/odbc.ini” file by providing the below details

Description = "Description of the connection user identification"
Driver = "Driver path for SQL server"
Server = "SQL Server database server name>"
Port = 5432 "this is default port number"
User = "username for connecting the sql server database"
Password = "password for connect the sql server database"
Sid = "SQL server database name"
Trace = No "default value"
TraceFile = null "default value"
Database = "Database name"
Servername = "Server name were the SQL server database exists"
Username = "database username"

Step 2:

Copy inithsodbc.ora file in ORACLE_HOME \hs\admin folder as initMSSQL.ora file and modify entries like

HS_FDS_CONNECT_INFO = MSSQL --> DSN Name which we created using ODBC
HS_FDS_TRACE_LEVEL = OFF



Step 3:

Add one more entry to SID_List_Listener in Listener.ora file in ORACLE_HOME\Network\Admin folder like the below

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = MSSQL) à DSN Name used as SID of SQL server
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = hsodbc)
)
)

Step 4:

Now create a TNS entry as the following in TnsNames.ora file in Oracle_Home\Network\Admin

MSSQL =
(DESCRIPTION=
(CONNECT_DATA=(SID=MSSQL))
(ADDRESS = (PROTOCOL = TCP)(HOST = C202878)(PORT = 1521))
(HS=OK)
)

Step 5:

Reload the listener.

Step 6:

Try tnspinging the sql server SID and check for the response.

U:\>tnsping MSSQL

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 25-SEP-2009 14:56:26

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

Used parameter files:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (CONNECT_DATA=(SID= MSSQL)) (ADDRESS = (PROTOCOL = TCP)(HOST = local host)(PORT = 1521)) (HS=OK))
OK (30 msec)

Step 7:

Create DB link using the above SID and try connecting.


By Using Oracle Transparent Gateways for Microsoft SQL server:

Step 1:

Install Transparent Gateway for Microsoft SQL server.

While installing it will ask for the server name and MS SQL server database name .Give the Server name where the SQL server database exists and the database name which you are planning to connect.

Installing this component will create a folder “tg4msql” in under ORACLE_HOME location.
Inside this we will have some configuration files inside “D:\oracle\product\10.2.0\db_1\tg4msql\admin” folder.

Optional (only needed when configuring for multiple SQL servers)
Take a copy of inittg4msql.ora file in the ORACLE_HOME\tg4msql\admin folder and change the name of file as init<>.ora where <> is the SID Name given to the Transparent Gateway (eg. initORCL_SqlSer.ora)



Step 2:

Add one more entry to SID_List_Listener in Listener.ora file in ORACLE_HOME\Network\Admin folder like the below


SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = tg4msql) à SID for SQL Server connectivity in Listener.ora
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = tg4msql) à Identifies transparent gateway for MS SQL Server
)
)

Step 3:

Now create a TNS entry as the following in TnsNames.ora file in Oracle_Home\Network\Admin

tg4msql =
(DESCRIPTION=
(CONNECT_DATA=(SID= tg4msql )) à SID of the MSQL server database.
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1521))
(HS=OK) à Need to identify the connection as a heterogeneous connection
)


Step 4:

Reload the listener.

Step 5:

Try tnspinging the sql server SID and check for the response.

U:\>tnsping tg4msql

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 25-SEP-2009 14:56:26

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

Used parameter files:
D:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (CONNECT_DATA=(SID= tg4msql)) (ADDRESS = (PROTOCOL = TCP)(HOST = local host)(PORT = 1521)) (HS=OK))
OK (30 msec)

Step 6:

Create DB link using the above SID and try connecting.

redo apply

You can specify that Redo Apply runs as a foreground session or as a background process, and enable it with real-time apply.
To start Redo Apply in the foreground, issue the following SQL statement:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
If you start a foreground session, control is not returned to the command prompt until recovery is canceled by another session.
To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
This statement starts a detached server process and immediately returns control to the user. While the managed recovery process is performing recovery in the background, the foreground process that issued the RECOVER statement can continue performing other tasks. This does not disconnect the current SQL session.
To start real-time apply, include the USING CURRENT LOGFILE clause on the SQL statement. For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/log_apply.htm

Oracle installation in Linux

installation 11g in linux:
http://ivan.kartik.sk/oracle/install_ora11gR1_elinux.html#st1
http://ivan.kartik.sk/oracle/
http://ivan.kartik.sk/oracle/install_ora10gR2_redhat.html

Kernel Version:
http://ocpdba.wordpress.com/2009/06/03/requirements-for-installing-oracle-10gr2-on-rheloel-5-x86/

http://agstamy.blogspot.com/2008/11/oracle-10gr2-installation-on-rhel5.html

Refer for ASM:

http://forums.oracle.com/forums/thread.jspa?messageID=3138599&#3138599


Packages RPM:

http://forums.oracle.com/forums/thread.jspa?messageID=2500349&#2500349

ORA-20000: ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid

Errors in file d:\oracle\product\10.2.0\admin\db\bdump\db1_j000_4964.trc:
ORA-12012: error on auto execute of job 102622
ORA-20000: ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at line 6
Note:This will occur, If there is an old tablespace dropped and that information is still stored in the dictionary.
Solution:
Basically, this job fails If there is an old tablespace dropped and that information is still stored in the dictionary.Here is how you get rid of it:Ex:sqlplus "/as sysdba"SQL*Plus: Release 10.2.0.2.0 - Production on Thu Jul 17 18:45:18 2008Copyright (c) 1982, 2005, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsSQL> select distinct tablespace_name from DBA_AUTO_SEGADV_CTL;TABLESPACE_NAME
TEST1TEST2test1_oldTEST34 rows selected.SQL> select tablespace_name from dba_tablespaces;TABLESPACE_NAME
TEST1TEST2TEST33 rows selected.SQL> select count(*) from DBA_AUTO_SEGADV_CTL where tablespace_name = 'test1_old';COUNT(*)
1SQL> select segment_owner, segment_name, status from DBA_AUTO_SEGADV_CTL where tablespace_name='test1_old';SEGMENT_OWNER SEGMENT_NAME STATUS
----------------------
BEING_PROCESSEDSQL> delete DBA_AUTO_SEGADV_CTL where tablespace_name='test1_old';1 row deleted.SQL> commit;Commit complete.Now execute the auto_space_advisor_job SQL> exec dbms_space.auto_space_advisor_job_proc;PL/SQL procedure successfully completed.
Reference:http://forums.oracle.com/forums/thread.jspa?threadID=356704&start=0&tstart=0

Performing Switchover from the primary to standby

Refer :
http://kb.dbatoolz.com/tp/2693.create_oracle_10gr2_standby_database_using_real_time_apply.html

Prerequisites to check before roles transition

1. Check the standby redolog files

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
3 1 16 NO ACTIVE
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED

2. Shows duration details on standby

SQL> COLUMN NAME FORMAT A18
SQL> COLUMN VALUE FORMAT A16
SQL> COLUMN TIME_COMPUTED FORMAT A24
SQL> SELECT * FROM V$DATAGUARD_STATS;
NAME VALUE TIME_COMPUTED
------------------ ---------------- ------------------------
apply finish time +00 00:00:02.4 15-MAY-2005 10:32:49
second(1)
interval
apply lag +00 0:00:04 15-MAY-2005 10:32:49
second(0)
interval
transport lag +00 00:00:00 15-MAY-2005 10:32:49
second(0)
interval

Switchovers Involving a Physical Standby Database

A switchover must be initiated on the current primary database and completed on the target standby database. The following steps describe how to perform a switchover.

Step 1 Verify it is possible to perform a switchover.

On the current primary database, query the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the primary database to verify it is possible to perform a switchover. For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
The TO STANDBY value in the SWITCHOVER_STATUS column indicates that it is possible to switch the primary database to the standby role. If the TO STANDBY value is not displayed, then verify the Data Guard configuration is functioning correctly (for example, verify all LOG_ARCHIVE_DEST_n parameter values are specified correctly).
If the value in the SWITCHOVER_STATUS column is SESSIONS ACTIVE, perform the steps described in Section A.4, "Problems Switching Over to a Standby Database" to identify and terminate active user or SQL sessions that might prevent a switchover from being processed. If, after performing these steps, the SWITCHOVER_STATUS column still displays SESSIONS ACTIVE, you can successfully perform a switchover by appending the WITH SESSION SHUTDOWN clause to the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY statement described in Step 2.

Step 2 Initiate the switchover on the primary database.

To change the current primary database to a physical standby database role, use the following SQL statement on the primary database:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
After this statement completes, the primary database is converted into a standby database. The current control file is backed up to the current SQL session trace file before the switchover. This makes it possible to reconstruct a current control file, if necessary.

Step 3 Shut down and restart the former primary instance.

Shut down the former primary instance, and restart and mount the database:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
At this point in the switchover process, both databases are configured as standby databases (see Figure 7-2).

Step 4 Verify the switchover status in the V$DATABASE view.

After you change the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, you should verify if the switchover notification was processed by the target standby database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the target standby database.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY

If the value in the SWITCHOVER_STATUS column is SESSIONS ACTIVE, perform the steps described in Section A.4, "Problems Switching Over to a Standby Database" to identify and terminate active user or SQL sessions that might prevent a switchover from being processed. If, after performing these steps, the SWITCHOVER_STATUS column still displays SESSIONS ACTIVE, you can proceed to Step 5, and append the WITH SESSION SHUTDOWN clause to the switchover statement. See Oracle Database Reference for information about other valid values for the SWITCHOVER_STATUS column of the V$DATABASE view

Step 5 Switch the target physical standby database role to the primary role.

You can switch a physical standby database from the standby role to the primary role when the standby database instance is either mounted in Redo Apply mode or open for read-only access. It must be in one of these modes so that the primary database switchover request can be coordinated. After the standby database is in an appropriate mode, issue the following SQL statement on the physical standby database that you want to change to the primary role:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Step 6 Finish the transition of the standby database to the primary role.

The task you perform is dependent on if the physical standby database has ever been opened in read-only mode:

If the physical standby database has not been opened in read-only mode since the last time it was started, issue the SQL ALTER DATABASE OPEN statement to open the new primary database:

SQL> ALTER DATABASE OPEN;
Then, go to step 7.
If the physical standby database has been opened in read-only mode since the last time it was started, you must shut down the target standby database and restart it:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
The target physical standby database has now undergone a transition to the primary database role. See Section 5.4.1, "Specifying Role-Based Destinations with the VALID_FOR Attribute" and Chapter 14, "LOG_ARCHIVE_DEST_n Parameter Attributes" for information about using the LOG_ARCHIVE_DEST_n VALID_FOR attribute to ensure the Data Guard configuration operates properly after a role transition.
Note: There is no need to shut down and restart other standby databases (not involved in the switchover) that are online at the time of the switchover. These standby databases will continue to function normally after the switchover completes.

Step 7 If necessary, restart log apply services on the standby databases.
For the new physical standby database and for each other physical or logical standby database in the Data Guard configuration, if log apply services were not previously configured to continue operating through a switchover, use an appropriate command to restart log apply services. See Chapter 6, "Log Apply Services" for more information about how to configure and start log apply services.

Step 8 Begin sending redo data to the standby databases.
Issue the following statement on the new primary database:

SQL> ALTER SYSTEM SWITCH LOGFILE;
Refer: http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/role_management.htm#i1033702

Flashback Database Technology Article

FLASHBACK RECOVERY

Introduction:

Flashback recovery feature is used to rollback the database to the prior point in time or system change number. Flashback Database is a new approach to point-in-time (PIT) database recovery. This incomplete recovery strategy can be used to recover a database that has been logically corrupted due to human error. Introduced in 10g, it is designed to maximize availability by reducing recovery time.

Incomplete Recovery:

Incomplete recovery is recovery of the database to a prior state. There are two steps to this process: restoring data and forward-recovering transaction activity to a desired time. The major difference between traditional recovery and Flashback Database is that traditional recovery begins by restoring all data files prior to the desired recovery time while Flashback Database works backwards by restoring blocks changed after the corruption. To put this in perspective, let's consider a 10 TB database with 1 MB of corruption. Traditional recovery begins by restoring 10 TB of application data while Flashback Database backs out 1 MB of application data to arrive at a point before the corruption. Restoring all data files from a backup and rolling forward redo logs to the desired point-in-time accomplish recovery. Recovery time is proportional to the size of the database, not the amount of changes that need to be backed out. The mean time to recover (MTTR) actually increases as the database grows in size. But in this feature the time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.

Pre-requisites:
You must have either the FLASHBACK ANY TABLE or the more specific FLASHBACK object privilege on the table you want to recover.
Database must be in Archivelog mode to enable Flashback.
The feature is enabled in database MOUNT EXCLUSIVE mode.
The following parameters must be set before enabling the feature

• DB_FLASHBACK_RETENTION_TARGET
• DB_RECOVERY_FILE_DEST
• DB_RECOVERY_FILE_DEST_SIZE.

Configuring Flashback Database:

Flash Recovery Area:
FRA is a disk location for holding recovery-related files. For Flashback Database, a new background process called Recovery Writer (RVWR) periodically writes before images of data blocks from the SGA flashback buffer to disk as Flashback Logs in the FRA. Flashback Logs are automatically managed within the FRA by Oracle. The cost of Flashback Logs is measured in space and performance. Space will be a factor of the write intensity of the database.

Alter system set db_recovery_file_dest=
'C:\oracle\product\10.2.0\flash_recovery_area' scope=both;

Alter system set db_recovery_file_dest_size = 10G scope=both;

Flashback Retention Target:
This initialization parameter, measured in minutes, represents how many days we need to keep the logs. After that period, the files are automatically deleted if more space is required. Its value translates into the amount and duration of Flashback Logs in the FRA. It is important to understand that this retention is not guaranteed. If the FRA is in need of space, Flashback Logs may be automatically deleted before their target retention. With the retention set, Flashback Database can be enabled.

SQL> startup mount;
Database mounted.
SQL> alter system set db_flashback_retention_target = 1440 scope=both;
System altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.

Flashback Logs:
Database retains a copy of the modified database blocks in the logs in
Flashback recovery area. Flashback logs have a file extension of .FLB. The logs are automatically deleted when space needed.

Flashback Technology Levels:

• Flashback database enables you to take the entire database to a past point in time (using flashback logs).
• Flashback drop lets you retrieve accidentally dropped tables and indexes (using the recycle bin).
• Flashback table lets you recover a table to a time in the past (using undo data).
• Flashback query lets you query and restore data rows to a point in time (using undo data). (Select * from emp as of timestamp to_timestamp (.))
• Flashback versions query The Flashback Versions Query feature enables you to use the VERSIONS clause to retrieve all the versions of rows that exist between two points in time or between two SCNs.
• Flashback Transaction query The flashback transaction query table shows the DML statements that used against the tables.

Advantages:

• Restoring data files from backup sets and image copies are not needed. Therefore the recovery is faster.
• The database automatically deletes obsolete logs and backup sets when space is required.
• Recovery related files become obsolete when they exceed the RMAN retention policy or have been backed up to tape.
• Can easily re-flash more than once if needed.
• If you determine that you flashed back too far into the past, you can use redo logs to roll forward.
• We can exclude the particular tablespace to be part of these Flashback operations.

Disadvantages:

• Transaction rate on the database will have an affect on performance of creating the logs.
• If a control file has been restored or re-created during the time span you want to flashback over, you can’t use the Flashback Database feature.
• You can’t flashback a database to before a RESETLOGS operation.
• You can’t flashback a data file that was dropped or shrunk during the time span covered by the flashback table operation.

Conclusion:
This feature is the best strategy to reduce recovery time. We've seen this technology is easy to use, faster than traditional recovery and best of all, it's free! I hope that Flashback Database is an essential component of availability architecture.

References:
http://www.skillbuilders.com/download/download-sample.cfm?course=ora10g-nfo&file=Ora10g_New_Features_Sample_V2.1.pdf
http://www.globusz.com/ebooks/Oracle/00000019.htm
http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta032.htm
http://www.oracle.com/technology/pub/articles/10gdba/week9_10gdba.html http://www.databasejournal.com/features/oracle/article.php/3446681
http://www.dbazine.com/olc/olc-articles/liu6
http://sysdba.wordpress.com/2006/01/23/flashback-any-error-with-oracle-10g-release-2/

ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors;ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTD

E:\export>expdp "'sys/*******@dbname as sysdba'" parfile=E:\export\expdp.par

Export: Release 10.2.0.4.0 - Production on Tuesday, 30 June, 2009 10:56:10

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 842
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"

Tried to compile that package and got the below error.

PACKAGE BODY SYS.DBMS_INTERNAL_LOGSTDBY On line: 3583 PL/SQL: ORA-00942: table or view does not exist

So tried to find which objects it is using/dependent with the below query.

SELECT owner, object_type, object_name, object_id, status
FROM SYS.dba_objects
where object_name = 'DBMS_INTERNAL_LOGSTDBY';

SELECT B.*
FROM PUBLIC_DEPENDENCY A, SYS.DBA_OBJECTS B
WHERE A.OBJECT_ID = 7331 AND A.REFERENCED_OBJECT_ID = B.OBJECT_ID AND OBJECT_TYPE = 'TABLE'
ORDER BY OWNER, OBJECT_NAME;

OBJECT_NAME

AUDIT_ACTIONS
COL$
DUAL
IND$
INDPART$
JOB$
OBJ$
TAB$
TS$
USER$
LOGMNR_DICTSTATE$
LOGMNR_LOG$
LOGMNR_SESSION$
LOGMNR_UID$
LOGSTDBY$APPLY_MILESTONE
LOGSTDBY$EVENTS
LOGSTDBY$HISTORY
LOGSTDBY$PARAMETERS
LOGSTDBY$SCN
LOGSTDBY$SKIP
LOGSTDBY$SKIP_TRANSACTION

Found that one table is missing by comparing the package with other database which is same version 10.2.0.4.

Table LOGSTDBY$SKIP_SUPPORT is missing in the system schema which the package is refering.

Created the script for that missing table from the database and created in the other database which is giving error.

The problem solved and export completed sucessfully with no errors.


reference:
------------

http://pandazen.wordpress.com/2009/01/03/cannot-drop-jobs-because-sysdbms_internal_logstdby-error/

http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=32765 - solution

Rename database using nid

Shutdown immediate

startup mount

D:\>nid target=sys/database@dba dbname=ORCL setname=yes

DBNEWID: Release 10.2.0.1.0 - Production on Thu Jun 18 2009

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

Connected to database DBA (DBID=1149114175)

Connected to server version 10.2.0

Control Files in database:
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\CONTROL01.CTL
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\CONTROL02.CTL
D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\CONTROL03.CTL

Change database name of database DBA to ORCL? (Y/[N]) => Y

Proceeding with operation
Changing database name from DBA to ORCL
Control File D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\CONTROL01.CTL - modified
Control File D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\CONTROL02.CTL - modified
Control File D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\CONTROL03.CTL - modified
Datafile D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\SYSTEM01.DBF - wrote new name
Datafile D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\UNDOTBS01.DBF - wrote new name
Datafile D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\SYSAUX01.DBF - wrote new name
Datafile D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\USERS01.DBF - wrote new name
Datafile D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\TEMP01.DBF - wrote new name
Control File D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\CONTROL01.CTL - wrote new name
Control File D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\CONTROL02.CTL - wrote new name
Control File D:\ORACLE\PRODUCT\10.2.0\ORADATA\DBA\CONTROL03.CTL - wrote new name
Instance shut down

Database name changed to ORCL.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.


D:\>orapwd
Usage: orapwd file= password= entries= force=

where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBA and force - whether to overwrite existing file (opt),
OPERs (opt),
There are no spaces around the equal-to (=) character.

D:\>orapwd file=D:\oracle\product\10.2.0\db\database\PWDorcl.ora password=database

D:\>sc delete OracleServiceDBA

D:\>oradim -new -sid orcl -pfile 'D:\oracle\product\10.2.0\admin\dba\pfile\init.ora.5182009184446'

D:\>set oracle_sid=orcl

D:\>lsnrctl

LSNRCTL> reload

LSNRCTL> exit

D:\>sqlplus

SQL> startup mount pfile='D:\oracle\product\10.2.0\admin\dba\pfile\init.ora.5182009184446';

SQL> create spfile from pfile='D:\oracle\product\10.2.0\admin\dba\pfile\init.ora.5182009184446';

SQL> shutdown immediate

SQL> startup

SQL> select name from v$database;
Reference: http://www.oracle-base.com/articles/9i/DBNEWID.php

File transfer using dbms_file_transfer between servers

Steps:
create directory a as 'D:\backup_log\'
CREATE DATABASE LINK "EDU.REGRESS.RDBMS.DEV.US.ORACLE.COM"
CONNECT TO NV
IDENTIFIED BY *******
USING 'EDD';
grant read,write on directory a to nv1
exec dbms_file_transfer.GET_FILE('t1','test.txt','edu','a','test.txt');

>> t1 is the directory created on the source machine.
>> test.txt is the file placed in the source directory location.
>> edu is the database link created between the source and destination database servers.
>> 'a' is the directory object created on the destination database server as shown above.
>> test.txt is the file that is to be copied to the destination server.

Reconfiguring Database console After configuring Loopback adapter on windows

To install a loopback adapter on Windows XP:
1. Open the Windows Control Panel.
2. Double-click Add Hardware to start the Add Hardware wizard.
3. In the Welcome window, click Next.
4. In the Is the hardware connected? window, select Yes, I have already connected the hardware, and click Next.
5. In the The following hardware is already installed on your computer window, in the list of installed hardware, select Add a new hardware device, and click Next.
6. In the The wizard can help you install other hardware window, select Install the hardware that I manually select from a list, and click Next.
7. From the list of hardware types, select the type of hardware you are installing window, select Network adapters, and click Next.
8. In the Select Network Adapter window, make the following selections:
· Manufacturer: Select Microsoft.
· Network Adapter: Select Microsoft Loopback Adapter.
9. Click Next.
10. In the The wizard is ready to install your hardware window, click Next.
11. In the Completing the Add Hardware Wizard window, click Finish.

Reconfiguring DB console :
d:\>emca -config dbcontrol db -repos recreate

STARTED EMCA at May 22, 2009 5:34:53 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: orcl
Database Control is already configured for the database orcl
You have chosen to configure Database Control for managing the database orcl
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ D:\oracle\product\10.2.0\db_1

Database hostname ................ nav.in.Nv.com
Listener port number ................ 1521
Database SID ................ orcl
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
May 22, 2009 5:35:31 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollogs\emca\orcl\emca_2009-05-22_05-34-53-PM.log.
May 22, 2009 5:35:33 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
May 22, 2009 5:36:17 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
May 22, 2009 5:38:17 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
May 22, 2009 5:38:17 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
May 22, 2009 5:40:27 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
May 22, 2009 5:40:32 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
May 22, 2009 5:41:12 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
May 22, 2009 5:41:12 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>>
Enterprise Manager configuration completed successfully
FINISHED EMCA at May 22, 2009 5:41:12 PM

Export and import the schema into the same database with different name

Oracle 10.2.0
Exported "N" schema in the database "BL".
"N" Schema details.
* Schema's default tablespace is "N_data".
* Schema has its indexes in "N_index" tablespace.
* Schema has LOB objects. And its size around 8 GB.
> Exp N/Passwd file='e:\n_exp.dmp' owner=N statistics=none buffer=314572800
exported sucessfully without warnings.
Import steps :
* Try to create a user named "NV" and created a new tablespace "NV_data" and assigned as the default tablespace.
* Created the tablespace named "NV_index" for indexes and assigned Unlimited tablespace quota to user "NV".
* Granted the same roles(Connect,resource,DBA) to the newly created user.
* Tried to import
> Imp Nv/passwd file='e:\n_exp.dmp' fromuser=n touser=nv statistics=none buffer=314572800

Objects imported into the NV user, but it used 'N_data' tablespace to create the objects.
And i tried to revoke the unlimited tablespace privilege from the user "NV".
And granted unlimited tablespace privilege on the tablespace "NV_data" to "NV" user.
Import completed sucessfully. But the index was only created in the "NV_data" tablespace later i moved the indexes to Nv_index" tablespace.

Refer :

http://forums.oracle.com/forums/thread.jspa?threadID=899752&tstart=285

Flashback Transaction Query

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

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.

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';
}

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 ...

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.

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

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