Steps for Migrating database from Normal file system to ASM

Steps Migration of database from Normal filesystem to ASM:
Set the environment variable to proper database : export ORACLE_SID=testdb;
Run the /home/oracle/db_data_files.sql to get the list of files used by database which are on NAS filesystems and save the output
Make sure enable archivelog of the database
SQL> archive log list;
SQL>shutdown immediate;
Edit the init.ora parameter with the following
*.log_archive_dest_1='+FRA_01'
*.log_archive_format='testdb_%t_%r_%s.arc' (make sure change to corresponding db name)
SQL> startup mount
SQL> alter database archivelog;
Edit the init.ora parameter with the following
*.db_create_file_dest = '+DATA_01'
*.db_recovery_file_dest = '+FRA_01'
*.db_recovery_file_dest_size = 8G
*.control_files = '+DATA_01', '+FRA_01'
( make sure backup old parameter file and comment old controlfile parameter)

Shudown the database

SQL> Shutdown immediate

Startup database with nomount
SQL> STARTUP NOMOUNT

1. From an RMAN session, copy one of your controlfiles from the local file system to its new location in ASM. The new controlfile will be copied to the value specified in the initialization parameter control_files:
RMAN > connect target /
RMAN> restore controlfile from '/data/8ps/test/data/testdb/control_01.dbf';
2. From an RMAN or SQL*Plus session, mount the database. This will mount the database using the controlfile stored in ASM:
RMAN> ALTER DATABASE MOUNT;
3. From an RMAN session, copy the database files from the local file system to ASM:
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA1_01';

NOTE: Since this step takes longtime run the following script in nohup mode

cat /home/oracle/copy_to_asm.ksh

#!/bin/ksh
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

rman << EOF
connect target /
run {
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
backup as copy database format '+DATA_01';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1;
}

exit;
EOF

nohup /home/oracle/copy_to_asm.ksh &

4. From an RMAN session, update the control file / data dictionary so that all database files point to the RMAN copy made in ASM:
rman target /
Rman > SWITCH DATABASE TO COPY;


5. From a SQL*Plus session, perform incomplete recovery and open the database using the RESETLOGS option:
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

NOTE: here you may not have anything to recover if changes are not many

SQL> ALTER DATABASE OPEN RESETLOGS;

6. From a SQL*Plus session, re-create any tempfiles that are still currently on the local file system to ASM. This is done by simply dropping the tempfiles from the local file system and re-creating them in ASM. This example relies on the initialization parameter db_create_file_dest=+DATA_01:
SQL> select tablespace_name, file_name, bytes from dba_temp_files;

SQL> alter database tempfile '/data/8ps/test/data/testdb/ts_temp_01.dbf' drop including datafiles;
SQL> alter tablespace temp add tempfile size 512M;



7. From a SQL*Plus session, re-create any online redo logfiles that are still currently on the local file system to ASM. This is done by simply dropping the logfiles from the local file system and re-creating them in ASM. This example relies on the initialization parameters db_create_file_dest=+DATA_01 and db_recovery_file_dest=+FRA_01:

a. Determine the current online redo logfiles to move to ASM by examining the file names (and sizes) from V$LOGFILE:
SQL> col member format a50
select a.group#, a.member, b.bytes
from v$logfile a, v$log b where a.group# = b.group#;
b. Force a log switch until the last redo log is marked "CURRENT" by issuing the following command:
SQL> select group#, status from v$log;
SQL> alter system switch logfile;
SQL> alter system switch logfile;

SQL> select group#, status from v$log;

c. After making the last online redo log file the CURRENT one, drop the first online redo log:
SQL> alter database drop logfile group 1;

d. Re-create the dropped redo log group in ASM (and a different size if desired):
SQL> alter database add logfile group 1 size 250m;

e. After re-creating the first online redo log group, loop back to drop / re-create the next online redo logfile until all logs are rebuilt in ASM.
Verify all online redo logfiles have been created in ASM:
SQL> col member format a50
select a.group#, a.member, b.bytes
from v$logfile a, v$log b where a.group# = b.group#;

8. Verify all the database files have been migrated to ASM

SQL> @/home/oracle/db_data_files_all.sql




9. Shutdown the database and edit the init.ora file to change controlfiles parameter to add both files

SQL> shutdown immediate

*.control_files = '+DATA_01/testdb/controlfile/current.257.754062863','+FRA_01/TESTDB/CONTROLFILE/current.259.754067365'




10. Startup the database

SQL> startup

what is channel and its functions and its characteristics in RMAN?

What is a channel?

An RMAN channel represents one stream of data to a device type, and corresponds to one server session. Most backup and recovery commands in RMAN are executed by server sessions. As illustrated above figure.Each channel establishes a connection from the RMAN client to a target or auxiliary database instance by starting a server session on the instance. The server session performs the backup, restore, and recovery.



There are two ways of Channel allocation.

i)Automatic Channel allocation
ii)Manual Channel allocation.

i)Automatic channel Allocation:
This can be allocated through following command.
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2G;
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK RATE 1900K;

ii)Manual Channel Allocation:
This can be allocated through following command.
In the Run block.. Allocated the channel..
Allocate channel c1 device type disk format '/oracle/g01/backup/';
Allocate channel for maintenance;

You can override automatic channel allocation settings by manually allocating channels within a RUN block. Manual channels always override automatic channels. For example, you override automatic channel allocation when you issue a command as follows:

RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
BACKUP DATABASE PLUS ARCHIVELOG;
}


You can change a parallelism setting by issuing another CONFIGURE DEVICE TYPE ... PARALLELISM command. This example configures PARALLELISM 2 and then changes it to 3:

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;


You can view the default setting for parallelism by running the SHOW DEVICE TYPE command. For example:

RMAN> SHOW DEVICE TYPE;
RMAN configuration parameters are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; #default

how to place backup dumps to different mount points(disks) through Rman.

Hi All,

This Article will be useful if you have space issue in single disk(mount point).

How to configure RMAN to backup to different disks(mount points).


You need to configure two channels for two mountpoints


Below are the steps.


step i)RMAN> configure channel 1 device type disk format '/d01/stage1/%U.bak';

step ii)RMAN>configure channel 2 device type disk format '/d01/stage2/%U.bak';

step iii)RMAN>CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

step iv)RMAN> configure default device type to disk;

step v)RMAN> backup database;

Then you can verify the backup dumps. It will placed in both stage1 and stage2 mountpoints

ls -l /d01/stage?

or you can write a programme and run the below script.

run { allocate channel c1 type disk maxpiecesize=60m format =
'/d01/stage1/%U.bak';
allocate channel c2 type disk maxpiecesize=60m format =
'/d01/stage2/%U.bak';
backup tablespace example;
}




I hope this helps.. :)

HOW TO CREATE WALLET FOR TDE(ASO)

STEP 1:CREATE THE WALLET DIRECTORY:
===================================
mkdir -p /oracle/dbtools/TDE/10.2.0/wallet

STEP 2:CHANGE THE WALLET PERMISSIONS:
====================================
chmod 0700 wallet

STEP 3:EDIT THE SQLNET.ORA FILE:
===============================
SQLNET.EXPIRE_TIME =5

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /oracle/dbtools/TDE/10.2.0/wallet/)))

STEP 4:STOP & START THE LISTENER:
=========================
LSNRCTL STOP LISTENERNAME & LSNRCTL START LISTENER NAME

STEP 5:CREATE THE ENCRYPTION KEY:
=================================
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "********";

STEP 6:CONFIRM WHETHER ENCRYPTION KEY IS CREATED IN WALLET DIRECTORY:
====================================================================

STEP 7:OPEN THE WALLET IF ITS NOT OPEN:

alter system set wallet open identified by "********";

EXECUTE TO PARSE IS LOW

EXECUTE TO PARSE EXPLANATION:
*****************************

Execute to parse ratio is a measure of how many times we execute a sql statement versus parse it.

This 'ratio' will go towards 100 as the number of executes goes up and up, while the number of parses remains the same.

It will go to zero as the number of parses and executes are equal

It'll go negative if you parse more than you execute.

This happens when an application parses a query but never executes it. Some applications
(generic apps typically) parse a "select * from T" to describe a table, never execute the
cursor -- their parses exceed their executes.

So:

Below will have a parse/execute ratio near 100

parse insert into t values ( :x )
for i in 1 .. 100000
loop
bind i
execute insert
end loop


Below will have a parse/execute ration near 0

for i in 1 .. 100000
loop
parse insert into t values ( :x );
bind i
execute insert
close insert
end loop


The only way to influence that number is to either change

a) the number of times you parse.
b) the number of times you execute.Its in the Hand of Application Developer. But we can consider the Below Points.

i)We can use as much as PL/SQL than SQL for this issue only. Since PL/SQL cache itself.

ii)We can use Bind variables.

iii)We can change the CURSOR_SHARING parameter but this also cant prevent parsing if application ask to do.
cursor sharing = similar MIGHT change a hard parse into a soft parse,
cursor sharing similar CANNOT change the number of times parse is invoked however.

When Apps say "parse this", It parse it - it matters not what the value of cursor sharing is.


iv)The developers must cache open cursors they know will be used over and over.
to accomplish this is to move all SQL into plsql, plsql automagically caches statements for us,
it is the most efficient method to interface with the database. But Here i am doubtful whether ATG will allow us to handle the
code since its handling that(if i am right).

v)by only parsing a statment ONCE per session,

not once per execution
not once to "describe a table"
not once to "describe the ith column in a table"





In Parsing.

We need to go through Below steps.
i)syntax
ii)semantic
iii)Optimization
iv)Row source generation.etc


Two Kind of Parsing is there.

i)Hard Parsing
ii)Soft Parsing.

Hard Parsing: Parsing the sql from Scratch,go through above all steps and Its very costly.

Soft Parsing: It will do first two steps then If same syntax already used then it will use the already parsed statement from shared_pool.

Here, If we want more soft parses, we need to use Bind Variables.





Below is the formula used to find Execute to Parse Ratio.

round(100*(1-prse/exe),2)


If we want to see the from views.

Select x.sql_text , x.parse_calls , x.executions
,round( 100*(1-( x.parse_calls / x.executions )),2) execute_to_parse_ratio
FROM v$sql x
WHERE x.parse_calls >0
AND x.executions !=0
AND x.parsing_schema_name='EMP'
ORDER BY execute_to_parse_ratio ;

select PARSE_CALLS,EXECUTIONS,SQL_TEXT
from v$sql
where executions = 0 and parse_calls > 0
order by parse_calls desc;

Select PARSE_CALLS, EXECUTIONS, SQL_TEXT
from v$sql
where executions < PARSE_CALLS
order by parse_calls;

HOW TO CLONE A DATABASE IN WINDOWS IN SAME SERVER:
************************************************************
Assume Old Database Name=ORCL and New Database Name=AUX
Follow the below 7 steps:
Step 1:Create a password file.
orapwd file=$ORACLE_HOME/database/orapwaux password=pwd entries=5
Connect to orcl database and create pfile
Step 2: Create pfile='path/initaux.ora from spfile;
Step 3:Edit the pfile according to the new database path for adump,bdump,cdump and udump. If its not already there create those directories and place the path in pfile.
and we need to change the db_name=AUX and we need to include two important parameters in new init file.
db_file_name_convert= ('C:\oracle\oradata\orcl','C:\auxdir\oradata\aux')
log_file_name_convert=('C:\oracle\oradata\orcl','C:\auxdir\oradata\aux')
Step 4:Add the tnsnames.ora and listener.ora file for the new database(aux).
Below is the example for tnsentry and listener entry.
aux=
(Description=
(Address=(protocol=TCP)(HOST=santhanamuthu.in.ibm.com)(port=1521)
(connect_data=
(server=dedicated)
(server_name=aux)
)
)
Listener entry
(SID_DESC=
(GLOBAL_DBNAME=AUX)
(ORACLE_HOME=C:\oracle\product\10.2.0\db_1)
(SID_NAME=aux)
)
)
Step 5: Now we need to stop and start the listener or we need to do reload.
lsnrctl stop
lsnrctl start
Step 6:Create a service in window, use the below command:
oradim -new -sid AUX -starmode m - pfile=C:\oracle\product\10.2.0\db_1\database\initaux.ora
Step 6:In RMAN prompt give the below commands.
rman>connect auxilary sys/pwd@aux
rman>connect target sys/pwd@orcl
rman>startup clone nomount force;
instance started
rman>duplicate target database to aux;
It will take some time depend on size of the source database and we can exit once completed.
rman>exit
Now cloned database is ready;
set the environment to new aux database
rman>connect target sys/pwd;
you will get the below message
Connected to target database : AUX (DBID=157465780900)
If we want we can verify the new cloned database now.. :)

Grid Agent Failed to start with HTTP LISTENER failed

The agent fails to start with: Starting agent ....... failed. Failed to start HTTP listener. Consult the log files in: /u01/app/oracle/product/agent10g/sysman/log

2007-07-09 09:06:18 Thread-1 Starting Agent 10.2.0.3.0 from /u01/app/oracle/product/agent10g2007-07-09 09:06:18 Thread-1 : Startup of HTTP LISTENER failure (00716)

1. Agent crashes. As a result it is still listening on its port, rendering it unavailable. This port is defined in the /sysman/config/emd.properties file in the EMD_URL parameter.2. The agent is part of a High Availability cluster with multiple VIP's defined, and one VIP was just moved to another node, where there was an agent already running on another VIP.
SolutionCASE 1:
1. Get the Agent port number by looking at the EMD_URL parameter as defined in the /sysman/config/emd.properties file (if it's a cluster agent, look under //sysman/config)
2. Check to see if the port is still in use (usually it's 1830 or 3872):
netstat -an grep
If it is in use you will see something like the following:
tcp 0 0 0.0.0.0:3872 0.0.0.0:* LISTEN
3. If this is the case then kill off the rogue emagent processes
ps -ef grep emagent awk ' {print $2}' xargs kill -9
4. Re-start the agent

CASE 2:
This can be solved by using "ListenOnAllNICs=false" in emd.properties. This is explained in step F.2 inNote 406014.1 How to Configure Grid Control Agents to Monitor Virtual Hostname in HA environments