DBA Blogs

I have to import the specific tables using DBMS_DATAPUMP PACKAGE . How to exclude the unwanted tables ?

Tom Kyte - Mon, 2017-03-13 04:46
Hi Tom , I have used the below import par file to exclude the tables those names are with $ DIRECTORY=FEB09_E DUMPFILE= MXPRD_01feb09_%U.dmp LOGFILE=IMP_EDRPRE.log REMAP_SCHEMA=MXPRD:MAXIMO remap_tablespace=MXPRD_DATA:MAXIMO_DATA,MXPRD_INDE...
Categories: DBA Blogs

SQLcl: Make NULLs “visible”

Galo Balda's Blog - Sun, 2017-03-12 21:40

One of the nice features in SQL Developer is to be able to define a value to replace NULLs (for display purpose only) in the data grids. Jeff Smith has a blog post about it in case you are interested.

You can achieve something similar in your SQLcl session by using set null some_string.

And here is  the output from a query after setting the system variable.

 


Filed under: Oracle, SQLcl Tagged: Oracle, SQLcl
Categories: DBA Blogs

12cR1 RAC Posts -- 8b : DUPLICATE DATABASE FOR STANDBY

Hemant K Chitale - Sun, 2017-03-12 10:27
Continuing the previous post, where I have done the pre-setup...
(A reminder : This is a SingleInstance Standby on FileSystem for a RAC Database with a PDB on ASM)


On the SingleInstance Node (for the Standby database)

Precreate the required folders for the database (and redo and control) files (I later realised that the "onlinelog" folders for the SEED and PDB are not required)

[oracle@oem132 ~]$ cd /u01/app/oracle/oradata
[oracle@oem132 oradata]$ mkdir STBY
[oracle@oem132 oradata]$ cd STBY
[oracle@oem132 STBY]$ mkdir datafile
[oracle@oem132 STBY]$ mkdir onlinelog
[oracle@oem132 STBY]$ mkdir 44bbc69ce8f552aee053334ea8c07365
[oracle@oem132 STBY]$ mkdir fd9ac20f64d244d7e043b6a9e80a2f2f
[oracle@oem132 STBY]$ cd 44*
[oracle@oem132 44bbc69ce8f552aee053334ea8c07365]$ mkdir datafile tempfile
[oracle@oem132 44bbc69ce8f552aee053334ea8c07365]$ cd ../fd*
[oracle@oem132 fd9ac20f64d244d7e043b6a9e80a2f2f]$ mkdir datafile tempfile
[oracle@oem132 fd9ac20f64d244d7e043b6a9e80a2f2f]$
[oracle@oem132 STBY]$
[oracle@oem132 STBY]$ cd /u01/app/oracle/fast_recovery_area
[oracle@oem132 fast_recovery_area]$ mkdir STBY
[oracle@oem132 fast_recovery_area]$ cd STBY
[oracle@oem132 STBY]$ mkdir onlinelog
[oracle@oem132 STBY]$ mkdir 44bbc69ce8f552aee053334ea8c07365
[oracle@oem132 STBY]$ mkdir fd9ac20f64d244d7e043b6a9e80a2f2f
[oracle@oem132 STBY]$ cd 44*
[oracle@oem132 44bbc69ce8f552aee053334ea8c07365]$ mkdir onlinelog
[oracle@oem132 44bbc69ce8f552aee053334ea8c07365]$ cd ../fd*
[oracle@oem132 fd9ac20f64d244d7e043b6a9e80a2f2f]$ mkdir onlinelog
[oracle@oem132 fd9ac20f64d244d7e043b6a9e80a2f2f]$
[oracle@oem132 STBY]$

Note that the names of the two folders for the SEED and the PDB are in *lower case*.

Next start the listener for STBY.

[oracle@oem132 STBY]$ cd
[oracle@oem132 ~]$ tail -1 /etc/oratab
STBY:/u01/app/oracle/product/12.1.0/dbhome_1:N
[oracle@oem132 ~]$ . oraenv
ORACLE_SID = [oracle] ? STBY
The Oracle base has been set to /u01/app/oracle
[oracle@oem132 ~]$ ls -l $ORACLE_HOME/dbs/*STBY*
-rw-r--r-- 1 oracle oinstall 508 Mar 6 22:11 /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initSTBY.ora
-rw-r----- 1 oracle oinstall 7680 Mar 6 22:13 /u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwSTBY
[oracle@oem132 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 12 23:03:43 2017

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

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 2929936 bytes
Variable Size 230689520 bytes
Database Buffers 599785472 bytes
Redo Buffers 5455872 bytes
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oem132 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-MAR-2017 23:04:28

Copyright (c) 1991, 2014, Oracle. All rights reserved.

Starting /u01/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oem132/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oem132.racattack)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oem132.racattack)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 12-MAR-2017 23:04:28
Uptime 0 days 0 hr. 0 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oem132/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oem132.racattack)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "STBY" has 1 instance(s).
Instance "STBY", status UNKNOWN, has 2 handler(s) for this service...
The command completed successfully
[oracle@oem132 ~]$

I am now ready to issue the DUPLICATE DATABASE command from the RAC Primary.

[oracle@collabn1 ~]$ . oraenv
ORACLE_SID = [RAC1] ? RAC1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@collabn1 ~]$ rman

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Mar 12 23:17:27 2017

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

RMAN> connect target sys/racattack

connected to target database: RAC (DBID=2519807290)

RMAN> connect auxiliary sys/racattack@STBY

connected to auxiliary database: RAC (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE ;

Starting Duplicate Db at 12-MAR-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK

contents of Memory Script:
{
backup as copy reuse
targetfile '+DATA/RAC/PASSWORD/pwdrac.277.931824933' auxiliary format
'/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwSTBY' ;
}
executing Memory Script

Starting backup at 12-MAR-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=273 instance=RAC1 device type=DISK
Finished backup at 12-MAR-17

contents of Memory Script:
{
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/STBY/controlfile/o1_mf_ddbs0p3w_.ctl'', ''/u01/app/oracle/fast_recovery_area/STBY/controlfile/o1_mf_ddbs0p40_.ctl'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/STBY/controlfile/o1_mf_ddbs0p3w_.ctl';
restore clone primary controlfile to '/u01/app/oracle/fast_recovery_area/STBY/controlfile/o1_mf_ddbs0p40_.ctl' from
'/u01/app/oracle/oradata/STBY/controlfile/o1_mf_ddbs0p3w_.ctl';
sql clone "alter system set control_files =
''/u01/app/oracle/oradata/STBY/controlfile/o1_mf_ddbs0p3w_.ctl'', ''/u01/app/oracle/fast_recovery_area/STBY/controlfile/o1_mf_ddbs0p40_.ctl'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set control_files = ''/u01/app/oracle/oradata/STBY/controlfile/o1_mf_ddbs0p3w_.ctl'', ''/u01/app/oracle/fast_recovery_area/STBY/controlfile/o1_mf_ddbs0p40_.ctl'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 12-MAR-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/oradata/STBY/controlfile/o1_mf_ddbs0p3w_.ctl tag=TAG20170312T231755
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-MAR-17

Starting restore at 12-MAR-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 12-MAR-17

sql statement: alter system set control_files = ''/u01/app/oracle/oradata/STBY/controlfile/o1_mf_ddbs0p3w_.ctl'', ''/u01/app/oracle/fast_recovery_area/STBY/controlfile/o1_mf_ddbs0p40_.ctl'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 838860800 bytes

Fixed Size 2929936 bytes
Variable Size 230689520 bytes
Database Buffers 599785472 bytes
Redo Buffers 5455872 bytes

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/STBY/tempfile/temp.285.931825311";
set newname for tempfile 2 to
"/u01/app/oracle/oradata/STBY/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/pdbseed_temp012016-12-29_12-23-03-am.dbf";
set newname for tempfile 3 to
"/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/tempfile/temp.295.931827153";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/STBY/datafile/system.279.931825083";
set newname for datafile 3 to
"/u01/app/oracle/oradata/STBY/datafile/sysaux.278.931825019";
set newname for datafile 4 to
"/u01/app/oracle/oradata/STBY/datafile/undotbs1.281.931825149";
set newname for datafile 5 to
"/u01/app/oracle/oradata/STBY/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/system.287.931825323";
set newname for datafile 6 to
"/u01/app/oracle/oradata/STBY/datafile/users.280.931825149";
set newname for datafile 7 to
"/u01/app/oracle/oradata/STBY/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/sysaux.286.931825323";
set newname for datafile 8 to
"/u01/app/oracle/oradata/STBY/datafile/undotbs2.289.931826143";
set newname for datafile 9 to
"/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/system.293.931827089";
set newname for datafile 10 to
"/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/sysaux.294.931827089";
set newname for datafile 11 to
"/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/users.296.931827189";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/STBY/datafile/system.279.931825083" datafile
3 auxiliary format
"/u01/app/oracle/oradata/STBY/datafile/sysaux.278.931825019" datafile
4 auxiliary format
"/u01/app/oracle/oradata/STBY/datafile/undotbs1.281.931825149" datafile
5 auxiliary format
"/u01/app/oracle/oradata/STBY/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/system.287.931825323" datafile
6 auxiliary format
"/u01/app/oracle/oradata/STBY/datafile/users.280.931825149" datafile
7 auxiliary format
"/u01/app/oracle/oradata/STBY/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/sysaux.286.931825323" datafile
8 auxiliary format
"/u01/app/oracle/oradata/STBY/datafile/undotbs2.289.931826143" datafile
9 auxiliary format
"/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/system.293.931827089" datafile
10 auxiliary format
"/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/sysaux.294.931827089" datafile
11 auxiliary format
"/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/users.296.931827189" ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/STBY/tempfile/temp.285.931825311 in control file
renamed tempfile 2 to /u01/app/oracle/oradata/STBY/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/pdbseed_temp012016-12-29_12-23-03-am.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/tempfile/temp.295.931827153 in control file

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 12-MAR-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/RAC/DATAFILE/system.279.931825083
output file name=/u01/app/oracle/oradata/STBY/datafile/system.279.931825083 tag=TAG20170312T231824
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/RAC/DATAFILE/sysaux.278.931825019
output file name=/u01/app/oracle/oradata/STBY/datafile/sysaux.278.931825019 tag=TAG20170312T231824
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=+DATA/RAC/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.286.931825323
output file name=/u01/app/oracle/oradata/STBY/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/sysaux.286.931825323 tag=TAG20170312T231824
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=+DATA/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/sysaux.294.931827089
output file name=/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/sysaux.294.931827089 tag=TAG20170312T231824
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=+DATA/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/system.293.931827089
output file name=/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/system.293.931827089 tag=TAG20170312T231824
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/RAC/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.287.931825323
output file name=/u01/app/oracle/oradata/STBY/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/system.287.931825323 tag=TAG20170312T231824
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/RAC/DATAFILE/undotbs1.281.931825149
output file name=/u01/app/oracle/oradata/STBY/datafile/undotbs1.281.931825149 tag=TAG20170312T231824
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DATA/RAC/DATAFILE/undotbs2.289.931826143
output file name=/u01/app/oracle/oradata/STBY/datafile/undotbs2.289.931826143 tag=TAG20170312T231824
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/RAC/DATAFILE/users.280.931825149
output file name=/u01/app/oracle/oradata/STBY/datafile/users.280.931825149 tag=TAG20170312T231824
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=+DATA/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/users.296.931827189
output file name=/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/users.296.931827189 tag=TAG20170312T231824
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-MAR-17

sql statement: alter system archive log current

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

datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=938474501 file name=/u01/app/oracle/oradata/STBY/datafile/system.279.931825083
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=938474501 file name=/u01/app/oracle/oradata/STBY/datafile/sysaux.278.931825019
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=938474501 file name=/u01/app/oracle/oradata/STBY/datafile/undotbs1.281.931825149
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=938474501 file name=/u01/app/oracle/oradata/STBY/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/system.287.931825323
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=938474501 file name=/u01/app/oracle/oradata/STBY/datafile/users.280.931825149
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=938474501 file name=/u01/app/oracle/oradata/STBY/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/sysaux.286.931825323
datafile 8 switched to datafile copy
input datafile copy RECID=9 STAMP=938474501 file name=/u01/app/oracle/oradata/STBY/datafile/undotbs2.289.931826143
datafile 9 switched to datafile copy
input datafile copy RECID=10 STAMP=938474501 file name=/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/system.293.931827089
datafile 10 switched to datafile copy
input datafile copy RECID=11 STAMP=938474501 file name=/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/sysaux.294.931827089
datafile 11 switched to datafile copy
input datafile copy RECID=12 STAMP=938474501 file name=/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/users.296.931827189
Finished Duplicate Db at 12-MAR-17

RMAN>

The DUPLICATE ... FOR STANDBY has completed.  Let me just run two verification checks.

On the Standby :

[oracle@oem132 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 12 23:24:05 2017

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, OLAP, Advanced Analytics and Real Application Testing options

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL>
SQL> select file#, name from v$datafile order by 1;

FILE#
----------
NAME
--------------------------------------------------------------------------------
1
/u01/app/oracle/oradata/STBY/datafile/system.279.931825083

3
/u01/app/oracle/oradata/STBY/datafile/sysaux.278.931825019

4
/u01/app/oracle/oradata/STBY/datafile/undotbs1.281.931825149

5
/u01/app/oracle/oradata/STBY/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/system.28
7.931825323

6
/u01/app/oracle/oradata/STBY/datafile/users.280.931825149

7
/u01/app/oracle/oradata/STBY/fd9ac20f64d244d7e043b6a9e80a2f2f/datafile/sysaux.28
6.931825323

8
/u01/app/oracle/oradata/STBY/datafile/undotbs2.289.931826143

9
/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/system.29
3.931827089

10
/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/sysaux.29
4.931827089

11
/u01/app/oracle/oradata/STBY/44bbc69ce8f552aee053334ea8c07365/datafile/users.296
.931827189


10 rows selected.

SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oem132 ~]$ cd $ORACLE_HOME/dbs
[oracle@oem132 dbs]$ ls -ltr *STBY*
-rw-r--r-- 1 oracle oinstall 508 Mar 6 22:11 initSTBY.ora
-rw-r----- 1 oracle oinstall 24 Mar 12 23:07 lkSTBY
-rw-r----- 1 oracle oinstall 7680 Mar 12 23:17 orapwSTBY
-rw-rw---- 1 oracle oinstall 1544 Mar 12 23:18 hc_STBY.dat
-rw-r----- 1 oracle oinstall 17920 Mar 12 23:18 spfileSTBY.ora
[oracle@oem132 dbs]$

On the Primary :

RMAN> exit


Recovery Manager complete.
[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 12 23:26:17 2017

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> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> set pages600
SQL> select file#, name from v$datafile order by 1;

FILE#
----------
NAME
--------------------------------------------------------------------------------
1
+DATA/RAC/DATAFILE/system.279.931825083

3
+DATA/RAC/DATAFILE/sysaux.278.931825019

4
+DATA/RAC/DATAFILE/undotbs1.281.931825149

5
+DATA/RAC/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/system.287.931825323

6
+DATA/RAC/DATAFILE/users.280.931825149

7
+DATA/RAC/FD9AC20F64D244D7E043B6A9E80A2F2F/DATAFILE/sysaux.286.931825323

8
+DATA/RAC/DATAFILE/undotbs2.289.931826143

9
+DATA/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/system.293.931827089

10
+DATA/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/sysaux.294.931827089

11
+DATA/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/users.296.931827189


10 rows selected.

SQL>

All datafiles (CDB, SEED and 1 PDB) are replicated.  Note how the folder names for the SEED and PDB are in lower-case on the Standby.  Since the source database is RAC, it has two Undo Tablespaces.  (This is 12.1 so I do not have the 12.2 feature of Local Undo in PDBs)

The next few posts will be on the DataGuard Broker and Redo Shipping and Applly.
(Should I also do a SWITCHOVER ?)
.
.
.


Categories: DBA Blogs

login issue using net service

Tom Kyte - Sun, 2017-03-12 10:26
when I used to login my account using net service name then ora-12514 error occurs and when i try to without net service name then i login successfully so what's a problem? c:\>sqlplus sys@mycon as sysdba SQL*Plus: Release 11.2.0.1.0 Production...
Categories: DBA Blogs

Production sanity check trigger on BEFORE SHUTDOWN - you'd think this would be an obvious need but...

Tom Kyte - Sun, 2017-03-12 10:26
A small, innocent mistake this week led to an unscheduled production outage. I had SSH sessions going for both my dev and my prod instances. I wanted to bounce my dev and entered SHUTDOWN IMMEDIATE. A few minutes later the customer called.... Yes, I ...
Categories: DBA Blogs

2 database with same data but hardware diff, some query are very fast on new db, some query in new db are slower than old one

Tom Kyte - Sun, 2017-03-12 10:26
Dear Tom, thanks for everything that i learned from you. i have a new power full server with oracle 11gr2 (and just for test , nobody connect to it) and another is older (Hardware are older) with oracle 10g and around 400 user on time when my que...
Categories: DBA Blogs

Effect of a Backup Retention Policy on Backup Recovery AREA to destination

Tom Kyte - Sat, 2017-03-11 15:06
Hi, What's the effect of a backup retention policy on BACKUP RECOVERY AREA rman command ? I have all backups in the FRA, and I would like to make BACKUP RECOVERY AREA to DESTINATION (disk) to have an "alternative" backup, but I am not sure of th...
Categories: DBA Blogs

Is changing passwords via alter <username> identified by values '<hash>'; a security risk?

Tom Kyte - Sat, 2017-03-11 15:06
<code>create user testuser1 identified by "DummyPass1"; create user testuser2 identified by "DummyPass1"; select name, spare4 from sys.user$ where name in ('TESTUSER1','TESTUSER2'); </code> For the above test case, the password hashes are dif...
Categories: DBA Blogs

how to preserver double quotes in the data using sqlloader data load.

Tom Kyte - Fri, 2017-03-10 20:46
hey I have data like below and need to preserve double quotes in data. Please let me know how to load data preserving double quotes using sql loader. Here record Filed 2 last line is in multiple lines marked in bold data "Filed 1","Field 2",...
Categories: DBA Blogs

Optimize insert or update million records in a table

Tom Kyte - Fri, 2017-03-10 20:46
Hi Tom, i need your help in optimizing a procedure in less time which is originally taking 40 mins to insert/update 14+million records into a table. We have a query with a table (20+ million records) which inserts/update(14 million records) in...
Categories: DBA Blogs

@OraclePartners EMEA Identity & Cloud Security Forum 2017 - SAVE THE DATE

This year we are excited to host the Oracle EMEA Identity & Cloud Security Partner Forum 2017 at Oracle Budapest, Hungary on April 27th & 28th, 2017. The purpose of this...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Corrupted Block Recovery without Backups

Tom Kyte - Fri, 2017-03-10 02:26
Dear TOM, I have the database that doesn't have good enough backups and it went down. The database starts up to mount state, but does not open. I figured out which block is corrupted, however, I could not find what does that block belong to, rman ...
Categories: DBA Blogs

Generate a CSV Blob and store it in a table

Tom Kyte - Fri, 2017-03-10 02:26
Hi Tom, i have a requirement that i need to export a table contents into a csv file and store it as a blob in other table. But i dont have the provision to store the file in a directory so i need to perform all actions together. How can i do that?
Categories: DBA Blogs

create and insert table in same procedure

Tom Kyte - Fri, 2017-03-10 02:26
I have one procedure within a procedure I have to create a table and insert into that table, insertion values are from associative array, how do i perform this by using dynamic sql. Please suggest me. Thanks in Advance :)
Categories: DBA Blogs

DBCA problem installing on ORACLE LINUX 7.3 with ORACLE 12cR2

Tom Kyte - Fri, 2017-03-10 02:26
hi all! NOW ORACLE12cR2 for Linux released.I won't test its new feature,so dowload oracle12cR2 for linux to install on oracle linux7.3,and it appear an error when DBCA,the log is: <code>cat /u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log [ 2017-...
Categories: DBA Blogs

Archive log

Tom Kyte - Fri, 2017-03-10 02:26
why the current and oldest online redo log sequence is by 2??
Categories: DBA Blogs

sys_context returning session id in RAC

Tom Kyte - Fri, 2017-03-10 02:26
Hi, In a RAC oracle environment where lets say multiple instance with same session id's are running, then what is the best way of getting the session ID from gv$session without affecting performance? what would sys_context('userenv', 'sessionid') re...
Categories: DBA Blogs

How to exclude mviews that are not being updated in the script?

Tom Kyte - Fri, 2017-03-10 02:26
Hello, I have a scheduled job on Oracle GRID to send alerts when any mviews are not updated. But I have three mviews that are not beeig updated and I canno't change it for now. So I need to change my script to exclude these three mviews (only thos...
Categories: DBA Blogs

Updating columns in tables having million of records

Tom Kyte - Fri, 2017-03-10 02:26
Hi, I have gone through your forums on how to update a table with millions of records Approach 1 - To create a temporary table and make the necessary changes, drop the original table and rename temporary table to original table. I have not gone...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs