Simple vi Editor Command

General Startup

To use vi: vi filename
To exit vi and save changes: ZZ or :wq
To exit vi without saving changes: :q!
To enter vi command mode: [esc]

Counts : A number preceding any vi command tells vi to repeat that command that many times.

Cursor Movement

h move left (backspace)
j move down
k move up
l move right (spacebar) [return] move to the beginning of the next line
$ last column on the current line
0 move cursor to the first column on the current line
^ move cursor to first nonblank column on the current line
w move to the beginning of the next word or punctuation mark
W move past the next space
b move to the beginning of the previous word or punctuation mark
B move to the beginning of the previous word, ignores punctuation
e end of next word or punctuation mark
E end of next word, ignoring punctuation
H move cursor to the top of the screen
M move cursor to the middle of the screen
L move cursor to the bottom of the screen

Screen Movement

G move to the last line in the file
xG move to line x
z+ move current line to top of screen
z move current line to the middle of screen
z- move current line to the bottom of screen
^F move forward one screen
^B move backward one line
^D move forward one half screen
^U move backward one half screen
^R redraw screen ( does not work with VT100 type terminals )
^L redraw screen ( does not work with Televideo terminals )

Inserting

r replace character under cursor with next character typed
R keep replacing character until [esc] is hit
i insert before cursor a append after cursor
A append at end of line
O open line above cursor and enter append mode

Deleting

x delete character under cursor
dd delete line under cursor
dw delete word under cursor
db delete word before cursor

Copying Code yy (yank)'copies' line which may then be put by the p(put) command. Precede with a count for multiple lines.

Put Command brings back previous deletion or yank of lines, words, or characters P bring back before cursor p bring back after cursor

Find Commands

? finds a word going backwards
/ finds a word going forwards
f finds a character on the line under the cursor going forward
F finds a character on the line under the cursor going backwards
t find a character on the current line going forward and stop one character before it
T find a character on the current line going backward and stop one character before it
; repeat last f, F, t, T

Miscellaneous Commands

. repeat last command
u undoes last command issued
U undoes all commands on one line
xp deletes first character and inserts after second (swap)
J join current line with the next line
^G display current line number
% if at one parenthesis, will jump to its mate
mx mark current line with character x
'x find line marked with character x

NOTE: Marks are internal and not written to the file.

Clone an Oracle database using an online/hot backup

This procedure will clone a database using a online copy of the source database files. Before beginning though, there are a few things that are worth noting about online/hot backups:

* When a tablespace is put into backup mode, Oracle will write entire blocks to redo rather than the usual change vectors. For this reason, do not perform a hot backup during periods of heavy database activity - it could lead to a lot of archive logs being created.
* This procedure will put all tablespaces into backup mode at the same time. If the source database is quite large and you think that it might take a long time to copy, consider copying the tablespaces one at a time, or in groups.
* While the backup is in progress, it will not be possible to take the tablespaces offline normally or shut down the instance.

Ok, lets get started...

* 1. Make a note of the current archive log change number
Because the restored files will require recovery, some archive logs will be needed. This applies even if you are not intending to put the cloned database into archive log mode. Work out which will be the first required log by running the following query on the source database. Make a note of the change number that is returned:

select max(first_change#) chng
from v$archived_log
/

* 2. Prepare the begin/end backup scripts
The following sql will produce two scripts; begin_backup.sql and end_backup.sql. When executed, these scripts will either put the tablespaces into backup mode or take them out of it:

=====================================
cr_hot_backup.sql

set lines 999 pages 999
set verify off
set feedback off
set heading off

spool begin_backup.sql

select 'alter tablespace ' || tablespace_name || ' begin backup;' tsbb
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name
/
spool off

spool end_backup.sql

select 'alter tablespace ' || tablespace_name || ' end backup;' tseb
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name
/
spool off

=======================================

#


# 3. Put the source database into backup mode
From sqlplus, run the begin backup script created in the last step:

@begin_backup

This will put all of the databases tablespaces into backup mode.

# 4. Copy the files to the new location
Copy, scp or ftp the files from the source database/machine to the target. Do not copy the control files across. Make sure that the files have the correct permissions and ownership.

# 5. Take the source database out of backup mode
Once the file copy has been completed, take the source database out of backup mode. Run the end backup script created in step 2. From sqlplus:

@end_backup

# 6. Copy archive logs
It is only necessary to copy archive logs created during the time the source database was in backup mode. Begin by archiving the current redo:

alter system archive log current;
# Then, identify which archive log files are required. When run, the following query will ask for a change number. This is the number noted in step 1.

select name
from v$archived_log
where first_change# >= &change_no
order by name
/

Create an archive directory in the clone database.s file system and copy all of the identified logs into it.

# 7. Produce a pfile for the new database
This step assumes that you are using a spfile. If you are not, just copy the existing pfile.

From sqlplus:

create pfile='init.ora' from spfile;

This will create a new pfile in the $ORACLE_HOME/dbs directory.

Once created, the new pfile will need to be edited. If the cloned database is to have a new name, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Also think about adjusting memory parameters. If you are cloning a production database onto a slower development machine you might want to consider reducing some values.

Ensure that the archive log destination is pointing to the directory created in step 6.

# 8. Create the clone controlfile
Create a control file for the new database. To do this, connect to the source database and request a dump of the current control file. From sqlplus:

alter database backup controlfile to trace as '/home/oracle/cr_.sql'
/

The file will require extensive editing before it can be used. Using your favourite editor make the following alterations:

* Remove all lines from the top of the file up to but not including the second 'STARTUP MOUNT' line (it's roughly halfway down the file).

* Remove any lines that start with --

* Remove any lines that start with a #

* Remove any blank lines in the 'CREATE CONTROLFILE' section.

* Remove the line 'RECOVER DATABASE USING BACKUP CONTROLFILE'

* Remove the line 'ALTER DATABASE OPEN RESETLOGS;'

* Make a copy of the 'ALTER TABLESPACE TEMP...' lines, and then remove them from the file. Make sure that you hang onto the command, it will be used later.

* Move to the top of the file to the 'CREATE CONTROLFILE' line. The word 'REUSE' needs to be changed to 'SET'. The database name needs setting to the new database name (if it is being changed). Decide whether the database will be put into archivelog mode or not.

* If the file paths are being changed, alter the file to reflect the changes.


Here is an example of how the file would look for a small database called dg9a which isn't in archivelog mode:

====================================================
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "DG9A" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/u03/oradata/dg9a/redo01.log' SIZE 100M,
GROUP 2 '/u03/oradata/dg9a/redo02.log' SIZE 100M,
GROUP 3 '/u03/oradata/dg9a/redo03.log' SIZE 100M
DATAFILE
'/u03/oradata/dg9a/system01.dbf',
'/u03/oradata/dg9a/undotbs01.dbf',
'/u03/oradata/dg9a/cwmlite01.dbf',
'/u03/oradata/dg9a/drsys01.dbf',
'/u03/oradata/dg9a/example01.dbf',
'/u03/oradata/dg9a/indx01.dbf',
'/u03/oradata/dg9a/odm01.dbf',
'/u03/oradata/dg9a/tools01.dbf',
'/u03/oradata/dg9a/users01.dbf',
'/u03/oradata/dg9a/xdb01.dbf',
'/u03/oradata/dg9a/andy01.dbf',
'/u03/oradata/dg9a/psstats01.dbf',
'/u03/oradata/dg9a/planner01.dbf'
CHARACTER SET WE8ISO8859P1
;
=======================================================

# 9. Add a new entry to oratab and source the environment
Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database.
Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:

echo $ORACLE_SID

If this doesn't output the new database sid go back and investigate.

# 10. Create the a password file
Use the following command to create a password file (add an appropriate password to the end of it):

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID} password=

# 11. Create the new control file(s)
Ok, now for the exciting bit! It is time to create the new controlfiles and open the database:

sqlplus "/ as sysdba"

@/home/oracle/cr_

If all goes to plan you will see the instance start and then the message 'Control file created'.

# 12. Recover and open the database
The archive logs that were identified and copied in step 6 must now be applied to the database. Issue the following command from sqlplus:

recover database using backup controlfile until cancel

When prompted to 'Specify log' enter 'auto'. Oracle will then apply all the available logs, and then error with ORA-00308. This is normal, it simply means that all available logs have been applied. Open the database with reset logs:

alter database open resetlogs;


# 13. Create temp files
Using the 'ALTER TABLESPACE TEMP...' command from step 8, create the temp files. Make sure the paths to the file(s) are correct, then run it from sqlplus.

# 14. Perform a few checks
If the last couple of steps went smoothly, the database should be open. It is advisable to perform a few checks at this point:

* Check that the database has opened with:

select status from v$instance;

The status should be 'OPEN'

* Make sure that the datafiles are all ok:

select distinct status from v$datafile;

It should return only ONLINE and SYSTEM.

* Take a quick look at the alert log too.


# 15. Set the databases global name
The new database will still have the source databases global name. Run the following to reset it:

alter database rename global_name to
/

Note. no quotes!

# 16. Create a spfile
From sqlplus:

create spfile from pfile;

# 17. Change the database ID
If RMAN is going to be used to back-up the database, the database ID must be changed. If RMAN isn't going to be used, there is no harm in changing the ID anyway - and it's a good practice to do so.

From sqlplus:

shutdown immediate
startup mount
exit

From unix:

nid target=/

NID will ask if you want to change the ID. Respond with 'Y'. Once it has finished, start the database up again in sqlplus:

shutdown immediate
startup mount
alter database open resetlogs
/

# 18. Configure TNS
Add entries for new database in the listener.ora and tnsnames.ora as necessary.

# 19. Finished
That's it!

Reference:
http://www.shutdownabort.com/quickguides/clone_hot.php

Tom Kytes Challenge

I Came across a wonderful Challenge Tom(Thomas Kyte) gave to people out there some time back, while going through his asktom site.

The challenge is simple(not as it seems) : Have to correctly provide ALL of the versions the following features were added to Oracle.

Thought interesting so blogged it here.

Happy reading, fun along with knowledge.....just go on

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

ops$tkyte%ORA10GR2> select distinct version from features order by version;

VERSION
--------------------
10.1
10.2
11.1
2
3
4
5
6
7.0
7.1
7.2
7.3
8.0
8.1.5
8.1.6
8.1.7
9.0
9.2

18 rows selected.

So, here are the features:

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select rownum, txt
2 from (select txt from features order by rnd)
3 /

ROWNUM TXT
---------- ----------------------------------------------------------------------
1 Real Application Testing
2 Read only Replication
3 Distributed Query
4 Drop column
5 Client-Server (where the client could be elsewhere in the network)
6 Object Relational Features
7 Ability to return result sets from stored procedures (ref cursors)
8 Commit and Rollback (transactions)
9 Triggers
10 Function based indexes
11 Materialized Views
12 Rman
13 Audit SYSDBA/SYSOPER activity
14 Automatic Undo Management
15 Resumable Operations
16 Automatic Storage Management (ASM)
17 Streams
18 Bitmap Indexes
19 csscan - Character Set Scanner utility
20 Flashback Query
21 Case statement (IN SQL, instead of decode)
22 Parallel Query
23 Transparent column level encryption
24 Tablespace encryption
25 PL/SQL
26 Partitioning
27 Row Level Locking
28 Read Consistency (my favorite feature!)
29 2 Phase Commit
30 Sorted Hash Clusters
31 Conditional compilation for PL/SQL
32 Connect By Queries (select ename, level from emp connect by prior....)
33 Update anywhere Replication

33 rows selected.

=======================================
Check your answers:


select rownum, version, txt
2 from (select version, txt from features order by rnd)
3 /

or

col txt for a40
select * from features order by
to_number(substr(version,1,decode(instr(version,'.'),0,1,instr(version,'.'))))
/



ROWNUM VERSION TXT
---------- -------------------- ----------------------------------------
1 8.1.5 Materialized Views
2 10.1 Sorted Hash Clusters
3 8.0 Rman
4 8.1.6 Case statement
5 7.0 2 Phase Commit
6 11.1 Real Application Testing
7 2 Connect By Queries (select ename, level
from emp connect by prior....)

8 9.0 Automatic Undo Management
9 3 Commit and Rollback (transactions)
10 7.2 Ability to return result sets from store
d procedures (ref cursors)

11 8.1.5 Function based indexes
12 11.1 Tablespace encryption
13 8.1.6 csscan - Character Set Scanner utility
14 4 Read Consistency (my favorite feature!)
15 7.0 Triggers
16 10.1 Automatic Storage Management (ASM)
17 7.1 Update anywhere Replication
18 5 Distributed Query
19 7.1 Parallel Query
20 5 Client-Server (where the client could be
elsewhere in the network)

21 10.2 Transparent column level encryption
22 10.2 Conditional compilation for PL/SQL
23 8.1.5 Drop column
24 9.2 Streams
25 8.0 Object Relational Features
26 9.0 Flashback Query
27 9.2 Audit SYSDBA/SYSOPER activity
28 7.3 Bitmap Indexes
29 6 PL/SQL
30 7.0 Read only Replication
31 6 Row Level Locking
32 8.0 Partitioning
33 9.0 Resumable Operations
================================================================================

Want to know how Tom exactly, created this table :

here is the script,

/*

drop table features;
create table features( rnd number, version varchar2(20), txt varchar2(100) );

insert into features values ( dbms_random.random, '2', 'Connect By Queries (select ename, level
from emp connect by prior....)');
insert into features values ( dbms_random.random, '3', 'Commit and Rollback (transactions)');
insert into features values ( dbms_random.random, '4', 'Read Consistency (my favorite feature!)');
insert into features values ( dbms_random.random, '5', 'Client-Server (where the client could be
elsewhere in the network)');
insert into features values ( dbms_random.random, '5', 'Distributed Query');
insert into features values ( dbms_random.random, '6', 'Row Level Locking');
insert into features values ( dbms_random.random, '6', 'PL/SQL');
insert into features values ( dbms_random.random, '7.0', '2 Phase Commit');
insert into features values ( dbms_random.random, '7.0', 'Triggers');
insert into features values ( dbms_random.random, '7.0', 'Read only Replication');
insert into features values ( dbms_random.random, '7.1', 'Update anywhere Replication');
insert into features values ( dbms_random.random, '7.1', 'Parallel Query');
insert into features values ( dbms_random.random, '7.2', 'Ability to return result sets from stored
procedures (ref cursors)');
insert into features values ( dbms_random.random, '7.3', 'Bitmap Indexes');
insert into features values ( dbms_random.random, '8.0', 'Object Relational Features');
insert into features values ( dbms_random.random, '8.0', 'Partitioning');
insert into features values ( dbms_random.random, '8.0', 'Rman');
insert into features values ( dbms_random.random, '8.1.5', 'Materialized Views');
insert into features values ( dbms_random.random, '8.1.5', 'Function based indexes');
insert into features values ( dbms_random.random, '8.1.5', 'Drop column');
insert into features values ( dbms_random.random, '8.1.6', 'Case statement');
insert into features values ( dbms_random.random, '8.1.6', 'csscan - Character Set Scanner
utility');
insert into features values ( dbms_random.random, '9.0', 'Automatic Undo Management');
insert into features values ( dbms_random.random, '9.0', 'Resumable Operations');
insert into features values ( dbms_random.random, '9.0', 'Flashback Query');
insert into features values ( dbms_random.random, '9.2', 'Streams');
insert into features values ( dbms_random.random, '9.2', 'Audit SYSDBA/SYSOPER activity');
insert into features values ( dbms_random.random, '10.1', 'Automatic Storage Management (ASM)');
insert into features values ( dbms_random.random, '10.1', 'Sorted Hash Clusters');
insert into features values ( dbms_random.random, '10.2', 'Conditional compilation for PL/SQL');
insert into features values ( dbms_random.random, '10.2', 'Transparent column level encryption');
insert into features values ( dbms_random.random, '11.1', 'Tablespace encryption');
insert into features values ( dbms_random.random, '11.1', 'Real Application Testing');

select distinct version from features order by version;
*/

===================================================================
Tom Kytes web sites...

http://tkyte.blogspot.com/
http://asktom.oracle.com/