I wanted a new test environment so re-created two instances on the always free option for the Oracle Cloud Infrastructure.
Two things which I could not remember from the last time I did this was that the private key that you download when creating the instance, when you use login vi putty you hit an error.
Basically the private key from OCI, the putty application does not like it, so you have to import and change it to a version that it does. Just need puttygen.
just use the OCI generated key and change it to .ppk as above.
I tend to follow the notes from the excellent Oracle Base website to install Oracle but when using MobaXterm to run say the graphical Oracle Installer, after a fresh build on Oracle OCI instance you need to change the X11UseLocalhost parameter. Worth changing this to use password authentication at the same time so not dependent on using private keys (this is a test box for me so happy with just a password).
After succesfully installing oracle 19c (follow the notes from Oracle Base) and fighting 19c with 1gig of memory when trying to connect the two servers needed to add port 1521 -
Couple of notes from Oracle Metalink when relinking Oracle and Grid Home.
Please note when relinking the grid home the /u01/app/oraInventory/locks directory had to have the permissions changed when the grid relink ran otherwise the relink did not work.
Also, after bringing back some of our boxes and running "crsctl start crs" the cluster was not starting on that node.
After running "oracleasm listdisks" - it turned out some of the diskgroups were missing.
Under investigation.
We ran "oracleasm scandisks" which discovered the disks but still no success with "crsctl stat res -t"
The only option was to run "crstcl stop crs -f" and force it down.
Once restarted the server was ok.
Please check that oracleasm scandisks returns the expected values prior to running crsctl start crs.
The text in red are the commands to run once you have stopped all the services on the node you are about to relink.
If you need to "bounce" the server say for a kernel update please run "crsctl disable crs" or the clustering will start once the server restarts and you will have to stop everything again. Please remember to "crsctl enable crs" when you are happy.
Abridged notes from the Metalink Note 1536057.1) :
How To Relink The Oracle Grid Infrastructure RAC/Cluster Installation - v12.2
In order to relink the Oracle Grid Infrastructure RAC/Cluster Installation, please follow the next steps after stopping all the services running:
1) As root OS user, please unlock the Grid Infrastructure Oracle Home (on every node) as follows (set the correct grid home env first) :
# cd < Grid Infrastructure Oracle Home >/crs/install # rootcrs.sh -unlock
2) As the Oracle Grid Infrastructure owner (on every node) relink the Grid Infrastructure Oracle Home as follows:
$ export ORACLE_HOME=< Grid Infrastructure Oracle Home > $ < Grid Infrastructure Oracle Home>/bin/relink all
3) As root OS user again, please execute the next commands (on every node):
# cd < Grid Infrastructure Oracle Home >/rdbms/install/ # ./rootadd_rdbms.sh # cd < Grid Infrastructure Oracle Home >/crs/install # rootcrs.sh -lock
Then "crsctl start crs" - please see note above re oracleasm listdisks
Note: CRS services (CRS, CSS ASM instances, diskgroups, listeners, DB instances, etc.) will automatically start.
4) Please review the relink.log file located at the following directory (to confirm or discard any error):
We needed to select the last date for each procedure.
This works :
select proc_name, max(start_dtm) from dw_main.proc_run_log where proc_name in (select distinct(proc_name) from dw_main.proc_run_log) group by proc_name order by proc_name;
We wanted to double check (and to show how this could also be achieved in pl/sql) to be absolutely certain so put this together (the sql is also easier to understand ):
create or replace procedure ruslist is proc_rus varchar2(30); start_rus date; CURSOR d1 IS SELECT distinct proc_name from dw_main.proc_run_log order by proc_name; CURSOR d2 IS SELECT max(start_dtm) from dw_main.proc_run_log where proc_name = proc_rus; BEGIN open d1; LOOP FETCH d1 into proc_rus; EXIT when d1% NOTFOUND; dbms_output.put( proc_rus); open d2; LOOP FETCH d2 into start_rus; EXIT when d2% NOTFOUND; dbms_output.put_line(' '||start_rus); END LOOP; CLOSE d2; END LOOP; CLOSE d1; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END ruslist;