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: 34 min 57 sec ago

Domain Indexes -- 1 : CONTEXT Indexes

Sun, 2017-12-10 03:17
A CONTEXT Index is one of a class of Domain Indexes.  It is used to build text-retrieval application.
Instead of a regular B-Tree index that captures the entire text of a VARCHAR2 column as a key, you can build an index that consists of "Tokens", words extracted from a long-ish text in the database column.  Searching the index is based on the CONTAINS operator.

Here is a quick demo in 11.2.0.4 :

First I setup a new user for this demo.  I could have used an existing user but my "HEMANT" user has DBA privileges and I want to demonstrate CONTEXT without such privileges.
Here the key grant is "CTXAPP" that is granted to the user.

SQL> create user ctxuser identified by ctxuser default tablespace users;

User created.

SQL> grant create session, create table to ctxuser;

Grant succeeded.

SQL> grant ctxapp to ctxuser;

Grant succeeded.

SQL> alter user ctxuser quota unlimited on users;

User altered.

SQL>


Next, this user creates the demonstration table and index :

SQL> connect ctxuser/ctxuser
Connected.
SQL> create table my_text_table
2 (id_column number primary key,
3 my_text varchar2(2000));

Table created.

SQL> create index my_text_index
2 on my_text_table(my_text)
3 indextype is ctxsys.context;

Index created.

SQL>
SQL> insert into my_text_table
2 values (1,'This is a long piece of text written by Hemant');

1 row created.

SQL> insert into my_text_table
2 values (2,'Another long text to be captured by the index');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> -- this update to the index would be a regular background job
SQL> exec ctx_ddl.sync_index('MY_TEXT_INDEX');

PL/SQL procedure successfully completed.

SQL>


Note the call to CTX_DDL.SYNC_INDEX.  Unlike a regular B-Tree index, the CONTEXT (Domain) Index is, by default, *not* updated in real-time when DML occurs against the base table.  (Hopefully, in a future post, I may demonstrate real-time updates to a CTXCAT index, different from a CONTEXT Index).
Only remember that this CONTEXT index is not automatically updated.  If new rows are added to the table, they are not visible through the index until a SYNC_INDEX operation is performed.  The SYNC_INDEX can be scheduled as a job with another call to DBMS_JOB or DBMS_SCHEDULER or itself as part of the CREATE INDEX statement.

Now, let me demonstrate usage of the Index with the CONTAINS operator.

SQL> select id_column as id,
2 my_text
3 from my_text_table
4 where contains (my_text, 'written by Hemant') > 0
5 /

ID
----------
MY_TEXT
--------------------------------------------------------------------------------
1
This is a long piece of text written by Hemant


SQL> select my_text
2 from my_text_table
3 where contains (my_text, 'Another long') > 0
4 /

MY_TEXT
--------------------------------------------------------------------------------
Another long text to be captured by the index

SQL>


Yes, the CONTAINS operator is a bit awkward.  It will be some time before you (or your developers) get used to the syntax !

Besides CTX_DDL, there are a number of other packages in the prebuilt CTXSYS schema that are available :
 CTX_CLS
 CTX_DDL
 CTX_DOC
 CTX_OUTPUT
 CTX_QUERY
 CTX_REPORT
 CTX_THES
 CTX_ULEXER

Since I have created a separate database user, I can also demonstrate the additional objects that are created when the INDEXTYPE IS CTXSYS.CONTEXT.

SQL> select object_type, object_name, to_char(created,'DD-MON-RR HH24:MI') Crtd
2 from user_objects
3 order by object_type, object_name
4 /

OBJECT_TYPE OBJECT_NAME CRTD
------------------- ------------------------------ ------------------------
INDEX DR$MY_TEXT_INDEX$X 10-DEC-17 16:48
INDEX DRC$MY_TEXT_INDEX$R 10-DEC-17 16:48
INDEX MY_TEXT_INDEX 10-DEC-17 16:48
INDEX SYS_C0017472 10-DEC-17 16:48
INDEX SYS_IL0000045133C00006$$ 10-DEC-17 16:48
INDEX SYS_IL0000045138C00002$$ 10-DEC-17 16:48
INDEX SYS_IOT_TOP_45136 10-DEC-17 16:48
INDEX SYS_IOT_TOP_45142 10-DEC-17 16:48
LOB SYS_LOB0000045133C00006$$ 10-DEC-17 16:48
LOB SYS_LOB0000045138C00002$$ 10-DEC-17 16:48
TABLE DR$MY_TEXT_INDEX$I 10-DEC-17 16:48
TABLE DR$MY_TEXT_INDEX$K 10-DEC-17 16:48
TABLE DR$MY_TEXT_INDEX$N 10-DEC-17 16:48
TABLE DR$MY_TEXT_INDEX$R 10-DEC-17 16:48
TABLE MY_TEXT_TABLE 10-DEC-17 16:48

15 rows selected.

SQL>
SQL> select table_name, constraint_name, index_name
2 from user_constraints
3 where constraint_type = 'P'
4 order by table_name, constraint_name
5 /

TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
INDEX_NAME
------------------------------
DR$MY_TEXT_INDEX$K SYS_IOT_TOP_45136
SYS_IOT_TOP_45136

DR$MY_TEXT_INDEX$N SYS_IOT_TOP_45142
SYS_IOT_TOP_45142

DR$MY_TEXT_INDEX$R DRC$MY_TEXT_INDEX$R
DRC$MY_TEXT_INDEX$R

MY_TEXT_TABLE SYS_C0017472
SYS_C0017472


SQL>


Yes, that is a large number of database objects besides MY_TEXT_TABLE and MY_TEXT_INDEX.  SYS_C0017472 is, of course, the Primary Key Index on MY_TEXT_TABLE (on the ID_COLUMN column).  The others are interesting.

The "Tokens" I mentioned in the first paragraph are, for the purpose of this table MY_TEXT_TABLE, in the DR$MY_TEXT_INDEX$I.

SQL> desc DR$MY_TEXT_INDEX$I
Name Null? Type
----------------------------------------- -------- ----------------------------
TOKEN_TEXT NOT NULL VARCHAR2(64)
TOKEN_TYPE NOT NULL NUMBER(10)
TOKEN_FIRST NOT NULL NUMBER(10)
TOKEN_LAST NOT NULL NUMBER(10)
TOKEN_COUNT NOT NULL NUMBER(10)
TOKEN_INFO BLOB

SQL> select token_text, token_count
2 from dr$my_text_index$i
3 /

TOKEN_TEXT TOKEN_COUNT
---------------------------------------------------------------- -----------
ANOTHER 1
CAPTURED 1
HEMANT 1
INDEX 1
LONG 2
PIECE 1
TEXT 2
WRITTEN 1

8 rows selected.

SQL>


I have been busy in the last few months and have not published much this quarter. Hopefully, I will get more time in the coming weeks to explore CONTEXT, CTXCAT and Domain Indexes.
.
.
.

Categories: DBA Blogs

Creating a PDB in a desired location in 12.2

Wed, 2017-11-15 08:53
A video on creating a Pluggable Database in a desired location, using the command-line.
.
.
.

Categories: DBA Blogs

UNIQUE LOCAL (Partitioned) Index

Mon, 2017-11-06 21:44
It is easy to create a default GLOBAL Index that is defined as a Unique Index on a column (or composite of columns) containing unique values.

But what if you have a Partitioned Table and want to create a LOCAL (i.e. equi-partitioned with the table) Index as a Unique Index ?  Are there any constraints ?

Let me demonstrate a Partitioned table listing Users by Region Code.

SQL> create table users
2 (region_code varchar2(3),
3 username varchar2(30),
4 account_status varchar2(32),
5 created date,
6 profile varchar2(128))
7 partition by range (region_code)
8 (partition a_m values less than ('N'),
9 partition n_r values less than ('S'),
10 partition s_z values less than (MAXVALUE))
11 /

Table created.

SQL>
SQL> insert into users
2 select substr(username,1,3), username, account_status, created, profile
3 from dba_users
4 /

39 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','USERS');

PL/SQL procedure successfully completed.

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

PARTITION_NAME NUM_ROWS
------------------------------ ----------
A_M 18
N_R 9
S_Z 12

SQL>


I now verify that I can create a GLOBAL (non-partitioned) Unique Index on USERNAME.

SQL> create unique index users_username_u1 on users(username) global;

Index created.

SQL> drop index users_username_u1;

Index dropped.

SQL> create unique index users_username_u1 on users(username);

Index created.

SQL> drop index users_username_u1;

Index dropped.

SQL>


I now verify that I can create a Non-Unique LOCAL Index (being equi-partitioned, the index is partitioned by REGION_CODE).  (Being equi-partitioned, the default behaviour is the Index Partition Names inherit from the Table Partition Names).

SQL> create index users_username_l1 on users(username) local;

Index created.

SQL> select partition_name, num_rows
2 from user_ind_partitions
3 where index_name = 'USERS_USERNAME_L1'
4 order by partition_position
5 /

PARTITION_NAME NUM_ROWS
------------------------------ ----------
A_M 18
N_R 9
S_Z 12

SQL>
SQL> drop index users_username_l1;

Index dropped.

SQL>


I've proven (with the GLOBAL Index) that USERNAME is Unique across the whole table.  Can I create a Unique LOCAL Index on this column ?

SQL> create unique index users_username_u_l1 on users(username) local;
create unique index users_username_u_l1 on users(username) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index


SQL>


Note the Error Message. The Partition Key must be a subset of the Unique Index columns.  Let me try adding the Partition Key  (in my example table, the Partition Key is a single column -- it could be a composite of multiple columns.  In that case all the columns of the Partition Key must for a subset of the Unique Index).

SQL> create unique index users_rc_un_u_l1 on users(region_code, username) local;

Index created.

SQL> select partition_name, num_rows
2 from user_ind_partitions
3 where index_name = 'USERS_RC_UN_U_L1'
4 order by partition_position
5 /

PARTITION_NAME NUM_ROWS
------------------------------ ----------
A_M 18
N_R 9
S_Z 12

SQL> drop index users_rc_un_u_l1;

Index dropped.

SQL> create unique index users_un_rc_u_l1 on users(username, region_code) local;

Index created.

SQL> select partition_name, num_rows
2 from user_ind_partitions
3 where index_name = 'USERS_UN_RC_U_L1'
4 order by partition_position
5 /

PARTITION_NAME NUM_ROWS
------------------------------ ----------
A_M 18
N_R 9
S_Z 12

SQL>

It doesn't matter if I put the Partition Key (REGION_CODE) before or after the target column(s) (USERNAME) when I create the LOCAL Unique Index.  What is necessary is that the Partition Key be a subset of the Unique Index definition.
.
.
.

Categories: DBA Blogs

Partitioned Indexes

Fri, 2017-09-29 10:15
Most discussions about Partitioning in Oracle are around Table Partitioning.  Rarely do we come across Index Partitioning.
A couple of days ago, there was an Oracle Community question on Partitioned Indexes.

So, here is a quick listing of Index Partitioning options  (these tests are in 11.2.0.4)


First, I start with a regular, non-partitioned table.

SQL> create table non_partitioned  
2 (id_col number,
3 data_col_1 number,
4 data_col_2 number,
5 data_col_3 varchar2(15)
6 )
7 /

Table created.

SQL>


I now attempt to create an Equi-Partitioned (LOCAL) Index on it.

SQL> create index equi_part on non_partitioned (id_col) local;
create index equi_part on non_partitioned (id_col) local
*
ERROR at line 1:
ORA-14016: underlying table of a LOCAL partitioned index must be partitioned


SQL>


As expected I can't create a LOCAL index on a non-partitioned table.

Can I create any partitioned index on this table ?

I try two different GLOBAL PARTITIONed Indexes

SQL> create index global_part   
2 on non_partitioned (id_col) global
3 partition by range (id_col)
4 (partition p_100 values less than (101),
5 partition p_200 values less than (201)
6 )
7 /
)
*
ERROR at line 6:
ORA-14021: MAXVALUE must be specified for all columns


SQL>
SQL> create index global_part
2 on non_partitioned (id_col) global
3 partition by range (id_col)
4 (partition p_100 values less than (101),
5 partition p_200 values less than (201),
6 partition p_max values less than (MAXVALUE)
7 )
8 /

Index created.

SQL>
SQL> create index global_part_comp
2 on non_partitioned (id_col, data_col_3) global
3 partition by range (id_col, data_col_3)
4 (partition p_1 values less than (101,'M'),
5 partition p_2 values less than (101,MAXVALUE),
6 partition p_3 values less than (201,'M'),
7 partition p_4 values less than (201,MAXVALUE),
8 partition p_max values less than (MAXVALUE, MAXVALUE)
9 )
10 /

Index created.

SQL>


So, I must have a MAXVALUE partition for the Index.  Note that the two indexes above are now Partitioned without the table itself being partitioned.

SQL> select index_name, partitioned
2 from user_indexes
3 where table_name = 'NON_PARTITIONED'
4 order by 1
5 /

INDEX_NAME PAR
------------------------------ ---
GLOBAL_PART YES
GLOBAL_PART_COMP YES

SQL>


The above indexes are Prefixed Global Partitioned Indexes. Can I create a Non-Prefixed Global Partitioned Index -- an Index where the Partition Key is not formed by the left-most columns of the index.

SQL> create index global_part_nonprefix
2 on non_partitioned (id_col, data_col_3) global
3 partition by range (data_col_1)
4 (partition p_1 values less than (101),
5 partition p_2 values less than (201),
6 partition p_max values less than (MAXVALUE)
7 )
8 /
partition by range (data_col_1)
*
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL>
SQL> !oerr ora 14038
14038, 00000, "GLOBAL partitioned index must be prefixed"
// *Cause: User attempted to create a GLOBAL non-prefixed partitioned index
// which is illegal
// *Action: If the user, indeed, desired to create a non-prefixed
// index, it must be created as LOCAL; otherwise, correct the list
// of key and/or partitioning columns to ensure that the index is
// prefixed

SQL>


So, I have proved that a Non-Partitioned Table cannot have a LOCAL Partitioned Index or a Non-Prefixed Global Partitioned Index but can still have a Global Partitioned Index where the Partition Key is left-prefixed from the Index Key. Also, that a Global Partitioned Index can be a Composite Index with columns of different datatypes.

Let me now proceed with a Partitioned Table.

SQL> create table partitioned
2 (id_col number,
3 data_col_1 number,
4 data_col_2 number,
5 data_col_3 varchar2(15)
6 )
7 partition by range (id_col)
8 (partition p_100 values less than (101),
9 partition p_200 values less than (201),
10 partition p_max values less than (MAXVALUE)
11 )
12 /

Table created.

SQL>


First, the Equi-Partitioned (LOCAL) Index.

SQL> create index part_equi_part
2 on partitioned (id_col) local
3 /

Index created.

SQL> select partition_name, partition_position
2 from user_ind_partitions
3 where index_name = 'PART_EQUI_PART'
4 order by 2
5 /

PARTITION_NAME PARTITION_POSITION
------------------------------ ------------------
P_100 1
P_200 2
P_MAX 3

SQL>


The usage of the LOCAL keyword instead of GLOBAL defines the Index as equi-partitioned with the table.  Index Partitions are automatically created to match the Table Partitions with the same Partition Names.  It is possible to create a LOCAL Partitioned Index and manually specify Partition Names but this, in my opinion, is a bad idea.  Attempting to manually name each Partition for the Index can result in a mis-match between Table Partition Names and Index Partition Names.

Next, I define two GLOBAL Partitioned Indexes on this table.

SQL> create index part_gbl_part  
2 on partitioned (data_col_1) global
3 partition by range (data_col_1)
4 (partition p_1 values less than (1001),
5 partition p_2 values less than (2001),
6 partition p_3 values less than (3001),
7 partition p_4 values less than (4001),
8 partition p_max values less than (MAXVALUE)
9 )
10 /

Index created.

SQL> create index part_gbl_part_comp
2 on partitioned (data_col_2, data_col_3) global
3 partition by range (data_col_2, data_col_3)
4 (partition p_a values less than (10, 'M'),
5 partition p_b values less than (10, MAXVALUE),
6 partition p_c values less than (20, 'M'),
7 partition p_d values less than (20, MAXVALUE),
8 partition p_e values less than (30, 'M'),
9 partition p_f values less than (30, MAXVALUE),
10 partition p_max values less than (MAXVALUE, MAXVALUE)
11 )
12 /

Index created.

SQL>
SQL> l
1 select index_name, partition_name, partition_position
2 from user_ind_partitions
3 where index_name in
4 (select index_name from user_indexes
5 where table_name = 'PARTITIONED'
6 )
7* order by 1,3
SQL> /

INDEX_NAME PARTITIO PARTITION_POSITION
------------------ -------- ------------------
PART_EQUI_PART P_100 1
PART_EQUI_PART P_200 2
PART_EQUI_PART P_MAX 3
PART_GBL_PART      P_1                       1
PART_GBL_PART P_2 2
PART_GBL_PART P_3 3
PART_GBL_PART P_4 4
PART_GBL_PART P_MAX 5
PART_GBL_PART_COMP P_A                       1
PART_GBL_PART_COMP P_B 2
PART_GBL_PART_COMP P_C 3
PART_GBL_PART_COMP P_D 4
PART_GBL_PART_COMP P_E 5
PART_GBL_PART_COMP P_F 6
PART_GBL_PART_COMP P_MAX 7

15 rows selected.

SQL>


The Equi-Partitioned (LOCAL) Index has the same number (and, recommended, names) of Partitions as the Table.
However, the GLOBAL Indexes can have different numbers of Partitions.

As with the first case, I cannot create a Global Non-Prefixed Partitioned Index (where the Index Partition key is not  a left-prefix of the Index).

SQL> create index part_global_part_nonprefix
2 on partitioned (id_col, data_col_3) global
3 partition by range (data_col_1)
4 (partition p_1 values less than (101),
5 partition p_2 values less than (201),
6 partition p_max values less than (MAXVALUE)
7 )
8 /
partition by range (data_col_1)
*
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed


SQL>


In this blog post, I haven't touched on Partial Indexing (a 12c feature).

I haven't touched on Unique LOCALly Partitioned Indexes.

I haven't demonstrated the impact of Partition Maintenance operations (TRUNCATE, DROP, MERGE, ADD, SPLIT) on LOCAL and GLOBAL Indexes here -- although I have touched on such operations and LOCAL indexes in earlier blog posts.
.
.
.

Categories: DBA Blogs

Recovering a Datafile created after the last Backup

Wed, 2017-09-13 04:00
Suppose you added a datafile to the database after the last backup and do not yet have a backup of the datafile when the file is lost / corrupt.

How does Oracle RMAN handle the RESTORE / RECOVER?

Here's the situation :  Tablespace HEMANT has three datafiles, of which file id 5 and 6 have been added after the last backup.  (This scenario tested in 11.2.0.4)
Note : Some of you may know that Oracle can reuse file ids.  So file ids 5 and 6 which may have been for some other tablespace / datafiles that had been dropped in the past were reused for these two datafiles added to the tablespace in September 17.


SQL> select v.file#,v.creation_time, v.name
2 from v$datafile v, v$tablespace t
3 where v.ts#=t.ts#
4 and t.name = 'HEMANT'
5 order by 2;

FILE# CREATION_
---------- ---------
NAME
--------------------------------------------------------------------------------
11 19-FEB-16
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf

5 07-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf

6 13-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvg01y_.dbf


SQL>
RMAN> list backup of tablespace hemant;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68 Full 230.29M DISK 00:00:42 07-SEP-17
BP Key: 68 Status: AVAILABLE Compressed: YES Tag: TAG20170907T230339
Piece Name: /u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp
List of Datafiles in backup set 68
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 7608466 07-SEP-17 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf

RMAN>


What happens if I lose all three datafiles ?

[oracle@ora11204 datafile]$ pwd
/u01/app/oracle/oradata/ORCL/datafile
[oracle@ora11204 datafile]$ ls -l *hemant*
-rw-rw----. 1 oracle oracle 104865792 Sep 13 16:31 o1_mf_hemant_dvktv2jd_.dbf
-rw-rw----. 1 oracle oracle 104865792 Sep 13 16:31 o1_mf_hemant_dvktvw02_.dbf
-rw-rw----. 1 oracle oracle 104865792 Sep 13 16:35 o1_mf_hemant_dvkvg01y_.dbf
[oracle@ora11204 datafile]$ rm *hemant*
[oracle@ora11204 datafile]$ ls -l *hemant*
ls: cannot access *hemant*: No such file or directory
[oracle@ora11204 datafile]$
[oracle@ora11204 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 13 16:40:02 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL>


The SHUTDOWN doesn't report an error for all three files, it only reports the error for the first datafile to "fail".

Let me try to startup and open the database.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 750781320 bytes
Database Buffers 310378496 bytes
Redo Buffers 5517312 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5:
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf'


SQL>


Here, too, it only reports the error for the first datafile to fail to open. I must query V$RECOVER_FILE.

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
5 ONLINE ONLINE
FILE NOT FOUND 0


6 ONLINE ONLINE
FILE NOT FOUND 0


11 ONLINE ONLINE
FILE NOT FOUND 0



SQL>


So, now I switch to RMAN.

RMAN> list backup of tablespace hemant;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
68 Full 230.29M DISK 00:00:42 07-SEP-17
BP Key: 68 Status: AVAILABLE Compressed: YES Tag: TAG20170907T230339
Piece Name: /u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp
List of Datafiles in backup set 68
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
11 Full 7608466 07-SEP-17 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf

RMAN>
RMAN> restore datafile 11;

Starting restore at 13-SEP-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktvw02_.dbf
channel ORA_DISK_1: reading from backup piece /u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp
channel ORA_DISK_1: piece handle=/u02/FRA/ORCL/backupset/2017_09_07/o1_mf_nnndf_TAG20170907T230339_dv2qyd23_.bkp tag=TAG20170907T230339
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 13-SEP-17

RMAN> restore datafile 5;

Starting restore at 13-SEP-17
using channel ORA_DISK_1

creating datafile file number=5 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvktv2jd_.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 13-SEP-17

RMAN> restore datafile 6;

Starting restore at 13-SEP-17
using channel ORA_DISK_1

creating datafile file number=6 name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvg01y_.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 13-SEP-17

RMAN>


Ignore the "restore not done; all files ..." error message. It's misleading.   But note how the RESTORE command actually did a "CREATING DATAFILE" operation.  Also note that these are OMF Files.

RMAN> recover datafile 11;

Starting recover at 13-SEP-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 180 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_180_dv2r1ywg_.arc
archived log for thread 1 with sequence 181 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_181_dv2r2f1r_.arc
archived log for thread 1 with sequence 182 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_182_dv2r2os5_.arc
archived log for thread 1 with sequence 183 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_183_dv2r62d0_.arc
archived log for thread 1 with sequence 184 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_184_dv2r68hf_.arc
archived log for thread 1 with sequence 185 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_185_dv2r97r0_.arc
archived log for thread 1 with sequence 186 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc
archived log for thread 1 with sequence 187 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc
archived log for thread 1 with sequence 188 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc
archived log for thread 1 with sequence 189 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc
archived log for thread 1 with sequence 190 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc
archived log for thread 1 with sequence 191 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc
archived log for thread 1 with sequence 192 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc
archived log for thread 1 with sequence 193 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc
archived log for thread 1 with sequence 194 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc
archived log for thread 1 with sequence 195 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc
archived log for thread 1 with sequence 196 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc
archived log for thread 1 with sequence 197 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc
archived log for thread 1 with sequence 198 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_198_dvktx82r_.arc
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_180_dv2r1ywg_.arc thread=1 sequence=180
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_181_dv2r2f1r_.arc thread=1 sequence=181
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_182_dv2r2os5_.arc thread=1 sequence=182
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_183_dv2r62d0_.arc thread=1 sequence=183
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_184_dv2r68hf_.arc thread=1 sequence=184
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_185_dv2r97r0_.arc thread=1 sequence=185
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc thread=1 sequence=186
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc thread=1 sequence=187
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc thread=1 sequence=188
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc thread=1 sequence=189
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc thread=1 sequence=190
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc thread=1 sequence=191
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc thread=1 sequence=192
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc thread=1 sequence=193
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc thread=1 sequence=194
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc thread=1 sequence=195
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc thread=1 sequence=196
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc thread=1 sequence=197
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-SEP-17

RMAN>
RMAN> recover datafile 5;

Starting recover at 13-SEP-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 186 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc
archived log for thread 1 with sequence 187 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc
archived log for thread 1 with sequence 188 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc
archived log for thread 1 with sequence 189 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc
archived log for thread 1 with sequence 190 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc
archived log for thread 1 with sequence 191 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc
archived log for thread 1 with sequence 192 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc
archived log for thread 1 with sequence 193 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc
archived log for thread 1 with sequence 194 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc
archived log for thread 1 with sequence 195 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc
archived log for thread 1 with sequence 196 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc
archived log for thread 1 with sequence 197 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc
archived log for thread 1 with sequence 198 is already on disk as file /u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_198_dvktx82r_.arc
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_07/o1_mf_1_186_dv2rdlfz_.arc thread=1 sequence=186
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_187_dvksno3o_.arc thread=1 sequence=187
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_188_dvksnqs5_.arc thread=1 sequence=188
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_189_dvksyb6y_.arc thread=1 sequence=189
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_190_dvksykkt_.arc thread=1 sequence=190
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_191_dvktb5yc_.arc thread=1 sequence=191
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_192_dvktgz1b_.arc thread=1 sequence=192
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_193_dvktj239_.arc thread=1 sequence=193
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_194_dvktjv7s_.arc thread=1 sequence=194
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_195_dvktl1ln_.arc thread=1 sequence=195
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_196_dvktx089_.arc thread=1 sequence=196
archived log file name=/u02/FRA/ORCL/archivelog/2017_09_13/o1_mf_1_197_dvktx1pj_.arc thread=1 sequence=197
media recovery complete, elapsed time: 00:00:00
Finished recover at 13-SEP-17

RMAN>
RMAN> recover datafile 6;

Starting recover at 13-SEP-17
using channel ORA_DISK_1

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

Finished recover at 13-SEP-17

RMAN>


For datafile 5, I had all the ArchiveLogs (and Online Redo Logs) since the datafile was created (on 07-Sep).
For datafile 6, since it had only been recently created, the only redo to be applied was from the Online Redo Log (not yet archived). See the alert log message :

alter database recover if needed
datafile 6
Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 1 Seq 199 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORCL/clone_o1_mf_1_91zfcp2o_.log
Media Recovery Complete (orcl)
Completed: alter database recover if needed
datafile 6



I should now be able to bring the datafiles online.

RMAN> sql 'alter database datafile 11 online';

sql statement: alter database datafile 11 online

RMAN> sql 'alter database datafile 5 online ';

sql statement: alter database datafile 5 online

RMAN> sql 'alter database datafile 6 online';

sql statement: alter database datafile 6 online

RMAN>
RMAN> sql 'alter database open';

sql statement: alter database open

RMAN>


I can query V$RECOVER_FILE now :

SQL> select * from v$recover_file;

no rows selected

SQL>


So, even though I did not have backups of datafiles 5 and 6, as long as I had all the redo (ArchiveLogs and Online Redo Logs) for actions against these datafiles, I could restore and recover them.

SQL> select v.file#, v.creation_time,  v.name
2 from v$datafile v, v$tablespace t
3 where v.ts#=t.ts#
4 and t.name = 'HEMANT'
5 order by 2;

FILE# CREATION_
---------- ---------
NAME
--------------------------------------------------------------------------------
11 19-FEB-16
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvyx0y_.dbf

5 07-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvz8tz_.dbf

6 13-SEP-17
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_dvkvzhnq_.dbf


SQL>


As these are OMF Files, the actual file name created can well be different from what it was earlier.


Question :  What if I had to do a Database Point In Time Recovery ?  Would this method still work ?  Test it yourself and come back with your comments.

.
.
.
Categories: DBA Blogs

ASM, DiskGroup, AU Size, Tablespace and Table Extents

Fri, 2017-09-01 10:49
Creating a new DiskGroup, specifying the Allocation Unit Size for it, creating a Tablespace in a Pluggable Database and creating multiple Segments (Tables) with multiple Extents.

I have added a new disk to my 12.1 VM.

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

PATH
--------------------------------------------------------------------------------
NAME TOTAL_MB HEADER_STATU STATE GROUP_NUMBER
------------------------------ ---------- ------------ -------- ------------
/dev/asm-disk1
DATA_0000 5114 MEMBER NORMAL 1

/dev/asm-disk2
DATA_0001 5114 MEMBER NORMAL 1

/dev/asm-disk3
FRA_0000 5114 MEMBER NORMAL 2

/dev/asm-disk4
OCRVOTE_0000 5114 MEMBER NORMAL 3

/dev/asm-disk5
DATA_0002 12284 MEMBER NORMAL 1

/dev/asm-disk6
0 CANDIDATE NORMAL 0


6 rows selected.

SQL>

The new disk is /dev/asm-disk6.
I create an ASM DiskGroup with AU Size of 1MB.

SQL> create diskgroup NEWDG_1M_AU external redundancy
2 disk '/dev/asm-disk6'
3 attribute 'compatible.asm'='12.1', 'compatible.rdbms'='12.1','au_size'='1M';

Diskgroup created.

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

PATH
--------------------------------------------------------------------------------
NAME TOTAL_MB HEADER_STATU STATE GROUP_NUMBER
------------------------------ ---------- ------------ -------- ------------
/dev/asm-disk1
DATA_0000 5114 MEMBER NORMAL 1

/dev/asm-disk2
DATA_0001 5114 MEMBER NORMAL 1

/dev/asm-disk3
FRA_0000 5114 MEMBER NORMAL 2

/dev/asm-disk4
OCRVOTE_0000 5114 MEMBER NORMAL 3

/dev/asm-disk5
DATA_0002 12284 MEMBER NORMAL 1

/dev/asm-disk6
NEWDG_1M_AU_0000 2149 MEMBER NORMAL 4


6 rows selected.

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

GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE
-------------------- -----------
1 DATA 512 4096
1048576 MOUNTED

2 FRA 512 4096
1048576 MOUNTED

3 OCRVOTE 512 4096
1048576 MOUNTED

4 NEWDG_1M_AU 512 4096
1048576 MOUNTED


SQL>

I then create a Tablespace in a Pluggable Database specifically on this DG, instead of the default location for new Tablespace datafiles.

SQL> alter session set container=PDB;

Session altered.

SQL> show parameter db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA
SQL>
SQL> create tablespace NEWTS_ON_1M_AU
2 datafile '+NEWDG_1M_AU'
3 extent management local autoallocate segment space management auto;

Tablespace created.

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

GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
1 DATA 22512 12865
2 FRA 5114 2851
3 OCRVOTE 5114 4820
4 NEWDG_1M_AU 2149 1987

SQL>
SQL> drop tablespace NEWTS_ON_1M_AU including contents and datafiles;

Tablespace dropped.

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

GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
1 DATA 22512 12865
2 FRA 5114 2851
3 OCRVOTE 5114 4820
4 NEWDG_1M_AU 2149 2089

SQL>
SQL> create tablespace NEWTS_ON_1M_AU
2 datafile '+NEWDG_1M_AU'
3 extent management local autoallocate segment space management auto;

Tablespace created.

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

GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
1 DATA 22512 12865
2 FRA 5114 2851
3 OCRVOTE 5114 4820
4 NEWDG_1M_AU 2149 1987

SQL>
SQL> select file_name, bytes/1048576, user_bytes/1048576
2 from dba_data_files
3 where tablespace_name = 'NEWTS_ON_1M_AU'
4 /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576 USER_BYTES/1048576
------------- ------------------
+NEWDG_1M_AU/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/newts_on_1m_au.256.95
3594325
100 99


SQL>

Thus, we can see that the Usable Space in the Disk Group is less than the DiskGroup size, even with External Redundancy that specifies no mirroring of ASM Extents. The Usable space in the Disk Group is 1987MB after creating a 100MB datafile.

Bytes available in the datafile are 99MB because a Locally Managed Tablespace reserves space for the Extent Map in the datafile header blocks.  The datafile, belonging to a Tablespace in a Pluggable Database has a path that is identified by the DB_UNIQUE_NAME (RAC) and the PDB GUID as the identifier (44BBC69CE8F552AEE053334EA8C07365)

Now, I create 5 small tables, each with 5 extents of 64KB in the Tablespace.  I use DEFERRED_SEGMENT_CREATION=FALSE  to force creation of the Segment and allocation of all the Extents.

SQL> connect hemant/hemant@PDB
Connected.
SQL> alter session set deferred_segment_creation=FALSE;

Session altered.

SQL> create table t1 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t2 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t3 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t4 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL> create table t5 (col_1 number) tablespace NEWTS_ON_1M_AU storage (minextents 5);

Table created.

SQL>
SQL>
SQL> connect system/manager@PDB

Connected.
SQL> select sum(bytes)/1024, count(*)
2 from dba_extents
3 where tablespace_name = 'NEWTS_ON_1M_AU'
4 /

SUM(BYTES)/1024 COUNT(*)
--------------- ----------
25600 25

SQL> select file_name, bytes/1048576, user_bytes/1048576
2 from dba_data_files
3 where tablespace_name = 'NEWTS_ON_1M_AU'
4 /

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576 USER_BYTES/1048576
------------- ------------------
+NEWDG_1M_AU/RAC/44BBC69CE8F552AEE053334EA8C07365/DATAFILE/newts_on_1m_au.256.95
3594325
100 99


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

GROUP_NUMBER NAME TOTAL_MB USABLE_FILE_MB
------------ ------------------------------ ---------- --------------
1 DATA 22512 12865
2 FRA 5114 2832
3 OCRVOTE 5114 4820
4 NEWDG_1M_AU 2149 1987

SQL>


Thus, a short demo of :
1.  Creating a new ASM DiskGroup with a specified AU Size
2.  Creating a Tablespace in the new DG (not being the default location)
3.  Creating multiple tables with pre-allocated Extents
4.  Verifying the Usable Space in the DiskGroup and Datafile
.
.
.


Categories: DBA Blogs

12c MultiTenant Posts -- 7 : Adding Custom Service to PDB (nonRAC/GI)

Sat, 2017-08-05 10:20
Earlier I have already demonstrated adding and managing custom services in a RAC environment in a blog post and a video.

But what if you are running Single Instance and not using Grid Infrastructure?  The srvctl command in Grid Infrastructure is what you'd use to add and manage services in RAC and Oracle Restart environments.  But with Grid Infrastructure, you can fall back on DBMS_SERVICE.

The DBMS_SERVICE API has been available since Oracle 8i -- when Services were introduced.

Here is a quick demo of some facilities with DBMS_SERVICE.

1.  Adding a Custom Service into a PDB :

$sqlplus system/oracle@NEWPDB

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 5 22:52:21 2017

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

Last Successful login time: Mon Jul 10 2017 22:22:30 +08:00

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

SQL> show con_id

CON_ID
------------------------------
4
SQL>
SQL> execute dbms_service.create_service('HR','HR');

PL/SQL procedure successfully completed.

SQL> execute dbms_service.start_service('HR');

PL/SQL procedure successfully completed.

SQL>


Connecting to the service via tnsnames.

SQL> connect hemant/hemant@HR
Connected.
SQL> show con_id

CON_ID
------------------------------
4
SQL>


2.  Disconnecting all connected users on the Service

$sqlplus system/oracle@NEWPDB

SQL*Plus: Release 12.2.0.1.0 Production on Sat Aug 5 23:02:47 2017

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

Last Successful login time: Sat Aug 05 2017 23:02:28 +08:00

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

SQL>
SQL> execute dbms_service.disconnect_session(-
> service_name=>'HR',disconnect_option=>DBMS_SERVICE.IMMEDIATE);

PL/SQL procedure successfully completed.

SQL>
In the HEMANT session connected to HR :
SQL> show con_id
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 5062
Session ID: 67 Serial number: 12744


SP2-1545: This feature requires Database availability.
SQL>


(Instead of DBMS_SERVICE.IMMEDIATE, we could also specify DBMS_SERVICE.POST_TRANSACTION).


3.  Shutting down a Service without closing the PDB :

SQL> execute dbms_service.stop_service('HR');

PL/SQL procedure successfully completed.

SQL>
SQL> connect hemant/hemant@HR
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SQL>


Does restarting the Database, restart this custom service?

SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8798312 bytes
Variable Size 343936920 bytes
Database Buffers 478150656 bytes
Redo Buffers 7974912 bytes
Database mounted.
Database opened.
SQL> alter pluggable databas all open;
alter pluggable databas all open
*
ERROR at line 1:
ORA-02000: missing DATABASE keyword


SQL> alter pluggable database all open;

Pluggable database altered.

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> connect hemant/hemant@HR
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SQL>
SQL> connect system/oracle@NEWPDB
Connected.
SQL> execute dbms_service.start_service('HR');

PL/SQL procedure successfully completed.

SQL> connect hemant/hemant@HR
Connected.
SQL>


I had to reSTART this custom service ('HR') after the PDB was OPENed.

Services is a facility that has been available since 8i non-OPS.  However, Services were apparently only being used by most sites in RAC environments.

Services allow you to run multiple "applications" (each application advertised as a Service) within the same (one) database.

Note that, in a RAC environment, srvctl configuration of Services can configure auto-restart of the Service.
.
.
.

Categories: DBA Blogs

New Video on Latches

Mon, 2017-07-24 10:13
I have published a new YouTube Video on Latches in Oracle.
.
.
.


Categories: DBA Blogs

12c MultiTenant Posts -- 6 : Partial (aka Subset) Cloning of PDB

Mon, 2017-07-10 09:27
Note : This is a 12.2 feature.

Normally, if you clone a PDB, you'd get a full copy with all the tablespaces.  Now, in 12.2, you can exclude non-essential tablespaces by specifying USER TABLESPACES -- those that you want cloned.  (SYSTEM, SYSAUX and Local UNDO will certainly be cloned).

Let me start with the "NEWPDB" PDB (that I've used in previous examples) that has one more schema and tablespace:

$sqlplus system/oracle@NEWPDB

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 10 21:52:55 2017

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

Last Successful login time: Mon Jul 10 2017 11:04:00 +08:00

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

SQL> select tablespace_name from dba_tablespaces order by 1;

TABLESPACE_NAME
------------------------------
HRDATA
MYDATA
SYSAUX
SYSTEM
TEMP
UNDOTBS1

6 rows selected.

SQL> col owner format a8
SQL> col segment_name format a30
SQL> col tablespace_name format a8
SQL> select owner, segment_name, tablespace_name
2 from dba_segments
3 where tablespace_name like '%DATA'
4 order by 1,2
5 /

OWNER SEGMENT_NAME TABLESPA
-------- ------------------------------ --------
HEMANT BIN$UVb24iaCIE/gUwEAAH/WaQ==$0 MYDATA
HEMANT BIN$UVb24iaIIE/gUwEAAH/WaQ==$0 MYDATA
HEMANT HKC_STORE_FILE MYDATA
HEMANT I MYDATA
HEMANT OBJ_LIST MYDATA
HEMANT SYS_IL0000073525C00003$$ MYDATA
HEMANT SYS_IL0000073532C00003$$ MYDATA
HEMANT SYS_IL0000073535C00003$$ MYDATA
HEMANT SYS_LOB0000073525C00003$$ MYDATA
HEMANT SYS_LOB0000073532C00003$$ MYDATA
HEMANT SYS_LOB0000073535C00003$$ MYDATA
HEMANT T MYDATA
HR EMPLOYEES HRDATA

13 rows selected.

SQL>
SQL> select * from hr.employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- ------------------------------ ------------------------------
HIRE_DATE DEPARTMENT_ID SALARY EMAIL_ID
--------- ------------- ---------- ---------------------------------------------
1 Hemant Chitale
06-JUL-17 1 15000 hemant@mydomain.com


SQL>


Besides, the HEMANT objects in the MYDATA tablespace, I now have HR owning an EMPLOYEES table in the HRDATA tablespace.

Now, I want to clone the NEWPDB tablespace but want to exclude HR data.

First, I set a target location for the datafiles.

$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jul 10 21:57:33 2017

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 db_create_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL> alter session set db_create_file_dest='/u02/oradata';

Session altered.

SQL>


Next, I create my Partial (or SubSet) Clone PDB:

SQL> create pluggable database NONHR from NEWPDB user_tablespaces=('MYDATA');

Pluggable database created.

SQL>
SQL> select con_id, file#, name
2 from v$datafile
3 order by 1,2
4 /

CON_ID FILE#
---------- ----------
NAME
--------------------------------------------------------------------------------
1 1
/u01/app/oracle/oradata/orcl12c/system01.dbf

1 3
/u01/app/oracle/oradata/orcl12c/sysaux01.dbf

1 7
/u01/app/oracle/oradata/orcl12c/users01.dbf

1 15
/u01/app/oracle/oradata/orcl12c/undotbs2.dbf

2 5
/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf

2 6
/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf

2 8
/u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

3 9
/u01/app/oracle/oradata/orcl12c/orcl/system01.dbf

3 10
/u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf

3 11
/u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf

3 12
/u01/app/oracle/oradata/orcl12c/orcl/users01.dbf

3 13
/u01/app/oracle/oradata/orcl12c/orcl/APEX_1991375173370654.dbf

3 14
/u01/app/oracle/oradata/orcl12c/orcl/APEX_1993195660370985.dbf

4 16
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di

4 17
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2

4 18
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1

4 19
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4

4 20
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-HRDATA_FNO-20_0ds7a8e5

5 21
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_system_dp72
3vp5_.dbf

5 22
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_sysaux_dp72
3vsz_.dbf

5 23
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_undotbs1_dp
723vt1_.dbf

5 24
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_mydata_dp72
3vt3_.dbf


22 rows selected.

SQL>
SQL> select con_id, name, open_mode
2 from v$pdbs
3 order by 1
4 /

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

3
ORCL
READ WRITE

4
NEWPDB
READ WRITE

5
NONHR
MOUNTED


SQL>
SQL> alter pluggable database nonhr open;

Pluggable database altered.

SQL>


I can identify the new PDB "NONHR" as CON_ID=5.
Note that in the CREATE PLUGGABLE DATABASE command with the USER_TABLESPACES clause, I can also specify either of COPY, NOCOPY, MOVE, NO DATA or even SNAPSHOT COPY.  This is the simplest Subset Clone that is a Copy with Data.

Let's create the TNSNAMES.ORA entry for NONHR:

NONHR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nonhr)
)
)


Let's now connect to NONHR and confirm its contents.

SQL> connect system/oracle@NONHR
Connected.
SQL> show con_id

CON_ID
------------------------------
5
SQL> show con_name

CON_NAME
------------------------------
NONHR

SQL> select tablespace_name
2 from dba_tablespaces
3 order by 1
4 /

TABLESPACE_NAME
------------------------------
HRDATA
MYDATA
SYSAUX
SYSTEM
TEMP
UNDOTBS1

6 rows selected.

SQL> select file_name from dba_data_files
2 where tablespace_name = 'HRDATA'
3 /

no rows selected

SQL> select owner, segment_name, segment_type
2 from dba_segments
3 where tablespace_name = 'HRDATA'
4 /

no rows selected

SQL>
SQL> select tablespace_name, file_name
2 from dba_data_files
3 order by 1
4 /

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
MYDATA
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_mydata_dp72
3vt3_.dbf

SYSAUX
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_sysaux_dp72
3vsz_.dbf

SYSTEM
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_system_dp72
3vp5_.dbf

UNDOTBS1
/u02/oradata/ORCL12C/53F8012866211264E0530100007FD493/datafile/o1_mf_undotbs1_dp
723vt1_.dbf


SQL>
SQL> select segment_name, segment_type
2 from dba_segments
3 where owner = 'HR'
4 /

no rows selected

SQL> select username
2 from dba_users
3 where username = 'HR'
4 /

USERNAME
--------------------------------------------------------------------------------
HR

SQL>
SQL> select object_name, object_type
2 from dba_objects
3 where owner = 'HR'
4 /

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
EMPLOYEES
TABLE


SQL>
SQL> select owner, segment_name
2 from dba_segments
3 where tablespace_name = 'MYDATA'
4 /

OWNER SEGMENT_NAME
-------- ------------------------------
HEMANT BIN$UVb24iaCIE/gUwEAAH/WaQ==$0
HEMANT BIN$UVb24iaIIE/gUwEAAH/WaQ==$0
HEMANT HKC_STORE_FILE
HEMANT I
HEMANT OBJ_LIST
HEMANT SYS_IL0000073525C00003$$
HEMANT SYS_IL0000073532C00003$$
HEMANT SYS_IL0000073535C00003$$
HEMANT SYS_LOB0000073525C00003$$
HEMANT SYS_LOB0000073532C00003$$
HEMANT SYS_LOB0000073535C00003$$
HEMANT T

12 rows selected.

SQL>
SQL> select count(*) from hemant.obj_list;

COUNT(*)
----------
145282

SQL>


So, what has been copied to the NONHR PDB?  The HRDATA Tablespace, but not the DataFile.  The HR User and Table (definition only, no data), but not the Segment.
However, for the MYDATA Tablespace that was identified as a USER_TABLESPACE in the CREATE PLUGGABLE DATABASE statement, the Tablespace, Datafile, User, Table and Segment have all been copied.

Therefore, NONHR does not have the HR data!   I can drop the User and Tablespace.

SQL> drop tablespace hrdata including contents;

Tablespace dropped.

SQL> drop user hr;

User dropped.

SQL>


However, HR is still present in NEWPDB where NONHR was cloned from:

SQL> connect system/oracle@NEWPDB
Connected.
SQL> select owner, segment_name
2 from dba_segments
3 where tablespace_name = 'HRDATA'
4 /

OWNER SEGMENT_NAME
-------- ------------------------------
HR EMPLOYEES

SQL> select * from hr.employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME
----------- ------------------------------ ------------------------------
HIRE_DATE DEPARTMENT_ID SALARY EMAIL_ID
--------- ------------- ---------- ---------------------------------------------
1 Hemant Chitale
06-JUL-17 1 15000 hemant@mydomain.com


SQL> show con_id

CON_ID
------------------------------
4
SQL> show con_name

CON_NAME
------------------------------
NEWPDB
SQL>
SQL> select tablespace_name, file_name
2 from dba_data_files
3 order by 1
4 /

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
HRDATA
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-HRDATA_FNO-20_0ds7a8e5

MYDATA
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4

SYSAUX
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2

SYSTEM
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di

UNDOTBS1
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1


SQL>


So, 12.2 introduces the ability to create a clone PDB database that is a SubSet (i.e. selected User Tablespaces data) of an existing PDB.

(Note : NEWPDB is in /u03 where it was moved from /u02 earlier as a Relocated Database while NONHR is in /u02 where it was created with OMF based on DB_CREATE_FILE_DEST).
.
.
.

Categories: DBA Blogs

New Video on 11g Nested Loop (enhancement)

Sat, 2017-07-08 10:41
I've published a new Video on the Optimizer Nested Loop Enhancement in 11g :  11g Nested Loop

.
.
.
Categories: DBA Blogs

12c MultiTenant Posts -- 5 : Flashback a PDB

Sun, 2017-07-02 10:31
12.2 allows FLASHBACK DATABASE for a Pluggable Database.

Note that ALTER DATABASE FLASHBACK ON is not enabled by default in a newly created database -- you must issue this command at the CDB level to enable Flashback for all the Pluggable Databases.

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
OBJ_LIST
HKC_STORE_FILE
T

SQL> select count(*) from obj_list;

COUNT(*)
----------
145282

SQL> select count(*) from hkc_store_file;

COUNT(*)
----------
1

SQL> truncate table obj_list;

Table truncated.

SQL> drop table hkc_store_file;

Table dropped.

SQL>


Now, let me Flashback the PDB.

SQL> connect / as sysdba
Connected.
SQL> alter pluggable database newpdb close;

Pluggable database altered.

SQL> flashback pluggable database newpdb to timestamp sysdate-3/1440;

Flashback complete.

SQL> alter pluggable database newpdb open;
alter pluggable database newpdb open
*
ERROR at line 1:
ORA-01113: file 19 needs media recovery
ORA-01110: data file 19:
'/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4'


SQL> alter pluggable database newpdb open resetlogs;

Pluggable database altered.

SQL>


Let me test the data.

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> select count(*) from obj_list;

COUNT(*)
----------
145282

SQL> select count(*) from hkc_store_file;

COUNT(*)
----------
1

SQL>


Yes, the FLASHBACK DATABASE is successful.

What are the pre-requisites ?

1.  ALTER DATABASE FLASHBACK ON  at the CDB
2.  ARCHIVELOG at the CDB
3.  LOCAL UNDO enabled -- highly recommended else a subsequent Point In Time Recovery of the CDB may prevent OPENing the PDB
4.  OPEN RESETLOGS for the PDB

.
.
.

Categories: DBA Blogs

12c MultiTenant Posts -- 4 : Relocate Individual PDB using RMAN

Tue, 2017-06-20 22:29
Given the current location of a PDB :

SQL> alter session set container=NEWPDB;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7
f8go_.dbf

/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7
f8hf_.dbf

/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dk
j7f8hg_.dbf

/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbc
p0wz_.dbf

FILE_NAME
--------------------------------------------------------------------------------


SQL>


I can use RMAN to relocate it. First I take an Image Copy Backup to the new location

RMAN> backup as copy pluggable database newpdb format '/u03/oradata/NEWPDB/%U';

Starting backup at 20-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00017 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2 tag=TAG20170620T231338 RECID=4 STAMP=947200428
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di tag=TAG20170620T231338 RECID=5 STAMP=947200441
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00018 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1 tag=TAG20170620T231338 RECID=6 STAMP=947200451
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00019 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
output file name=/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4 tag=TAG20170620T231338 RECID=7 STAMP=947200454
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 20-JUN-17

Starting Control File and SPFILE Autobackup at 20-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_20/o1_mf_s_947200455_dnms48pp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-JUN-17

RMAN>


Then I switch the database file pointers to the new location.

RMAN> alter pluggable database newpdb close;

Statement processed

RMAN> switch pluggable database newpdb to copy;

datafile 16 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di"
datafile 17 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2"
datafile 18 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1"
datafile 19 switched to datafile copy "/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4"

RMAN> recover pluggable database newpdb;

Starting recover at 20-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK

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

Finished recover at 20-JUN-17

RMAN> alter pluggable database newpdb open;

Statement processed

RMAN>


I can now verify the new location for the database files.

 
SQL> alter session set container=NEWPDB;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSTEM_FNO-16_0as7a8di
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-SYSAUX_FNO-17_09s7a8d2
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-UNDOTBS1_FNO-18_0bs7a8e1
/u03/oradata/NEWPDB/data_D-ORCL12C_I-768045447_TS-MYDATA_FNO-19_0cs7a8e4

SQL>


The datafiles at the old location still remain and can be manually deleted later as they are no longer part of the database.  Note that those datafiles are still registered by RMAN as COPY

RMAN> list copy of pluggable database newpdb;

using target database control file instead of recovery catalog
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - --------------- ---------- --------------- ------
8 16 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
Container ID: 4, PDB Name: NEWPDB

9 17 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
Container ID: 4, PDB Name: NEWPDB

10 18 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
Container ID: 4, PDB Name: NEWPDB

11 19 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
Container ID: 4, PDB Name: NEWPDB


RMAN>


If I manually delete the old location files, I'd need to also delete them from the RMAN Registry.  Alternatively, I can directly delete them from RMAN.

RMAN> delete copy of pluggable database newpdb;

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=278 device type=DISK
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time Sparse
------- ---- - --------------- ---------- --------------- ------
8 16 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
Container ID: 4, PDB Name: NEWPDB

9 17 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
Container ID: 4, PDB Name: NEWPDB

10 18 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
Container ID: 4, PDB Name: NEWPDB

11 19 A 20-JUN-17 2188274 20-JUN-17 NO
Name: /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
Container ID: 4, PDB Name: NEWPDB


Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf RECID=8 STAMP=947200522
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf RECID=9 STAMP=947200522
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf RECID=10 STAMP=947200522
deleted datafile copy
datafile copy file name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf RECID=11 STAMP=947200522
Deleted 4 objects


RMAN>


I still need to relocate the TEMP Tablespace Tempfile.

SQL> alter session set container=NEWPDB;

Session altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_temp_dkj7f8
hg_.dbf


SQL> alter tablespace temp add tempfile '/u03/oradata/NEWPDB/temp01.dbf' size 100M;

Tablespace altered.

SQL>
SQL> alter tablespace temp drop tempfile '/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_temp_dkj7f8hg_.dbf';

Tablespace altered.

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u03/oradata/NEWPDB/temp01.dbf

SQL>


So, I used the same Image Copy method we'd use in 11g databases to relocate a 12c Pluggable Database when within the same server and storage.

To verify that I can access the PDB in the new location :

SQL> connect hemant/hemant@NEWPDB
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
OBJ_LIST
HKC_STORE_FILE
T

SQL> insert into obj_list select * from obj_list;

72641 rows created.

SQL> commit;

Commit complete.

SQL>


.
.

Categories: DBA Blogs

12c MultiTenant Posts -- 3 : Restore Individual PB

Wed, 2017-06-07 05:36
Restoring the single PDB in a Container Database.

$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jun 7 06:34:47 2017

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

connected to target database: ORCL12C (DBID=768045447)

RMAN> restore pluggable database newpdb;

Starting restore at 07-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00016 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
channel ORA_DISK_1: restoring datafile 00017 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
channel ORA_DISK_1: restoring datafile 00018 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
channel ORA_DISK_1: restoring datafile 00019 to /u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp tag=TAG20170605T061347
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 07-JUN-17

RMAN> recover pluggable database newpdb;

Starting recover at 07-JUN-17
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_06/o1_mf_1_16_dmgyksjy_.arc
archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_17_dmhj6nbk_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=15
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp tag=TAG20170605T061438
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_15_dmhoqlq3_.arc thread=1 sequence=15
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_07/o1_mf_1_15_dmhoqlq3_.arc RECID=4 STAMP=946017330
media recovery complete, elapsed time: 00:00:11
Finished recover at 07-JUN-17

RMAN> alter pluggable database newpdb open;

Statement processed

RMAN>


I had connected to the root to restore the PDB.
.
.

.
Categories: DBA Blogs

12c MultiTenant Posts -- 2 : Backup individual PDB

Mon, 2017-06-05 05:16
In the 12c MultiTenant architecture, you can backup (and restore) individual PDBs.  However, you must also backup the CDB (Root) !  You cannot restore a PDB as a "standalone" database --- it has to be restored back to the CDB.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 8798312 bytes
Variable Size 343936920 bytes
Database Buffers 478150656 bytes
Redo Buffers 7974912 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter pluggable database newpdb open;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$
$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jun 5 06:10:17 2017

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

connected to target database: ORCL12C (DBID=768045447)

RMAN> backup database root;

Starting backup at 05-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=270 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl12c/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl12c/sysaux01.dbf
input datafile file number=00015 name=/u01/app/oracle/oradata/orcl12c/undotbs2.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-JUN-17
channel ORA_DISK_1: finished piece 1 at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061033_dmbcjttq_.bkp tag=TAG20170605T061033 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 05-JUN-17

Starting Control File and SPFILE Autobackup at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_05/o1_mf_s_945843079_dmbcl8fm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUN-17

RMAN>
RMAN> backup pluggable database newpdb;

Starting backup at 05-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00017 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7f8hf_.dbf
input datafile file number=00016 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7f8go_.dbf
input datafile file number=00018 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dkj7f8hg_.dbf
input datafile file number=00019 name=/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_mydata_dmbcp0wz_.dbf
channel ORA_DISK_1: starting piece 1 at 05-JUN-17
channel ORA_DISK_1: finished piece 1 at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/4F793A6D323D1344E0530100007FABC7/backupset/2017_06_05/o1_mf_nnndf_TAG20170605T061347_dmbcpvjr_.bkp tag=TAG20170605T061347 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 05-JUN-17

Starting Control File and SPFILE Autobackup at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_05/o1_mf_s_945843252_dmbcqo6t_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUN-17

RMAN>
RMAN> backup archivelog all delete input;

Starting backup at 05-JUN-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=1 STAMP=945843278
channel ORA_DISK_1: starting piece 1 at 05-JUN-17
channel ORA_DISK_1: finished piece 1 at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/backupset/2017_06_05/o1_mf_annnn_TAG20170605T061438_dmbcrgml_.bkp tag=TAG20170605T061438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/archivelog/2017_06_05/o1_mf_1_15_dmbcrfq1_.arc RECID=1 STAMP=945843278
Finished backup at 05-JUN-17

Starting Control File and SPFILE Autobackup at 05-JUN-17
piece handle=/u01/app/oracle/fast_recovery_area/orcl12c/ORCL12C/autobackup/2017_06_05/o1_mf_s_945843280_dmbcrjdg_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-JUN-17

RMAN>


Thus, a PDB in a MultiTenant environment can be backed-up but the Root must always be backed up.
.
.
.

Categories: DBA Blogs

12cR1 RAC Posts -- 11 : Managing Services in RAC (with PDB) (Video)

Sun, 2017-05-21 11:01
I have uploaded a video on managing services (add, start, check, remove) in a 12c RAC database with a Pluggable Database.
.
.
.


Categories: DBA Blogs

12c MultiTenant Posts -- 1 : Creating a PDB in a different directory

Sun, 2017-05-14 04:12
What if the current (default) location for Pluggable Databases is running out of space and, yet, you want to create an Oracle Managed Files Pluggable Database ?

First, I start with this configuration :

SQL> select con_id, file#, substr(name,1,56)
2 from v$datafile
3 order by 1,2;

CON_ID FILE#
---------- ----------
SUBSTR(NAME,1,56)
--------------------------------------------------------------------------------
1 1
/u01/app/oracle/oradata/orcl12c/system01.dbf

1 3
/u01/app/oracle/oradata/orcl12c/sysaux01.dbf

1 7
/u01/app/oracle/oradata/orcl12c/users01.dbf

1 15
/u01/app/oracle/oradata/orcl12c/undotbs2.dbf

2 5
/u01/app/oracle/oradata/orcl12c/pdbseed/system01.dbf

2 6
/u01/app/oracle/oradata/orcl12c/pdbseed/sysaux01.dbf

2 8
/u01/app/oracle/oradata/orcl12c/pdbseed/undotbs01.dbf

3 9
/u01/app/oracle/oradata/orcl12c/orcl/system01.dbf

3 10
/u01/app/oracle/oradata/orcl12c/orcl/sysaux01.dbf

3 11
/u01/app/oracle/oradata/orcl12c/orcl/undotbs01.dbf

3 12
/u01/app/oracle/oradata/orcl12c/orcl/users01.dbf

3 13
/u01/app/oracle/oradata/orcl12c/orcl/APEX_19913751733706

3 14
/u01/app/oracle/oradata/orcl12c/orcl/APEX_19931956603709


13 rows selected.

SQL>


Currently, I have one Pluggable Database (CON_ID=3) but none of the database files are OMF.  I don't have enough space in /u01 and want to create the new PDB in /u02 and also use OMF.  As long as I have /u02/oradata precreated by the system administrator, I can :

SQL> show parameter db_create_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL> alter session set db_create_file_dest='/u02/oradata';

Session altered.

SQL> create pluggable database NEWPDB admin user newpdb_adm identified by newpdb_adm;

Pluggable database created.

SQL> alter pluggable database NEWPDB open;

Pluggable database altered.

SQL> select con_id, open_mode
2 from v$pdbs
3 where name = 'NEWPDB'
4 /

CON_ID OPEN_MODE
---------- ----------
4 READ WRITE

SQL>
SQL> select file#, name
2 from v$datafile
3 where con_id=4
4 order by file#
5 /

FILE#
----------
NAME
--------------------------------------------------------------------------------
16
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_system_dkj7
f8go_.dbf

17
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_sysaux_dkj7
f8hf_.dbf

18
/u02/oradata/ORCL12C/4F793A6D323D1344E0530100007FABC7/datafile/o1_mf_undotbs1_dk
j7f8hg_.dbf


SQL>
SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string orcl12c
SQL>


Note how the parent folder name "ORCL12C" is inherited from the DB_UNIQUE_NAME.  I can now proceed to setup this new PDB.  Later, I  can migrate it as an OMF PDB to another Container Database.
.
.
.
Categories: DBA Blogs

12cR1 RAC Posts -- 10 : Video on Database Startup

Mon, 2017-05-08 09:38
I've created a short video on manually starting up a RAC database that has a PDB with two custom services.  If a database is shutdown before the cluster is shutdown, the cluster startup does not automatically startup the database instance, so I demonstrate a manual startup.

See the video at:  https://youtu.be/saFvo9QhYSI


.
.
.
Categories: DBA Blogs

12cR1 RAC Posts -- 9 : Adding a Service to a PDB in RAC

Sat, 2017-04-29 11:02
My 2node RAC environment has 1 PDB.  Here I add (create) a new Service to the PDB.

Oracle recommends using srvctl instead of DBMS_SERVICE to add a Service.

Can I add a service without having the PDB OPEN ?

[oracle@collabn1 ~]$ srvctl start database -d RAC
[oracle@collabn1 ~]$ srvctl add service -db RAC -service MYSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT -pdb PDB
[oracle@collabn1 ~]$
[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:16:01 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 con_id, name, open_mode from v$pdbs;

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

SQL> alter pluggable database pdb open; -- command issued in both instances

Pluggable database altered.

SQL>
SQL> select con_id, pdb, name, creation_date, clb_goal
2 from v$services
3 where pdb='PDB'
4 /

CON_ID PDB
---------- ------------------------------
NAME CREATION_ CLB_G
---------------------------------------------------------------- --------- -----
3 PDB
pdb.racattack 29-DEC-16 LONG


SQL>


I add the Service to the TNSNAMES.ORA and try to connect to it.

MYSVC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYSVC.racattack)
)
)

[oracle@collabn1 ~]$ tnsping MYSVC

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-APR-2017 23:22:43

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 = collabn-cluster-scan.racattack)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYSVC.racattack)))
OK (0 msec)
[oracle@collabn1 ~]$ sqlplus hemant/hemant@MYSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:22:51 2017

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:


So, apparently, the service isn't running.   Shall I try re-adding the service ?

[oracle@collabn1 ~]$ srvctl add service -db RAC -service MYSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT -pdb PDB
PRKO-3117 : Service MYSVC already exists in database RAC
[oracle@collabn1 ~]$ srvctl remove service -db RAC -service MYSVC -pdb PDB
PRKO-2002 : Invalid command line option: -pdb
[oracle@collabn1 ~]$ srvctl remove service -db RAC -service MYSVC
[oracle@collabn1 ~]$ srvctl add service -db RAC -service MYSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT -pdb PDB
[oracle@collabn1 ~]$
[oracle@collabn1 ~]$ sqlplus hemant/hemant@MYSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:31:15 2017

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:


Yet, it doesn't work !   How do I add and start service to a PDB ?  What's missing ?

[oracle@collabn1 ~]$ srvctl remove service -db RAC -service MYSVC
[oracle@collabn1 ~]$ srvctl start service -db RAC -service MYSVC
[oracle@collabn1 ~]$

SQL> select distinct name from v$services;

NAME
----------------------------------------------------------------
pdb.racattack
RAC.racattack
MYSVC
SYS$BACKGROUND
RAC_DGB
RACXDB
SYS$USERS


[oracle@collabn1 ~]$ sqlplus hemant/hemant@MYSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:43:01 2017

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

Last Successful login time: Sun Apr 16 2017 23:30:21 +08:00

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 table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
MY_DATA
MY_NEW_TABLE

SQL>


I had forgotten to *START* the service !   Let me go back and add another service with the PDB CLOSEd.

SQL> alter pluggable database pdb close immediate;  -- on both instances

Pluggable database altered.

SQL>

[oracle@collabn1 ~]$ srvctl add service -db RAC -pdb PDB -service NEWSVC -preferred RAC1,RAC2 \
> -tafpolicy BASIC -clbgoal SHORT
[oracle@collabn1 ~]$

SQL> alter pluggable database pdb open; -- on both instances

Pluggable database altered.

SQL>

NEWSVC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NEWSVC.racattack)
)
)

[oracle@collabn1 ~]$ srvctl start service -db RAC -service NEWSVC
[oracle@collabn1 ~]$ tnsping NEWSVC

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 29-APR-2017 23:54:38

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 = collabn-cluster-scan.racattack)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = NEWSVC.racattack)))
OK (0 msec)


[oracle@collabn1 ~]$ sqlplus hemant/hemant@NEWSVC

SQL*Plus: Release 12.1.0.2.0 Production on Sat Apr 29 23:55:25 2017

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

Last Successful login time: Sat Apr 29 2017 23:54:51 +08:00

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 table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
MY_DATA
MY_NEW_TABLE

SQL>


However, these entries in PDB_PLUG_IN_VIOLATIONS seem to be a bug :

SQL> alter pluggable database pdb close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb open;

Pluggable database altered.

SQL> select * from pdb_plug_in_violations;

TIME
---------------------------------------------------------------------------
NAME
------------------------------
CAUSE TYPE
---------------------------------------------------------------- ---------
ERROR_NUMBER LINE
------------ ----------
MESSAGE
--------------------------------------------------------------------------------
STATUS
---------
ACTION
--------------------------------------------------------------------------------
29-APR-17 11.58.32.409572 PM
PDB
Service Name Conflict WARNING
0 1
Service name or network name of service MYSVC in the PDB is invalid or conflicts
with an existing service name or network name in the CDB.
PENDING
Drop the service and recreate it with an appropriate name.

29-APR-17 11.58.32.412142 PM
PDB
Service Name Conflict WARNING
0 2
Service name or network name of service NEWSVC in the PDB is invalid or conflict
s with an existing service name or network name in the CDB.
PENDING
Drop the service and recreate it with an appropriate name.


SQL>


So, remember, it is not sufficient to ADD a Service. You must also START the Service using srvctl.
.
.
.

Categories: DBA Blogs

12cR1 RAC Posts -- 8i : Switchback from SingleInstance to RAC

Sun, 2017-04-16 10:59
Earlier this week (10-April), I had done a Switchover from the 2node RAC database to a SingleInstance database.

It is time now to Switchback from SingleInstance to RAC.

First, I check the status of the two databases :

On STBY (the current Primary) :

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
STBY
oem132.racattack


SQL>


Next, I check on the RAC database instance RAC1 (the current Standby) :

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY

SQL> select instance_name, status, host_name from gv$instance;

INSTANCE_NAME STATUS
---------------- ------------
HOST_NAME
----------------------------------------------------------------
RAC1 MOUNTED
collabn1.racattack

RAC2 MOUNTED
collabn2.racattack


SQL>


I also confirm that only one of the two RAC instances is doing recovery (in 12.1 we have only 1 instance in RAC doing recovery) by verifying the (automatic) message in the alert log for RAC1 :

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
Sun Apr 16 23:05:50 2017
Attempt to start background Managed Standby Recovery process (RAC1)
Starting background process MRP0
Sun Apr 16 23:05:50 2017
MRP0 started with pid=52, OS id=16739
Sun Apr 16 23:05:50 2017
MRP0: Background Managed Standby Recovery process started (RAC1)
Sun Apr 16 23:05:55 2017
Started logmerger process
Sun Apr 16 23:05:56 2017
Managed Standby Recovery starting Real Time Apply
Sun Apr 16 23:06:10 2017
Parallel Media Recovery started with 2 slaves
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY


Now, I add some data to the PDB Pluggable Database currently running in STBY :

[oracle@oem132 ~]$ sqlplus hemant/hemant@PDB

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 16 23:29:37 2017

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

Last Successful login time: Mon Apr 10 2017 23:43:30 +08:00

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 count(*) from my_data;

COUNT(*)
----------
300

SQL> create table my_new_table as select * from my_data;

Table created.

SQL>


I am now ready to switchover from STBY to RAC. However, I have a problem because I started the RAC database (and , therefore, DataGuard Broker) before I  started the SingleInstance node and database.  I have drcRAC1.log and drcRAC2.log both reporting :

04/16/2017 23:05:38
Failed to connect to remote database stby. Error is ORA-12543
Failed to send message to site stby. Error code is ORA-12543.
database rac unable to contact primary database for version check; status ORA-12543
completing bootstrap of this database


The fix is to have the Standby RAC database started *after* the singleInstance Primary and verify that Managed Recovery is restarted in RAC1 :

[oracle@collabn1 ~]$ srvctl stop database -d RAC
[oracle@collabn1 ~]$ srvctl start database -d RAC


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
Sun Apr 16 23:43:58 2017
Attempt to start background Managed Standby Recovery process (RAC1)
Starting background process MRP0
Sun Apr 16 23:43:59 2017
MRP0 started with pid=53, OS id=2033
Sun Apr 16 23:43:59 2017
MRP0: Background Managed Standby Recovery process started (RAC1)
Sun Apr 16 23:44:03 2017
RFS[2]: Assigned to RFS process (PID:1922)
RFS[2]: Selected log 5 for thread 1 sequence 76 dbid 2519807290 branch 931825279
Sun Apr 16 23:44:04 2017
Started logmerger process
Sun Apr 16 23:44:07 2017
Managed Standby Recovery starting Real Time Apply
RFS[1]: Selected log 7 for thread 1 sequence 78 dbid 2519807290 branch 931825279
Sun Apr 16 23:44:20 2017
Archived Log entry 144 added for thread 1 sequence 77 ID 0x96bd0722 dest 1:
Sun Apr 16 23:44:25 2017
Archived Log entry 145 added for thread 1 sequence 76 ID 0x96bd0722 dest 1:
Sun Apr 16 23:44:27 2017
Parallel Media Recovery started with 2 slaves
Sun Apr 16 23:44:27 2017
Waiting for all non-current ORLs to be archived...
Sun Apr 16 23:44:27 2017
All non-current ORLs have been archived.
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY


Now, recheck the configuration from the SingleInstance node and then  Switchover to RAC :

[oracle@oem132 ~]$ 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@STBY
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - rac

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

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS (status updated 16 seconds ago)

DGMGRL> switchover to rac;
Performing switchover NOW, please wait...
Operation requires a connection to instance "RAC1" on database "rac"
Connecting to instance "RAC1"...
Connected as SYSDBA.
New primary database "rac" is opening...
Operation requires start up of instance "STBY" on database "stby"
Starting instance "STBY"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oem132.racattack)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBY_DGMGRL)(INSTANCE_NAME=STBY)(SERVER=DEDICATED)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
start up and mount instance "STBY" of database "stby"

DGMGRL>


The ORA-12514 error here is acceptable. I only need to startup STBY manually.

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

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 16 23:51:25 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 289409776 bytes
Database Buffers 541065216 bytes
Redo Buffers 5455872 bytes
SQL> alter database mount standby database;

Database altered.

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY

SQL> select instance_name, status, host_name from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
HOST_NAME
----------------------------------------------------------------
STBY MOUNTED
oem132.racattack


SQL>


STBY has now reverted to being a Standby.

Let me check the RAC database instances

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

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 16 23:55:28 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 open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY

SQL> select instance_name, status, host_name from gv$instance;

INSTANCE_NAME STATUS
---------------- ------------
HOST_NAME
----------------------------------------------------------------
RAC2 OPEN
collabn2.racattack

RAC1 OPEN
collabn1.racattack


SQL>


Yes, RAC is now Primary with both instances OPEN.

Let me (now on RAC) verify the new table created and populated when STBY was the Primary. 

SQL> alter pluggable database pdb open;
alter pluggable database pdb open
*
ERROR at line 1:
ORA-65019: pluggable database PDB already open


SQL> alter session set container=PDB;

Session altered.

SQL> select table_name from dba_tables where owner = 'HEMANT';

TABLE_NAME
--------------------------------------------------------------------------------
MY_DATA
MY_NEW_TABLE

SQL> select count(*) from hemant.my_new_table;

COUNT(*)
----------
300

SQL>


Yes, the data in the Pluggable Database PDB has also come over to RAC.

So, on 10-April, I did a Switchover from RAC (2nodes) to STBY (SingleNode).  Today, I have done a Switchover from STBY to RAC.
.
.
.

Categories: DBA Blogs

12cR1 RAC Posts -- 8h : DataGuard Switchover (RAC to nonRAC) Messages

Tue, 2017-04-11 10:21
Yesterday, I demonstrated a DataGuard Switchover from a 2node RAC(ASM) database to a SingleInstance(FileSystem) Database.

These are the messages in the alert log and drc log files during the Switchover (Messages from the start of the Switchover to the subsequent restart and shutdown of all three instances.

First on node 1 which is running database instance RAC1. The drcRAC1.log and alert_RAC1.log :

04/10/2017 23:30:58
SWITCHOVER TO stby
Switchover to physical standby cannot be initiated from primary database; redirecting to the switchover target stby
using connect identifier: stby
04/10/2017 23:31:00
SWITCHOVER TO stby
04/10/2017 23:31:02
Notifying Oracle Clusterware to prepare primary database for switchover
04/10/2017 23:31:19
Target standby stby did not have Active Data Guard enabled at the time of switchover.
To maintain Active Data Guard license compliance Block Change Tracking will be disabled.
04/10/2017 23:31:20
Notifying Clusterware to restart this instance for Switchover
04/10/2017 23:31:23
Shutting down instance after CTL_SWITCH
04/10/2017 23:33:20
>> Starting Data Guard Broker bootstrap <<
Broker Configuration File Locations:
dg_broker_config_file1 = "+DATA/RAC/dgbroker1.dat"
dg_broker_config_file2 = "+FRA/RAC/dgbroker2.dat"
2017-04-10 23:33:20.858 DMON: Attach state object
2017-04-10 23:33:21.407 DMON: rfafoGetLocks reinitializing dubious PMYSHUT lock value block contents: sts=0, flags=0x0, spare1=0x0, spare2=0x0, cksm=0x0, rndm=0x0
2017-04-10 23:33:21.407 DMON: Broker state reconciled, version = 0, state = 00000000
2017-04-10 23:33:21.407 DMON: Broker State Initialized
2017-04-10 23:33:21.407 Version = 1
2017-04-10 23:33:21.407 State = 00000000
2017-04-10 23:33:21.407 DMON: Entered rfm_get_chief_lock() for CTL_BOOTSTRAP, reason 2
2017-04-10 23:33:21.422 7fffffff 0 DMON: Entered rfm_get_chief_lock() for CTL_BOOTSTRAP, reason 0
2017-04-10 23:33:24.580 7fffffff 0 DMON: start task execution: broker initialization
2017-04-10 23:33:24.580 DMON: Boot configuration (0.0.0), loading from "+DATA/RAC/dgbroker1.dat"
2017-04-10 23:33:24.856 DMON Registering service RAC_DGB with listener(s)
2017-04-10 23:33:24.857 DMON: Executing SQL [ALTER SYSTEM REGISTER]
2017-04-10 23:33:24.902 SQL [ALTER SYSTEM REGISTER] Executed successfully
04/10/2017 23:33:25
Broker Configuration: "rac"
Protection Mode: Maximum Performance
Fast-Start Failover (FSFO): Disabled, flags=0x0, version=0
Primary Database: stby (0x02010000)
Physical standby bootstrap requires Oracle Clusterware buildup
04/10/2017 23:33:39
Notifying Oracle Clusterware to buildup
04/10/2017 23:33:41
rac version check successfully completed
Broker configuration file is current on rac, completing initialization
Creating process RSM0
04/10/2017 23:42:33
Data Guard Broker shutting down
Data Guard Broker - notifying primary of shutdown
posting shutdown message to primary database 0x02001000
RSM0 successfully terminated
2017-04-10 23:42:36.437 >> DMON Process Shutdown <<



=============================================================================================



Mon Apr 10 23:30:59 2017
SWITCHOVER VERIFY: Send VERIFY request to switchover target stby
SWITCHOVER VERIFY COMPLETE
SWITCHOVER VERIFY: Send VERIFY request to switchover target stby
SWITCHOVER VERIFY COMPLETE
ALTER DATABASE SWITCHOVER TO 'stby'
Mon Apr 10 23:31:04 2017
Starting switchover [Process ID: 17791]
Mon Apr 10 23:31:04 2017
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 17791] (RAC1)
Waiting for target standby to receive all redo
Mon Apr 10 23:31:04 2017
Waiting for all non-current ORLs to be archived...
Mon Apr 10 23:31:04 2017
All non-current ORLs have been archived.
Mon Apr 10 23:31:04 2017
Waiting for all FAL entries to be archived...
Mon Apr 10 23:31:04 2017
All FAL entries have been archived.
Mon Apr 10 23:31:04 2017
Waiting for dest_id 2 to become synchronized...
Mon Apr 10 23:31:05 2017
Active, synchronized Physical Standby switchover target has been identified
Preventing updates and queries at the Primary
Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 70 has been fixed
Switchover End-Of-Redo Log thread 2 sequence 39 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x3553b3
ARCH: Noswitch archival of thread 2, sequence 39
ARCH: End-Of-Redo Branch archival of thread 2 sequence 39
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 2 sequence 39 for destination LOG_ARCHIVE_DEST_2
ARCH: Noswitch archival of thread 1, sequence 70
ARCH: End-Of-Redo Branch archival of thread 1 sequence 70
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 1 sequence 70 for destination LOG_ARCHIVE_DEST_2
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
Waiting for target standby to apply all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/rac/RAC1/trace/RAC1_rsm0_17791.trc
Converting the primary database to a new standby database
Clearing standby activation ID 2519803190 (0x96312536)
The primary database controlfile was created using the
'MAXLOGFILES 192' clause.
There is space for up to 188 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 52428800;
Archivelog for thread 1 sequence 70 required for standby recovery
Archivelog for thread 2 sequence 39 required for standby recovery
Offline data file 5 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 7 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Switchover: Primary controlfile converted to standby controlfile succesfully.
Mon Apr 10 23:31:14 2017
Reconfiguration started (old inc 4, new inc 6)
List of instances (total 1) :
1
Dead instances (total 1) :
2
My inst 1
publish big name space - dead or down/up instance detected, invalidate domain 0
Global Resource Directory frozen
* dead instance detected - domain 0 invalid = TRUE
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Mon Apr 10 23:31:14 2017
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Mon Apr 10 23:31:14 2017
Post SMON to start 1st pass IR
Mon Apr 10 23:31:14 2017
Switchover: Complete - Database shutdown required
Mon Apr 10 23:31:14 2017
Sending request(convert to primary database) to switchover target stby
Mon Apr 10 23:31:14 2017
minact-scn: Inst 1 is now the master inc#:4 mmon proc-id:17203 status:0x7
Mon Apr 10 23:31:14 2017
Process (ospid 17189) is suspended due to switchover to physical standby operation.
Mon Apr 10 23:31:14 2017
minact-scn status: grec-scn:0x0000.00000000 gmin-scn:0x0000.0033ccde gcalc-scn:0x0000.0033ccee
minact-scn: master found reconf/inst-rec before recscn scan old-inc#:4 new-inc#:4
Mon Apr 10 23:31:14 2017
Submitted all GCS remote-cache requests
Fix write in gcs resources
Mon Apr 10 23:31:14 2017
Reconfiguration complete (total time 0.6 secs)
Mon Apr 10 23:31:19 2017
Switchover complete. Database shutdown required
Completed: ALTER DATABASE SWITCHOVER TO 'stby'
Target standby stby did not have Active Data Guard enabled at the time of switchover.
To maintain Active Data Guard license compliance Block Change Tracking will be disabled.
Mon Apr 10 23:31:21 2017
Errors in file /u01/app/oracle/diag/rdbms/rac/RAC1/trace/RAC1_rfs_6513.trc:
ORA-16456: switchover to standby in progress or completed
Mon Apr 10 23:31:22 2017
Errors in file /u01/app/oracle/diag/rdbms/rac/RAC1/trace/RAC1_rfs_6518.trc:
ORA-16456: switchover to standby in progress or completed
Mon Apr 10 23:31:23 2017
Clusterware restarting instance for Data Guard Broker operation; shutting down instance now
DMON (ospid: 17306): terminating the instance
Mon Apr 10 23:31:24 2017
Instance terminated by DMON, pid = 17306
Mon Apr 10 23:31:39 2017
Starting ORACLE instance (normal) (OS id: 6761)
Mon Apr 10 23:31:40 2017
CLI notifier numLatches:3 maxDescs:519
Mon Apr 10 23:31:41 2017
Due to limited space in shared pool (need 6094848 bytes, have 4194112 bytes), limiting Resource Manager entities from 2048 to 32
Mon Apr 10 23:31:42 2017
**********************************************************************
Mon Apr 10 23:31:42 2017
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)

Mon Apr 10 23:31:42 2017
Per process system memlock (soft) limit = UNLIMITED
Mon Apr 10 23:31:42 2017
Expected per process system memlock (soft) limit to lock
SHARED GLOBAL AREA (SGA) into memory: 802M
Mon Apr 10 23:31:42 2017
Available system pagesizes:
4K, 2048K
Mon Apr 10 23:31:42 2017
Supported system pagesize(s):
Mon Apr 10 23:31:42 2017
PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
Mon Apr 10 23:31:42 2017
4K Configured 4 204804 NONE
Mon Apr 10 23:31:42 2017
2048K 0 401 0 NONE
Mon Apr 10 23:31:42 2017
RECOMMENDATION:
Mon Apr 10 23:31:42 2017
1. For optimal performance, configure system with expected number
of pages for every supported system pagesize prior to the next
instance restart operation.
Mon Apr 10 23:31:42 2017
**********************************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 2
Number of processor cores in the system is 2
Number of processor sockets in the system is 1
Private Interface 'eth1:1' configured from GPnP for use as a private interconnect.
[name='eth1:1', type=1, ip=169.254.3.70, mac=08-00-27-6f-ef-ba, net=169.254.0.0/16, mask=255.255.0.0, use=haip:cluster_interconnect/62]
Public Interface 'eth0' configured from GPnP for use as a public interface.
[name='eth0', type=1, ip=192.168.78.51, mac=08-00-27-44-0e-4c, net=192.168.78.0/24, mask=255.255.255.0, use=public/1]
Public Interface 'eth0:1' configured from GPnP for use as a public interface.
[name='eth0:1', type=1, ip=192.168.78.61, mac=08-00-27-44-0e-4c, net=192.168.78.0/24, mask=255.255.255.0, use=public/1]
Public Interface 'eth0:4' configured from GPnP for use as a public interface.
[name='eth0:4', type=1, ip=192.168.78.251, mac=08-00-27-44-0e-4c, net=192.168.78.0/24, mask=255.255.255.0, use=public/1]
Public Interface 'eth0:5' configured from GPnP for use as a public interface.
[name='eth0:5', type=1, ip=192.168.78.252, mac=08-00-27-44-0e-4c, net=192.168.78.0/24, mask=255.255.255.0, use=public/1]
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name: Linux
Node name: collabn1.racattack
Release: 2.6.39-400.17.1.el6uek.x86_64
Version: #1 SMP Fri Feb 22 18:16:18 PST 2013
Machine: x86_64
Using parameter settings in server-side pfile /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initRAC1.ora
System parameters with non-default values:
processes = 300
sga_max_size = 800M
spfile = "+DATA/RAC/spfilerac.ora"
control_files = "+DATA/RAC/CONTROLFILE/current.282.931825275"
control_files = "+FRA/RAC/CONTROLFILE/current.256.931825275"
db_block_size = 8192
db_cache_size = 100M
compatible = "12.1.0.2.0"
log_archive_dest_1 = "location=USE_DB_RECOVERY_FILE_DEST"
log_archive_dest_1 = "valid_for=(ALL_LOGFILES, ALL_ROLES)"
log_archive_dest_2 = "service="stby""
log_archive_dest_2 = "ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="stby" net_timeout=30"
log_archive_dest_2 = "valid_for=(online_logfile,all_roles)"
log_archive_dest_state_2 = "ENABLE"
log_archive_min_succeed_dest= 1
log_archive_trace = 0
log_archive_config = "dg_config=(RAC,stby)"
log_archive_format = "%t_%s_%r.dbf"
log_archive_max_processes= 4
archive_lag_target = 0
cluster_database = TRUE
db_create_file_dest = "+DATA"
db_recovery_file_dest = "+FRA"
db_recovery_file_dest_size= 4785M
standby_file_management = "MANUAL"
thread = 1
undo_tablespace = "UNDOTBS1"
instance_number = 1
remote_login_passwordfile= "EXCLUSIVE"
db_domain = "racattack"
dispatchers = "(PROTOCOL=TCP) (SERVICE=RACXDB)"
audit_file_dest = "/u01/app/oracle/admin/RAC/adump"
audit_trail = "DB"
db_name = "RAC"
open_cursors = 300
dg_broker_start = TRUE
dg_broker_config_file1 = "+DATA/RAC/dgbroker1.dat"
dg_broker_config_file2 = "+FRA/RAC/dgbroker2.dat"
diagnostic_dest = "/u01/app/oracle"
enable_pluggable_database= TRUE
Mon Apr 10 23:31:52 2017
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Mon Apr 10 23:31:52 2017
Cluster communication is configured to use the following interface(s) for this instance
169.254.3.70
cluster interconnect IPC version: Oracle UDP/IP (generic)
IPC Vendor 1 proto 2
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0
Starting background process PMON
Mon Apr 10 23:31:53 2017
PMON started with pid=2, OS id=6880
Starting background process PSP0
Starting background process VKTM
Mon Apr 10 23:31:53 2017
PSP0 started with pid=3, OS id=6882
Mon Apr 10 23:31:55 2017
VKTM started with pid=4, OS id=6892 at elevated (RT) priority
Starting background process GEN0
Mon Apr 10 23:31:55 2017
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Starting background process MMAN
Mon Apr 10 23:31:55 2017
GEN0 started with pid=5, OS id=6896
Mon Apr 10 23:31:55 2017
MMAN started with pid=6, OS id=6898
Starting background process DIAG
Starting background process DBRM
Mon Apr 10 23:31:55 2017
DIAG started with pid=8, OS id=6910
Starting background process VKRM
Mon Apr 10 23:31:55 2017
DBRM started with pid=9, OS id=6912
Starting background process PING
Mon Apr 10 23:31:55 2017
VKRM started with pid=10, OS id=6914
Starting background process ACMS
Mon Apr 10 23:31:55 2017
PING started with pid=11, OS id=6916
Starting background process DIA0
Mon Apr 10 23:31:55 2017
ACMS started with pid=12, OS id=6918
Mon Apr 10 23:31:56 2017
DIA0 started with pid=13, OS id=6920
Starting background process LMON
Mon Apr 10 23:31:56 2017
LMON started with pid=14, OS id=6922
Starting background process LMD0
Starting background process LMS0
Mon Apr 10 23:31:56 2017
LMD0 started with pid=7, OS id=6929
Mon Apr 10 23:31:56 2017
* Load Monitor used for high load check
* New Low - High Load Threshold Range = [1920 - 2560]
Mon Apr 10 23:31:56 2017
LMS0 started with pid=15, OS id=6931 at elevated (RT) priority
Starting background process RMS0
Starting background process LMHB
Mon Apr 10 23:31:56 2017
RMS0 started with pid=16, OS id=6935
Mon Apr 10 23:31:56 2017
LMHB started with pid=17, OS id=6937
Starting background process LCK1
Starting background process DBW0
Mon Apr 10 23:31:56 2017
LCK1 started with pid=18, OS id=6939
Starting background process LGWR
Mon Apr 10 23:31:56 2017
DBW0 started with pid=19, OS id=6941
Starting background process CKPT
Mon Apr 10 23:31:56 2017
LGWR started with pid=20, OS id=6943
Mon Apr 10 23:31:56 2017
CKPT started with pid=21, OS id=6945
Starting background process SMON
Mon Apr 10 23:31:56 2017
SMON started with pid=23, OS id=6949
Starting background process RECO
Starting background process LREG
Mon Apr 10 23:31:56 2017
RECO started with pid=25, OS id=6953
Starting background process PXMN
Mon Apr 10 23:31:56 2017
LREG started with pid=26, OS id=6955
Mon Apr 10 23:31:56 2017
PXMN started with pid=27, OS id=6957
Starting background process RBAL
Mon Apr 10 23:31:56 2017
RBAL started with pid=28, OS id=6959
Starting background process ASMB
Starting background process MMON
Mon Apr 10 23:31:56 2017
ASMB started with pid=29, OS id=6961
Starting background process MMNL
Mon Apr 10 23:31:56 2017
MMON started with pid=30, OS id=6963
Mon Apr 10 23:31:56 2017
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Mon Apr 10 23:31:56 2017
MMNL started with pid=31, OS id=6965
starting up 1 shared server(s) ...
Mon Apr 10 23:31:57 2017
NOTE: ASMB registering with ASM instance as Standard client 0xffffffffffffffff (reg:839035202) (new connection)
NOTE: ASMB connected to ASM instance +ASM1 osid: 6971 (Flex mode; client id 0xffffffffffffffff)
Mon Apr 10 23:31:58 2017
lmon registered with NM - instance number 1 (internal mem no 0)
Mon Apr 10 23:31:58 2017
NOTE: initiating MARK startup
Starting background process MARK
Mon Apr 10 23:31:58 2017
MARK started with pid=35, OS id=6977
Mon Apr 10 23:31:58 2017
NOTE: MARK has subscribed
Mon Apr 10 23:31:58 2017
Reconfiguration started (old inc 0, new inc 2)
List of instances (total 1) :
1
My inst 1 (I'm a new instance)
Mon Apr 10 23:31:59 2017
Using default pga_aggregate_limit of 2048 MB
Mon Apr 10 23:32:18 2017
Global Resource Directory frozen
* allocate domain 0, invalid = TRUE
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Mon Apr 10 23:32:26 2017
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Mon Apr 10 23:32:26 2017
Post SMON to start 1st pass IR
Submitted all GCS remote-cache requests
Fix write in gcs resources
Mon Apr 10 23:32:26 2017
Reconfiguration complete (total time 27.7 secs)
Starting background process LCK0
Mon Apr 10 23:32:29 2017
LCK0 started with pid=39, OS id=7084
Starting background process RSMN
Mon Apr 10 23:32:30 2017
RSMN started with pid=40, OS id=7094
Mon Apr 10 23:32:32 2017
Instance started by oraagent
Starting background process DMON
ORACLE_BASE from environment = /u01/app/oracle
Mon Apr 10 23:32:32 2017
DMON started with pid=41, OS id=7100
Mon Apr 10 23:32:34 2017
ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))' SCOPE=MEMORY SID='RAC1';
Mon Apr 10 23:32:35 2017
ALTER SYSTEM SET remote_listener=' collabn-cluster-scan.racattack:1521' SCOPE=MEMORY SID='RAC1';
ALTER DATABASE MOUNT /* db agent *//* {1:46642:1193} */
Mon Apr 10 23:32:36 2017
This instance was first to mount
Mon Apr 10 23:32:38 2017
NOTE: ASMB mounting group 1 (DATA)
NOTE: Assigning number (1,0) to disk (/dev/asm-disk1)
NOTE: Assigning number (1,1) to disk (/dev/asm-disk2)
NOTE: Assigning number (1,2) to disk (/dev/asm-disk5)
SUCCESS: mounted group 1 (DATA)
NOTE: grp 1 disk 0: DATA_0000 path:/dev/asm-disk1
NOTE: grp 1 disk 1: DATA_0001 path:/dev/asm-disk2
NOTE: grp 1 disk 2: DATA_0002 path:/dev/asm-disk5
NOTE: ASMB mounting group 2 (FRA)
Mon Apr 10 23:32:39 2017
NOTE: dependency between database RAC and diskgroup resource ora.DATA.dg is established
Mon Apr 10 23:32:39 2017
NOTE: Assigning number (2,0) to disk (/dev/asm-disk3)
SUCCESS: mounted group 2 (FRA)
NOTE: grp 2 disk 0: FRA_0000 path:/dev/asm-disk3
Mon Apr 10 23:32:40 2017
NOTE: dependency between database RAC and diskgroup resource ora.FRA.dg is established
Mon Apr 10 23:32:46 2017
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
Starting background process TMON
ARCH: STARTING ARCH PROCESSES
Starting background process ARC0
Mon Apr 10 23:32:48 2017
TMON started with pid=44, OS id=7628
Mon Apr 10 23:32:48 2017
ARC0 started with pid=45, OS id=7630
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
Mon Apr 10 23:32:48 2017
ARC0: STARTING ARCH PROCESSES
Starting background process ARC1
Starting background process ARC2
Mon Apr 10 23:32:48 2017
ARC1 started with pid=46, OS id=7632
Starting background process ARC3
Mon Apr 10 23:32:48 2017
ARC2 started with pid=47, OS id=7634
ARC1: Archival started
ARC2: Archival started
Mon Apr 10 23:32:48 2017
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC1: Thread not mounted
Mon Apr 10 23:32:48 2017
ARC2: Becoming the heartbeat ARCH
Mon Apr 10 23:32:48 2017
ARC3 started with pid=48, OS id=7636
Mon Apr 10 23:32:48 2017
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Thread not mounted
Mon Apr 10 23:32:48 2017
ARC3: Thread not mounted
Mon Apr 10 23:32:49 2017
ARC2: Thread not mounted
Mon Apr 10 23:32:49 2017
Network Resource Management enabled for Process LGWR (pid 6943) for Exadata I/O
Successful mount of redo thread 1, with mount id 2528954836
Mon Apr 10 23:32:49 2017
Allocated 3981120 bytes in shared pool for flashback generation buffer
Starting background process RVWR
Mon Apr 10 23:32:49 2017
RVWR started with pid=49, OS id=7638
Physical Standby Database mounted.
Lost write protection disabled
Mon Apr 10 23:32:58 2017
Archiving previously deferred ORLs (RAC1)
ARCH: End-Of-Redo Branch archival of thread 1 sequence 70
Mon Apr 10 23:33:13 2017
Archived Log entry 136 added for thread 1 sequence 70 ID 0x96312536 dest 1:
ARCH: End-Of-Redo Branch archival of thread 2 sequence 39
Mon Apr 10 23:33:15 2017
Archived Log entry 137 added for thread 2 sequence 39 ID 0x96312536 dest 1:
Completed: ALTER DATABASE MOUNT /* db agent *//* {1:46642:1193} */
Mon Apr 10 23:33:20 2017
Starting Data Guard Broker (DMON)
Starting background process INSV
Mon Apr 10 23:33:21 2017
INSV started with pid=42, OS id=7855
Starting background process NSV1
Mon Apr 10 23:33:25 2017
NSV1 started with pid=52, OS id=7869
Mon Apr 10 23:33:26 2017
Decreasing number of real time LMS from 1 to 0
Starting background process RSM0
Mon Apr 10 23:33:41 2017
RSM0 started with pid=55, OS id=7929
Mon Apr 10 23:33:41 2017
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Network Resource Management enabled for Process (pid 7909) for Exadata I/O
Primary database is in MAXIMUM PERFORMANCE mode
Mon Apr 10 23:33:46 2017
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Mon Apr 10 23:33:46 2017
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
Mon Apr 10 23:33:46 2017
ARC2: Becoming the active heartbeat ARCH
ARC2: Becoming the active heartbeat ARCH
RFS[1]: Assigned to RFS process (PID:7909)
RFS[1]: Selected log 5 for thread 1 sequence 72 dbid 2519807290 branch 931825279
Mon Apr 10 23:33:46 2017
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Mon Apr 10 23:33:47 2017
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='RAC1';
Mon Apr 10 23:33:47 2017
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='RAC1';
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
Mon Apr 10 23:33:50 2017
Attempt to start background Managed Standby Recovery process (RAC1)
Starting background process MRP0
Mon Apr 10 23:33:50 2017
MRP0 started with pid=56, OS id=7988
Mon Apr 10 23:33:50 2017
MRP0: Background Managed Standby Recovery process started (RAC1)
Mon Apr 10 23:33:55 2017
Started logmerger process
Mon Apr 10 23:33:57 2017
Managed Standby Recovery starting Real Time Apply
Mon Apr 10 23:33:58 2017
RFS[2]: Assigned to RFS process (PID:8007)
RFS[2]: Opened log for thread 1 sequence 71 dbid 2519807290 branch 931825279
RFS[1]: Selected log 6 for thread 1 sequence 73 dbid 2519807290 branch 931825279
Mon Apr 10 23:33:59 2017
Reconfiguration started (old inc 2, new inc 4)
List of instances (total 2) :
1 2
New instances (total 1) :
2
My inst 1
Global Resource Directory frozen
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Mon Apr 10 23:33:59 2017
Post SMON to start 1st pass IR
Submitted all GCS remote-cache requests
Fix write in gcs resources
Mon Apr 10 23:34:00 2017
Reconfiguration complete (total time 0.5 secs)
Mon Apr 10 23:34:15 2017
Archived Log entry 138 added for thread 1 sequence 72 ID 0x96bd0722 dest 1:
Mon Apr 10 23:34:16 2017
Archived Log entry 139 added for thread 1 sequence 71 rlc 931825279 ID 0x96bd0722 dest 2:
RFS[2]: Opened log for thread 2 sequence 40 dbid 2519807290 branch 931825279
Mon Apr 10 23:34:23 2017
Archived Log entry 140 added for thread 2 sequence 40 rlc 931825279 ID 0x0 dest 2:
Mon Apr 10 23:34:28 2017
Parallel Media Recovery started with 2 slaves
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY
Mon Apr 10 23:34:33 2017
Waiting for all non-current ORLs to be archived...
Mon Apr 10 23:34:33 2017
All non-current ORLs have been archived.
Clearing online redo logfile 1 +DATA/RAC/ONLINELOG/group_1.283.931825279

Clearing online log 1 of thread 1 sequence number 69
Mon Apr 10 23:34:50 2017
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/RAC/ONLINELOG/group_2.284.931825283

Clearing online log 2 of thread 1 sequence number 70
Mon Apr 10 23:34:51 2017
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Mon Apr 10 23:35:00 2017
Increasing number of real time LMS from 0 to 1
Mon Apr 10 23:35:09 2017
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DATA/RAC/ONLINELOG/group_3.290.931826413

Clearing online log 3 of thread 2 sequence number 39
Mon Apr 10 23:35:22 2017
Clearing online redo logfile 3 complete
Clearing online redo logfile 4 +DATA/RAC/ONLINELOG/group_4.291.931826417

Clearing online log 4 of thread 2 sequence number 38
Clearing online redo logfile 4 complete
Mon Apr 10 23:35:28 2017
Media Recovery Log +FRA/RAC/ARCHIVELOG/2017_04_10/thread_2_seq_38.357.940980599
Mon Apr 10 23:35:28 2017
Media Recovery Log +FRA/RAC/ARCHIVELOG/2017_04_10/thread_1_seq_67.355.940978999
Mon Apr 10 23:35:29 2017
Media Recovery Log +FRA/RAC/ARCHIVELOG/2017_04_10/thread_1_seq_68.356.940980597
Mon Apr 10 23:35:31 2017
Media Recovery Log +FRA/RAC/ARCHIVELOG/2017_04_10/thread_1_seq_69.358.940980603
Mon Apr 10 23:35:32 2017
Media Recovery Log +FRA/RAC/ARCHIVELOG/2017_04_10/thread_2_seq_39.360.940980795
Mon Apr 10 23:35:35 2017
Media Recovery Log +FRA/RAC/ARCHIVELOG/2017_04_10/thread_1_seq_70.359.940980783
Mon Apr 10 23:35:38 2017
Resetting standby activation ID 0 (0x0)
Mon Apr 10 23:35:39 2017
Media Recovery End-Of-Redo indicator encountered
Mon Apr 10 23:35:39 2017
Media Recovery Continuing
Mon Apr 10 23:35:39 2017
Media Recovery Log +FRA/RAC/ARCHIVELOG/2017_04_10/thread_1_seq_71.361.940980839
Mon Apr 10 23:35:39 2017
Media Recovery Log +FRA/RAC/ARCHIVELOG/2017_04_10/thread_2_seq_40.363.940980861
Mon Apr 10 23:35:39 2017
Media Recovery Log +FRA/RAC/ARCHIVELOG/2017_04_10/thread_1_seq_72.362.940980841
Mon Apr 10 23:35:51 2017
Media Recovery Waiting for thread 1 sequence 73 (in transit)
Mon Apr 10 23:35:51 2017
Recovery of Online Redo Log: Thread 1 Group 6 Seq 73 Reading mem 0
Mem# 0: +DATA/RAC/ONLINELOG/group_6.298.937936361
Mem# 1: +FRA/RAC/ONLINELOG/group_6.304.937936363
Mon Apr 10 23:41:26 2017
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
Mon Apr 10 23:41:28 2017
MRP0: Background Media Recovery cancelled with status 16037
Mon Apr 10 23:41:28 2017
Errors in file /u01/app/oracle/diag/rdbms/rac/RAC1/trace/RAC1_pr00_8020.trc:
ORA-16037: user requested cancel of managed recovery operation
Mon Apr 10 23:41:28 2017
Managed Standby Recovery not using Real Time Apply
Mon Apr 10 23:41:29 2017
Recovery interrupted!
Recovered data files to a consistent state at change 3497232
Mon Apr 10 23:41:29 2017
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 99
Mon Apr 10 23:41:29 2017
Errors in file /u01/app/oracle/diag/rdbms/rac/RAC1/trace/RAC1_pr00_8020.trc:
ORA-16037: user requested cancel of managed recovery operation
Mon Apr 10 23:41:29 2017
MRP0: Background Media Recovery process shutdown (RAC1)
Mon Apr 10 23:41:29 2017
NOTE: Deferred communication with ASM instance
License high water mark = 10
Mon Apr 10 23:41:29 2017
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Stopping Emon pool
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Mon Apr 10 23:41:30 2017
ARCH shutting down
ARC0: Archival stopped
Mon Apr 10 23:41:30 2017
ARCH shutting down
ARC1: Archival stopped
Mon Apr 10 23:41:30 2017
ARCH shutting down
ARC2: Relinquishing active heartbeat ARCH role
Mon Apr 10 23:41:30 2017
ARCH shutting down
ARC3: Archival stopped
Mon Apr 10 23:41:30 2017
ARC2: Archival stopped
Mon Apr 10 23:41:31 2017
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 5
Mon Apr 10 23:41:31 2017
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 14
Mon Apr 10 23:41:31 2017
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 2
Completed: ALTER DATABASE DISMOUNT
Mon Apr 10 23:41:37 2017
NOTE: De-assigning number (2,0) from disk (/dev/asm-disk3)
SUCCESS: diskgroup FRA was dismounted
NOTE: De-assigning number (1,0) from disk (/dev/asm-disk1)
NOTE: De-assigning number (1,1) from disk (/dev/asm-disk2)
NOTE: De-assigning number (1,2) from disk (/dev/asm-disk5)
SUCCESS: diskgroup DATA was dismounted
NOTE: Database dismounted; ASMB process exiting
NOTE: ASMB clearing idle groups before exit
Stopping background process MARK
Mon Apr 10 23:41:38 2017
NOTE: Shutting down MARK background process
Stopping background process RBAL
Mon Apr 10 23:41:56 2017
Reconfiguration started (old inc 4, new inc 6)
List of instances (total 1) :
1
Dead instances (total 1) :
2
My inst 1
publish big name space - dead or down/up instance detected, invalidate domain 0
Global Resource Directory frozen
* dead instance detected - domain 0 invalid = TRUE
Communication channels reestablished
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Mon Apr 10 23:41:56 2017
Post SMON to start 1st pass IR
Submitted all GCS remote-cache requests
Fix write in gcs resources
Mon Apr 10 23:41:56 2017
Reconfiguration complete (total time 0.2 secs)
Mon Apr 10 23:42:31 2017
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Shutting down Data Guard Broker processes
Mon Apr 10 23:42:36 2017
Completed: Data Guard Broker shutdown
Mon Apr 10 23:42:37 2017
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Mon Apr 10 23:42:37 2017
Stopping background process VKTM
Mon Apr 10 23:42:42 2017
freeing rdom 0
Mon Apr 10 23:42:44 2017
Instance shutdown complete






Next on node2 running instance RAC2 :

04/10/2017 23:34:35
>> Starting Data Guard Broker bootstrap <<
Broker Configuration File Locations:
dg_broker_config_file1 = "+DATA/RAC/dgbroker1.dat"
dg_broker_config_file2 = "+FRA/RAC/dgbroker2.dat"
2017-04-10 23:34:35.584 DMON: Attach state object
2017-04-10 23:34:35.991 DMON: Broker State Initialized
2017-04-10 23:34:35.991 Version = 3
2017-04-10 23:34:35.991 State = 00000000
2017-04-10 23:34:35.992 DMON: Entered rfm_get_chief_lock() for CTL_BOOTSTRAP, reason 2
2017-04-10 23:34:35.993 7fffffff 0 DMON: Entered rfm_get_chief_lock() for CTL_BOOTSTRAP, reason 0
2017-04-10 23:34:39.031 7fffffff 0 DMON: start task execution: broker initialization
2017-04-10 23:34:39.130 DMON: Boot configuration (0.0.0), loading from "+DATA/RAC/dgbroker1.dat"
2017-04-10 23:34:39.192 DMON Registering service RAC_DGB with listener(s)
2017-04-10 23:34:39.192 DMON: Executing SQL [ALTER SYSTEM REGISTER]
2017-04-10 23:34:39.193 SQL [ALTER SYSTEM REGISTER] Executed successfully
04/10/2017 23:34:39
Broker Configuration: "rac"
Protection Mode: Maximum Performance
Fast-Start Failover (FSFO): Disabled, flags=0x0, version=0
Primary Database: stby (0x02010000)
04/10/2017 23:34:47
rac version check successfully completed
Broker configuration file is current on rac, completing initialization
Creating process RSM0
04/10/2017 23:41:41
Data Guard Broker shutting down
RSM0 successfully terminated
2017-04-10 23:41:44.988 >> DMON Process Shutdown <<



==========================================================================================================



Mon Apr 10 23:31:10 2017
Switchover in progress in another database instance - Database is shutdown automatically
LGWR (ospid: 3335): terminating the instance due to error 16456
Mon Apr 10 23:31:11 2017
Instance terminated by LGWR, pid = 3335
Mon Apr 10 23:33:50 2017
Starting ORACLE instance (normal) (OS id: 8233)
Mon Apr 10 23:33:51 2017
CLI notifier numLatches:3 maxDescs:519
Mon Apr 10 23:33:51 2017
Due to limited space in shared pool (need 6094848 bytes, have 4194112 bytes), limiting Resource Manager entities from 2048 to 32
Mon Apr 10 23:33:51 2017
**********************************************************************
Mon Apr 10 23:33:51 2017
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)

Mon Apr 10 23:33:51 2017
Per process system memlock (soft) limit = UNLIMITED
Mon Apr 10 23:33:51 2017
Expected per process system memlock (soft) limit to lock
SHARED GLOBAL AREA (SGA) into memory: 802M
Mon Apr 10 23:33:51 2017
Available system pagesizes:
4K, 2048K
Mon Apr 10 23:33:51 2017
Supported system pagesize(s):
Mon Apr 10 23:33:51 2017
PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
Mon Apr 10 23:33:51 2017
4K Configured 4 204804 NONE
Mon Apr 10 23:33:51 2017
2048K 0 401 0 NONE
Mon Apr 10 23:33:51 2017
RECOMMENDATION:
Mon Apr 10 23:33:51 2017
1. For optimal performance, configure system with expected number
of pages for every supported system pagesize prior to the next
instance restart operation.
Mon Apr 10 23:33:51 2017
**********************************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 2
Number of processor cores in the system is 2
Number of processor sockets in the system is 1
Private Interface 'eth1:1' configured from GPnP for use as a private interconnect.
[name='eth1:1', type=1, ip=169.254.178.54, mac=08-00-27-c5-68-87, net=169.254.0.0/16, mask=255.255.0.0, use=haip:cluster_interconnect/62]
Public Interface 'eth0' configured from GPnP for use as a public interface.
[name='eth0', type=1, ip=192.168.78.52, mac=08-00-27-1e-02-8d, net=192.168.78.0/24, mask=255.255.255.0, use=public/1]
Public Interface 'eth0:1' configured from GPnP for use as a public interface.
[name='eth0:1', type=1, ip=192.168.78.62, mac=08-00-27-1e-02-8d, net=192.168.78.0/24, mask=255.255.255.0, use=public/1]
Public Interface 'eth0:2' configured from GPnP for use as a public interface.
[name='eth0:2', type=1, ip=192.168.78.253, mac=08-00-27-1e-02-8d, net=192.168.78.0/24, mask=255.255.255.0, use=public/1]
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics
and Real Application Testing options.
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name: Linux
Node name: collabn2.racattack
Release: 2.6.39-400.17.1.el6uek.x86_64
Version: #1 SMP Fri Feb 22 18:16:18 PST 2013
Machine: x86_64
Using parameter settings in server-side pfile /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initRAC2.ora
System parameters with non-default values:
processes = 300
sga_max_size = 800M
spfile = "+DATA/RAC/spfilerac.ora"
control_files = "+DATA/RAC/CONTROLFILE/current.282.931825275"
control_files = "+FRA/RAC/CONTROLFILE/current.256.931825275"
db_block_size = 8192
db_cache_size = 100M
compatible = "12.1.0.2.0"
log_archive_dest_1 = "location=USE_DB_RECOVERY_FILE_DEST"
log_archive_dest_1 = "valid_for=(ALL_LOGFILES, ALL_ROLES)"
log_archive_dest_2 = "service="stby""
log_archive_dest_2 = "ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="stby" net_timeout=30"
log_archive_dest_2 = "valid_for=(online_logfile,all_roles)"
log_archive_dest_state_2 = "ENABLE"
log_archive_min_succeed_dest= 1
log_archive_trace = 0
log_archive_config = "dg_config=(RAC,stby)"
log_archive_format = "%t_%s_%r.dbf"
log_archive_max_processes= 4
archive_lag_target = 0
cluster_database = TRUE
db_create_file_dest = "+DATA"
db_recovery_file_dest = "+FRA"
db_recovery_file_dest_size= 4785M
standby_file_management = "MANUAL"
thread = 2
undo_tablespace = "UNDOTBS2"
instance_number = 2
remote_login_passwordfile= "EXCLUSIVE"
db_domain = "racattack"
dispatchers = "(PROTOCOL=TCP) (SERVICE=RACXDB)"
audit_file_dest = "/u01/app/oracle/admin/RAC/adump"
audit_trail = "DB"
db_name = "RAC"
open_cursors = 300
dg_broker_start = TRUE
dg_broker_config_file1 = "+DATA/RAC/dgbroker1.dat"
dg_broker_config_file2 = "+FRA/RAC/dgbroker2.dat"
diagnostic_dest = "/u01/app/oracle"
enable_pluggable_database= TRUE
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Mon Apr 10 23:33:55 2017
Cluster communication is configured to use the following interface(s) for this instance
169.254.178.54
cluster interconnect IPC version: Oracle UDP/IP (generic)
IPC Vendor 1 proto 2
Oracle instance running with ODM: Oracle Direct NFS ODM Library Version 3.0
Starting background process PMON
Mon Apr 10 23:33:56 2017
PMON started with pid=2, OS id=8257
Starting background process PSP0
Mon Apr 10 23:33:56 2017
PSP0 started with pid=3, OS id=8259
Starting background process VKTM
Mon Apr 10 23:33:57 2017
VKTM started with pid=4, OS id=8261 at elevated (RT) priority
Starting background process GEN0
Mon Apr 10 23:33:57 2017
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Starting background process MMAN
Mon Apr 10 23:33:57 2017
GEN0 started with pid=5, OS id=8265
Mon Apr 10 23:33:57 2017
MMAN started with pid=6, OS id=8267
Starting background process DIAG
Mon Apr 10 23:33:57 2017
Starting background process DBRM
DIAG started with pid=8, OS id=8271
Starting background process VKRM
Mon Apr 10 23:33:57 2017
DBRM started with pid=9, OS id=8273
Starting background process PING
Mon Apr 10 23:33:57 2017
VKRM started with pid=10, OS id=8275
Mon Apr 10 23:33:57 2017
PING started with pid=11, OS id=8277
Starting background process ACMS
Mon Apr 10 23:33:57 2017
Starting background process DIA0
ACMS started with pid=12, OS id=8279
Mon Apr 10 23:33:57 2017
Starting background process LMON
DIA0 started with pid=13, OS id=8281
Mon Apr 10 23:33:57 2017
Starting background process LMD0
LMON started with pid=14, OS id=8283
Mon Apr 10 23:33:57 2017
Starting background process LMS0
LMD0 started with pid=15, OS id=8285
Mon Apr 10 23:33:57 2017
* Load Monitor used for high load check
* New Low - High Load Threshold Range = [1920 - 2560]
Starting background process RMS0
Mon Apr 10 23:33:57 2017
LMS0 started with pid=16, OS id=8287 at elevated (RT) priority
Mon Apr 10 23:33:57 2017
Starting background process LMHB
RMS0 started with pid=17, OS id=8291
Mon Apr 10 23:33:57 2017
Starting background process LCK1
LMHB started with pid=18, OS id=8293
Mon Apr 10 23:33:57 2017
Starting background process DBW0
LCK1 started with pid=19, OS id=8295
Mon Apr 10 23:33:57 2017
DBW0 started with pid=20, OS id=8297
Starting background process LGWR
Mon Apr 10 23:33:57 2017
Starting background process CKPT
LGWR started with pid=21, OS id=8299
Mon Apr 10 23:33:57 2017
CKPT started with pid=22, OS id=8301
Starting background process SMON
Mon Apr 10 23:33:57 2017
SMON started with pid=24, OS id=8305
Starting background process RECO
Mon Apr 10 23:33:57 2017
RECO started with pid=26, OS id=8309
Starting background process LREG
Mon Apr 10 23:33:57 2017
Starting background process PXMN
LREG started with pid=27, OS id=8311
Mon Apr 10 23:33:57 2017
Starting background process RBAL
PXMN started with pid=7, OS id=8313
Starting background process ASMB
Mon Apr 10 23:33:57 2017
RBAL started with pid=28, OS id=8315
Mon Apr 10 23:33:57 2017
ASMB started with pid=29, OS id=8317
Starting background process MMON
Starting background process MMNL
Mon Apr 10 23:33:57 2017
MMON started with pid=30, OS id=8319
Mon Apr 10 23:33:57 2017
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Mon Apr 10 23:33:57 2017
MMNL started with pid=31, OS id=8323
Mon Apr 10 23:33:57 2017
NOTE: ASMB registering with ASM instance as Standard client 0xffffffffffffffff (reg:593534517) (new connection)
Mon Apr 10 23:33:57 2017
starting up 1 shared server(s) ...
Mon Apr 10 23:33:57 2017
NOTE: ASMB connected to ASM instance +ASM2 osid: 8321 (Flex mode; client id 0xffffffffffffffff)
NOTE: initiating MARK startup
Starting background process MARK
Mon Apr 10 23:33:57 2017
lmon registered with NM - instance number 2 (internal mem no 1)
Mon Apr 10 23:33:57 2017
MARK started with pid=34, OS id=8329
Mon Apr 10 23:33:57 2017
NOTE: MARK has subscribed
Mon Apr 10 23:33:59 2017
Reconfiguration started (old inc 0, new inc 4)
List of instances (total 2) :
1 2
My inst 2 (I'm a new instance)
Global Resource Directory frozen
* allocate domain 0, invalid = TRUE
Communication channels reestablished
Mon Apr 10 23:33:59 2017
* domain 0 valid = 0 according to instance 1
Mon Apr 10 23:33:59 2017
Master broadcasted resource hash value bitmaps
Non-local Process blocks cleaned out
Mon Apr 10 23:33:59 2017
LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived
Set master node info
Submitted all remote-enqueue requests
Dwn-cvts replayed, VALBLKs dubious
All grantable enqueues granted
Mon Apr 10 23:33:59 2017
Post SMON to start 1st pass IR
Submitted all GCS remote-cache requests
Fix write in gcs resources
Mon Apr 10 23:34:00 2017
Reconfiguration complete (total time 0.8 secs)
Starting background process LCK0
Mon Apr 10 23:34:00 2017
LCK0 started with pid=38, OS id=8337
Mon Apr 10 23:34:00 2017
Using default pga_aggregate_limit of 2048 MB
Starting background process RSMN
Mon Apr 10 23:34:01 2017
RSMN started with pid=40, OS id=8351
Mon Apr 10 23:34:02 2017
Instance started by oraagent
Starting background process DMON
ORACLE_BASE from environment = /u01/app/oracle
Mon Apr 10 23:34:02 2017
DMON started with pid=41, OS id=8353
Mon Apr 10 23:34:03 2017
ALTER SYSTEM SET local_listener=' (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.62)(PORT=1521))' SCOPE=MEMORY SID='RAC2';
Mon Apr 10 23:34:03 2017
ALTER SYSTEM SET remote_listener=' collabn-cluster-scan.racattack:1521' SCOPE=MEMORY SID='RAC2';
ALTER DATABASE MOUNT /* db agent *//* {1:46642:1276} */
Mon Apr 10 23:34:04 2017
NOTE: ASMB mounting group 1 (DATA)
NOTE: Assigning number (1,0) to disk (/dev/asm-disk1)
NOTE: Assigning number (1,1) to disk (/dev/asm-disk2)
NOTE: Assigning number (1,2) to disk (/dev/asm-disk5)
SUCCESS: mounted group 1 (DATA)
NOTE: grp 1 disk 0: DATA_0000 path:/dev/asm-disk1
NOTE: grp 1 disk 1: DATA_0001 path:/dev/asm-disk2
NOTE: grp 1 disk 2: DATA_0002 path:/dev/asm-disk5
Mon Apr 10 23:34:04 2017
NOTE: dependency between database RAC and diskgroup resource ora.DATA.dg is established
Mon Apr 10 23:34:04 2017
NOTE: ASMB mounting group 2 (FRA)
NOTE: Assigning number (2,0) to disk (/dev/asm-disk3)
SUCCESS: mounted group 2 (FRA)
NOTE: grp 2 disk 0: FRA_0000 path:/dev/asm-disk3
Mon Apr 10 23:34:04 2017
NOTE: dependency between database RAC and diskgroup resource ora.FRA.dg is established
Mon Apr 10 23:34:14 2017
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
Starting background process TMON
Mon Apr 10 23:34:16 2017
ARCH: STARTING ARCH PROCESSES
TMON started with pid=45, OS id=8374
Starting background process ARC0
Mon Apr 10 23:34:16 2017
ARC0 started with pid=46, OS id=8376
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
Mon Apr 10 23:34:16 2017
ARC0: STARTING ARCH PROCESSES
Starting background process ARC1
Starting background process ARC2
Mon Apr 10 23:34:16 2017
ARC1 started with pid=47, OS id=8378
Starting background process ARC3
Mon Apr 10 23:34:16 2017
ARC2 started with pid=48, OS id=8380
Mon Apr 10 23:34:16 2017
ARC3 started with pid=49, OS id=8382
ARC1: Archival started
ARC2: Archival started
Mon Apr 10 23:34:16 2017
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC1: Thread not mounted
Mon Apr 10 23:34:16 2017
ARC2: Becoming the heartbeat ARCH
Mon Apr 10 23:34:16 2017
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Mon Apr 10 23:34:16 2017
ARC0: Thread not mounted
Mon Apr 10 23:34:16 2017
ARC3: Thread not mounted
Mon Apr 10 23:34:16 2017
ARC2: Thread not mounted
Mon Apr 10 23:34:17 2017
Network Resource Management enabled for Process LGWR (pid 8299) for Exadata I/O
Successful mount of redo thread 2, with mount id 2528954836
Mon Apr 10 23:34:22 2017
Allocated 3981120 bytes in shared pool for flashback generation buffer
Starting background process RVWR
Mon Apr 10 23:34:22 2017
RVWR started with pid=50, OS id=8385
Physical Standby Database mounted.
Lost write protection disabled
Completed: ALTER DATABASE MOUNT /* db agent *//* {1:46642:1276} */
Mon Apr 10 23:34:35 2017
Starting Data Guard Broker (DMON)
Starting background process INSV
Mon Apr 10 23:34:36 2017
INSV started with pid=42, OS id=8424
Starting background process NSV1
Mon Apr 10 23:34:39 2017
NSV1 started with pid=51, OS id=8432
Starting background process RSM0
Mon Apr 10 23:34:47 2017
RSM0 started with pid=52, OS id=8439
Mon Apr 10 23:34:51 2017
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Mon Apr 10 23:34:51 2017
ALTER SYSTEM SET log_archive_dest_2='' SCOPE=BOTH;
Mon Apr 10 23:34:51 2017
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Mon Apr 10 23:34:52 2017
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='RAC2';
Mon Apr 10 23:34:52 2017
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='RAC2';
Mon Apr 10 23:41:28 2017
Managed Standby Recovery not using Real Time Apply
Mon Apr 10 23:41:30 2017
ARC2: Becoming the active heartbeat ARCH
ARC2: Becoming the active heartbeat ARCH
Mon Apr 10 23:41:37 2017
Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 2
Mon Apr 10 23:41:40 2017
All dispatchers and shared servers shutdown
ALTER DATABASE CLOSE NORMAL
Stopping Emon pool
ORA-1109 signalled during: ALTER DATABASE CLOSE NORMAL...
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Mon Apr 10 23:41:40 2017
ARCH shutting down
ARC1: Archival stopped
Mon Apr 10 23:41:40 2017
ARCH shutting down
Mon Apr 10 23:41:40 2017
ARCH shutting down
Mon Apr 10 23:41:40 2017
ARC0: Archival stopped
Mon Apr 10 23:41:40 2017
ARC2: Relinquishing active heartbeat ARCH role
ARC2: Archival stopped
Mon Apr 10 23:41:40 2017
ARCH shutting down
ARC3: Archival stopped
Mon Apr 10 23:41:41 2017
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 5
Mon Apr 10 23:41:41 2017
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 15
Mon Apr 10 23:41:41 2017
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 2
Completed: ALTER DATABASE DISMOUNT
Mon Apr 10 23:41:41 2017
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Shutting down Data Guard Broker processes
Mon Apr 10 23:41:43 2017
NOTE: Deferred communication with ASM instance
Mon Apr 10 23:41:44 2017
Completed: Data Guard Broker shutdown
Mon Apr 10 23:41:45 2017
NOTE: force a map free for map id 2
Mon Apr 10 23:41:45 2017
Stopping background process VKTM
Mon Apr 10 23:41:45 2017
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Mon Apr 10 23:41:46 2017
NOTE: Shutting down MARK background process
Mon Apr 10 23:41:53 2017
NOTE: ASMB releasing group resources due to shutdown
NOTE: ASMB clearing idle groups before exit
Stopping background process RBAL
Mon Apr 10 23:41:55 2017
freeing rdom 0
Mon Apr 10 23:41:57 2017
Instance shutdown complete





Next, the original Standby (SingleInstance) node : the drcSTBY.log and alert_STBY.log

04/10/2017 23:31:03
Notifying Oracle Clusterware to prepare target standby database for switchover
04/10/2017 23:33:49
Deferring associated archivelog destinations of sites permanently disabled due to Switchover
Notifying Oracle Clusterware to buildup primary database after switchover
Switchover Complete
New Primary Site is named: stby
04/10/2017 23:41:22
Notifying DMON of db close
Notifying RSM0 of db close
04/10/2017 23:41:27
Data Guard Broker shutting down
RSM0 successfully terminated
2017-04-10 23:41:30.761 >> DMON Process Shutdown <<
04/10/2017 23:41:55
>> Starting Data Guard Broker bootstrap <<
Broker Configuration File Locations:
dg_broker_config_file1 = "/u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr1STBY.dat"
dg_broker_config_file2 = "/u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr2STBY.dat"
2017-04-10 23:41:55.856 DMON: Attach state object
2017-04-10 23:41:55.856 DMON: rfafoGetLocks reinitializing dubious PMYSHUT lock value block contents: sts=0, flags=0x0, spare1=0x0, spare2=0x0, cksm=0x0, rndm=0x0
2017-04-10 23:41:55.857 DMON: Broker state reconciled, version = 0, state = 00000000
2017-04-10 23:41:55.857 DMON: Broker State Initialized
2017-04-10 23:41:55.857 Version = 1
2017-04-10 23:41:55.857 State = 00000000
2017-04-10 23:41:55.857 DMON: Entered rfm_get_chief_lock() for CTL_BOOTSTRAP, reason 2
2017-04-10 23:41:55.857 7fffffff 0 DMON: Entered rfm_get_chief_lock() for CTL_BOOTSTRAP, reason 0
2017-04-10 23:41:58.902 7fffffff 0 DMON: start task execution: broker initialization
2017-04-10 23:41:58.902 DMON: Boot configuration (0.0.0), loading from "/u01/app/oracle/product/12.1.0/dbhome_1/dbs/dr1STBY.dat"
2017-04-10 23:41:58.903 DMON Registering service STBY_DGB with listener(s)
2017-04-10 23:41:58.903 DMON: Executing SQL [ALTER SYSTEM REGISTER]
2017-04-10 23:41:58.904 SQL [ALTER SYSTEM REGISTER] Executed successfully
04/10/2017 23:41:58
Broker Configuration: "rac"
Protection Mode: Maximum Performance
Fast-Start Failover (FSFO): Disabled, flags=0x0, version=0
Primary Database: stby (0x02010000)
04/10/2017 23:42:11
Failed to connect to remote database rac. Error is ORA-12528
Failed to send message to site rac. Error code is ORA-12528.
Version Check Results:
Database rac returned ORA-12528
Creating process RSM0
04/10/2017 23:42:35
Processing shutdown notification from database rac, instance 1
Broker operation
Setting SHUTDOWN status for database rac
04/10/2017 23:45:20
Notifying DMON of db close
Notifying RSM0 of db close
04/10/2017 23:45:28
Data Guard Broker shutting down
RSM0 successfully terminated
2017-04-10 23:45:31.850 >> DMON Process Shutdown <<



=======================================================================================



Mon Apr 10 23:30:59 2017
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database
SWITCHOVER VERIFY COMPLETE
Mon Apr 10 23:31:00 2017
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY WARNING: no standby database is defined in LOG_ARCHIVE_DEST_n to protect this database if it is converted to a primary database
SWITCHOVER VERIFY COMPLETE
Mon Apr 10 23:31:08 2017
RFS[3]: Assigned to RFS process (PID:30383)
RFS[3]: Selected log 7 for thread 2 sequence 39 dbid 2519807290 branch 931825279
Mon Apr 10 23:31:08 2017
Archived Log entry 44 added for thread 2 sequence 39 ID 0x96312536 dest 1:
Mon Apr 10 23:31:08 2017
RFS[4]: Assigned to RFS process (PID:30386)
RFS[4]: Selected log 5 for thread 1 sequence 70 dbid 2519807290 branch 931825279
Mon Apr 10 23:31:09 2017
Archived Log entry 45 added for thread 1 sequence 70 ID 0x96312536 dest 1:
Mon Apr 10 23:31:09 2017
Resetting standby activation ID 2519803190 (0x96312536)
Mon Apr 10 23:31:09 2017
Media Recovery End-Of-Redo indicator encountered
Mon Apr 10 23:31:09 2017
Media Recovery Continuing
Media Recovery Waiting for thread 2 sequence 40
Mon Apr 10 23:31:10 2017
RFS[5]: Assigned to RFS process (PID:29959)
RFS[5]: Possible network disconnect with primary database
Mon Apr 10 23:31:15 2017
SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database.
Mon Apr 10 23:31:15 2017
ALTER DATABASE SWITCHOVER TO PRIMARY (STBY)
Maximum wait for role transition is 15 minutes.
Switchover: Media recovery is still active
Role Change: Canceling MRP - no more redo to apply
Mon Apr 10 23:31:15 2017
MRP0: Background Media Recovery cancelled with status 16037
Mon Apr 10 23:31:15 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_pr00_29969.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Mon Apr 10 23:31:16 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_pr00_29969.trc:
ORA-16037: user requested cancel of managed recovery operation
Mon Apr 10 23:31:16 2017
MRP0: Background Media Recovery process shutdown (STBY)
Mon Apr 10 23:31:17 2017
Role Change: Canceled MRP
Killing 1 processes (PIDS:30372) (all RFS) in order to disallow current and future RFS connections. Requested by OS process 30402
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_rmi_30402.trc
SwitchOver after complete recovery through change 3494835
Online logfile pre-clearing operation disabled by switchover
Online log /u01/app/oracle/oradata/STBY/onlinelog/group_1.283.931825279: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_1.257.931825281: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/STBY/onlinelog/group_2.284.931825283: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_2.258.931825287: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/STBY/onlinelog/group_3.290.931826413: Thread 2 Group 3 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_3.259.931826417: Thread 2 Group 3 was previously cleared
Online log /u01/app/oracle/oradata/STBY/onlinelog/group_4.291.931826417: Thread 2 Group 4 was previously cleared
Online log /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_4.260.931826421: Thread 2 Group 4 was previously cleared
Standby became primary SCN: 3494833
Switchover: Complete - Database mounted as primary
SWITCHOVER: completed request from primary database.
Mon Apr 10 23:31:21 2017
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Mon Apr 10 23:31:21 2017
ALTER SYSTEM SET log_archive_dest_2='service="rac1"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="rac" net_timeout=30','valid_for=(online_logfile,all_roles)' SCOPE=BOTH;
Mon Apr 10 23:31:21 2017
ARC0: Becoming the 'no SRL' ARCH
Mon Apr 10 23:31:21 2017
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
ALTER DATABASE OPEN
Mon Apr 10 23:31:21 2017
Data Guard Broker initializing...
Ping without log force is disabled
.
Mon Apr 10 23:31:21 2017
Assigning activation ID 2528970530 (0x96bd0722)
Mon Apr 10 23:31:21 2017
Thread 1 advanced to log sequence 72 (thread open)
Thread 1 opened at log sequence 72
Current log# 2 seq# 72 mem# 0: /u01/app/oracle/oradata/STBY/onlinelog/group_2.284.931825283
Current log# 2 seq# 72 mem# 1: /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_2.258.931825287
Successful open of redo thread 1
Mon Apr 10 23:31:21 2017
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Apr 10 23:31:21 2017
SMON: enabling cache recovery
Mon Apr 10 23:31:21 2017
******************************************************************
TT00: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Mon Apr 10 23:31:21 2017
Archived Log entry 46 added for thread 1 sequence 71 ID 0x96bd0722 dest 1:
Mon Apr 10 23:31:21 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_tt00_30433.trc:
ORA-16456: switchover to standby in progress or completed
Mon Apr 10 23:31:21 2017
Error 16456 for archive log file 2 to 'rac1'
Mon Apr 10 23:31:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_arc3_29915.trc:
ORA-16456: switchover to standby in progress or completed
Mon Apr 10 23:31:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_tt00_30433.trc:
ORA-16456: switchover to standby in progress or completed
Mon Apr 10 23:31:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_tt00_30433.trc:
ORA-16456: switchover to standby in progress or completed
Mon Apr 10 23:31:22 2017
FAL[server, ARC3]: Error 16456 creating remote archivelog file 'rac1'
ARC3: FAL archive failed with error 16456. See trace for details
Mon Apr 10 23:31:22 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_arc3_29915.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Mon Apr 10 23:31:22 2017
ORACLE Instance STBY - Archival Error. Archiver continuing.
Mon Apr 10 23:31:24 2017
[29938] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:4159104 end:4161254 diff:2150 ms (2.2 seconds)
Dictionary check beginning
Dictionary check complete
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Mon Apr 10 23:31:26 2017
SMON: enabling tx recovery
Starting background process SMCO
Mon Apr 10 23:31:26 2017
SMCO started with pid=33, OS id=30448
Mon Apr 10 23:31:28 2017
Database Characterset is AL32UTF8
Mon Apr 10 23:31:29 2017
Redo thread 2 internally disabled at seq 40 (CKPT)
Mon Apr 10 23:31:30 2017
ARC2: Archiving disabled thread 2 sequence 40
Mon Apr 10 23:31:30 2017
Archived Log entry 47 added for thread 2 sequence 40 ID 0x0 dest 1:
Mon Apr 10 23:31:31 2017
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process AQPC
Mon Apr 10 23:31:37 2017
AQPC started with pid=36, OS id=30543
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Mon Apr 10 23:31:39 2017
ARC0: STARTING ARCH PROCESSES
Starting background process ARC4
Mon Apr 10 23:31:39 2017
ARC4 started with pid=37, OS id=30553
ARC4: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Mon Apr 10 23:32:00 2017
Pluggable database PDB$SEED dictionary check beginning
Pluggable Database PDB$SEED Dictionary check complete
Database Characterset for PDB$SEED is AL32UTF8
Mon Apr 10 23:32:17 2017
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Mon Apr 10 23:32:36 2017
Opening pdb PDB$SEED (2) with no Resource Manager plan active
Shutting down archive processes
Mon Apr 10 23:32:40 2017
ARCH shutting down
ARC4: Archival stopped
Mon Apr 10 23:32:45 2017
Pluggable database PDB dictionary check beginning
Pluggable Database PDB Dictionary check complete
Database Characterset for PDB is AL32UTF8
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Mon Apr 10 23:32:58 2017
Cannot start service pdb, reason=-1
Starting background process CJQ0
Mon Apr 10 23:33:13 2017
CJQ0 started with pid=37, OS id=31055
Completed: ALTER DATABASE OPEN
ALTER PLUGGABLE DATABASE ALL OPEN
Mon Apr 10 23:33:15 2017
Database Characterset for PDB is AL32UTF8
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Mon Apr 10 23:33:36 2017
Shared IO Pool defaulting to 36MB. Trying to get it from Buffer Cache for process 29792.
Mon Apr 10 23:33:37 2017
Opening pdb PDB (3) with no Resource Manager plan active
Mon Apr 10 23:33:38 2017
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
Mon Apr 10 23:33:38 2017
Pluggable database PDB opened read write
Completed: ALTER PLUGGABLE DATABASE ALL OPEN
Mon Apr 10 23:33:38 2017
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='STBY';
Mon Apr 10 23:33:39 2017
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='STBY';
Mon Apr 10 23:33:39 2017
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH SID='*';
Mon Apr 10 23:33:39 2017
ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';
Mon Apr 10 23:33:39 2017
ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';
Mon Apr 10 23:33:39 2017
ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';
Mon Apr 10 23:33:40 2017
ALTER SYSTEM SET db_file_name_convert='+DATA/RAC','/u01/app/oracle/oradata/STBY','+FRA/RAC','/u01/app/oracle/fast_recovery_area/STBY' SCOPE=SPFILE;
Mon Apr 10 23:33:40 2017
ALTER SYSTEM SET log_file_name_convert='+DATA/RAC','/u01/app/oracle/oradata/STBY','+FRA/RAC','/u01/app/oracle/fast_recovery_area/STBY' SCOPE=SPFILE;
Mon Apr 10 23:33:40 2017
******************************************************************
TT00: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Mon Apr 10 23:33:41 2017
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Mon Apr 10 23:33:41 2017
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Mon Apr 10 23:33:41 2017
ALTER SYSTEM ARCHIVE LOG
Mon Apr 10 23:33:44 2017
Thread 1 advanced to log sequence 73 (LGWR switch)
Current log# 1 seq# 73 mem# 0: /u01/app/oracle/oradata/STBY/onlinelog/group_1.283.931825279
Current log# 1 seq# 73 mem# 1: /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_1.257.931825281
Mon Apr 10 23:33:46 2017
db_recovery_file_dest_size of 16384 MB is 0.73% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Mon Apr 10 23:33:46 2017
TT00: Standby redo logfile selected for thread 1 sequence 72 for destination LOG_ARCHIVE_DEST_2
Mon Apr 10 23:33:46 2017
Archived Log entry 48 added for thread 1 sequence 72 ID 0x96bd0722 dest 1:
Mon Apr 10 23:33:59 2017
TT00: Standby redo logfile selected for thread 1 sequence 73 for destination LOG_ARCHIVE_DEST_2
Mon Apr 10 23:34:47 2017
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;
Mon Apr 10 23:41:17 2017
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process CJQ0
Stopping background process MMNL
Stopping background process MMON
License high water mark = 11
ALTER DATABASE CLOSE NORMAL
Mon Apr 10 23:41:21 2017
SMON: disabling tx recovery
Mon Apr 10 23:41:21 2017
Stopping Emon pool
Stopping Emon pool
Mon Apr 10 23:41:22 2017
SMON: disabling cache recovery
Shutting down archive processes
Archiving is disabled
Mon Apr 10 23:41:24 2017
ARCH shutting down
Mon Apr 10 23:41:24 2017
ARC2: Archival stopped
Mon Apr 10 23:41:24 2017
ARCH shutting down
Mon Apr 10 23:41:24 2017
ARC3: Archival stopped
Mon Apr 10 23:41:24 2017
ARCH shutting down
ARC0: Relinquishing active heartbeat ARCH role
ARC0: Archival stopped
Mon Apr 10 23:41:24 2017
ARCH shutting down
ARC1: Archival stopped
Mon Apr 10 23:41:25 2017
Thread 1 closed at log sequence 73
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
Mon Apr 10 23:41:26 2017
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Shutting down Data Guard Broker processes
Mon Apr 10 23:41:30 2017
Completed: Data Guard Broker shutdown
Mon Apr 10 23:41:32 2017
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Mon Apr 10 23:41:32 2017
Stopping background process VKTM
Mon Apr 10 23:41:35 2017
Instance shutdown complete
Mon Apr 10 23:41:44 2017
Starting ORACLE instance (normal) (OS id: 1281)
Mon Apr 10 23:41:44 2017
CLI notifier numLatches:3 maxDescs:354
Mon Apr 10 23:41:44 2017
Due to limited space in shared pool (need 6094848 bytes, have 4194112 bytes), limiting Resource Manager entities from 2048 to 32
Mon Apr 10 23:41:44 2017
**********************************************************************
Mon Apr 10 23:41:44 2017
Dump of system resources acquired for SHARED GLOBAL AREA (SGA)

Mon Apr 10 23:41:44 2017
Per process system memlock (soft) limit = 128G
Mon Apr 10 23:41:44 2017
Expected per process system memlock (soft) limit to lock
SHARED GLOBAL AREA (SGA) into memory: 802M
Mon Apr 10 23:41:44 2017
Available system pagesizes:
4K, 2048K
Mon Apr 10 23:41:44 2017
Supported system pagesize(s):
Mon Apr 10 23:41:44 2017
PAGESIZE AVAILABLE_PAGES EXPECTED_PAGES ALLOCATED_PAGES ERROR(s)
Mon Apr 10 23:41:44 2017
4K Configured 4 204804 NONE
Mon Apr 10 23:41:44 2017
2048K 0 401 0 NONE
Mon Apr 10 23:41:44 2017
RECOMMENDATION:
Mon Apr 10 23:41:44 2017
1. For optimal performance, configure system with expected number
of pages for every supported system pagesize prior to the next
instance restart operation.
Mon Apr 10 23:41:44 2017
**********************************************************************
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Initial number of CPU is 2
Number of processor cores in the system is 2
Number of processor sockets in the system is 1
_ksb_restart_policy_times={0,60,120,240}
Picked latch-free SCN scheme 3
Autotune of undo retention is turned on.
IMODE=BR
ILAT =35
LICENSE_MAX_USERS = 0
SYS auditing is enabled
NOTE: remote asm mode is local (mode 0x1; from cluster type)
NOTE: Using default ASM root directory ASM
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
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_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name: Linux
Node name: oem132.racattack
Release: 2.6.39-400.17.1.el6uek.x86_64
Version: #1 SMP Fri Feb 22 18:16:18 PST 2013
Machine: x86_64
Using parameter settings in server-side spfile /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfileSTBY.ora
System parameters with non-default values:
_ksb_restart_policy_times= "0"
_ksb_restart_policy_times= "60"
_ksb_restart_policy_times= "120"
_ksb_restart_policy_times= "240"
sga_target = 800M
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"
compatible = "12.1.0.2.0"
log_archive_dest_1 = "location=USE_DB_RECOVERY_FILE_DEST"
log_archive_dest_1 = "valid_for=(ALL_LOGFILES, ALL_ROLES)"
log_archive_dest_2 = "service="rac1""
log_archive_dest_2 = "ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="rac" net_timeout=30"
log_archive_dest_2 = "valid_for=(online_logfile,all_roles)"
log_archive_dest_state_2 = "ENABLE"
log_archive_min_succeed_dest= 1
fal_server = "rac1"
log_archive_trace = 0
log_archive_config = "dg_config=(rac)"
log_archive_format = "%t_%s_%r.dbf"
log_archive_max_processes= 4
log_buffer = 5064K
archive_lag_target = 0
db_create_file_dest = "/u01/app/oracle/oradata"
db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size= 16G
standby_file_management = "MANUAL"
_compression_compatibility= "12.1.0.2.0"
remote_login_passwordfile= "EXCLUSIVE"
connection_brokers = "((TYPE=DEDICATED)(BROKERS=1))"
connection_brokers = "((TYPE=EMON)(BROKERS=1))"
plsql_warnings = "DISABLE:ALL"
result_cache_max_size = 4M
_gby_vector_aggregation_enabled= TRUE
_optimizer_vector_transformation= TRUE
core_dump_dest = "/u01/app/oracle/diag/rdbms/stby/STBY/cdump"
db_name = "RAC"
db_unique_name = "STBY"
optimizer_mode = "ALL_ROWS"
_optimizer_mode_force = TRUE
_always_anti_join = "CHOOSE"
_optimizer_null_aware_antijoin= TRUE
_optimizer_partial_join_eval= TRUE
_partition_view_enabled = TRUE
_b_tree_bitmap_plans = TRUE
_cpu_to_io = 0
_optimizer_extended_cursor_sharing= "UDO"
_optimizer_extended_cursor_sharing_rel= "SIMPLE"
_optimizer_adaptive_cursor_sharing= TRUE
_optimizer_cost_model = "CHOOSE"
_optimizer_undo_cost_change= "12.1.0.2"
_optimizer_system_stats_usage= TRUE
_new_sort_cost_estimate = TRUE
_complex_view_merging = TRUE
_unnest_subquery = TRUE
_optimizer_unnest_scalar_sq= TRUE
_eliminate_common_subexpr= TRUE
_pred_move_around = TRUE
_convert_set_to_join = FALSE
_px_ual_serial_input = TRUE
_px_minus_intersect = TRUE
_remove_aggr_subquery = TRUE
_optimizer_push_down_distinct= 0
_optimizer_cost_based_transformation= "LINEAR"
_optimizer_squ_bottomup = TRUE
_push_join_predicate = TRUE
_push_join_union_view = TRUE
_push_join_union_view2 = TRUE
_fast_full_scan_enabled = TRUE
_optimizer_skip_scan_enabled= TRUE
_optimizer_join_sel_sanity_check= TRUE
_optim_enhance_nnull_detection= TRUE
_parallel_broadcast_enabled= TRUE
_always_semi_join = "CHOOSE"
_ordered_nested_loop = TRUE
_optimizer_max_permutations= 2000
_nlj_batching_enabled = 1
query_rewrite_enabled = "TRUE"
_mmv_query_rewrite_enabled= TRUE
_local_communication_costing_enabled= TRUE
_left_nested_loops_random= TRUE
_improved_row_length_enabled= TRUE
_index_join_enabled = TRUE
_enable_type_dep_selectivity= TRUE
_improved_outerjoin_card = TRUE
_use_column_stats_for_function= TRUE
_subquery_pruning_enabled= TRUE
_subquery_pruning_mv_enabled= FALSE
_or_expand_nvl_predicate = TRUE
_table_scan_cost_plus_one= TRUE
_cost_equality_semi_join = TRUE
_new_initial_join_orders = TRUE
_optim_peek_user_binds = TRUE
_minimal_stats_aggregation= TRUE
_gs_anti_semi_join_allowed= TRUE
_optim_new_default_join_sel= TRUE
optimizer_dynamic_sampling= 2
_pre_rewrite_push_pred = TRUE
_optimizer_new_join_card_computation= TRUE
_union_rewrite_for_gs = "YES_GSET_MVS"
_generalized_pruning_enabled= TRUE
_optim_adjust_for_part_skews= TRUE
_optimizer_compute_index_stats= TRUE
_optimizer_push_pred_cost_based= TRUE
_optimizer_extend_jppd_view_types= TRUE
_optimizer_filter_pred_pullup= TRUE
_optimizer_connect_by_cost_based= TRUE
_optimizer_connect_by_combine_sw= TRUE
_optimizer_connect_by_elim_dups= TRUE
_connect_by_use_union_all= "TRUE"
_right_outer_hash_enable = TRUE
_replace_virtual_columns = TRUE
skip_unusable_indexes = TRUE
_optimizer_correct_sq_selectivity= TRUE
_optimizer_dim_subq_join_sel= TRUE
_query_rewrite_setopgrw_enable= TRUE
_optimizer_join_order_control= 3
_bloom_filter_enabled = TRUE
_bloom_folding_enabled = TRUE
_bloom_serial_filter = "ON"
_optimizer_join_elimination_enabled= TRUE
_gby_hash_aggregation_enabled= TRUE
_globalindex_pnum_filter_enabled= TRUE
_sql_model_unfold_forloops= "RUN_TIME"
_optimizer_cost_hjsmj_multimatch= TRUE
_optimizer_transitivity_retain= TRUE
_px_pwg_enabled = TRUE
_optimizer_cbqt_no_size_restriction= TRUE
_optimizer_enhanced_filter_push= TRUE
_optimizer_rownum_pred_based_fkr= TRUE
_optimizer_better_inlist_costing= "ALL"
_optimizer_or_expansion = "DEPTH"
_optimizer_outer_to_anti_enabled= TRUE
_optimizer_order_by_elimination_enabled= TRUE
_optimizer_star_tran_in_with_clause= TRUE
_optimizer_sortmerge_join_inequality= TRUE
_selfjoin_mv_duplicates = TRUE
_dimension_skip_null = TRUE
_optimizer_complex_pred_selectivity= TRUE
_bloom_pruning_enabled = TRUE
_first_k_rows_dynamic_proration= TRUE
_optimizer_distinct_elimination= TRUE
_optimizer_multi_level_push_pred= TRUE
_optimizer_group_by_placement= TRUE
_optimizer_distinct_placement= TRUE
_optimizer_coalesce_subqueries= TRUE
_optimizer_enable_density_improvements= TRUE
_optimizer_rownum_bind_default= 10
_optimizer_improve_selectivity= TRUE
_optimizer_native_full_outer_join= "FORCE"
_optimizer_ansi_join_lateral_enhance= TRUE
_optimizer_multi_table_outerjoin= TRUE
_optimizer_null_accepting_semijoin= TRUE
_optimizer_ansi_rearchitecture= TRUE
_optimizer_aggr_groupby_elim= TRUE
_optimizer_enable_extended_stats= TRUE
_pivot_implementation_method= "CHOOSE"
_optimizer_interleave_jppd= TRUE
_optimizer_fkr_index_cost_bias= 10
_optimizer_extended_stats_usage_control= 192
_optimizer_fast_pred_transitivity= TRUE
_optimizer_fast_access_pred_analysis= TRUE
_optimizer_unnest_disjunctive_subq= TRUE
_optimizer_unnest_corr_set_subq= TRUE
_optimizer_distinct_agg_transform= TRUE
_aggregation_optimization_settings= 0
_optimizer_eliminate_filtering_join= TRUE
_optimizer_join_factorization= TRUE
_optimizer_use_cbqt_star_transformation= TRUE
_optimizer_table_expansion= TRUE
_and_pruning_enabled = TRUE
_optimizer_use_feedback = TRUE
_optimizer_gather_feedback= TRUE
_optimizer_try_st_before_jppd= TRUE
_px_partition_scan_enabled= TRUE
_optimizer_false_filter_pred_pullup= TRUE
_optimizer_enable_table_lookup_by_nl= TRUE
_optimizer_cube_join_enabled= TRUE
_optimizer_outer_join_to_inner= TRUE
_optimizer_hybrid_fpwj_enabled= TRUE
_px_object_sampling_enabled= TRUE
_px_concurrent = TRUE
_px_replication_enabled = TRUE
_optimizer_full_outer_join_to_outer= TRUE
_px_filter_parallelized = TRUE
_px_filter_skew_handling = TRUE
_px_groupby_pushdown = "FORCE"
_px_parallelize_expression= TRUE
_optimizer_gather_stats_on_load= TRUE
_optimizer_batch_table_access_by_rowid= TRUE
_px_wif_dfo_declumping = "CHOOSE"
_px_wif_extend_distribution_keys= TRUE
_px_join_skew_handling = TRUE
_px_adaptive_dist_method = "CHOOSE"
_px_partial_rollup_pushdown= "ADAPTIVE"
_optimizer_dsdir_usage_control= 126
_px_cpu_autodop_enabled = TRUE
_px_single_server_enabled= TRUE
_optimizer_use_gtt_session_stats= TRUE
_optimizer_adaptive_plans= TRUE
_optimizer_strans_adaptive_pruning= TRUE
_optimizer_proc_rate_level= "BASIC"
_optimizer_use_histograms= TRUE
_adaptive_window_consolidator_enabled= TRUE
_optimizer_cluster_by_rowid= TRUE
_optimizer_cluster_by_rowid_control= 129
_distinct_agg_optimization_gsets= "CHOOSE"
_px_scalable_invdist = TRUE
_optimizer_reduce_groupby_key= TRUE
_optimizer_cluster_by_rowid_batched= TRUE
_optimizer_inmemory_table_expansion= TRUE
_optimizer_inmemory_gen_pushable_preds= TRUE
_optimizer_inmemory_autodop= TRUE
_optimizer_inmemory_access_path= TRUE
_px_external_table_default_stats= TRUE
_optimizer_nlj_hj_adaptive_join= TRUE
_optimizer_inmemory_bloom_filter= TRUE
_optimizer_inmemory_cluster_aware_dop= TRUE
_optimizer_inmemory_minmax_pruning= TRUE
dg_broker_start = TRUE
_diag_adr_trace_dest = "/u01/app/oracle/diag/rdbms/stby/STBY/trace"
enable_pluggable_database= TRUE
NOTE: remote asm mode is local (mode 0x1; from cluster type)
Starting background process PMON
Mon Apr 10 23:41:46 2017
PMON started with pid=2, OS id=1315
Starting background process PSP0
Mon Apr 10 23:41:46 2017
PSP0 started with pid=3, OS id=1317
Starting background process VKTM
Starting background process GEN0
Mon Apr 10 23:41:47 2017
VKTM started with pid=4, OS id=1321 at elevated (RT) priority
Mon Apr 10 23:41:47 2017
VKTM running at (1)millisec precision with DBRM quantum (100)ms
Mon Apr 10 23:41:47 2017
GEN0 started with pid=5, OS id=1327
Starting background process MMAN
Mon Apr 10 23:41:47 2017
MMAN started with pid=6, OS id=1329
Starting background process DIAG
Starting background process DBRM
Mon Apr 10 23:41:47 2017
DIAG started with pid=8, OS id=1333
Starting background process VKRM
Mon Apr 10 23:41:47 2017
DBRM started with pid=9, OS id=1335
Starting background process DIA0
Mon Apr 10 23:41:47 2017
VKRM started with pid=10, OS id=1337
Starting background process DBW0
Mon Apr 10 23:41:47 2017
DIA0 started with pid=11, OS id=1339
Starting background process LGWR
Mon Apr 10 23:41:47 2017
DBW0 started with pid=12, OS id=1342
Starting background process CKPT
Mon Apr 10 23:41:47 2017
LGWR started with pid=13, OS id=1344
Mon Apr 10 23:41:47 2017
CKPT started with pid=14, OS id=1347
Starting background process SMON
Mon Apr 10 23:41:47 2017
SMON started with pid=16, OS id=1355
Starting background process RECO
Starting background process LREG
Mon Apr 10 23:41:47 2017
RECO started with pid=18, OS id=1359
Starting background process PXMN
Mon Apr 10 23:41:47 2017
LREG started with pid=19, OS id=1361
Starting background process MMON
Mon Apr 10 23:41:47 2017
PXMN started with pid=20, OS id=1363
Starting background process MMNL
Mon Apr 10 23:41:47 2017
MMON started with pid=21, OS id=1365
Starting background process DMON
Mon Apr 10 23:41:47 2017
MMNL started with pid=22, OS id=1367
ORACLE_BASE from environment = /u01/app/oracle
Mon Apr 10 23:41:47 2017
DMON started with pid=23, OS id=1369
Mon Apr 10 23:41:47 2017
ALTER DATABASE MOUNT
Mon Apr 10 23:41:50 2017
Using default pga_aggregate_limit of 2048 MB
Mon Apr 10 23:41:52 2017
Successful mount of redo thread 1, with mount id 2528976638
Mon Apr 10 23:41:52 2017
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Mon Apr 10 23:41:55 2017
ALTER DATABASE OPEN
Mon Apr 10 23:41:55 2017
Data Guard Broker initializing...
Mon Apr 10 23:41:55 2017
Starting Data Guard Broker (DMON)
Starting background process INSV
Mon Apr 10 23:41:55 2017
INSV started with pid=24, OS id=1402
Mon Apr 10 23:41:56 2017
Data Guard Broker initialization complete
Data Guard: verifying database primary role...
Starting background process NSV0
Mon Apr 10 23:41:58 2017
NSV0 started with pid=25, OS id=1410


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=rac_DGB.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:01
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=rac_DGB.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:01
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=rac_DGB.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:01
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=rac_DGB.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:01
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:01
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:01
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:01
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:01
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=rac_DGB.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:11
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=rac_DGB.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:11
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=rac_DGB.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:11
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=rac_DGB.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:11
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:11
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:11
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:11
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:11
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Starting background process RSM0
Mon Apr 10 23:42:11 2017
RSM0 started with pid=26, OS id=1452
Mon Apr 10 23:42:12 2017
Data Guard: version check completed
Data Guard: primary role verified
Ping without log force is disabled
.
Starting background process TMON
Mon Apr 10 23:42:12 2017
LGWR: STARTING ARCH PROCESSES
Starting background process ARC0
Mon Apr 10 23:42:12 2017
TMON started with pid=27, OS id=1456
Mon Apr 10 23:42:12 2017
ARC0 started with pid=28, OS id=1460
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
Mon Apr 10 23:42:12 2017
ARC0: STARTING ARCH PROCESSES
Starting background process ARC1
Mon Apr 10 23:42:12 2017
ARC1 started with pid=29, OS id=1462
Starting background process ARC2
Starting background process ARC3
Mon Apr 10 23:42:13 2017
ARC2 started with pid=30, OS id=1464
Mon Apr 10 23:42:13 2017
ARC3 started with pid=31, OS id=1466
ARC1: Archival started
ARC2: Archival started
Mon Apr 10 23:42:13 2017
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Mon Apr 10 23:42:13 2017
ARC2: Becoming the heartbeat ARCH
Mon Apr 10 23:42:13 2017
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Mon Apr 10 23:42:13 2017
Thread 1 advanced to log sequence 74 (thread open)
Thread 1 opened at log sequence 74
Current log# 2 seq# 74 mem# 0: /u01/app/oracle/oradata/STBY/onlinelog/group_2.284.931825283
Current log# 2 seq# 74 mem# 1: /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_2.258.931825287
Successful open of redo thread 1
Mon Apr 10 23:42:13 2017
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Apr 10 23:42:13 2017
SMON: enabling cache recovery
Mon Apr 10 23:42:13 2017
******************************************************************
TT00: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:13
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:13
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:13
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:13
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Error 12528 received logging on to the standby
Mon Apr 10 23:42:13 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_tt00_1468.trc:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Error 12528 for archive log file 2 to 'rac1'
Mon Apr 10 23:42:13 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_tt00_1468.trc:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Mon Apr 10 23:42:13 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_tt00_1468.trc:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Mon Apr 10 23:42:13 2017
Archived Log entry 52 added for thread 1 sequence 73 ID 0x96bd0722 dest 1:


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:13
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:13
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:13
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:13
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Error 12528 received logging on to the standby
Mon Apr 10 23:42:13 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_arc0_1460.trc:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
FAL[server, ARC0]: Error 12528 creating remote archivelog file 'rac1'
ARC0: FAL archive failed with error 12528. See trace for details
Mon Apr 10 23:42:13 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_arc0_1460.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
Mon Apr 10 23:42:13 2017
ORACLE Instance STBY - Archival Error. Archiver continuing.
Mon Apr 10 23:42:14 2017
[1400] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:4809484 end:4810074 diff:590 ms (0.6 seconds)
Verifying minimum file header compatibility (11g) for tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Mon Apr 10 23:42:14 2017
SMON: enabling tx recovery
Starting background process SMCO
Mon Apr 10 23:42:14 2017
SMCO started with pid=34, OS id=1474
Mon Apr 10 23:42:14 2017
Database Characterset is AL32UTF8
No Resource Manager plan active
Mon Apr 10 23:42:16 2017
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Mon Apr 10 23:42:16 2017
******************************************************************
TT00: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Error 12528 received logging on to the standby


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Mon Apr 10 23:42:16 2017
Error 12528 received logging on to the standby
Mon Apr 10 23:42:16 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_arc2_1464.trc:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
PING[ARC2]: Heartbeat failed to connect to standby 'rac1'. Error is 12528.
Mon Apr 10 23:42:16 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_tt00_1468.trc:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Mon Apr 10 23:42:16 2017
Error 12528 for archive log file 2 to 'rac1'
Mon Apr 10 23:42:16 2017
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0


***********************************************************************

Fatal NI connect error 12528, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.78.61)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=RAC.racattack)(CID=(PROGRAM=oracle)(HOST=oem132.racattack)(USER=oracle))))

VERSION INFORMATION:
TNS for Linux: Version 12.1.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 12.1.0.2.0 - Production
Time: 10-APR-2017 23:42:16
Tracing not turned on.
Tns error struct:
ns main err code: 12564

TNS-12564: TNS:connection refused
ns secondary err code: 0
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Mon Apr 10 23:42:16 2017
Error 12528 received logging on to the standby
Mon Apr 10 23:42:16 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_arc2_1464.trc:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
PING[ARC2]: Heartbeat failed to connect to standby 'rac1'. Error is 12528.
Mon Apr 10 23:42:17 2017
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='STBY';
Mon Apr 10 23:42:17 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_tt00_1468.trc:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Mon Apr 10 23:42:17 2017
Errors in file /u01/app/oracle/diag/rdbms/stby/STBY/trace/STBY_tt00_1468.trc:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Mon Apr 10 23:42:17 2017
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='STBY';
Mon Apr 10 23:42:17 2017
replication_dependency_tracking turned off (no async multimaster replication found)
Mon Apr 10 23:42:18 2017
Thread 1 advanced to log sequence 75 (LGWR switch)
Current log# 1 seq# 75 mem# 0: /u01/app/oracle/oradata/STBY/onlinelog/group_1.283.931825279
Current log# 1 seq# 75 mem# 1: /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_1.257.931825281
Mon Apr 10 23:42:18 2017
Archived Log entry 53 added for thread 1 sequence 74 ID 0x96bd0722 dest 1:
Starting background process AQPC
Mon Apr 10 23:42:18 2017
AQPC started with pid=37, OS id=1489
Mon Apr 10 23:42:21 2017
Database Characterset for PDB$SEED is AL32UTF8
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Opening pdb PDB$SEED (2) with no Resource Manager plan active
Mon Apr 10 23:42:35 2017
ALTER SYSTEM SET log_archive_dest_state_2='RESET' SCOPE=BOTH;
Starting background process CJQ0
Completed: ALTER DATABASE OPEN
Mon Apr 10 23:42:39 2017
CJQ0 started with pid=50, OS id=1904
Mon Apr 10 23:42:58 2017
Shared IO Pool defaulting to 36MB. Trying to get it from Buffer Cache for process 1365.
===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
Mon Apr 10 23:42:59 2017
db_recovery_file_dest_size of 16384 MB is 0.84% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
alter pluggable database pdb open
Mon Apr 10 23:43:00 2017
Database Characterset for PDB is AL32UTF8
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
Mon Apr 10 23:43:15 2017
Opening pdb PDB (3) with no Resource Manager plan active
Pluggable database PDB opened read write
Completed: alter pluggable database pdb open
Mon Apr 10 23:45:07 2017
alter pluggable database pdb close
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Mon Apr 10 23:45:12 2017
Pluggable database PDB closed
Completed: alter pluggable database pdb close
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process CJQ0
Killed process oracle@oem132.racattack (Q002) with pid is 48, OS pid 1537
Stopping background process MMNL
Stopping background process MMON
License high water mark = 9
ALTER DATABASE CLOSE NORMAL
Mon Apr 10 23:45:20 2017
SMON: disabling tx recovery
Mon Apr 10 23:45:20 2017
Stopping Emon pool
Stopping Emon pool
Mon Apr 10 23:45:20 2017
SMON: disabling cache recovery
Shutting down archive processes
Archiving is disabled
Mon Apr 10 23:45:24 2017
ARCH shutting down
Mon Apr 10 23:45:24 2017
ARCH shutting down
ARC2: Archival stopped
Mon Apr 10 23:45:24 2017
ARC3: Archival stopped
Mon Apr 10 23:45:24 2017
ARCH shutting down
Mon Apr 10 23:45:24 2017
Mon Apr 10 23:45:24 2017
ARC1: Archival stoppedARCH shutting down

Mon Apr 10 23:45:24 2017
ARC0: Archival stopped
Mon Apr 10 23:45:25 2017
Thread 1 closed at log sequence 75
Successful close of redo thread 1
Completed: ALTER DATABASE CLOSE NORMAL
ALTER DATABASE DISMOUNT
Shutting down archive processes
Archiving is disabled
Completed: ALTER DATABASE DISMOUNT
Mon Apr 10 23:45:27 2017
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Shutting down Data Guard Broker processes
Mon Apr 10 23:45:31 2017
Completed: Data Guard Broker shutdown
Mon Apr 10 23:45:33 2017
Mon Apr 10 23:45:33 2017
ARCH: Archival disabled due to shutdown: 1089
Stopping background process VKTM
Shutting down archive processes
Archiving is disabled
Mon Apr 10 23:45:41 2017
Instance shutdown complete


Ignore the Fatal NI and TNS errrors.  I plan to address them later.

.
.
.

Categories: DBA Blogs

Pages