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

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.

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

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>

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

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

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"

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

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

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.

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

steps to create ASM instance on windows

steps to create ASM instance

set oracle_sid=+ASM


create initialization parameter file
E:\app\Mani\product\11.1.0\db_1\database\init+ASM.ora
with following enteries

instance_type=asm
ASM_DISKSTRING= 'E:\asmdisks\*'
_ASM_ALLOW_ONLY_RAW_DISKS=FALSE
ASM_DISKGROUPS='dgroup1'



create service for ASM
#oradim -new -sid +ASM


create files which we trick to asm to take as raw logical volumes

asmtool -create E:\asmdisks\asmdisks1.asm 300
asmtool -create E:\asmdisks\asmdisks2.asm 300
asmtool -create E:\asmdisks\asmdisks3.asm 300

startup

create the diskgooup for asm
create diskgroup dgroup1 normal redundancy disk
'E:\ASMDISKS\ASMDISKS1.ASM','E:\ASMDISKS\ASMDISKS2.ASM';


SQL> select path,mount_status from v$asm_disk;

PATH
-------------------------------------------------MOUNT_S
-------
E:\ASMDISKS\ASMDISKS1.ASM CLOSED

E:\ASMDISKS\ASMDISKS3.ASM
CLOSED

E:\ASMDISKS\ASMDISKS2.ASM
CLOSED



ASM_DISKDROUPS='dgroup1'



SQL> shutdown;
ASM diskgroups dismounted
ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area 535662592 bytes
Fixed Size 1334380 bytes
Variable Size 509162388 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted



thts it :)

WHAT IS AN ORACLE ASM

Earlier Oracle had to use either a file system or a
raw device to store its physical files

Things might become a little slow when it was using a file system as it has to
comply with file system standards and go through its buffering.

On the other hand ,Oracle was not able to keep multiple files in a raw device
like a file system would allow.(it will need multiple raw devices).


The solution :Write your own volume manager and own file system.

Oracle has is own file system storage solution called Oracle Automatic storage Management(ASM)


Oracle ASM is a volume manager which provides a special file system designed especially
for Oracle Databases.

what is i and g signifies in oracle 9i and 10g/11g

10g
definition -
10g is Oracle's grid computing product group including (among other things) a database management system (DBMS) and an
application server. In addition to supporting grid computing features such as resource sharing and automatic load balancing,
10g products automate many database management tasks. The Real Application Cluster (RAC) component makes it possible to install
a database over multiple servers.
10g follows Oracle's 9i platform. Oracle says that the g (instead of the expected i) in the name symbolizes the company's
commitment to the grid model. However, according to some reports, many early adopters are deploying 10g solely for its
automation features and have no immediate plans of implementing a grid environment.

Definition of: Oracle database

A relational database management system (DBMS) from Oracle, which runs on more than 80 platforms. Introduced in the late 1970s,
Oracle was the first database product to run on a variety of platforms from micro to mainframe. The Oracle database is Oracle's
flagship product, and version 11g was introduced in 2007.

Oracle 11g features include built-in testing for changes, the capability of viewing tables back in time, superior compression of all
types of data and enhanced disaster recovery functions.

The "i" and "g" Versions
Starting in 1999 with Version 8i, Oracle added the "i" to the version name to reflect support for the Internet with its built-in Java Virtual Machine
(JVM). Oracle 9i added more support for XML in 2001. In 2003, Oracle 10g was introduced with emphasis on the "g" for grid computing,
which enables clusters of low-cost, industry standard servers to be treated as a single unit.

Explanation of parameters used in oracle installation

"/etc/sysctl.conf"

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

#########################################################################################################
*fs.suid_dumpable = 1
The value in this file determines whether core dump files are produced for set-user-ID or otherwise protected/tainted binaries.
Three different integer values can be specified:

0 (default) - This provides the traditional behaviour. A core dump will not be produced for a process which has changed credentials
(by calling seteuid(2), setgid(2), or similar, or by executing a set-user-ID or set-group-ID program) or whose binary does not have
read permission enabled.
1 ("debug") - All processes dump core when possible. The core dump is owned by the file system user ID of the dumping process and no
security is applied. This is intended for system debugging situations only. Ptrace is unchecked.
2 ("suidsafe") - Any binary which normally would not be dumped (see "0" above) is dumped readable by root only. This allows the user
to remove the core dump file but not to read it. For security reasons core dumps in this mode will not overwrite one another or other files.
This mode is appropriate when administrators are attempting to debug problems in a normal environment.

*fs.aio-max-nr = 1048576-----is the maximum number of allowable concurrent requests. The maximum is commonly 64KB,
which is adequate for most applications. This is related to asynchronous I/O usage on your system
*fs.file-max = 6815744------------------------ Controls the total number of files which can be opened at once
*kernel.shmall = 2097152-----------------------The total amount of shared memory (in pages) which can be allocated on the system
*kernel.shmmax = 536870912---------------------The maximum size of a shared memory segment (in pages)
*kernel.shmmni = 4096--------------------------The maximum number of shared memory segments available on the system
# semaphores: semmsl, semmns, semopm, semmni
###########################
*kernel.sem = 250 32000 100 128
# /sbin/sysctl -a | grep sem
kernel.sem = 250 32000 32 128

The values for the semaphores represent the following:

semmsl: The number of semaphores per set
semmns: The total number of semaphores available
semopm: The number of operations which can be made per semaphore call
semmni: The maximum number of shared memory segments available in the system
##################
*net.ipv4.ip_local_port_range = 9000 65500 ---------------------This controls the number of network connections which can be allocated at once.
These are unrelated to the network ports on which incoming connections are made
*net.core.rmem_default=4194304,net.core.rmem_max=4194304---------------Represent the default and maximum size of the network receive buffer
*net.core.wmem_default=262144,net.core.wmem_max=1048586---------------Represent the default and maximum size of the network send buffer

how to change data dir in mysql(5.6)

In mysql 5.6 the my.ini by default built with the datadir and not in programfile in c:\ drive like prev releases.
so to change the datadir just place the my.cnf in programfiles/mysql and then change the deafault file value
from registry

get procedure list in oracle

SQL> SELECT object_name
2 FROM dba_objects
3 WHERE object_type = 'PROCEDURE' and owner='FAITH';



SELECT text FROM all_source WHERE owner = 'FAITH'
AND name = 'FAITHSUBUNSUB'
ORDER BY line;

how to recover a table in mysql if error is like table not exists

In the separate database create a table of same name
del its frm and ibd file from datadir(1st only frm and later ibd)
replace it with the older frm and ibd from the older database
check for permissions and user

you will now see your data and table in mysql prompt

mycnf commonly changed parameters

old_passwords=1
lower_case_table_names = 1
connect_timeout=60
wait_timeout=259200
interactive_timeout=259200

standard mycnf 8-12 GB

################################################################################
#DATE: 2011-02-02
#SITE: http://datastrangler.com
#DESCRIPTION: MySQL config 5.0.x, 5.1.x, 5.5.x
#RAM: 12GB RAM dedicated server
#Connections: 1000 connections
################################################################################
[mysqld_safe]
nice = -15
log-error =/var/log/mysqld.log
pid-file =/var/run/mysqld/mysqld.pid

[client]
socket = /var/lib/mysql/mysql.sock
default-character-set = utf8

[mysqld]
## Charset and Collation
#innodb_force_recovery=1
character-set-server = utf8
collation-server = utf8_general_ci
lower_case_table_names = 1
## Files
wait_timeout = 57600
lower_case_table_names = 1
back_log = 300
open-files-limit = 8192
open-files = 1024
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
#skip-name-resolve

## Logging
datadir = /var/lib/mysql
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-index
#log = mysql-gen.log
log_error = mysql-error.err
log_warnings
#log_bin = mysql-bin
#log_slow_queries = mysql-slow.log
#log_queries_not_using_indexes
long_query_time = 10 #default: 10
max_binlog_size = 256M #max size for binlog before rolling
expire_logs_days = 4 #binlog files older than this will be purged

## Per-Thread Buffers * (max_connections) = total per-thread mem usage
thread_stack = 256K #default: 32bit: 192K, 64bit: 256K
sort_buffer_size = 1M #default: 2M, larger may cause perf issues
read_buffer_size = 1M #default: 128K, change in increments of 4K
read_rnd_buffer_size = 1M #default: 256K
join_buffer_size = 1M #default: 128K
binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries
## total per-thread buffer memory usage: 8832000K = 8.625GB

## Query Cache
query_cache_size = 8M #global buffer
query_cache_limit = 512K #max query result size to put in cache

## Connections
max_connections = 500 #multiplier for memory usage via per-thread buffers
max_connect_errors = 100 #default: 10
concurrent_insert = 2 #default: 1, 2: enable insert for all instances
connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10
max_allowed_packet = 8M #max size of incoming data to allow

## Default Table Settings
sql_mode = NO_AUTO_CREATE_USER

## Table and TMP settings
max_heap_table_size = 512M #recommend same size as tmp_table_size
bulk_insert_buffer_size = 512M #recommend same size as tmp_table_size
tmp_table_size = 512M #recommend 1G min
#tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir

## Table cache settings
#table_cache = 512 #5.0.x
#table_open_cache = 512 #5.1.x, 5.5.x

## Thread settings
thread_concurrency = 16 #recommend 2x CPU cores
thread_cache_size = 100 #recommend 5% of max_connections

## Replication
#read_only
#skip-slave-start
#slave-skip-errors =
#slave-net-timeout =
#slave-load-tmpdir =
#slave_transaction_retries =
#server-id =
#replicate-same-server-id =
#auto-increment-increment =
#auto-increment-offset =
#master-connect-retry =
#log-slave-updates =
#report-host =
#report-user =
#report-password =
#report-port =
#replicate-do-db =
#replicate-ignore-db =
#replicate-do-table =
#relicate-ignore-table =
#replicate-rewrite-db =
#replicate-wild-do-table =
#replicate-wild-ignore-table =

## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability
#rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0
#rpl_semi_sync_master_timeout = 1000 #in milliseconds , master only setting

## 5.1.x and 5.5.x replication related setting.
#binlog_format = MIXED

## MyISAM Engine
key_buffer = 1M #global buffer
myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes
myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes
myisam_repair_threads = 4 #thread quantity when running repairs
myisam_recover = BACKUP #repair mode, recommend BACKUP

## InnoDB Plugin Dependent Settings
#ignore-builtin-innodb
#plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so

## InnoDB IO Capacity - 5.1.x plugin, 5.5.x
#innodb_io_capacity = 200

## InnoDB IO settings - 5.1.x only
#innodb_file_io_threads = 16

## InnoDB IO settings - 5.5.x and greater
#innodb_write_io_threads = 16
#innodb_read_io_threads = 16

## InnoDB Plugin Independent Settings
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:2G:autoextend
innodb_log_file_size = 256M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128
innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4
innodb_buffer_pool_size = 2560M #global buffer
innodb_additional_mem_pool_size = 1M #global buffer
innodb_status_file #extra reporting
innodb_file_per_table #enable always
innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID
innodb_table_locks = 0 #preserve table locks
innodb_log_buffer_size = 32M #global buffer
innodb_lock_wait_timeout = 60
innodb_thread_concurrency = 16 #recommend 2x core quantity
innodb_commit_concurrency = 16 #recommend 4x num disks
#innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI
innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush
skip-innodb-doublewrite

## Binlog sync settings
## XA transactions = 1, otherwise set to 0 for best performance
sync_binlog = 0

## TX Isolation
transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA

## Per-Thread Buffer memory utilization equation:
#(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections

## Global Buffer memory utilization equation:
# innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size

[mysqldump]
quick
quote-names
max_allowed_packet = 64M

find any table in sqlserver

use database
select * from sysobjects where name like '%tbl_billing_logs_month20121103%'

findobject '%tbl_billing_success_month20121105%','u'

mysqldump windows

SET CC=%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%%Time:~0,2%%Time:~3,2%%Time:~6,2%
mysqldump -uafz -pibm3400 --ignore-table=database.table1 --ignore-table=database.table1 webatpl >G:\mysqlbackup\webatpl%CC%.sql
forfiles /P G:\mysqlbackup\ /S /M webatpl* /D -5 /C "cmd /c del @PATH"

install mysql 5.6

rpm -Uvh *.rpm /root/.mysql_secret

Exports/Imports

Table Exports/Imports

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax.

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

For example output files see expdpEMP_DEPT.log and impdpEMP_DEPT.log.

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.
Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported.
The following is an example of the schema export and import syntax.

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

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

impdp VXMLDB/vxmldb REMAP_SCHEMA=FORCE_CHARGING:VXMLDB REMAP_TABLESPACE=FORCE_CHARGING:VXMLDB
tables=DELHI_DND directory=GoTo244_169 dumpfile=DELHI_DND.dmp logfile=DELHI_DND.log

impdp system/moddb REMAP_SCHEMA=hgmod2:moddb REMAP_TABLESPACE=ORCLDGR2:moddb,MOCDATA:moddb
directory=oracle_exp dumpfile=HGMOD_HGMOD2_BACKUP_11282012183140.DMP
logfile=HGMOD_HGMOD2_BACKUP_11282012183140.log

impdp system/moddb REMAP_SCHEMA=hgmod2:moddb REMAP_TABLESPACE=ORCLDGR2:moddb,MOCDATA:moddb directory=exp_dir
dumpfile=HGMOD2201212_BACKUP_12202012124427.DMP logfile=82012183140.log

expdp SYSTEM/hgmod tables=hgmod2.TBL_PRO_USERPROFILE1 dumpfile=tTBL_PRO_USERPROFILE1.DMP
LOGfile=TBL_PRO_USERPROFILE1.LOG directory=export_dump