Sunday, October 6, 2013

Shrink data file and reclaim unused space


shrink data file and reclaim unused space inside a data file can be achieved in simple way by
moving all the objects inside the tablespace to another tablespace and the droping or shrinking the empty datafiles

steps are
create new tablespace
move all the tables to the new tablespace
move all the indexes to the new tablespace
move all the partitions of a partioned tables to the new tablespace
move all the LOBS to the new tablespace
check if still any object is left in the Older tablespace
shrink the old data files or drop the old tablespace
grant default tablespace and quota to the specified users to use new tablespace


note : while moving the indexes sometime the status of indexes goes to unused.
take structure of those indexes drop those indexes and create them again.or try rebuilt again.


------ to see the free space the datafiles------------------

SELECT SUBSTR (df.NAME, 1, 60) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb,((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb FROM v$datafile df, dba_free_space dfs WHERE df.file# = dfs.file_id(+) GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name ORDER BY file_name;


-----move tables -------------------
SELECT 'ALTER TABLE RFC_PROMO2.' || OBJECT_NAME ||' MOVE TABLESPACE '||' PROMO02; '
FROM ALL_OBJECTS
WHERE OWNER = 'RFC_PROMO2'
AND OBJECT_TYPE = 'TABLE';


-----move indexes -------------------
SELECT 'ALTER INDEX RFC_PROMO2.'||INDEX_NAME||' REBUILD TABLESPACE PROMO02 ONLINE;'
FROM ALL_INDEXES
WHERE OWNER = 'RFC_PROMO2';

-----move lobs-----------------
SELECT 'ALTER TABLE RFC_PROMO2.'||LOWER(TABLE_NAME)||' MOVE LOB('||LOWER(COLUMN_NAME)||') STORE AS (TABLESPACE PROMO02);'
FROM DBA_TAB_COLS
WHERE OWNER = 'RFC_PROMO2' AND DATA_TYPE like '%LOB%';



-----confirm if any object is still left in old tablespace

select segment_name,segment_type,owner
from dba_segments
where tablespace_name ='PROMO1';


-----to see the names of portioned tables and their partitions
SELECT TABLESPACE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='tablename';


-----move partioned table partition

alter table partitioned move partition part_3 tablespace users;

SELECT 'ALTER TABLE ' ||table_name ||' MOVE PARTITION ' ||partition_name ||' TABLESPACE REPORT;' FROM all_tab_partitions WHERE table_name = 'requestLog';

---------------shrinking temp file
SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

alter database tempfile 'D:\APP\ADMINISTRATOR\ORADATA\MONETA\TEMP01.DBF' resize 256M;


--- to shrink datafiles:
select 'alter database datafile '''||file_name||''' resize '||ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )||'m;' as cmd, bytes/1024/1024 from dba_data_files a, ( select file_id, max(block_id+blocks-1) as hwm from dba_extents group by file_id ) b where a.file_id = b.file_id(+) and ceil(blocks*&&blksize/1024/1024)- ceil((nvl(hwm,1)* &&blksize)/1024/1024 ) > 0 ;



Thursday, July 11, 2013

BTREE v/s BITMAP indexes


btree index

when the values in a particular coulumn is all diffrenet or have
large variance,then b tree index is usefull
this is based on concept of branch block and leaf block
branch block has condition and pointer,pointer will either pints to next condition or the rowid.
leaf block has rowid and data.

bitmap index
when the data in particular coulumn is repitative
eg sex field it has value only as male or female
in such case its not good to create b tree index.
bitmap index is use full there.
its will be like following

male 0 1 0 0 0 0 0 0 0 0 0 0

female 1 0 1 1 1 1 1 1 1 1 1 1


Saturday, April 27, 2013

NULL Has No Equivalents



One aspect involving NULL in SQL often stumps people. SQL expressions are tri-valued, meaning every expression
can be true, false, or NULL. This affects all kinds of comparisons, operators, and logic as you've already seen.
But a nuance of this kind of logic is occasionally forgotten, so we'll repeat it explicitly.



NULL has no equivalents.
No other value is the same as NULL, not even other NULL values.


If you run the following query, can you guess your results?

select first_name, last_name
from hr.employees
where commission_pct = NULL;

The answer is no rows will be selected. Even though the values exists, so the COMMISSION_PCT = NULL criterion
will never find a match, and you will never see results from this query.
Always use IS NULL and IS NOT NULL to find or exclude your NULL values.

Saturday, April 20, 2013

Automating Database Startup and Shutdown on Linux 11g R2


I followed this link


Create a file called "/etc/init.d/dbora" as the root user, containing the following
vi /etc/init.d/dbora #!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database software.

ORA_OWNER=oracle

case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su $ORA_OWNER -c "/home/oracle/scripts/startup.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
touch /var/lock/subsys/dbora
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su $ORA_OWNER -c "/home/oracle/scripts/shutdown.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
rm -f /var/lock/subsys/dbora
;;
esac


Use the chmod command to set the privileges to 750.

chmod 750 /etc/init.d/dbora

Associate the dbora service with the appropriate run levels and set it to auto-start using the following command.

chkconfig --add dbora

Next, we must create the "startup.sh" and "shutdown.sh" scripts in the "/home/oracle/scripts". First create the directory.

# mkdir -p /home/oracle/scripts
# chown oracle.oinstall /home/oracle/scripts


The "/home/oracle/scripts/startup.sh" script should contain the following commands.


#!/bin/bash

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=maniner.domain
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH

export ORACLE_SID=orcl
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES

# Start Listener
lsnrctl start

# Start Database
sqlplus / as sysdba << EOF
STARTUP;
EXIT;
EOF


The "/home/oracle/scripts/shutdown.sh" script is similar.


#!/bin/bash

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=maniner.domain
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH

export ORACLE_SID=orcl
ORAENV_ASK=NO
. oraenv
ORAENV_ASK=YES

# Stop Database
sqlplus / as sysdba << EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF

# Stop Listener
lsnrctl stop



Note. You could move the environment settings into the "dbora" file or into a separate file that is sourced in the startup and shutdown script. I kept it local to the script so you could see the type of things that need to be set in case you have to write a script to deal with multiple installations, instances and listeners.

Make sure the permissions and ownership of the files is correct.

# chmod u+x /home/oracle/scripts/startup.sh /home/oracle/scripts/shutdown.sh
# chown oracle.oinstall /home/oracle/scripts/startup.sh /home/oracle/scripts/shutdown.sh



The listener and database will now start and stop automatically with the machine. You can test them using the following command as the "root" user.

# service dbora start
# service dbora stop




Check by rebooting the system ,its magic :) Oracle will start by itself

error in invoking target 'ntcontab.o' of makefile




I was stuck in the error when installing oracle on my vmvare centos 5 m/c

finally got solution,problem was with rpms.
as I don't have internet running on my m/c
I was not using yum cmd to install the rpms
I was downloading the rpms and then installing them
and getting the error like

libstdc++-devel-4.1.2-54.el5.i386 conflicts with file from package gcc-c++-4.1.2-33.i38

and reversal means order of installing rms doesn't work

then as solution I manage run internet on my system
and run

yum remove gcc-c++-4.1.2-33.i386
yum remove libstdc++-devel-4.1.2-54.el5.i386
yum remove libstdc++-devel
yum install gcc-c++
and finally get it resolved :)

Tuesday, April 16, 2013

mysql clustering steps by step


i have followed this link
for basics about mysql clustering here are some basic things
mysql clustering basics
setting up the Cluster

To set up the cluster, you need three servers. Two cluster nodes and one Management node. I should point out that the Management node is not required after the cluster install, but I strongly recommend keeping it as it gives you the automatic failover capability. I will use three servers as examples:


Server1 192.168.52.128 (Cluster Management Server)
Server2 192.168.52.129 (Data Node 1)
Server3 192.168.52.130 (Data Node 2)

#############################################################################################!
First step is to install MySQL Cluster Management Server on Server1.
Lets download from MySQL Cluster 6.2 from MySQL website (http://dev.mysql.com/downloads/cluster/).
This guide is intended for Debian based systems, so we will download nonrpm package (mysql-cluster-gpl-6.2.15-linux-i686-glibc23.tar.gz).
Here are the steps to follow, to set up the MySQL Cluster Management Server (ndb_mgmd) and the cluster Management client (ndb_mgm)
mkdir /usr/src/mysql-mgm
cd /usr/src/mysql-mgm
tar xvfz mysql-cluster-gpl-6.2.15-linux-i686-glibc23.tar.gz
cd mysql-cluster-gpl-6.2.15-linux-i686-glibc23
mv bin/ndb_mgm /usr/bin
mv bin/ndb_mgmd /usr/bin
chmod 755 /usr/bin/ndb_mg*
cd /usr/src
rm -rf /usr/src/mysql-mgm

Next step is to create the Cluster configuration file:

mkdir /var/lib/mysql-cluster
cd /var/lib/mysql-cluster
vi config.ini

(I try to use nano as the text editor, just because it is much easier to use than vi.)


Here is the sample config file:


[NDBD DEFAULT]
NoOfReplicas=2

[MYSQLD DEFAULT]

[NDB_MGMD DEFAULT]

[TCP DEFAULT]

# Section for the cluster management node
[NDB_MGMD]
# IP address of the management node (server1)
HostName=192.168.52.128

# Section for the storage nodes
[NDBD]
# IP address of the first data node (Server2)
HostName=192.168.52.129
DataDir= /var/lib/mysql-cluster

[NDBD]
# IP address of the second storage node (Server3)
HostName=192.168.52.130
DataDir=/var/lib/mysql-cluster

# one [MYSQLD] per storage node
[MYSQLD]
[MYSQLD]


Now let's start the Management Server:

ndb_mgmd -f /var/lib/mysql-cluster/config.ini

$$$$
[root@localhost mysql]# ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.5.29 ndb-7.2.10
$$$$


Now, we would want to start the Management Server automatically in case of a system reboot,
so we add an init script to do that:


echo 'ndb_mgmd -f /var/lib/mysql-cluster/config.ini' > /etc/init.d/ndb_mgmd
chmod 755 /etc/init.d/ndb_mgmd
update-rc.d ndb_mgmd defaults

#######################################################################################################################
Data Nodes Configuration (Server2 and Server3):

Now let's set up the data nodes. Here are the steps to do that (do on both data nodes)


groupadd mysql
useradd -g mysql mysql
cd /usr/local/
wget pick up any mirror from MySQL's website
tar xvfz mysql-cluster-gpl-6.2.15-linux-i686-glibc23.tar.gz
ln -s mysql-cluster-gpl-6.2.15-linux-i686-glibc23 mysql
cd mysql
scripts/mysql_install_db --user=mysql
chown -R root:mysql .
chown -R mysql data
cp support-files/mysql.server /etc/init.d/
chmod 755 /etc/init.d/mysql.server
update-rc.d mysql.server defaults
cd /usr/local/mysql/bin
mv * /usr/bin
cd ../
rm -fr /usr/local/mysql/bin
ln -s /usr/bin /usr/local/mysql/bin

#################################################################################
Next we need to create the MySQL config file /etc/my.cnf on #####both nodes:####

vi /etc/my.cnf


Here is the sample file:

[mysqld]
ndbcluster
# IP address of the cluster management server (Server1)
ndb-connectstring=192.168.52.128

[mysql_cluster]
# IP address of the cluster management Server (Server1)
ndb-connectstring=192.168.52.128


Our MySQL installation is almost complete, now let's create the data directories and start the MySQL Server on both nodes:


mkdir /var/lib/mysql-cluster
cd /var/lib/mysql-cluster
ndbd --initial
/etc/init.d/mysql.server start


###2013-02-08 08:52:46 [ndbd] INFO -- Angel connected to '192.168.52.128:1186'
2013-02-08 08:52:46 [ndbd] INFO -- Angel allocated nodeid

(Important: we need to run ndbd --initial
only when the start MySQL for the first time, and if /var/lib/mysql-cluster/config.ini on Management Server changes.)

MySQL installation is complete, now let's put in a root password for our MySQL Servers:

mysqladmin -u root password newrootpassword


Again, it makes sense to start up the cluster nodes automatically in case of a system restart/failure.
Here are the ndbd init script and system startup links for that:

echo 'ndbd' > /etc/init.d/ndbd
chmod 755 /etc/init.d/ndbd
update-rc.d ndbd defaults


this completes are Cluster installation process, next, now let's test it.



################################################################################################
Test:

On Cluster Management Server, run the Cluster Management Client:

ndb_mgm

It will take you to the ndb_mgm prompt:


-- NDB Cluster -- Management Client --
ndb_mgm>


Now type show on the prompt:

ndb_mgm> show;


You should see an output similar to this:

ndb_mgm> show;
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @192.168.52.129 (Version: version number, Nodegroup: 0, Master)
id=3 @192.168.52.130 (Version: version number, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.52.128 (Version: version number)

[mysqld(API)] 2 node(s)
id=4 @192.168.52.129 (Version: version number)
id=5 @192.168.52.130 (Version: version number)

ndb_mgm>



We should see our data nodes connected in the previous screen. Now type quit to close the Management client:

ndb_mgm>quit;



Test the Cluster:

Now, let's create a Test database on Server2 (192.168.52.129) and run some tests

On Server2:

mysql -u root -p
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE tblCustomer (ID INT) ENGINE=NDBCLUSTER;
INSERT INTO tblCustomer VALUES (1);
SELECT * FROM tblCustomer;
quit;

pay attention to the create table statement,
we must specify ENGINE=NDBCLUSTER for all tables that we want to clustered. As stated earlier,
MySQL cluster only saupports NDB engine, so if you use any other engine, table simply wont get clustered.

The result of the SELECT statement would be:

mysql> SELECT * FROM tblCustomer;
+------+
| ID |
+------+
| 1 |
+------+


Since clustering in MySQL is at the "table level" not at the database level, so we would have to create the database sperately on
Server3 (192.168.52.130) as well, but afterwards tblCustomer would be replicated with all its data (since the engine is NDBCLUSTER):


On Server3.......192.168.52.130

mysql -u root -p
CREATE DATABASE testdb;
USE testdb;
SELECT * FROM tblCustomer;

Now, if we insert a row of data on Server3, it should be replicated back to Server2:192.168.52.129

INSERT INTO tblCustomer VALUES (2);


If we run a SELECT query on Server2, here is what we should see:


mysql> SELECT * FROM testtable;

+------+
| ID |
+------+
| 1 |
| 2 |
+------+


Test Node shutdown:

Now run the following on Server2....192.168.52.129.... to test what happens if a node goes offline:


killall ndbd

and run this command to make sure that all ndbd processes have terminated:


ps aux | grep ndbd | grep -iv grep


If you still see any prpcesses, run this again:

killall ndbd

Now, lets go the management server (Server1)....192.168.52.128... and run the following to check the cluster status:


ndb_mgm

On the ndb_mgm console. run:

show;

it should be bring you an output simlar to the following:

ndb_mgm> show;
Connected to Management Server at: localhost:1186

Cluster Configuration
---------------------

[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 192.168.52.129)..
id=3 @192.168.52.130 (Version: -----, Nodegroup: 0, Master).....

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.52.128 (Version: -----)

[mysqld(API)] 2 node(s)
id=4 @192.168.52.129 (Version: --------)
id=5 @192.168.52.130 (Version: --------)

ndb_mgm>

You see, Server2 is not connected anymore.

Type quit; to leave the ndb_mgm management console. Now, let's check on Server3,
if our database is still up and we can make connections to it:

mysql -u root -p
USE testdb;
SELECT * FROM tblCustomer;
quit;

It should bring up the following result set:


mysql> SELECT * FROM tblCustomer;

+------+
| ID |
+------+
| 1 |
| 2 |
+------+


Now, let's start MySQL on Server2 again by issuing the following command:

ndbd


How to Restart MySQL Cluster:

In managing a produciton MySQL environment or any other transactional database environment,
times come when we have to restart/shutdone our systems. So, let's see how would we shutdown our MySQL Cluster:

On Server1, open the management console:

ndb_mgm

then type:

shutdown;

it would bring up an output like this:

ndb_mgm> shutdown;
Node 3: Cluster shutdown initiated
Node 2: Node shutdown completed.
2 NDB Cluster node(s) have shutdown.
NDB Cluster management server shutdown.
ndb_mgm>

This means that the cluster nodes Server2 and Server3 and also the Management node (Server1) have shut down.

To leave the Management console, run:

quit;


To start the cluster management server again, run the following (on Server1, Management Server):

ndb_mgmd -f /var/lib/mysql-cluster/config.ini


and on Server2 and Server3, run the following:

ndbd

in case /var/lib/mysql-cluster/config.ini on Management Server changed, you should run the following:


ndbd --initial


You can go back to the Management node and verify if the cluster started ok, without any errors:

ndb_mgm

on the Management console run the following:

show;

This should bring up the cluster configuration.


steps to do replication in mysql (both ways)



################################################################################################
MASTER -MASTER Replication on 2 Server (Primary Server(10.110.19.99)- Standby Server(10.110.19.100))
################################################################################################

################################################################################################
Creating the replication user on both nodes :
################################################################################################


MASTER SERVER ON 10.135.44.3
################################################################################################
grant replication slave, replication client on *.* to repl@"10.135.44.4" identified by "repl";
grant replication slave, replication client on *.* to repl@"Airtel_Ghana_4" identified by "repl";


SLAVE SERVER ON 10.110.19.100 altruist_100
################################################################################################
grant replication slave, replication client on *.* to repl@"10.135.44.3" identified by "repl";
grant replication slave, replication client on *.* to repl@"airtel_Ghana_3" identified by "repl";



################################################################################################
PRIMARY Server My.cnf file
################################################################################################
server-id=1
log-bin=/var/lib/mysql/mysql-bin.log
binlog-do-db=voicechat
replicate-do-db=voicechat
auto_increment_increment = 10
auto_increment_offset = 1

################################################################################################
Standby Server My.cnd File
################################################################################################
server-id=2
log-bin=/var/lib/mysql/mysql-bin.log
binlog-do-db=voicechat
replicate-do-db=voicechat
auto_increment_increment = 10
auto_increment_offset = 2

################################################################################################
Check log position for both server
MASTER SERVER
################################################################################################

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 | 107 | voicechat | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

################################################################################################
SLAVE SERVER
################################################################################################
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 107 | voicechat | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

################################################################################################
show master status and then slave status run the following command
################################################################################################
MASTER SERVER
################################################################################################
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='10.135.44.4', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;
START SLAVE;
quit;


################################################################################################
SLAVE SERVER
################################################################################################
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='10.135.44.3', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=107;
START SLAVE;
quit;


################################################################################################
Test Replication (Master-Master)
################################################################################################
Create database voicechat;
create table primary1 (name varchar(15));

insert into primary2(name) values('test_primary');
insert into primary2(name) values('test_primary');
insert into primary2(name) values('test_primary');






delete from primary1 ;
insert into abc_primary(name) values('test1_standby');
insert into abc_primary(name) values('test2_standby');
insert into abc_primary(name) values('test3_standby');




show slave status\G;
stop slave;
start slave;

steps to do replication in mysql (source to target -one way)


replication of master server on slave server

steps

step 1
on master

must enable binary logging
configure a unique server ID.

this can be done by setting follwing parameters in my.cnf file

[mysqld]
server-id=2
log-bin=mysql-bin


step 2

take backup of the databases or tables of which you want to set replication


step 3

create user

CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';


restart mysql


serice mysql restart


note the follwing

mysql>FLUSH TABLES WITH READ LOCK;
mysql>SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 333 | | |
+------------------+----------+--------------+------------------+



step4

on slave

[mysqld]
server-id=2


export the backup which you have taken on master


restart the mysql services

on mysql prompt run following command

mysql>CHANGE MASTER TO
MASTER_HOST='192.168.163.130', ##ip of master
MASTER_USER='repl',
MASTER_PASSWORD='slavepass',
MASTER_LOG_FILE='mysql-bin.000007',
MASTER_LOG_POS=333;

mysql>start slave



its done..:)



if u want to replicate a spesific table add following in the server my.cnf

replicate-do-table=mani.abc

Friday, April 12, 2013

how to convert a cooked file system oracle database to ASM



First create a ASM instance by following the link
steps-to-create-asm-instance-on-windows

once done follow the following steps


sqlplus>select name, state, type, total_mb, free_mb from v$asm_diskgroup;
sqlplus>select path,name from v$asm_disk;


set oracle_sid=test

take backup to be secure


create a table just for verification

sqlplus>create table friend (name varchar(20));
sqlplus>insert into friend values ('mani');
sqlplus>insert into friend values ('kirnai');
sqlplus>insert into friend values ('lovi');
sqlplus>commit

sqlplus>create pfile from spfile;

edit the pfile as follows
in the value of contrtolfile edit it with '+dgroup1'

sqlplus>shu immediate;

sqlplus / as sysdba


SQL> create spfile from pfile;

File created.



RMAN> startup nomount;

Oracle instance started

Total System Global Area 293601280 bytes

Fixed Size 1248624 bytes
Variable Size 113246864 bytes
Database Buffers 176160768 bytes
Redo Buffers 2945024 bytes

RMAN> restore controlfile from 'C:\oracle\product\10.2.0\oradata\orcl\CONTROL01.CTL';

Starting restore at 12-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: copied control file copy
output filename=+DGROUP1/orcl/controlfile/backup.256.812557745
Finished restore at 12-APR-13

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> backup as copy database format '+dgroup1';

Starting backup at 12-APR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
output filename=+DGROUP1/orcl/datafile/system.257.812558199 tag=TAG20130412T143637 recid=7 stamp=812558268
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
output filename=+DGROUP1/orcl/datafile/sysaux.258.812558273 tag=TAG20130412T143637 recid=8 stamp=812558310
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
output filename=+DGROUP1/orcl/datafile/example.259.812558319 tag=TAG20130412T143637 recid=9 stamp=812558335
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
output filename=+DGROUP1/orcl/datafile/undotbs1.260.812558345 tag=TAG20130412T143637 recid=10 stamp=812558358
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
output filename=+DGROUP1/orcl/datafile/users.261.812558361 tag=TAG20130412T143637 recid=11 stamp=812558361
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DGROUP1/orcl/controlfile/backup.262.812558363 tag=TAG20130412T143637 recid=12 stamp=812558366
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 12-APR-13
channel ORA_DISK_1: finished piece 1 at 12-APR-13
piece handle=+DGROUP1/orcl/backupset/2013_04_12/nnsnf0_tag20130412t143637_0.263.812558367 tag=TAG20130412T143637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 12-APR-13

RMAN>



alter diskgroup group1 add disk ' ';
# if you are show error of insufficient space

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DGROUP1/orcl/datafile/system.257.812558199"
datafile 2 switched to datafile copy "+DGROUP1/orcl/datafile/undotbs1.260.812558345"
datafile 3 switched to datafile copy "+DGROUP1/orcl/datafile/sysaux.258.812558273"
datafile 4 switched to datafile copy "+DGROUP1/orcl/datafile/users.261.812558361"
datafile 5 switched to datafile copy "+DGROUP1/orcl/datafile/example.259.812558319"


RMAN> alter database open;

database opened


SQL> select file_name from dba_data_files;

FILE_NAME
----------------------------------------------
+DGROUP1/orcl/datafile/users.261.812558361
+DGROUP1/orcl/datafile/sysaux.258.812558273
+DGROUP1/orcl/datafile/undotbs1.260.812558345
+DGROUP1/orcl/datafile/system.257.812558199
+DGROUP1/orcl/datafile/example.259.812558319


SQL> select name from v$controlfile;

NAME
------------------------------------------------------
+DGROUP1/orcl/controlfile/backup.256.812557745


SQL> select member from v$logfile;

MEMBER
-----------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG



SQL> alter database drop logfile group 2;

Database altered.


SQL> alter database add logfile group 2 ('+DGROUP1/orcl/REDO02.LOG') size 10m;

Database altered.
SQL> alter database drop logfile group 3;

Database altered.


SQL> alter database add logfile group 3 ('+DGROUP1/orcl/REDO03.LOG') size 10m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 1;

Database altered.


SQL> alter database add logfile group 1 ('+DGROUP1/orcl/REDO01.LOG') size 10m;

Database altered.

1

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

GROUP# STATUS
---------- ----------------
1 UNUSED
2 INACTIVE
3 CURRENT


SQL> select member from v$logfile;

MEMBER
-------------------------------------
+DGROUP1/orcl/redo03.log
+DGROUP1/orcl/redo02.log
+DGROUP1/orcl/redo01.log


SQL> select name from v$tempfile;

NAME
----------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF


SQL> alter tablespace temp add tempfile '+DGROUP1' SIZE 10M;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF
+DGROUP1/orcl/tempfile/temp.267.812559725


SQL> alter tablespace temp drop tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF';

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DGROUP1/orcl/tempfile/temp.267.812559725


SQL> select * from friend
2 ;

NAME
--------------------
mani
kirnai
lovi


thats it :)

Wednesday, April 10, 2013

ORA-29701: unable to connect to Cluster Manager


Please run the following batch files in the $ORACLE_HOME/bin directory to solve this error
localconfig delete
localconfig add

Thursday, April 4, 2013

sqlplus easy connect to other database server from local


sqlplus username/password@//server_ip_or_name:1521/servicename
eg sqlplus scott/tiger@//PODS.GATE.COM:1521/ODS

Wednesday, April 3, 2013

block change tracking to speeds up the incremntal backup


This parameter is new in 10g and it enables fast incremental backup

Oracle need not to scan all the database block for change as if this parameter is enable
it contains all the information of changed blocks ,so it speeds up the incremental backup.


to check whether this parameter is enables or not


SQL> select * from v$block_change_tracking;

to enable it use following

SQL> alter database enable block change tracking using file 'e:\file.txt';

Database altered.

lost undo datfile ,what to do?



You can offline drop the undo tablespce and then set undo managament to manual
and then recreate another undo tablespace,alter system to use the new undo and
set undo management to auto again,drop the older undo tablespace


follow the steps


SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 176161684 bytes
Database Buffers 352321536 bytes
Redo Buffers 5844992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'E:\DATA\TEST\UNDOTBS01.DBF'



SQL> startup mount

SQL> alter system set undo_management=manual scope=spfile;

System altered.

SQL> alter database datafile 'E:\DATA\TEST\UNDOTBS01.DBF' offline drop;

Database altered.

SQL> shu immediate;

SQL> select file_name,status,online_status from dba_data_files;

SQL> create undo tablespace undomani datafile 'E:\DATA\TEST\UNDOTBSMANI01.DBF' SIZE 10M;

Tablespace created.

SQL> alter system set undo_management=auto scope=spfile;

SQL> alter system set undo_tablespace ='undomani';

SQL> drop tablespace undo tbs_1;

Tuesday, April 2, 2013

RAC and Grid Computing , are these similar or different or one is subpart or other ?


RAC existed as OPS (Oracle Parallel Server) back in Oracle v7. So the concept of a clustered database
(shared everything database running across several servers) is not new.

What did change was the RAC s/w stack and architecture - as did HPC (High Performance Computing) in general.
The clusters we build today are very different from those we build back in the 90's.

So as the h/w technology changed, so too did the s/w technology for clustering. And Oracle (mostly for marketing reasons IMO)
choose the term Grid to describe their cluster s/w stack.

Grid in Oracle-speak basically means a s/w stack that provides the basic foundation for a clustered system.
RAC happens to be the house most frequently build on this foundation.

Monday, April 1, 2013

dbms_scheduler create,schedule,drop a job



create a procedure that is to be scheduled as a job

create or replace procedure insert_employees
as
begin
insert into employees values('aak');
end;
/


create a job and schedule it
begin
dbms_scheduler.create_job(
job_name => 'insertintoemployees2',
job_type => 'stored_procedure',
job_action => 'insert_employees',
start_date => sysdate,
repeat_interval => 'FREQ=DAILY;BYHOUR=4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22;BYMINUTE=0,5,10,15,20,25,30,35,40,45,50,55',
enabled => true,
auto_drop => false );
end;
/


mani>select job_name,state from dba_scheduler_jobs where job_name='INSERTINTOEMPLOYEES2';

JOB_NAME STATE
------------------------------ ---------------
INSERTINTOEMPLOYEES1 SUCCEEDED



drop a job

SQL> begin
2 dbms_scheduler.drop_job('INSERTINTOEMPLOYEES1');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select job_name,state from dba_scheduler_jobs where job_name='INSERTINTOEMP
LOYEES1';

no rows selected

SQL>


useful link

Saturday, March 30, 2013

delete vs truncate vs drop



The DROP statement is distinct from the DELETE and TRUNCATE statements, in that DELETE and TRUNCATE do not remove the table itself.
For example, a DELETE statement might delete some (or all) data from a table while leaving the table itself in the database,
whereas a DROP statement would remove the entire table from the database.

truncate cmd doesnt del the structure (metadata),it only deletes the data
drop deletes every thing i.e data+structure(metadata)

delete requires commit and rollback as it is DML
and unlike delete truncate is DDL and thus gets implicit commit;

drop and truncate are DDL(implicit commit)
delete is DML (requires explicit commit or rollback)

what happens to transactions when sqlplus program is closed or db crashes?


sql>quit (gracefull close of session)
the transaction automatically get implicit commit;

sql> close the window (ungracefully close the session)
the transaction gets rollback
what happens to transactions when during transaction oracle database crashes?
it automatically rollback those transactions during the recovery

Friday, March 29, 2013

RMAN related tasks


#how to reset RMAN persistent parameters back to their defaults

rman>configure retention policy clear;
rman>configure controlfile autobackup clear;
rman>configure channel device type disk clear;
rman>configure backup optimization on;
rman>configure backup optimization clear;

rman>show all;
rman>report schema;


#rman run script and from cmd shell
rman>@scropt_name.sql
rman target / cmdfile scropt_name

#how to modify where RMAN backup pieces are written on disk
rman backup def loc is

default:E:\APP\MANI\PRODUCT\11.1.0\DB_1\DATABASE\
ie oraclehome/database

rman>show all;

rman>configure channel device type disk format 'C:\rman-%d%s.bkp

d---db name
s----bkp set unique no


#how to multiplex RMAN backup pieces when running backup

rman>backup device type disk copies 3 database plus archivelog;
to make it persistent
rman>configure datafile backup copies for device disk to 3;
rman>configure ARCHIVELOG backup copies for device disk to 3;
how to multiplex archived log locations for an oracle db
show parameter log_archive_dest;
alter system set log_archive_dest_1='LOCATION=e:\archivelogs\' scope=spfile;
alter system set log_archive_dest_2='LOCATION=e:\archivelogs2\' scope=spfile;
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=spfile;
archive log list;
to disable one location
alter system set log_archive_dest_state_2=defer scope=both;
change the default location and size limit for the rman backup
SQL> show parameter db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string E:\app\Mani\flash_recovery_are
a
db_recovery_file_dest_size big integer 2G
SQL> alter system set db_recovery_file_dest='D:\rmanbackup' scope=both;

System altered.

SQL> show parameter db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\rmanbackup
db_recovery_file_dest_size big integer 2G
SQL> alter system set db_recovery_file_dest_size=20G scope=both;

System altered.

SQL> show parameter db_recovery

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string D:\rmanbackup
db_recovery_file_dest_size big integer 20G
SQL>

alter database vs alter system


The obvious idea is that alter database might apply to a whole database and alter system might be applicable to only one instance.

The description for alter database is :- Use the ALTER DATABASE statement to modify, maintain, or recover an existing database.

The description for alter system is :- Use the ALTER SYSTEM statement to dynamically alter your Oracle Database instance.
The settings stay in effect as long as the database is mounted.

The key difference seems to be the use of the word dynamically for alter system. Alter system allows things to happen to the database
whilst it is in use – flush shared pool, set a init.ora parameter,
switch archive log, kill session. They are all either non-database wide or non-intrusive database wide. By that I mean that killing a session
is specific to that session and flushing shared pool does not harm everyone connected (albeit it might affect performance in the short-term).

Let’s look at alter database and see if I can find any anomalies to this theory. The various clauses of startup, recovery, datafile, logfile,
controlfile, standby database all fall in line. The only one that sits uncomfortably with my theory is the alter database parallel command.


So to summarise, if asked and you do not know the answer then figure out if it affects every user and session on the database and go for alter database,
if it looks like it might be specific to a session or non-intrusive across all users then go for alter system.


Thursday, March 28, 2013

how to rename a tablespace and datafiles in oracle databse


how to rename a tablespace and datafiles in oracle databse

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

6 rows selected.


SQL> create tablespace payroll datafile 'e:\payroll.dbf' size 10m;

Tablespace created.


SQL> select name from v$datafile;

NAME
----------------------------------------
E:\DATA\TEST\SYSTEM01.DBF
E:\DATA\TEST\SYSAUX01.DBF
E:\DATA\TEST\UNDOTBS01.DBF
E:\DATA\TEST\USERS01.DBF
E:\DATA\TEST\EXAMPLE01.DBF
E:\PAYROLL.DBF

6 rows selected.

SQL>


SQL> alter tablespace payroll rename to payme;

Tablespace altered.


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
PAYME

SQL> select tablespace_name,file_name from dba_data_files where tablespace_name ='PAYME';

TABLESPACE_NAME FILE_NAME
-------------------- --------------------
PAYME E:\PAYROLL.DBF


rename datafile


SQL> alter database rename file 'e:\payroll.dbf' to 'e:\payme.dbf';
alter database rename file 'e:\payroll.dbf' to 'e:\payme.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 6 - file is in use or recovery
ORA-01110: data file 6: 'E:\PAYROLL.DBF'

SQL> SHU IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 176161684 bytes
Database Buffers 352321536 bytes
Redo Buffers 5844992 bytes
Database mounted.
SQL> alter database rename file 'e:\payroll.dbf' to 'e:\payme.dbf';
alter database rename file 'e:\payroll.dbf' to 'e:\payme.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 6 - new file 'e:\payme.dbf' not found
ORA-01110: data file 6: 'E:\PAYROLL.DBF'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> HOST
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.

C:\Users\Mani>E:

E:\>move PAYROLL.DBF PAYROME.DBF
1 file(s) moved.

E:\>EXIT

SQL> alter database rename file 'e:\payroll.dbf' to 'e:\payROME.dbf';

Database altered.



SQL> select tablespace_name,file_name from dba_data_files where tablespace_name ='PAYME';

TABLESPACE_NAME FILE_NAME
-------------------- --------------------
PAYME E:\PAYROME.DBF
other way is to take the datafile offline and then rename it(this will need recovery of file)
and other way is to take backup of control file to trace and shu immideiate and make the changes
in the created file this will also need downtime.

how to set the log sequence number in oracle database



SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination e:\ARCHIVEDLOGS\
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10


SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 176161684 bytes
Database Buffers 352321536 bytes
Redo Buffers 5844992 bytes
Database mounted.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
sql>recover database until cancel;

SQL> recover database until cancel;
Media recovery complete.

SQL> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination e:\ARCHIVEDLOGS\
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1



SQL> alter system archive log current;

System altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination e:\ARCHIVEDLOGS\
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

Tuesday, March 26, 2013

how RMAN expires backup (retention)



three types of retentions are there
1.redudancy retention
2.recovery window retention
3.keep until retention

1.redudancy retention:here the one database backup will be kept always as REDUNDANCY is 1 o

when we take a proper new backup the last one will become obsolete

rman>show all;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

rman>show all;
rman>list backup;
rman>report obsolete;
rman> backup database plus archivelog;
rman>list backup;

rman>show all;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

rman>report obsolete;

rman>delete obsolete;

rman>list backup summary;

rman> backup database plus archivelog;

rman>list backup summary;

rman>list backup;

rman>delete noprompt obsolete;
####################################### recovery window
################

eg 3 days window

rman >show all;

#change policy from redundancy(default) to recovery window

rman>configure retention policy to recovery window of 3 days;
rman >show all;
rman>report obsolete;
rmn>list backup;
rman>backup database plus archivelog;
rman>list backup summary;---4files
rman>report obsolete;

if todays is 20 date sun
#change date to 21 mon

rman>backup database plus archivelog;
rman>list backup summary;-----8files
rman>report obsolete;

#change date to 22--tuesday

rman>backup database plus archivelog;
rman>list backup summary;-----12files
rman>report obsolete;

#change date to 23 --wed

rman>report obsolete;

you will see the backup obolete and archivelog also plus meta data
#currently archivelog seq

rman>backup database plus archivelog;
rman>list backup summary;-----16files
rman>report obsolete;

#change date to 24 --wed

rman>report obsolete;

obsolete things are
archivelog seq + backup piece + archivelog seq

rman>delete obsolete;

rman>report obsolete;

nothing is obsolete now.

keep until

rman> backup database keep until time 'sysdate+7';

this overrites the set value of the retention for this backup

and make it obsolete oon the 8th day

Incremental backup of database(RMAN)


incremental backup of database

only possible with rman
two types of incremental backup are there
1.differential(default)====contains changes from last level 1 or 0 means the prev
2.cummulative=======contains changes from last level 0




1.differential(default)
saves time ,space, band width
only backups the change
rman target /
level 0 : sunday
level 1 : every other day

rman> backup incremental level=0 database tag='LEVEL0-SUN'

tag='LEVEL0-SUN' IS NAME OF BACKUP THAT CONtrol file knows

rman > list backup summary;

sqlplus > create a table days (varchar (20));

insert mondsy

rman> backup incremental level=1 database tag='LEVEL1-MoN'

takes only the change now(data blocks)

insert tuesday


rman> backup incremental level=1 database tag='LEVEL1-TUE'

insert wed

rman> backup incremental level=1 database tag='LEVEL1-wed' and so on to sat


crash the database intensionaly

restore


rman>startup nomount;
rman> restore controlfile from 'backup piece name latest';
rman> alter database mount;
rman>list backup summary;
rman>restore database;
rman>recover database; (by def it will rec as uch it can)
rman> alter database open resetlogs;


#####

2.cumulative (backs up everything changed from last 0 level)


rman> backup incremental level=0 cumulative database tag='LEVEL0-cum-SUN';

create table days(name varcahr(2))

in monday

rman> backup incremental level=1 cumulative database tag='LEVEL1-cum-moN';


#datablock in dbf has scn in header ,rman checks this scn with the prev backup taken



drop ts records in alert
so check in alert for drop ts

and take ur db to seq back just before drop


select sequence#,first_change# from v$log_history where sequence#='4';

recover database until change change# using backup controlfile;

alter databse open resetlogs;

oracle hot backup(user managed)





#backup is taken when database is up and runnning


prereq for hot backup

archivelog mode :
1.set the archive log destination/directory
2.shutdown the database
3. startup mount and alter database archivelog
4 open database

hot backup

1.put tablespaces/database in hot backup mode
2.take backup of datafiles which are in hot backup mode from previous step
3.put tablespaces/database out of hot backup mode
4.perform archive log switch
5.take binary backup of control file

if database crash
1.restore hot backup
2.apply all archivelogs
3.open database



#determine weather database is in archivelog mode

set oracle_Sid=test
sqlplus / as sysdba

select name from v$database;
select log_mode from v$datbase;
archive log list;

#change oracle default loc for bkp


#show parameter log_archive_dest_1;


alter system set log_archive_dest_1='LOCATION=e:\ARCHIVEDLOGS\' SCOPE=SPFILE;


startup mount;


alter database archivelog;


alter database open;

alter system archive log current;
/
/
/
/

tablespace should be in hot backup mode

select * from v$backup;
(notactive)

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

6 rows selected.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 0

to put database in hot backup mode

SQL> alter database begin backup;
Database altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 ACTIVE 2004538 26-MAR-13
2 ACTIVE 2004538 26-MAR-13
3 ACTIVE 2004538 26-MAR-13
4 ACTIVE 2004538 26-MAR-13
5 ACTIVE 2004538 26-MAR-13

# take the hot backup now (copy datafiles to other location)

#put tablespaces/database out of hot backup mode
SQL> alter database end backup;

Database altered.

SQL> select * from v$backup;

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
1 NOT ACTIVE 2004538 26-MAR-13
2 NOT ACTIVE 2004538 26-MAR-13
3 NOT ACTIVE 2004538 26-MAR-13
4 NOT ACTIVE 2004538 26-MAR-13
5 NOT ACTIVE 2004538 26-MAR-13

SQL>
5 NOT ACTIVE 2004538 26-MAR-13

SQL> alter system archive log current;


System altered.


SQL> alter database backup controlfile to 'e:\backups\controlbackup';

Database altered.

SQL> alter system archive log current;

System altered.

SQL> select * from employees;

FNAME
--------------------
minka

SQL> insert into employees values ('prem');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> insert into employees values ('deepak');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> insert into employees values ('mark');

1 row created.

SQL> commit;

Commit complete.

SQL> alter system archive log current;

System altered.

SQL> select * from employees;

FNAME
--------------------
minka
prem
deepak
mark

now delete ur data(to make it crash)

if database crash
1.restore hot backup
2.apply all archivelogs
3.open database


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 2005208 generated at 03/26/2013 23:08:38 needed for thread 1
ORA-00289: suggestion : E:\ARCHIVEDLOGS\ARC00042_0810363319.001
ORA-00280: change 2005208 for thread 1 is in sequence #42


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 2005217 generated at 03/26/2013 23:08:59 needed for thread 1
ORA-00289: suggestion : E:\ARCHIVEDLOGS\ARC00043_0810363319.001
ORA-00280: change 2005217 for thread 1 is in sequence #43
ORA-00278: log file 'E:\ARCHIVEDLOGS\ARC00042_0810363319.001' no longer needed
for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;

Database altered.

SQL>

Sunday, March 24, 2013

My 1st pl/sql program



basic block of pl/sql
declare
bigin
exception
end


1st pl/sql program

set serveroutput on
begin
dbms_output.put_line ('hello world') ;
end;
/


2nd pl/sql program

set serveroutput on
declare
x number;
bonus number;
begin
select sal into x from emp where empno=7369;
bonus := x*0.10 ;
dbms_output.put_line (bonus) ;
end;
/


types of blocks in pl/sql

anonymous
function
procedure


Saturday, March 23, 2013

PL/SQL



procedural constructs + SQL statements ---> PL/SQL
PL SQL PLSQL

plsql is prpetiory lanuguage of oracle
It has seemless integration of procedural constructs like variables,if,while,for data structure.

pl/sql compiler or pl/sql engine
takes whole block as pl/sql and separates the sql and pl part,procedural staatement executer executes pl
sql statemnt executer will execute sql than again gets integrated

pl/sql engine can be on client side or whole thing can exists in database itself

features
Ability to real life programming
Tight inter-relation with SQL
Full portability
Access to predefined packages eg dbms_lob,utl_http
Tight security(same securt as database othr users)
Better performance(5-sql sataments takes more time than one plsql block)
APEX oracle app express-web application development using ajax or others

Friday, March 22, 2013

very basics to work on VIM etitor linux



starting vim
sudo vim filename
sudo chown user filename

a----to enter into insert mode u can use i also
esc---get out of insert mode out of insert

find something

:/ MAX top to bottom
:/? MAX bottom to up
n--next search

:q -----quits
:e filename ---open other file

:q!---force quit
:wq -----save and quit---shift + ZZ
:wq filename ----file save as

little script for variable table_names




today=`date +'%y-%m-%d-%H-%M-%S'`
mon=`date +%m`_`date +%Y`
echo $mon
filename=push_report_$mon
echo $filename
mysqldump -h 10.130.0.53 -uroot -pmobi21 deploynew $filename >/data1/53_backup/dailybackp53/$filename$today.sql
##############################
mon=`date +%m`_`date +%Y`
echo $mon
lmon=`date --date="last month" +%m_%Y`
echo $lmon
mysql -uroot -ppaswd -e "drop view test.mo_reportlogs"
mysql -uroot -ppaswd -e "CREATE VIEW test.mo_reportlogs as SELECT reqtime,MSG,keyword_invoked FROM deploynew.mo_report_$mon
UNION SELECT reqtime,MSG,keyword_invoked FROM deploynew.mo_report_$lmon"

mysql -uroot -pgloadmin123 -e "CREATE VIEW `test`.`mo_reportlogs` SELECT deploynew.mo_report_$mon.reqtime,deploynew.mo_report_$mon.MSG,
deploynew.mo_report_$mon.keyword_invoked FROM deploynew.mo_report_$mon UNION SELECT reqtime,MSG,keyword_invoked FROM deploynew.mo_report_lmon"

##############################

Thursday, March 21, 2013

whatis transaction? (its behaviour) ACID properties



consistent state vs inconsistent state

Transactions are the features that keep database in a consistent state.
It is shift change from file base system to database system.


Database system implements transactions.

suppose we have two acounts

c:5000---checking
s:4000---savin
you are said to add from c into s 1000

1>read how much new amt in checking act
2>deduct 1000 from c act
3>write the new amt in c act
4>reas s
5>add 1000 to s
6>write new amt in s

these steps are transaction


observations

total sum at start in 9000
total sum at end is 9000

this is called consistency


now lets suppose after step 3 something happens and you are not able to do next steps,
this will lead to unconsistent data
so we have to undo the 1st three steps

either do all steps or do none--atomicity
after the transactions the data is permament #durability



If two transactions works concurrently
we have to make sure their work not interfere with each other.
i.e the transactions should work in isolation


so every transaction should follow ACID properties of (fundamental prperties of transaction)

A---atomicity
C---consistency
I-----isolation
D----durability


all databases implement these four properties .

how cuncurrent transactions are maintained

transaction control language TCL
commit
savepoint
rollback




oracle startup shutdown


database startup

instance creation
memory strucctures allocation
bg process
mount
instance will read ctl files
locate dbf and redo files
open
open the dbf and redo files
optional-:instance recovery
RECO will recover indoubt trx

sqlplus / as sysdba or sqlplus sys as sysdba (pssword)


sql> startup
sql> startup nomount.(only reates the instance)
used to modify control files

sql>alter database mount;(readint th ctl file and locating the files redo and data
use to rename datafile
enable/disable archivelogging redo
full db recovery


sql>alter database open;
options
restct mode ---dba's can log in
read only



shutdown database

1>flush buffer cache
close data/redo files
2>unmount
dis-associate instance from database
close the control files
3>shut down instance
release sga/memory structure
terminate bg process


shutdown <normal>---default
new connections are not allowed
oracle will wait for all current users get diconnected
write buffer caches (buffer pool and redo pool)
next starup will not require recover



shutdown trancsactional
no new tnx are allowed
disconects the user once the current tnx is over
write buffer caches (buffer pool and redo pool)
next starup will not require recover


shutdown ( immediate )
oracle will rollback the current tnx
write buffer caches (buffer pool and redo pool)
stops sql execution


shutdown abort
instace is terminated abruptly
write buffer caches not happens (buffer pool and redo pool)
next starup will require recover


Tuesday, March 19, 2013

Database Writer,LOG Writer,Checkpoint,log sequence number,SCN



Database Writer

The server process records changes to rollback and data blocks in the buffer cache. Database
Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files. It
ensures that a sufficient number of free buffers—buffers that can be overwritten when server
processes need to read in blocks from the data files—are available in the database buffer
cache. Database performance is improved because server processes make changes only in the
buffer cache.

DBWn defers writing to the data files until one of the following events occurs:
• Incremental or normal checkpoint
• The number of dirty buffers reaches a threshold value
• A process scans a specified number of blocks when scanning for free buffers and cannot fine any.
• Timeout occurs.
• A ping request in Real Application Clusters environment.
• Placing a normal or temporary tablespace offline.
• Placing a tablespace in read only mode.
• Dropping or Truncating a table.
• ALTER TABLESPACE tablespace name BEGIN BACKUP
#####################################################################
LOG Writer
LGWR performs sequential writes from the redo log buffer cache to the redo log file under
the following situations:
• When a transaction commits
• When the redo log buffer cache is one-third full
• When there is more than a megabyte of changes records in the redo log buffer cache
• Before DBWn writes modified blocks in the database buffer cache to the data files
• Every 3 seconds.
Because the redo is needed for recovery, LGWR confirms the commit only after the redo is
written to disk.
LGWR can also call on DBWn to write to the data files.

##################################
Checkpoint
A checkpoint occurs in the following situations:
• At every log switch
• When an instance has been shut down with the normal, transactional, or immediate
option
• When forced by setting the initialization parameter FAST_START_MTTR_TARGET.
• When manually requested by the database administrator
• When the ALTER TABLESPACE [OFFLINE NORMAL|READ ONLY|BEGIN BACKUP] cause checkpointing on specific data files.
log_checkpoints_to_alert FALSE|TRUE
to make record of checkpoint in alert

ALTER SYSTEM CHECKPOINT; ---this can be used to force the checkpoint

An event called a checkpoint occurs when the Oracle background process DBWn writes all
the modified database buffers in the SGA, including both committed and uncommitted data,
to the data files.
Checkpoints are implemented for the following reasons:
• Checkpoints ensure that data blocks in memory that change frequently are written to
data files regularly. Because of the least recently used algorithm of DBWn, a data block
that changes frequently might never qualify as the least recently used block and thus
might never be written to disk if checkpoints did not occur.
• Because all database changes up to the checkpoint have been recorded in the data files,
redo log entries before the checkpoint no longer need to be applied to the data files if
instance recovery is required. Therefore, checkpoints are useful because they can
expedite instance recovery.

At a checkpoint, the following information is written:
• Checkpoint number into the data file headers
• Checkpoint number, log sequence number, archived log names, and system change
numbers into the control file.
CKPT does not write data blocks to disk or redo blocks to the online redo logs.
#######################################################

Checkpoint number

Checkpoint number is the SCN number at which all the dirty buffers are written to the disk, there can be a checkpoint at object/tablespace/datafile/database level.
Checkpoint number is never updated for the datafiles of readonly tablespaces.
A checkpoint occurs when the DBWR (database writer) process writes all modified buffers in the SGA buffer cache to the database data files. Data file headers are also updated with the latest checkpoint SCN, even if the file had no changed blocks.
Checkpoints occur AFTER (not during) every redo log switch and also at intervals specified by initialization parameters.

log sequence number

A number that uniquely identifies a set of redo records in a redo log file. When Oracle fills one online redo log file and switches to a
different one, Oracle automatically assigns the new file a log sequence number. For example, if you create a database with two online log files,
then the first file is assigned log sequence number 1. When the first file fills and Oracle switches to the second file, it assigns
log sequence number 2; when it switches back to the first file, it assigns log sequence number 3, and so forth.


SCN system change numbers (high and low SCN number )

the system change number (SCN) is Oracle's clock - every time we commit, the clock
increments. The SCN just marks a consistent point in time in the database.
select dbms_flashback.get_system_change_number scn from dual;
When the Oracle server begins executing a SELECT statement, it determines the current
system change number (SCN) and ensures that any changes not committed before this SCN
are not processed by the statement. Consider the case where a long-running query is executed
at a time when several changes are being made. If a row has changes that were not committed
at the start of the query, the Oracle server constructs a read-consistent image of the row by
retrieving the before image of the changes from the undo segment and applying the changes
to a copy of the row in memory.
the scn increments with every commit, not every second. It is not a "ticker".
It is written to the controlfiles when the control files are updated - which happens as the result of a few things, one being "end of official checkpoint".
the database never sleeps. Most of those other "programs" do transactions and commit.
scn's are written to redo logs continuously - when you commit they are emitted into the redo stream.
and semantically speaking, after the checkpoint COMPLETES, not after the checkpoint is INITIATED
when database has no transactions going on,when do checkpoint be using ?
The database ALWAYS has transactions going on, ALWAYS. SMON and many other background
processes are always doing work, the database (unless it is opened read only) is always
doing transactions.

The system change number (SCN) is an ever-increasing value that uniquely identifies
a committed version of the database. Every time a user commits a transaction,
Oracle records a new SCN. You can obtain SCNs in a number of ways, for example,
from the alert log. You can then use the SCN as an identifier for purposes of
recovery. For example, you can perform an incomplete recovery of a database up to
SCN 1030. Oracle uses SCNs in control files, datafile headers, and redo records. Every redo log
file has both a log sequence number and low and high SCN. The low SCN records
the lowest SCN recorded in the log file, while the high SCN records the highest SCN
in the log file.

Sunday, March 17, 2013

oracle coldbackup (offline backup)



A cold backup, also called an offline backup, is a database backup when the database is offline
and thus not accessible for updating. This is the safest way to back up because it avoids the risk
of copying data that may be in the process of being updated. However, a cold backup involve
downtime because users cannot use the database while it is being backed up.



First shutdown safely and make database consistent

go to disc where database is created


mkdir e:\backup


copy *dbf e:\backup
copy *log e:\backup
copy *ctl e:\backup

set oracle_sid=test
sqlplus / a sysdba
shutdown immediate;

del * from where data is located

sqlplus / a sysdba
pfile still exists so db will get mount


shutdown

copy e:\backup\* .

startup ;

RMAN with basic steps


ORACLE_HOME/BIN/rman.exe

RMAN provides way to backup and restrore,recover database using oracle server
Rman works like sqlplus but rman is a server program and sqlplus is a client program



make sure you are in archivelog mode
with
sql>archive log list
if not
sql>shu immediate;
sql>startup mount;
sql>alterdatbase archivelog;


set oracle_sid=test
echo %oracle_sid%
rman target / (sysdba is impliceit here)
Recovery Manager: Release 11.1.0.6.0 - Production on Mon Mar 18 04:10:05 2013

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

connected to target database: TEST (DBID=2108461682, not open)

rman> backup database;

rman connects to ontrol file and gets information abt the datafiles and everything
like which blocks are in use,order of backup

output using target database controlfiles
################################################


RMAN> backup database;

Starting backup at 18-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=E:\DATA\TEST\SYSTEM01.DBF
input datafile file number=00002 name=E:\DATA\TEST\SYSAUX01.DBF
input datafile file number=00005 name=E:\DATA\TEST\EXAMPLE01.DBF
input datafile file number=00003 name=E:\DATA\TEST\UNDOTBS01.DBF
input datafile file number=00004 name=E:\DATA\TEST\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 18-MAR-13
channel ORA_DISK_1: finished piece 1 at 18-MAR-13
piece handle=E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_03_18\O1_MF_NNN
DF_TAG20130318T041154_8NDKOX5G_.BKP tag=TAG20130318T041154 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:46
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 18-MAR-13
channel ORA_DISK_1: finished piece 1 at 18-MAR-13
piece handle=E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_03_18\O1_MF_NCS
NF_TAG20130318T041154_8NDKZFF0_.BKP tag=TAG20130318T041154 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 18-MAR-13

RMAN>

############################################

backup set--one for ctl file and other for datafile is get created
backup piece----file name to whic the backup set is written to(8 character file name)

rman>list backup;


####

RMAN> list backup;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.03G DISK 00:04:49 18-MAR-13
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20130318T041154
Piece Name: E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_03_18\O1_MF_NNNDF_TAG20130318T041154_8NDKOX5G_.BKP
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1006436 18-MAR-13 E:\DATA\TEST\SYSTEM01.DBF
2 Full 1006436 18-MAR-13 E:\DATA\TEST\SYSAUX01.DBF
3 Full 1006436 18-MAR-13 E:\DATA\TEST\UNDOTBS01.DBF
4 Full 1006436 18-MAR-13 E:\DATA\TEST\USERS01.DBF
5 Full 1006436 18-MAR-13 E:\DATA\TEST\EXAMPLE01.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:28 18-MAR-13
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20130318T041154
Piece Name: E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_03_18\O1_MF_NCSNF_TAG20130318T041154_8NDKZFF0_.BKP
SPFILE Included: Modification time: 18-MAR-13
SPFILE db_unique_name: TEST
Control File Included: Ckp SCN: 1006436 Ckp time: 18-MAR-13
#####

rman>delete backup;


##


RMAN> delete backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 AVAILABLE DISK E:\APP\MANI\FLASH_RECOVERY_AREA\
TEST\BACKUPSET\2013_03_18\O1_MF_NNNDF_TAG20130318T041154_8NDKOX5G_.BKP
2 2 1 1 AVAILABLE DISK E:\APP\MANI\FLASH_RECOVERY_AREA\
TEST\BACKUPSET\2013_03_18\O1_MF_NCSNF_TAG20130318T041154_8NDKZFF0_.BKP

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_03_18\O1
_MF_NNNDF_TAG20130318T041154_8NDKOX5G_.BKP RECID=1 STAMP=810360723
deleted backup piece
backup piece handle=E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_03_18\O1
_MF_NCSNF_TAG20130318T041154_8NDKZFF0_.BKP RECID=2 STAMP=810361029
Deleted 2 objects

rman> backup database plus archivelog;




RMAN> backup database plus archivelog;


Starting backup at 18-MAR-13
using channel ORA_DISK_1
specification does not match any archived log in the recovery catalog
backup cancelled because all files were skipped
Finished backup at 18-MAR-13

Starting backup at 18-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=E:\DATA\TEST\SYSTEM01.DBF
input datafile file number=00002 name=E:\DATA\TEST\SYSAUX01.DBF
input datafile file number=00005 name=E:\DATA\TEST\EXAMPLE01.DBF
input datafile file number=00003 name=E:\DATA\TEST\UNDOTBS01.DBF
input datafile file number=00004 name=E:\DATA\TEST\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 18-MAR-13
channel ORA_DISK_1: finished piece 1 at 18-MAR-13
piece handle=E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_03_18\O1_MF_NNNDF_TAG20130318T042146_8NDL88KX_.BKP tag=TAG20130318T042146 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 18-MAR-13
channel ORA_DISK_1: finished piece 1 at 18-MAR-13
piece handle=E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_03_18\O1_MF_NCSNF_TAG20130318T042146_8NDLK590_.BKP tag=TAG20130318T042146 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 18-MAR-13

Starting backup at 18-MAR-13
using channel ORA_DISK_1
specification does not match any archived log in the recovery catalog
backup cancelled because all files were skipped
Finished backup at 18-MAR-13

RMAN>


RMAN> list backup;


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


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 1.03G DISK 00:04:25 18-MAR-13
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20130318T042146
Piece Name: E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_03_18\O1_MF_NNNDF_TAG20130318T042146_8NDL88KX_.BKP
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 1006436 18-MAR-13 E:\DATA\TEST\SYSTEM01.DBF
2 Full 1006436 18-MAR-13 E:\DATA\TEST\SYSAUX01.DBF
3 Full 1006436 18-MAR-13 E:\DATA\TEST\UNDOTBS01.DBF
4 Full 1006436 18-MAR-13 E:\DATA\TEST\USERS01.DBF
5 Full 1006436 18-MAR-13 E:\DATA\TEST\EXAMPLE01.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 9.36M DISK 00:00:25 18-MAR-13
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20130318T042146
Piece Name: E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_03_18\O1_MF_NCSNF_TAG20130318T042146_8NDLK590_.BKP
SPFILE Included: Modification time: 18-MAR-13
SPFILE db_unique_name: TEST
Control File Included: Ckp SCN: 1006436 Ckp time: 18-MAR-13

RMAN>


this will do the archhive log switch and takes backup of archivelog then datafiles and then ctl file and again does the switch and take backup of
archive log

go to the datadir
shu abort
\
del *

now startup will not work as data ia gone

SQL> startup
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 159384468 bytes
Database Buffers 369098752 bytes
Redo Buffers 5844992 bytes
ORA-00205: error in identifying control file, check alert log for more info


lets restore and recover the database now;

rman target /
observer the output(connected but db is not startted0

C:\Users\Administrator>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Mon

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

connected to target database: TEST (not mounted)


C:\Users\Administrator>rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Mon Mar 18 04:32:41 20

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

connected to target database (not started)



rman> startup nomount;

RMAN> startup nomount;

Oracle instance started

Total System Global Area 535662592 bytes

Fixed Size 1334380 bytes
Variable Size 159384468 bytes
Database Buffers 369098752 bytes
Redo Buffers 5844992 bytes
rman>restore controlfile from 'backup piece name'

RMAN> restore controlfile from 'E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2
013_03_18\O1_MF_NCSNF_TAG20130318T042146_8NDLK590_.BKP'
;

Starting restore at 18-MAR-13
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
output file name=E:\DATA\TEST\CONTROL01.CTL
output file name=E:\DATA\TEST\CONTROL02.CTL
output file name=E:\DATA\TEST\CONTROL03.CTL
Finished restore at 18-MAR-13

this will restore controlfile and create three coppies(as file told it )


rman>alter database mount;



rman>restore database;

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;
###############################################################################
Starting restore at 18-MAR-13
Starting implicit crosscheck backup at 18-MAR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 18-MAR-13

Starting implicit crosscheck copy at 18-MAR-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-MAR-13

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\2013_03_18\O1_MF_NCSNF
_TAG20130318T042146_8NDLK590_.BKP

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to E:\DATA\TEST\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to E:\DATA\TEST\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to E:\DATA\TEST\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to E:\DATA\TEST\USERS01.DBF
channel ORA_DISK_1: restoring datafile 00005 to E:\DATA\TEST\EXAMPLE01.DBF
channel ORA_DISK_1: reading from backup piece E:\APP\MANI\FLASH_RECOVERY_AREA\TE
ST\BACKUPSET\2013_03_18\O1_MF_NNNDF_TAG20130318T042146_8NDL88KX_.BKP
channel ORA_DISK_1: piece handle=E:\APP\MANI\FLASH_RECOVERY_AREA\TEST\BACKUPSET\
2013_03_18\O1_MF_NNNDF_TAG20130318T042146_8NDL88KX_.BKP tag=TAG20130318T042146
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:10:46
Finished restore at 18-MAR-13
###############################################################################
RMAN> recover database;

rman> recover database;

rman>alter database open resetlogs;



dba_group and user identified externaly


1>dba_group

I installed oracle as user-mani windows account
then I tried to login from aother administrator account as
sqlplus / as sysdba but not able to connect .

here come dba_group

I. right clicked my computer>manage>localusersgroups>add administrator
to dba_group and all start working ..:)



2>user identified externaly
use something other than data dictoinaly by oracle for authentication


echo %username%
hostname
echo %userdomain%


create user "OPS$MANI-PC\Mani" identified externally;
grant connect,resource to "OPS$MANI-PC\Mani";


then you will be able to connect database when you are looged into system as
use Mani without any password simply bu

sqlplus /

what is oracle Data Dictionary



Read only set of tables provides Administrator metadata about DB.
e.g columns,index,constraint (user can only read it)
write priv is to do by oracle itself

two forms

1>tables and views(db structure)----remains static,only changes when we creat something
three set of views
user_= users view(wha you own,what is in your schema)
all_=expanded users views (what you can access)
dba_=dba's view(what is in everyone's schema)
#select table_name from dba_tables wher table_name like '%$";
to see the base tables to which the views refering to.

#desc ds$;
#desc dba_tablespaces and compare the output

2>dynamic performance data(starts with v$ )

e.g v$session,v$database