Hemant K Chitale

Subscribe to Hemant K Chitale feed
I am an Oracle Database Specialist in Singapore.
get an rss feed of this blog at http://hemantoracledba.blogspot.com/feeds/posts/default?alt=rss
follow me on twitter : @HemantKChitale
Updated: 7 hours 5 min ago

12cR1 RAC Posts -- 8d : Registering the two databases in DataGuard Broker

Mon, 2017-03-27 10:19
Continuing my series on setting up a non-RAC, FileSystem Standby for a RAC, ASM Database....

On the Standby:

[oracle@oem132 ~]$ . oraenv
ORACLE_SID = [oracle] ? STBY
The Oracle base has been set to /u01/app/oracle
[oracle@oem132 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2017 22:24:54

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 27-MAR-2017 22:24:58
Uptime 0 days 0 hr. 0 min. 2 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 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 27 22:25:07 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> alter database mount standby database;

Database altered.

SQL>
SQL> show parameter dg_broker

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/12.1.0
/dbhome_1/dbs/dr1STBY.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.1.0
/dbhome_1/dbs/dr2STBY.dat
dg_broker_start boolean FALSE
SQL>
SQL> alter system set dg_broker_Start=TRUE;

System altered.

SQL>


On the first instance of the Primary :

SQL> show parameter dg_broker

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_config_file1 string /u01/app/oracle/product/12.1.0
/dbhome_1/dbs/dr1RAC.dat
dg_broker_config_file2 string /u01/app/oracle/product/12.1.0
/dbhome_1/dbs/dr2RAC.dat
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_config_file1='+DATA/RAC/dgbroker1.dat' sid='*' scope=BOTH;

System altered.

SQL> alter system set dg_broker_config_file2='+FRA/RAC/dgbroker2.dat' sid='*' scope=BOTH;

System altered.

SQL> alter system set dg_broker_start=TRUE;

System altered.

SQL>

[oracle@collabn1 ~]$ tnsping RAC1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2017 22:37:41

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.racattack)))
OK (0 msec)
[oracle@collabn1 ~]$ sqlplus sys/racattack@STBY

SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 27 22:37:51 2017

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

ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0


Enter user-name: sys/racattack@STBY as SYSDBA

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> 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@collabn1 ~]$ tnsping RAC1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2017 22:38:07

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RAC.racattack)))
OK (0 msec)
[oracle@collabn1 ~]$ tnsping STBY

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 27-MAR-2017 22:38:10

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oem132)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STBY)))
OK (0 msec)
[oracle@collabn1 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/racattack@RAC1
Connected as SYSDBA.
DGMGRL> create configuration RAC as primary database is RAC connect identifier is RAC1;
Configuration "rac" created with primary database "rac"
DGMGRL> add database stby as connect identifier is stby maintained as physical;
Database "stby" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - rac

Protection Mode: MaxPerformance
Members:
rac - Primary database
stby - Physical standby database
Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR (status updated 7 seconds ago)

DGMGRL>


Errors in the STBY DataGuard Broker Logfile drcSTBY.log :

03/27/2017 22:45:12
Warning: Property 'StandbyFileManagement' has inconsistent values:METADATA='MANUAL', SPFILE='', DATABASE='MANUAL'
Warning: Property 'ArchiveLagTarget' has inconsistent values:METADATA='0', SPFILE='', DATABASE='0'
Warning: Property 'LogArchiveMaxProcesses' has inconsistent values:METADATA='4', SPFILE='', DATABASE='4'
Warning: Property 'LogArchiveMinSucceedDest' has inconsistent values:METADATA='1', SPFILE='', DATABASE='1'
SPFILE is missing value for property 'LogArchiveTrace' with sid='STBY'
Warning: Property 'LogArchiveTrace' has inconsistent values:METADATA='0', SPFILE='(missing)', DATABASE='0'
SPFILE is missing value for property 'LogArchiveFormat' with sid='STBY'
Warning: Property 'LogArchiveFormat' has inconsistent values:METADATA='%t_%s_%r.dbf', SPFILE='(missing)', DATABASE='%t_%s_%r.dbf'
Failed to send message to site rac. Error code is ORA-16501.

Errors in the RAC1 DataGuard Broker Logfile drcRAC1.log :

Site stby returned ORA-16664.
Data Guard Broker Status Summary:
Type Name Severity Status
Configuration rac Warning ORA-16607
Primary Database rac Success ORA-00000
Physical Standby Database stby Error ORA-16664


Actions taken on the Standby :

SQL> show parameter standby

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string ?/dbs/arch
standby_file_management string MANUAL
SQL> alter system set standby_file_management='AUTO' scope=BOTH;

System altered.

SQL> alter system set archive_lag_target=0 scope=BOTH;

System altered.

SQL> alte system set log_archive_max_processes=4 scope=BOTH;
SP2-0734: unknown command beginning "alte syste..." - rest of line ignored.
SQL> alter system set log_archive_max_processes=4 scope=BOTH;

System altered.

SQL> alter system set log_archive_min_succeed_dest=1 scope=BOTH;

System altered.

SQL> alter system set log_archive_trace=0 scope=BOTH;

System altered.

SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=BOTH;
alter system set log_archive_format='%t_%s_%r.dbf' scope=BOTH
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


SQL>


I also had to edit the TNSNAMES.ORA on the STBY server for the correct *VIP* entry (not the physical hostname) for the RAC1 instance.

RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.78.61)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC.racattack)
)
)

RAC1_DGB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.78.61)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC_DGMGRL)
)
)


Thereafter, it worked.  Note : You sometimes have to wait a while to enter the SHOW CONFIGURATION, the first result may show an error.  So I entered it twice.

DGMGRL> disable configuration;
Disabled.
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - rac

Protection Mode: MaxPerformance
Members:
rac - Primary database
stby - Physical standby database
Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR (status updated 125 seconds ago)

DGMGRL> show configuration;

Configuration - rac

Protection Mode: MaxPerformance
Members:
rac - Primary database
stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 14 seconds ago)

DGMGRL>


These are the drcSTBY.log entries when the TNSNAMES.ORA for RAC1 was incorrect on the Standby Server :

Failed to connect to remote database rac. Error is ORA-12541
Failed to send message to site rac. Error code is ORA-12541.


This is the drcSTBY.log entry on success :

03/27/2017 23:00:51
Apply Instance for Database stby set to STBY


And the entry in drcRAC1.log on success :

03/27/2017 23:00:10
DISABLE CONFIGURATION
03/27/2017 23:00:46
ENABLE CONFIGURATION
03/27/2017 23:00:57
EDIT DATABASE stby SET PROPERTY ActualApplyInstance = STBY
03/27/2017 23:01:00
Apply Instance for database stby is STBY


Setting Maximum Performance mode (command issued on the *Primary*) :

SQL> alter database set standby to maximize performance;

Database altered.

SQL>

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MaxPerformance;
Succeeded.
DGMGRL> show configuration;

Configuration - rac

Protection Mode: MaxPerformance
Members:
rac - Primary database
stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 34 seconds ago)

DGMGRL>
DGMGRL> show configuration verbose

Configuration - rac

Protection Mode: MaxPerformance
Members:
rac - Primary database
stby - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
ObserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>


Will be testing Redo Transmission and Apply later.
.
.
.

Categories: DBA Blogs

1.5million PageViews

Mon, 2017-03-13 10:01
My Oracle Blog now has had 1.5million PageViews.


The 1million PageViews mark was hit in March 2015.
.
.
.

Categories: DBA Blogs

12cR1 RAC Posts -- 8c : Ignorable "Errors" during the DUPLICATE

Mon, 2017-03-13 09:56
In yesterday's post, I had shown a DUPLICATE DATABASE from RAC-ASM to SingleInstance-FileSystem.

During the course of the DUPLICATE DATABASE run, the Standby alert log seemingly reported errors.  I chose to ignore the "errors" as I know the DUPLICATE was running successfully.

For your reference, here are some of the good messages :

Sun Mar 12 23:17:55 2017
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;
Sun Mar 12 23:17:59 2017
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;

Sun Mar 12 23:21:41 2017
Switch of datafile 1 complete to datafile copy
checkpoint is 3315193
Switch of datafile 3 complete to datafile copy
checkpoint is 3315211
Switch of datafile 4 complete to datafile copy
checkpoint is 3315270
Switch of datafile 5 complete to datafile copy
checkpoint is 1754712
Switch of datafile 6 complete to datafile copy
checkpoint is 3315280
Switch of datafile 7 complete to datafile copy
checkpoint is 1754712
Switch of datafile 8 complete to datafile copy
checkpoint is 3315276
Switch of datafile 9 complete to datafile copy
checkpoint is 2978862
Switch of datafile 10 complete to datafile copy
checkpoint is 2978862
Switch of datafile 11 complete to datafile copy
checkpoint is 2978862


And here are the errors (about the online redo logs) continuously being reported.  These are messages which I ignored because the Standby has no online redo logs yet.

Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_1.257.931825281'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/STBY/onlinelog/group_1.283.931825279'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_1.257.931825281'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/STBY/onlinelog/group_1.283.931825279'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_2.258.931825287'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/STBY/onlinelog/group_2.284.931825283'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_2.258.931825287'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/STBY/onlinelog/group_2.284.931825283'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 3 of thread 2
ORA-00312: online log 3 thread 2: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_3.259.931826417'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 2: '/u01/app/oracle/oradata/STBY/onlinelog/group_3.290.931826413'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 3 of thread 2
ORA-00312: online log 3 thread 2: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_3.259.931826417'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 3 thread 2: '/u01/app/oracle/oradata/STBY/onlinelog/group_3.290.931826413'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_4.260.931826421'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 4 thread 2: '/u01/app/oracle/oradata/STBY/onlinelog/group_4.291.931826417'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 4 of thread 2
ORA-00312: online log 4 thread 2: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_4.260.931826421'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 4 thread 2: '/u01/app/oracle/oradata/STBY/onlinelog/group_4.291.931826417'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 5 of thread 0
ORA-00312: online log 5 thread 0: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_5.303.937936343'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 5 thread 0: '/u01/app/oracle/oradata/STBY/onlinelog/group_5.292.937936339'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 5 of thread 0
ORA-00312: online log 5 thread 0: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_5.303.937936343'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 5 thread 0: '/u01/app/oracle/oradata/STBY/onlinelog/group_5.292.937936339'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 6 of thread 0
ORA-00312: online log 6 thread 0: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_6.304.937936363'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 6 thread 0: '/u01/app/oracle/oradata/STBY/onlinelog/group_6.298.937936361'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 6 of thread 0
ORA-00312: online log 6 thread 0: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_6.304.937936363'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 6 thread 0: '/u01/app/oracle/oradata/STBY/onlinelog/group_6.298.937936361'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 7 of thread 0
ORA-00312: online log 7 thread 0: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_7.305.937936377'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 7 thread 0: '/u01/app/oracle/oradata/STBY/onlinelog/group_7.299.937936375'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 7 of thread 0
ORA-00312: online log 7 thread 0: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_7.305.937936377'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 7 thread 0: '/u01/app/oracle/oradata/STBY/onlinelog/group_7.299.937936375'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 8 of thread 0
ORA-00312: online log 8 thread 0: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_8.306.937936389'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 8 thread 0: '/u01/app/oracle/oradata/STBY/onlinelog/group_8.300.937936389'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 8 of thread 0
ORA-00312: online log 8 thread 0: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_8.306.937936389'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 8 thread 0: '/u01/app/oracle/oradata/STBY/onlinelog/group_8.300.937936389'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 9 of thread 0
ORA-00312: online log 9 thread 0: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_9.307.937936405'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 9 thread 0: '/u01/app/oracle/oradata/STBY/onlinelog/group_9.301.937936403'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Sun Mar 12 23:18:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_lgwr_16416.trc:
ORA-00313: open failed for members of log group 9 of thread 0
ORA-00312: online log 9 thread 0: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_9.307.937936405'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 9 thread 0: '/u01/app/oracle/oradata/STBY/onlinelog/group_9.301.937936403'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

alter database clear logfile group 1
Clearing online log 1 of thread 1 sequence number 41
Sun Mar 12 23:21:42 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_ora_16474.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_1.257.931825281'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3



It is also interesting to see how CONTROL_FILES, DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT are reported in the alert log :

  
control_files = "/u01/app/oracle/oradata/STBY/controlfile/o1_mf_ddbs0p3w_.ctl"
control_files = "/u01/app/oracle/fast_recovery_area/STBY/controlfile/o1_mf_ddbs0p40_.ctl"
db_file_name_convert = "+DATA/RAC"
db_file_name_convert = "/u01/app/oracle/oradata/STBY"
db_file_name_convert = "+FRA/RAC"
db_file_name_convert = "/u01/app/oracle/fast_recovery_area/STBY"
log_file_name_convert = "+DATA/RAC"
log_file_name_convert = "/u01/app/oracle/oradata/STBY"
log_file_name_convert = "+FRA/RAC"
log_file_name_convert = "/u01/app/oracle/fast_recovery_area/STBY"

Each component of the entry in the init parameter file is reported on a separate line in the alert log.
.
.
.

Categories: DBA Blogs

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

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

12cR1 RAC Posts -- 8a : Setting up SingleInstance DG Standby for RAC

Wed, 2017-03-08 09:08
This is the first of a small series of subposts on setting up SingleInstance DataGuard Standby for my 12cR1 RAC environment.

Primary Database : 12cR1 MultiTenant RAC : 2 nodes, database on ASM
Standby Database : 12cR1 MultiTenant SingleInstance : 1 node, database on FileSystem

The "difficulties" are :
a.  The Primary Database is MultiTenant with 1 PDB besides the SEED
b.  The Primary Database is on ASM but the Standby is to be built on FileSystem
c.  The Primary is a 2-node Cluster while the Standby is a SingleInstance installatin

This post is on the initial setup.

RAC Nodes
collabn1.racattack   192.168.78.51
collabn2.racattack   192.168.78.52

SingleInstance Node 
oem132.racattack     192.168.78.100


Database Changes :
SQL> alter database force logging;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL>
SQL> alter database add standby logfile size 50M;

Database altered.

SQL> alter database add standby logfile size 50M;

Database altered.

SQL> alter database add standby logfile size 50M;

Database altered.

SQL> alter database add standby logfile size 50M;

Database altered.

SQL> alter database add standby logfile size 50M;

Database altered.

SQL>
SQL>select  group# , status, type, member from v$logfile order by 1;


Listener on Standby Server :
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oem132.racattack)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
(SID_LIST =
  (SID_DESC =
    (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
    (SID_NAME = STBY)
   )
  (SID_DESC =
    (GLOBAL_NAME = STBY_DGMGRL)
    (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
    (SID_NAME = STBY)
   )
)


tnsnames.ora on first RAC Node :
STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oem132)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STBY)
    )
  )

STBY_DGB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oem132)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STBY_DGMGRL)
    )
  )


STBY Instance Parameter File :
compatible=12.1.0.2.0
db_name=RAC
db_unique_name=STBY
enable_pluggable_database=TRUE
sga_target=800M
db_file_name_convert='+DATA/RAC','/u01/app/oracle/oradata/STBY','+FRA/RAC','/u01/app/oracle/fast_recovery_area/STBY'
log_file_name_convert='+DATA/RAC','/u01/app/oracle/oradata/STBY','+FRA/RAC','/u01/app/oracle/fast_recovery_area/STBY'
db_create_file_dest='/u01/app/oracle/oradata'
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size=16G
remote_login_passwordfile=EXCLUSIVE


Copying the Password File :
ASMCMD> pwcopy +DATA/RAC/PASSWORD/pwdrac.277.931824933 /var/tmp/RACPasswordFile
copying +DATA/RAC/PASSWORD/pwdrac.277.931824933 -> /var/tmp/RACPasswordFile
ASMCMD> exit
scp /var/tmp/RACPasswordFile oracle@oem132:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwSTBY


to be continued in post 8b on the filesystem directories for the STBY instance and the execution of the DUPLICATE DATABASE command.

Categories: DBA Blogs

12cR1 RAC Posts -- 7 : OCR Commands

Mon, 2017-03-06 03:11
[Yes, I know that 12.2 is now available for download but it will be some time before I have a running 12.2 RAC environment]

Some OCR / OLR Commands :

The OCR is the Cluster Registry.  We also have an OLR that is the Local Registry which is created on a local filesystem.

We can check the consistency of the Registry with ocrcheck.  Note the difference between using oracle (or grid) and using root to run the check.  oracle can't check the OLR and can't do a logical consistency check of the OCR -- both require to be run as root.

[root@collabn1 ~]# su - oracle
[oracle@collabn1 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[oracle@collabn1 ~]$ ocrcheck -local
PROTL-602: Failed to retrieve data from the local registry
PROCL-26: Error while accessing the physical storage Operating System error [Permission denied] [13]
[oracle@collabn1 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1676
Available space (kbytes) : 407892
ID : 827167720
Device/File Name : +OCRVOTE
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

[oracle@collabn1 ~]$ su
Password:
[root@collabn1 oracle]# ocrcheck -local
Status of Oracle Local Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1036
Available space (kbytes) : 408532
ID : 1014277103
Device/File Name : /u01/app/12.1.0/grid/cdata/collabn1.olr
Device/File integrity check succeeded

Local registry integrity check succeeded

Logical corruption check succeeded

[root@collabn1 oracle]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1676
Available space (kbytes) : 407892
ID : 827167720
Device/File Name : +OCRVOTE
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@collabn1 oracle]#


Oracle automates backups of the OCR (but not the OLR !).  Below, the -showbackuploc shows the location of backups.

[root@collabn1 oracle]# ocrconfig -showbackuploc
The Oracle Cluster Registry backup location is [/u01/app/12.1.0/grid/cdata/]
[root@collabn1 oracle]# ls -lt /u01/app/12.1.0/grid/cdata
total 1272
-rw-------. 1 root oinstall 503484416 Mar 6 17:03 collabn1.olr
drwxrwxr-x. 2 oracle oinstall 4096 Jan 16 14:12 collabn-cluster
drwxr-xr-x. 2 oracle oinstall 4096 Dec 19 15:06 collabn1
drwxr-xr-x. 2 oracle oinstall 4096 Dec 19 14:37 localhost
[root@collabn1 oracle]# ls -lt /u01/app/12.1.0/grid/cdata/collabn1
total 820
-rw-r--r--. 1 root root 839680 Dec 19 15:06 backup_20161219_150615.olr
[root@collabn1 oracle]# ocrconfig -showbackup

collabn1 2017/01/16 14:09:40 /u01/app/12.1.0/grid/cdata/collabn-cluster/backup00.ocr 0

collabn1 2017/01/16 14:09:40 /u01/app/12.1.0/grid/cdata/collabn-cluster/day.ocr 0

collabn1 2017/01/16 14:09:40 /u01/app/12.1.0/grid/cdata/collabn-cluster/week.ocr 0

collabn2 2016/12/19 15:47:24 /u01/app/12.1.0/grid/cdata/collabn-cluster/backup_20161219_154724.ocr 0

collabn2 2016/12/19 15:47:16 /u01/app/12.1.0/grid/cdata/collabn-cluster/backup_20161219_154716.ocr 0
[root@collabn1 oracle]#


All recent (4-Hourly, Daily, Weekly) of the OCR are on the "master" node -- collabn1 -- which comes up first in my cluster.    The 19-Dec backups (of the OCR and OLR) are when I started setting up the Cluster.  Note that there are no subsequent (automated) OLR backups.
Note : There are no 4-Hourly/Daily/Weekly backups since 16-Jan because I haven't had my cluster running for long enough for those backups to kick in.

[root@collabn1 oracle]# ocrconfig -local -manualbackup

collabn1 2017/03/06 17:11:29 /u01/app/12.1.0/grid/cdata/collabn1/backup_20170306_171129.olr 0

collabn1 2016/12/19 15:06:15 /u01/app/12.1.0/grid/cdata/collabn1/backup_20161219_150615.olr 0
[root@collabn1 oracle]# ocrconfig -manualbackup

collabn1 2017/03/06 17:12:21 /u01/app/12.1.0/grid/cdata/collabn-cluster/backup_20170306_171221.ocr 0

collabn2 2016/12/19 15:47:24 /u01/app/12.1.0/grid/cdata/collabn-cluster/backup_20161219_154724.ocr 0

collabn2 2016/12/19 15:47:16 /u01/app/12.1.0/grid/cdata/collabn-cluster/backup_20161219_154716.ocr 0
[root@collabn1 oracle]#


I can run manual backups (the -local is for the OLR) as shown above.

It is important to include these backups in the backup strategy for the filesystem(s) that hold the Grid Infrastructure and RDBMS installations (binaries, configuration files, trace files etc).
.
.
.
Categories: DBA Blogs

12cR1 RAC Posts -- 6 :Running the Cluster Verification Utility

Mon, 2017-02-20 03:45
With a successful RAC Cluster, running the Cluster Verification Utility

[oracle@collabn1 ~]$ cluvfy stage -post crsinst -n collabn1,collabn2

Performing post-checks for cluster services setup

Checking node reachability...
Node reachability check passed from node "collabn1"


Checking user equivalence...
User equivalence check passed for user "oracle"

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity using interfaces on subnet "192.168.78.0"
Node connectivity passed for subnet "192.168.78.0" with node(s) collabn2,collabn1
TCP connectivity check passed for subnet "192.168.78.0"


Check: Node connectivity using interfaces on subnet "172.16.100.0"
Node connectivity passed for subnet "172.16.100.0" with node(s) collabn1,collabn2
TCP connectivity check passed for subnet "172.16.100.0"

Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "192.168.78.0".
Subnet mask consistency check passed for subnet "172.16.100.0".
Subnet mask consistency check passed.

Node connectivity check passed

Checking multicast communication...

Checking subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251"...
Check of subnet "172.16.100.0" for multicast communication with multicast group "224.0.0.251" passed.

Check of multicast communication passed.

Checking whether the ASM filter driver is active and consistent on all nodes
ASM filter driver library is not installed on any of the cluster nodes.
ASM filter driver configuration was found consistent across all the cluster nodes.
Time zone consistency check passed

Checking Cluster manager integrity...


Checking CSS daemon...
Oracle Cluster Synchronization Services appear to be online.

Cluster manager integrity check passed


UDev attributes check for OCR locations started...
UDev attributes check passed for OCR locations


UDev attributes check for Voting Disk locations started...
UDev attributes check passed for Voting Disk locations

Default user file creation mask check passed

Checking cluster integrity...


Cluster integrity check passed


Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations


Checking daemon liveness...
Liveness check passed for "CRS daemon"

Checking OCR config file "/etc/oracle/ocr.loc"...

OCR config file "/etc/oracle/ocr.loc" check successful


Disk group for ocr location "+OCRVOTE/collabn-cluster/OCRFILE/registry.255.934671619" is available on all the nodes


Checking OCR backup location "/u01/app/12.1.0/grid/cdata/collabn-cluster"

OCR backup location "/u01/app/12.1.0/grid/cdata/collabn-cluster" check passed

NOTE:
This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.

OCR integrity check passed

Checking CRS integrity...

Clusterware version consistency passed.

CRS integrity check passed

Checking node application existence...

Checking existence of VIP node application (required)
VIP node application check passed

Checking existence of NETWORK node application (required)
NETWORK node application check passed

Checking existence of ONS node application (optional)
ONS node application check passed


Checking Single Client Access Name (SCAN)...

Checking TCP connectivity to SCAN listeners...
TCP connectivity to SCAN listeners exists on all cluster nodes

Checking name resolution setup for "collabn-cluster-scan.racattack"...

Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...
All nodes have same "hosts" entry defined in file "/etc/nsswitch.conf"
Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed


Checking SCAN IP addresses...
Check of SCAN IP addresses passed

Verification of SCAN VIP and listener setup passed

Checking OLR integrity...
Check of existence of OLR configuration file "/etc/oracle/olr.loc" passed
Check of attributes of OLR configuration file "/etc/oracle/olr.loc" passed

WARNING:
This check does not verify the integrity of the OLR contents. Execute 'ocrcheck -local' as a privileged user to verify the contents of OLR.

OLR integrity check passed

Checking Oracle Cluster Voting Disk configuration...

Oracle Cluster Voting Disk configuration check passed

User "oracle" is not part of "root" group. Check passed
Oracle Clusterware is installed on all nodes.
CTSS resource check passed
Query of CTSS for time offset passed

CTSS is in Active state. Proceeding with check of clock time offsets on all nodes...
Check of clock time offsets passed


Oracle Cluster Time Synchronization Services check passed
Checking VIP configuration.
Checking VIP Subnet configuration.
Check for VIP Subnet configuration passed.
Checking VIP reachability
Check for VIP reachability passed.

Post-check for cluster services setup was successful.
[oracle@collabn1 ~]$
[oracle@collabn1 ~]$ su
Password:
[root@collabn1 oracle]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1688
Available space (kbytes) : 407880
ID : 827167720
Device/File Name : +OCRVOTE
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@collabn1 oracle]#
[root@collabn1 oracle]# ocrcheck -local
Status of Oracle Local Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1028
Available space (kbytes) : 408540
ID : 1014277103
Device/File Name : /u01/app/12.1.0/grid/cdata/collabn1.olr
Device/File integrity check succeeded

Local registry integrity check succeeded

Logical corruption check succeeded

[root@collabn1 oracle]#



[root@collabn2 oracle]# ocrcheck -local
Status of Oracle Local Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 796
Available space (kbytes) : 408772
ID : 1896774486
Device/File Name : /u01/app/12.1.0/grid/cdata/collabn2.olr
Device/File integrity check succeeded

Local registry integrity check succeeded

Logical corruption check succeeded

[root@collabn2 oracle]#


The "cluvfy stage -post crsinst" command as the Grid Infrastructure user checks the status of all components after the installation and configuration of the Cluster.  Note : This does not and cannot check any database (RAC or non-RAC) that is created.

The "ocrcheck" commands by root are to check the integrity of the Cluster Registry and Local Registry.  (You would run "ocrcheck -local" on each node of the Cluster).

.
.
.

Categories: DBA Blogs

12cR1 RAC Posts -- 5 : Relocating OCR and VoteDisk

Mon, 2017-01-30 09:09
Most default installation guides for Grid Infrastructure will have you creating a DATA Disk Group during the install.  This results in the OCR, the Vote Disk and MGMTDB being created in the same DATA Disk Group.

The proper installation is to have OCR and Vote Disk separated from DATA.  If you create a different Disk Group during the installation, you would have all three components (MGMTDB is new in 12c, did not exist in 11g) and you can have these components properly placed in a non-DATA Disk Group that you create as the default.

But what if they have already been located in DATA ?  Can you relocate them to another Disk Group ?



I start with creating a new Disk Group called OCRVOTE on an existing unused ASM Disk.

[root@collabn1 ~]# su - oracle
[oracle@collabn1 ~]$ . oraenv
ORACLE_SID = [oracle] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[oracle@collabn1 ~]$ sqlplus '/ as sysasm'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 30 22:34:38 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 Real Application Clusters and Automatic Storage Management options

SQL>
SQL> set pages600
SQL> col name format a12
SQL> col path format a16
SQL> select name, path, state, group_number, disk_number
2 from v$asm_disk
3 order by 4,5,1
4 /

NAME PATH STATE GROUP_NUMBER DISK_NUMBER
------------ ---------------- -------- ------------ -----------
/dev/asm-disk4 NORMAL 0 0
DATA_0000 /dev/asm-disk1 NORMAL 1 0
DATA_0001 /dev/asm-disk2 NORMAL 1 1
DATA_0002 /dev/asm-disk5 NORMAL 1 2
FRA_0000 /dev/asm-disk3 NORMAL 2 0

SQL>
SQL> create diskgroup OCRVOTE external redundancy disk '/dev/asm-disk4';

Diskgroup created.

SQL>
SQL> select name, path, state, group_number, disk_number
2 from v$asm_disk
3 order by 4,5,1
4 /

NAME PATH STATE GROUP_NUMBER DISK_NUMBER
------------ ---------------- -------- ------------ -----------
DATA_0000 /dev/asm-disk1 NORMAL 1 0
DATA_0001 /dev/asm-disk2 NORMAL 1 1
DATA_0002 /dev/asm-disk5 NORMAL 1 2
FRA_0000 /dev/asm-disk3 NORMAL 2 0
OCRVOTE_0000 /dev/asm-disk4 NORMAL 3 0

SQL>
SQL> alter diskgroup OCRVOTE set attribute 'COMPATIBLE.ASM' = '12.1';

Diskgroup altered.

SQL>


I have identified the ASM Disk on /dev/asm-disk4 as available and created a DiskGroup on it.  I have to mount the DiskGroup on the second node as well.  (If I don't mount the DiskGroup on the second node collabn2, I get errors
"PROT-30: The Oracle Cluster Registry location to be added is not usable
PROC-50: The Oracle Cluster Registry location to be added is inaccessible on nodes collabn2."
when attempting to add the OCR on the DiskGroup).

[oracle@collabn2 trace]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 30 22:52:18 2017

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

Enter user-name: / as sysasm

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

SQL> alter diskgroup OCRVOTE mount;

Diskgroup altered.

SQL> set pages60
SQL> col name format a12
SQL> col path format a16
SQL> select name, path, state, group_number, disk_number
2 from v$asm_disk
3 order by 4,5,1
4 /

NAME PATH STATE GROUP_NUMBER DISK_NUMBER
------------ ---------------- -------- ------------ -----------
DATA_0000 /dev/asm-disk1 NORMAL 1 0
DATA_0001 /dev/asm-disk2 NORMAL 1 1
DATA_0002 /dev/asm-disk5 NORMAL 1 2
FRA_0000 /dev/asm-disk3 NORMAL 2 0
OCRVOTE_0000 /dev/asm-disk4 NORMAL 3 0

SQL>


Next, I plan to relocate the OCR which is currently in the DATA DiskGroup.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options
[oracle@collabn1 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1688
Available space (kbytes) : 407880
ID : 827167720
Device/File Name : +DATA
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check bypassed due to non-privileged user

[oracle@collabn1 ~]$
[oracle@collabn1 ~]$ su
Password:
[root@collabn1 oracle]# ocrconfig -add +OCRVOTE
[root@collabn1 oracle]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1684
Available space (kbytes) : 407884
ID : 827167720
Device/File Name : +DATA
Device/File integrity check succeeded
Device/File Name : +OCRVOTE
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@collabn1 oracle]# ocrconfig -delete +DATA
[root@collabn1 oracle]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1684
Available space (kbytes) : 407884
ID : 827167720
Device/File Name : +OCRVOTE
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@collabn1 oracle]#


I first used OCRCHECK ("ocrcheck" at the commandline) to list the existing OCR location.  I then used root and OCRCONFIG -ADD to add +OCRVOTE as a location and verified it with OCRCHECK.  I then used OCRCONFIG -DELETE ("ocrconfig -add" and "ocrconfig -delete") to delete the old location.

I could have used "ocrconfig -replace" to replace the OCR location but I prefer ADD and DELETE.



How do I relocate the VoteDisk ?  Since I am using a single DiskGroup and External Redundancy with only 1 Failure Group, I have only 1 VoteDisk.

[root@collabn1 oracle]# crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 96fbcb40bfeb4ff7bf18881adcfef149 (/dev/asm-disk1) [DATA]
Located 1 voting disk(s).
[root@collabn1 oracle]#
[root@collabn1 oracle]# crsctl replace votedisk +OCRVOTE
Successful addition of voting disk a58b8b9d58064fb8bf6df0b3ee701e32.
Successful deletion of voting disk 96fbcb40bfeb4ff7bf18881adcfef149.
Successfully replaced voting disk group with +OCRVOTE.
CRS-4266: Voting file(s) successfully replaced
[root@collabn1 oracle]#
[root@collabn1 oracle]# crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE a58b8b9d58064fb8bf6df0b3ee701e32 (/dev/asm-disk4) [OCRVOTE]
Located 1 voting disk(s).
[root@collabn1 oracle]#


With ASM as  the location for VoteDisk, I cannot use CRSCTL ADD VOTEDISK  but have to use CRSCTL REPLACE VOTEDISK.

So, I have added a new DiskGroup called OCRVOTE and relocated both OCR and VoteDisk to this new DiskGroup (on disk /dev/asm-disk4).

Can I relocate +MGMTDB ?  I am open to suggestions.

.
.
.

Categories: DBA Blogs

12cR1 RAC Posts -- 4 : Adding a Disk of a different size

Sat, 2017-01-21 10:44
How does 12.1.0.2 ASM handle adding a disk of a different size to an existing DiskGroup ?

I currently have 4 disks of 5GB each in 2 DiskGroups

[oracle@collabn1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 21 23:48:00 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 Real Application Clusters and Automatic Storage Management options

SQL> select group_number, disk_number, name, state, total_mb
2 from v$asm_disk
3 order by 1,2,3
4 /

GROUP_NUMBER DISK_NUMBER NAME STATE TOTAL_MB
------------ ----------- ------------------------------ -------- ----------
0 0 NORMAL 0
1 0 DATA_0000 NORMAL 5114
1 1 DATA_0001 NORMAL 5114
2 0 FRA_0000 NORMAL 5114

SQL>
SQL> select group_number, name
2 from v$asm_diskgroup
3 order by 1
4 /

GROUP_NUMBER NAME
------------ ------------------------------
1 DATA
2 FRA

SQL>


The DATA DiskGroup has 2 disks of 5GB each and the FRA DiskGroup has 1 disk of 5GB.  One disk (identified as DiskNumber=0) is not yet assigned.

What happens if I try to expand the DATA DiskGroup with a Disk of 10GB ?

[root@collabn1 dev]# fdisk /dev/sdf
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xff8b0ab7.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
switch off the mode (command 'c') and change display units to
sectors (command 'u').

Command (m for help): m
Command action
a toggle a bootable flag
b edit bsd disklabel
c toggle the dos compatibility flag
d delete a partition
l list known partition types
m print this menu
n add a new partition
o create a new empty DOS partition table
p print the partition table
q quit without saving changes
s create a new empty Sun disklabel
t change a partition's system id
u change display/entry units
v verify the partition table
w write table to disk and exit
x extra functionality (experts only)

Command (m for help): p

Disk /dev/sdf: 12.9 GB, 12884901888 bytes
255 heads, 63 sectors/track, 1566 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xff8b0ab7

Device Boot Start End Blocks Id System

Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-1566, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-1566, default 1566):
Using default value 1566

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@collabn1 dev]#
[root@collabn1 dev]# /sbin/scsi_id -g -u -d /dev/sdf
1ATA_VBOX_HARDDISK_VB535deca9-9a295efe
[root@collabn1 dev]#
[root@collabn1 dev]# /sbin/scsi_id -g -u -d /dev/sdf
1ATA_VBOX_HARDDISK_VB535deca9-9a295efe
[root@collabn1 dev]# cd /etc/udev/rules.d
[root@collabn1 rules.d]# vi 99-oracle-asmdevices.rules
[root@collabn1 rules.d]# tail -1 99-oracle-asmdevices.rules
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="1ATA_VBOX_HARDDISK_VB535deca9-9a295efe", NAME="asm-disk5", OWNER="oracle", GROUP="dba", MODE="0660"
[root@collabn1 rules.d]#
[root@collabn1 rules.d]# /sbin/partprobe /dev/sdf1
[root@collabn1 rules.d]# /sbin/udevadm test /block/sdb/sdf1
run_command: calling: test
udevadm_test: version 147
This program is for debugging only, it does not run any program,
specified by a RUN key. It may show incorrect results, because
some values may be different, or not available at a simulation run.

parse_file: reading '/lib/udev/rules.d/10-console.rules' as rules file
parse_file: reading '/lib/udev/rules.d/10-dm.rules' as rules file
parse_file: reading '/lib/udev/rules.d/11-dm-lvm.rules' as rules file
parse_file: reading '/lib/udev/rules.d/13-dm-disk.rules' as rules file
parse_file: reading '/lib/udev/rules.d/40-isdn.rules' as rules file
parse_file: reading '/lib/udev/rules.d/40-redhat.rules' as rules file
parse_file: reading '/lib/udev/rules.d/42-qemu-usb.rules' as rules file
parse_file: reading '/lib/udev/rules.d/50-firmware.rules' as rules file
parse_file: reading '/lib/udev/rules.d/50-udev-default.rules' as rules file
parse_file: reading '/etc/udev/rules.d/55-usm.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-alias-kmsg.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-cdrom_id.rules' as rules file
parse_file: reading '/etc/udev/rules.d/60-fprint-autosuspend.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-net.rules' as rules file
parse_file: reading '/etc/udev/rules.d/60-pcmcia.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-alsa.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-input.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-serial.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-storage-tape.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-storage.rules' as rules file
parse_file: reading '/lib/udev/rules.d/60-persistent-v4l.rules' as rules file
parse_file: reading '/etc/udev/rules.d/60-raw.rules' as rules file
parse_file: reading '/etc/udev/rules.d/60-vboxadd.rules' as rules file
parse_file: reading '/lib/udev/rules.d/61-mobile-action.rules' as rules file
parse_file: reading '/lib/udev/rules.d/61-option-modem-modeswitch.rules' as rules file
parse_file: reading '/lib/udev/rules.d/61-persistent-storage-edd.rules' as rules file
parse_file: reading '/lib/udev/rules.d/64-device-mapper.rules' as rules file
parse_file: reading '/lib/udev/rules.d/64-md-raid.rules' as rules file
parse_file: reading '/lib/udev/rules.d/65-md-incremental.rules' as rules file
parse_file: reading '/lib/udev/rules.d/69-dm-lvm-metad.rules' as rules file
parse_file: reading '/lib/udev/rules.d/70-acl.rules' as rules file
parse_file: reading '/lib/udev/rules.d/70-cups-libusb.rules' as rules file
parse_file: reading '/lib/udev/rules.d/70-hid2hci.rules' as rules file
parse_file: reading '/etc/udev/rules.d/70-persistent-cd.rules' as rules file
parse_file: reading '/etc/udev/rules.d/70-persistent-net.rules' as rules file
parse_file: reading '/lib/udev/rules.d/71-biosdevname.rules' as rules file
parse_file: reading '/lib/udev/rules.d/75-cd-aliases-generator.rules' as rules file
parse_file: reading '/lib/udev/rules.d/75-net-description.rules' as rules file
parse_file: reading '/lib/udev/rules.d/75-persistent-net-generator.rules' as rules file
parse_file: reading '/lib/udev/rules.d/75-tty-description.rules' as rules file
parse_file: reading '/lib/udev/rules.d/78-sound-card.rules' as rules file
parse_file: reading '/lib/udev/rules.d/79-fstab_import.rules' as rules file
parse_file: reading '/lib/udev/rules.d/80-drivers.rules' as rules file
parse_file: reading '/lib/udev/rules.d/80-iosched.rules' as rules file
parse_file: reading '/lib/udev/rules.d/80-mpath-iosched.rules' as rules file
parse_file: reading '/lib/udev/rules.d/85-regulatory.rules' as rules file
parse_file: reading '/lib/udev/rules.d/88-clock.rules' as rules file
parse_file: reading '/lib/udev/rules.d/89-microcode.rules' as rules file
parse_file: reading '/etc/udev/rules.d/90-alsa.rules' as rules file
parse_file: reading '/lib/udev/rules.d/90-btrfs.rules' as rules file
parse_file: reading '/etc/udev/rules.d/90-hal.rules' as rules file
parse_file: reading '/lib/udev/rules.d/91-drm-modeset.rules' as rules file
parse_file: reading '/lib/udev/rules.d/95-dm-notify.rules' as rules file
parse_file: reading '/lib/udev/rules.d/95-keyboard-force-release.rules' as rules file
parse_file: reading '/lib/udev/rules.d/95-keymap.rules' as rules file
parse_file: reading '/lib/udev/rules.d/95-udev-late.rules' as rules file
parse_file: reading '/etc/udev/rules.d/98-kexec.rules' as rules file
parse_file: reading '/etc/udev/rules.d/99-oracle-asmdevices.rules' as rules file
parse_file: reading '/dev/.udev/rules.d/99-root.rules' as rules file
udev_rules_new: rules use 32448 bytes tokens (2704 * 12 bytes), 19085 bytes buffer
udev_rules_new: temporary index used 19500 bytes (975 * 20 bytes)
unable to open device '/sys/block/sdb/sdf1'
[root@collabn1 rules.d]#
[root@collabn1 rules.d]# /sbin/udevadm control --reload-rules
[root@collabn1 rules.d]# /sbin/start_udev
Starting udev: [ OK ]
[root@collabn1 rules.d]#
[root@collabn1 rules.d]# ls -l /dev/asm*
brw-rw----. 1 oracle dba 8, 17 Jan 22 00:07 /dev/asm-disk1
brw-rw----. 1 oracle dba 8, 33 Jan 22 00:07 /dev/asm-disk2
brw-rw----. 1 oracle dba 8, 49 Jan 22 00:07 /dev/asm-disk3
brw-rw----. 1 oracle dba 8, 65 Jan 22 00:05 /dev/asm-disk4
brw-rw----. 1 oracle dba 8, 81 Jan 22 00:05 /dev/asm-disk5


So I now have asm-disk5 as the new ASM Disk.  Let my try to add this disk.

SQL> set pages600
SQL> select group_number, disk_number, name, path, total_mb
2 from v$asm_disk
3 order by 1,2
4 /

GROUP_NUMBER DISK_NUMBER NAME
------------ ----------- ------------------------------
PATH
--------------------------------------------------------------------------------
TOTAL_MB
----------
0 0
/dev/asm-disk5
0

0 1
/dev/asm-disk4
0

1 0 DATA_0000
/dev/asm-disk1
5114

1 1 DATA_0001
/dev/asm-disk2
5114

2 0 FRA_0000
/dev/asm-disk3
5114


SQL>
SQL> alter diskgroup data add disk '/dev/asm-disk5';

Diskgroup altered.

SQL>
SQL> alter diskgroup data add disk '/dev/asm-disk5';

Diskgroup altered.

SQL> select group_number, name, total_mb
2 from v$asm_diskgroup
3 order by 1,2
4 /

GROUP_NUMBER NAME TOTAL_MB
------------ ------------------------------ ----------
1 DATA 22512
2 FRA 5114

SQL>
SQL> select group_number, name, type
2 from v$asm_diskgroup
3 order by 1,2
4 /

GROUP_NUMBER NAME TYPE
------------ ------------------------------ ------
1 DATA EXTERN
2 FRA EXTERN

SQL>
SQL> select group_number, name, compatibility, database_compatibility
2 from v$asm_diskgroup
3 order by 1
4 /

GROUP_NUMBER NAME
------------ ------------------------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY
------------------------------------------------------------
1 DATA
12.1.0.0.0
10.1.0.0.0

2 FRA
12.1.0.0.0
10.1.0.0.0


SQL>
SQL> select group_number, disk_number, name, path, total_mb
2 from v$asm_disk
3 order by 1,2,3
4 /

GROUP_NUMBER DISK_NUMBER NAME
------------ ----------- ------------------------------
PATH
--------------------------------------------------------------------------------
TOTAL_MB
----------
0 0
/dev/asm-disk4
0

1 0 DATA_0000
/dev/asm-disk1
5114

1 1 DATA_0001
/dev/asm-disk2
5114

1 2 DATA_0002
/dev/asm-disk5
12284

2 0 FRA_0000
/dev/asm-disk3
5114


SQL>


According to Oracle Support Document 1938950.1, adding a disk of a different size to an existing DiskGroup fails with an error ORA-15410 in 12.1.0.2.  However, that seems to apply to NORMAL or HIGH Redundancy and COMPATIBLE.ASM 12.1.0.2.   Here, I have EXTERNAL Redundancy and COMPATIBLE.ASM 12.1.0.0.0

Do I recommend Disks of different sizes ?  Absolutely *not* in Production.  This is a "play" environment in Virtual Machines on my desktop that I can destroy and recreate anytime.  I can monitor disk usage as well.
.
.
.

Categories: DBA Blogs

12cR1 RAC Posts -- 3 : Convert PolicyManaged DB back to AdminManaged

Sun, 2017-01-15 20:35
In the previous post on 12cR1 RAC, I had converted my AdminManaged Database to PolicyManaged.

Here, I convert it back to AdminManaged.

First, I verify that the database is shutdown (note that I have only 1 node of the cluster currently up and running, I don't need both nodes and instances up).

[oracle@collabn1 ~]$ srvctl status database -d RAC
Instance RAC_1 is running on node collabn1
[oracle@collabn1 ~]$ srvctl stop database -d RAC
[oracle@collabn1 ~]$ ps -fuoracle |grep smon
oracle 3422 1 0 09:49 ? 00:00:00 asm_smon_+ASM1
oracle 4882 1 0 09:50 ? 00:00:00 mdb_smon_-MGMTDB
oracle 16889 9821 0 10:08 pts/0 00:00:00 grep smon
[oracle@collabn1 ~]$


Next, I remove the database from the Cluster Registry.

[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file:
Domain: racattack
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: MyPool
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is policy managed
[oracle@collabn1 ~]$ srvctl remove database -d RAC
Remove the database RAC? (y/[n]) y
[oracle@collabn1 ~]$ srvctl config database -d RAC
PRCD-1120 : The resource for database RAC could not be found.
PRCR-1001 : Resource ora.rac.db does not exist
[oracle@collabn1 ~]$


I then remove the defined Server Pool that I used for this database.

[oracle@collabn1 ~]$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: MyPool
Importance: 100, Min: 1, Max: 2
Category:
Candidate server names: collabn1,collabn2
[oracle@collabn1 ~]$ srvctl remove srvpool -serverpool MyPool
[oracle@collabn1 ~]$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
[oracle@collabn1 ~]$


I then add the database back into the Cluster Registry.

[oracle@collabn1 ~]$ srvctl add database -d RAC \
> -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 \
> -pwfile +DATA/RAC/PASSWORD/pwdrac.277.931824933
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file: +DATA/RAC/PASSWORD/pwdrac.277.931824933
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is administrator managed
[oracle@collabn1 ~]$


I start the second node of the cluster before I configure the instances (Note : I have the $ORACLE_HOME/dbs pfiles created in advance).

[oracle@collabn1 ~]$ srvctl add instance -d RAC -i RAC1 -n collabn1
[oracle@collabn1 ~]$ srvctl add instance -d RAC -i RAC2 -n collabn2
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file: +DATA/RAC/PASSWORD/pwdrac.277.931824933
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: RAC1,RAC2
Configured nodes: collabn1,collabn2
Database is administrator managed
[oracle@collabn1 ~]$


I am now ready to start the database (instances).

[oracle@collabn1 ~]$ srvctl start database -d RAC
[oracle@collabn1 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node collabn1
Instance RAC2 is running on node collabn2
[oracle@collabn1 ~]$ ps -fuoracle |grep smon
oracle 3422 1 0 09:49 ? 00:00:00 asm_smon_+ASM1
oracle 4882 1 0 09:50 ? 00:00:00 mdb_smon_-MGMTDB
oracle 25431 1 0 10:30 ? 00:00:00 ora_smon_RAC1
oracle 27533 9821 0 10:33 pts/0 00:00:00 grep smon
[oracle@collabn1 ~]$
[root@collabn2 ~]# ps -fuoracle |grep smon
oracle 3460 1 0 10:19 ? 00:00:00 asm_smon_+ASM2
oracle 9561 1 0 10:30 ? 00:00:00 ora_smon_RAC2
[root@collabn2 ~]#
[oracle@collabn1 ~]$ env |grep SID
ORACLE_SID=RAC1
[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Jan 16 10:34:15 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 * from v$containers;

CON_ID DBID CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME OPEN_MODE RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
1 2519807290 1 FD9AC20F64D344D7E043B6A9E80A2F2F
CDB$ROOT READ WRITE NO
16-JAN-17 10.31.34.014 AM +08:00
0 0 8192 ENABLED 0


CON_ID DBID CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME OPEN_MODE RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
2 2061548092 2061548092 44BB5E17F41A2618E053334EA8C006B9
PDB$SEED READ ONLY NO
16-JAN-17 10.31.34.859 AM +08:00
1594413 859832320 8192 ENABLED 0


CON_ID DBID CON_UID GUID
---------- ---------- ---------- --------------------------------
NAME OPEN_MODE RES
------------------------------ ---------- ---
OPEN_TIME
---------------------------------------------------------------------------
CREATE_SCN TOTAL_SIZE BLOCK_SIZE RECOVERY SNAPSHOT_PARENT_CON_ID
---------- ---------- ---------- -------- ----------------------
3 1857084550 1857084550 44BBC69CE8F552AEE053334EA8C07365
PDB MOUNTED

1755977 0 8192 ENABLED 0


SQL> alter pluggable database PDB open;

Pluggable database altered.

SQL>
SQL> select con_id, name, open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB READ WRITE

SQL>


Thus, I converted my PolicyManaged database to AdministratorManaged.
.
.
.

Categories: DBA Blogs

Copying a Tablespace from NonCDB to a PDB (using TTS)

Sun, 2017-01-15 02:54
A Tablespace can be "transported"  from a NonCDB to a PDB as a way of copying the Tablespace.  Here I work with ASM as well.

First in the NonCDB :

[oracle@ora12102 Desktop]$ . oraenv
ORACLE_SID = [oracle] ? NONCDB
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:03:43 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 402653184 bytes
Fixed Size 2924928 bytes
Variable Size 260050560 bytes
Database Buffers 134217728 bytes
Redo Buffers 5459968 bytes
Database mounted.
Database opened.
SQL> select file_name, bytes/1048576
2 from dba_data_files
3 where tablespace_name = 'EXAMPLE'
4 /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
+DATA/NONCDB/DATAFILE/example.266.896482777
1243.75


SQL>
[oracle@ora12102 Desktop]$ expdp hemant/hemant \
> directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp \
> transport_tablespaces=EXAMPLE transport_full_check=Y

Export: Release 12.1.0.2.0 - Production on Sun Jan 15 16:08:27 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01": hemant/******** directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp transport_tablespaces=EXAMPLE transport_full_check=Y
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'EXAMPLE' is not read only
Job "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at Sun Jan 15 16:08:41 2017 elapsed 0 00:00:06

[oracle@ora12102 Desktop]$


The tablespace has to be set READ ONLY before we can use export to transport it (also, it should be READ ONLY while the data files are being copied.

[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:09:10 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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter tablespace example read only;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ expdp hemant/hemant \
> directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp \
> transport_tablespaces=EXAMPLE transport_full_check=Y

Export: Release 12.1.0.2.0 - Production on Sun Jan 15 16:09:58 2017

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01": hemant/******** directory=data_pump_dir dumpfile=EXAMPLE_TTS.dmp transport_tablespaces=EXAMPLE transport_full_check=Y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HEMANT.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/NONCDB/dpdump/EXAMPLE_TTS.dmp
******************************************************************************
Datafiles required for transportable tablespace EXAMPLE:
+DATA/NONCDB/DATAFILE/example.266.896482777
Job "HEMANT"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Sun Jan 15 16:12:48 2017 elapsed 0 00:02:46

[oracle@ora12102 Desktop]$


Now, I need to copy the datafile (while the tablespace is READ ONLY).

[oracle@ora12102 Desktop]$ su - grid
Password:
[grid@ora12102 ~]$ asmcmd
ASMCMD> cp +DATA/NONCDB/DATAFILE/example.266.896482777 /tmp/example.dbf
copying +DATA/NONCDB/DATAFILE/example.266.896482777 -> /tmp/example.dbf
ASMCMD>
ASMCMD> exit
[grid@ora12102 ~]$ exit
logout
[oracle@ora12102 Desktop]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:16:24 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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> alter tablespace example read write ;

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$


I must now identify the target location for the datafile in the CDB database.

[oracle@ora12102 Desktop]$ . oraenv
ORACLE_SID = [NONCDB] ? CDB1
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ora12102 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:17:44 2017

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1644167168 bytes
Fixed Size 2925024 bytes
Variable Size 973082144 bytes
Database Buffers 654311424 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
SQL> alter pluggable database pdb1 open;

Pluggable database altered.

SQL>
SQL> alter session set container=PDB1;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/system.284.914408541
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/sysaux.285.914408541
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/users.287.914408663
+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/hemant.288.914713623

SQL>


Now that I have identiied the default location for all PDB1 files, I need to use ASMCMD to copy the datafile.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ su - grid
Password:
[grid@ora12102 ~]$
[grid@ora12102 ~]$ asmcmd
ASMCMD> cp /tmp/example.dbf +DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/
copying /tmp/example.dbf -> +DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/example.dbf
ASMCMD> cd +DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE
ASMCMD> ls
HEMANT.288.914713623
SYSAUX.285.914408541
SYSTEM.284.914408541
USERS.287.914408663
example.dbf
ASMCMD> exit
[grid@ora12102 ~]$


Now, I need to import the tablespace with the datafile. Before that, I need to setup the user that will do the import and all the users of the target tablespace.

[grid@ora12102 ~]$ exit
logout
[oracle@ora12102 Desktop]$
[oracle@ora12102 Desktop]$ sqlplus system/oracle@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:28:16 2017

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

ERROR:
ORA-28002: the password will expire within 7 days


Last Successful login time: Sun Jan 15 2017 16:27:18 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> grant select_catalog_role, imp_full_database to hemant;

Grant succeeded.

SQL> select privilege from dba_sys_privs where grantee = 'HEMANT';

PRIVILEGE
----------------------------------------
CREATE TABLE
CREATE SESSION

SQL> select granted_role from dba_role_privs where grantee = 'HEMANT';

GRANTED_ROLE
--------------------------------------------------------------------------------
SELECT_CATALOG_ROLE
IMP_FULL_DATABASE

SQL>


As with the Export, I am using a non-DBA user for the import.  I also have to setup the users and their grants.

[oracle@ora12102 Desktop]$ sqlplus sys/oracle@PDB1 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:33: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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create directory imp_from_noncdb as '/u01/app/oracle/admin/NONCDB/dpdump';

Directory created.

SQL> grant read, write on directory imp_from_noncdb to hemant;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$
[oracle@ora12102 Desktop]$ sqlplus system/oracle@PDB1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jan 15 16:41:36 2017

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

ERROR:
ORA-28002: the password will expire within 7 days


Last Successful login time: Sun Jan 15 2017 16:28:16 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create user HR identified by HR ;
create user IX identified by IX ;
create user OE identified by OE ;
create user PM identified by PM ;
create user SH identified by SH ;

User created.

SQL>
User created.

SQL>
User created.

SQL>
User created.

SQL>
User created.

SQL> SQL>
SQL> @grants_to_EXAMPLE
SQL> spool grants_to_EXAMPLE
SQL>
SQL> grant ALTER SESSION to HR ;

Grant succeeded.

SQL> grant ALTER SESSION to IX ;

Grant succeeded.

SQL> grant ALTER SESSION to SH ;

Grant succeeded.

SQL> grant CREATE CLUSTER to IX ;

Grant succeeded.

SQL> grant CREATE CLUSTER to SH ;

Grant succeeded.

SQL> grant CREATE DATABASE LINK to HR ;

Grant succeeded.

SQL> grant CREATE DATABASE LINK to IX ;

Grant succeeded.

SQL> grant CREATE DATABASE LINK to OE ;

Grant succeeded.

SQL> grant CREATE DATABASE LINK to SH ;

Grant succeeded.

SQL> grant CREATE DIMENSION to SH ;

Grant succeeded.

SQL> grant CREATE INDEXTYPE to IX ;

Grant succeeded.

SQL> grant CREATE MATERIALIZED VIEW to OE ;

Grant succeeded.

SQL> grant CREATE MATERIALIZED VIEW to SH ;

Grant succeeded.

SQL> grant CREATE OPERATOR to IX ;

Grant succeeded.

SQL> grant CREATE PROCEDURE to HR ;

Grant succeeded.

SQL> grant CREATE PROCEDURE to IX ;

Grant succeeded.

SQL> grant CREATE RULE to IX ;

Grant succeeded.

SQL> grant CREATE RULE SET to IX ;

Grant succeeded.

SQL> grant CREATE SEQUENCE to HR ;

Grant succeeded.

SQL> grant CREATE SEQUENCE to IX ;

Grant succeeded.

SQL> grant CREATE SEQUENCE to SH ;

Grant succeeded.

SQL> grant CREATE SESSION to HR ;

Grant succeeded.

SQL> grant CREATE SESSION to IX ;

Grant succeeded.

SQL> grant CREATE SESSION to OE ;

Grant succeeded.

SQL> grant CREATE SESSION to SH ;

Grant succeeded.

SQL> grant CREATE SYNONYM to HR ;

Grant succeeded.

SQL> grant CREATE SYNONYM to IX ;

Grant succeeded.

SQL> grant CREATE SYNONYM to OE ;

Grant succeeded.

SQL> grant CREATE SYNONYM to SH ;

Grant succeeded.

SQL> grant CREATE TABLE to IX ;

Grant succeeded.

SQL> grant CREATE TABLE to SH ;

Grant succeeded.

SQL> grant CREATE TRIGGER to IX ;

Grant succeeded.

SQL> grant CREATE TYPE to IX ;

Grant succeeded.

SQL> grant CREATE VIEW to HR ;

Grant succeeded.

SQL> grant CREATE VIEW to IX ;

Grant succeeded.

SQL> grant CREATE VIEW to OE ;

Grant succeeded.

SQL> grant CREATE VIEW to SH ;

Grant succeeded.

SQL> grant QUERY REWRITE to OE ;

Grant succeeded.

SQL> grant QUERY REWRITE to SH ;

Grant succeeded.

SQL> grant SELECT ANY DICTIONARY to IX ;

Grant succeeded.

SQL> grant UNLIMITED TABLESPACE to HR ;

Grant succeeded.

SQL> grant UNLIMITED TABLESPACE to IX ;

Grant succeeded.

SQL> grant UNLIMITED TABLESPACE to OE ;

Grant succeeded.

SQL> grant UNLIMITED TABLESPACE to PM ;

Grant succeeded.

SQL> grant UNLIMITED TABLESPACE to SH ;

Grant succeeded.

SQL>
SQL> spool off
SQL> @roles_to_EXAMPLE
SQL> set echo on
SQL> spool roles_to_EXAMPLE
SQL>
SQL> grant AQ_ADMINISTRATOR_ROLE to IX ;

Grant succeeded.

SQL> grant AQ_USER_ROLE to IX ;

Grant succeeded.

SQL> grant CONNECT to IX ;

Grant succeeded.

SQL> grant CONNECT to PM ;

Grant succeeded.

SQL> grant RESOURCE to HR ;

Grant succeeded.

SQL> grant RESOURCE to IX ;

Grant succeeded.

SQL> grant RESOURCE to OE ;

Grant succeeded.

SQL> grant RESOURCE to PM ;

Grant succeeded.

SQL> grant RESOURCE to SH ;

Grant succeeded.

SQL> grant SELECT_CATALOG_ROLE to IX ;

Grant succeeded.

SQL> grant SELECT_CATALOG_ROLE to SH ;

Grant succeeded.

SQL> grant XDBADMIN to OE ;

Grant succeeded.

SQL>
SQL> spool off
SQL>


I am now ready to import the tablespace and datafile.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
[oracle@ora12102 Desktop]$ impdp hemant/hemant@PDB1 \
> dumpfile=EXAMPLE_TTS.dmp directory=imp_from_noncdb \
> transport_datafiles=+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/example.dbf

Import: Release 12.1.0.2.0 - Production on Sun Jan 15 16:50:16 2017

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

UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "HEMANT"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source time zone is +00:00 and target time zone is -07:00.
Starting "HEMANT"."SYS_IMPORT_TRANSPORTABLE_01": hemant/********@PDB1 dumpfile=EXAMPLE_TTS.dmp directory=imp_from_noncdb transport_datafiles=+DATA/CDB1/35208E5B92306007E0530F02000A969A/DATAFILE/example.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/XMLSCHEMA/XMLSCHEMA
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39360: Table "OE"."ORDERS" was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.
ORA-39151: Table "OE"."PURCHASEORDER" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."PROMOTIONS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."PRODUCT_DESCRIPTIONS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."PRODUCT_INFORMATION" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."INVENTORIES" TO "BI"
ORA-39112: Dependent object type OBJECT_GRANT:"OE" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"OE" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."ORDER_ITEMS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."WAREHOUSES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "OE"."CUSTOMERS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."COSTS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."SALES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."COUNTRIES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."CUSTOMERS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."PROMOTIONS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."CHANNELS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."PRODUCTS" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."TIMES" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."FWEEK_PSCAT_SALES_MV" TO "BI"
ORA-39083: Object type OBJECT_GRANT failed to create with error:
ORA-01917: user or role 'BI' does not exist
Failing sql is:
GRANT SELECT ON "SH"."CAL_MONTH_SALES_MV" TO "BI"
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"OE"."ORD_SALES_REP_IX" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"OE"."ORD_ORDER_DATE_IX" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"OE"."ORD_CUSTOMER_IX" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type INDEX:"OE"."ORDER_PK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"OE"."ORDER_MODE_LOV" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"OE"."ORDER_TOTAL_MIN" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"OE"."ORDER_PK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT:"OE"."ORDER_ITEMS_ORDER_ID_FK" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "OE"."ORDER_ITEMS" ADD CONSTRAINT "ORDER_ITEMS_ORDER_ID_FK" FOREIGN KEY ("ORDER_ID") REFERENCES "OE"."ORDERS" ("ORDER_ID") ON DELETE CASCADE ENABLE NOVALIDATE
ORA-39112: Dependent object type REF_CONSTRAINT:"OE"."ORDERS_SALES_REP_FK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"OE"."ORDERS_CUSTOMER_ID_FK" skipped, base object type TABLE:"OE"."ORDERS" creation failed
Processing object type TRANSPORTABLE_EXPORT/INDEX/BITMAP_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings
ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings
Job "HEMANT"."SYS_IMPORT_TRANSPORTABLE_01" completed with 41 error(s) at Sun Jan 15 16:51:40 2017 elapsed 0 00:01:23

[oracle@ora12102 Desktop]$


The key error is the failure on the ORDERS table creation because of a TimeZone mismatch !   So, there is a lesson to be learnt !

.
.
.

Categories: DBA Blogs

V$RMAN_BACKUP_JOB_DETAILS, a caveat

Sun, 2017-01-08 23:46
Building on a previous blog post (you could read it before or after this post), here's a quick demo of a caveat or quirk with V$RMAN_BACKUP_JOB_DETAILS.

This in 11.2.0.4

[oracle@ora11204 Desktop]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jan 9 13:39:44 2017

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

connected to target database: ORCL (DBID=1362461976)

RMAN> backup as compressed backupset
2> incremental level 1 database
3> plus archivelog ;


Starting backup at 09-JAN-17
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=108 RECID=1245 STAMP=928093294
input archived log thread=1 sequence=109 RECID=1246 STAMP=928093719
input archived log thread=1 sequence=110 RECID=1247 STAMP=928093722
input archived log thread=1 sequence=111 RECID=1248 STAMP=928093724
...
...
...
input archived log thread=1 sequence=163 RECID=1318 STAMP=929802055
input archived log thread=1 sequence=164 RECID=1319 STAMP=932823436
input archived log thread=1 sequence=165 RECID=1320 STAMP=932823439
input archived log thread=1 sequence=166 RECID=1321 STAMP=932823606
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_annnn_TAG20170109T134007_d768kr8l_.bkp tag=TAG20170109T134007 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 09-JAN-17

Starting backup at 09-JAN-17
using channel ORA_DISK_1
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL/sysaux.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL/system.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL/undotbs1.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL/users.dbf
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_nnnd0_TAG20170109T134123_d768n3v5_.bkp tag=TAG20170109T134123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting compressed incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_c552qnsh_.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_intermed_c552qpc7_.dbf
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_nnnd1_TAG20170109T134123_d768ojmm_.bkp tag=TAG20170109T134123 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 09-JAN-17

Starting backup at 09-JAN-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=167 RECID=1322 STAMP=932823743
channel ORA_DISK_1: starting piece 1 at 09-JAN-17
channel ORA_DISK_1: finished piece 1 at 09-JAN-17
piece handle=/u02/FRA/ORCL/backupset/2017_01_09/o1_mf_annnn_TAG20170109T134223_d768ozv9_.bkp tag=TAG20170109T134223 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-JAN-17

Starting Control File and SPFILE Autobackup at 09-JAN-17
piece handle=/u02/FRA/ORCL/autobackup/2017_01_09/o1_mf_s_932823745_d768p1jo_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 09-JAN-17

RMAN>


What does V$RMAN_BACKUP_JOB_DETAILS tell us ?

SQL> l
1 select to_char(start_time,'DD-MON HH24:MI') Start_At,
2 to_char(end_time,'DD-MON HH24:MI') End_At,
3 input_bytes/1048576 Input_MB, output_bytes/1048576 Output_MB,
4 input_type, status
5 from v$rman_backup_job_details
6 where start_time > trunc(sysdate)
7* order by start_time
SQL> /

START_AT END_AT INPUT_MB OUTPUT_MB INPUT_TYPE
--------------------- --------------------- ---------- ---------- -------------
STATUS
-----------------------
09-JAN 13:40 09-JAN 13:42 2917.06055 491.563477 DB INCR
COMPLETED


SQL>


The view does NOT show how much of the input/output was for ArchiveLogs.  It clubs ArchiveLogs and the controlfile autobackup under the   single entry for "DB INCR".   Anyone reading this row from V$RMAN_BACKUP_JOB_DETAILS would NOT know if ArchiveLogs had been backed-up, would NOT know if a controlfile/spfile autobackup was created.
Furtheremore, if there is a failure (e.g. only the last ArchiveLog backupset failed ?), would you be able to identify what has successfully been backed up.  Also see my previous blog post.
.
.
.


Categories: DBA Blogs

12cR1 RAC Posts -- 2 : Convert AdminManaged DB to PolicyManaged

Fri, 2016-12-30 22:14
I have an AdminManaged Database in my (2node) RAC Cluster.

How do I convert it to PolicyManaged ?

(Yes, let me admit :  It makes no sense to have PolicyManaged on a 2node Cluster.  But since I can't create an 8 or 16 node Cluster (with multiple databases to boot ?!), let me demonstrate with a 2node  Cluster.  The principle remains the same).

First, I show the configuration of the database in the Cluster :
[oracle@collabn1 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node collabn1
Instance RAC2 is running on node collabn2
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/spfileRAC1.ora
Password file: +DATA/RAC/PASSWORD/pwdrac.277.931824933
Domain: racattack
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: FRA,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances: RAC1,RAC2
Configured nodes: collabn1,collabn2
Database is administrator managed
[oracle@collabn1 ~]$


This is the current definition of server pool(s) :
[oracle@collabn1 ~]$ srvctl config srvpool
Server pool name: Free
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names:
Server pool name: Generic
Importance: 0, Min: 0, Max: -1
Category:
Candidate server names: collabn1,collabn2
[oracle@collabn1 ~]$


So, we see that I don't have any Server Pools defined. Only the default FREE and GENERIC (for AdminManaged database(s)) exist.
I now proceed to remove the database from the configuration.
[oracle@collabn1 ~]$ srvctl stop database -d RAC
[oracle@collabn1 ~]$ srvctl remove database -d RAC
Remove the database RAC? (y/[n]) y
[oracle@collabn1 ~]$


I now create a new (custom) Server Pool (called "MyPool").
[oracle@collabn1 ~]$ srvctl add srvpool -serverpool MyPool -importance 100 -min 1 -max 2 \
> -servers "collabn1,collabn2" -verbose
[oracle@collabn1 ~]$ srvctl config srvpool -serverpool MyPool
Server pool name: MyPool
Importance: 100, Min: 1, Max: 2
Category:
Candidate server names: collabn1,collabn2
[oracle@collabn1 ~]$


So, now with an "upto 2nodes" Server Pool, I add my database to it.
[oracle@collabn1 ~]$ srvctl add database -d RAC -oraclehome /u01/app/oracle/product/12.1.0/dbhome_1 \
> -serverpool MyPool -verbose
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: MyPool
Disk Groups:
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is policy managed
[oracle@collabn1 ~]$


This shows that RAC is now a PolicyManaged database in the "MyPool" Server Pool !
Can I now start the database and check on the instance(s) ?
[oracle@collabn1 ~]$ srvctl start database -d RAC
PRCR-1079 : Failed to start resource ora.rac.db
CRS-5017: The resource action "ora.rac.db start" encountered the following error:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initRAC_2.ora'
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/collabn2/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.rac.db' on 'collabn2' failed
CRS-5017: The resource action "ora.rac.db start" encountered the following error:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initRAC_1.ora'
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/collabn1/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.rac.db' on 'collabn1' failed
CRS-2632: There are no more servers to try to place resource 'ora.rac.db' on that would satisfy its placement policy
[oracle@collabn1 ~]$


Yes, of course. I need to create initRAC_1.ora and initRAC_2.ora.
After creating the new parameter files (pointing to the SPFILE in the ASM Diskgroup), I try again.
[oracle@collabn1 ~]$ srvctl start database -d RAC
[oracle@collabn1 ~]$ srvctl status database -d RAC
Instance RAC_1 is running on node collabn1
Instance RAC_2 is running on node collabn2
[oracle@collabn1 ~]$ ps -ef |grep smon
oracle 3447 1 0 11:37 ? 00:00:00 asm_smon_+ASM1
root 3605 1 0 11:37 ? 00:00:11 /u01/app/12.1.0/grid/bin/osysmond.bin
oracle 4203 1 0 11:38 ? 00:00:00 mdb_smon_-MGMTDB
oracle 22882 1 0 12:08 ? 00:00:00 ora_smon_RAC_1
oracle 23422 12657 0 12:10 pts/0 00:00:00 grep smon
[oracle@collabn1 ~]$
[oracle@collabn2 ~]$ ps -ef |grep smon
oracle 3495 1 0 11:41 ? 00:00:00 asm_smon_+ASM2
root 3593 1 0 11:41 ? 00:00:09 /u01/app/12.1.0/grid/bin/osysmond.bin
oracle 15973 1 0 12:08 ? 00:00:00 ora_smon_RAC_2
oracle 16647 4582 0 12:10 pts/0 00:00:00 grep smon
[oracle@collabn2 ~]$
[oracle@collabn1 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name:
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile:
Password file:
Domain: racattack
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: MyPool
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group:
Database instances:
Configured nodes:
Database is policy managed
[oracle@collabn1 ~]$


Yes, I now have instances RAC_1 and RAC_2 (instead of RAC1 and RAC2) now running. If I had multiple (4 or more ?!) nodes (and a Server Pool configuration to match), there's no guarantee that RAC_1 starts on collabn1 (and RAC_2 on collabn2).  These are "floating" instances that can start on any nodes in the Cluster.


(UPDATE : It seems that when I shutdown a node, a PolicyManaged Instance is SHUTDOWN ABORT, unlike an AdminManaged Instance which gets SHUTDOWN NORMAL ?)

.
.
.

Categories: DBA Blogs

12cR1 RAC Posts -- 1 : Grid Infrastructure Install completed (first cycle)

Sat, 2016-12-24 09:17
Just as I had posted 11gR2 RAC Posts in 2014  (listed here), I plan to post some 12cR1 RAC (GI, ASM) posts over the next few weeks.

Here's my Grid Infrastructure up and running.  (Yes, I used racattack for this first 12cR1 setup.

[root@collabn1 ~]# crsctl status resource -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.SHARED.advm
ONLINE ONLINE collabn1 Volume device /dev/a
sm/shared-141 is onl
ine,STABLE
ONLINE ONLINE collabn2 Volume device /dev/a
sm/shared-141 is onl
ine,STABLE
ora.DATA.dg
ONLINE ONLINE collabn1 STABLE
ONLINE ONLINE collabn2 STABLE
ora.FRA.dg
ONLINE ONLINE collabn1 STABLE
ONLINE ONLINE collabn2 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE collabn1 STABLE
ONLINE ONLINE collabn2 STABLE
ora.asm
ONLINE ONLINE collabn1 Started,STABLE
ONLINE ONLINE collabn2 Started,STABLE
ora.data.shared.acfs
ONLINE ONLINE collabn1 mounted on /shared,S
TABLE
ONLINE ONLINE collabn2 mounted on /shared,S
TABLE
ora.net1.network
ONLINE ONLINE collabn1 STABLE
ONLINE ONLINE collabn2 STABLE
ora.ons
ONLINE ONLINE collabn1 STABLE
ONLINE ONLINE collabn2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE collabn2 STABLE
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE collabn1 STABLE
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE collabn1 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE collabn1 169.254.3.70 172.16.
100.51,STABLE
ora.collabn1.vip
1 ONLINE ONLINE collabn1 STABLE
ora.collabn2.vip
1 ONLINE ONLINE collabn2 STABLE
ora.cvu
1 ONLINE ONLINE collabn1 STABLE
ora.mgmtdb
1 ONLINE ONLINE collabn1 Open,STABLE
ora.oc4j
1 ONLINE ONLINE collabn1 STABLE
ora.scan1.vip
1 ONLINE ONLINE collabn2 STABLE
ora.scan2.vip
1 ONLINE ONLINE collabn1 STABLE
ora.scan3.vip
1 ONLINE ONLINE collabn1 STABLE
--------------------------------------------------------------------------------
[root@collabn1 ~]#
[root@collabn1 ~]# crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 96fbcb40bfeb4ff7bf18881adcfef149 (/dev/asm-disk1) [DATA]
Located 1 voting disk(s).
[root@collabn1 ~]#
[root@collabn1 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 4
Total space (kbytes) : 409568
Used space (kbytes) : 1632
Available space (kbytes) : 407936
ID : 827167720
Device/File Name : +DATA
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@collabn1 ~]#
[root@collabn1 ~]# nslookup collabn-cluster-scan
Server: 192.168.78.51
Address: 192.168.78.51#53

Name: collabn-cluster-scan.racattack
Address: 192.168.78.252
Name: collabn-cluster-scan.racattack
Address: 192.168.78.253
Name: collabn-cluster-scan.racattack
Address: 192.168.78.251

[root@collabn1 ~]#


I hope to run a few cycles of setups, switching to different node names, IPs, DiskGroup names etc over the next few weeks).
.
.
.

Categories: DBA Blogs

12.2 New Features -- 5 : Memory Parameters for Pluggable Database

Tue, 2016-12-06 08:07
12.2 allows Instance Memory parameters to be configured at the PDB level.

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

SQL*Plus: Release 12.2.0.1.0 Production on Tue Dec 6 13:56:28 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 2544M
sga_min_size big integer 0
sga_target big integer 2544M
unified_audit_sga_queue_size integer 1048576
SQL> show parameter db_cach

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 0
SQL>


Those are parameters set at the CDB level. Let's see the PDB.

SQL> alter session set container = PDB1;

Session altered.

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 2544M
sga_min_size big integer 0
sga_target big integer 0
unified_audit_sga_queue_size integer 1048576
SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 0
SQL> alter system set db_cache_size=400M;

System altered.

SQL>
SQL> alter system set sga_target=512M;
alter system set sga_target=512M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-56750: invalid value 536870912 for parameter sga_target; must be larger
than 200% of parameter db_cache_size


SQL> alter system set sga_target=810M;

System altered.

SQL> alter system set shared_pool_size=256M;

System altered.

SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 400M
SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 810M
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 26004684
shared_pool_size big integer 256M
SQL>
SQL> alter system set pga_aggregate_target=128M;

System altered.

SQL>


Returning to the CDB ...

SQL> connect / as sysdba
Connected.
SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string ON
db_cache_size big integer 0
SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 2544M
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 26004684
shared_pool_size big integer 0
SQL> show parameter pga_aggergate_target
SQL> show parameter pga_aggregate_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 1775294400
SQL>


Thus, multiple PDBs can have their own private target and limits (even an SGA_MIN_SIZE) all shared within the single instance that they co-exist in.
Note : The requirement is that MEMORY_TARGET is to be not set.
.
.
.

Categories: DBA Blogs

12.2 New Features -- 4 : AWR for Pluggable Database

Thu, 2016-12-01 03:29
12.2 now allows AWR Snapshots and Reports to be created at the PDB level.

Here I demonstrate a Manual Snapshot.  Although Automatic PDB AWR Snapshots are possible (with the AWR_PDB_AUTOFLUSH_ENABLED parameter) , they are disabled by default and Oracle recommends Manual Snapshots.

SQL> connect / as sysdba
Connected.
SQL> alter session set container=PDB1;

Session altered.

SQL> exec dbms_workload_repository.create_snapshot();

PL/SQL procedure successfully completed.

SQL>


I then proceed to create an AWR Report, still in the PDB1 container.

SQL> @?/rdbms/admin/awrrpt

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.

'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report

Enter value for report_type: text

Type Specified: text

Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
Enter value for awr_location: AWR_PDB




Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance Container Name
-------------- -------------- -------------- -------------- --------------
3774315809 HKCORCL 1 HKCORCL PDB1


Root DB Id Container DB Id AWR DB Id
--------------- --------------- ---------------
947935822 3774315809 3774315809









Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
3774315809 1 HKCORCL HKCORCL HKCORCL.comp

Using 3774315809 for database Id
Using 1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing without
specifying a number lists all completed snapshots.


Enter value for num_days: 1

Listing the last day's Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------

HKCORCL HKCORCL 1 01 Dec 2016 08:48 1
2 01 Dec 2016 08:49 1
3 01 Dec 2016 08:52 1


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 3
End Snapshot Id specified: 3




Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_1_3.txt. To use this name,
press to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_1_3.txt


Here's a look at the header of the AWR report.

WORKLOAD REPOSITORY PDB report (PDB snapshots)

DB Name DB Id Unique Name DB Role Edition Release RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
HKCORCL 3774315809 HKCORCL PRIMARY EE 12.2.0.1.0 NO NO

Instance Inst Num Startup Time
------------ -------- ---------------
HKCORCL 1 16-Nov-16 06:13

PDB Name PDB Id PDB DB Id Open Time
------------ ------ ---------- ---------------
PDB1 3 3774315809 25-Nov-16 14:11

Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
HKCORCL.compute- Linux x86 64-bit 2 2 1 7.05

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 1 01-Dec-16 08:48:46 0 4.0
End Snap: 3 01-Dec-16 08:52:08 1 12.0
Elapsed: 3.36 (mins)
DB Time: 0.29 (mins)

Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 0.1 4.3 0.00 0.06
DB CPU(s): 0.1 2.9 0.00 0.04
Background CPU(s): 0.0 0.0 0.00 0.00
Redo size (bytes): 138,443.8 6,976,599.0
Logical read (blocks): 1,798.4 90,625.0
Block changes: 282.3 14,224.3
Physical read (blocks): 21.0 1,055.8
Physical write (blocks): 0.7 34.5
Read IO requests: 20.9 1,051.8
Write IO requests: 0.3 12.5
Read IO (MB): 0.2 8.3
Write IO (MB): 0.0 0.3
IM scan rows: 0.0 0.0
Session Logical Read IM: 0.0 0.0
User calls: 1.5 77.5
Parses (SQL): 17.9 904.0
Hard parses (SQL): 3.2 161.5
SQL Work Area (MB): 2.5 123.5
Logons: 0.0 1.0
Executes (SQL): 45.7 2,302.3
Rollbacks: 0.0 0.0
Transactions: 0.0

Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Total Wait Avg % DB Wait
Event Waits Time (sec) Wait time Class
------------------------------ ----------- ---------- --------- ------ --------
DB CPU 11.5 66.9
db file sequential read 3,758 2.5 667.74us 14.6 User I/O
direct path write 23 .7 29.08ms 3.9 User I/O
flashback log file sync 36 .6 16.98ms 3.6 User I/O
local write wait 12 .4 37.17ms 2.6 User I/O
acknowledge over PGA limit 9 .1 9.50ms .5 Schedule
control file sequential read 189 .1 293.42us .3 System I
PGA memory operation 3,687 0 11.02us .2 Other
db file scattered read 4 0 8.32ms .2 User I/O
log file sync 3 0 9.35ms .2 Commit


The Header identifies the PDB being reported on.Note that Snapshots 1 to 3 are local to the PDB and are not in the Root.  PDB Snapshots can be maintained (create or drop snapshot) in the same manner as CDB snapshots.  (Note : PDB AWR Snapshots are in the view AWR_PDB_SNAPSHOT,  not DBA_HIST_SNAPSHOT).




In contrast, this below is the Header for a CDB where Automatic Snapshots have meant Snap IDs are already at 379,380.  Thus, the CDB snapshots are different from the PDB snapshots.

WORKLOAD REPOSITORY report for

DB Name DB Id Unique Name DB Role Edition Release RAC CDB
------------ ----------- ----------- ---------------- ------- ---------- --- ---
HKCORCL 947935822 HKCORCL PRIMARY EE 12.2.0.1.0 NO YES

Instance Inst Num Startup Time
------------ -------- ---------------
HKCORCL 1 16-Nov-16 06:13

Host Name Platform CPUs Cores Sockets Memory(GB)
---------------- -------------------------------- ---- ----- ------- ----------
HKCORCL.compute- Linux x86 64-bit 2 2 1 7.05

Snap Id Snap Time Sessions Curs/Sess PDBs
--------- ------------------- -------- --------- -----
Begin Snap: 379 01-Dec-16 08:00:47 51 .6 2
End Snap: 380 01-Dec-16 09:00:09 62 .9 2
Elapsed: 59.36 (mins)
DB Time: 1.14 (mins)



Note how it doesn't identify a PDB.

You need to be explicitly connected to a PDB before awrrpt shows you the option to generate PDB-level AWR report.
.
.
.
Categories: DBA Blogs

12.2 New Features -- 3 : Flashback Pluggable Database

Fri, 2016-11-25 08:35
12.1 allows Point In Time Recovery of a Pluggable Database but not Flashback of an individual PDB.

12.2 now allows Flashback of an individual PDB.   This is easier with a Local Undo Tablespace instead of a Shared Undo Tablespace.

Here is a quick demo (all times in UTC timezone) :

[oracle@HKCORCL ~]$ sqlplus system/Oracle_4U@PDB1

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 25 14:19:06 2016

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

Last Successful login time: Thu Nov 24 2016 01:03:52 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(*) from hr.employees_part;

COUNT(*)
----------
107

SQL> drop table hr.employees_part purge;

Table dropped.

SQL> connect / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL>
SQL> select sysdate, sysdate-oldest_flashback_time
2 from v$flashback_database_log;

SYSDATE SYSDATE-OLDEST_FLASHBACK_TIME
--------- -----------------------------
25-NOV-16 2.36273148

SQL>
SQL> flashback pluggable database pdb1
2 to timestamp sysdate-2/24;

Flashback complete.

SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17:
'/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf
_pdb1undo_d3dgxhbh_.dbf'


SQL> alter pluggable database pdb1 open resetlogs;

Pluggable database altered.

SQL>
SQL> connect system/Oracle_4U@PDB1
Connected.
SQL> select count(*) from hr.employees_part;

COUNT(*)
----------
107

SQL>


(Note : The 12.2 documentation shows the TO TIME clause, it is actually TO TIMESTAMP).
I have done a flashback of PDB1 to a time as of 2hours ago, when the table HR.EMPLOYEES_PART still existed.

Let's look for messages in the alert log.

2016-11-25T14:19:52.992589+00:00
Archived Log entry 11 added for T-1.S-11 ID 0x38800462 LAD:1
2016-11-25T14:19:57.621705+00:00
alter pluggable database pdb1 close
2016-11-25T14:19:57.640353+00:00
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:19:58.885892+00:00
Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 close
2016-11-25T14:26:10.205824+00:00
flashback pluggable database pdb1
to timestamp sysdate-2/24
2016-11-25T14:26:10.627900+00:00
Flashback Restore Start
2016-11-25T14:26:11.513882+00:00
Restore Flashback Pluggable Database PDB1 (3) until change 3536013
Flashback Restore Complete
2016-11-25T14:26:11.707236+00:00
Flashback Media Recovery Start
2016-11-25T14:26:11.718480+00:00
Serial Media Recovery started
2016-11-25T14:26:12.006472+00:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /u04/app/oracle/redo/redo02.log
2016-11-25T14:26:12.283587+00:00
Incomplete Recovery applied until change 3536477 time 11/25/2016 12:26:56
Flashback Media Recovery Complete
Flashback Pluggable Database PDB1 (3) recovered until change 3536477, at 11/25/2016 12:26:56
Completed: flashback pluggable database pdb1
to timestamp sysdate-2/24
2016-11-25T14:26:21.451523+00:00
alter pluggable database pdb1 open
PDB1(3):Autotune of undo retention is turned on.
2016-11-25T14:26:21.659109+00:00
Pdb PDB1 hit error 1113 during open read write (1) and will be closed.
2016-11-25T14:26:21.659410+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_ora_7920.trc:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: '/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf_pdb1undo_d3dgxhbh_.dbf'
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:26:21.804780+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 9: '/u02/app/oracle/oradata/HKCORCL/PDB1/system01.dbf'
ORA-1113 signalled during: alter pluggable database pdb1 open...
2016-11-25T14:26:22.086212+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 10: '/u02/app/oracle/oradata/HKCORCL/PDB1/sysaux01.dbf'
2016-11-25T14:26:22.175778+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 12: '/u02/app/oracle/oradata/HKCORCL/PDB1/users01.dbf'
2016-11-25T14:26:22.270876+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 17: '/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf_pdb1undo_d3dgxhbh_.dbf'
Checker run found 4 new persistent data failures
2016-11-25T14:26:39.804216+00:00
alter pluggable database pdb1 open resetlogs
2016-11-25T14:26:40.377390+00:00
Online datafile 17
Online datafile 12
Online datafile 10
Online datafile 9
2016-11-25T14:26:40.881181+00:00
PDB1(3):Autotune of undo retention is turned on.
PDB1(3):Endian type of dictionary set to little
PDB1(3):[7920] Successfully onlined Undo Tablespace 7.
PDB1(3):Undo initialization finished serial:0 start:868281239 end:868281333 diff:94 ms (0.1 seconds)
PDB1(3):Database Characterset for PDB1 is AL32UTF8
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:26:42.441388+00:00
PDB1(3):Autotune of undo retention is turned on.
2016-11-25T14:26:42.827673+00:00
PDB1(3):Endian type of dictionary set to little
PDB1(3):[7920] Successfully onlined Undo Tablespace 7.
PDB1(3):Undo initialization finished serial:0 start:868283079 end:868283168 diff:89 ms (0.1 seconds)
PDB1(3):Pluggable database PDB1 dictionary check beginning
2016-11-25T14:26:43.706672+00:00
PDB1(3):Pluggable Database PDB1 Dictionary check complete
PDB1(3):Database Characterset for PDB1 is AL32UTF8
2016-11-25T14:26:44.083617+00:00
PDB1(3):Opatch validation is skipped for PDB PDB1 (con_id=0)
PDB1(3):Opening pdb with no Resource Manager plan active
2016-11-25T14:26:45.205147+00:00
Starting control autobackup

Deleted Oracle managed file /u03/app/oracle/fast_recovery_area/HKCORCL/415864F430FE5FFEE053B2D4100A149C/backupset/2016_11_16/o1_mf_nnndf_TAG20161116T024856_d2qldlnv_.bkp
2016-11-25T14:26:46.523130+00:00
Deleted Oracle managed file /u03/app/oracle/fast_recovery_area/HKCORCL/3E09703FB0AF1A7EE053DE4BC40A6C1D/backupset/2016_11_16/o1_mf_nnndf_TAG20161116T024856_d2qlfzqg_.bkp
Control autobackup written to DISK device

handle '/u03/app/oracle/fast_recovery_area/HKCORCL/autobackup/2016_11_25/o1_mf_s_928852005_d3jlk651_.bkp'

Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 open resetlogs


The set of ORA-01113 and ORA-01110 errors are when I tried to open PDB1 without a RESETLOGS.
The OPEN RESETLOGS issued at 2016-11-25T14:26:39.804216+00:00 was successful.
(Note : The ALTER SYSTEM SWITCH LOGFILE wasn't required but I like to archive out the CURRENT redo whenever I make a significant action against the database).

.
.
.

Categories: DBA Blogs

12.2 New Features -- 2 : Partitioning an Existing Table

Wed, 2016-11-23 19:27
A non-partitioned table can be Partitioned (without having to use DBMS_REDEFINITION) online.

SQL> connect hr/Oracle_4U@PDB1
Connected.
SQL> select count(*) from employees;

COUNT(*)
----------
107

SQL> create table employees_part as select * from employees;

Table created.

SQL> select table_name from user_part_tables;

no rows selected

SQL> alter table employees_part
2 modify
3 partition by range (last_name)
4 (partition p_N values less than ('O'),
5 partition p_Q values less than ('R'),
6 partition p_LAST values less than (MAXVALUE))
7 online;

Table altered.

SQL>
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'EMPLOYEES_PART'
4 order by partition_position
5 /

PARTITION_NAME
--------------------------------------------------------------------------------
HIGH_VALUE
------------
P_N
'O'

P_Q
'R'

P_LAST
MAXVALUE


SQL>
SQL> select table_name, partitioning_type, partition_count
2 from user_part_tables
3 where table_name = 'EMPLOYEES_PART'
4 /

TABLE_NAME
--------------------------------------------------------------------------------
PARTITION PARTITION_COUNT
--------- ---------------
EMPLOYEES_PART
RANGE 3


SQL>
SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'EMPLOYEES_PART'
4 order by partition_position
5 /

PARTITION_NAME
--------------------------------------------------------------------------------
NUM_ROWS
----------
P_N
71

P_Q
10

P_LAST
26


SQL>


I was able to convert a Non-Partitioned Table to a Range-Partitioned Table online.
.
.
.

Categories: DBA Blogs

Pages