Instance recovery occurs in two steps:
1.Cache Recovery
2.Transaction Recovery
Cache recovery:
Changes being made to a database are recorded in the database buffer cache. These changes are also recorded in online redo log files simultaneously.
When there are enough data in the database buffer cache, they are written to data files.
If an Oracle instance fails before the data in the database buffer cache are written to data files, Oracle uses the data recorded in the online redo log files to recover the lost data when the associated database is re-started. This process is called cache recovery.
Transaction recovery:
When a transaction modifies data in a database, the before image of the modified data is stored in an undo segment.The data stored in the undo segment is used to restore the original values in case a transaction is rolled back. At the time of an instance failure, the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files.(We don't have any mechanism to track this)
To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started. Oracle uses the undo data stored in undo segments to accomplish this. This process is called transaction recovery.
Thursday, September 22, 2011
ORA-01565
ORA-01565: error in identifying file /dbs/spfile@.ora
When we’re going to create pfile from spfile while spfile running on ASM instance, We can face the below errors,
SQL> create pfile=’/u01/inittest.ora’ from spfile;
create pfile=’/u01/inittest.ora’ from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file ‘?/dbs/spfile@.ora’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Solution :
We need to give ASM path of SPFILE.
SQL> create pfile=’/u01/inittest.ora’ from spfile=’+DATA/dbasm/spfiledbasm.ora’;
File created.
When we’re going to create pfile from spfile while spfile running on ASM instance, We can face the below errors,
SQL> create pfile=’/u01/inittest.ora’ from spfile;
create pfile=’/u01/inittest.ora’ from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file ‘?/dbs/spfile@.ora’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Solution :
We need to give ASM path of SPFILE.
SQL> create pfile=’/u01/inittest.ora’ from spfile=’+DATA/dbasm/spfiledbasm.ora’;
File created.
ORA-01031
Could not validate asmsnmp password due to following error ora-01031:insufficent privileges
[oracle@db1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
[oracle@db1 ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin
[oracle@db1 ~]$ export ORACLE_SID=+ASM
[oracle@db1 ~]$ sqlplus sys/sys as sysasm
SQL> create user asmsnmp identified by asmsnmp;
User created.
SQL> grant sysdba to asmsnmp;
Grant succeeded.
[oracle@db1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
[oracle@db1 ~]$ export PATH=/u01/app/oracle/product/11.2.0/grid/bin
[oracle@db1 ~]$ export ORACLE_SID=+ASM
[oracle@db1 ~]$ sqlplus sys/sys as sysasm
SQL> create user asmsnmp identified by asmsnmp;
User created.
SQL> grant sysdba to asmsnmp;
Grant succeeded.
Encryption for RMAN Backup
RAM backups are unencrypted.
we can encrypt any rman backup in the form of a backup set.
we can encrypt in two ways ie transport and password encryption
Password Encryption:
$export ORACLE_SID=TEMP
$rlwrap rman target sys/oracle
rman>set encryption on identified only;
Here i am choosing encryption on tablespace ie users
rman>configure encryption for tablespace users on;
rman>sql'alter tablespace users offline';
rman>restore tablespace users;
failed we need to
Rman>set decryption identified by;
Rman>restore tablespace users;
rman> recover tablespace users;
rman>sql'alter tablespace users online';
we can encrypt any rman backup in the form of a backup set.
we can encrypt in two ways ie transport and password encryption
Password Encryption:
$export ORACLE_SID=TEMP
$rlwrap rman target sys/oracle
rman>set encryption on identified
Here i am choosing encryption on tablespace ie users
rman>configure encryption for tablespace users on;
rman>sql'alter tablespace users offline';
rman>restore tablespace users;
failed we need to
Rman>set decryption identified by
Rman>restore tablespace users;
rman> recover tablespace users;
rman>sql'alter tablespace users online';
Wednesday, September 21, 2011
Duplicate Database Using RMAN
You want to use RMAN to create a duplicate database on the same server by using RMAN backups.
My Source Database Name is MYTEST
Target (duplicate) Database name is MYDB
Step 1: Configure Listener.ora and tnsnames.ora files
tnsnames.ora file
MYTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mytest)
)
)
MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb)
)
)
listener.ora file
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mytest)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mytest)
)
(SID_DESC =
(GLOBAL_DBNAME= mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mydb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
Step 2: create pfile for target database from source database
SQL> create pfile from spfile;
File created.
Step 3: modified parameter file for target database (duplicate) here log_file_name_convert and db_file_name_convert and control file location is important, further your choice.
*.audit_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/adump’
*.compatible=’10.2.0.1.0′
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’mydb’
*.control_files=’/home/oracle/oracle/product/10.2.0/oradata/mydb/control01.ctl’,'/home/oracle/oracle/product/10.2.0/oradata/mydb/control02.ctl’,'/home/oracle/oracle/product/10.2.0/oradata/mydb/control03.ctl’
*.db_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,'/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.log_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,'/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.db_recovery_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=mydbXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=285212672
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
Step 4: Create password file
[oracle@localhost ~]$orapwd file=orapwmydb password=oracle entries=5
Step 5: start your target database nomount stage
[oracle@localhost ~]$ export ORACLE_SID=mydb
[oracle@localhost ~]$ rlwrap sqlplus
SQL*Plus: Release 10.2.0.1.0 – Production on Tue May 25 18:22:09 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys/oracle@mydb as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> startup nomount pfile=/home/oracle/oracle/product/10.2.0/db_1/dbs/initmydb.ora
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> exit
Step 6: From source database
SQL> alter system switch logfile;
System altered.
SQL>
Step 7: Backup database and archivelog Source database
[oracle@localhost root]$ rlwrap rman target sys/azar@mytest
Recovery Manager: Release 10.2.0.1.0 – Production on Tue May 25 19:56:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MYTEST (DBID=2419076664)
RMAN> backup database plus archivelog;
Starting backup at 25-MAY-10
current log archived
input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/system01.dbf
input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/sysaux01.dbf
input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAY-10
channel ORA_DISK_1: finished piece 1 at 25-MAY-10
(skipped)
Starting Control File and SPFILE Autobackup at 25-MAY-10
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/autobackup/2010_05_25/o1_mf_s_719956316_5zr01f85_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAY-10
Step 8: Connect auxiliary database
RMAN> connect auxiliary sys/oracle@mydb;
connected to auxiliary database: MYDB (not mounted)
Step 9: Create duplicate database.
RMAN> duplicate target database to mydb;
Starting Duplicate Db at 25-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
contents of Memory Script:
{
set until scn 470310;
set newname for datafile 1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf”;
set newname for datafile 2 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
set newname for datafile 3 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
set newname for datafile 4 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf”;
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 25-MAY-10
using channel ORA_AUX_DISK_1
skipping datafile 1; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
skipping datafile 2; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
skipping datafile 3; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
skipping datafile 4; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 25-MAY-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
contents of Memory Script:
{
set until scn 470310;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 25-MAY-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
starting media recovery
archive log thread 1 sequence 9 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 25-MAY-10
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
set newname for tempfile 1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf”;
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf in control file
cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf recid=1 stamp=719956593
cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf recid=2 stamp=719956593
cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf recid=3 stamp=719956593
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 25-MAY-10
RMAN>
Step 10: My duplicate database succesfully created.
Enter user-name: sys/oracle@mydb as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> select status from v$instance;
STATUS
————
OPEN
SQL> select name from V$database;
NAME
———
MYDB
SQL>
My Source Database Name is MYTEST
Target (duplicate) Database name is MYDB
Step 1: Configure Listener.ora and tnsnames.ora files
tnsnames.ora file
MYTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mytest)
)
)
MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb)
)
)
listener.ora file
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mytest)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mytest)
)
(SID_DESC =
(GLOBAL_DBNAME= mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME = mydb)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
)
Step 2: create pfile for target database from source database
SQL> create pfile from spfile;
File created.
Step 3: modified parameter file for target database (duplicate) here log_file_name_convert and db_file_name_convert and control file location is important, further your choice.
*.audit_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/admin/mydb/adump’
*.compatible=’10.2.0.1.0′
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’mydb’
*.control_files=’/home/oracle/oracle/product/10.2.0/oradata/mydb/control01.ctl’,'/home/oracle/oracle/product/10.2.0/oradata/mydb/control02.ctl’,'/home/oracle/oracle/product/10.2.0/oradata/mydb/control03.ctl’
*.db_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,'/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.log_file_name_convert=’/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest’,'/home/oracle/oracle/product/10.2.0/oradata/mydb’
*.db_recovery_file_dest=’/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=mydbXDB)’
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=285212672
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
Step 4: Create password file
[oracle@localhost ~]$orapwd file=orapwmydb password=oracle entries=5
Step 5: start your target database nomount stage
[oracle@localhost ~]$ export ORACLE_SID=mydb
[oracle@localhost ~]$ rlwrap sqlplus
SQL*Plus: Release 10.2.0.1.0 – Production on Tue May 25 18:22:09 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys/oracle@mydb as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> startup nomount pfile=/home/oracle/oracle/product/10.2.0/db_1/dbs/initmydb.ora
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> exit
Step 6: From source database
SQL> alter system switch logfile;
System altered.
SQL>
Step 7: Backup database and archivelog Source database
[oracle@localhost root]$ rlwrap rman target sys/azar@mytest
Recovery Manager: Release 10.2.0.1.0 – Production on Tue May 25 19:56:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MYTEST (DBID=2419076664)
RMAN> backup database plus archivelog;
Starting backup at 25-MAY-10
current log archived
input datafile fno=00001 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/system01.dbf
input datafile fno=00003 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/sysaux01.dbf
input datafile fno=00002 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/db_1/oradata/mytest/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-MAY-10
channel ORA_DISK_1: finished piece 1 at 25-MAY-10
(skipped)
Starting Control File and SPFILE Autobackup at 25-MAY-10
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/autobackup/2010_05_25/o1_mf_s_719956316_5zr01f85_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-MAY-10
Step 8: Connect auxiliary database
RMAN> connect auxiliary sys/oracle@mydb;
connected to auxiliary database: MYDB (not mounted)
Step 9: Create duplicate database.
RMAN> duplicate target database to mydb;
Starting Duplicate Db at 25-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
contents of Memory Script:
{
set until scn 470310;
set newname for datafile 1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf”;
set newname for datafile 2 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
set newname for datafile 3 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
set newname for datafile 4 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf”;
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 25-MAY-10
using channel ORA_AUX_DISK_1
skipping datafile 1; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf
skipping datafile 2; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
skipping datafile 3; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
skipping datafile 4; already restored to file /home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
restore not done; all files readonly, offline, or already restored
Finished restore at 25-MAY-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956582 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
contents of Memory Script:
{
set until scn 470310;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 25-MAY-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK
starting media recovery
archive log thread 1 sequence 9 is already on disk as file /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc
archive log filename=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYTEST/archivelog/2010_05_25/o1_mf_1_9_5zr01bl8_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 25-MAY-10
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “MYDB” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo01.log’ ) SIZE 50 M REUSE,
GROUP 2 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo02.log’ ) SIZE 50 M REUSE,
GROUP 3 ( ‘/home/oracle/oracle/product/10.2.0/oradata/mydb/redo03.log’ ) SIZE 50 M REUSE
DATAFILE
‘/home/oracle/oracle/product/10.2.0/oradata/mydb/system01.dbf’
CHARACTER SET WE8ISO8859P1
contents of Memory Script:
{
set newname for tempfile 1 to
“/home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf”;
catalog clone datafilecopy “/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf”;
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /home/oracle/oracle/product/10.2.0/oradata/mydb/temp01.dbf in control file
cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf recid=1 stamp=719956593
cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf recid=2 stamp=719956593
cataloged datafile copy
datafile copy filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf recid=3 stamp=719956593
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=719956593 filename=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 25-MAY-10
RMAN>
Step 10: My duplicate database succesfully created.
Enter user-name: sys/oracle@mydb as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> select status from v$instance;
STATUS
————
OPEN
SQL> select name from V$database;
NAME
———
MYDB
SQL>
Sunday, August 28, 2011
Scripts
Administration - Status
Startup time
SQL> select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time" from v$instance;
Startup time
---------------
13:13 28-AUG-11
How large is the database
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;
Database Size Used space Free space
-------------------- -------------------- --------------------
1 GB 1 GB 0 GB
Distribution of objects and data
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
Owner Objects size MB
------------------------------ ------------ ------------
SYS 22,904 531
SYSMAN 1,321 51
XDB 680 49
MDSYS 885 33
SYSTEM 454 23
OLAPSYS 720 16
WMSYS 242 7
CTXSYS 339 5
EXFSYS 281 4
DBSNMP 46 2
ORDSYS 1,669 1
DMSYS 189 1
SCOTT 9 1
OUTLN 8 1
TSMSYS 3 1
PUBLIC 19,981 0
ORDPLUGINS 10 0
SI_INFORMTN_SCHEMA 8 0
Show the ten largest objects in the database
col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/
OWNER SEGMENT_NAME SEGMENT_TYPE MB
--------------- ------------------------------ --------------- ------------
SYS IDL_UB1$ TABLE 167
SYS SOURCE$ TABLE 41
MDSYS SYS_LOB0000046123C00006$$ LOBSEGMENT 18
SYS IDL_UB2$ TABLE 16
SYS C_TOID_VERSION# CLUSTER 16
SYS PK_C0 INDEX 10
SYS PK_CS INDEX 10
SYS PK_O INDEX 10
SYS PK_CT INDEX 10
SYS SYS_LOB0000046606C00004$$ LOBSEGMENT 9
10 rows selected.
Is java installed in the database?
select count(*) from all_objects where object_type like '%JAVA%'and owner = 'SYS';
COUNT(*)
----------
15741
Display character set information
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0
Show all used features
NAME DETECTED_USAGES
-------------------------------------------------- ---------------
Partitioning (system) 1
Protection Mode - Maximum Performance 1
Streams (system) 1
Streams (user) 1
Virtual Private Database (VPD) 1
Automatic Segment Space Management (system) 1
Automatic Segment Space Management (user) 1
Automatic SQL Execution Memory 1
Automatic Undo Management 1
Character Set 1
Dynamic SGA 1
Locally Managed Tablespaces (system) 1
Locally Managed Tablespaces (user) 1
Administration - Session
Show all connected users
set lines 100 pages 999
col ID format a15
select username
, sid || ',' || serial# "ID"
, status
, last_call_et "Last Activity"
from v$session
where username is not null
order by status desc
, last_call_et desc
/
USERNAME ID STATUS Last Activity
------------------------------ --------------- -------- -------------
WEBUSER 116,18996 INACTIVE 1766
WEBUSER 132,33364 INACTIVE 1727
WEBUSER 127,4392 INACTIVE 1709
WEBUSER 124,20787 INACTIVE 1682
SYS 143,10 INACTIVE 436
ORA_BLA 126,37072 INACTIVE 3
ORA_BLA 115,6676 INACTIVE 3
ORA_BLA 139,5349 INACTIVE 3
REP_USER 141,3881 ACTIVE 0
REP_USER 144,17898 ACTIVE 0
Time since last user activity
set lines 100 pages 999
select username
, floor(last_call_et / 60) "Minutes"
, status
from v$session
where username is not null
order by last_call_et
/
USERNAME Minutes STATUS
------------------------------ ---------- --------
SYS 0 ACTIVE
ORA_BLA 1 INACTIVE
ORA_BLA 1 INACTIVE
ORA_BLA 1 INACTIVE
SYS 8 INACTIVE
WEBUSER 29 INACTIVE
WEBUSER 29 INACTIVE
WEBUSER 30 INACTIVE
WEBUSER 30 INACTIVE
REP_USER 34734 INACTIVE
Sessions sorted by logon time
set lines 100 pages 999
col ID format a15
col osuser format a15
col login_time format a14
select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/
USERNAME OSUSER ID STATUS LOGIN_TIME LAST_CALL_ET
--------------- --------------- ---------- -------- -------------- ------------
ORA_BLA 115,6676 INACTIVE 06:51 19/05/08 181
REP_USER oracle 141,3881 ACTIVE 08:17 19/05/08 0
REP_USER oracle 144,17898 ACTIVE 08:18 19/05/08 0
REP_USER oracle 129,8407 ACTIVE 08:23 19/05/08 0
REP_USER oracle 145,19887 ACTIVE 08:28 19/05/08 0
ORA_BLA 139,5349 INACTIVE 09:51 19/05/08 181
ORA_BLA 126,37072 INACTIVE 09:51 19/05/08 181
WEBUSER oracle 116,18996 INACTIVE 10:51 19/05/08 1944
WEBUSER oracle 127,4392 INACTIVE 10:52 19/05/08 1887
WEBUSER oracle 124,20787 INACTIVE 10:52 19/05/08 1860
WEBUSER oracle 132,33364 INACTIVE 10:52 19/05/08 1905
SYS oracle 131,34710 ACTIVE 10:56 19/05/08 0
SYS oracle 143,10 INACTIVE 14:13 16/04/08 614
Show user info including os pid
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
/
SID/SERIAL USERNAME OSUSER OS PID PROGRAM
---------- --------------- --------------- ------------ ----------------------------------------
170,1 oracle 8339 oracle@dg1.ecsme.com (PMON)
169,1 oracle 8341 oracle@dg1.ecsme.com (PSP0)
168,1 oracle 8343 oracle@dg1.ecsme.com (MMAN)
166,1 oracle 8345 oracle@dg1.ecsme.com (DBW0)
167,1 oracle 8347 oracle@dg1.ecsme.com (LGWR)
162,1 oracle 8349 oracle@dg1.ecsme.com (CKPT)
165,1 oracle 8351 oracle@dg1.ecsme.com (SMON)
163,1 oracle 8353 oracle@dg1.ecsme.com (RECO)
164,1 oracle 8355 oracle@dg1.ecsme.com (CJQ0)
161,1 oracle 8357 oracle@dg1.ecsme.com (MMON)
160,1 oracle 8359 oracle@dg1.ecsme.com (MMNL)
154,1 oracle 8382 oracle@dg1.ecsme.com (QMNC)
150,1 oracle 8400 oracle@dg1.ecsme.com (q000)
146,1 oracle 8404 oracle@dg1.ecsme.com (q001)
159,16 SYS oracle 9791 sqlplus@dg1.ecsme.com (TNS V1-V3)
148,195 oracle 9888 oracle@dg1.ecsme.com (J000)
Show a users current sql
Select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like '&username')
/
Enter value for username: SYS
old 6: where username like '&username')
new 6: where username like 'SYS')
SQL_TEXT
----------------------------------------------------------------------------------------------------
Select sql_text from v$sqlarea where (address, hash_value) in (select sql_address, sql_hash_value
from v$session where username like 'SYS')
Session status associated with the specified os process id
select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid'
/
USERNAME SID SERIAL# SPID LAST_CALL_ET STATUS
--------------- ---------- ---------- ------------ ------------ --------
SYS 159 16 9791 0 ACTIVE
All active sql
set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
for x in
(select username||'('||sid||','||serial#||') ospid = '|| process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address,
sql_hash_value
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null ) loop
for y in (select sql_text
from v$sqlarea
where address = x.sql_address ) loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
/
List open cursors per user
set pages 999
select sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value
/
USERNAME SID SERIAL# CURSORS
--------------- ---------- ---------- ----------
SYS 152 122 1
SYS 159 16 4
or
set lines 100 pages 999
select count(hash_value) cursors
, sid
, user_name
from v$open_cursor
group by
sid
, user_name
order by
cursors
/
CURSORS SID USER_NAME
---------- ---------- ------------------------------
1 150 SYS
1 148 SYS
1 156 SYS
2 149 SYS
5 164 SYS
5 145 SYS
7 147 SYS
7 155 SYS
7 159 SYS
8 143 SYS
15 161 SYS
17 153 SYS
22 158 SYS
38 152 SYS
Administration - Init Parameters
Show latent parameter changes
select name,value from v$parameter where ismodified != 'FALSE';
Show non-default parameters
set pages 999 lines 100
col name format a30
col value format a50
select name,value from v$parameter where isdefault = 'FALSE' and value is not null order by name;
or for name='parameter' style...
set pages 999 lines 100
select name || '=' || decode(type, 2, '''') || value || decode(type, 2, '''') parameter from v$parameter where isdefault = 'FALSE'
and value is not null order by name;
Reset/Unset a spfile parameter
Setting a parameter to =' ' often isn't enough. Do this instead...
alter system reset scope=spfile sid='*'
/
The sid='*' bit is always necessary, even in non RAC database.
Startup time
SQL> select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time" from v$instance;
Startup time
---------------
13:13 28-AUG-11
How large is the database
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;
Database Size Used space Free space
-------------------- -------------------- --------------------
1 GB 1 GB 0 GB
Distribution of objects and data
set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
Owner Objects size MB
------------------------------ ------------ ------------
SYS 22,904 531
SYSMAN 1,321 51
XDB 680 49
MDSYS 885 33
SYSTEM 454 23
OLAPSYS 720 16
WMSYS 242 7
CTXSYS 339 5
EXFSYS 281 4
DBSNMP 46 2
ORDSYS 1,669 1
DMSYS 189 1
SCOTT 9 1
OUTLN 8 1
TSMSYS 3 1
PUBLIC 19,981 0
ORDPLUGINS 10 0
SI_INFORMTN_SCHEMA 8 0
Show the ten largest objects in the database
col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/
OWNER SEGMENT_NAME SEGMENT_TYPE MB
--------------- ------------------------------ --------------- ------------
SYS IDL_UB1$ TABLE 167
SYS SOURCE$ TABLE 41
MDSYS SYS_LOB0000046123C00006$$ LOBSEGMENT 18
SYS IDL_UB2$ TABLE 16
SYS C_TOID_VERSION# CLUSTER 16
SYS PK_C0 INDEX 10
SYS PK_CS INDEX 10
SYS PK_O INDEX 10
SYS PK_CT INDEX 10
SYS SYS_LOB0000046606C00004$$ LOBSEGMENT 9
10 rows selected.
Is java installed in the database?
select count(*) from all_objects where object_type like '%JAVA%'and owner = 'SYS';
COUNT(*)
----------
15741
Display character set information
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0
Show all used features
NAME DETECTED_USAGES
-------------------------------------------------- ---------------
Partitioning (system) 1
Protection Mode - Maximum Performance 1
Streams (system) 1
Streams (user) 1
Virtual Private Database (VPD) 1
Automatic Segment Space Management (system) 1
Automatic Segment Space Management (user) 1
Automatic SQL Execution Memory 1
Automatic Undo Management 1
Character Set 1
Dynamic SGA 1
Locally Managed Tablespaces (system) 1
Locally Managed Tablespaces (user) 1
Administration - Session
Show all connected users
set lines 100 pages 999
col ID format a15
select username
, sid || ',' || serial# "ID"
, status
, last_call_et "Last Activity"
from v$session
where username is not null
order by status desc
, last_call_et desc
/
USERNAME ID STATUS Last Activity
------------------------------ --------------- -------- -------------
WEBUSER 116,18996 INACTIVE 1766
WEBUSER 132,33364 INACTIVE 1727
WEBUSER 127,4392 INACTIVE 1709
WEBUSER 124,20787 INACTIVE 1682
SYS 143,10 INACTIVE 436
ORA_BLA 126,37072 INACTIVE 3
ORA_BLA 115,6676 INACTIVE 3
ORA_BLA 139,5349 INACTIVE 3
REP_USER 141,3881 ACTIVE 0
REP_USER 144,17898 ACTIVE 0
Time since last user activity
set lines 100 pages 999
select username
, floor(last_call_et / 60) "Minutes"
, status
from v$session
where username is not null
order by last_call_et
/
USERNAME Minutes STATUS
------------------------------ ---------- --------
SYS 0 ACTIVE
ORA_BLA 1 INACTIVE
ORA_BLA 1 INACTIVE
ORA_BLA 1 INACTIVE
SYS 8 INACTIVE
WEBUSER 29 INACTIVE
WEBUSER 29 INACTIVE
WEBUSER 30 INACTIVE
WEBUSER 30 INACTIVE
REP_USER 34734 INACTIVE
Sessions sorted by logon time
set lines 100 pages 999
col ID format a15
col osuser format a15
col login_time format a14
select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/
USERNAME OSUSER ID STATUS LOGIN_TIME LAST_CALL_ET
--------------- --------------- ---------- -------- -------------- ------------
ORA_BLA 115,6676 INACTIVE 06:51 19/05/08 181
REP_USER oracle 141,3881 ACTIVE 08:17 19/05/08 0
REP_USER oracle 144,17898 ACTIVE 08:18 19/05/08 0
REP_USER oracle 129,8407 ACTIVE 08:23 19/05/08 0
REP_USER oracle 145,19887 ACTIVE 08:28 19/05/08 0
ORA_BLA 139,5349 INACTIVE 09:51 19/05/08 181
ORA_BLA 126,37072 INACTIVE 09:51 19/05/08 181
WEBUSER oracle 116,18996 INACTIVE 10:51 19/05/08 1944
WEBUSER oracle 127,4392 INACTIVE 10:52 19/05/08 1887
WEBUSER oracle 124,20787 INACTIVE 10:52 19/05/08 1860
WEBUSER oracle 132,33364 INACTIVE 10:52 19/05/08 1905
SYS oracle 131,34710 ACTIVE 10:56 19/05/08 0
SYS oracle 143,10 INACTIVE 14:13 16/04/08 614
Show user info including os pid
col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
/
SID/SERIAL USERNAME OSUSER OS PID PROGRAM
---------- --------------- --------------- ------------ ----------------------------------------
170,1 oracle 8339 oracle@dg1.ecsme.com (PMON)
169,1 oracle 8341 oracle@dg1.ecsme.com (PSP0)
168,1 oracle 8343 oracle@dg1.ecsme.com (MMAN)
166,1 oracle 8345 oracle@dg1.ecsme.com (DBW0)
167,1 oracle 8347 oracle@dg1.ecsme.com (LGWR)
162,1 oracle 8349 oracle@dg1.ecsme.com (CKPT)
165,1 oracle 8351 oracle@dg1.ecsme.com (SMON)
163,1 oracle 8353 oracle@dg1.ecsme.com (RECO)
164,1 oracle 8355 oracle@dg1.ecsme.com (CJQ0)
161,1 oracle 8357 oracle@dg1.ecsme.com (MMON)
160,1 oracle 8359 oracle@dg1.ecsme.com (MMNL)
154,1 oracle 8382 oracle@dg1.ecsme.com (QMNC)
150,1 oracle 8400 oracle@dg1.ecsme.com (q000)
146,1 oracle 8404 oracle@dg1.ecsme.com (q001)
159,16 SYS oracle 9791 sqlplus@dg1.ecsme.com (TNS V1-V3)
148,195 oracle 9888 oracle@dg1.ecsme.com (J000)
Show a users current sql
Select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like '&username')
/
Enter value for username: SYS
old 6: where username like '&username')
new 6: where username like 'SYS')
SQL_TEXT
----------------------------------------------------------------------------------------------------
Select sql_text from v$sqlarea where (address, hash_value) in (select sql_address, sql_hash_value
from v$session where username like 'SYS')
Session status associated with the specified os process id
select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid'
/
USERNAME SID SERIAL# SPID LAST_CALL_ET STATUS
--------------- ---------- ---------- ------------ ------------ --------
SYS 159 16 9791 0 ACTIVE
All active sql
set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
for x in
(select username||'('||sid||','||serial#||') ospid = '|| process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address,
sql_hash_value
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null ) loop
for y in (select sql_text
from v$sqlarea
where address = x.sql_address ) loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
Display any long operations
set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
/
List open cursors per user
set pages 999
select sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value
/
USERNAME SID SERIAL# CURSORS
--------------- ---------- ---------- ----------
SYS 152 122 1
SYS 159 16 4
or
set lines 100 pages 999
select count(hash_value) cursors
, sid
, user_name
from v$open_cursor
group by
sid
, user_name
order by
cursors
/
CURSORS SID USER_NAME
---------- ---------- ------------------------------
1 150 SYS
1 148 SYS
1 156 SYS
2 149 SYS
5 164 SYS
5 145 SYS
7 147 SYS
7 155 SYS
7 159 SYS
8 143 SYS
15 161 SYS
17 153 SYS
22 158 SYS
38 152 SYS
Administration - Init Parameters
Show latent parameter changes
select name,value from v$parameter where ismodified != 'FALSE';
Show non-default parameters
set pages 999 lines 100
col name format a30
col value format a50
select name,value from v$parameter where isdefault = 'FALSE' and value is not null order by name;
or for name='parameter' style...
set pages 999 lines 100
select name || '=' || decode(type, 2, '''') || value || decode(type, 2, '''') parameter from v$parameter where isdefault = 'FALSE'
and value is not null order by name;
Reset/Unset a spfile parameter
Setting a parameter to =' ' often isn't enough. Do this instead...
alter system reset
/
The sid='*' bit is always necessary, even in non RAC database.
Thursday, May 5, 2011
Configure VNC Server on Solaris
root# ./vncserver
vncserver: couldn't find "Xvnc" on your PATH.
root# cd /usr/X11/bin
root# ls
Xvnc ......
root# pwd
/usr/X11/bin
root# export PATH=$PATH:/usr/X11/bin:/usr/openwin/bin
root# vncserver
You will require a password to access your desktops.
Password:
Verify:
New 'hostname:1 (root)' desktop is hostname:1
Creating default startup script //.vnc/xstartup
Starting applications specified in //.vnc/xstartup
Log file is //.vnc/hostnames:1.log
vncserver: couldn't find "Xvnc" on your PATH.
root# cd /usr/X11/bin
root# ls
Xvnc ......
root# pwd
/usr/X11/bin
root# export PATH=$PATH:/usr/X11/bin:/usr/openwin/bin
root# vncserver
You will require a password to access your desktops.
Password:
Verify:
New 'hostname:1 (root)' desktop is hostname:1
Creating default startup script //.vnc/xstartup
Starting applications specified in //.vnc/xstartup
Log file is //.vnc/hostnames:1.log
Tuesday, February 8, 2011
how to add a swap file Linux
Procedure to add a swap file
You need to use dd command to create swapfile. Next you need to use mkswap command to set up a Linux swap area on a device or in a file.
a) Login as the root user
b) Type following command to create 512MB swap file (1024 * 512MB = 524288 block size):
# dd if=/dev/zero of=/swapfile1 bs=1024 count=524288
c) Set up a Linux swap area:
# mkswap /swapfile1
d) Activate /swapfile1 swap space immediately:
# swapon /swapfile1
e) To activate /swapfile1 after Linux system reboot, add entry to /etc/fstab file. Open this file using text editor such as vi:
# vi /etc/fstab
Append following line:
/swapfile1 swap swap defaults 0 0
So next time Linux comes up after reboot, it enables the new swap file for you automatically.
g) How do I verify swap is activated or not?
Simply use free command:
$ free -m
You need to use dd command to create swapfile. Next you need to use mkswap command to set up a Linux swap area on a device or in a file.
a) Login as the root user
b) Type following command to create 512MB swap file (1024 * 512MB = 524288 block size):
# dd if=/dev/zero of=/swapfile1 bs=1024 count=524288
c) Set up a Linux swap area:
# mkswap /swapfile1
d) Activate /swapfile1 swap space immediately:
# swapon /swapfile1
e) To activate /swapfile1 after Linux system reboot, add entry to /etc/fstab file. Open this file using text editor such as vi:
# vi /etc/fstab
Append following line:
/swapfile1 swap swap defaults 0 0
So next time Linux comes up after reboot, it enables the new swap file for you automatically.
g) How do I verify swap is activated or not?
Simply use free command:
$ free -m
Tuesday, January 25, 2011
Recreate the OraInventory
In cases where the OraInventory is missing or otherwise corrupt, recreate the oraInventory directory on UNIX systems, using the following steps. In a normal installation, there is a Global Inventory (OraInventory) and a Local Inventory ($ORACLE_HOME/inventory).
Locate the oraInst.loc file, which may be in different locations, depending on your system:
/var/opt/oracle/oraInst.loc file
or
/etc/oraInst.loc
Modify the file oraInst.loc file:
cp /var/opt/oracle/oraInst.loc /var/opt/oracle/oraInst.loc.bak
mkdir /u01/oracle/oraInventory
---file contents---
inventory_loc=/u01/oracle/oraInventory
inst_group=oinstall
---file contents---
Important:
Theses example uses a typical directory, considered an $ORACLE_BASE, and a typical UNIX group which installed the Oracle products. Ensure that the correct values are used for your system.
The oraInventory directory is usually a directory under the $ORACLE_HOME. For example, if the $ORACLE_HOME is equal to "/u01/oracle/product/10g", then the OraInventory could be "/u01/oracle/OraInventory".
Change the permissions to be appropriate, (using your directory location):
chmod 644 /var/opt/oracle/oraInst.loc
For consistency, copy the file to Oracle home directory, (using your directory location):
cp $ORACLE_HOME/oraInst.loc $ORACLE_HOME/oraInst.loc.bak
cp /var/opt/oracle/oraInst.loc $ORACLE_HOME/oraInst.loc
Run Oracle Universal Installer from your Oracle home as below, (using your site specific directory location and Oracle home name):
cd $ORACLE_HOME/oui/bin
./runInstaller -silent -attachHome
ORACLE_HOME="/u01/oracle/product/10.2" ORACLE_HOME_NAME="Ora10gHome"
Check the inventory output is correct for your Oracle home:
$ORACLE_HOME/OPatch/opatch lsinventory -detail
If the table at the beginning of the output is showing the proper directories, and the Oracle home components are properly reflected in the details, then the Global Inventory has been successfully created from the Local Inventory. At this time, you may patch an maintain your Oracle home, as normal.
Locate the oraInst.loc file, which may be in different locations, depending on your system:
/var/opt/oracle/oraInst.loc file
or
/etc/oraInst.loc
Modify the file oraInst.loc file:
cp /var/opt/oracle/oraInst.loc /var/opt/oracle/oraInst.loc.bak
mkdir /u01/oracle/oraInventory
---file contents---
inventory_loc=/u01/oracle/oraInventory
inst_group=oinstall
---file contents---
Important:
Theses example uses a typical directory, considered an $ORACLE_BASE, and a typical UNIX group which installed the Oracle products. Ensure that the correct values are used for your system.
The oraInventory directory is usually a directory under the $ORACLE_HOME. For example, if the $ORACLE_HOME is equal to "/u01/oracle/product/10g", then the OraInventory could be "/u01/oracle/OraInventory".
Change the permissions to be appropriate, (using your directory location):
chmod 644 /var/opt/oracle/oraInst.loc
For consistency, copy the file to Oracle home directory, (using your directory location):
cp $ORACLE_HOME/oraInst.loc $ORACLE_HOME/oraInst.loc.bak
cp /var/opt/oracle/oraInst.loc $ORACLE_HOME/oraInst.loc
Run Oracle Universal Installer from your Oracle home as below, (using your site specific directory location and Oracle home name):
cd $ORACLE_HOME/oui/bin
./runInstaller -silent -attachHome
ORACLE_HOME="/u01/oracle/product/10.2" ORACLE_HOME_NAME="Ora10gHome"
Check the inventory output is correct for your Oracle home:
$ORACLE_HOME/OPatch/opatch lsinventory -detail
If the table at the beginning of the output is showing the proper directories, and the Oracle home components are properly reflected in the details, then the Global Inventory has been successfully created from the Local Inventory. At this time, you may patch an maintain your Oracle home, as normal.
SQL*Plus command line history completion
The rlwrap (readline wrapper) utility provides a command history and editing of keyboard input
for any other command. This is a really handy addition to SQL*Plus and RMAN .
Download the latest rlwrap software from the following URL.
http://utopia.knoware.nl/~hlub/uck/rlwrap/
Unzip and install the software using the following commands.
gunzip rlwrap*.gz
tar -xvf rlwrap*.tar
cd rlwrap*
./configure
make
make check
make install
Run the following commands, or better still append then to the ".bash_profile" of the
oracle software owner.
alias sqlplus='rlwrap ${ORACLE_HOME}/bin/sqlplus'
alias rman='rlwrap ${ORACLE_HOME}/bin/rman'
alias expdp='rlwrap ${ORACLE_HOME}/bin/expdp'
You can now start SQL*Plus or RMAN using "sqlplus" and "rman" respectively, and you will have
a basic command history and the current line will be editable using the arrow and delete keys.
for any other command. This is a really handy addition to SQL*Plus and RMAN .
Download the latest rlwrap software from the following URL.
http://utopia.knoware.nl/~hlub/uck/rlwrap/
Unzip and install the software using the following commands.
gunzip rlwrap*.gz
tar -xvf rlwrap*.tar
cd rlwrap*
./configure
make
make check
make install
Run the following commands, or better still append then to the ".bash_profile" of the
oracle software owner.
alias sqlplus='rlwrap ${ORACLE_HOME}/bin/sqlplus'
alias rman='rlwrap ${ORACLE_HOME}/bin/rman'
alias expdp='rlwrap ${ORACLE_HOME}/bin/expdp'
You can now start SQL*Plus or RMAN using "sqlplus" and "rman" respectively, and you will have
a basic command history and the current line will be editable using the arrow and delete keys.
Monday, January 24, 2011
Redhat Package Management (RPM)
Features:
1. Provides package management
a. Query
b. Install
c. Uninstall
d. Upgrade
e. Verify
2. Auto-verifies packages using GPG, MD5, SHA1SUMs
3. Automatically reports on unresolved dependencies
Install (Does NOT overwrite previous package):
Note: Use this method to install a new version of the kernel
1. rpm -ivh *.rpm
2. rpm -ivh http://192.168.75.100/RH5/i386/Server/dhcp-3.0.5-7.el5.i386.rpm
Upgrade (Installs or overwrites existing package):
1. rpm -Uvh *.rpm
2. rpm -Uvh http://192.168.75.100/RH5/i386/Server/dhcp-3.0.5-7.el5.i386.rpm
Freshen (Updates an existing package):
Note: Will NOT install the package, if it doesn't exist locally
1. rpm -Fvh *.rpm - freshens the current version of a package
Removal:
1. rpm -ev *.rpm - removes a pacakge
Note: removal process considers dependencies and will complain if the removal will break 1 or more packages. To get around this, use '--nodeps' option with 'rpm -ev --nodeps *.rpm'
2. rpm -ev gftp
Query:
1. rpm -qa - shows all installed packages
2. rpm -qa | wc -l - this dumps all packages and provides a count
3. rpm -qa | grep -i sendmail
4. rpm -qi nano - dumps info. about the 'sendmail' package as it's recorded in the local RPM database
5. rpm -qf /usr/bin/nano - dumps package membership info. for the 'nano' file
6. rpm -qpi http://192.168.75.100/RH5/i386/Server/dhcp-3.0.5-7.el5.i386.rpm - dumps info. about the uninstalled 'dhcp' package, which resides on the repository
7. rpm -ql package_name - returns all included files
Verify:
1. rpm -Va - verifies ALL packages on the system, returning info. only if there are discrepancies from the original installation
2. rpm -Vf /usr/bin/nano
Task: Change '/usr/bin/nano' then verify
SM5....T /usr/bin/nano
S(file size), M(mode or permissions), 5(MD5), T(mod time)
3. rpm -Vp nano
1. Provides package management
a. Query
b. Install
c. Uninstall
d. Upgrade
e. Verify
2. Auto-verifies packages using GPG, MD5, SHA1SUMs
3. Automatically reports on unresolved dependencies
Install (Does NOT overwrite previous package):
Note: Use this method to install a new version of the kernel
1. rpm -ivh *.rpm
2. rpm -ivh http://192.168.75.100/RH5/i386/Server/dhcp-3.0.5-7.el5.i386.rpm
Upgrade (Installs or overwrites existing package):
1. rpm -Uvh *.rpm
2. rpm -Uvh http://192.168.75.100/RH5/i386/Server/dhcp-3.0.5-7.el5.i386.rpm
Freshen (Updates an existing package):
Note: Will NOT install the package, if it doesn't exist locally
1. rpm -Fvh *.rpm - freshens the current version of a package
Removal:
1. rpm -ev *.rpm - removes a pacakge
Note: removal process considers dependencies and will complain if the removal will break 1 or more packages. To get around this, use '--nodeps' option with 'rpm -ev --nodeps *.rpm'
2. rpm -ev gftp
Query:
1. rpm -qa - shows all installed packages
2. rpm -qa | wc -l - this dumps all packages and provides a count
3. rpm -qa | grep -i sendmail
4. rpm -qi nano - dumps info. about the 'sendmail' package as it's recorded in the local RPM database
5. rpm -qf /usr/bin/nano - dumps package membership info. for the 'nano' file
6. rpm -qpi http://192.168.75.100/RH5/i386/Server/dhcp-3.0.5-7.el5.i386.rpm - dumps info. about the uninstalled 'dhcp' package, which resides on the repository
7. rpm -ql package_name - returns all included files
Verify:
1. rpm -Va - verifies ALL packages on the system, returning info. only if there are discrepancies from the original installation
2. rpm -Vf /usr/bin/nano
Task: Change '/usr/bin/nano' then verify
SM5....T /usr/bin/nano
S(file size), M(mode or permissions), 5(MD5), T(mod time)
3. rpm -Vp nano
IPTables in Linux
Features:
1. Firewall for Linux
2. Interface to Netfilter, which is loaded by the kernel
3. Operates primarily @ layers 3 & 4 of the OSI model
4. Modular
5. Provides Network Address Translation (NAT)
6. IPTables can also access other layers (2, 5-7), with modules
1. grep -i config_netfilter /boot/config*
Note: Save rules in: /etc/sysconfig/iptables so that when IPTables is restarted, the rules will be applied OR, update /etc/sysconfig/iptables-config to save the rules automatically
/sbin/iptables - primary ACL modifier utility
/sbin/iptables-restore - restores rules to current IPTables instance
/sbin/iptables-save - saves rules to STDOUT, by default, or to a file
IPTables includes 3 default tables, which you cannot remove:
1. NAT
2. Mangle
3. Filter (Default) - filters inbound/outbound traffic
Note: Each table, includes chains, which include Access Control Entries (ACEs)
Usage:
1. iptables -L
Note: The Filter table includes 3 chains:
1. INPUT - applies to traffic destined to a service that our system is bound to
2. FORWARD - applies to traffic being routed through the system
3. OUTPUT - applies to traffic sourced from our system, heading outbound
Examples:
1. Filter inbound traffic to remote RH5 system to SSH
a. iptables -A INPUT -p tcp --dport 22 -j ACCEPT
b. iptables -A INPUT -j DROP
2. Filter outbound traffic to ANY remote SSH port
a. iptables -A OUTPUT -p tcp --dport 22 -j DROP
3. Flush ALL rules from OUTPUT chain of the Filter table
a. iptables -F OUTPUT
4. Save rules to file, then flush rules
a. iptables-save > iptables.rules.1
5. Reinstate flushed rules
a. iptables-restore iptables.rules.1
1. Firewall for Linux
2. Interface to Netfilter, which is loaded by the kernel
3. Operates primarily @ layers 3 & 4 of the OSI model
4. Modular
5. Provides Network Address Translation (NAT)
6. IPTables can also access other layers (2, 5-7), with modules
1. grep -i config_netfilter /boot/config*
Note: Save rules in: /etc/sysconfig/iptables so that when IPTables is restarted, the rules will be applied OR, update /etc/sysconfig/iptables-config to save the rules automatically
/sbin/iptables - primary ACL modifier utility
/sbin/iptables-restore - restores rules to current IPTables instance
/sbin/iptables-save - saves rules to STDOUT, by default, or to a file
IPTables includes 3 default tables, which you cannot remove:
1. NAT
2. Mangle
3. Filter (Default) - filters inbound/outbound traffic
Note: Each table, includes chains, which include Access Control Entries (ACEs)
Usage:
1. iptables -L
Note: The Filter table includes 3 chains:
1. INPUT - applies to traffic destined to a service that our system is bound to
2. FORWARD - applies to traffic being routed through the system
3. OUTPUT - applies to traffic sourced from our system, heading outbound
Examples:
1. Filter inbound traffic to remote RH5 system to SSH
a. iptables -A INPUT -p tcp --dport 22 -j ACCEPT
b. iptables -A INPUT -j DROP
2. Filter outbound traffic to ANY remote SSH port
a. iptables -A OUTPUT -p tcp --dport 22 -j DROP
3. Flush ALL rules from OUTPUT chain of the Filter table
a. iptables -F OUTPUT
4. Save rules to file, then flush rules
a. iptables-save > iptables.rules.1
5. Reinstate flushed rules
a. iptables-restore iptables.rules.1
Sunday, January 23, 2011
Server Status & Health Check in Linux
Top :
Top Command can provide the top processes, CPU Load, Memory Utilization, Swap Utilization
Running/Zombie Process, Uptime, etc..
iostat:
This command will provide the information about the disk utilization, Read/Write rate, etc..
vmstat:
vmstat command provides the swap memory utilization report
sar (System Activity Reporter)
sar tool can provide the report about CPU Load, iowait and other useful info at specified intervals.
dmesg
dmesg command displays all the server errors including Hardware, booting, network errors
This command will retrieve the information from log files which located in /var/log
Other Useful commands to collect some info about the server :
runlevel - To Know the Current Run Level
netstat –a - To see the Network sessions
netstat –rn - To View the gateways configured
who / finger - To View the Users logged in detail
ps –ef - To view all the running process and its related files
sestatus - To check SELINUX status
service iptables status - To check firewall status
ifconfig –a - To View the Network cards and IP Address configured
Top Command can provide the top processes, CPU Load, Memory Utilization, Swap Utilization
Running/Zombie Process, Uptime, etc..
iostat:
This command will provide the information about the disk utilization, Read/Write rate, etc..
vmstat:
vmstat command provides the swap memory utilization report
sar (System Activity Reporter)
sar tool can provide the report about CPU Load, iowait and other useful info at specified intervals.
dmesg
dmesg command displays all the server errors including Hardware, booting, network errors
This command will retrieve the information from log files which located in /var/log
Other Useful commands to collect some info about the server :
runlevel - To Know the Current Run Level
netstat –a - To see the Network sessions
netstat –rn - To View the gateways configured
who / finger - To View the Users logged in detail
ps –ef - To view all the running process and its related files
sestatus - To check SELINUX status
service iptables status - To check firewall status
ifconfig –a - To View the Network cards and IP Address configured
Linux Booting Process Sequence
When the computer is switched on, it automatically invokes BIOS [a ROM chip embedded in the motherboard].
The BIOS will start the processor and perform a POST [power on self test] to check whether the connected device are ready to use and are working properly.
Once the POST is completes BIOS will jump to a specified location in the RAM and check for the booting device. The boot sector is always the first sector of the hard disk and BIOS will load the MBR into the memory.
Here the boot loader takes the control of the booting process.
LILO or GRUB is the boot loaders commonly available. It will help the user to select various boot options.
Depending on the boot option selected the kernel is loaded.
After kernel is loaded the kernel will take the control of the booting process
initrd will be loaded which contains drivers to detect hardware (Initialization of RAM Disk)
Then it will initialize all the hardware including I/O processors etc.
Kernel then mounts the root partition as read-only
INIT is loaded.
INIT will mount the root partition and other partitions as read/write and checks for file system errors.
Sets the System Clock, hostname etc..
Based on the Runlevel, it will load the services and runs the startup scripts (Network, cups, nfs, etc.)
Finally it runs the rc.local script.
Now the login prompt will appear.
The BIOS will start the processor and perform a POST [power on self test] to check whether the connected device are ready to use and are working properly.
Once the POST is completes BIOS will jump to a specified location in the RAM and check for the booting device. The boot sector is always the first sector of the hard disk and BIOS will load the MBR into the memory.
Here the boot loader takes the control of the booting process.
LILO or GRUB is the boot loaders commonly available. It will help the user to select various boot options.
Depending on the boot option selected the kernel is loaded.
After kernel is loaded the kernel will take the control of the booting process
initrd will be loaded which contains drivers to detect hardware (Initialization of RAM Disk)
Then it will initialize all the hardware including I/O processors etc.
Kernel then mounts the root partition as read-only
INIT is loaded.
INIT will mount the root partition and other partitions as read/write and checks for file system errors.
Sets the System Clock, hostname etc..
Based on the Runlevel, it will load the services and runs the startup scripts (Network, cups, nfs, etc.)
Finally it runs the rc.local script.
Now the login prompt will appear.
Disable SSH root login
Providing direct login access to root via SSH is not a good practice. Administrators should use sudo to switch to root after logged in as themselves. this will helpful in auditing in terms of security.
Here is the step by step procedure to disable/deny direct root login via SSH
1. Login to the server as Root
2. Edit /etc/ssh/sshd_config
Look for the line,
PermitRootLogin=Yes
and then change the value of it to,
PermitRootLogin=No
3. Restart the sshd service and make sure its turned on
service sshd restart or /etc/init.d/sshd restart
service sshd status
Here is the step by step procedure to disable/deny direct root login via SSH
1. Login to the server as Root
2. Edit /etc/ssh/sshd_config
Look for the line,
PermitRootLogin=Yes
and then change the value of it to,
PermitRootLogin=No
3. Restart the sshd service and make sure its turned on
service sshd restart or /etc/init.d/sshd restart
service sshd status
Delete old log files in Linux
To find files modified more than 5 days use -mtime +5 and files modified less than 5 days use -mtime -5
Use -ctime parameter to find out the created time
To List and Delete log files older than 10 days, execute the following commands
find /var/log/ -name *.log -mtime +10 -exec ls -tl {} \;
find /var/log/ -name *.log -mtime +10 -exec rm -f {} \;
Use -ctime parameter to find out the created time
To List and Delete log files older than 10 days, execute the following commands
find /var/log/ -name *.log -mtime +10 -exec ls -tl {} \;
find /var/log/ -name *.log -mtime +10 -exec rm -f {} \;
Reinstall Grub in Linux
Boot the server using the Linux CD and Type "linux rescue" on the boot prompt.
Once you get the Shell Prompt, Now follow the steps:
# chroot /mnt/sysimage
Now issue the command "grub-install"
For Example:
# grub-install /dev/sda
Now the grub will be reinstalled in the primary hard disk.
Once you get the Shell Prompt, Now follow the steps:
# chroot /mnt/sysimage
Now issue the command "grub-install
For Example:
# grub-install /dev/sda
Now the grub will be reinstalled in the primary hard disk.
Changing hostname without rebooting Linux server
Here the steps for changing the hostname in Linux (Redhat / CentOS / Fedora) without rebooting the server
First you need to find out your current hostname:
# hostname
server1.mydomain.com
Now, Edit /etc/hosts
For example, I need to assign it hostname as ftpsrv.mydomain.com.
To do this, I have edited /etc/hosts as follows.
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
192.168.2.100 ftpsrv.mydomain.com ftpsrv
Edit /etc/sysconfig/network
To change servers hostname to ftpsrv.mydomain.com, change the file as follows.
# cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=ftpsrv.mydomain.com
Now Type the following command in Terminal,
# hostname ftpsrv.mydomain.com
#service syslog restart
Now, You should logout and login back to see the changes
First you need to find out your current hostname:
# hostname
server1.mydomain.com
Now, Edit /etc/hosts
For example, I need to assign it hostname as ftpsrv.mydomain.com.
To do this, I have edited /etc/hosts as follows.
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
192.168.2.100 ftpsrv.mydomain.com ftpsrv
Edit /etc/sysconfig/network
To change servers hostname to ftpsrv.mydomain.com, change the file as follows.
# cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=ftpsrv.mydomain.com
Now Type the following command in Terminal,
# hostname ftpsrv.mydomain.com
#service syslog restart
Now, You should logout and login back to see the changes
Backup and Recovery in Linux using dd command
CREATING A HARD DISK IMAGE FILE
It is better to creating an image file of the hard disk and save it in other storage devices for future recovery. The following command will create an image file "appdisk.img" in your home directory from /dev/sda:
dd if=/dev/sda of=~/appdisk.img
Since you have created an image file, you can compress it with "gzip" or "bzip2":
gzip appdisk.img #generates appdisk.img.gz
It might take very long time.
CREATING A PARTITION IMAGE
Backing up a hard disk partition is much similar to backing up a whole hard disk.
For example, if you want to create an image file from the first partition of /dev/sda, use "dd" like this:
dd if=/dev/sda1 of=~/disk2.img
Also, you can compress the image file:
gzip disk2.img
By the way, you can copy a partition to another partition completely, just set "of" to the partition's device name. For example:
dd if=/dev/sda1 of=/dev/sdb5
This command will copy all the contents from /dev/sda1 to /dev/sdb5.
You must be sure that the capacity of /dev/sdb5 is larger than /dev/sda1.
RESTORING FROM AN IMAGE FILE
To restore a partition or a hard disk from an image file, just exchange the arguments "if" and "of".
For example, restore the whole hard disk from the image file "disk1.img":
dd if=disk1.img of=/dev/sda
Restore the first partition of /dev/sda from the image file "disk2.img":
dd if=disk2.img of=/dev/sda1
It is better to creating an image file of the hard disk and save it in other storage devices for future recovery. The following command will create an image file "appdisk.img" in your home directory from /dev/sda:
dd if=/dev/sda of=~/appdisk.img
Since you have created an image file, you can compress it with "gzip" or "bzip2":
gzip appdisk.img #generates appdisk.img.gz
It might take very long time.
CREATING A PARTITION IMAGE
Backing up a hard disk partition is much similar to backing up a whole hard disk.
For example, if you want to create an image file from the first partition of /dev/sda, use "dd" like this:
dd if=/dev/sda1 of=~/disk2.img
Also, you can compress the image file:
gzip disk2.img
By the way, you can copy a partition to another partition completely, just set "of" to the partition's device name. For example:
dd if=/dev/sda1 of=/dev/sdb5
This command will copy all the contents from /dev/sda1 to /dev/sdb5.
You must be sure that the capacity of /dev/sdb5 is larger than /dev/sda1.
RESTORING FROM AN IMAGE FILE
To restore a partition or a hard disk from an image file, just exchange the arguments "if" and "of".
For example, restore the whole hard disk from the image file "disk1.img":
dd if=disk1.img of=/dev/sda
Restore the first partition of /dev/sda from the image file "disk2.img":
dd if=disk2.img of=/dev/sda1
Enable cron for a locked account in Linux
In some environments, there are times when locked application/databases accounts need to run some cron jobs. In linux, by default, a locked account can not run the cron job.
We can enable this by editing a specific setting is disabled in /etc/pam.d/crond file.
Here is the details:
# cat /etc/pam.d/crond
#
# The PAM configuration file for the cron daemon
#
auth sufficient pam_rootok.so
auth required pam_stack.so service=system-auth
auth required pam_env.so
account required pam_stack.so service=system-auth
# account required pam_access.so
session required pam_limits.so
session required pam_loginuid.so
This example is working in Redhat Linux. In the /etc/pam.d/crond file, if we disable "account required pam_access.so" line the cron started working again for the locked account as well.
We can enable this by editing a specific setting is disabled in /etc/pam.d/crond file.
Here is the details:
# cat /etc/pam.d/crond
#
# The PAM configuration file for the cron daemon
#
auth sufficient pam_rootok.so
auth required pam_stack.so service=system-auth
auth required pam_env.so
account required pam_stack.so service=system-auth
# account required pam_access.so
session required pam_limits.so
session required pam_loginuid.so
This example is working in Redhat Linux. In the /etc/pam.d/crond file, if we disable "account required pam_access.so" line the cron started working again for the locked account as well.
Subscribe to:
Posts (Atom)