Tuesday, June 1, 2010
How to copy controlfile from ASM diskgroup to a cooked filesystem --Moid
How to copy controlfile from ASM diskgroup to disk in 10g?
One of the 11g new ASM feature is the ability to use the "cp" command in ASM using "asmcmd" which 10g lacks. The following steps will help one understand how to copy controlfile from ASM to disk.
10.2.0.3 databases on the server called MoidDBServer are:
MoidDBServer > ps -ef |grep pmon |grep -v 'grep'
oracle 9305 1 0 Jun03 ? 00:00:05 asm_pmon_+ASM
oracle 6818 1 0 Jun03 ? 00:00:14 ora_pmon_QADB
oracle 21648 1 0 16:13 ? 00:00:00 ora_pmon_DEVDB
Database called DEVDB has only two copies of controlfile and both of them are on ASM. My task was to add a copy of controlfile to disk.
These are the steps I did to copy the controlfile from ASM to disk.
First, I found the the current controlfiles locations of DEVDB.
MoidDBServer > export ORACLE_SID=DEVDB
MoidDBServer > sqlplus / as sysdba
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DG1/DEVDB/controlfile/current.257.686842909, +DG2/DEVDB/controlfile/current.256.686842909
Now I updated the control_files parameter in spfile to reflect the new controlfile I will be creating next.
SQL> alter system set control_files = '+DG1/DEVDB/controlfile/current.257.686842909', '+DG2/DEVDB/controlfile/current.256.686842909', '/backup/DEVDB/CONTROLFILE/control03.ctl' scope=spfile;
Then I created a pfile in case we need it later:
SQL> create pfile='/tmp/initDEVDB.ora' from spfile;
Then database was brought down cleanly:
SQL> shutdown immediate;
Now, to copy controlfile from asm to disk, I logged in as sysdba to any other database (in this case, it will be database called QADB) which is up. Then I created the following temporary directories:
MoidDBServer > export ORACLE_SID=QADB
MoidDBServer > sqlplus / as sysdba
SQL> create or replace directory AA as '+DG1/DEVDB/controlfile';
SQL> create or replace directory BB as '/backup/DEVDB/CONTROLFILE';
Then I issued the following to copy the controlfile from ASM to DISK:
SQL> BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'AA',
source_file_name => 'current.257.686842909',
destination_directory_object => 'BB',
destination_file_name => 'control03.ctl');
END;
/
SQL> exit;
AT this point, I went back to database I stopped earlier and started it. Since the database is started with spfile, third control file came in full use.
MoidDBServer > export ORACLE_SID=DEVDB
MoidDBServer > sqlplus / as sysdba
SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1262812 bytes
Variable Size 260049700 bytes
Database Buffers 272629760 bytes
Redo Buffers 2928640 bytes
Database mounted.
Database opened.
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DG1/DEVDB/controlfile/curre
nt.257.686842909, +DG2/DEVDB
/controlfile/current.256.68684
2909, /backup/DEVDB/CONTROLF
ILE/control03.ctl
Lastly, I cleaned up the temporary directory AA and BB from the second database we used earlier.
Moid
One of the 11g new ASM feature is the ability to use the "cp" command in ASM using "asmcmd" which 10g lacks. The following steps will help one understand how to copy controlfile from ASM to disk.
10.2.0.3 databases on the server called MoidDBServer are:
MoidDBServer > ps -ef |grep pmon |grep -v 'grep'
oracle 9305 1 0 Jun03 ? 00:00:05 asm_pmon_+ASM
oracle 6818 1 0 Jun03 ? 00:00:14 ora_pmon_QADB
oracle 21648 1 0 16:13 ? 00:00:00 ora_pmon_DEVDB
Database called DEVDB has only two copies of controlfile and both of them are on ASM. My task was to add a copy of controlfile to disk.
These are the steps I did to copy the controlfile from ASM to disk.
First, I found the the current controlfiles locations of DEVDB.
MoidDBServer > export ORACLE_SID=DEVDB
MoidDBServer > sqlplus / as sysdba
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DG1/DEVDB/controlfile/current.257.686842909, +DG2/DEVDB/controlfile/current.256.686842909
Now I updated the control_files parameter in spfile to reflect the new controlfile I will be creating next.
SQL> alter system set control_files = '+DG1/DEVDB/controlfile/current.257.686842909', '+DG2/DEVDB/controlfile/current.256.686842909', '/backup/DEVDB/CONTROLFILE/control03.ctl' scope=spfile;
Then I created a pfile in case we need it later:
SQL> create pfile='/tmp/initDEVDB.ora' from spfile;
Then database was brought down cleanly:
SQL> shutdown immediate;
Now, to copy controlfile from asm to disk, I logged in as sysdba to any other database (in this case, it will be database called QADB) which is up. Then I created the following temporary directories:
MoidDBServer > export ORACLE_SID=QADB
MoidDBServer > sqlplus / as sysdba
SQL> create or replace directory AA as '+DG1/DEVDB/controlfile';
SQL> create or replace directory BB as '/backup/DEVDB/CONTROLFILE';
Then I issued the following to copy the controlfile from ASM to DISK:
SQL> BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'AA',
source_file_name => 'current.257.686842909',
destination_directory_object => 'BB',
destination_file_name => 'control03.ctl');
END;
/
SQL> exit;
AT this point, I went back to database I stopped earlier and started it. Since the database is started with spfile, third control file came in full use.
MoidDBServer > export ORACLE_SID=DEVDB
MoidDBServer > sqlplus / as sysdba
SQL> startup
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1262812 bytes
Variable Size 260049700 bytes
Database Buffers 272629760 bytes
Redo Buffers 2928640 bytes
Database mounted.
Database opened.
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DG1/DEVDB/controlfile/curre
nt.257.686842909, +DG2/DEVDB
/controlfile/current.256.68684
2909, /backup/DEVDB/CONTROLF
ILE/control03.ctl
Lastly, I cleaned up the temporary directory AA and BB from the second database we used earlier.
Moid
MIITDocID-993: Redo log is accidentally dropped. Now what? --Moid
Scenario is:
Farhan had lost one of the redolog when he accidentally use "rm -rf" command. Since there was no sufficient backups and archivelogs to restore, I used the following steps to open the db.
All the steps are pretty self explanatory, so I wont be putting too much time describing what I am doing here.
DR-server-01:/u02/oradata/farhan01(farhan01)$ export ORACLE_SID=farhan01
DR-server-01:/u02/oradata/farhan01(farhan01)$ sq
SQL> startup
ORACLE instance started.
Total System Global Area 159383552 bytes
Fixed Size 1266344 bytes
Variable Size 104861016 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u14/oradata/farhan01/system01.dbf';
when I try to recover database from the old backup, it was clear to me that redo log was missing. Alert log reported the following:DR-server-01:/u02/oradata/farhan01(farhan01)$ sq
SQL> startup
ORACLE instance started.
Total System Global Area 159383552 bytes
Fixed Size 1266344 bytes
Variable Size 104861016 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u14/oradata/farhan01/system01.dbf';
Errors in file /u01/app/oracle/admin/farhan01/udump/farhan01_ora_11555.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u15/oradata/farhan01/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
.
.
Media Recovery Start
Thu Nov 26 00:11:24 2009
Errors in file /u01/app/oracle/admin/farhan01/udump/farhan01_ora_2683.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u15/oradata/farhan01/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu Nov 26 00:11:27 2009
Media Recovery failed with error 313
ORA-283 signalled during: alter database recover if needed
start
...
SQL> alter database backup controlfile to trace as '/tmp/farhan01.controlfile.trace.trc' reuse;
SQL!
:/u02/oradata/farhan01(arhan01)$ vi /tmp/farhan01.controlfile.trace.trc
CREATE CONTROLFILE REUSE DATABASE "FARHAN01" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u15/oradata/farhan01/redo01.log' SIZE 50M,
GROUP 2 '/u16/oradata/farhan01/redo02.log' SIZE 50M,
GROUP 3 '/u17/oradata/farhan01/redo03.log' SIZE 50M,
GROUP 4 '/u15/oradata/farhan01/redo04.log' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'/u14/oradata/farhan01/system01.dbf',
'/u14/oradata/farhan01/undotbs01.dbf',
'/u14/oradata/farhan01/sysaux01.dbf',
'/u14/oradata/farhan01/users01.dbf',
'/u14/oradata/farhan01/dev01',
'/u14/oradata/farhan01/system02.dbf'
CHARACTER SET WE8ISO8859P1
;
.
.
.
:/u02/oradata/farhan01farhan01)$ exit
SQL> shutdown immediate;
DR-server-01:/u02/oradata/farhan01(farhan01)$ sq
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 26 00:18:24 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 159383552 bytes
Fixed Size 1266344 bytes
Variable Size 104861016 bytes
Database Buffers 50331648 bytes
Redo Buffers 2924544 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "FARHAN01" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 2 '/u16/oradata/farhan01/redo02.log' SIZE 50M,
9 GROUP 3 '/u17/oradata/farhan01/redo03.log' SIZE 50M,
10 GROUP 4 '/u15/oradata/farhan01/redo04.log' SIZE 100M
11 DATAFILE
12 '/u14/oradata/farhan01/system01.dbf',
13 '/u14/oradata/farhan01/undotbs01.dbf',
14 '/u14/oradata/farhan01/sysaux01.dbf',
15 '/u14/oradata/farhan01/users01.dbf',
16 '/u14/oradata/farhan01/dev01',
17 '/u14/oradata/farhan01/system02.dbf'
18 CHARACTER SET WE8ISO8859P1
19 ;
Control file created.
Elapsed: 00:00:00.80
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
Elapsed: 00:00:00.00
SQL> alter database open resetlogs;
Database altered.
Elapsed: 00:00:16.67
alter database mount
*
ERROR at line 1:
ORA-01100: database already mounted
Elapsed: 00:00:00.00
SQL> alter database open resetlogs;
Database altered.
Elapsed: 00:00:16.67
Great. DB is open for I/O. However, lets create/change backup settings and take a immediate level 0 backup as shown below:
DR-server-01:/home/oracle(farhan01)$ mkdir -p /u99/farhan01/backup
DR-server-01:/home/oracle(farhan01)$ mkdir -p /u99/farhan01/archive
DR-server-01:/home/oracle(farhan01)$ mkdir -p /u99/farhan01/archive2
DR-server-01:/u99(farhan01)$ sq
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 26 00:39:33 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter archive
SQL> alter system set log_archive_dest_1='LOCATION=/u99/farhan01/archive' scope=both;
System altered.
Elapsed: 00:00:00.00
SQL> alter system set log_archive_dest_2='LOCATION=/u99/farhan01/archive2' scope=both;
System altered.
Elapsed: 00:00:00.00
DR-server-01:/u01/app/oracle/admin/farhan01/bdump(farhan01)$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Nov 26 00:34:03 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: FARHAN01 (DBID=1364576715)
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
DR-server-01:/home/oracle(farhan01)$ mkdir -p /u99/farhan01/archive
DR-server-01:/home/oracle(farhan01)$ mkdir -p /u99/farhan01/archive2
DR-server-01:/u99(farhan01)$ sq
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 26 00:39:33 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter archive
SQL> alter system set log_archive_dest_1='LOCATION=/u99/farhan01/archive' scope=both;
System altered.
Elapsed: 00:00:00.00
SQL> alter system set log_archive_dest_2='LOCATION=/u99/farhan01/archive2' scope=both;
System altered.
Elapsed: 00:00:00.00
DR-server-01:/u01/app/oracle/admin/farhan01/bdump(farhan01)$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Nov 26 00:34:03 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: FARHAN01 (DBID=1364576715)
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u99/farhan01/backup/control_n_spfile_%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u99/farhan01/backup/control_n_spfile_%F';
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u99/farhan01/backup/full_%d_%T_%t_%U.bak' MAXPIECESIZE 1 G;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u99/farhan01/backup/full_%d_%T_%t_%U.bak' MAXPIECESIZE 1 G;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored
DR-server-01:/u99(farhan01)$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Nov 26 00:43:47 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: FARHAN01 (DBID=1364576715)
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u99/farhan01/backup/control_n_spfile_%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u99/farhan01/backup/control_n_spfile_%F';
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u99/farhan01/backup/full_%d_%T_%t_%U.bak' MAXPIECESIZE 1 G;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u99/farhan01/backup/full_%d_%T_%t_%U.bak' MAXPIECESIZE 1 G;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored
DR-server-01:/u99(farhan01)$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Nov 26 00:43:47 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: FARHAN01 (DBID=1364576715)
At this point, I realize that rman needs more DBA's attention to clear the old archivelogs from its repository. Using the "force" rman command helped in cleaning up missing archivelogs.
RMAN> backup database plus archivelog;
Starting backup at 26-NOV-09
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=256 devtype=DISK
archived log /u99/archive/farhan01/1_78_699291916.dbf not found or out of sync with catalog
trying alternate file for archivelog thread 1, sequence 78
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 11/26/2009 00:43:53
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u99/arch/farhan011_78_699291916.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
RMAN> crosscheck backup;
using channel ORA_DISK_1
RMAN> delete force archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=256 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
3 1 78 A 21-NOV-09 /u99/archive/farhan01/1_78_699291916.dbf
4 1 78 A 21-NOV-09 /u99/arch/farhan011_78_699291916.dbf
2 1 80 A 22-NOV-09 /u99/arch/farhan011_80_699291916.dbf
1 1 80 A 22-NOV-09 /u99/archive/farhan01/1_80_699291916.dbf
6 1 1 A 26-NOV-09 /u99/farhan01/archive2/1_1_703901992.dbf
5 1 1 A 26-NOV-09 /u99/farhan01/archive/1_1_703901992.dbf
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/u99/archive/farhan01/1_78_699291916.dbf recid=3 stamp=703901993
deleted archive log
archive log filename=/u99/arch/farhan011_78_699291916.dbf recid=4 stamp=703901993
deleted archive log
archive log filename=/u99/arch/farhan011_80_699291916.dbf recid=2 stamp=703901992
deleted archive log
archive log filename=/u99/archive/farhan01/1_80_699291916.dbf recid=1 stamp=703901992
deleted archive log
archive log filename=/u99/farhan01/archive2/1_1_703901992.dbf recid=6 stamp=703903431
deleted archive log
archive log filename=/u99/farhan01/archive/1_1_703901992.dbf recid=5 stamp=703903431
Deleted 6 objects
RMAN> backup database plus archivelog;
Starting backup at 26-NOV-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=8 stamp=703903601
channel ORA_DISK_1: starting piece 1 at 26-NOV-09
channel ORA_DISK_1: finished piece 1 at 26-NOV-09
piece handle=/u99/farhan01/backup/full_FARHAN01_20091126_703903601_01kv9drh_1_1.bak tag=TAG20091126T004641 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 26-NOV-09
Starting backup at 26-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u14/oradata/farhan01/system01.dbf
input datafile fno=00006 name=/u14/oradata/farhan01/system02.dbf
input datafile fno=00003 name=/u14/oradata/farhan01/sysaux01.dbf
input datafile fno=00005 name=/u14/oradata/farhan01/dev01
input datafile fno=00002 name=/u14/oradata/farhan01/undotbs01.dbf
input datafile fno=00004 name=/u14/oradata/farhan01/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-NOV-09
channel ORA_DISK_1: finished piece 1 at 26-NOV-09
piece handle=/u99/farhan01/backup/full_FARHAN01_20091126_703903603_02kv9drj_1_1.bak tag=TAG20091126T004643 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 26-NOV-09
Starting backup at 26-NOV-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=10 stamp=703903669
channel ORA_DISK_1: starting piece 1 at 26-NOV-09
channel ORA_DISK_1: finished piece 1 at 26-NOV-09
piece handle=/u99/farhan01/backup/full_FARHAN01_20091126_703903669_03kv9dtl_1_1.bak tag=TAG20091126T004749 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 26-NOV-09
Starting Control File and SPFILE Autobackup at 26-NOV-09
piece handle=/u99/farhan01/backup/control_n_spfile_c-1364576715-20091126-00 comment=NONE
Finished Control File and SPFILE Autobackup at 26-NOV-09
RMAN> exit
DR-server-01:/u99(farhan01)$ sq
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 26 00:51:53 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col member format a70
SQL> set linesize 200
SQL> select group#, member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------------------------------------
4 /u15/oradata/farhan01/redo04.log
3 /u17/oradata/farhan01/redo03.log
2 /u16/oradata/farhan01/redo02.log
Starting backup at 26-NOV-09
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=256 devtype=DISK
archived log /u99/archive/farhan01/1_78_699291916.dbf not found or out of sync with catalog
trying alternate file for archivelog thread 1, sequence 78
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 11/26/2009 00:43:53
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /u99/arch/farhan011_78_699291916.dbf
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
RMAN> crosscheck backup;
using channel ORA_DISK_1
RMAN> delete force archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=256 devtype=DISK
List of Archived Log Copies
Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
3 1 78 A 21-NOV-09 /u99/archive/farhan01/1_78_699291916.dbf
4 1 78 A 21-NOV-09 /u99/arch/farhan011_78_699291916.dbf
2 1 80 A 22-NOV-09 /u99/arch/farhan011_80_699291916.dbf
1 1 80 A 22-NOV-09 /u99/archive/farhan01/1_80_699291916.dbf
6 1 1 A 26-NOV-09 /u99/farhan01/archive2/1_1_703901992.dbf
5 1 1 A 26-NOV-09 /u99/farhan01/archive/1_1_703901992.dbf
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/u99/archive/farhan01/1_78_699291916.dbf recid=3 stamp=703901993
deleted archive log
archive log filename=/u99/arch/farhan011_78_699291916.dbf recid=4 stamp=703901993
deleted archive log
archive log filename=/u99/arch/farhan011_80_699291916.dbf recid=2 stamp=703901992
deleted archive log
archive log filename=/u99/archive/farhan01/1_80_699291916.dbf recid=1 stamp=703901992
deleted archive log
archive log filename=/u99/farhan01/archive2/1_1_703901992.dbf recid=6 stamp=703903431
deleted archive log
archive log filename=/u99/farhan01/archive/1_1_703901992.dbf recid=5 stamp=703903431
Deleted 6 objects
RMAN> backup database plus archivelog;
Starting backup at 26-NOV-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=8 stamp=703903601
channel ORA_DISK_1: starting piece 1 at 26-NOV-09
channel ORA_DISK_1: finished piece 1 at 26-NOV-09
piece handle=/u99/farhan01/backup/full_FARHAN01_20091126_703903601_01kv9drh_1_1.bak tag=TAG20091126T004641 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 26-NOV-09
Starting backup at 26-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u14/oradata/farhan01/system01.dbf
input datafile fno=00006 name=/u14/oradata/farhan01/system02.dbf
input datafile fno=00003 name=/u14/oradata/farhan01/sysaux01.dbf
input datafile fno=00005 name=/u14/oradata/farhan01/dev01
input datafile fno=00002 name=/u14/oradata/farhan01/undotbs01.dbf
input datafile fno=00004 name=/u14/oradata/farhan01/users01.dbf
channel ORA_DISK_1: starting piece 1 at 26-NOV-09
channel ORA_DISK_1: finished piece 1 at 26-NOV-09
piece handle=/u99/farhan01/backup/full_FARHAN01_20091126_703903603_02kv9drj_1_1.bak tag=TAG20091126T004643 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 26-NOV-09
Starting backup at 26-NOV-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=10 stamp=703903669
channel ORA_DISK_1: starting piece 1 at 26-NOV-09
channel ORA_DISK_1: finished piece 1 at 26-NOV-09
piece handle=/u99/farhan01/backup/full_FARHAN01_20091126_703903669_03kv9dtl_1_1.bak tag=TAG20091126T004749 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 26-NOV-09
Starting Control File and SPFILE Autobackup at 26-NOV-09
piece handle=/u99/farhan01/backup/control_n_spfile_c-1364576715-20091126-00 comment=NONE
Finished Control File and SPFILE Autobackup at 26-NOV-09
RMAN> exit
DR-server-01:/u99(farhan01)$ sq
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Nov 26 00:51:53 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col member format a70
SQL> set linesize 200
SQL> select group#, member from v$logfile;
GROUP# MEMBER
---------- ----------------------------------------------------------------------
4 /u15/oradata/farhan01/redo04.log
3 /u17/oradata/farhan01/redo03.log
2 /u16/oradata/farhan01/redo02.log
See how group#1, is missing. This is because we removed it from the database during our rebuilding of controlfile. I will leave this to Farhanbhia to recreate group#1 and also recommend HIM AND EVERYONE ELSE to have multiplex redolog members in each group to avoid this kind of recovery in the future. Hope this helps.
--Moid
Monday, May 31, 2010
Wednesday, May 26, 2010
Automating Database Startup and Shutdown on Linux
Automating Database Startup and Shutdown on Linux
Edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.
Next, create a file called "/etc/init.d/oracle" as the root user, containing the following.
########################################################################### #!/bin/bash # # # # Run-level Startup script for the Oracle Instance and Listener # # # # chkconfig: 345 98 34 # # description: Startup/Shutdown Oracle listener, dbconsole and instances # # # ###########################################################################
ORA_HM="/d01/app/oracle/product/10.2.0.2.0" ORA_OWNR="oracle"
# if the executables do not exist -- display error
if [ ! -f $ORA_HM/bin/dbstart -o ! -d $ORA_HM ] then echo "Oracle startup: cannot start" exit 1 fi
# depending on parameter -- startup, shutdown, restart # of the instance and listener or usage display
case "$1" in start) # Oracle listener and instance startup echo -n "Starting Oracle: " su - $ORA_OWNR -c "$ASM_HM/bin/lsnrctl start" su - $ORA_OWNR -c $ORA_HM/bin/dbstart touch /var/lock/subsys/oracle su - $ORA_OWNR -c "export ORACLE_SID=MMDEVDB;$ORA_HM/bin/emctl start dbconsole" su - $ORA_OWNR -c "export ORACLE_SID=MMDEVPSDB;$ORA_HM/bin/emctl start dbconsole" su - $ORA_OWNR -c "export ORACLE_SID=MMQADB;$ORA_HM/bin/emctl start dbconsole" su - $ORA_OWNR -c "export ORACLE_SID=MMQAPSDB;$ORA_HM/bin/emctl start dbconsole" echo "OK" ;; stop) # Oracle listener and instance shutdown echo -n "Shutdown Oracle: " su - $ORA_OWNR -c "export ORACLE_SID=MMDEVDB;$ORA_HM/bin/emctl stop dbconsole" su - $ORA_OWNR -c "export ORACLE_SID=MMDEVPSDB;$ORA_HM/bin/emctl stop dbconsole" su - $ORA_OWNR -c "export ORACLE_SID=MMQADB;$ORA_HM/bin/emctl stop dbconsole" su - $ORA_OWNR -c "export ORACLE_SID=MMQAPSDB;$ORA_HM/bin/emctl stop dbconsole" su - $ORA_OWNR -c "$ASM_HM/bin/lsnrctl stop" su - $ORA_OWNR -c $ORA_HM/bin/dbshut rm -f /var/lock/subsys/oracle echo "OK" ;; reload|restart) $0 stop $0 start ;; *) echo "Usage: $0 start|stop|restart|reload" exit 1 esac exit 0 ######################################################################################### |
chmod 750 /etc/init.d/oracle
chkconfig --level 345 oracle on
How to turn off/on the script.
[root@bl-mm-db-dev ~]# /sbin/chkconfig oracle off
[root@bl-mm-db-dev ~]# /sbin/chkconfig --levels 345 oracle on
Monday, May 24, 2010
Wednesday, May 19, 2010
How to kill Oracle connections from UNIX and SQL command prompt?
How to kill all sessions from a specific user?
1) export ORACLE_SID=Prime
2) sqlplus / as sysdba
3) set serveroutput on;
4)
DECLARE
CURSOR c1 IS
select sid, serial# from v$session where username='SCOTT';
kill_it c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO kill_it;
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''||
kill_it.sid||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
/
5) exit;
How to kill all sessions from any server except any connections coming from the database server?
1) export ORACLE_SID=Prime
2) sqlplus / as sysdba
3) set serveroutput on;
4)
DECLARE
CURSOR c1 IS
select sid, serial# from v$session where machine='production';
kill_it c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO kill_it;
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''||
kill_it.sid||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
/
5) exit;
How to kill all sessions except any background processes and my current session;
1) export ORACLE_SID=Prime
2) sqlplus / as sysdba
3) set serveroutput on;
4)
DECLARE
CURSOR c1 IS
select
sid, serial# from v$session
where
username is not null
AND
username not in
(select
username from v$session
where
sid =
(select distinct sid from v$mystat));
kill_it c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO kill_it;
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''||
kill_it.sid||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
/
5) exit;
Generate a SQL output that you can copy paste in the SQL Editor to kill all the Oracle Sessions (except the background processes)
set pagesize 100
select
'Alter System Kill Session '''|| Sid || ',' || Serial# || ''' IMMEDIATE;' as Kill_the_following_connections
from
(select
Sid,
Serial#
from
v$session
where
username is not null
and
username not in ('SYS','SYSTEM','SYSMAN','DBSNMP'));
To kill the sessions from LINUX (except the background processes and local database connections)
ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq |awk '{ print $2 }' |xargs kill -9
The above has worked for me. Although sometimes I see few stale connections that I can kill later with any of the above methods but mostly, the above works.
Below is my testing results. When time permits, I will add more on my steps but for now, lets just say it is pretty much self explanatory.
Let's see how many users are connected to the database.
set linesize 200
set username format a20
col machine format a20
set pagesize 60
select
a.username,
a.machine,
a.sid,
a.serial#,
b.spid,
a.process
from
v$session a, v$process b
where
a.paddr=b.addr
and a.username is not null
-- and a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and a.machine like 'DR%'
-- and a.username='SCOTT'
-- and a.machine <> 'production'
order by
spid;
USERNAME MACHINE SID SERIAL# SPID PROCESS
------------------------------ -------------------- ---------- ---------- ------------ ------------
DBSNMP production 175 42 25213 13467
SYSMAN production 156 1796 25237 1234
SYSMAN production 164 41 25247 1234
DBSNMP production 165 135 25281 13467
SYS production 141 25733 25375 25374
SCOTT production 182 45691 25437 25436
SCOTT AAA\1-11816-LAPXP 142 13808 25490 5352:5396
SYSTEM AAA\1-11816-LAPXP 163 245 25512 1724:5616
SYSMAN production 162 37192 26511 1234
SYSMAN production 154 64670 26516 1234
SYSMAN production 181 34355 26520 1234
SYSTEM production 170 33536 26551 1234
SYS production 158 24367 26682 26681
13 rows selected.
SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq
oracle 25213 1 0 01:03 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 25237 1 0 01:03 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 25247 1 0 01:03 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 25281 1 0 01:04 ? 00:00:01 oraclePrime (LOCAL=NO)
oracle 25490 1 0 01:06 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 25512 1 0 01:06 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 26511 1 0 01:10 ? 00:00:01 oraclePrime (LOCAL=NO)
oracle 26516 1 0 01:11 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 26520 1 0 01:11 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 26551 1 0 01:11 ? 00:00:00 oraclePrime (LOCAL=NO)
As you can see some of the SPID are associated with DBSNMP and SYSMAN schemas. At this point, you can skip all those users by manually killing each session minus any DBSNMP/SYSMAN connections
OR
use the following method to kill all the connections which are not local to the DB Server. This includes all DBSNMP and SYSMAN connections as well.
SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq |awk '{ print $2 }'
25213
25237
25247
25281
25490
25512
26511
26516
26551
SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq |awk '{ print $2 }' |xargs kill -9
At this point, I check the outside (local=no) connections, and I see the following.
SQL> SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq
oracle 27400 1 0 01:21 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 27402 1 0 01:21 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 27438 1 0 01:22 ? 00:00:00 oraclePrime (LOCAL=NO)
If my assumption is right, DBSNMP and SYSMAN connections connected back after they realized that connection was lost. Lets check it out
select
a.username,
a.machine,
a.sid,
a.serial#,
b.spid,
a.process
from
v$session a, v$process b
where
a.paddr=b.addr
-- and
-- a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and
-- a.machine like 'DR%'
-- and
-- a.username='SCOTT'
and
a.username is not null
--and
-- a.machine <> 'production'
order by
spid;
USERNAME MACHINE SID SERIAL# SPID PROCESS
------------------------------ -------------------- ---------- ---------- ------------ ------------
SYS production 158 24367 26682 26681
DBSNMP production 182 45779 29655 13467
SYSMAN production 165 145 29657 1234
SYSMAN production 145 17657 29668 1234
As you can see, every outside connection is dead except the connections which are internal and connections from DBSNMP and SYSMAN. Althought more testing is needed, my guess is dbconsole will invoke its connections after they shutdown ungracefully. If you are still in doubt, then restarting dbconsole won't hurt.
The above method of killing Oracle connections from outside of SQL Plus has always worked for me. If you have any other idea or have a better way, please share with us.
Thanks,
--Moid Muhammad
Other Notes:
Note-1
ALTER SYSTEM DISABLE RESTRICTED SESSION; To kill a session, the syntax is
Alter system kill session "SID,SERIAL#" immediate;
ALTER SYSTEM QUIESCE RESTRICTED;
How to find the current session's ID (the session you are connected)
select sid, serial# from v$session where audsid = sys_context('userenv','sessionid');
Or, if you're only interested in the sid, not in serial#:
select distinct sid from v$mystat;
Note, the selected sid has nothing to do with the System Identifier.
If dbms_support is installed, the current sid can also be found with its mysid function:
The package is not installed by default. ..../rdbms/admin/dbmssupp.sql will install it.
select dbms_support.mysid from dual;
Note-4
Try trigger on logon
Insted of trying disconnect users you should not allow them to connect.
There is and example of such trigger.
CREATE OR REPLACE TRIGGER rds_logon_trigger
AFTER LOGON ON DATABASE
BEGIN
IF SYS_CONTEXT('USERENV','IP_ADDRESS') not in ('192.168.2.121','192.168.2.123','192.168.2.233') THEN
RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');
END IF;
IF (to_number(to_char(sysdate,'HH24'))< 6) and (to_number(to_char(sysdate,'HH24')) >18) THEN
RAISE_APPLICATION_ERROR(-20005,'Logon only allowed during business hours');
END IF;
END;
/
Note-5
The below query can be used to identify those sessions which are inactive from last 3 minutes.
select
a.username,
a.machine,
ROUND(a.LAST_CALL_ET/60) wait_mins,
a.sid,
a.serial#,
b.spid,
a.process
from
v$session a, v$process b
where
a.paddr=b.addr
and a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and a.machine like 'DR%'
-- and a.username='SCOTT'
-- and a.machine <> 'production'
and a.username is not null
and a.status ='INACTIVE'
and a.TYPE='USER'
and a.LAST_CALL_ET > 180
order by
spid;
To kill the above sessions which are INACTIVE from last 3 minutes, use the following:
DECLARE
CURSOR c1 IS
select
a.username,
a.machine,
ROUND(a.LAST_CALL_ET/60) wait_mins,
a.sid,
a.serial#,
b.spid,
a.process
from
v$session a, v$process b
where
a.paddr=b.addr
and a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and a.machine like 'DR%'
-- and a.username='SCOTT'
-- and a.machine <> 'production'
and a.username is not null
and a.status ='INACTIVE'
and a.TYPE='USER'
and a.LAST_CALL_ET > 180
order by
spid;
kill_it c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO kill_it;
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''||
kill_it.sid||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
/
Note-6
from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1419803982336
then just make the query be:
select ..
from v$session
where sid <> ( select sid from v$mystat where rownum=1)
or -- just
SQL> startup force;
:) would have the same effect.
Note-7
Showsql.sql from AskTom site. Just copy and paste the below in /tmp as showsql.sql script. It will come in handy.
Also, notice the last column is showing time in seconds.
column username format a15 word_wrapped
column module format a35 word_wrapped
column action format a15 word_wrapped
column client_info format a35 word_wrapped
column status format a10
column sid_serial format a15
set feedback off
set serveroutput on
set linesize 200
set feedback on
select username, ''''||sid||','||serial#||'''' sid_serial, status , module, action, client_info,
last_call_et
from v$session
where username is not null
/
set feedback off
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
pid number;
procedure p ( p_str in varchar2 )
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
end;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' command = ' || command ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET, sid, paddr
from v$session
where status = 'ACTIVE'
and sid <> ( select sid from v$mystat where rownum = 1 )
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
select max(spid) into pid from v$process where addr = x.paddr;
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username || ' dedicated server=' || pid );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
for y in ( select sql_text
from v$sqltext_with_newlines
where address = x.sql_address
order by piece )
loop
p( y.sql_text );
end loop;
end loop;
end;
/
set feedback on
Note-8
Note-9
Note-10
1) export ORACLE_SID=Prime
2) sqlplus / as sysdba
3) set serveroutput on;
4)
DECLARE
CURSOR c1 IS
select sid, serial# from v$session where username='SCOTT';
kill_it c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO kill_it;
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''||
kill_it.sid||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
/
5) exit;
How to kill all sessions from any server except any connections coming from the database server?
1) export ORACLE_SID=Prime
2) sqlplus / as sysdba
3) set serveroutput on;
4)
DECLARE
CURSOR c1 IS
select sid, serial# from v$session where machine='production';
kill_it c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO kill_it;
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''||
kill_it.sid||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
/
5) exit;
How to kill all sessions except any background processes and my current session;
1) export ORACLE_SID=Prime
2) sqlplus / as sysdba
3) set serveroutput on;
4)
DECLARE
CURSOR c1 IS
select
sid, serial# from v$session
where
username is not null
AND
username not in
(select
username from v$session
where
sid =
(select distinct sid from v$mystat));
kill_it c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO kill_it;
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''||
kill_it.sid||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
/
5) exit;
Generate a SQL output that you can copy paste in the SQL Editor to kill all the Oracle Sessions (except the background processes)
set pagesize 100
select
'Alter System Kill Session '''|| Sid || ',' || Serial# || ''' IMMEDIATE;' as Kill_the_following_connections
from
(select
Sid,
Serial#
from
v$session
where
username is not null
and
username not in ('SYS','SYSTEM','SYSMAN','DBSNMP'));
To kill the sessions from LINUX (except the background processes and local database connections)
ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq |awk '{ print $2 }' |xargs kill -9
The above has worked for me. Although sometimes I see few stale connections that I can kill later with any of the above methods but mostly, the above works.
Below is my testing results. When time permits, I will add more on my steps but for now, lets just say it is pretty much self explanatory.
Let's see how many users are connected to the database.
set linesize 200
set username format a20
col machine format a20
set pagesize 60
select
a.username,
a.machine,
a.sid,
a.serial#,
b.spid,
a.process
from
v$session a, v$process b
where
a.paddr=b.addr
and a.username is not null
-- and a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and a.machine like 'DR%'
-- and a.username='SCOTT'
-- and a.machine <> 'production'
order by
spid;
USERNAME MACHINE SID SERIAL# SPID PROCESS
------------------------------ -------------------- ---------- ---------- ------------ ------------
DBSNMP production 175 42 25213 13467
SYSMAN production 156 1796 25237 1234
SYSMAN production 164 41 25247 1234
DBSNMP production 165 135 25281 13467
SYS production 141 25733 25375 25374
SCOTT production 182 45691 25437 25436
SCOTT AAA\1-11816-LAPXP 142 13808 25490 5352:5396
SYSTEM AAA\1-11816-LAPXP 163 245 25512 1724:5616
SYSMAN production 162 37192 26511 1234
SYSMAN production 154 64670 26516 1234
SYSMAN production 181 34355 26520 1234
SYSTEM production 170 33536 26551 1234
SYS production 158 24367 26682 26681
13 rows selected.
SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq
oracle 25213 1 0 01:03 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 25237 1 0 01:03 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 25247 1 0 01:03 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 25281 1 0 01:04 ? 00:00:01 oraclePrime (LOCAL=NO)
oracle 25490 1 0 01:06 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 25512 1 0 01:06 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 26511 1 0 01:10 ? 00:00:01 oraclePrime (LOCAL=NO)
oracle 26516 1 0 01:11 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 26520 1 0 01:11 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 26551 1 0 01:11 ? 00:00:00 oraclePrime (LOCAL=NO)
As you can see some of the SPID are associated with DBSNMP and SYSMAN schemas. At this point, you can skip all those users by manually killing each session minus any DBSNMP/SYSMAN connections
OR
use the following method to kill all the connections which are not local to the DB Server. This includes all DBSNMP and SYSMAN connections as well.
SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq |awk '{ print $2 }'
25213
25237
25247
25281
25490
25512
26511
26516
26551
SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq |awk '{ print $2 }' |xargs kill -9
At this point, I check the outside (local=no) connections, and I see the following.
SQL> SQL> !ps -ef |grep oracle$ORACLE_SID |grep -v grep |grep -v PROTOCOL=beq
oracle 27400 1 0 01:21 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 27402 1 0 01:21 ? 00:00:00 oraclePrime (LOCAL=NO)
oracle 27438 1 0 01:22 ? 00:00:00 oraclePrime (LOCAL=NO)
If my assumption is right, DBSNMP and SYSMAN connections connected back after they realized that connection was lost. Lets check it out
select
a.username,
a.machine,
a.sid,
a.serial#,
b.spid,
a.process
from
v$session a, v$process b
where
a.paddr=b.addr
-- and
-- a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and
-- a.machine like 'DR%'
-- and
-- a.username='SCOTT'
and
a.username is not null
--and
-- a.machine <> 'production'
order by
spid;
USERNAME MACHINE SID SERIAL# SPID PROCESS
------------------------------ -------------------- ---------- ---------- ------------ ------------
SYS production 158 24367 26682 26681
DBSNMP production 182 45779 29655 13467
SYSMAN production 165 145 29657 1234
SYSMAN production 145 17657 29668 1234
As you can see, every outside connection is dead except the connections which are internal and connections from DBSNMP and SYSMAN. Althought more testing is needed, my guess is dbconsole will invoke its connections after they shutdown ungracefully. If you are still in doubt, then restarting dbconsole won't hurt.
The above method of killing Oracle connections from outside of SQL Plus has always worked for me. If you have any other idea or have a better way, please share with us.
Thanks,
--Moid Muhammad
Other Notes:
Note-1
Before killing sessions, if possible stop new sessions from connecting.
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION; To kill a session, the syntax is
Alter system kill session "SID,SERIAL#" immediate;
Note-2
If you want to stop new users from connecting, but allow current sessions to continue until they are inactive, you can put the database in QUIESCE mode:
From the Oracle Database Administrator's Guide:
Note-3Non-DBA active sessions will continue until they become inactive. An active session is one that is currently inside of a transaction, a query, a fetch, or a PL/SQL statement; or a session that is currently holding any shared resources (for example, enqueues). No inactive sessions are allowed to become active...Once all non-DBA sessions become inactive, the ALTER SYSTEM QUIESCE RESTRICTED statement completes, and the database is in a quiesced state
How to find the current session's ID (the session you are connected)
select sid, serial# from v$session where audsid = sys_context('userenv','sessionid');
Or, if you're only interested in the sid, not in serial#:
select distinct sid from v$mystat;
Note, the selected sid has nothing to do with the System Identifier.
If dbms_support is installed, the current sid can also be found with its mysid function:
The package is not installed by default. ..../rdbms/admin/dbmssupp.sql will install it.
select dbms_support.mysid from dual;
Note-4
Try trigger on logon
Insted of trying disconnect users you should not allow them to connect.
There is and example of such trigger.
CREATE OR REPLACE TRIGGER rds_logon_trigger
AFTER LOGON ON DATABASE
BEGIN
IF SYS_CONTEXT('USERENV','IP_ADDRESS') not in ('192.168.2.121','192.168.2.123','192.168.2.233') THEN
RAISE_APPLICATION_ERROR(-20003,'You are not allowed to connect to the database');
END IF;
IF (to_number(to_char(sysdate,'HH24'))< 6) and (to_number(to_char(sysdate,'HH24')) >18) THEN
RAISE_APPLICATION_ERROR(-20005,'Logon only allowed during business hours');
END IF;
END;
/
Note-5
The below query can be used to identify those sessions which are inactive from last 3 minutes.
select
a.username,
a.machine,
ROUND(a.LAST_CALL_ET/60) wait_mins,
a.sid,
a.serial#,
b.spid,
a.process
from
v$session a, v$process b
where
a.paddr=b.addr
and a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and a.machine like 'DR%'
-- and a.username='SCOTT'
-- and a.machine <> 'production'
and a.username is not null
and a.status ='INACTIVE'
and a.TYPE='USER'
and a.LAST_CALL_ET > 180
order by
spid;
To kill the above sessions which are INACTIVE from last 3 minutes, use the following:
DECLARE
CURSOR c1 IS
select
a.username,
a.machine,
ROUND(a.LAST_CALL_ET/60) wait_mins,
a.sid,
a.serial#,
b.spid,
a.process
from
v$session a, v$process b
where
a.paddr=b.addr
and a.username not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
-- and a.machine like 'DR%'
-- and a.username='SCOTT'
-- and a.machine <> 'production'
and a.username is not null
and a.status ='INACTIVE'
and a.TYPE='USER'
and a.LAST_CALL_ET > 180
order by
spid;
kill_it c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO kill_it;
EXIT WHEN c1%NOTFOUND;
BEGIN
EXECUTE IMMEDIATE 'alter system kill session '''||
kill_it.sid||', '||kill_it.serial#||'''';
END;
END LOOP;
CLOSE c1;
END;
/
Note-6
from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1419803982336
then just make the query be:
select ..
from v$session
where sid <> ( select sid from v$mystat where rownum=1)
or -- just
SQL> startup force;
:) would have the same effect.
Note-7
Showsql.sql from AskTom site. Just copy and paste the below in /tmp as showsql.sql script. It will come in handy.
Also, notice the last column is showing time in seconds.
column username format a15 word_wrapped
column module format a35 word_wrapped
column action format a15 word_wrapped
column client_info format a35 word_wrapped
column status format a10
column sid_serial format a15
set feedback off
set serveroutput on
set linesize 200
set feedback on
select username, ''''||sid||','||serial#||'''' sid_serial, status , module, action, client_info,
last_call_et
from v$session
where username is not null
/
set feedback off
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
pid number;
procedure p ( p_str in varchar2 )
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
end;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' command = ' || command ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET, sid, paddr
from v$session
where status = 'ACTIVE'
and sid <> ( select sid from v$mystat where rownum = 1 )
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
select max(spid) into pid from v$process where addr = x.paddr;
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username || ' dedicated server=' || pid );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
for y in ( select sql_text
from v$sqltext_with_newlines
where address = x.sql_address
order by piece )
loop
p( y.sql_text );
end loop;
end loop;
end;
/
set feedback on
Note-8
Note-9
Note-10
Wednesday, November 25, 2009
Subscribe to:
Posts (Atom)