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
Subscribe to:
Posts (Atom)