[Pansophy] Database Stuff

Thu May 14 10:01:31 EDT 2020

David,

I cannot attach the databases below to our pansophydev server in the ColdFusion administrator.
acctst, factst, samtst, and travtst
What am I missing? I wanted to test them before we release our production server.

Also, Mike asked a question about converting our current materialized views inti just views. Is this possible?
Then we can downgrade to normal Oracle server.

Will you be rebooting our database servers on the 19th?  Will the coldfusion servers also be rebooted?

Thanks.

Valerie

SQL from materialized_view  for MV_C50_DECOMM

  CREATE MATERIALIZED VIEW "ADAPPS"."MV_C50_DECOMM" ("DATE_STAMP")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT sysdate + 1
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS ENABLE QUERY REWRITE
  AS SELECT TO_DATE(TIME_STAMP,'DD-FMMonth-FMYYYY') AS DATE_STAMP
FROM adapps.c50_decomm
GROUP BY TO_DATE(TIME_STAMP,'DD-FMMonth-FMYYYY')

;

  CREATE UNIQUE INDEX "ADAPPS"."I_SNAP$_MV_C50_DECOMM" ON "ADAPPS"."MV_C50_DECOMM" (SYS_OP_MAP_NONNULL("DATE_STAMP"))
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 8192 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;

   COMMENT ON MATERIALIZED VIEW "ADAPPS"."MV_C50_DECOMM"  IS 'snapshot table for snapshot ADAPPS.MV_C50_DECOMM';

From: David Sheppard <sheppard at jlab.org>
Sent: Friday, April 24, 2020 3:56 PM
To: Valerie Bookwalter <bookwalt at jlab.org>
Cc: pansophy <pansophy at jlab.org>; E. Anne McEwen <mcewen at jlab.org>; Tony Reilly <areilly at jlab.org>
Subject: Re: Database Stuff

Good Afternoon,

I have set up a test database environment. The databases are acctst, factst, samtst, and travtst.
They have the same naming and password conventions that the production databases have. We
can discuss how to set them up whenever you like. I can even have them refresh data from the
production databases nightly if you would like. In that case, this could be how testing is done
going forward. Just a thought. Anyway, have a great weekend, and we can discuss more as we
move forward.

David Shepard

________________________________
From: David Sheppard <sheppard at jlab.org<mailto:sheppard at jlab.org>>
Sent: Tuesday, April 21, 2020 10:48 AM
To: Valerie Bookwalter <bookwalt at jlab.org<mailto:bookwalt at jlab.org>>
Cc: pansophy <pansophy at jlab.org<mailto:pansophy at jlab.org>>; E. Anne McEwen <mcewen at jlab.org<mailto:mcewen at jlab.org>>; Tony Reilly <areilly at jlab.org<mailto:areilly at jlab.org>>
Subject: Re: Database Stuff

ok. Thank you. I will do my best to set up some sort of test environment for you. You only have test schemas, not test databases. I will try to create some test databases with version 18c. Then, we can navigate how you can test using them.

________________________________
From: Valerie Bookwalter <bookwalt at jlab.org<mailto:bookwalt at jlab.org>>
Sent: Tuesday, April 21, 2020 10:31 AM
To: David Sheppard <sheppard at jlab.org<mailto:sheppard at jlab.org>>
Cc: pansophy <pansophy at jlab.org<mailto:pansophy at jlab.org>>; E. Anne McEwen <mcewen at jlab.org<mailto:mcewen at jlab.org>>; Tony Reilly <areilly at jlab.org<mailto:areilly at jlab.org>>
Subject: Re: Database Stuff

David,

Yes, please reboot our db server tonight. And yes let's setup a monthly reboot to keep things running smoothly.

Also, Yes to upgrading our database server. Let us know how much time you need and possible dates. Will there be a need for us to test before going to production? We will also have to let users know before we do the switch.

Additionally, did our new db server get funded and approved? What is the ETA?

What other information do you need from us?
Thanks for your support!!

Valerie
Pansophy Team
________________________________
From: David Sheppard <sheppard at jlab.org<mailto:sheppard at jlab.org>>
Sent: Tuesday, April 21, 2020 10:20 AM
To: Valerie Bookwalter <bookwalt at jlab.org<mailto:bookwalt at jlab.org>>
Subject: Database Stuff

Good Morning Valerie,

I was wondering if we can start doing monthly database server reboots
during maintenance day. There doesn't appear to be anything wrong. It
may make things run a little better and more efficient. Also, I have
installed Oracle Database 18c on our development database servers.
Since, you don't have development databases or development servers,
I will not attempt to install it right now, but it should be something we
should do in the not to distant future. I'm not as worried about it because
18c is really just 12.2.0.2. You're already running Oracle Database 12.2.0.1.
I just wanted to put a bug in your ear about it. And let me know about
rebooting pansophydb tonight. Stay safe and have a great day.

David Sheppard
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.jlab.org/pipermail/pansophy/attachments/20200514/eeb6006b/attachment-0001.html>