Based on metalink note(Measuring Network Capacity using oratcptest (Doc ID 2064368.1)) I've checked the network performance for one Oracle DataGuard enviroment. Here is the approach:
[oracle@marmi101 ~]$ ping marmidr
PING marmidr.cmd.far.xtr.com (10.28.32.151) 56(84) bytes of data.
64 bytes from marmidr.cmd.far.xtr.com (10.28.32.151): icmp_seq=1 ttl=64 time=0.458 ms
64 bytes from marmidr.cmd.far.xtr.com (10.28.32.151): icmp_seq=2 ttl=64 time=0.317 ms
--- marmidr.cmd.far.xtr.com ping statistics ---
---lvm
/srv/ora/app/oracle/12.1.0.2/jdk/bin/java -jar oratcptest.jar -help
java -jar oratcptest.jar -help
####start server####
oracle@marmidr
/tmp/jre1.8.0_161/bin/java -version
java version "1.8.0_161"
Java(TM) SE Runtime Environment (build 1.8.0_161-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.161-b12, mixed mode)
/tmp/jre1.8.0_161/bin/java -jar /tmp/oratcptest.jar -help
/tmp/jre1.8.0_161/bin/java -jar /tmp/oratcptest.jar -server -port=5555
####start client####
oracle@marmi101
/tmp/jre1.8.0_161/bin/java -jar /tmp/oratcptest.jar -help
/tmp/jre1.8.0_161/bin/java -jar /tmp/oratcptest.jar marmidr -port=5555 -duration=10s -interval=2s
[Requesting a test]
Message payload = 1 Mbyte
Payload content type = RANDOM
Delay between messages = NO
Number of connections = 1
Socket send buffer = (system default)
Transport mode = SYNC
Disk write = NO
Statistics interval = 2 seconds
Test duration = 10 seconds
Test frequency = NO
Network Timeout = NO
(1 Mbyte = 1024x1024 bytes)
(14:12:10) The server is ready.
Throughput Latency
(14:12:12) 631,033 Mbytes/s 1,585 ms
(14:12:14) 648,010 Mbytes/s 1,543 ms
(14:12:16) 442,924 Mbytes/s 2,258 ms
(14:12:18) 620,133 Mbytes/s 1,613 ms
(14:12:20) 687,618 Mbytes/s 1,454 ms
(14:12:20) Test finished.
Socket send buffer = 562320 bytes
Avg. throughput = 605,763 Mbytes/s
Avg. latency = 1,651 ms
/tmp/jre1.8.0_161/bin/java -jar /tmp/oratcptest.jar marmidr -port=5555 -mode=async -duration=10s -interval=2s
[Requesting a test]
Message payload = 1 Mbyte
Payload content type = RANDOM
Delay between messages = NO
Number of connections = 1
Socket send buffer = (system default)
Transport mode = ASYNC
Disk write = NO
Statistics interval = 2 seconds
Test duration = 10 seconds
Test frequency = NO
Network Timeout = NO
(1 Mbyte = 1024x1024 bytes)
(14:14:57) The server is ready.
Throughput
(14:14:59) 758,240 Mbytes/s
(14:15:01) 541,444 Mbytes/s
(14:15:03) 548,761 Mbytes/s
(14:15:05) 661,394 Mbytes/s
(14:15:07) 674,031 Mbytes/s
(14:15:07) Test finished.
Socket send buffer = 2 Mbytes
Avg. throughput = 636,474 Mbytes/s
/tmp/jre1.8.0_161/bin/java -jar /tmp/oratcptest.jar marmidr -port=5555 -mode=sync -duration=10s -interval=2s
[Requesting a test]
Message payload = 1 Mbyte
Payload content type = RANDOM
Delay between messages = NO
Number of connections = 1
Socket send buffer = (system default)
Transport mode = SYNC
Disk write = NO
Statistics interval = 2 seconds
Test duration = 10 seconds
Test frequency = NO
Network Timeout = NO
(1 Mbyte = 1024x1024 bytes)
(14:18:02) The server is ready.
Throughput Latency
(14:18:04) 653,487 Mbytes/s 1,530 ms
(14:18:06) 757,528 Mbytes/s 1,320 ms
(14:18:08) 770,965 Mbytes/s 1,297 ms
(14:18:10) 778,632 Mbytes/s 1,284 ms
(14:18:12) 775,789 Mbytes/s 1,289 ms
(14:18:12) Test finished.
Socket send buffer = 807840 bytes
Avg. throughput = 746,854 Mbytes/s
Avg. latency = 1,339 ms
/tmp/jre1.8.0_161/bin/java -jar /tmp/oratcptest.jar marmidr -port=5555 -rtt -duration=10s -interval=2s
[Requesting a test]
Message payload = 0 bytes
Payload content type = RANDOM
Delay between messages = NO
Number of connections = 1
Socket send buffer = (system default)
Transport mode = SYNC
Disk write = NO
Statistics interval = 2 seconds
Test duration = 10 seconds
Test frequency = NO
Network Timeout = NO
(1 Mbyte = 1024x1024 bytes)
(14:25:15) The server is ready.
Latency
(14:25:17) 0,097 ms
(14:25:19) 0,091 ms
(14:25:21) 0,090 ms
(14:25:23) 0,091 ms
(14:25:25) 0,092 ms
(14:25:25) Test finished.
Socket send buffer = 9900 bytes
Avg. latency = 0,092 ms
Tuesday, 20 February 2018
RMAN Duplicate with NOOPEN
Duplicate with noopen clause is very useful in scenario when you perform migration(to other server) and upgrade(to new release) on new server where old Oracle Software is not installed.
In my testcase I used NFS server to save the backup from source database. It was presented to new server. Just for your info: I used it to upgrade from Oracle 11.2.0.2 version on Red Hat 6.9 to Oracle 12.1.0.2 on Red Hat 7.4.
Please check MOS for more info: Upgrade to 12c(12.1 /12.2) through RMAN Duplicate using BACKUP LOCATION with NOOPEN clause (Doc ID 2022820.1)
So let's start with preparation. On source database perform full backup:
mkdir -p /u01/orabackup/backup/RMAN/testdb/noopen
backup as compressed backupset database format '/u01/orabackup/backup/RMAN/testdb/noopen/db_%U' plus archivelog format '/u01/orabackup/backup/RMAN/testdb/noopen/arch_%U';
backup current controlfile format '/u01/orabackup/backup/RMAN/testdb/noopen/cf_%U';
Copy password file and initfile to new server. Change parameter cluster_database=false by duplicating RAC DB.
On target server:
export ORACLE_HOME=/u01/app/oracle/12.1.0.2
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE=/u01/app/oracle
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 9 15:26:59 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL > startup nomount;
Connect to this instance as auxiliary and perform duplicate with noopen:
$ rman auxiliary /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jan 9 15:18:10 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: TESTDB (not mounted)
RMAN> DUPLICATE DATABASE to testdb noopen backup location '/u01/orabackup/backup/RMAN/testdb/noopen/';
Starting Duplicate Db at 09-JAN-18
contents of Memory Script:
{
sql clone "alter system set db_name =
''TESTDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/u01/orabackup/backup/RMAN/testdb/noopen/cf_k2so88l5_1_1';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''TESTDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 2097152000 bytes
Fixed Size 2926320 bytes
Variable Size 1291847952 bytes
Database Buffers 788529152 bytes
Redo Buffers 13848576 bytes
Starting restore at 09-JAN-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=84 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/testdb/controlfile/control01.ctl
output file name=+FRA/testdb/controlfile/control02.ctl
Finished restore at 09-JAN-18
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=84 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=7 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=85 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=167 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=244 device type=DISK
allocated channel: ORA_AUX_DISK_6
channel ORA_AUX_DISK_6: SID=324 device type=DISK
contents of Memory Script:
{
set until scn 14331183090492;
sql clone 'alter database flashback off';
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
...
...
set newname for clone datafile 61 to new;
set newname for clone datafile 62 to new;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database flashback off
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
...
...
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 09-JAN-18
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00013 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00018 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00028 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00031 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00032 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00035 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00036 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00040 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00042 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00052 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00056 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /u01/orabackup/backup/RMAN/testdb/noopen/db_juso88fq_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to +DATA
...
...
channel ORA_AUX_DISK_1: piece handle=/u01/orabackup/backup/RMAN/testdb/noopen/db_juso88fq_1_1 tag=TAG20180109T123921
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
...
...
channel ORA_AUX_DISK_6: piece handle=/u01/orabackup/backup/RMAN/testdb/noopen/db_jsso88fq_1_1 tag=TAG20180109T123921
channel ORA_AUX_DISK_6: restored backup piece 1
channel ORA_AUX_DISK_6: restore complete, elapsed time: 00:00:55
Finished restore at 09-JAN-18
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=63 STAMP=964970532 file name=+DATA/TESTDBN/DATAFILE/system.695.964963147
datafile 2 switched to datafile copy
input datafile copy RECID=64 STAMP=964970532 file name=+DATA/TESTDBN/DATAFILE/sysaux.697.964963147
datafile 3 switched to datafile copy
input datafile copy RECID=65 STAMP=964970532 file name=+DATA/TESTDBN/DATAFILE/undotbs1.704.964963147
datafile 4 switched to datafile copy
input datafile copy RECID=66 STAMP=964970532 file name=+DATA/TESTDBN/DATAFILE/users.719.964963147
...
...
datafile 62 switched to datafile copy
input datafile copy RECID=124 STAMP=964970535 file name=+DATA/TESTDBN/DATAFILE/xdb.718.964963147
contents of Memory Script:
{
set until scn 14331183090492;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 09-JAN-18
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=36
channel ORA_AUX_DISK_1: reading from backup piece /u01/orabackup/backup/RMAN/testdb/noopen/arch_jvso88kj_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=32
channel ORA_AUX_DISK_2: reading from backup piece /u01/orabackup/backup/RMAN/testdb/noopen/arch_k0so88kj_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/orabackup/backup/RMAN/testdb/noopen/arch_jvso88kj_1_1 tag=TAG20180109T124154
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_2: piece handle=/u01/orabackup/backup/RMAN/testdb/noopen/arch_k0so88kj_1_1 tag=TAG20180109T124154
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/TESTDBN/ARCHIVELOG/2018_01_09/thread_1_seq_32.1865.964963345 thread=1 sequence=32
archived log file name=+FRA/TESTDBN/ARCHIVELOG/2018_01_09/thread_2_seq_36.2113.964963345 thread=2 sequence=36
channel clone_default: deleting archived log(s)
archived log file name=+FRA/TESTDBN/ARCHIVELOG/2018_01_09/thread_1_seq_32.1865.964963345 RECID=2 STAMP=964970544
channel clone_default: deleting archived log(s)
archived log file name=+FRA/TESTDBN/ARCHIVELOG/2018_01_09/thread_2_seq_36.2113.964963345 RECID=1 STAMP=964970544
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-JAN-18
Oracle instance started
Total System Global Area 2097152000 bytes
Fixed Size 2926320 bytes
Variable Size 1291847952 bytes
Database Buffers 788529152 bytes
Redo Buffers 13848576 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''TESTDB'' comment=
''Reset to original value by RMAN'' scope=spfile";
}
executing Memory Script
sql statement: alter system set db_name = ''TESTDB'' comment= ''Reset to original value by RMAN'' scope=spfile
Oracle instance started
Total System Global Area 2097152000 bytes
Fixed Size 2926320 bytes
Variable Size 1291847952 bytes
Database Buffers 788529152 bytes
Redo Buffers 13848576 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 16 SIZE 256 M ,
GROUP 17 SIZE 256 M ,
GROUP 18 SIZE 256 M ,
GROUP 19 SIZE 256 M
DATAFILE
'+DATA/TESTDBN/DATAFILE/system.695.964963147'
CHARACTER SET WE8DEC
sql statement: ALTER DATABASE ADD LOGFILE
INSTANCE 'i2'
GROUP 20 SIZE 256 M ,
GROUP 21 SIZE 256 M ,
GROUP 22 SIZE 256 M ,
GROUP 23 SIZE 256 M
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA/TESTDBN/DATAFILE/sysaux.697.964963147",
"+DATA/TESTDBN/DATAFILE/undotbs1.704.964963147",
"+DATA/TESTDBN/DATAFILE/users.719.964963147",
"+DATA/TESTDBN/DATAFILE/undotbs2.710.964963147",
...
...
"+DATA/TESTDBN/DATAFILE/undotbs.715.964963147",
"+DATA/TESTDBN/DATAFILE/xdb.718.964963147";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/sysaux.697.964963147 RECID=1 STAMP=964970583
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/undotbs1.704.964963147 RECID=2 STAMP=964970583
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/users.719.964963147 RECID=3 STAMP=964970583
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/undotbs2.710.964963147 RECID=4 STAMP=964970583
...
...
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/xdb.718.964963147 RECID=61 STAMP=964970585
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=964970583 file name=+DATA/TESTDBN/DATAFILE/sysaux.697.964963147
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=964970583 file name=+DATA/TESTDBN/DATAFILE/undotbs1.704.964963147
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=964970583 file name=+DATA/TESTDBN/DATAFILE/users.719.964963147
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=964970583 file name=+DATA/TESTDBN/DATAFILE/undotbs2.710.964963147
...
...
datafile 62 switched to datafile copy
input datafile copy RECID=61 STAMP=964970585 file name=+DATA/TESTDBN/DATAFILE/xdb.718.964963147
Reenabling controlfile options for auxiliary database
Executing: alter database force logging
Leaving database unopened, as requested
Finished Duplicate Db at 09-JAN-18
At the end we can open database in upgrade mode with resetlogs:
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 9 15:26:59 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> alter database open resetlogs upgrade;
Database altered.
In my testcase I used NFS server to save the backup from source database. It was presented to new server. Just for your info: I used it to upgrade from Oracle 11.2.0.2 version on Red Hat 6.9 to Oracle 12.1.0.2 on Red Hat 7.4.
Please check MOS for more info: Upgrade to 12c(12.1 /12.2) through RMAN Duplicate using BACKUP LOCATION with NOOPEN clause (Doc ID 2022820.1)
So let's start with preparation. On source database perform full backup:
mkdir -p /u01/orabackup/backup/RMAN/testdb/noopen
backup as compressed backupset database format '/u01/orabackup/backup/RMAN/testdb/noopen/db_%U' plus archivelog format '/u01/orabackup/backup/RMAN/testdb/noopen/arch_%U';
backup current controlfile format '/u01/orabackup/backup/RMAN/testdb/noopen/cf_%U';
Copy password file and initfile to new server. Change parameter cluster_database=false by duplicating RAC DB.
On target server:
export ORACLE_HOME=/u01/app/oracle/12.1.0.2
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE=/u01/app/oracle
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 9 15:26:59 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL > startup nomount;
Connect to this instance as auxiliary and perform duplicate with noopen:
$ rman auxiliary /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jan 9 15:18:10 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: TESTDB (not mounted)
RMAN> DUPLICATE DATABASE to testdb noopen backup location '/u01/orabackup/backup/RMAN/testdb/noopen/';
Starting Duplicate Db at 09-JAN-18
contents of Memory Script:
{
sql clone "alter system set db_name =
''TESTDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/u01/orabackup/backup/RMAN/testdb/noopen/cf_k2so88l5_1_1';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''TESTDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 2097152000 bytes
Fixed Size 2926320 bytes
Variable Size 1291847952 bytes
Database Buffers 788529152 bytes
Redo Buffers 13848576 bytes
Starting restore at 09-JAN-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=84 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/testdb/controlfile/control01.ctl
output file name=+FRA/testdb/controlfile/control02.ctl
Finished restore at 09-JAN-18
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=84 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=7 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=85 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=167 device type=DISK
allocated channel: ORA_AUX_DISK_5
channel ORA_AUX_DISK_5: SID=244 device type=DISK
allocated channel: ORA_AUX_DISK_6
channel ORA_AUX_DISK_6: SID=324 device type=DISK
contents of Memory Script:
{
set until scn 14331183090492;
sql clone 'alter database flashback off';
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
...
...
set newname for clone datafile 61 to new;
set newname for clone datafile 62 to new;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database flashback off
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
...
...
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 09-JAN-18
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00013 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00018 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00028 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00031 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00032 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00035 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00036 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00040 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00042 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00052 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00056 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /u01/orabackup/backup/RMAN/testdb/noopen/db_juso88fq_1_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to +DATA
...
...
channel ORA_AUX_DISK_1: piece handle=/u01/orabackup/backup/RMAN/testdb/noopen/db_juso88fq_1_1 tag=TAG20180109T123921
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
...
...
channel ORA_AUX_DISK_6: piece handle=/u01/orabackup/backup/RMAN/testdb/noopen/db_jsso88fq_1_1 tag=TAG20180109T123921
channel ORA_AUX_DISK_6: restored backup piece 1
channel ORA_AUX_DISK_6: restore complete, elapsed time: 00:00:55
Finished restore at 09-JAN-18
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=63 STAMP=964970532 file name=+DATA/TESTDBN/DATAFILE/system.695.964963147
datafile 2 switched to datafile copy
input datafile copy RECID=64 STAMP=964970532 file name=+DATA/TESTDBN/DATAFILE/sysaux.697.964963147
datafile 3 switched to datafile copy
input datafile copy RECID=65 STAMP=964970532 file name=+DATA/TESTDBN/DATAFILE/undotbs1.704.964963147
datafile 4 switched to datafile copy
input datafile copy RECID=66 STAMP=964970532 file name=+DATA/TESTDBN/DATAFILE/users.719.964963147
...
...
datafile 62 switched to datafile copy
input datafile copy RECID=124 STAMP=964970535 file name=+DATA/TESTDBN/DATAFILE/xdb.718.964963147
contents of Memory Script:
{
set until scn 14331183090492;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 09-JAN-18
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4
using channel ORA_AUX_DISK_5
using channel ORA_AUX_DISK_6
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=2 sequence=36
channel ORA_AUX_DISK_1: reading from backup piece /u01/orabackup/backup/RMAN/testdb/noopen/arch_jvso88kj_1_1
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=32
channel ORA_AUX_DISK_2: reading from backup piece /u01/orabackup/backup/RMAN/testdb/noopen/arch_k0so88kj_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/orabackup/backup/RMAN/testdb/noopen/arch_jvso88kj_1_1 tag=TAG20180109T124154
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_2: piece handle=/u01/orabackup/backup/RMAN/testdb/noopen/arch_k0so88kj_1_1 tag=TAG20180109T124154
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=+FRA/TESTDBN/ARCHIVELOG/2018_01_09/thread_1_seq_32.1865.964963345 thread=1 sequence=32
archived log file name=+FRA/TESTDBN/ARCHIVELOG/2018_01_09/thread_2_seq_36.2113.964963345 thread=2 sequence=36
channel clone_default: deleting archived log(s)
archived log file name=+FRA/TESTDBN/ARCHIVELOG/2018_01_09/thread_1_seq_32.1865.964963345 RECID=2 STAMP=964970544
channel clone_default: deleting archived log(s)
archived log file name=+FRA/TESTDBN/ARCHIVELOG/2018_01_09/thread_2_seq_36.2113.964963345 RECID=1 STAMP=964970544
media recovery complete, elapsed time: 00:00:01
Finished recover at 09-JAN-18
Oracle instance started
Total System Global Area 2097152000 bytes
Fixed Size 2926320 bytes
Variable Size 1291847952 bytes
Database Buffers 788529152 bytes
Redo Buffers 13848576 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''TESTDB'' comment=
''Reset to original value by RMAN'' scope=spfile";
}
executing Memory Script
sql statement: alter system set db_name = ''TESTDB'' comment= ''Reset to original value by RMAN'' scope=spfile
Oracle instance started
Total System Global Area 2097152000 bytes
Fixed Size 2926320 bytes
Variable Size 1291847952 bytes
Database Buffers 788529152 bytes
Redo Buffers 13848576 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 16 SIZE 256 M ,
GROUP 17 SIZE 256 M ,
GROUP 18 SIZE 256 M ,
GROUP 19 SIZE 256 M
DATAFILE
'+DATA/TESTDBN/DATAFILE/system.695.964963147'
CHARACTER SET WE8DEC
sql statement: ALTER DATABASE ADD LOGFILE
INSTANCE 'i2'
GROUP 20 SIZE 256 M ,
GROUP 21 SIZE 256 M ,
GROUP 22 SIZE 256 M ,
GROUP 23 SIZE 256 M
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA/TESTDBN/DATAFILE/sysaux.697.964963147",
"+DATA/TESTDBN/DATAFILE/undotbs1.704.964963147",
"+DATA/TESTDBN/DATAFILE/users.719.964963147",
"+DATA/TESTDBN/DATAFILE/undotbs2.710.964963147",
...
...
"+DATA/TESTDBN/DATAFILE/undotbs.715.964963147",
"+DATA/TESTDBN/DATAFILE/xdb.718.964963147";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/sysaux.697.964963147 RECID=1 STAMP=964970583
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/undotbs1.704.964963147 RECID=2 STAMP=964970583
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/users.719.964963147 RECID=3 STAMP=964970583
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/undotbs2.710.964963147 RECID=4 STAMP=964970583
...
...
cataloged datafile copy
datafile copy file name=+DATA/TESTDBN/DATAFILE/xdb.718.964963147 RECID=61 STAMP=964970585
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=964970583 file name=+DATA/TESTDBN/DATAFILE/sysaux.697.964963147
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=964970583 file name=+DATA/TESTDBN/DATAFILE/undotbs1.704.964963147
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=964970583 file name=+DATA/TESTDBN/DATAFILE/users.719.964963147
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=964970583 file name=+DATA/TESTDBN/DATAFILE/undotbs2.710.964963147
...
...
datafile 62 switched to datafile copy
input datafile copy RECID=61 STAMP=964970585 file name=+DATA/TESTDBN/DATAFILE/xdb.718.964963147
Reenabling controlfile options for auxiliary database
Executing: alter database force logging
Leaving database unopened, as requested
Finished Duplicate Db at 09-JAN-18
At the end we can open database in upgrade mode with resetlogs:
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 9 15:26:59 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> alter database open resetlogs upgrade;
Database altered.
Friday, 5 January 2018
WRI$_OPTSTAT_HISTHEAD_HISTORY and SM/OPTSTAT in SYSAUX Tablespace. Why is getting full and how to reorg it.
The SYSAUX Tablespace can become full for various reasons. In my case, it was due to SM/OPSTATS segments (WRI$_OPTSTAT_HISTHEAD_HISTORY and their indexes) occupying most of the space. How can we fix this issue and avoid continually adding more space to the tablespace?
This post will identify the root cause and provide solutions based on real production data from a 30TB data warehouse running on Exadata X4-2. The database version is 11.2.0.4 BP20.
Let's start with a standard query to check the "occupants" in the SYSAUX tablespace:
set linesize 120
set pagesize 100
COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40
SELECT occupant_name "Item",
space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 2 desc
/
Item Space Used (GB) Schema Move Procedure
------------------------- --------------- ------------------ ----------------------------------------
SM/OPTSTAT 132.57 SYS
SM/AWR 47.35 SYS
JOB_SCHEDULER 7.79 SYS
SM/ADVISOR 2.47 SYS
XDB .06 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE
...
...
AUDIT_TABLES .00 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables
ORDIM .00 ORDSYS ordsys.ord_admin.move_ordim_tblspc
Almost 132GB is occupied by SM/OPSTATS and WRI$_OPTSTAT_HISTHEAD_HISTORY table with two indexes on it:
select segment_name,segment_type,trunc(bytes/1024/1024/1024) gb from dba_segments where segment_name in ('WRI$_OPTSTAT_HISTHEAD_HISTORY','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST','I_WRI$_OPTSTAT_HH_ST');
SEGMENT_NAME SEGMENT_TYPE GB
-------------------------------- ------------------ ----------
WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 57
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 37
I_WRI$_OPTSTAT_HH_ST INDEX 27
select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
So we have here default value of 1 month but in my database we have history of one year:
SQL> select /*+full(t) parallel(8)*/ trunc(sysdate)-trunc(min(savtime)) from sys.WRI$_OPTSTAT_HISTHEAD_HISTORY t;
TRUNC(SYSDATE)-TRUNC(MIN(SAVTIME))
----------------------------------
350
Why? The default job which purge this table is limited to 5 minutes. And if it could not finish on time, then you would not get any information in alertlog about aborted job and the space consumption in SYSAUX will grow.
In internal table wri$_optstat_opr which store the information about gathering/restoring/purging stats, we don's see anything:
select count(*) from sys.wri$_optstat_opr where operation='purge_stats';
COUNT(*)
----------
0
----------
0
Bare in mind that WRI$_OPTSTAT_HISTHEAD_HISTORY table stores history information about column statistics per table/partition/subpartitions, so for example for partitioned table with 100 partition with 15 columns one run of gather_table_stats will generate = 100 x 15 +1x15 = 1515 rows!
I've checked this with simple test on in my VirtaulBox lab:
SQL> exec dbms_stats.purge_stats(sysdate);
PL/SQL procedure successfully completed.
SQL> select count(*) from sys.WRI$_OPTSTAT_HISTHEAD_HISTORY t;
COUNT(*)
----------
0
Table created.
SQL> select count(*) from dba_tab_columns where table_name='TABLE_PURGE';
COUNT(*)
----------
15
SQL> exec dbms_stats.gather_table_stats('SYS','TABLE_PURGE');
PL/SQL procedure successfully completed.
SQL> select count(*) from sys.WRI$_OPTSTAT_HISTHEAD_HISTORY t;
COUNT(*)
----------
1515
----------
1515
Imagine that is pretty common(like in my production datawarehouse) that after some application release+bunch of batch loading jobs database gets some new columns and thousands of partition/subpartition. After it, lets gather the statistics for all your tables and you have problem:) Purge job will not finish within the limit to "DELETE FROM".
So what is the next step? We use a simple loop to remove old entries. This step can take lot of time as under the hood a simple delete from ... is running.
begin
for i in reverse 31..360
loop
dbms_application_info.set_action(sysdate-i);
dbms_stats.purge_stats(sysdate-i);
end loop;
end;
loop
dbms_application_info.set_action(sysdate-i);
dbms_stats.purge_stats(sysdate-i);
end loop;
end;
/
Once it finish, you can check how many empty block the table has:
set serveroutput on
exec dbms_output.enable(1000000);
declare
unf number;
unfb number;
fs1 number;
fs1b number;
fs2 number;
fs2b number;
fs3 number;
fs3b number;
fs4 number;
fs4b number;
full number;
fullb number;
own dba_tables.owner%type;
tab dba_tables.table_name%type;
yesno varchar2(3);
type parts is table of dba_tab_partitions%rowtype;
partlist parts;
type cursor_ref is ref cursor;
c_cur cursor_ref;
begin
own:=upper('&owner');
tab:=upper('&table_name');
dbms_output.put_line('--------------------------------------------------------------------------------');
open c_cur for select partitioned from dba_tables
where owner=own and table_name=tab;
fetch c_cur into yesno;
close c_cur;
dbms_output.put_line('Owner: '||own);
dbms_output.put_line('Table: '||tab);
dbms_output.put_line('------------------------------------------------');
if yesno='NO'
then
dbms_space.space_usage(own,tab,'TABLE',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full);
else
open c_cur for select * from dba_tab_partitions
where table_owner=own and table_name=tab;
fetch c_cur bulk collect into partlist;
close c_cur;
for i in partlist.first .. partlist.last
loop
dbms_space.space_usage(partlist(i).table_owner,partlist(i).table_name,'TABLE PARTITION',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partlist(i).partition_name);
dbms_output.put_line('Partition: '||partlist(i).partition_name);
dbms_output.put_line('unf: '||unf||' fs1: '||fs1||' fs2: '||fs2||' fs3: '||fs3||' fs4: '||fs4||' full: '||full);
end loop;
end if;
dbms_output.put_line('--------------------------------------------------------------------------------');
end;
/
--------------------------------------------------------------------------------
Owner: SYS
Table: WRI$_OPTSTAT_HISTHEAD_HISTORY
------------------------------------------------
unf: 17204 fs1: 109 fs2: 152 fs3: 180 fs4: 6830726 full: 708823
--------------------------------------------------------------------------------
fs4 means empty blocks. We have 6830726 of them after purging.
As the most of space is empty lets reorganize the table and rebuild Function-Based Indexes:
alter index "SYS"."I_WRI$_OPTSTAT_HH_ST" unusable;
alter index "SYS"."I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST" unusable;
alter table "SYS"."WRI$_OPTSTAT_HISTHEAD_HISTORY" move parallel 8;
alter index "SYS"."I_WRI$_OPTSTAT_HH_ST" rebuild online parallel 8;
alter index "SYS"."I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST" rebuild online parallel 8;
alter index "SYS"."I_WRI$_OPTSTAT_HH_ST" noparallel;
alter index "SYS"."I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST" noparallel;
select segment_name,segment_type,trunc(bytes/1024/1024/1024) gb from dba_segments where segment_name in ('WRI$_OPTSTAT_HISTHEAD_HISTORY','I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST','I_WRI$_OPTSTAT_HH_ST');
SEGMENT_NAME SEGMENT_TYPE GB
------------------------------- ------------------ ----------
WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 5
I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 2
I_WRI$_OPTSTAT_HH_ST INDEX 1
--------------------------------------------------------------------------------
Owner: SYS
Table: WRI$_OPTSTAT_HISTHEAD_HISTORY
------------------------------------------------
unf: 0 fs1: 0 fs2: 0 fs3: 0 fs4: 0 full: 712575
--------------------------------------------------------------------------------
Before 132GB and after 8GB. Not bad.
Item Space Used (GB) Schema Move Procedure
------------------------- --------------- ------------------------- ----------------------------------------
SM/AWR 47.93 SYS
SM/OPTSTAT 19.62 SYS
JOB_SCHEDULER 7.79 SYS
SM/ADVISOR 2.49 SYS
...
...
AUDIT_TABLES .00 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables
ORDIM .00 ORDSYS ordsys.ord_admin.move_ordim_tblspc
Wednesday, 24 May 2017
Failed login attempts
Number of failed login attempts exceeds threshold value message comes from OEM.
How to check what user is causing this? DBA_AUDIT_SESSION can help to identifiy the username,userhost, etc:
SQL> desc DBA_AUDIT_SESSION
Name
-------------------
OS_USERNAME
USERNAME
USERHOST
TERMINAL
TIMESTAMP
ACTION_NAME
LOGOFF_TIME
LOGOFF_LREAD
LOGOFF_PREAD
LOGOFF_LWRITE
LOGOFF_DLOCK
SESSIONID
RETURNCODE
CLIENT_ID
SESSION_CPU
EXTENDED_TIMESTAMP
PROXY_SESSIONID
GLOBAL_UID
INSTANCE_NUMBER
OS_PROCESS
Lets check the errors within last day:
SELECT
TO_CHAR(TIMESTAMP,'YYYY/MM/DD HH24:MI') TIMESTAMP,
SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
SUBSTR(USERNAME,1,20) USERNAME,
SUBSTR(TERMINAL,1,20) TERMINAL,
ACTION_NAME,
RETURNCODE
FROM
SYS.DBA_AUDIT_SESSION
WHERE TIMESTAMP BETWEEN SYSDATE-1 AND SYSDATE
and RETURNCODE>0
ORDER BY TIMESTAMP;
TIMESTAMP OS_USERNAM USERNAME TERMINAL ACTION_NAME RETURNCODE
---------------- ---------- --------------------- ----------------- ------------- ----------
2017/05/24 12:56 SYSTEM DBP_007_GENEOS_USER LONGCASU0014 LOGON 1017
2017/05/24 12:56 SYSTEM DBP_007_GENEOS_USER LONGCASU0014 LOGON 1017
2017/05/24 12:56 SYSTEM DBP_007_GENEOS_USER LONGCASU0014 LOGON 1017
2017/05/24 12:56 SYSTEM DBP_007_GENEOS_USER LONGCASU0014 LOGON 1017
2017/05/24 12:56 SYSTEM DBP_007_GENEOS_USER LONGCASU0014 LOGON 1017
2017/05/24 12:56 SYSTEM DBP_007_GENEOS_USER LONGCASU0014 LOGON 1017
2017/05/24 12:57 SYSTEM DBP_007_GENEOS_USER LONGCASU0014 LOGON 1017
2017/05/24 12:57 SYSTEM DBP_007_GENEOS_USER LONGCASU0014 LOGON 1017
SELECT SUBSTR(OS_USERNAME,1,20) OS_USERNAME,
SUBSTR(USERNAME,1,20) USERNAME,
SUBSTR(TERMINAL,1,20) TERMINAL,
ACTION_NAME,
RETURNCODE,count(*)
FROM
SYS.DBA_AUDIT_SESSION
WHERE TIMESTAMP BETWEEN SYSDATE-1 AND SYSDATE
and RETURNCODE>0
group by SUBSTR(OS_USERNAME,1,20),
SUBSTR(USERNAME,1,20),
SUBSTR(TERMINAL,1,20),
ACTION_NAME,RETURNCODE
order by count(*);
OS_USERNAM USERNAME TERMINAL ACTION_NAME RETURNCODE COUNT(*)
---------- ------------------------ ---------------- ------------ ---------- ----------
umesnag DBP_107_ETL_USER unknown LOGON 1017 1
singtaja DBP_TOOLS_USER unknown LOGON 1017 1
singtaja DBP_007_ACCESS_OWNER unknown LOGON 28000 1
rohivai DBP_TOOLS_USER unknown LOGON 1017 1
dubeamib DUBEAMIB unknown LOGON 28001 1
kumshar PRASAVV unknown LOGON 28001 1
nunepab DBP_011_STAGING_USER unknown LOGON 1017 1
singhash DBP_TOOLS_USER unknown LOGON 1017 1
umalaks URJA_DBP_107_RAP_OWN unknown LOGON 1017 1
umalaks DBP_107_RAP_OWNER unknown LOGON 1017 1
kumakun DBP_TOOLS_USER unknown LOGON 1017 5
panitan DBP_014_APP_USER unknown LOGON 1017 10
dubeamib DBP_107_ETL_USER unknown LOGON 1017 12
infarun DBP_007_ETL_USER LOGON 28000 36
SYSTEM DBP_007_GENEOS_USER LONGCASU0014 LOGON 1017 121
SYSTEM DBP_007_GENEOS_USER LONGCASU0014 LOGON 28000 6920
RETURNCODE column is simply Oracle Error Code
SQL> !oerr ora 28000
28000, 00000, "the account is locked"
// *Cause: The user has entered wrong password consequently for maximum
// number of times specified by the user's profile parameter
// FAILED_LOGIN_ATTEMPTS, or the DBA has locked the account
// *Action: Wait for PASSWORD_LOCK_TIME or contact DBA
SQL> !oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:
Subscribe to:
Comments (Atom)