This article is showing how to resolve issue when query has an issue during its execution with database link.

When the query works with ".world" on the db link name, but it doesn't work without ".world".

Example) This has been tested on Oracle Database 12.2

17:14:27 TESTDB> select 1 from dual@SIMPLE;

select 1 from dual@SIMPLE

 *

ERROR at line 1:

ORA-02019: connection description for remote database not found

Elapsed: 00:00:00.00

17:14:40 TESTDB> select 1 from dual@SIMPLE.world;

1

----------

 1

Then, the global name setting should be checked :

17:20:58 TESTDB> select * from global_name;

GLOBAL_NAME

------------------------------

TESTDB

There is no ".world" at the end of the global_name. So just this simply can be updated with the following command. (No database bounce required)

17:21:58 TESTDB> ALTER DATABASE RENAME GLOBAL_NAME TO TESTDB.WORLD;

17:21:28 TESTDB> select * from global_name;

GLOBAL_NAME

------------------------------

TESTDB.WORLD

17:21:28 TESTDB> select 1 from dual@SIMPLE;

1

----------

 1

FYI, this is not related with db domain name setting. If db_domain is changed, then you will need to change lots of things such as db service names, listeners, and so on. Please don't touch db_domain name for only this ".world" on db link name issue.

17:22:03 TESTDB> show parameter domain

NAME                                 TYPE       VALUE

------------------------------------ ---------- ------------------------------

db_domain                            string

Here are the code could help to find all stored procedures(SP) related to specific table.

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
1,2,3,4
WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
WHERE PK.TABLE_NAME IN ('one_thing', 'another')
WHERE FK.TABLE_NAME IN ('one_thing', 'another')

SELECT K_Table = FK.TABLE_NAME, FK_Column = CU.COLUMN_NAME, PK_Table = PK.TABLE_NAME, PK_Column = PT.COLUMN_NAME, Constraint_Name = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME, i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME ---- optional: ORDER BY 1,2,3,4 WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something' WHERE PK.TABLE_NAME IN ('one_thing', 'another') WHERE FK.TABLE_NAME IN ('one_thing', 'another')

SQLT can be downloaded from Note 215187.1 on My Oracle Support for those that have a valid Oracle Support contract. The utility comes as a simple zip file. Before SQLT can be used, it must be installed in the database. Installation will create two schemas, sqltxplain and sqltxadmin. The utility was unzipped to the directory /home/oracle/sqlt and the installation is started below. The installation should be performed as a sysdba user.

SQL> connect / as sysdba
SQL> @/home/oracle/sqlt/install/sqcreate.sql

PL/SQL procedure successfully completed.

Installing SQLT

SQLT installs under its own schemas SQLTXPLAIN and SQLTXADMIN. It does not install any objects into the application schema(s). You can install this version of SQLT in Oracle databases 10.2, 11.1, 11.2 and higher, on UNIX, Linux or Windows platforms.

Installation steps:

1. UNINSTALL A PRIOR VERSION (OPTIONAL).

This optional step removes all obsolete SQLTXPLAIN/SQLTXADMIN schema objects and prepares the environment for a fresh install. Skip this step if you want to preserve the existing content of the SQLT repository (recommended).

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdrop.sql

2. EXECUTE INSTALLATION SCRIPT SQLT/INSTALL/SQCREATE.SQL CONNECTED AS SYS.

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcreate.sql

During the installation you will be asked to enter values for these parameters:

1. Optional Connect Identifier (mandatory when installing in a Pluggable Database)

In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the “Enter” key. Entering nothing is the most common setup.

The Connect Identifier is a mandatory parameter when installing SQLT in a Pluggable Database.

2. SQLTXPLAIN password.

Case sensitive in most systems.

3. SQLTXPLAIN Default Tablespace.

Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.

4. SQLTXPLAIN Temporary Tablespace.

Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.

5. Optional Application User.

This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft SYSADM. You won’t be asked to enter the password for this user. You can add additional SQLT users after the tool is installed, by granting them role SQLT_USER_ROLE.

6. Licensed Oracle Pack. (T, D or N)

You can specify T for Oracle Tuning, D for Oracle Diagnostic or N for none. If T or D is selected, SQLT may include licensed content within the diagnostics files it produces. Default is T. If N is selected, SQLT installs with limited functionality.

If a silent installation is desired, there are three options to pass all 6 installation parameters:

1. In a file.

Executing first a script with pre-defined values, similar to sample script sqlt/install/sqdefparams.sql. Then use sqlt/install/sqcsilent.sqlinstead of sqlt/install/sqcreate.sql.

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqdefparams.sql
SQL> START sqcsilent.sql

2. In-line.

Executing sqlt/install/sqcsilent2.sql instead of sqlt/install/sqcreate.sql. The former inputs the same 6 installation parameters but in-line.

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcsilent2.sql ” sqltxplain USERS TEMP ” T

3. Internal installation at Oracle.

Executing sqlt/install/sqcinternal.sql instead of sqlt/install/sqcreate.sql. The former executes sqlt/install/sqdefparams.sqlfollowed by sqlt/install/sqcsilent.sql.

# cd sqlt/install
# sqlplus / as sysdba
SQL> START sqcinternal.sql

If you need further help with install issues, you can get help in the following community thread: SQLTXPLAIN: SQLT Installation Issues

Reference : https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=251255531953961&parent=DOCUMENT&sourceId=215187.1&id=1614107.1&_afrWindowMode=0&_adf.ctrl-state=18te3heobc_114

Step 0: Prepare

Source database;
Target Database : fopsp
Target Database Server : bdcorap02
Using ASM storage on Windows Server 2008 R2

Destination database;
Auxiliary Database : fopst
Auxiliary Database Server : bdcorat02
Using OS file system on Windows Server 2008 R2

Step 1:

Take the incremental level 0 backup of the Target database using RMAN.
In my case, I had the backup of my target database (fopsp) taken at the location “\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\”

PS Microsoft.PowerShell.Core\FileSystem::\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP> dir | more

Directory: \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP

Mode LastWriteTime Length Name
---- ------------- ------ ----
-a--- 05/10/2015 12:33 PM 9961472 C-1427950135-20151005-00
-a--- 05/10/2015 6:22 PM 9961472 C-1427950135-20151005-01
-a--- 05/10/2015 12:33 PM 176651264 FOPSP_ARCHLOG_T892298026_S564_P1
-a--- 05/10/2015 6:22 PM 318571008 FOPSP_ARCHLOG_T892318958_S566_P1
-a--- 22/07/2015 6:20 PM 9961472 C-1427950135-20150722-02
-a--- 23/07/2015 12:18 PM 9961472 C-1427950135-20150723-00
-a--- 27/06/2015 12:24 PM 213430272 FOPSP_ARCHLOG_FOPSP_T883484670_S88_P1
-a--- 27/06/2015 5:48 PM 183261184 FOPSP_ARCHLOG_FOPSP_T883504088_S89_P1
-a--- 27/06/2015 11:43 PM 93816832 FOPSP_ARCHLOG_FOPSP_T883525414_S90_P1
-a--- 27/06/2015 3:45 AM 3621756928 FOPSP_FULL_FOPSP_2MQAGRGR_1_883453467
-a--- 27/06/2015 3:45 AM 9961472 FOPSP_FULL_FOPSP_2NQAGRJH_1_883453553

Step 2:

These backup pieces are able to be accessed from Target server and Auxiliary server at the same time.

Step 3:

On the Auxiliary server, edit the pfile that was copied earlier to the desired entries (dump locations, control file location, datafile locations, if using ASM then specify the desired disk group) and rename it to the desired instance name file (init<SID>.ora). Below is the sample I had it done.

initfopst.ora which is copied from initfopsp.ora

fopsp.__db_cache_size=2113929216
fopsp.__java_pool_size=16777216
fopsp.__large_pool_size=50331648
fopsp.__oracle_base='C:\Oracle'#ORACLE_BASE set from environment
fopsp.__pga_aggregate_target=419430400
fopsp.__sga_target=3355443200
fopsp.__shared_io_pool_size=0
fopsp.__shared_pool_size=1124073472
fopsp.__streams_pool_size=16777216
*.audit_file_dest='C:\Oracle\admin\FOPST\adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='E:\FOPST\control\CONTROLFOPST.CTL'
*.cursor_sharing='SIMILAR'
*.db_block_size=8192
*.db_domain='WESTERNFOREST.COM'
*.db_name='FOPST'
*.db_recovery_file_dest_size=4385144832
*.db_recovery_file_dest='E:\FOPST\fast_recovery_area'
*.diagnostic_dest='C:\Oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=FOPSTXDB)'
*.log_archive_dest_1='LOCATION=E:\FOPST\arch'
*.log_archive_format='ARC%S_%R.%T'
*.memory_max_target=3774873600
*.memory_target=3774873600
*.open_cursors=300
*.pga_aggregate_target=419430400
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_max_size=3250585600
*.sga_target=3250585600
*.shared_pool_size=956301312
*.undo_tablespace='UNDOTBS1'

Step 4:

a. Create password file

PS H:\> orapwd file=C:\Oracle\product\11.2.0\dbhome_1\database\PWDFOPST.ora password=xxxxx

b. Create Windows Service for Oracle new SID

PS H:\> C:\oracle\product\11.2.0\dbhome_1\bin\oradim -new -sid FOPST -startmode manual -shutmode immediate

Step 5:

When you sqlplus login if you get this error message:

ERROR:
ORA-12631: Username retrieval failed

You can try this steps;

a. You can log in as a NT local user.

b. You can disable NTS in sqlnet.ora by setting the following parameter:

SQLNET.AUTHENTICATION_SERVICES = (NONE)

PS H:\> sqlplus sys/xxxxx as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 2 16:12:04 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

16:12:06 >
16:12:06 > startup nomount;

Total System Global Area 1.7771E+10 bytes
Fixed Size 2288480 bytes
Variable Size 6777996448 bytes
Database Buffers 1.0939E+10 bytes
Redo Buffers 52224000 bytes
16:14:12 >

Step 6:

Connect the auxiliary instance through RMAN and start the duplication.

PS H:\> rman auxiliary sys/xxxxx

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Oct 2 16:15:21 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to auxiliary database: FOPST (not mounted)

RMAN>

Script:
RUN {
set newname for datafile 1 to 'E:\FOPST\oradata\SYSTEM01.DBF';
set newname for datafile 2 to 'E:\FOPST\oradata\SYSAUX01.DBF';
set newname for datafile 3 to 'E:\FOPST\oradata\UNDOTBS1_01.DBF';
set newname for datafile 4 to 'E:\FOPST\oradata\USERS01.DBF';
set newname for datafile 5 to 'E:\FOPST\oradata\forestops_index01.DBF';
set newname for datafile 6 to 'E:\FOPST\oradata\forest_ops_data_ts01.DBF';
set newname for tempfile 1 to 'E:\FOPST\oradata\TEMP01.DBF';
duplicate database to 'FOPST' backup location '\\bdcdr4k01\DBBACKUP\Oracle\Production\FOPSP';
switch datafile all;
}
Result:
--------------------------------------------------------------------------------------
executing command: SET NEWNAME

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 Duplicate Db at 05-OCT-15

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 3240239104 bytes

Fixed Size 2285592 bytes
Variable Size 1107300328 bytes
Database Buffers 2113929216 bytes
Redo Buffers 16723968 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''FOPSP'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''FOPST'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\C-1427950135-20151004-01';
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ''FOPSP'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''FOPST'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 3240239104 bytes

Fixed Size 2285592 bytes
Variable Size 1107300328 bytes
Database Buffers 2113929216 bytes
Redo Buffers 16723968 bytes

Starting restore at 05-OCT-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=130 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=E:\FOPST\CONTROL\CONTROLFOPST.CTL
Finished restore at 05-OCT-15

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=130 device type=DISK

contents of Memory Script:
{
set until scn 4298569114718;
set newname for datafile 1 to
"E:\FOPST\oradata\SYSTEM01.DBF";
set newname for datafile 2 to
"E:\FOPST\oradata\SYSAUX01.DBF";
set newname for datafile 3 to
"E:\FOPST\oradata\UNDOTBS1_01.DBF";
set newname for datafile 4 to
"E:\FOPST\oradata\USERS01.DBF";
set newname for datafile 5 to
"E:\FOPST\oradata\forestops_index01.DBF";
set newname for datafile 6 to
"E:\FOPST\oradata\forest_ops_data_ts01.DBF";
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 05-OCT-15
using channel ORA_AUX_DISK_1

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 00001 to E:\FOPST\oradata\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002 to E:\FOPST\oradata\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003 to E:\FOPST\oradata\UNDOTBS1_01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004 to E:\FOPST\oradata\USERS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00005 to E:\FOPST\oradata\forestops_index01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00006 to E:\FOPST\oradata\forest_ops_data_ts01.DBF
channel ORA_AUX_DISK_1: reading from backup piece \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_FULL_H8QIOI7P_1_892
094713
channel ORA_AUX_DISK_1: piece handle=\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_FULL_H8QIOI7P_1_892094713 tag=WE
EKLY_FULL_BACKUP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 05-OCT-15

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=892303045 file name=E:\FOPST\ORADATA\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=892303045 file name=E:\FOPST\ORADATA\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=892303045 file name=E:\FOPST\ORADATA\UNDOTBS1_01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=892303045 file name=E:\FOPST\ORADATA\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=892303045 file name=E:\FOPST\ORADATA\FORESTOPS_INDEX01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=892303045 file name=E:\FOPST\ORADATA\FOREST_OPS_DATA_TS01.DBF

contents of Memory Script:
{
set until scn 4298569114718;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 05-OCT-15
using channel ORA_AUX_DISK_1

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=1 sequence=590
channel ORA_AUX_DISK_1: reading from backup piece \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892122428_
S554_P1
channel ORA_AUX_DISK_1: piece handle=\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892122428_S554_P1 tag=A
RCHIVELOG_LOG_BACKUP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=E:\FOPST\ARCH\ARC0000000590_0881072057.0001 thread=1 sequence=590
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000590_0881072057.0001 RECID=1 STAMP=892303048
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=591
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=592
channel ORA_AUX_DISK_1: reading from backup piece \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892146539_
S556_P1
channel ORA_AUX_DISK_1: piece handle=\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892146539_S556_P1 tag=A
RCHIVELOG_LOG_BACKUP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=E:\FOPST\ARCH\ARC0000000591_0881072057.0001 thread=1 sequence=591
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000591_0881072057.0001 RECID=3 STAMP=892303055
archived log file name=E:\FOPST\ARCH\ARC0000000592_0881072057.0001 thread=1 sequence=592
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000592_0881072057.0001 RECID=2 STAMP=892303054
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=593
channel ORA_AUX_DISK_1: reading from backup piece \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892165775_
S558_P1
channel ORA_AUX_DISK_1: piece handle=\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892165775_S558_P1 tag=A
RCHIVELOG_LOG_BACKUP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=E:\FOPST\ARCH\ARC0000000593_0881072057.0001 thread=1 sequence=593
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000593_0881072057.0001 RECID=4 STAMP=892303062
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=594
channel ORA_AUX_DISK_1: reading from backup piece \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892209183_
S560_P1
channel ORA_AUX_DISK_1: piece handle=\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892209183_S560_P1 tag=A
RCHIVELOG_LOG_BACKUP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=E:\FOPST\ARCH\ARC0000000594_0881072057.0001 thread=1 sequence=594
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000594_0881072057.0001 RECID=5 STAMP=892303065
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=595
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=596
channel ORA_AUX_DISK_1: reading from backup piece \\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892230482_
S562_P1
channel ORA_AUX_DISK_1: piece handle=\\BDCDR4K01\DBBACKUP\ORACLE\PRODUCTION\FOPSP\FOPSP_ARCHLOG_T892230482_S562_P1 tag=A
RCHIVELOG_LOG_BACKUP
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=E:\FOPST\ARCH\ARC0000000595_0881072057.0001 thread=1 sequence=595
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000595_0881072057.0001 RECID=7 STAMP=892303071
archived log file name=E:\FOPST\ARCH\ARC0000000596_0881072057.0001 thread=1 sequence=596
channel clone_default: deleting archived log(s)
archived log file name=E:\FOPST\ARCH\ARC0000000596_0881072057.0001 RECID=6 STAMP=892303070
media recovery complete, elapsed time: 00:00:06
Finished recover at 05-OCT-15
Oracle instance started

Total System Global Area 3240239104 bytes

Fixed Size 2285592 bytes
Variable Size 1107300328 bytes
Database Buffers 2113929216 bytes
Redo Buffers 16723968 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''FOPST'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_name = ''FOPST'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 3240239104 bytes

Fixed Size 2285592 bytes
Variable Size 1107300328 bytes
Database Buffers 2113929216 bytes
Redo Buffers 16723968 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "FOPST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 262145 K ,
GROUP 2 SIZE 262145 K ,
GROUP 3 SIZE 262145 K
DATAFILE
'E:\FOPST\ORADATA\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"E:\FOPST\oradata\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "E:\FOPST\ORADATA\SYSAUX01.DBF",
"E:\FOPST\ORADATA\UNDOTBS1_01.DBF",
"E:\FOPST\ORADATA\USERS01.DBF",
"E:\FOPST\ORADATA\FORESTOPS_INDEX01.DBF",
"E:\FOPST\ORADATA\FOREST_OPS_DATA_TS01.DBF";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to E:\FOPST\oradata\TEMP01.DBF in control file

cataloged datafile copy
datafile copy file name=E:\FOPST\ORADATA\SYSAUX01.DBF RECID=1 STAMP=892303104
cataloged datafile copy
datafile copy file name=E:\FOPST\ORADATA\UNDOTBS1_01.DBF RECID=2 STAMP=892303104
cataloged datafile copy
datafile copy file name=E:\FOPST\ORADATA\USERS01.DBF RECID=3 STAMP=892303104
cataloged datafile copy
datafile copy file name=E:\FOPST\ORADATA\FORESTOPS_INDEX01.DBF RECID=4 STAMP=892303104
cataloged datafile copy
datafile copy file name=E:\FOPST\ORADATA\FOREST_OPS_DATA_TS01.DBF RECID=5 STAMP=892303104

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=892303104 file name=E:\FOPST\ORADATA\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=892303104 file name=E:\FOPST\ORADATA\UNDOTBS1_01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=892303104 file name=E:\FOPST\ORADATA\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=892303104 file name=E:\FOPST\ORADATA\FORESTOPS_INDEX01.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=892303104 file name=E:\FOPST\ORADATA\FOREST_OPS_DATA_TS01.DBF

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 05-OCT-15
RMAN>

RMAN>

RMAN>

RMAN>

RMAN> exit
Recovery Manager complete.
PS H:\> sqlplus sys/xxxxx as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 5 14:02:59 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

14:02:59 fopst> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
14:03:15 fopst> startup
ORACLE instance started.

Total System Global Area 3240239104 bytes
Fixed Size 2285592 bytes
Variable Size 1090523112 bytes
Database Buffers 2130706432 bytes
Redo Buffers 16723968 bytes
Database mounted.
Database opened.
14:03:32 fopst> create spfile from pfile;

File created.

Elapsed: 00:00:00.09
14:03:40 fopst> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
14:03:54 fopst> startup mount;
ORACLE instance started.

Total System Global Area 3240239104 bytes
Fixed Size 2285592 bytes
Variable Size 1090523112 bytes
Database Buffers 2130706432 bytes
Redo Buffers 16723968 bytes
Database mounted.
14:04:09 fopst> alter database noarchivelog;

Database altered.

Elapsed: 00:00:00.04
14:04:15 fopst> alter database open;

Database altered.

Elapsed: 00:00:03.66
14:04:22 fopst> show parameter spfile;

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
spfile string C:\ORACLE\PRODUCT\11.2.0\DBHOM
E_1\DATABASE\SPFILEFOPST.ORA
14:04:27 fopst>

When you did any mistake on your Oracle database such as drop tables or drop databases, you can recover it from the backup copy and archive log files with "RMAN restore until time script" as following;

RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down

RMAN> RUN
2> {
3> startup mount;
4> ALLOCATE CHANNEL c1_restore DEVICE TYPE DISK;
5>
6> set until time= "to_date('23/09/2015 17:10:00','dd/mm/yyyy hh24:mi:ss')";
7> RESTORE DATABASE;
8> RECOVER DATABASE;
9> RELEASE CHANNEL c1_restore;
10> }

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 17771253760 bytes

Fixed Size 2288480 bytes
Variable Size 6777996448 bytes
Database Buffers 10938744832 bytes
Redo Buffers 52224000 bytes

allocated channel: c1_restore
channel c1_restore: SID=263 device type=DISK

executing command: SET until clause

Starting restore at 23-SEP-15

channel c1_restore: starting datafile backup set restore
channel c1_restore: specifying datafile(s) to restore from backup set
channel c1_restore: restoring datafile 00001 to +DATA/limsprod/datafile/system.276.881074097
channel c1_restore: restoring datafile 00002 to +DATA/limsprod/datafile/sysaux.277.881074097
channel c1_restore: restoring datafile 00003 to +DATA/limsprod/datafile/undotbs1.278.881074097
channel c1_restore: restoring datafile 00004 to +DATA/limsprod/datafile/users.279.881074097
channel c1_restore: restoring datafile 00005 to +DATA/limsprod/datafile/lims_archive.286.881076053
channel c1_restore: restoring datafile 00006 to +DATA/limsprod/datafile/lims_base.287.881076073
channel c1_restore: restoring datafile 00007 to +DATA/limsprod/datafile/lims_common.288.881076097
channel c1_restore: restoring datafile 00008 to +DATA/limsprod/datafile/lims_data.289.881076443
channel c1_restore: restoring datafile 00009 to +DATA/limsprod/datafile/lims_data.290.881076535
channel c1_restore: restoring datafile 00010 to +DATA/limsprod/datafile/lims_data.291.881076625
channel c1_restore: restoring datafile 00011 to +DATA/limsprod/datafile/lims_data.292.881076865
channel c1_restore: restoring datafile 00012 to +DATA/limsprod/datafile/lims_data.293.881077055
channel c1_restore: restoring datafile 00013 to +DATA/limsprod/datafile/lims_data.294.881077235
channel c1_restore: restoring datafile 00014 to +DATA/limsprod/datafile/lims_data.295.881077657
channel c1_restore: restoring datafile 00015 to +DATA/limsprod/datafile/lims_data.296.881077735
channel c1_restore: restoring datafile 00016 to +DATA/limsprod/datafile/lims_data.299.881078031
channel c1_restore: restoring datafile 00017 to +DATA/limsprod/datafile/lims_index.300.881078289
channel c1_restore: restoring datafile 00018 to +DATA/limsprod/datafile/lims_index.301.881078439
channel c1_restore: restoring datafile 00019 to +DATA/limsprod/datafile/lims_index.302.881078505
channel c1_restore: restoring datafile 00020 to +DATA/limsprod/datafile/lims_index.303.881078609
channel c1_restore: restoring datafile 00021 to +DATA/limsprod/datafile/lims_index.304.881078723
channel c1_restore: restoring datafile 00022 to +DATA/limsprod/datafile/lims_index.306.881078811
channel c1_restore: restoring datafile 00023 to +DATA/limsprod/datafile/lims_index.308.881078901
channel c1_restore: restoring datafile 00024 to +DATA/limsprod/datafile/lims_index.314.881079053
channel c1_restore: restoring datafile 00025 to +DATA/limsprod/datafile/lims_index.316.881079155
channel c1_restore: restoring datafile 00026 to +DATA/limsprod/datafile/lims_index.324.881079755
channel c1_restore: restoring datafile 00027 to +DATA/limsprod/datafile/lims_index.327.881079851
channel c1_restore: restoring datafile 00028 to +DATA/limsprod/datafile/undotbs1.335.881080165
channel c1_restore: restoring datafile 00029 to +DATA/limsprod/datafile/sysaux.337.883641629
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_1_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_1_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 1
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_2_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_2_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 2
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_3_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_3_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 3
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_4_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_4_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 4
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_5_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_5_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 5
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_6_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_6_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 6
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_7_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_7_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 7
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_8_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_8_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 8
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_9_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_9_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 9
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_10_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_10_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 10
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_11_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_11_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 11
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_12_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_12_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 12
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_13_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_13_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 13
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_14_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_14_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 14
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_15_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_15_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 15
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_16_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_16_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 16
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_17_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_17_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 17
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_18_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_18_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 18
channel c1_restore: reading from backup piece V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_19_891188665
channel c1_restore: piece handle=V:\LIMSPROD\LIMSPROD_FULL_LIMSPROD_G6QHSTDP_19_891188665 tag=TAG20150923T162425
channel c1_restore: restored backup piece 19
channel c1_restore: restore complete, elapsed time: 00:31:19
Finished restore at 23-SEP-15

Starting recover at 23-SEP-15

starting media recovery
media recovery complete, elapsed time: 00:00:10

Finished recover at 23-SEP-15

released channel: c1_restore

RMAN>

PS H:\> sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 23 19:40:04 2015

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option

19:40:15 limsprod> alter database open resetlogs;

Database altered.

Elapsed: 00:00:20.51
19:40:50 limsprod>

The following log is from the alert log of the database while RMAN is recovering the database.

Wed Sep 23 19:06:32 2015
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process QMNC
Wed Sep 23 19:06:34 2015
Stopping background process CJQ0
Stopping background process MMNL
Stopping background process MMON
Wed Sep 23 19:06:43 2015
License high water mark = 111
All dispatchers and shared servers shutdown
alter database close
Wed Sep 23 19:06:49 2015
SMON: disabling tx recovery
SMON: disabling cache recovery
Wed Sep 23 19:06:52 2015
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 45
Shutting down archive processes
Archiving is disabled
Wed Sep 23 19:06:52 2015
ARCH shutting down
ARC2: Archival stopped
Wed Sep 23 19:06:52 2015
NOTE: Deferred communication with ASM instance
Wed Sep 23 19:06:52 2015
ARCH shutting down
Wed Sep 23 19:06:52 2015
ARCH shutting down
ARC0: Archival stopped
Wed Sep 23 19:06:52 2015
ARCH shutting down
ARC3: Archival stopped
ARC1: Archival stopped
Thread 1 closed at log sequence 2231
Successful close of redo thread 1
Wed Sep 23 19:06:55 2015
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 3
Wed Sep 23 19:06:59 2015
Completed: alter database close
alter database dismount
Shutting down archive processes
Archiving is disabled
Wed Sep 23 19:06:59 2015
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 2
Completed: alter database dismount
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Wed Sep 23 19:07:04 2015
NOTE: Database dismounted; ASMB process exiting
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Wed Sep 23 19:07:05 2015
NOTE: Shutting down MARK background process
Wed Sep 23 19:07:05 2015
Stopping background process VKTM
Wed Sep 23 19:07:10 2015
Instance shutdown complete
Wed Sep 23 19:07:53 2015
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 6
Number of processor cores in the system is 6
Number of processor sockets in the system is 2
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =85
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Release 11.2.0.4.0 - 64bit Production.
Windows NT Version V6.1 Service Pack 1
CPU : 6 - type 8664, 6 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:22985M/36863M, Ph+PgF:41931M/73725M
VM name : VMWare Version (6)
Using parameter settings in server-side pfile C:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITLIMSPROD.ORA
System parameters with non-default values:
processes = 500
sessions = 774
sga_max_size = 17024M
shared_pool_size = 6016M
java_pool_size = 64M
spfile = "+DATA/limsprod/spfilelimsprod.ora"
sga_target = 17024M
memory_target = 18048M
memory_max_target = 18048M
control_files = "+DATA/limsprod/controlfile/current.280.881074207"
db_block_size = 8192
compatible = "11.2.0.4.0"
log_archive_dest_1 = "LOCATION=Z:\oradata\limsprod\arch"
log_archive_format = "ARC%S_%R.%T"
db_create_file_dest = "+DATA"
db_recovery_file_dest = "Z:\oradata\limsprod\fast_recovery_area"
db_recovery_file_dest_size= 4182M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = "WESTERNFOREST.COM"
dispatchers = "(PROTOCOL=TCP) (SERVICE=LIMSPRODXDB)"
audit_file_dest = "C:\ORACLE\ADMIN\LIMSPROD\ADUMP"
audit_trail = "NONE"
db_name = "LIMSPROD"
open_cursors = 300
pga_aggregate_target = 1000M
optimizer_secure_view_merging= FALSE
diagnostic_dest = "C:\ORACLE"
Wed Sep 23 19:07:54 2015
PMON started with pid=2, OS id=4328
Wed Sep 23 19:07:54 2015
PSP0 started with pid=3, OS id=2820
Wed Sep 23 19:07:55 2015
VKTM started with pid=4, OS id=3084 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Wed Sep 23 19:07:55 2015
GEN0 started with pid=5, OS id=4556
Wed Sep 23 19:07:55 2015
DIAG started with pid=6, OS id=4856
Wed Sep 23 19:07:55 2015
DBRM started with pid=7, OS id=6032
Wed Sep 23 19:07:55 2015
DIA0 started with pid=8, OS id=6436
Wed Sep 23 19:07:55 2015
MMAN started with pid=9, OS id=3248
Wed Sep 23 19:07:55 2015
DBW0 started with pid=10, OS id=3896
Wed Sep 23 19:07:55 2015
LGWR started with pid=11, OS id=3584
Wed Sep 23 19:07:55 2015
CKPT started with pid=12, OS id=1280
Wed Sep 23 19:07:55 2015
SMON started with pid=13, OS id=6736
Wed Sep 23 19:07:55 2015
RECO started with pid=14, OS id=5536
Wed Sep 23 19:07:55 2015
RBAL started with pid=15, OS id=6316
Wed Sep 23 19:07:55 2015
ASMB started with pid=16, OS id=2604
Wed Sep 23 19:07:55 2015
MMON started with pid=17, OS id=4000
Wed Sep 23 19:07:55 2015
MMNL started with pid=18, OS id=4544
NOTE: initiating MARK startup
Starting background process MARK
Wed Sep 23 19:07:55 2015
MARK started with pid=19, OS id=6328
NOTE: MARK has subscribed
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = C:\Oracle
Wed Sep 23 19:07:57 2015
ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=10.102.248.172)(PORT=1521))' SCOPE=MEMORY SID='limsprod';
Wed Sep 23 19:07:58 2015
alter database mount
NOTE: Loaded library: System
SUCCESS: diskgroup DATA was mounted
NOTE: dependency between database LIMSPROD and diskgroup resource ora.DATA.dg is established
Successful mount of redo thread 1, with mount id 1278723982
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount
Wed Sep 23 19:18:29 2015
Full restore complete of datafile 3 +DATA/limsprod/datafile/undotbs1.278.881074097. Elapsed time: 0:10:21
checkpoint is 4298719146844
last deallocation scn is 3
Undo Optimization current scn is 4298719137466
Full restore complete of datafile 8 +DATA/limsprod/datafile/lims_data.289.881076443. Elapsed time: 0:10:23
checkpoint is 4298719146844
Full restore complete of datafile 9 +DATA/limsprod/datafile/lims_data.290.881076535. Elapsed time: 0:10:23
checkpoint is 4298719146844
Full restore complete of datafile 10 +DATA/limsprod/datafile/lims_data.291.881076625. Elapsed time: 0:10:27
checkpoint is 4298719146844
Full restore complete of datafile 11 +DATA/limsprod/datafile/lims_data.292.881076865. Elapsed time: 0:10:30
checkpoint is 4298719146844
Wed Sep 23 19:18:43 2015
Full restore complete of datafile 12 +DATA/limsprod/datafile/lims_data.293.881077055. Elapsed time: 0:10:35
checkpoint is 4298719146844
Full restore complete of datafile 13 +DATA/limsprod/datafile/lims_data.294.881077235. Elapsed time: 0:10:39
checkpoint is 4298719146844
Full restore complete of datafile 14 +DATA/limsprod/datafile/lims_data.295.881077657. Elapsed time: 0:10:43
checkpoint is 4298719146844
Wed Sep 23 19:22:59 2015
db_recovery_file_dest_size of 4182 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Sep 23 19:28:35 2015
Full restore complete of datafile 15 +DATA/limsprod/datafile/lims_data.296.881077735. Elapsed time: 0:10:06
checkpoint is 4298719146844
Full restore complete of datafile 16 +DATA/limsprod/datafile/lims_data.299.881078031. Elapsed time: 0:10:07
checkpoint is 4298719146844
Full restore complete of datafile 17 +DATA/limsprod/datafile/lims_index.300.881078289. Elapsed time: 0:10:07
checkpoint is 4298719146844
Wed Sep 23 19:28:48 2015
Full restore complete of datafile 18 +DATA/limsprod/datafile/lims_index.301.881078439. Elapsed time: 0:10:13
checkpoint is 4298719146844
Wed Sep 23 19:28:58 2015
Full restore complete of datafile 19 +DATA/limsprod/datafile/lims_index.302.881078505. Elapsed time: 0:10:20
checkpoint is 4298719146844
Full restore complete of datafile 20 +DATA/limsprod/datafile/lims_index.303.881078609. Elapsed time: 0:10:25
checkpoint is 4298719146844
Wed Sep 23 19:29:18 2015
Full restore complete of datafile 21 +DATA/limsprod/datafile/lims_index.304.881078723. Elapsed time: 0:10:31
checkpoint is 4298719146844
Full restore complete of datafile 22 +DATA/limsprod/datafile/lims_index.306.881078811. Elapsed time: 0:10:35
checkpoint is 4298719146844
Wed Sep 23 19:31:37 2015
Full restore complete of datafile 6 +DATA/limsprod/datafile/lims_base.287.881076073. Elapsed time: 0:02:11
checkpoint is 4298719146844
Wed Sep 23 19:33:39 2015
Full restore complete of datafile 7 +DATA/limsprod/datafile/lims_common.288.881076097. Elapsed time: 0:02:02
checkpoint is 4298719146844
Wed Sep 23 19:35:51 2015
Full restore complete of datafile 27 +DATA/limsprod/datafile/lims_index.327.881079851. Elapsed time: 0:02:12
checkpoint is 4298719146844
Wed Sep 23 19:37:06 2015
Full restore complete of datafile 1 +DATA/limsprod/datafile/system.276.881074097. Elapsed time: 0:01:15
checkpoint is 4298719146844
last deallocation scn is 895285
Undo Optimization current scn is 4298719142269
Full restore complete of datafile 4 +DATA/limsprod/datafile/users.279.881074097. Elapsed time: 0:00:03
checkpoint is 4298719146844
last deallocation scn is 3
Full restore complete of datafile 5 +DATA/limsprod/datafile/lims_archive.286.881076053. Elapsed time: 0:00:01
checkpoint is 4298719146844
Wed Sep 23 19:38:28 2015
Full restore complete of datafile 2 +DATA/limsprod/datafile/sysaux.277.881074097. Elapsed time: 0:09:20
checkpoint is 4298719146844
last deallocation scn is 896771
Full restore complete of datafile 29 +DATA/limsprod/datafile/sysaux.337.883641629. Elapsed time: 0:09:10
checkpoint is 4298719146844
Wed Sep 23 19:38:57 2015
Full restore complete of datafile 23 +DATA/limsprod/datafile/lims_index.308.881078901. Elapsed time: 0:10:22
checkpoint is 4298719146844
Full restore complete of datafile 24 +DATA/limsprod/datafile/lims_index.314.881079053. Elapsed time: 0:10:19
checkpoint is 4298719146844
Full restore complete of datafile 25 +DATA/limsprod/datafile/lims_index.316.881079155. Elapsed time: 0:10:19
checkpoint is 4298719146844
Full restore complete of datafile 26 +DATA/limsprod/datafile/lims_index.324.881079755. Elapsed time: 0:10:09
checkpoint is 4298719146844
Wed Sep 23 19:39:27 2015
Full restore complete of datafile 28 +DATA/limsprod/datafile/undotbs1.335.881080165. Elapsed time: 0:10:29
checkpoint is 4298719146844
Undo Optimization current scn is 4298719142501
Wed Sep 23 19:39:29 2015
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 , 15 , 16 , 17 , 18 , 19 , 20
alter database recover datafile list
21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29
Completed: alter database recover datafile list
21 , 22 , 23 , 24 , 25 , 26 , 27 , 28 , 29
alter database recover if needed
start until time 'SEP 23 2015 17:10:00'
Media Recovery Start
started logmerger process
Parallel Media Recovery started with 6 slaves
Wed Sep 23 19:39:30 2015
Recovery of Online Redo Log: Thread 1 Group 3 Seq 2229 Reading mem 0
Mem# 0: +DATA/limsprod/onlinelog/group_3.283.881074219
Incomplete Recovery applied until change 4298719150809 time 09/23/2015 17:10:00
Completed: alter database recover if needed
start until time 'SEP 23 2015 17:10:00'
Wed Sep 23 19:40:14 2015
alter database open
Errors in file C:\ORACLE\diag\rdbms\limsprod\limsprod\trace\limsprod_ora_5844.trc:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
ORA-1589 signalled during: alter database open...
Wed Sep 23 19:40:30 2015
alter database open resetlogs
RESETLOGS after incomplete recovery UNTIL CHANGE 4298719150809
Archived Log entry 2223 added for thread 1 sequence 2231 ID 0x4b9dee9c dest 1:
Resetting resetlogs activation ID 1268641436 (0x4b9dee9c)
Deleted Oracle managed file +DATA/limsprod/onlinelog/group_1.281.881074211
Deleted Oracle managed file +DATA/limsprod/onlinelog/group_2.282.881074215
Deleted Oracle managed file +DATA/limsprod/onlinelog/group_3.283.881074219
Wed Sep 23 19:40:38 2015
Setting recovery target incarnation to 3
Wed Sep 23 19:40:38 2015
Assigning activation ID 1278723982 (0x4c37c78e)
LGWR: STARTING ARCH PROCESSES
Wed Sep 23 19:40:38 2015
ARC0 started with pid=28, OS id=7032
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Wed Sep 23 19:40:39 2015
ARC1 started with pid=29, OS id=3480
Wed Sep 23 19:40:39 2015
ARC2 started with pid=30, OS id=6808
Wed Sep 23 19:40:39 2015
ARC3 started with pid=31, OS id=5348
ARC1: Archival started
ARC2: Archival started
Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: +DATA/limsprod/onlinelog/group_1.281.891200433
Current log# 1 seq# 1 mem# 1: Z:\ORADATA\LIMSPROD\FAST_RECOVERY_AREA\LIMSPROD\ONLINELOG\O1_MF_1_C06RO13Z_.LOG
Successful open of redo thread 1
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Wed Sep 23 19:40:40 2015
SMON: enabling cache recovery
ARC3: Archival started
Wed Sep 23 19:40:41 2015
[5844] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:910193872 end:910194371 diff:499 (4 seconds)
ARC0: STARTING ARCH PROCESSES COMPLETE
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Sep 23 19:40:44 2015
QMNC started with pid=32, OS id=4444
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open resetlogs
Wed Sep 23 19:40:51 2015
Starting background process CJQ0
Wed Sep 23 19:40:51 2015
CJQ0 started with pid=33, OS id=4520

Execution of Hash Join

Explanation

(1) 개요

JOIN 의 종류는 3가지로 나뉘는데, Sort merge join, Nested loop join, Hash join 이다. 이중 Hash Join (HJ) 은 7.3 부터 사용가능하며 그 주요 기능을 살펴보면
- index 가 여러 level 의 depth 를 가질 때 Sort Merge Join (SMJ) 이나 Nested Loops (NL)보다 좋은 효과를 낸다.
- sort 를 하지 않으므로 SMJ 보다 좋은 성능을 내며, 작은 table 과 큰 table 의 join 시에 유리하다.
- 주의해야 할 것은 hash join 은 equi join 에서만 가능하다는 것이다.
- HJ 은 driving table 에 index 를 필요로 하지 않는다.
- SMJ 나 NL 보다 효율적인데, 이는 SMJ 가 merge 단계에 들어가기 위해 양쪽 table 이 모두 sort 되어야 하기 때문이다. 또 NL은 driving table이 많은 row 의 data 를 갖는 경우 비효율적이서 inner table 을 여러번 probe(탐색)하게 한다. 이에 반해 HJ는 각 table 에 대해 1 번만 pass 한다.

(2) Cost의 비교

편의상 join 되는 sql 문이 다음과 같다고 가정하자.
SELECT S.a, B.a FROM S,B WHERE S.a = B.a
S는 small table 이고, B는 big table 이다.
(* analyze 를 수행하면 CBO 는 이미 S가 out table 이고 B 가 inner table 이며 , S 가 driving table 임을 인식한다. )
NLJ 는 S table 의 모든 row 에 대해 a column 을 B table 의 모든 column 을 match 하기 때문에 rS* rB key 비교가 요구된다.:
Cost(NLJ) 는 Read(S) + [ rS *Read(B) ] 에 비례
또 SMJ 는 S 와 B 를 memory 에 읽어와 join key 를 각각 sort 하고, join 을 수행하므로 cost 는

Cost(SMJ) 는 Read(S) + Write(SortRuns(S))
+ Read(B) + Write(SortRuns(B))
+ Merge(S,B)
+ CPUSortCost(S + B) 에 비례한다.

memory 에서 수행되는 HJ 의 algorithm 은 아래에서 설명된어 지는데 이의 cost 를 미리 check 해 보자면

Cost(HJ) = Read(S) + Build Hash Table in Memory (cpu)
+ Read(B) + Perform In memory Join(cpu)

이 경우 CPU costs를 무시하면, Cost(HJ)는 Read(S) + Read(B) 에 비례한다고 할 수 있다.

(3) Hash join 을 수행하기 위해 Oracle 은 다음의 과정을 거친다.:

이를 수행하기 위해 partition 단계와 join 단계를 거치며 이의 algorithm 을 grace join 이라 한다. 이의 한계는 join value 의 분배가 한쪽으로 치우침이 없이 partition 에 고르게 분포되어야 한다는 것이다. 이 algorithm 은 다음과 같다.

1. partiton 갯수를 결정한다.이를 fan out 이라한다.

high fan out 은 여러개의 작은 partition 을 만들어 i/o 의 효율을 떨어뜨리며,low fan out 은 커다란 partition 을 만들어 hash memory 의 hit율을 떨어뜨린다 . 그러므로 이를 적절히 가져가는 것이 performance 의 주 요점이며(이는 bit map 갯수를 결정) 이의 효율을 높이기 위해 hash area size를 늘리고, hash multi block io 를 줄인다.

2. driving table 을 결정한다.(작은 table 로 결정)

3. small table 의 필요 column 을 읽어들여 hash area 의 partition 에 분배하는데 이는 join column 으로 hash function1을 통과 시키면서 partition 에 hash function2 의 hash value 와 함께 분배한다. 이때 bitmap vector 를 만든다. 이 bitmap 은 2차원 bucket 인데 hash function 1 과 2 를 통과시켜 만든다.즉 partition 이 100 개라면 100* 100 의 10000 개의 cell 로 이루어진다.

4. 각 row 에 대해 bitmap 의 (a,b) 에 marking 을 한다.

5 위의 step 이 모두 끝나면 driving table 이 아닌 큰 table 을 읽어들여 function1,2 를 통과한다. 이때 나온 hash value 를 driving table 이 만들어 놓은 bitmap 과 대조하여 1 이면 join 을 해야 하는 column 으로 인식하고 아니면 join할 필요가 없는 row 이므로 버린다. 이를 bit vector filtering 이라한다. 이때 hash table 을 구성하기 위해 항상 full table 을 scan 하는 것은 아니다. 먼저 where 조건의 index 를 타서 조건에 맞게 row 를 걸러낸 다음 그 결과에 대해 hash table 을 구성한다. 또 hash array size 가 크면 문제가 안되는데, 작으면 disk 의 temp segment 에 내려 보내야 하므로 problem 이 발생한다.

6. B 의 joined value 를 hash function 1 을 통과시켜 이 row 가 bit vector에 있고, memory 위의 partition 에 있으면 join 이 수행되고 결과가 return 된다. memory 에 있지 않으면 disk 에 있는 적절한 S partition 에 씌여진다. 7. 1번째 B 가 pass 된후 S 의 수행되지 않는 partition 들이 최대한 temp segment 에서 memory 로 올려지고 hash table 이 생성된다. 그리고 B 의 partition 이 다시 읽혀져 memory join 이 실행된다. 즉 수행되지 않는 disk 의 partition (S,B) 이 읽혀진다.

(4) parameter

-HASH_JOIN_ENABLED : true 로 지정시 사용가능
-HASH_AREA_SIZE : sort_area_size 의 2배가 기본
-HASH_MULTIBLOCK_IO_COUNT : DB_BLOCK_READ_COUNT 가 기본
-USE_HASH : hint

(5) partition 갯수 결정

첫번째로 우리는 partition (bucket) 의 갯수를 결정해야 한다. 여기에 우리는 hashed row 를 넣을 것이다. 이는 hash_area_size, db_block_size and ash_multiblock_io_count parameters에 의해 결정된다. 또 이 값은 20% 정도의 overhead 를 고려해야 한다.
- storing partitions, the bitmap of unique left input and the hash table

함수 : Partitions 갯수 = (0.8 x hash_area_size) / (db_block_size x hash_multiblock_io_count)

row 가 가장 작은 table 이 읽혀지고 (R 이라고 부르자) , 각 row 는 hash algorithm 을 따른다. 각 row 를 bucket 에 골고루 펼쳐지게 하기 위해 2가지의 algorithm을 따른다. hash 되는 row 가 partition 에 골고루 분산되기 위해 1 번째 hash function 을 따르며, 2 번째 hash value 는 다음 hash 되는 경우를 위해 row 와 함께 저장된다. 이와 동시에 두가지의 hash value 를 이용한 bitmap 이 만들어진다.

(6) Bitmap building 예제 :

Hash
Algorithm 1 ->
1 2 3 4
1 0 0 0 0
Second
Hash 2 0 0 0 0 ------>
Algorithm
| 3 0 0 0 0
V
4 0 0 0 0

driving table 은 hash function 1, 2 를 통과하여 bitmap 을 만든다. 만일 hash area 가 모두 차면 가장 큰 partition 이 disk 로 내려간다. disk 의 partition 은 partition 에 할당되는 row 에 의해 disk 에서 update 되어진다. 만일 hash area 의 부족으로 1 partition 만이 memeory에 올라간다면 나머지 partition 은 모두 disk 에 놓여지게 된다. 이런 경우는 생기지 않도록 조심하여야 한다.
이 작업이 R table 의 모든 row 에 대해 행해진다. 이 작업시 가능한 모든 partition 이 memeory 에 위치하도록 해야 한다.
이 작업이후 B table 을 읽어들인다.이도 역시 hash function 을 통과시켜 hash value 가 memory 에 있는 partition 을 hit 하는지 check 한다.

만일 그러면 이 row 는 joined row 로 반환한다. 만일 아니면 해당 row 를 새로운 partiion 에 write 한다. 이때 S 와 같은 hash function 을 사용하며 이의 의미는 S와 B 의 같은 value는 같은 partition number 를 갖게 하기 위함이다.

(7) unique join keys 의 bitmap

bitmap 은 partition 에 들어있는 value 의 flag 이라 할수 있다. 이는 S 의 row 가 disk 의 partititon 에 씌이기 전에 생성되어 진다.

Making Whole Database Backups with RMAN

RMAN> BACKUP DATABASE; # Uses automatic channels to make backup

RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT'; # Switches logs and archives all logs

Backing up Individual Tablespaces with RMAN

RMAN> BACKUP DEVICE TYPE sbt MAXSETSIZE = 10M TABLESPACE users, tools;

Backing Up Individual Datafiles and Datafile Copies with RMAN

RMAN> BACKUP DEVICE TYPE sbt DATAFILE 1,2,3,4 DATAFILECOPY '/tmp/system01.dbf';

Backing Up Datafile Copies

RMAN> BACKUP DEVICE TYPE sbt DATAFILECOPY '/tmp/system01.dbf';

Including the Current Control File in a Backup of Other Files

RMAN> BACKUP DEVICE TYPE sbt TABLESPACE users INCLUDE CURRENT CONTROLFILE;

Backing Up the Current Control File Manually

RMAN> BACKUP CURRENT CONTROLFILE TAG = mondaypmbackup;

Backing Up a Control File Copy

RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/tmp/control01.ctl';
RMAN> BACKUP DEVICE TYPE sbt CONTROLFILECOPY '/tmp/control01.ctl';

Backing Up Server Parameter Files with RMAN

RMAN> BACKUP DEVICE TYPE sbt SPFILE;

Backing Up Archived Redo Logs with RMAN

RMAN> BACKUP ARCHIVELOG ALL;

RMAN> BACKUP ARCHIVELOG FROM TIME 'SYSDATE-30' UNTIL TIME 'SYSDATE-7';

RMAN> BACKUP ARCHIVELOG FROM TIME "to_date('20161121 124000','YYYYMMDD HH24MISS')" UNTIL TIME "to_date('20161121 140000','YYYYMMDD HH24MISS')" DELETE INPUT TAG 'ARCHIVELOG_LOG_BACKUP';

How to check FK (foreign key) constraint using query in Oracle database

Example 1)

deptno column on dept table is referenced by deptno pk column on emp table.

### dept(pk : deptno) -> emp(fk : deptno)

SQL> alter table dept add constraints pk_deptno primary key (deptno);

Table altered.
Elapsed: 00:00:00.40

SQL> alter table emp add constraints fk_deptno
foreign key (deptno)
references dept
/

fk_const_to.sql

DOC
file: fk_const.sql
input : child
output : parent
EX) dept(pk : deptno) -> emp(fk : deptno)
if the input is emp, then the output is dept
#

set line 132
col owner format a10 heading 'PK owner'
column table_name format a15 heading 'PK table'
column const_name format a15 heading 'PK const'
column referencing_table format a25 heading 'Ref Table'
column referencing_table format a15 heading 'FK Table'
column foreign_const format a15 heading 'FK Const'
column fk_status format a8 heading 'Status'
column key_column format a8 heading 'Key Col'
column fk_status format a8
column validated format a8 trunc
column DEFERRED format a8 trunc
column DEFERRABLE format a8 trunc

select a.owner,
a.table_name,
a.constraint_name const_name,
b.table_name referencing_table,
b.constraint_name foreign_const,
c.column_name key_column,
b.status fk_status,
b.validated ,
b.deferred,
b.deferrable
from dba_constraints a, dba_constraints b, all_cons_columns c
where
a.owner=b.owner and a.owner=c.owner
and a.constraint_name= c.constraint_name
and a.constraint_name = b.r_constraint_name
and b.table_name = upper('&table_name')
and b.constraint_type = 'R'
and a.owner not in ('SYS','SYSTEM','OUTLN','PERFSTAT')
order by 1,2,3,4
/

clear columns

Result :

Enter value for table_name: emp
old 16: and b.table_name = upper('&table_name')
new 16: and b.table_name = upper('emp')
PK owner PK table PK const FK Table FK Const Key Col Status VALIDATE DEFERRED DEFERRAB
---------- --------------- --------------- --------------- --------------- -------- -------- -------- -------- --------
SCOTT DEPT PK_DEPTNO EMP FK_DEPTNO DEPTNO ENABLED VALIDATE IMMEDIAT NOT DEFE

fk_const_from.sql

DOC
file: fk_const_from.sql
input : parent
output : child
dept(pk : deptno) -> emp(fk : deptno)
if the input is dept, then the output is emp
#

set line 132
col owner format a10 heading 'PK owner'
column table_name format a15 heading 'PK table'
column const_name format a15 heading 'PK const'
column referencing_table format a25 heading 'Ref Table'
column referencing_table format a15 heading 'FK Table'
column foreign_const format a15 heading 'FK Const'
column fk_status format a8 heading 'Status'
column key_column format a8 heading 'Key Col'
column fk_status format a8
column validated format a8 trunc
column DEFERRED format a8 trunc
column DEFERRABLE format a8 trunc

select a.owner,
a.table_name,
a.constraint_name const_name,
b.table_name referencing_table,
b.constraint_name foreign_const,
c.column_name key_column,
b.status fk_status,
b.validated,
b.deferred,
b.deferrable
from dba_constraints a, dba_constraints b, all_cons_columns c
where
a.owner=b.owner and a.owner=c.owner
and a.constraint_name= c.constraint_name
and a.constraint_name = b.r_constraint_name
and a.table_name = upper('&table_name')
and b.constraint_type = 'R'
and a.owner not in ('SYS','SYSTEM','OUTLN','PERFSTAT')
order by 1,2,3,4
/

clear columns

Enter value for table_name: dept
old 16: and a.table_name = upper('&table_name')
new 16: and a.table_name = upper('dept')
PK owner PK table PK const FK Table FK Const Key Col Status VALIDATE DEFERRED DEFERRAB
---------- --------------- --------------- --------------- --------------- -------- -------- -------- -------- --------
SCOTT DEPT PK_DEPTNO EMP FK_DEPTNO DEPTNO ENABLED VALIDATE IMMEDIAT NOT DEFE

These days I'm digging into how to collect and analyze wait events on SQL Server database. So I simply have created a stored procedure to collect wait event information and have set it on SQL Agent job to run every 5 or 10 minutes. Then using same procedure with different parameter, I was able to get the analyzed wait event information. Also I reviewed Paul S. Randal's post (http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/) for this stored procedure. Thanks Paul.

After collecting wait event information, I can get meaningful data to troubleshoot database issues from here.

Here is the procedure code. Please let me know if you have any comment on this.

/*dba_WaitsCollecting_V1 =====================================================
  File:     dba_WaitsCollecting.sql
 
  Summary:  Collect Wait stats and session information
 
  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  1/17/2017 : Written by Steve Lim
------------------------------------------------------------------------------
  Usage : -- Collecting information
          [dbo].[dba_WaitsCollecting]  
    -- Analyzing information
          [dbo].[dba_WaitsCollecting] 0, '2017-01-17 09:00:00', '2017-01-18 17:00:00'
============================================================================*/
CREATE PROCEDURE [dbo].[dba_WaitsCollecting]
  @isCollect bit = 1 /* default : 1 (1: collect, 0: analyze) */
  , @beginTime datetime = NULL /* default : getdate() - 1 */
  , @endTime   datetime = NULL /* default : getdate() */
AS

Set NoCount On;
Set XACT_Abort On;
Set Quoted_Identifier On;

IF ISNULL(OBJECT_ID('dba_WaitTasksCollected'),0) = 0 BEGIN
 CREATE TABLE [dbo].[dba_WaitTasksCollected](
        [Num]       [int] IDENTITY(1,1) NOT NULL,
        [TimeCollected]          [nvarchar](24) DEFAULT(CONVERT(VARCHAR, GETDATE(), 112) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(HOUR,GETDATE())),2) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(MINUTE,GETDATE())),2)),
  [SPID]      [smallint] NULL,
  [Thread]    [int]      NULL,
  [Scheduler] [int]      NULL,
  [Wait_ms]   [bigint]   NULL,
  [Wait_type] [nvarchar](60) NULL,
  [Blocking_SPID] [smallint] NULL,
  [Resource_description] [nvarchar](3072) NULL,
  [Node_ID]   [nvarchar](3072) NULL,
  [DOP]       [smallint] NULL,
  [DBID]      [smallint] NULL,
  [Help_Info_URL] [XML] NULL,
  [Query_plan]    [XML] NULL,
  [Text]      [nvarchar](max) NULL,
  CONSTRAINT [PK_WaitTasksCollected] PRIMARY KEY CLUSTERED 
 (
  [Num] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
END

IF ISNULL(OBJECT_ID('dba_WaitsCollected'),0) = 0 BEGIN
 CREATE TABLE [dbo].[dba_WaitsCollected](
        [Num]                    [int] IDENTITY(1,1) NOT NULL,
        [TimeCollected]          [nvarchar](24) DEFAULT(CONVERT(VARCHAR, GETDATE(), 112) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(HOUR,GETDATE())),2) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(MINUTE,GETDATE())),2)),
  [wait_type]              [nvarchar](60) NOT NULL,
  [waiting_tasks_count]    [bigint]       NOT NULL,
  [wait_time_ms]           [bigint]       NOT NULL,
  [max_wait_time_ms]       [bigint]       NOT NULL,
  [signal_wait_time_ms]    [bigint]       NOT NULL,
  CONSTRAINT [PK_WaitsCollected] PRIMARY KEY CLUSTERED 
 (
  [Num] ASC
 )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
 ) ON [PRIMARY]
END

BEGIN

IF @isCollect = 1  -- COLLECT
BEGIN

 INSERT INTO dba_WaitTasksCollected ([SPID],[Thread],[Scheduler],[Wait_ms],[Wait_type],[Blocking_SPID]
                ,[Resource_description],[Node_ID],[DOP],[DBID],[Help_Info_URL],[Query_plan],[Text])
 SELECT 
  [owt].[session_id] AS [SPID],
  [owt].[exec_context_id] AS [Thread],
  [ot].[scheduler_id] AS [Scheduler],
  [owt].[wait_duration_ms] AS [Wait_ms],
  [owt].[Wait_type],
  [owt].[blocking_session_id] AS [Blocking_SPID],
  [owt].[Resource_description],
  CASE [owt].[Wait_type]
   WHEN N'CXPACKET' THEN
    RIGHT ([owt].[Resource_description],
     CHARINDEX (N'=', REVERSE ([owt].[Resource_description])) - 1)
   ELSE NULL
  END AS [Node_ID],
  [eqmg].[dop] AS [DOP],
  [er].[database_id] AS [DBID],
  CAST ('https://www.sqlskills.com/help/waits/' + [owt].[wait_type] as XML) AS [Help_Info_URL],
  [eqp].[Query_plan],
  [est].text as [Text]
 FROM sys.dm_os_waiting_tasks [owt]
 INNER JOIN sys.dm_os_tasks [ot] ON
  [owt].[waiting_task_address] = [ot].[task_address]
 INNER JOIN sys.dm_exec_sessions [es] ON
  [owt].[session_id] = [es].[session_id]
 INNER JOIN sys.dm_exec_requests [er] ON
  [es].[session_id] = [er].[session_id]
 FULL JOIN sys.dm_exec_query_memory_grants [eqmg] ON
  [owt].[session_id] = [eqmg].[session_id]
 OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
 OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
 WHERE
  [es].[is_user_process] = 1
 ORDER BY
  [owt].[session_id],
  [owt].[exec_context_id];


 INSERT INTO dba_WaitsCollected ([wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms])
 SELECT [wait_type],[waiting_tasks_count],[wait_time_ms],[max_wait_time_ms],[signal_wait_time_ms] FROM sys.dm_os_wait_stats;

END

IF @isCollect = 0 -- ANALYZE
BEGIN

 CREATE TABLE #dba_WaitsDelta (
  wait_type  nvarchar(60) NULL,
  WaitS      bigint NULL,
  ResourceS  bigint NULL,
  SignalS    bigint NULL,
  WaitCount  bigint NULL,
  Percentage int    NULL,
  RowNum     smallint NULL,
  TimeAnalyzed nvarchar(80) NULL
 )

 DECLARE @beginSnap NVARCHAR(24), @endSnap NVARCHAR(24);
 DECLARE @beginTSnap NVARCHAR(24), @endTSnap NVARCHAR(24);
-- DECLARE @beginTime datetime, @endTime datetime;

 IF @beginTime is NULL SELECT @beginTime = getdate() - 1;
 IF @endTime is NULL   SELECT @endTime = getdate();

 SELECT @beginTSnap = CONVERT(VARCHAR, @beginTime, 112) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(HOUR,@beginTime)),2) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(MINUTE,@beginTime)),2)
 SELECT @endTSnap = CONVERT(VARCHAR, @endTime, 112) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(HOUR,@endTime)),2) + RIGHT('00' + CONVERT(VARCHAR,DATEPART(MINUTE,@endTime)),2)

 SELECT @beginSnap = CONVERT(VARCHAR,MIN(TimeCollected)), @endSnap = CONVERT(VARCHAR,MAX(TimeCollected)) 
 FROM   [dbo].[dba_WaitsCollected]
 WHERE  TimeCollected between @beginTSnap and @endTSnap;

 INSERT INTO #dba_WaitsDelta (wait_type, WaitS, ResourceS, SignalS, WaitCount, Percentage, RowNum, TimeAnalyzed)
 SELECT ows1.wait_type as [wait_type]
       , (ows2.wait_time_ms - ows1.wait_time_ms) / 1000.0 as [WaitS]
    , ((ows2.wait_time_ms - ows1.wait_time_ms) - (ows2.signal_wait_time_ms - ows1.signal_wait_time_ms)) / 1000.0 as [ResourceS]
    , (ows2.signal_wait_time_ms - ows1.signal_wait_time_ms) / 1000.0 as [SignalS]
       , (ows2.waiting_tasks_count - ows1.waiting_tasks_count) as [WaitCount]
       , 100.0 * (ows2.wait_time_ms - ows1.wait_time_ms) / SUM (ows2.wait_time_ms - ows1.wait_time_ms) OVER() as [Percentage]
    --, (ows2.signal_wait_time_ms - ows1.signal_wait_time_ms) as [signal_wait_time_ms]
    , ROW_NUMBER() OVER(ORDER BY (ows2.wait_time_ms - ows1.wait_time_ms) DESC) as [RowNum]
    , ows1.TimeCollected + '-' + ows2.TimeCollected as TimeAnalyzed
 FROM 
  (select ows.wait_type, ows.waiting_tasks_count, ows.wait_time_ms, ows.signal_wait_time_ms, ows.TimeCollected
   from   [dbo].[dba_WaitsCollected]  ows
   where  ows.TimeCollected = (@beginSnap)
  ) ows1 inner join (
   select ows.wait_type, ows.waiting_tasks_count, ows.wait_time_ms, ows.signal_wait_time_ms, ows.TimeCollected
   from   [dbo].[dba_WaitsCollected]  ows
   where  ows.TimeCollected in (@endSnap)
  ) ows2 on ows1.wait_type = ows2.wait_type
 WHERE ows1.wait_type NOT IN (
   N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
   N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
   N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
   N'CHKPT', N'CLR_AUTO_EVENT',
   N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',

   -- Maybe uncomment these four if you have mirroring issues
   N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
   N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
 
   N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
   N'EXECSYNC', N'FSAGENT',
   N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
  
   -- Maybe uncomment these six if you have AG issues
   N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
   N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
   N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',

   N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
   N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
   N'ONDEMAND_TASK_QUEUE',
   N'PREEMPTIVE_XE_GETTARGETSTATE',
   N'PWAIT_ALL_COMPONENTS_INITIALIZED',
   N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
   N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
   N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEE', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
   N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
   N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
   N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
   N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
   N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
   N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
   N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
   N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
   N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
   N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
   N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
   N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
   N'WAIT_XTP_RECOVERY',
   N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
   N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
   N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
 AND (ows2.waiting_tasks_count - ows1.waiting_tasks_count) > 0;

 SELECT
  MAX ([W1].[wait_type]) AS [WaitType],
  CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
  CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
  CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
  MAX ([W1].[WaitCount]) AS [WaitCount],
  CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
  CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
  CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
  CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
  CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL],
  MAX(W1.TimeAnalyzed) as TimeAnalyzed
 FROM #dba_WaitsDelta AS [W1]
 INNER JOIN #dba_WaitsDelta AS [W2]
  ON [W2].[RowNum] <= [W1].[RowNum]
 GROUP BY [W1].[RowNum]
 HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 97; -- percentage threshold

 DROP TABLE #dba_WaitsDelta;

END

END

How to automate backups of SSAS cubes!

This article provides how to automate backups of SSAS cubes using Windows PowerShell.

1. Create text files and scipts on C:\Scripts\

a. CubeList.txt

EnterpriseDW
TrackingDW
ETLDW

b. Backup_SSAS.ps1

$ServerName=”localhost”
$loadInfo = [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) | Out-Null
## Add the AMO namespace
[Microsoft.AnalysisServices.BackupInfo]$serverBackup = New-Object ([Microsoft.AnalysisServices.BackupInfo])
[Microsoft.AnalysisServices.Server]$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($ServerName)
If ($server.name -eq $null)
{
Write-Output (“Server ‘{0}’ not found” -f $ServerName)
break
}
$DBList = Get-Content “c:\Scripts\CubeList.txt”
Foreach($DBName in $DBList)
{
$DB = $server.Databases.FindByName($DBName)
if ($DB -eq $null)
{
Write-Output (“Database ‘{0}’ not found” -f $DBName)
}
else
{
Write-Output(“—————————————————————-“)
Write-Output(“Server : {0}” -f $Server.Name)
Write-Output(“Database: {0}” -f $DB.Name)
Write-Output(“DB State: {0}” -f $DB.State)
Write-Output(“DB Size : {0}MB” -f ($DB.EstimatedSize/1024/1024).ToString(“#,##0″))
Write-Output(“—————————————————————-“)
#$BackupDestination=$server.ServerProperties.Item(“BackupDir”).value
$BackupDestination=”\\backup\dbbackup\SQL_Server\SSAS\” + $DB.Name
$serverBackup.AllowOverwrite = 1
$serverBackup.ApplyCompression = 1
$serverBackup.BackupRemotePartitions = 1
if (-not $backupDestination.EndsWith(“\”))
{
$backupDestination += “\”
}
[string]$backupTS = Get-Date -Format “yyyyMMddHHmm”
$serverBackup.file = $backupDestination + $db.name + “_” + $backupTS + “.abf”
$serverBackup.file
$db.Backup($serverBackup)
if ($?) {“Successfully backed up ” + $db.Name + ” to ” + $serverBackup.File }
else {“Failed to back up ” + $db.Name + ” to ” + $serverBackup.File }
}
}
$server.Disconnect()

c. Bacup_SSAS_main.ps1

[string]$backupTS = Get-Date -Format “yyyyMMddHHmm”
powershell -executionpolicy bypass -file C:\Scripts\Backup_SSAS.ps1 > C:\Scripts\Backup_SSAS_Logs\Bacup_SSAS_$backupTS.log

2. Create backup log directory

: C:\Scripts\Backup_SSAS_Logs\

3. Create a job in SQL Server database

Type : Operating system(CmdExec)

Run as : SQL Server Agent Service Account

powershell -executionpolicy bypass -file C:\Scripts\Backup_SSAS_main.ps1

4. Backup log files look like this;

———————-
Server : ABIP01
Database: EnterpriseDW
DB State: Processed
DB Size : 32MB
———————-
\\backup\dbbackup\SQL_Server\SSAS\EnterpriseDW\EnterpriseDW_201508042052.abf
Successfully backed up EnterpriseDWP to \\backup\dbbackup\SQL_Server\SSAS\EnterpriseDW\EnterpriseDW_201508042052.abf
———————-
Server : ABIP01
Database: TrackingDW
DB State: Processed
DB Size : 14MB
———————-
\\backup\dbbackup\SQL_Server\SSAS\TrackingDW\TrackingDW_201508042052.abf
Successfully backed up ETL to \\backup\dbbackup\SQL_Server\SSAS\TrackingDW\TrackingDW_201508042052.abf