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;