ORA-02153: invalid VALUES password string

I am trying to alter an oracle user with the values clause rather than supplying the password in plain text but I am getting the error.

ORA-02153: invalid VALUES password string


SQL> alter user harvey identified by values 'S:AE24159F69EAD93A06E2D274D4C3E493EEE134CBCAA88CE869AEE7A40AC4;T:2E3AC72D182037E1EEC8D07FC1AAA7315940BF742217A635DBE4A58BF3E56B524E593BF9D30BD6B3CD73C3B56
  2  A781A00DD56D35E1E2C531DA702D4B6BF9A4D6C21597BA18C11BAFED7D9B9627A179DB5;337BCAD956B180F8';
alter user harvey identified by values 'S:AE24159F69EAD93A06E2D274D4C3E493EEE134CBCAA88CE869AEE7A40AC4;T:2E3AC72D182037E1EEC8D07FC1AAA7315940BF742217A635DBE4A58BF3E56B524E593BF9D30BD6B3CD73C3B56
*
ERROR at line 1:
ORA-02153: invalid VALUES password string


SQL>


I could not get my head around what is this, but it looks like it is something to do with the values string.

I have noticed that when I run the get_ddl for user it returned the hash of password in multiple lines and I was copying and pasting it and hence getting the error.

In order to fix this I have to get that value in one line and then it works

SQL> alter user harvey identified by values 'S:AE24159F69EAD93A06E2D274D4C3E493EEE134CBCAA88CE869AEE7A40AC4;T:2E3AC72D182037E1EEC8D07FC1AAA7315940BF742217A635DBE4A58BF3E56B524E593BF9D30BD6B3CD73C3B56A781A00DD56D35E1E2C531DA702D4B6BF9A4D6C21597BA18C11BAFED7D9B9627A179DB5;337BCAD956B180F8';

User altered.

SQL>

Done deal :-) 

Getting error while trying to run "Login-AzureRmAccount" from powershell

I am getting the error below while trying to login to azure using the power shell

PS C:\WINDOWS\system32> Login-AzureRmAccount
Login-AzureRmAccount : The term 'Login-AzureRmAccount' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is
correct and try again.
At line:1 char:1
+ Login-AzureRmAccount
+ ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Login-AzureRmAccount:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException


PS C:\WINDOWS\system32>


In order to fix this I have done the installation of required module and it has started working fine.

PS C:\WINDOWS\system32> Install-Module -Name AzureRM -AllowClobber

PS C:\WINDOWS\system32>


But then I started getting another error:

PS C:\WINDOWS\system32> Login-AzureRmAccount
Login-AzureRmAccount : The 'Login-AzureRmAccount' command was found in the module 'AzureRM.profile', but the module could not be loaded. For more information, run 'Import-Module AzureRM.profile'.
At line:1 char:1
+ Login-AzureRmAccount
+ ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : ObjectNotFound: (Login-AzureRmAccount:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CouldNotAutoloadMatchingModule


PS C:\WINDOWS\system32>

Then another one :-( 

PS C:\WINDOWS\system32> Import-Module AzureRM.profile
Import-Module : File C:\Program Files\WindowsPowerShell\Modules\AzureRM.profile\5.8.2\AzureRM.Profile.psm1 cannot be loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies
at https:/go.microsoft.com/fwlink/?LinkID=135170.
At line:1 char:1
+ Import-Module AzureRM.profile
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : SecurityError: (:) [Import-Module], PSSecurityException
    + FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand

PS C:\WINDOWS\system32>

The I had to install the module 

PS C:\WINDOWS\system32> Install-Module -Name AzureRM.profile

PS C:\WINDOWS\system32>

Finally noticed that the execution policy is set to Restricted and it should be Unrestricted.


PS C:\WINDOWS\system32> Get-ExecutionPolicy
Restricted

PS C:\WINDOWS\system32> set-executionpolicy Unrestricted

PS C:\WINDOWS\system32>


PS C:\WINDOWS\system32> Get-ExecutionPolicy
Unrestricted

PS C:\WINDOWS\system32>

and there we go the azure login script is working now.


PS C:\WINDOWS\system32> Login-AzureRmAccount

Account                   SubscriptionName TenantId                             Environment
-------                   ---------------- --------                             -----------
XFtxxxxxxxx@xxxxxxxxxx.com Azuxxxx       00000000-0000-0000-0000-000000000000 AzureCloud



PS C:\WINDOWS\system32>

ORA-39181: Only partial table data may be exported due to fine grain access control on "OWNER"."TABLE_NAME"

Received an error while performing the export of an oracle database using System user.

ORA-39181: Only partial table data may be exported due to fine grain access control on "OWNER"."TABLE_NAME"

As the errors points that this is the issue with the fine grain access on the table. User surprisingly user system need exemption and can be done by

 GRANT EXEMPT ACCESS POLICY to System;

Next time you perform the export using system it should be all good.

Role is disabled for users when operation is done through plsql

Getting error like ORA-00942 or ORA-01933 or ORA-01031 and ORA-06512 or  PLS-00201 and ORA-06550 or ORA-28111 or ORA-01031: insufficient privileges or PLS-00201: identifier 'x' must be declared


If there is a user which has got some permission to do the following through the role


select a table, select a view, create a table, create a view, create a trigger


and the above operations are done using calling a plsql procedure then the operation will only work if the direct grants are done for the user running plsql block.


The reason is that when a plsql block is executed then only direct grants on that user are in use and grant done through the role will not work as the role will not kick in.


Example is as show below:

SQL> create user harv identified by abc123 default tablespace USERS temporary tablespace temp;

User created.

SQL> grant create session to harv;

Grant succeeded.

SQL> grant resource to harv;

Grant succeeded.

SQL> conn harv/abc123
Connected.
SQL> create or replace procedure harv.temp is
  2     var1 varchar2(4000);
  3  BEGIN
  4     var1:='create table harv.tab(
  5            id number)';
  6     EXECUTE IMMEDIATE var1;
  7  end temp;
  8  /

Procedure created.

SQL> exec harv.temp
BEGIN harv.temp; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "harv.TEMP", line 6
ORA-06512: at line 1


SQL> conn / as sysdba
Connected.
SQL>

SQL> grant create table to harv;

Grant succeeded.

SQL>

SQL> conn harv/abc123
Connected.
SQL> exec harv.temp

PL/SQL procedure successfully completed.


SQL>

Stopping / Killing oracle datapump job from SQL

There are two ways to stop the datapump job in oracle.

One way I have described earlier here in which we attach to the expdp session and kill_job from expdp command prompt.

However there is another way of doing the same thing and that is from SQL prompt.

In this post I will give step by step instructions on how to stop the job. I am using oracle 12c

I have got my export running and when looked under dba_datapump_jobs I can see it executing 

SQL> col owner_name for a20
SQL> col job_name for a30
SQL> col OPERATION for a20
SQL> SELECT owner_name, job_name, rtrim(operation) "OPERATION", rtrim(job_mode) "JOB_MODE", state, attached_sessions, DATAPUMP_SESSIONS, DEGREE
  2  FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;

OWNER_NAME           JOB_NAME                       OPERATION            JOB_MODE                       STATE                          ATTACHED_SESSIONS DATAPUMP_SESSIONS     DEGREE
-------------------- ------------------------------ -------------------- ------------------------------ ------------------------------ ----------------- ----------------- ----------
SYSTEM               SYS_EXPORT_FULL_01             EXPORT               FULL                           EXECUTING                                      1                 6          4

SQL>

This output have all the information to stop that job. Information needed is owner_name and job_name

Execute the command below as sysdba or system from SQL>

SQL> SET serveroutput on
SQL> set lines 270
SQL> DECLARE
  2   job1 NUMBER;
  3  BEGIN
  4   job1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_FULL_01','SYSTEM');
  5   DBMS_DATAPUMP.STOP_JOB (job1,1,0,20);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

Please note that it may take few minutes to return the prompt back, but you will immediately see the log entry in the export log that it is stopped

Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at Tue Sep 4 10:04:39 2018 elapsed 0 00:04:36

This execution of procedure will drop the table SYSTEM.SYS_EXPORT_FULL_01.

For documentation on STOP_JOB please refer back to the oracle documentation.

Running Tuning Adviser from sqlplus

-- Get the SQL ID
5s1uq1cdc2rzp

-- get the explain plan
select plan_table_output from table(dbms_xplan.display_cursor('5s1uq1cdc2rzp',null,'basic'));


-- Create tuning taks for the SQL ID
DECLARE
  v_tune_taskid  VARCHAR2(100);
BEGIN
  v_tune_taskid := dbms_sqltune.create_tuning_task (
                          sql_id      => '5s1uq1cdc2rzp',
                          scope       => dbms_sqltune.scope_comprehensive,
                          time_limit  => 30,
                          task_name   => 'sqltune_5s1uq1cdc2rzp',
                          description => 'Tuning task sql_id 5s1uq1cdc2rzp');
  dbms_output.put_line('taskid = ' || v_tune_taskid);
END;
/

-- Run the Tuning Task
exec dbms_sqltune.execute_tuning_task(task_name => 'sqltune_5s1uq1cdc2rzp');

-- Check the status for the task
select task_name, status
from dba_advisor_log
where owner = '&OWNER_YOU_RAN_TASK_AS';   -- sys in my case


-- Get the report
set long 10000;
set pagesize 1000
set linesize 220
set pagesize 24
select dbms_sqltune.report_tuning_task('sqltune_5s1uq1cdc2rzp') as output
from dual;

Change sys password in oracle 12c on RAC and dataguard

As part of the password change policy, we have to change the password of sys user. This can be difficult at times if we do not do it correctly. 

One of the scenario is as below:

Database type: RAC two nodes on primary with one node dataguard 
Location of password file on primary:  under ASM (+data/tst1ap/password/pwdtst1ap.8124.1248539452)
Location of password file on DR: file system (/u01/app/oracle/product/12.1.0.2/dbs/orapwtst1ad1)

Primary Nodes:
rac01n01
rac01n02

DR Node: racdr01n01

Primary database unique name: TST1AP
DR database unique name: TST1AD
New password unique name: P4ssworD



Steps: 
1. Check the name of the password file currently in use on primary side
2. Create new password file
3. Copy the new password file to DR
4. alter sys user on primary
5. grant sysdba to rman (or any other user which had sysdba access)

Actual Steps:

-- racn01 as oracle
srvctl config database -d TST1AP
orapwd file='+DATA' dbuniquename=tst1ap entries=10 force=y ignorecase=y 

-- racn01 as grid
ASMCMD> pwcopy pwdtst1ap.459.475125462 /tmp/orapwtst1ap1
copying +data/TST1AP/PASSWORD/pwdtst1ap.459.475125462 -> /tmp/orapwtst1ap1
ASMCMD> 

-- rac01n01 as grid
-- Remove old passwordfile
rm +data/tst1ap/password/pwdtst1ap.8124.1248539452

-- rac01n01 as oracle
srvctl config database -d TST1AP
scp -p /tmp/orapwtst1ap1 racdr01n01:/u01/app/oracle/product/12.1.0.2/dbs/orapwtst1ad1
srvctl config database -d TST1AP

-- rac01n01 as oracle
alter user sys identified by P4ssworD;

-- racdr01n01 as oracle
srvctl config database -d TST1AD
ls -lah  /u01/app/oracle/product/12.1.0.2/dbs/orapwtst1ad1

 -- rac01n01 on Primary as oracle
select * from v$pwfile_users;
grant sysdba to rman;
select * from v$pwfile_users;

ORA-16613: initialization in progress for database

getting the error ORA-16613 when trying to create DGMGRL configuration

DGMGRL> create configuration test_dg as primary database is 'testp' connect identifier is 'testp';
Error:
ORA-16613: initialization in progress for database

DGMGRL>

In this case the message is right. Do not work at the speed of the light. Let oracle start the processes and wait for a bit. After a minute it should be okay.

Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold

DGMGRL> show configuration

Configuration - t2est_dg

  Protection Mode: MaxPerformance
  Members:
  t2estp - Primary database
    t2estd - Physical standby database
      Warning: ORA-16857: standby disconnected from redo source for longer than specified threshold

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 23 seconds ago)

DGMGRL>

Noticed that the redo log file size and standby logfile on primary is 100M and on DR redo as well as standby log is 50M.

To get the size and status of the files query v$log and v$standby_log. The size of the log and standby logfile should be the same on primary and DR database.

First step is change the standby file management to manual and stop the real time log apply.

-- Primary

alter system set standby_file_management='MANUAL' scope=both sid='*';

alter system set log_archive_dest_state_2='defer' scope=both sid='*';

On DR stop the recovery process and change the file management to manual as well.

-- on DR

alter database recover managed standby database cancel;

alter system set standby_file_management='MANUAL' scope=both sid='*';

Now when you query from v$log and v$standby_log on DR you will see that all the log files have status of UNUSED and all standby logfiles have status of UNASSIGNED

This is perfect. Now what I have done it added some dummy files to log file and standby log file on dr  and then remove the 50M files .

After that I have added new 100M files with the proper group and all done.

-- on DR dummy files

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 31 ('+DATA','+FRA') SIZE 100M;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 32 ('+DATA','+FRA') SIZE 100M;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 33 ('+DATA','+FRA') SIZE 100M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 34 ('+DATA','+FRA') SIZE 100M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 35 ('+DATA','+FRA') SIZE 100M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 36 ('+DATA','+FRA') SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 100M; 

-- on DR correct files (this is to maintain the consistency of group 1,2,3,4....)

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 ('+DATA','+FRA') SIZE 100M;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ('+DATA','+FRA') SIZE 100M;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 3 ('+DATA','+FRA') SIZE 100M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ('+DATA','+FRA') SIZE 100M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5 ('+DATA','+FRA') SIZE 100M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 ('+DATA','+FRA') SIZE 100M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 100M; 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 100M; 

-- Remove the unwanted files

alter database drop logfile group 31;

alter database drop logfile group 32;

alter database drop logfile group 33;

alter database drop logfile group 34;

alter database drop logfile group 35;

alter database drop logfile group 36;

alter database drop logfile group 23;

alter database drop logfile group 24;

alter database drop logfile group 25;

alter database drop logfile group 26;

alter database drop logfile group 27;

alter database drop logfile group 28;

Once that is done.

I will just reset the parameters to original values on primary and DR

-- on primary and DR

alter system set standby_file_management='AUTO' scope=both sid='*';

-- on Primary

alter system set log_archive_dest_state_2='enable' scope=both sid='*';

Now if you check DGMGRL it is all good.

DGMGRL> show configuration

Configuration - t2est_dg

  Protection Mode: MaxPerformance

  Members:

  t2estp - Primary database

    t2estd - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS   (status updated 48 seconds ago)

DGMGRL> 

I will just restart the DR database using srvctl. That's it!!

Have a good day :-) 

RMAN list backup of database completed between dates

RMAN> list backup of database completed between "to_date('19/03/2018','DD/MM/YYYY')" and "to_date('21/03/2018','DD/MM/YYYY')";


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31418348 Incr 1  23.50M     SBT_TAPE    00:01:25     19-MAR-18     
        BP Key: 31418353   Status: UNAVAILABLE  Compressed: NO  Tag: TAG20180319T083057
        Handle: 1062101_HDB01_bbsu5kub_1_1   Media: V_23423423_4082593
  List of Datafiles in backup set 31418348
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/system01.dbf
  2    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/sysaux01.dbf
  3    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/undotbs01.dbf
  4    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/users01.dbf
  5    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/const01.dbf
  6    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/drsys01.dbf
  7    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/nic01.dbf
  8    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/nic02.dbf
  9    1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/nic03.dbf
  10   1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/inf_admin01.dbf
  11   1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/rollback01.dbf
  12   1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/aa01.dbf
  13   1  Incr 2816591309129 19-MAR-18 /harv/oradata/HDB01/ab.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31435758 Incr 1  70.75M     SBT_TAPE    00:01:50     19-MAR-18     
        BP Key: 31435763   Status: UNAVAILABLE  Compressed: NO  Tag: TAG20180319T203102
        Handle: 1062535_HDB01_bjsu6v4u_1_1   Media: V_23423423_4084511
  List of Datafiles in backup set 31435758
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/system01.dbf
  2    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/sysaux01.dbf
  3    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/undotbs01.dbf
  4    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/users01.dbf
  5    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/const01.dbf
  6    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/drsys01.dbf
  7    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/nic01.dbf
  8    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/nic02.dbf
  9    1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/nic03.dbf
  10   1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/inf_admin01.dbf
  11   1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/rollback01.dbf
  12   1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/aa01.dbf
  13   1  Incr 2816600475698 19-MAR-18 /harv/oradata/HDB01/ab.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31450103 Incr 0  11.90G     SBT_TAPE    00:11:13     20-MAR-18     
        BP Key: 31450108   Status: AVAILABLE  Compressed: NO  Tag: TAG20180320T063112
        Handle: 1062980_HDB01_brsu829m_1_1   Media: V_2371232_4086316
  List of Datafiles in backup set 31450103
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/system01.dbf
  2    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/sysaux01.dbf
  3    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/undotbs01.dbf
  4    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/users01.dbf
  5    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/const01.dbf
  6    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/drsys01.dbf
  7    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/nic01.dbf
  8    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/nic02.dbf
  9    0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/nic03.dbf
  10   0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/inf_admin01.dbf
  11   0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/rollback01.dbf
  12   0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/aa01.dbf
  13   0  Incr 2816607800382 20-MAR-18 /harv/oradata/HDB01/ab.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31451918 Incr 1  7.25M      SBT_TAPE    00:01:23     20-MAR-18     
        BP Key: 31451923   Status: AVAILABLE  Compressed: NO  Tag: TAG20180320T083049
        Handle: 1063057_HDB01_c3su89aa_1_1   Media: V_2371319_4086641
  List of Datafiles in backup set 31451918
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/system01.dbf
  2    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/sysaux01.dbf
  3    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/undotbs01.dbf
  4    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/users01.dbf
  5    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/const01.dbf
  6    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/drsys01.dbf
  7    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/nic01.dbf
  8    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/nic02.dbf
  9    1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/nic03.dbf
  10   1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/inf_admin01.dbf
  11   1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/rollback01.dbf
  12   1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/aa01.dbf
  13   1  Incr 2816609478907 20-MAR-18 /harv/oradata/HDB01/ab.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31467652 Incr 1  22.75M     SBT_TAPE    00:01:21     20-MAR-18     
        BP Key: 31467657   Status: AVAILABLE  Compressed: NO  Tag: TAG20180320T203055
        Handle: 1063477_HDB01_cbsu9jgh_1_1   Media: V_2370174_4088496
  List of Datafiles in backup set 31467652
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/system01.dbf
  2    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/sysaux01.dbf
  3    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/undotbs01.dbf
  4    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/users01.dbf
  5    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/const01.dbf
  6    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/drsys01.dbf
  7    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/nic01.dbf
  8    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/nic02.dbf
  9    1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/nic03.dbf
  10   1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/inf_admin01.dbf
  11   1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/rollback01.dbf
  12   1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/aa01.dbf
  13   1  Incr 2816617482420 20-MAR-18 /harv/oradata/HDB01/ab.dbf

RMAN>

RMAN using TAG to recover database in RAC from backup

I want to recover database from backup using the TAG.

Please note that if use the duplicate command below then it will give you an error 

DUPLICATE DATABASE prd1db TO tes1db from tag 'TAG20180319T142455' NOFILENAMECHECK;

So what can be done?

The solution is find the scn number attached to controlfile under RMAN backups and us it to create the duplicate database as:

DUPLICATE DATABASE prd1db TO tes1db until SCN 2816323557926 NOFILENAMECHECK;

log in to RMAN using the recovery catalog after setting the env for source database in our case it is prd1db.

$ORACLE_HOME/bin/rman target / catalog rman@rcat

RMAN> list backup;

List of Backup Sets

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

Please note that above command will list all the backups for the database. You can try getting the list between time stamps if you know.

The output of the above command will be something like 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

31431944 Incr 0  66.38G     SBT_TAPE    00:07:35     19-MAR-18      

        BP Key: 31431951   Status: AVAILABLE  Compressed: NO  Tag: TAG20180319T142455

        Handle: 1062193_TES1DB_66su69lo_1_1   Media: V_2370411_4083354

  List of Datafiles in backup set 31431944

  File LV Type Ckp SCN    Ckp Time  Name

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

  2    0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1362.971174187

  3    0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/sysaux.1307.971174905

  6    0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/users.1282.971175289

  7    0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1361.971174187

  10   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1360.971174187

  13   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1359.971174189

  16   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1358.971174189

  19   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1357.971174189

  22   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1356.971174189

  25   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1355.971174189

  28   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/enterprise.1288.971175261

  31   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1338.971174499

  34   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1337.971174499

  39   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1291.971175153

  41   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/refmu.1284.971175261

  42   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1336.971174499

  45   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1335.971174499

  48   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1334.971174499

  51   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1333.971174501

  54   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/rds.1283.971175261

  57   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1332.971174501

  60   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1331.971174501

  63   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1314.971174903

  66   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts.1286.971175261

  69   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/repo_mds.1287.971175261

  70   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts_attrstore.1285.971175261

  72   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1313.971174905

  75   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1312.971174905

  78   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1311.971174905

  81   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1310.971174905

  85   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1309.971174905

  88   0  Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1308.971174905

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

31431945 Incr 0  65.54G     SBT_TAPE    00:07:43     19-MAR-18      

        BP Key: 31431952   Status: AVAILABLE  Compressed: NO  Tag: TAG20180319T142455

        Handle: 1062193_TES1DB_65su69lo_1_1   Media: V_2368252_4083353

  List of Datafiles in backup set 31431945

  File LV Type Ckp SCN    Ckp Time  Name

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

  4    0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/undotbs1.1353.971174219

  9    0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1350.971174219

  12   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1348.971174219

  15   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1346.971174219

  18   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1344.971174219

  21   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1342.971174219

  24   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1340.971174219

  30   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1339.971174219

  33   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1323.971174569

  36   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1293.971174927

  37   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1292.971175151

  38   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1322.971174569

  44   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1321.971174571

  47   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1320.971174571

  50   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1319.971174571

  56   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1318.971174571

  59   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1317.971174571

  62   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1315.971174815

  71   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1299.971174927

  74   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1298.971174927

  77   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1297.971174927

  80   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1296.971174927

  83   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1295.971174927

  87   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1294.971174927

  89   0  Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1290.971175175

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

31431946 Incr 0  35.00M     SBT_TAPE    00:00:01     19-MAR-18      

        BP Key: 31431953   Status: AVAILABLE  Compressed: NO  Tag: TAG20180319T142455

        Handle: 1062193_TES1DB_68su6a4b_1_1   Media: V_2370411_4083355

  Control File Included: Ckp SCN: 2816323557926   Ckp time: 19-MAR-18

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

31431947 Incr 0  1.00M      SBT_TAPE    00:00:00     19-MAR-18      

        BP Key: 31431954   Status: AVAILABLE  Compressed: NO  Tag: TAG20180319T142455

        Handle: 1062193_TES1DB_69su6a4d_1_1   Media: V_2370411_4083357

  SPFILE Included: Modification time: 19-MAR-18

  SPFILE db_unique_name: TES1DBP

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

31431948 Incr 0  67.01G     SBT_TAPE    00:06:50     19-MAR-18      

        BP Key: 31431955   Status: AVAILABLE  Compressed: NO  Tag: TAG20180319T142455

        Handle: 1062193_TES1DB_67su6a4a_1_1   Media: V_2368252_4083356

  List of Datafiles in backup set 31431948

  File LV Type Ckp SCN    Ckp Time  Name

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

  1    0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/system.1300.971174923

  5    0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/undotbs2.1354.971174219

  8    0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1352.971174219

  11   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1351.971174219

  14   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1349.971174219

  17   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1347.971174219

  20   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1345.971174219

  23   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1343.971174219

  26   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/const.1280.971175313

  27   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/cdrnps.1281.971175291

  29   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1341.971174219

  32   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1330.971174557

  35   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1329.971174557

  40   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/dart_admin.1275.971175321

  43   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1328.971174557

  46   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1327.971174557

  49   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1326.971174557

  52   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1325.971174559

  53   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/repo_disco_ptm5_cache.1274.971175339

  55   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/repo_disco_pstore.1279.971175321

  58   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1324.971174559

  61   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1316.971174805

  64   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1306.971174923

  65   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts_default.1277.971175321

  67   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts_battrstore.1276.971175321

  68   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts_ct_store.1278.971175321

  73   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1305.971174923

  76   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1304.971174923

  79   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1303.971174923

  82   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1302.971174923

  84   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1289.971175203

  86   0  Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1301.971174923

Life is not easy, in my case the source database had many incarnations so best find the incarnation you want to use

RMAN> list incarnation of database prd1db;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

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

70111   70159   PRD1DB  2306850230       PARENT  1          17-SEP-11

70111   70112   PRD1DB  2306850230       PARENT  995548     18-FEB-13

70111   72689   PRD1DB  2306850230       PARENT  1250093    21-FEB-13

70111   388729  PRD1DB  2306850230       PARENT  2790127477836 04-JUN-13

70111   390747  PRD1DB  2306850230       PARENT  2790137436622 05-JUN-13

70111   445093  PRD1DB  2306850230       PARENT  2790285891826 19-JUN-13

70111   447758  PRD1DB  2306850230       CURRENT 2790286767652 19-JUN-13

3756286 3756430 PRD1DB  2368813298       PARENT  22644025   02-OCT-13

3756286 3756287 PRD1DB  2368813298       CURRENT 2797234764372 22-JAN-15

7714199 7714327 PRD1DB  2397641027       PARENT  22644025   02-OCT-13

7714199 7714200 PRD1DB  2397641027       CURRENT 2801717021314 15-DEC-15

31308663 31308850 PRD1DB  2469971143       PARENT  1594143    11-MAY-16

31308663 31308664 PRD1DB  2469971143       CURRENT 2816323493241 15-MAR-18

31418462 31418649 PRD1DB  2470302757       PARENT  1594143    11-MAY-16

31418462 31418463 PRD1DB  2470302757       CURRENT 2816323493241 19-MAR-18

RMAN>

things for out interest are in red above. In nutshell my SCN number is 2816323557926 and DBID for the source database is 2470302757 

Let's do the duplicate database. Make sure /etc/oratab has the tes1dbp1 

Create the init_aux.ora file on the server where you want to create the new database tes1db

-- Create init parameter file to start the dupication

vi /u01/app/oracle/product/12.1.0.2/dbs/init_aux.ora 

*.audit_file_dest='/u01/app/oracle/admin/tes1dbp/adump' 

*.compatible='12.1.0.2.0' 

*.db_create_file_dest='+DATA' 

*.db_domain='example.com'

*.db_name='tes1db'

*.db_recovery_file_dest='+FRA' 

*.db_recovery_file_dest_size=180G 

*.db_unique_name='tes1dbp'

*.standby_file_management='AUTO'

*.sga_target=2399141888

*.job_queue_processes=0

. oraenv

tes1dbp1

cd /u01/app/oracle/product/12.1.0.2/dbs/

sqlplus "/as sysdba" 

startup nomount pfile='?/dbs/init_aux.ora';

exit;

create the RMAN session (ofcourse the tnsnames.ora file and listener.ora files are updated and done lsnrctl reload listener as grid)

rman

connect auxiliary sys/<sys password>@tes1dbp_DGMGRL

connect catalog rman/<rman password>@rcat

on RMAN prompt run the following command 

run {

set dbid 2470302757

allocate auxiliary channel ch1 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base/libobk.so,BLKSIZE=262144" TRACE 0;

allocate auxiliary channel ch2 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base/libobk.so,BLKSIZE=262144" TRACE 0;

allocate auxiliary channel ch3 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base/libobk.so,BLKSIZE=262144" TRACE 0;

allocate auxiliary channel ch4 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base/libobk.so,BLKSIZE=262144" TRACE 0;

DUPLICATE DATABASE prd1db TO tes1db until SCN 2816323557926 NOFILENAMECHECK;

}

Now the above command will succeed and the prd1db will be duplicated to tes1db up to the SCN 2816323557926  and that SCN is for TAG TAG20180319T142455

ORA-00851: SGA_MAX_SIZE 15032385536 cannot be set to more than MEMORY_TARGET 1073741824.

Trying to start one of the RAC nodes and getting the error message that MEMORY_TARGET is smaller than SGA_MAX_SIZE

in my case I had disabled the AMM and i should not be using memory_target parameter. To get it fixed what I did was.

Error message:

[oracle@rac01 (dev1tesp1) dbs]$ srvctl start database -d dev1tesp -i dev1tesp1
PRKO-2002 : Invalid command line option: -i
[oracle@rac01 (dev1tesp1) dbs]$ srvctl start instance -d dev1tesp -i dev1tesp1
PRCR-1013 : Failed to start resource ora.dev1tesp.db
PRCR-1064 : Failed to start resource ora.dev1tesp.db on node rac01
CRS-5017: The resource action "ora.dev1tesp.db start" encountered the following error:
ORA-00844: Parameter not taking MEMORY_TARGET into account
ORA-00851: SGA_MAX_SIZE 15032385536 cannot be set to more than MEMORY_TARGET 1073741824.
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/rac01/crs/trace/crsd_oraagent_oracle.trc".

CRS-2674: Start of 'ora.dev1tesp.db' on 'rac01' failed
[oracle@rac01 (dev1tesp1) dbs]$


Created the pfile from spfile 

SQL> create pfile='/u01/app/oracle/product/12.1.0.2/dbs/initdev1tesp1_change.ora' from spfile='+DATA/dev1tesp/spfiledev1tesp.ora';

File created.

SQL>


Then remove the parameter 
*.memory_target=1073741824

Try to startup database with pfile as there is no memory_target values set in that pfile

SQL> startup pfile='/u01/app/oracle/product/12.1.0.2/dbs/initdev1tesp1_change.ora';
ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated
ORA-32006: SEC_CASE_SENSITIVE_LOGON initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area 1.5032E+10 bytes
Fixed Size                  7660464 bytes
Variable Size            6006246480 bytes
Database Buffers         8992587776 bytes
Redo Buffers               25890816 bytes
Database mounted.
Database opened.
SQL>

re-create spfile

SQL> create spfile='+DATA/dev1tesp/spfiledev1tesp.ora' from pfile='/u01/app/oracle/product/12.1.0.2/dbs/initdev1tesp1_change.ora';

File created.

SQL>


Checks status of database 

[oracle@rac01 (dev1tesp1) dbs]$ srvctl status database -d dev1tesp
Instance dev1tesp1 is running on node rac01
Instance dev1tesp2 is running on node racdev01n02
[oracle@rac01 (dev1tesp1) dbs]$

[oracle@rac01 (dev1tesp1) dbs]$ srvctl status service -d dev1tesp
Service dev1tes_ha is running on instance(s) dev1tesp1,dev1tesp2
[oracle@rac01 (dev1tesp1) dbs]$


Stop the instance and restart it 

[oracle@rac01 (dev1tesp1) dbs]$ srvctl stop service -d dev1tesp -s dev1tes_ha -i dev1tesp1
[oracle@rac01 (dev1tesp1) dbs]$ srvctl status service -d dev1tesp
Service dev1tes_ha is running on instance(s) dev1tesp2
[oracle@rac01 (dev1tesp1) dbs]$


[oracle@rac01 (dev1tesp1) dbs]$ srvctl stop instance -d dev1tesp -i dev1tesp1
[oracle@rac01 (dev1tesp1) dbs]$
[oracle@rac01 (dev1tesp1) dbs]$
[oracle@rac01 (dev1tesp1) dbs]$ srvctl start instance -d dev1tesp -i dev1tesp1
[oracle@rac01 (dev1tesp1) dbs]$ srvctl status database -d dev1tesp
Instance dev1tesp1 is running on node rac01
Instance dev1tesp2 is running on node racdev01n02
[oracle@rac01 (dev1tesp1) dbs]$ srvctl status service -d dev1tesp
Service dev1tes_ha is running on instance(s) dev1tesp1,dev1tesp2
[oracle@rac01 (dev1tesp1) dbs]$

Puppet learning 0curl: (7) Failed connect to learning.puppetlabs.vm:8140; Connection refused

Trying the very first few commands from the tutorial of puppet and getting the error:

learning@hello: $ curl -k https://learning.puppetlabs.vm:8140/packages/current/install.bash | sudo bash                                                                                                                                   
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0curl: (7) Failed connect to learning.puppetlabs.vm:8140; Connection refused
[~]
learning@hello: $


After searching some time over the net the solution is restart pe-puppetserver

root@learning: # service pe-puppetserver restart
Redirecting to /bin/systemctl restart pe-puppetserver.service
[~]
root@learning: #



Then run the command again and issue is all sorted now.


learning@hello: $ curl -k https://learning.puppetlabs.vm:8140/packages/current/install.bash | sudo bash
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 25790  100 25790    0     0   149k      0 --:--:-- --:--:-- --:--:--  149k
Loaded plugins: fastestmirror, ovl
Repository 'local' is missing name in configuration, using id
Cleaning repos: pe_repo
Cleaning up everything
Maybe you want: rm -rf /var/cache/yum, to also free up space taken by orphaned data from disabled or removed repos
Cleaning up list of fastest mirrors
.
.
.
.
.
.
  ensure => 'link',
  target => '/opt/puppetlabs/puppet/bin/puppet',
}
Notice: /File[/usr/local/bin/pe-man]/ensure: created
file { '/usr/local/bin/pe-man':
  ensure => 'link',
  target => '/opt/puppetlabs/puppet/bin/pe-man',
}
Notice: /File[/usr/local/bin/hiera]/ensure: created
file { '/usr/local/bin/hiera':
  ensure => 'link',
  target => '/opt/puppetlabs/puppet/bin/hiera',
}
[~]
learning@hello: $

ORA-10631: SHRINK clause should not be specified for this object

Trying to shrink space on a table and getting the error message

ORA-10631: SHRINK clause should not be specified for this object

Issue: The table has got a functional based index.

Solution:

Get the DDL for the index.
Remove the index 
Perform the shrink operation
re-create the index

Under standing /etc/resolv.conf

When your request from local machine has to go out and you need to find the IP address or domain name of a remote computer then you need some help from DNS.

In order to reach out to your preferred DNS Server one need to setup /etc/resolv.conf properly.

At minimum you can have at least values for two variables

search
nameserver


search: Is the list of the domains which the location machine can search to get the answer.
nameserver: Is the IP address of the DNS Server in those domain. There can be maximum of 3 nameservers and it is setup in (/usr/include/resolv.h)  with value
# define MAXNS                  3       /* max # name servers we'll track */

Let's test it. My setting is

My computer is on a domain and then on my computer I have virtualbox network setup of machines

My Domain:                   appowl247.com
My Virtualbox Domain: example.com
DNS on exmaple.com:   192.168.2.150
Two DNS on appowl274.com : xxx.xxx.x.xxx and yyy.yyy.yy.yy

Setting on my DNS for example.com

[root@dns ~]# cat /etc/resolv.conf
# Generated by NetworkManager
domain appowl247.com
search appowl247.com example.com
nameserver xxx.xxx.x.xxx
nameserver yyy.yyy.yy.yy
[root@dns ~]#

Test:
I will try to resolve the name for
wa01.example.com
google.com
hdhillon.appowl247.com



Scenario 1:

Let's create the /etc/resolv.conf file with appowl247.com and example.com

[root@wa01 ~]# cat /etc/resolv.conf
search example.com appowl247.com

nameserver 192.168.2.150

nameserver xxx.xxx.x.xxx
nameserver yyy.yyy.yy.yy

[root@wa01 ~]#


Below nslookup will work for example.com , appowl247.com and internet.

[root@wa01 ~]# nslookup wa01.example.com
Server:         192.168.2.150nslookup google.com

Address:        192.168.2.150#53

Name:   wa01.example.com
Address: 192.168.2.11

[root@wa01 ~]# nslookup google.com
nslookup hdhillon
Server:         xxx.xxx.x.xxx
Address:        xxx.xxx.x.xxx#53

Non-authoritative answer:
Name:   google.com
Address: 172.217.25.46

[root@wa01 ~]# nslookup hdhillon
Server:         xxx.xxx.x.xxx
Address:        xxx.xxx.x.xxx#53

Name:   hdhillon.appowl247.com
Address: 10.11.12.105

[root@wa01 ~]#


Scenario 2:

[root@wa01 ~]# cat /etc/resolv.conf
search example.com appowl247.com
nameserver 192.168.2.150

[root@wa01 ~]#

nslookup is not able to find the IP address of appowl247.com as well as internet

[root@wa01 ~]# nslookup wa01.example.com
Server:         192.168.2.150
Address:        192.168.2.150#53

Name:   wa01.example.com
Address: 192.168.2.11

[root@wa01 ~]# nslookup google.com
nslookup hdhillon
;; connection timed out; trying next origin
;; connection timed out; no servers could be reached

[root@wa01 ~]# nslookup hdhillon
;; connection timed out; trying next origin
;; connection timed out; no servers could be reached

[root@wa01 ~]#

Scenario 3:

[root@wa01 ~]# cat /etc/resolv.conf

search example.com 

nameserver 192.168.2.150

nameserver xxx.xxx.x.xxx

nameserver yyy.yyy.yy.yy

[root@wa01 ~]# 

nslookup is not able to find the IP address of my machine on appowl247.com

[root@wa01 ~]# nslookup wa01.example.com

Server:         192.168.2.150

Address:        192.168.2.150#53

Name:   wa01.example.com

Address: 192.168.2.11

[root@wa01 ~]# nslookup google.com

nslookup hdhillon

Server:         xxx.xxx.x.xxx

Address:        xxx.xxx.x.xxx#53

Non-authoritative answer:

Name:   google.com

Address: 172.217.25.46

[root@wa01 ~]# nslookup hdhillon

Server:         xxx.xxx.x.xxx

Address:        xxx.xxx.x.xxx#53

** server can't find hdhillon: NXDOMAIN

[root@wa01 ~]# 

Scenario 4:

[root@wa01 ~]# chattr +i /etc/resolv.conf
[root@wa01 ~]# cat /etc/resolv.conf
# Harvey additions
search example.com

nameserver 192.168.2.150

nameserver xxx.xxx.x.xxx
nameserver yyy.yyy.yy.yy

[root@wa01 ~]#

nslookup will not be able to locate the IP for machine on appowl247.com

[root@wa01 ~]# nslookup wa01.example.com
Server:         192.168.2.150
Address:        192.168.2.150#53

Name:   wa01.example.com
Address: 192.168.2.11

[root@wa01 ~]# nslookup google.com
nslookup hdhillon
Server:         xxx.xxx.x.xxx
Address:        xxx.xxx.x.xxx#53

Non-authoritative answer:
Name:   google.com
Address: 216.58.200.110

[root@wa01 ~]# nslookup hdhillon
Server:         xxx.xxx.x.xxx
Address:        xxx.xxx.x.xxx#53

** server can't find hdhillon: NXDOMAIN

[root@wa01 ~]#


Findings:

It is very important to consider adding all the domains you wish to search for to look at the DNS.

If domain name is missing under search then you will get an error like:
** server can't find <what ever>: NXDOMAIN


In case domain is present but the IP address of DNS is missing under nameserver then you will get
[root@wa01 ~]# nslookup google.com
;; connection timed out; trying next origin
;; connection timed out; no servers could be reached

In case domain is missing and but the IP address of DNS is present under nameserver then you will get

** server can't find <Something>: NXDOMAIN