https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=435773428082446&id=759747.1&_afrWindowMode=0&_adf.ctrl-state=ppqrcpqcb_4
select wfno.msg_id
from applsys.aq$wf_notification_out wfno
where corr_id='APPS:ALR:'
and wfno.msg_state='READY';
Thursday, May 28, 2020
Wednesday, May 27, 2020
Thursday, May 21, 2020
Alter Scheduled Jobs
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE('HOURLY_INSERT', 'REPEAT_INTERVAL',
'FREQ=HOURLY;INTERVAL=1');
END;
CREATE TABLE BKUP_TEST
(
COL1 VARCHAR2(20 BYTE),
CAP_DATE TIMESTAMP(6) DEFAULT current_timestamp,
USERNAME VARCHAR2(10 BYTE) DEFAULT user
);
2. Create the procedure
CREATE OR REPLACE PROCEDURE INS_BKPTST
IS
BEGIN
insert into bkup_test(col1) values('Marines');
COMMIT;
END;
/
3
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SYS.HOURLY_INSERT'
,start_date => TO_TIMESTAMP_TZ('2020/04/30 20:06:43.996546 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=hourly;'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'INS_BKPTST'
,comments => NULL
);
END;
/
SELECT to_char(log_date, 'DD-MON-YY HH24:MI:SS') TIMESTAMP, job_name,
job_class, operation, status FROM USER_SCHEDULER_JOB_LOG
WHERE job_name ='HOURLY_INSERT';
select * from user_scheduler_job_run_details
BEGIN
DBMS_SCHEDULER.RUN_JOB(
JOB_NAME => 'HOURLY_INSERT',
USE_CURRENT_SESSION => FALSE);
END;
/
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS where job_name ='HOURLY_INSERT';
select job_name, owner, enabled from dba_scheduler_jobs where job_name ='HOURLY_INSERT';
exec dbms_scheduler.enable('HOURLY_INSERT');
select * from user_scheduler_job_run_details where job_name ='HOURLY_INSERT';
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE('HOURLY_INSERT', 'REPEAT_INTERVAL',
'FREQ=HOURLY;INTERVAL=1');
END;
select * from user_SCHEDULER_SCHEDULES where job_name ='HOURLY_INSERT';
DBMS_SCHEDULER.SET_ATTRIBUTE('HOURLY_INSERT', 'REPEAT_INTERVAL',
'FREQ=HOURLY;INTERVAL=1');
END;
CREATE TABLE BKUP_TEST
(
COL1 VARCHAR2(20 BYTE),
CAP_DATE TIMESTAMP(6) DEFAULT current_timestamp,
USERNAME VARCHAR2(10 BYTE) DEFAULT user
);
2. Create the procedure
CREATE OR REPLACE PROCEDURE INS_BKPTST
IS
BEGIN
insert into bkup_test(col1) values('Marines');
COMMIT;
END;
/
3
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SYS.HOURLY_INSERT'
,start_date => TO_TIMESTAMP_TZ('2020/04/30 20:06:43.996546 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=hourly;'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'INS_BKPTST'
,comments => NULL
);
END;
/
SELECT to_char(log_date, 'DD-MON-YY HH24:MI:SS') TIMESTAMP, job_name,
job_class, operation, status FROM USER_SCHEDULER_JOB_LOG
WHERE job_name ='HOURLY_INSERT';
select * from user_scheduler_job_run_details
BEGIN
DBMS_SCHEDULER.RUN_JOB(
JOB_NAME => 'HOURLY_INSERT',
USE_CURRENT_SESSION => FALSE);
END;
/
SELECT JOB_NAME, STATE FROM DBA_SCHEDULER_JOBS where job_name ='HOURLY_INSERT';
select job_name, owner, enabled from dba_scheduler_jobs where job_name ='HOURLY_INSERT';
exec dbms_scheduler.enable('HOURLY_INSERT');
select * from user_scheduler_job_run_details where job_name ='HOURLY_INSERT';
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE('HOURLY_INSERT', 'REPEAT_INTERVAL',
'FREQ=HOURLY;INTERVAL=1');
END;
select * from user_SCHEDULER_SCHEDULES where job_name ='HOURLY_INSERT';
HOW TO MOVE DATAFILES IN SQL SERVER
1. Set database offline
USE master
ALTER DATABASE STG SET OFFLINE;
-- 2. Move the file or files to the new location
ALTER DATABASE STG MODIFY FILE ( NAME = STG, FILENAME = 'F:\SQL_DATABASES\STG.mdf' );
-- 3. Set database online
ALTER DATABASE STG SET ONLINE;
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'STG');
exec sp_helpdb 'STG'
USE master
ALTER DATABASE STG SET OFFLINE;
-- 2. Move the file or files to the new location
ALTER DATABASE STG MODIFY FILE ( NAME = STG, FILENAME = 'F:\SQL_DATABASES\STG.mdf' );
-- 3. Set database online
ALTER DATABASE STG SET ONLINE;
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'STG');
exec sp_helpdb 'STG'
Wednesday, May 13, 2020
Where is cmlean.sql
REM
REM FILENAME
REM cmclean.sql
REM DESCRIPTION
REM Clean out the concurrent manager tables
REM NOTES
REM Usage: sqlplus @cmclean
REM
REM
REM $Id: cmclean.sql,v 1.4 2001/04/07 15:55:07 pferguso Exp $
REM
REM
REM +======================================================================+
set verify off;
set head off;
set timing off
set pagesize 1000
column manager format a20 heading 'Manager short name'
column pid heading 'Process id'
column pscode format a12 heading 'Status code'
column ccode format a12 heading 'Control code'
column request heading 'Request ID'
column pcode format a6 heading 'Phase'
column scode format a6 heading 'Status'
WHENEVER SQLERROR EXIT ROLLBACK;
DOCUMENT
WARNING : Do not run this script without explicit instructions
from Oracle Support
*** Make sure that the managers are shut down ***
*** before running this script ***
*** If the concurrent managers are NOT shut down, ***
*** exit this script now !! ***
#
accept answer prompt 'If you wish to continue type the word ''dual'': '
set feed off
select null from &answer;
set feed on
REM Update process status codes to TERMINATED
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating invalid process status codes in FND_CONCURRENT_PROCESSES
set feedback off
set head on
break on manager
SELECT concurrent_queue_name manager,
concurrent_process_id pid,
process_status_code pscode
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE process_status_code not in ('K', 'S')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id;
set head off
set feedback on
UPDATE fnd_concurrent_processes
SET process_status_code = 'K'
WHERE process_status_code not in ('K', 'S');
REM Set all managers to 0 processes
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating running processes in FND_CONCURRENT_QUEUES
prompt -- Setting running_processes = 0 and max_processes = 0 for all managers
UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0;
REM Reset control codes
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating invalid control_codes in FND_CONCURRENT_QUEUES
set feedback off
set head on
SELECT concurrent_queue_name manager,
control_code ccode
FROM fnd_concurrent_queues
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;
set feedback on
set head off
UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;
REM Also null out target_node for all managers
UPDATE fnd_concurrent_queues
SET target_node = null;
REM Set all 'Terminating' requests to Completed/Error
REM Also set Running requests to completed, since the managers are down
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating any Running or Terminating requests to Completed/Error canceled by CMCLEAN
set feedback off
set head on
SELECT request_id request,
phase_code pcode,
status_code scode
FROM fnd_concurrent_requests
WHERE status_code = 'T' OR phase_code = 'R'
ORDER BY request_id;
set feedback on
set head off
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'E'
WHERE status_code ='T' OR phase_code = 'R';
REM Set all Runalone flags to 'N'
REM This has to be done differently for Release 10
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating any Runalone flags to 'N'
prompt
set serveroutput on
set feedback off
declare
c pls_integer := dbms_sql.open_cursor;
upd_rows pls_integer;
vers varchar2(50);
tbl varchar2(50);
col varchar2(50);
statement varchar2(255);
begin
select substr(release_name, 1, 2)
into vers
from fnd_product_groups;
if vers >= 11 then
tbl := 'fnd_conflicts_domain';
col := 'runalone_flag';
else
tbl := 'fnd_concurrent_conflict_sets';
col := 'run_alone_flag';
end if;
statement := 'update ' || tbl || ' set ' || col || '=''N'' where ' || col || ' = ''Y''';
dbms_sql.parse(c, statement, dbms_sql.native);
upd_rows := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
dbms_output.put_line('Updated ' || upd_rows || ' rows of ' || col || ' in ' || tbl || ' to ''N''');
end;
/
prompt
prompt ------------------------------------------------------------------------
prompt Updates complete.
prompt Type commit now to commit these updates, or rollback to cancel.
prompt ------------------------------------------------------------------------
prompt
set feedback on
REM <= Last REM statment -----------------------------------------------------
REM FILENAME
REM cmclean.sql
REM DESCRIPTION
REM Clean out the concurrent manager tables
REM NOTES
REM Usage: sqlplus @cmclean
REM
REM
REM $Id: cmclean.sql,v 1.4 2001/04/07 15:55:07 pferguso Exp $
REM
REM
REM +======================================================================+
set verify off;
set head off;
set timing off
set pagesize 1000
column manager format a20 heading 'Manager short name'
column pid heading 'Process id'
column pscode format a12 heading 'Status code'
column ccode format a12 heading 'Control code'
column request heading 'Request ID'
column pcode format a6 heading 'Phase'
column scode format a6 heading 'Status'
WHENEVER SQLERROR EXIT ROLLBACK;
DOCUMENT
WARNING : Do not run this script without explicit instructions
from Oracle Support
*** Make sure that the managers are shut down ***
*** before running this script ***
*** If the concurrent managers are NOT shut down, ***
*** exit this script now !! ***
#
accept answer prompt 'If you wish to continue type the word ''dual'': '
set feed off
select null from &answer;
set feed on
REM Update process status codes to TERMINATED
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating invalid process status codes in FND_CONCURRENT_PROCESSES
set feedback off
set head on
break on manager
SELECT concurrent_queue_name manager,
concurrent_process_id pid,
process_status_code pscode
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE process_status_code not in ('K', 'S')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id;
set head off
set feedback on
UPDATE fnd_concurrent_processes
SET process_status_code = 'K'
WHERE process_status_code not in ('K', 'S');
REM Set all managers to 0 processes
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating running processes in FND_CONCURRENT_QUEUES
prompt -- Setting running_processes = 0 and max_processes = 0 for all managers
UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0;
REM Reset control codes
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating invalid control_codes in FND_CONCURRENT_QUEUES
set feedback off
set head on
SELECT concurrent_queue_name manager,
control_code ccode
FROM fnd_concurrent_queues
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;
set feedback on
set head off
UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;
REM Also null out target_node for all managers
UPDATE fnd_concurrent_queues
SET target_node = null;
REM Set all 'Terminating' requests to Completed/Error
REM Also set Running requests to completed, since the managers are down
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating any Running or Terminating requests to Completed/Error canceled by CMCLEAN
set feedback off
set head on
SELECT request_id request,
phase_code pcode,
status_code scode
FROM fnd_concurrent_requests
WHERE status_code = 'T' OR phase_code = 'R'
ORDER BY request_id;
set feedback on
set head off
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'E'
WHERE status_code ='T' OR phase_code = 'R';
REM Set all Runalone flags to 'N'
REM This has to be done differently for Release 10
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating any Runalone flags to 'N'
prompt
set serveroutput on
set feedback off
declare
c pls_integer := dbms_sql.open_cursor;
upd_rows pls_integer;
vers varchar2(50);
tbl varchar2(50);
col varchar2(50);
statement varchar2(255);
begin
select substr(release_name, 1, 2)
into vers
from fnd_product_groups;
if vers >= 11 then
tbl := 'fnd_conflicts_domain';
col := 'runalone_flag';
else
tbl := 'fnd_concurrent_conflict_sets';
col := 'run_alone_flag';
end if;
statement := 'update ' || tbl || ' set ' || col || '=''N'' where ' || col || ' = ''Y''';
dbms_sql.parse(c, statement, dbms_sql.native);
upd_rows := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
dbms_output.put_line('Updated ' || upd_rows || ' rows of ' || col || ' in ' || tbl || ' to ''N''');
end;
/
prompt
prompt ------------------------------------------------------------------------
prompt Updates complete.
prompt Type commit now to commit these updates, or rollback to cancel.
prompt ------------------------------------------------------------------------
prompt
set feedback on
REM <= Last REM statment -----------------------------------------------------
Target node/queue unavailable in R12
Shutdown application tier;
select control_code from FND_CONCURRENT_QUEUES WHERE concurrent_queue_name='OAMGCS_ARTEMIS';
select target_node from FND_CONCURRENT_QUEUES where concurrent_queue_name='OAMGCS_ARTEMIS';
update FND_CONCURRENT_QUEUES
set control_code = null
where concurrent_queue_name ='OAMGCS_ARTEMIS';
update FND_CONCURRENT_QUEUES set control_code=null where concurrent_queue_name ='OAMGCS_ARTEMIS';
update FND_CONCURRENT_QUEUES
set TARGET_NODE='ARTEMIS.ECP.PRIV'
where CONCURRENT_QUEUE_NAME='OAMGCS_ARTEMIS';
select control_code from FND_CONCURRENT_QUEUES WHERE concurrent_queue_name='OAMGCS_ARTEMIS';
select target_node from FND_CONCURRENT_QUEUES where concurrent_queue_name='OAMGCS_ARTEMIS';
update FND_CONCURRENT_QUEUES
set control_code = null
where concurrent_queue_name ='OAMGCS_ARTEMIS';
update FND_CONCURRENT_QUEUES set control_code=null where concurrent_queue_name ='OAMGCS_ARTEMIS';
update FND_CONCURRENT_QUEUES
set TARGET_NODE='ARTEMIS.ECP.PRIV'
where CONCURRENT_QUEUE_NAME='OAMGCS_ARTEMIS';
Monday, May 11, 2020
Oracle create automatic table inserts on a Table
I did this to test RMAN functionality.
Things we need
1. sample table
2. stored procedure that will do the insert
3. oracle scheduled job to execute the stored procedure.
CREATE TABLE BKUP_TEST
(
COL1 VARCHAR2(20 BYTE),
CAP_DATE TIMESTAMP(6) DEFAULT current_timestamp,
USERNAME VARCHAR2(10 BYTE) DEFAULT user
);
2. Create the procedure
CREATE OR REPLACE PROCEDURE INS_BKPTST
IS
BEGIN
insert into bkup_test(col1) values('Marines');
COMMIT;
END;
/
3
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SYS.HOURLY_INSERT'
,start_date => TO_TIMESTAMP_TZ('2020/04/30 20:03:43.996546 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=hourly;'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'INS_BKPTST'
,comments => NULL
);
END;
/
Things we need
1. sample table
2. stored procedure that will do the insert
3. oracle scheduled job to execute the stored procedure.
CREATE TABLE BKUP_TEST
(
COL1 VARCHAR2(20 BYTE),
CAP_DATE TIMESTAMP(6) DEFAULT current_timestamp,
USERNAME VARCHAR2(10 BYTE) DEFAULT user
);
2. Create the procedure
CREATE OR REPLACE PROCEDURE INS_BKPTST
IS
BEGIN
insert into bkup_test(col1) values('Marines');
COMMIT;
END;
/
3
BEGIN
SYS.DBMS_SCHEDULER.CREATE_JOB
(
job_name => 'SYS.HOURLY_INSERT'
,start_date => TO_TIMESTAMP_TZ('2020/04/30 20:03:43.996546 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
,repeat_interval => 'FREQ=hourly;'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => 'STORED_PROCEDURE'
,job_action => 'INS_BKPTST'
,comments => NULL
);
END;
/
SAMPLE
Marines 5/12/2020 10:03:43.260792 AM SYS
Marines 5/12/2020 9:03:43.129653 AM SYS
Marines 5/12/2020 8:03:44.021578 AM SYS
Marines 5/12/2020 7:03:43.877183 AM SYS
Node ID does Not Exist for the Current Application Server ID -FIX
connect as apps
select * from fnd_nodes;
no rows selected.
stop application
cd $ADMIN_SCRIPTS_HOME
./adstpall.sh
artemis:/dbapps/applmgr/HRCP/inst/apps/HRCP_artemis/admin/install>sh adgendbc.sh
adgendbc.sh started at Mon May 11 18:39:23 MST 2020
Enter the APPS username: apps
Enter the APPS password:
SQL*Plus: Release 10.1.0.5.0 - Production on Mon May 11 18:39:41 2020
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter value for 1: Enter value for 2: Enter value for 3: Connected.
[ APPS_DATABASE_ID ]
Application Id : 0
Profile Value : HRCP
Level Name: SITE
INFO : Updated/created profile option value.
.
PL/SQL procedure successfully completed.
Commit complete.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
==============================
* * * * DBC PARAMETERS * * * *
==============================
fnd_jdbc_buffer_min=1
fnd_jdbc_buffer_max=5
fnd_jdbc_buffer_decay_interval=300
fnd_jdbc_buffer_decay_size=5
fnd_jdbc_usable_check=false
fnd_jdbc_context_check=true
fnd_jdbc_plsql_reset=false
====================================
* * * * NO CUSTOM PARAMETERS * * * *
====================================
Unique constraint error (00001) is OK if key already exists
Creating the DBC file...
ADD executed successfully - /dbapps/applmgr/HRCP/inst/apps/HRCP_artemis/appl/fnd/12.0.0/secure/HRCP.dbc
Updating Server Security Authentication
AUTHENTICATION SECURE executed successfully - /dbapps/applmgr/HRCP/inst/apps/HRCP_artemis/appl/fnd/12.0.0/secure/HRCP.dbc
adgendbc.sh ended at Mon May 11 18:39:47 MST 2020
adgendbc.sh exiting with status 0
ERRORCODE = 0 ERRORCODE_END
artemis:/dbapps/applmgr/HRCP/inst/apps/HRCP_artemis/admin/install>
./adstrtal.sh
Friday, May 8, 2020
How to Create RMAN Catalogue
In this short post. I will show you how to create an RMAN catalog
There are 2 main parts.
There are 2 main parts.
- Create the user.
- Create the catalog.
In this example I have an Oracle Database called 'EPCHRS' residing and in a linux server called 'SERPENTCUSTOM'. I will create an RMAN catalog using a user called 'EPCHRS' and I will create it on a database called 'RMANP' residing on a server called 'DBPROD1AEP'
Login to the database that you want to be a repository. It can be any Oracle Enterprise Edition Database and it can also be at a separate server.
1. Create user on RMANP
dbprod1aep|RMANP:/dbapps/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 8 02:25:29 2020
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> CREATE USER EPCHRS IDENTIFIED BY A$hl3y
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users; 2 3 4
User created.
SQL> GRANT RECOVERY_CATALOG_OWNER TO EPCHRS;
Grant succeeded.
SQL>
2. Create catalog
Go to SERPENTCUSTOM
Thursday, May 7, 2020
CURE FOR FEAR OF NOT HAVING ENOUGH TIME
5/8/2020 6:05 AM
Are you one of those people who wants to get early in the office so that he can get ahead of the day. It is currently 6 am right now and I cannot go back to sleep because I am thinking of all the things I need to do.
If I am being honest with myself, I would say that I am currently procrastinating right now. However, I want to believe that I am writing this to get to a program or to a solution of not having enough time. Writing helps me to get things out of my head.
Do other people have this anxiety as well? Yes they do. Let us think for the moment what possible reasons does anxiety play in our lives.
According to experts anxiety and stress happens to give you that additional boost of energy to escape an uncomfortable situation. For example in the animal kingdom stress happens when they are being hunted by predatory animals. Have you watched the discovery channel and see a lion chase a pack of zebras? All the zebras are stressed out and that gives them the energy to either run or stay in fight for their lives. What do you mean zebras fight back? Yes they do! Google it, some of them do fight back lions and sometimes they win.When we are stressed there are only two responses fight or flight.
Let us tangent a little bit fight or flight is an automatic response. It is not something you choose, let me repeat that you have no choice in the matter, it is an automatic response. Research shows that the automatic response is hard wired on what makes you happier. Some people are happier to stay and fight to test out there skills while others choose to avoid conflict and live for another day.
Now that is an interesting nugget of knowledge doesn't it? How you might ask? Well when you are stressed if you not equipped or prepared for what is coming you will run to preserve yourself. On the other hand if you are equipped and ready you will stay and fight what was bothering you and test out your skills.
Fight or flight is not a zero and one response for a particular person you will always have both. To illustrate this let us say that a production database went down and then the CEO of the company is yelling at your boss because the company is losing money while the database is down. For me that is stressful but I will stay and fight. Firstly because it is my job. Secondly because I am equipped and skilled to restore the database at a moment’s notice. If this is to happen when I am not yet skilled I would be pulling my hair out of my head.
Now for the second example of an auto response. Are you afraid of cockroaches? For me I am not scared of cockroaches that are crawling. I am terrified of those that are flying! This is a silly auto response for me. A flying cockroach sends my body into panic always looking for a way out of the room. Never in my life have I imagined myself to chase head on a flying cockroach. Am I equipped and skilled to deal with it? Hell yeah! But why does my body run towards the exit? It has something to do with the limbic part of my brain where there is an auto response that this buggers use to kill my ancestors and it was way better to run away from them.
So what am I saying it is all in your head? Sounds cliché I mean where else would it be, in my knee? Furthermore, saying its all in your head is precisely what makes it excruciating that we stay awake at night. It is for that same reason you are reading this post.
Circling back to the zebras being hunted by the lions. The lions chase their prey and eventually gets an old sick zebra that cannot run anymore. Have you noticed on youtube how the other zebras are just standing there and grazing while the lions are chowing down their dear old grandpa? How can they possibly do that?
It is because for them the stress is gone and no one is chasing them anymore. They can relax while the lions are just 100 feet away from them. They do not worry about the future.
How we wish we could be zebras and have no worry in the world. You might ask then how does a zebra do it? How can they not worry?
Because they literally can't. They do not have a prefrontal cortex in their brains.
Yes that is correct the very thing that separates humans from animals, the prefrontal cortex, is also the culprit for us to worry. The prefrontal cortex is everything that makes a human. It is responsible for planning, emotional control, analytic thinking and other cool stuff.
What I am saying is, you are human and it is exactly normal to worry. Worry is a byproduct of the human psyche's ability to do simulations on the future. We get anxious because we don't have immediate solutions in some simulations we play on our minds.
Well thanks a lot we are now doomed because we cannot escape worrying. Correct we cannot escape it. It is built in in our system. It is what makes a human. It is the flying the cockroach inside the room.
The thing I want you to do is simulate how to deal with the flying cockroach on the room.
Think of options you have.
- Run for your life and get out of the room.
- Comeback and finish the job with
- Bug Spray
- flip flops
- Shotgun
- Bring your cat to deal with it.
- Leave and let live
- Stay and endure the moment.
- Chase the bugger and stomp it.
Next are what the viable options are? I don't have a shotgun or a cat. I don't want to endure the moment. I cannot stomp on it because I am walking barefoot.
I am now left with options 1:1 & 1:2. Kill the bugger and clean up after or leave it until it leaves on its own. Personally speaking I will go with 1:2 because I believe all lives are precious.
Did you see what we did there? We deal with worrying by simulation. Let me breakdown the steps.
CURE FOR WORRYING
- Literally list down your options. Use either a literal or digital notepad. Do not filter the ideas just write what you feel in your gut.
- Short list the options based on
- Preference - I don't want to clean up after I kill it. Wait what I meant to say is that all lives are precious.
- Resources - I don't have a shotgun
- Time
- Training
- Internet
- Colleagues
- Skills - I know how to get out of the room
- Functional
- Social
- Political
- Planning
- Decide what is right and then do it.
It looks simple but it is not. Have you ever seen SWAT train and train simulations after simulations years and years of training for a hostage event that has a slim chance of happening in their entire career? Did you know that research shows that 98% of SWAT will never use a firearm in a real life situation? Yet they train endlessly for it. Why you may ask. Because they want to be SWAT.
They decided to endure the endless training for years for that one moment to be heroes.
SWAT did not start out as SWAT. They were once bullied in the schools and still afraid of cockroaches. They trained slowly and accurately.
They did not go to zero to 100. They trained and simulated zero to 10,10 to 20, 20 to 30.........90 to 100.
Go ahead and start listing options that is first and crucial step and then follow the steps I provided.
Whatever role or situation in life that you are on. You are there for a reason. If you do not want to deal with the flying cockroach on the room work up the skill to leave the room.
In conclusion, the old adage “Don't worry be Happy” is 100% inaccurate. It is literally impossible for a normal human being not to worry. We are hardwired to worry and to be anxious.
There are 3 hard steps to cure worry.
- Literally list down your options.
- Short list the options based on situation
- Decide what is right.
Improve your worrying skills from zero to 10.
Let me end this with a quote for you to remember when you start to worry again.
"Worrying is a major part of life.
We need only to react.
Flight or Fight" - Dexter Velasco
Tuesday, May 5, 2020
RMAN incarnations
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 EPCHRS 1025323918 PARENT 1 07-FEB-18
2 2 EPCHRS 1025323918 PARENT 1477662 14-APR-20
3 3 EPCHRS 1025323918 PARENT 3057981 30-APR-20
4 4 EPCHRS 1025323918 CURRENT 3058941 30-APR-20
select * from chrs.bkup_test;
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 EPCHRS 1025323918 PARENT 1 07-FEB-18
2 2 EPCHRS 1025323918 PARENT 1477662 14-APR-20
3 3 EPCHRS 1025323918 PARENT 3057981 30-APR-20
4 4 EPCHRS 1025323918 CURRENT 3058941 30-APR-20
select * from chrs.bkup_test;
| COL1 | CAP_DATE | USERNAME |
| Marines | 06-MAY-20 11.03.43.957232000 AM | SYS |
| 3547908 | 06-MAY-20 10.55.58.490482000 AM | SYSTEM |
| FLINC2 | 06-MAY-20 10.55.28.065953000 AM | SYSTEM |
| FLINC2 | 06-MAY-20 10.55.27.712340000 AM | SYSTEM |
| FLINC2 | 06-MAY-20 10.55.27.050855000 AM | SYSTEM |
| FLINC2 | 06-MAY-20 10.51.59.876000000 AM | SYSTEM |
| 3546949 | 06-MAY-20 10.26.48.434402000 AM | SYSTEM |
| FLINC1 | 06-MAY-20 10.13.02.378823000 AM | SYSTEM |
| FLINC1 | 06-MAY-20 10.13.00.513703000 AM | SYSTEM |
| FLINC1 | 06-MAY-20 10.11.29.473162000 AM | SYSTEM |
| Marines | 06-MAY-20 10.03.43.849205000 AM | SYS |
Restore database only to may 6 10 am
startup nomount
alter database mount
run
{
allocate channel dev1 type disk;
set until time "TO_DATE('06-MAY-2020 10:00', 'DD-MON-YYYY HH24:MI')";
restore database;
recover database; }
alter database open resetlogs;
Marines 06-MAY-20 01.22.51.307943000 PM SYS
Marines 06-MAY-20 09.03.43.727733000 AM SYS
Marines 06-MAY-20 08.03.43.592781000 AM SYS
Marines 06-MAY-20 07.03.43.505016000 AM SYS
Marines 06-MAY-20 06.03.43.359553000 AM SYS
Marines 06-MAY-20 05.03.43.264703000 AM SYS
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 EPCHRS 1025323918 PARENT 1 07-FEB-18
2 2 EPCHRS 1025323918 PARENT 1477662 14-APR-20
3 3 EPCHRS 1025323918 PARENT 3057981 30-APR-20
4 4 EPCHRS 1025323918 PARENT 3058941 30-APR-20
5 5 EPCHRS 1025323918 CURRENT 3546181 06-MAY-20
I want to see the FLINC1 entries again
shutdown immeidate;
startup nomount
alter database mount;
run
{
allocate channel dev1 type disk;
restore database;
recover database; }
Marines 06-MAY-20 01.22.51.307943000 PM SYS
Marines 06-MAY-20 09.03.43.727733000 AM SYS
Marines 06-MAY-20 08.03.43.592781000 AM SYS
Marines 06-MAY-20 07.03.43.505016000 AM SYS
Marines 06-MAY-20 06.03.43.359553000 AM SYS
Marines 06-MAY-20 05.03.43.264703000 AM SYS
Marines 06-MAY-20 04.03.44.109648000 AM SYS
## It is still the same because you did a restore a full restore
Try to restore until 11 am
Recovery Manager complete.
[oracle@serpentcustom davscripts]$ rman target /
Recovery Manager: Release 18.0.0.0.0 - Production on Wed May 6 13:40:35 2020
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: EPCHRS (not mounted)
RMAN> alter database mount;
using target database control file instead of recovery catalog
Statement processed
RMAN> run
{
allocate channel dev1 type disk;
set until time "TO_DATE('06-MAY-2020 11:00', 'DD-MON-YYYY HH24:MI')";
restore database;
recover database; }2> 3> 4> 5> 6>
allocated channel: dev1
channel dev1: SID=259 device type=DISK
executing command: SET until clause
Starting restore at 06-MAY-20
released channel: dev1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/06/2020 13:41:45
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
RMAN>
## It is logical and expected so how the hell are we gonna get those old backups now!!!
DO this
RMAN> alter database mount;
Statement processed
RMAN> reset database to incarnation 4;
database reset to incarnation 4
RMAN> RUN
{
SET UNTIL TIME "TO_DATE('06-MAY-2020 12:06', 'DD-MON-YYYY HH24:MI')";
RESTORE DATABASE;
RECOVER DATABASE;
}2> 3> 4> 5> 6>
.
.
.
archived log file name=/u03/app/oracle/fast_recovery_area/EPCHRS/EPCHRS/archivelog/2020_05_06/o1_mf_1 _596_hc4l38s5_.arc thread=1 sequence=596
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-MAY-20
RMAN> alter database open resetlogs;
Marines 06-MAY-20 02.00.59.634651000 PM SYS
Marines 06-MAY-20 12.03.44.109976000 PM SYS
Marines 06-MAY-20 11.03.43.957232000 AM SYS
3547908 06-MAY-20 10.55.58.490482000 AM SYSTEM
FLINC2 06-MAY-20 10.55.28.065953000 AM SYSTEM
FLINC2 06-MAY-20 10.55.27.712340000 AM SYSTEM
FLINC2 06-MAY-20 10.55.27.050855000 AM SYSTEM
FLINC2 06-MAY-20 10.51.59.876000000 AM SYSTEM
3546949 06-MAY-20 10.26.48.434402000 AM SYSTEM
FLINC1 06-MAY-20 10.13.02.378823000 AM SYSTEM
FLINC1 06-MAY-20 10.13.00.513703000 AM SYSTEM
FLINC1 06-MAY-20 10.11.29.473162000 AM SYSTEM
Marines 06-MAY-20 10.03.43.849205000 AM SYS
Marines 06-MAY-20 09.03.43.727733000 AM SYS
Marines 06-MAY-20 08.03.43.592781000 AM SYS
Now they are back!!!
Good Hunting Comrade!!!
How to Tell RMAN where to save
export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
rman target / catalog rman12c/rman12c@oravm &amp;lt;&amp;lt;-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/full-01/%U';
backup database tag 'full-01' plus archivelog tag 'full-01';
}
EOF
rman target / catalog rman12c/rman12c@oravm &amp;lt;&amp;lt;-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/full-02/%U';
backup database tag 'full-02' plus archivelog tag 'full-02';
}
EOF
rman target / catalog rman12c/rman12c@oravm &amp;lt;&amp;lt;-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/lvl0-01/%U';
backup incremental level 0 database tag 'lvl0-01' plus archivelog tag 'lvl0-01';
}
EOF
rman target / catalog rman12c/rman12c@oravm &amp;lt;&amp;lt;-EOF
run {
allocate channel ch1 device type disk format '/mnt/oracle-backups/ora12c/lvl0-02/%U';
backup incremental level 0 database tag 'lvl0-02' plus archivelog tag 'lvl0-02';
}
EOF
Monday, May 4, 2020
How to Refresh a DB Schema
drop user compass cascade;
impdp system@epwebd DIRECTORY=DUMP_EPWEBD DUMPFILE=expdpWEBP-0405202020.dmp schemas='COMPASS' logfile=impdp_COMPASS.log
impdp system@epwebd DIRECTORY=DUMP_EPWEBD DUMPFILE=expdpWEBP-0405202020.dmp schemas='COMPASS' logfile=impdp_COMPASS.log
SQL Server when was the database last used
select object_name(object_id),
last_user_update, *
from sys.dm_db_index_usage_stats
where database_id=db_id('ESQR_rev3')
last_user_update, *
from sys.dm_db_index_usage_stats
where database_id=db_id('ESQR_rev3')
Subscribe to:
Posts (Atom)