Unable To mount ACFS after Reboot


Built a stand alone grid infrastructure and added an ACFS file system.

However, following a reboot, I was unable to start the acfs cluster.

Eventually, found this document on Metalink.

 Unable To mount ACFS after Reboot On Standalone GI [SIHA] (Doc ID 2348133.1)

 Standalone setup does not start ACFS automatically, so create rc.local script to load the ACFS drivers well before the OHASD service starts. ACFS drivers should get loaded before ASM starts.

 To prove this worked, I ran a shutdown on ASM, started acfs i.e. "acfsload start" and then started ASM and et voila. Need to add the "acfsload start" to the startup scripts otherwise will need to do this following every reboot.

[oracle@localhost ~]$ sqlplus / as sysasm
SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 22 04:19:45 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> shutdown abort
ASM instance shutdown
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

If the server is using HA i.e. RAC One Node or Oracle restart you could run a :

crstl stop has 

 then :

[oracle@localhost ~]$ lsmod | grep oracle
oracleasm              59173  1

--- No acfs.

[oracle@localhost ~]$ su
Password:

[root@localhost oracle]# . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /u01/app

[root@localhost oracle]# acfsload start

ACFS-9391: Checking for existing ADVM/ACFS installation.
ACFS-9392: Validating ADVM/ACFS installation files for operating system.
ACFS-9393: Verifying ASM Administrator setup.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9154: Loading 'oracleoks.ko' driver.
ACFS-9154: Loading 'oracleadvm.ko' driver.
ACFS-9154: Loading 'oracleacfs.ko' driver.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.
ACFS-9156: Detecting control device '/dev/ofsctl'.
ACFS-9322: completed

Now :

[root@node01 ~]# lsmod | grep oracle

oracleacfs              4815623  1
oracleadvm            785504  7
oracleoks               638647  2 oracleacfs,oracleadvm
oracleasm              59173  1

Restart ASM or "crsctl start has" if the server is HA enabled.

[oracle@localhost ~]$ sqlplus / as sysasm

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 22 04:21:26 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup;
ASM instance started
Total System Global Area 1140850688 bytes
Fixed Size                  8629704 bytes
Variable Size            1107055160 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted
ASM diskgroups volume enabled

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Check diskgroups are ok via asmcmd, if not run a "volenable --all" to ensure the volumes are enabled.

ASMCMD> volinfo --all
Diskgroup Name: NFPSADWGGDATA
         Volume Name: NFPSADWGG
         Volume Device: /dev/asm/nfpsadwgg-236
         State: ENABLED
         Size (MB): 306176
         Resize Unit (MB): 512
         Redundancy: UNPROT
         Stripe Columns: 8
         Stripe Width (K): 1024
         Usage: ACFS
         Mountpath: /acfsmounts
Using this information :

mount -t acfs /dev/asm/nfpsadwgg-236 /acfsmounts

 Check successfully mounted

$ df -h | grep acfs

Filesystem                                                   Size  Used Avail Use% Mounted on
/dev/asm/nfpsadwgg-236                            299G  4.6G  295G   2% /acfsmounts


Check anything that is using acfs is working e.g. Golden Gate.

See also this metalink doc when acfs not running on OCI cluster :

OCI Backup issue - DCS-10001:Internal error encountered: Mount point:/opt/oracle/dcs/commonstore is not mounted or does not exist (Doc ID 2513385.1)

No comments:

Flashback database

I don't really like using flashback, if given the choice would use pluggable databases and clone the pdb prior to doing any work and if you hit a problem clone the pdb back to it's original state (but keep that for another day).

Here are the steps for flashback of a database. The DB needs to be in achieve log mode and forced logging enabled. Verify that the flash recovery area is setup and large enough for the upgrade.

NB - This process is slightly different if using DataGuard please see - 

https://www.thegeekdiary.com/how-to-create-and-drop-guaranteed-restore-point-in-oracle-data-guard/


You don’t need to enable flashback on the whole database, but I did it anyway. The restore point needs to be guaranteed.

Verify Flashback status, size and location:

select flashback_on, LOG_MODE, FORCE_LOGGING, status from v$database, v$instance;

show parameter DB_RECOVERY

show parameter FLASHBACK


Steps before the upgrade:

ALTER DATABASE FLASHBACK ON;

CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Steps after the upgrade:

DROP RESTORE POINT before_upgrade;

ALTER DATABASE FLASHBACK OFF;



Steps if need to flashback the Database:

su – oracle

sqlplus / as sysdba;


select current_scn from v$database;

shutdown immediate;

startup mount;

select * from v$restore_point;

flashback database to restore point before_upgrade;

alter database open resetlogs;

Check flashback space usage:

select * from v$flash_recovery_area_usage;

select name, time, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE from v$restore_point;

More detailed instructions (when running an application upgrade and you hit problems) :

Instruction Steps
Task Steps
Reference/Screenshots
1.     
Verify Flashback Recovery status, size and location:

If db_recovery_file_dest and db_recovery_file_dest_size is blank, add them before proceeding.
select flashback_on, log_mode, force_logging, status from v$database, v$instance;
show parameter db_recovery;
show parameter flashback;
2.     


3.     
Steps before the Application upgrade:
Note: Save value for current_scn to compare if recovery is performed
alter database flashback on;
select current_scn from v$database;
create restore point "before_upgrade" guarantee flashback database;
4.     


5.     
Steps after the Application upgrade:
Note: Only perform these steps if upgrade has been tested and declared successful
drop restore point "before_upgrade";
alter database flashback off;
6.     


7.     
If Application upgrade was not successful and a roll back is required, perform these steps to roll back database changes:



Note: Compare SCN from step 3 to ensure flashback was successful. SCN’s may not match, but should be close in range

Replace db_name with your RAC database name
For single database instance:
sqlplus / as sysdba;
shutdown immediate;
startup mount;
flashback database to restore point "before_upgrade";
alter database open resetlogs;
select current_scn from v$database;

For multi-node RAC instances:
srvctl stop database -db db_name
export ORACLE_SID=db_name_1
sqlplus / as sysdba;
startup mount;
flashback database to restore point "before_upgrade";
alter database open resetlogs;
select current_scn from v$database;
shutdown immediate;
quit;
srvctl start database -db db_name
8.     


9.     
Check flashback space usage:
select * from v$flash_recovery_area_usage;
select name, time, guarantee_flashback_database, storage_size from v$restore_point;
10.   







No comments:

Removing node from RAC Cluster















 **Host names etc changed.

Stop all Process running on the Database home     




srvctl stop instance -d rpap_gdc2 -n prsodb02

srvctl disable  instance -d rpap_gdc2 -n prsodb02



RELOCATE ALL SERVICES HAVING NODE 2 AS PREFERRED OR AVAILABLE


srvctl modify  service -s customer_pdb.scc.local -d rpap_gdc2  -modifyconfig  -preferred "rpap1"

srvctl modify  service -s lms_pdb.scc.local -d rpap_gdc2  -modifyconfig  -preferred "rpap1"

srvctl modify  service -s osmm.scc.local -d rpap_gdc2  -modifyconfig  -preferred "rpap1"

srvctl modify  service -s pdb1p_pdb.scc.local -d rpap_gdc2  -modifyconfig  -preferred "rpap1"

srvctl modify  service -s batch_service -d rpap_gdc2  -modifyconfig  -preferred "rpap1"



Ensure that the instance to be removed from the database is neither a preferred nor an available instance of any service



srvctl config service –s  customer_pdb.scc.local -d rpap_gdc2 

srvctl config service –s lms_pdb.scc.local -d rpap_gdc2 

srvctl config service –s  s osmm.scc.local -d rpap_gdc2 

srvctl config service –s pdb1p_pdb.scc.local -d rpap_gdc2 

srvctl config service –s s batch_service -d rpap_gdc2 





DELETE THE DATABASE INSTANCE USING DBCA SILENT MODE


dbca -silent -responseFile deleterpap2.rsp




Verify that the dropped instance's redo thread has been removed by using SQL*Plus on an existing node to query the GV$LOG view. If the redo thread is not disabled, then disable the thread. For example:

 ALTER DATABASE DISABLE THREAD 2;

Verify that the instance has been removed from OCR by running the following command, where db_unique_name is the database unique name for your Oracle RAC database:

 srvctl config database -d rpap_gdc2



UPDATE INVENTORY ON prsodb02 TO REFLECT NODE TO BE DELETED


Run the following command from $ORACLE_HOME/oui/bin on the node that you are deleting to update the inventory on that node:

 ./runInstaller -updateNodeList ORACLE_HOME= /u01/app/oracle/product/12.1.0.1/dbhome_1 "CLUSTER_NODES={prsodb02}" -local



DEINSTALL ORACLE HOME ON prsodb02


Deinstall the Oracle home—only if the Oracle home is not shared—from the node that you are deleting by running the following command from the Oracle_home\deinstall directory:

 deinstall -local



UPDATE THE INVENTORY ON prsodb01 To REFLECT REMAINING NODE


 


Run the following command from the $ORACLE_HOME/oui/bin directory on any one of the remaining nodes in the cluster to update the inventories of those nodes, specifying a comma-delimited list of remaining node names and the name of the local node:

  ./runInstaller -updateNodeList ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/dbhome_1

"CLUSTER_NODES={ prsodb01}"  LOCAL_NODE= prsodb01







 


REMOVE GRID HOME




Stop and DISABLE THE listener process


srvctl stop listener -l listener  -n prsodb02

 srvctl disable listener -l listener  -n prsodb02

UPDATE INVENTORY ON prsodb02 TO REFLECT NODE TO BE DELETED


On the node you want to delete, run the following command as the user that installed Oracle Clusterware from the Grid_home/oui/bin directory where node_to_be_deleted is the name of the node that you are deleting:

  ./runInstaller -updateNodeList ORACLE_HOME= /u01/app/12.1.0.1/grid "CLUSTER_NODES={

prsodb02}" CRS=TRUE -silent -local



DEINSTALL GRID HOME ON prsodb02


For a local home, deinstall the Oracle Clusterware home from the node that you want to delete, as follows, by running the following command, where Grid_home is the path defined for the Oracle Clusterware home:

  Grid_home/deinstall/deinstall –local



UPDATE THE INVENTORY ON prsodb01 To REFLECT REMAINING NODE




On any node other than the node you are deleting (except for a Leaf Node in an Oracle Flex Cluster), run the following command from the Grid_home/oui/bin directory where remaining_nodes_list is a comma-delimited list of the nodes that are going to remain part of your cluster:

./runInstaller -updateNodeList ORACLE_HOME== /u01/app/12.1.0.1/grid  "CLUSTER_NODES={ prsodb01}" CRS=TRUE -silent



REMOVE DELETED NODE FROM OCR


From any node that you are not deleting, run the following command from the Grid_home/bin directory as root to delete the node from the cluster:

  crsctl delete node -n prsodb02

Run the following CVU command to verify that the specified nodes have been successfully deleted from the cluster:

  cluvfy stage -post nodedel -n prsodb01 -verbose



If you remove a cluster node on which Oracle Clusterware is down, then determine whether the VIP for the deleted node still exists, as follows:

 srvctl config vip -node prsodb02



If the VIP still exists, then delete it, as follows:

  srvctl stop vip -node prsodb02

  srvctl remove vip -node prsodb02
No comments:

Cluster Health Diagnostic

This is proving very useful - more to follow.

[grid@ORA01 ~]$ chactl query diagnosis -start "2018-12-31 00:00:00" -end "2019-01-08 23:59:00" -htmlfile ~/chaprob.html
No comments:

Oracle sysdate / timestamp etc.


This just a reminder to myself when outputting date / time  in oracle.

Loads of possibilities to achieve this - one solution.

Standard sysdate output (limited to date only but pain when you want the time too)...

 SQL> select sysdate from dual;

SYSDATE
---------
07-JAN-19

Could use the cast function to change the type to timestamp so more detail to include time

 SQL> select CAST(sysdate as timestamp) from dual;

CAST(SYSDATEASTIMESTAMP)
---------------------------------------------------------------------------
07-JAN-19 09.37.41.000000 AM

more control alter session and pick the format you want....

 SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate from dual;

SYSDATE
--------------------
07-JAN-2019 09:37:54


Break a table down into counts by month for a date column :

SQL> select count(REQ_TIMESTAMP), to_char(trunc REQ_TIMESTAMP),'MM-YYYY') from REQ_LOG group by to_char(trunc(REQ_TIMESTAMP),'MM-YYYY');

COUNT(REQUEST_TIMESTAMP) TO_CHAR
------------------------ -------
2740644  04-2019
3978        02-2019
3604984  05-2019
or...

SQL> select count(*),trunc(REQ_TIMESTAMP,'MM') from REQ_LOG group by trunc(REQ_TIMESTAMP,'MM');

COUNT(*) TRUNC(INS
---------- ---------
2740644  01-APR-19
3978        01-FEB-19
3604984  01-MAY-19

SQL> delete from EC_MAIN.API_REQUEST_LOG where trunc(INSERTED_DTM,'MM') = '01-FEB-19';

3978 rows deleted.



No comments:

Oracle database connection background info

For Oracle database TESTAPPS 

Bit of background :




** ora1, ora2, ora3 and ora4 redacted server names - 

Thin driver

Oracle's JDBC Thin driver uses Java sockets to connect directly to Oracle.

It provides its own TCP/IP version of Oracle's SQL*Net protocol.

Because it is 100% Java, this driver is platform independent and can also run from a Web Browser (applets).

Oracle is replacing identifying databases by the SID with the new services approach. There is a third approach, TNSNames, which can be used if configured in the file tnsnames.ora correctly.

The Thin driver offers these kinds of URL formats for all of them:

SID (no longer recommended by Oracle to be used):

jdbc:oracle:thin:[<user>/<password>]@<host>[:<port>]:<SID>

Services:

jdbc:oracle:thin:[<user>/<password>]@//<host>[:<port>]/<service>

TNSNames:

jdbc:oracle:thin:[<user>/<password>]@<TNSName>

Also -

12c and service names -

You're in a multitenant environment.

You should always use the SERVICE name when connecting to a 12c instance.

SID in a multitenant will always take you to the container database.

The pluggables running in the container will only be reachable by their service name.

Remember databases use self registration so listener will know about each database regardless.

When connection to ncs - the services works but not the SID as would be expected as on 12c.

(user status.java script throughout this changing the connection details to test the jdbc connection string)

[oracle@ORA01 admin]$ srvctl status database -db umb -v

Instance umbapps1 is running on node ora01 with online services oam,oms,prodcat,umb. Instance status: Open.
Instance umb2 is running on node ora02 with online services cch,cdcs,cmc,cng,cxs,ec,frm,ncs,nis,oms,pal,prodcat,umb2. Instance status: Open.
Instance umbapps3 is running on node ora03 with online services cch,cdcs,cmc,cng,cxs,ec,frm,ncs,nis,oam,pal,umb3. Instance status: Open.
Instance umbapps4 is running on node ora04 with online services cch,cdcs,cmc,cng,cxs,ec,frm,ncs,nis,oam,oms,pal,prodcat,umb4. Instance status: Open.

[oracle@ORA01 test_cub]$ sqlplus GUEST/.......@ora-scan:1521/ncs

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 8 04:43:34 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Wed Aug 08 2018 04:01:15 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
GUEST@ora-scan:1521/ncs

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Using SID

[oracle@ORA01 test_cub]$ sqlplus GUEST/……………….@ora-scan:1521:ncs
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 8 04:43:44 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
ERROR:
ORA-12545: Connect failed because target host or object does not exist

To list full details about each service :

--check cardinality
oracle@ORA01 ~]$ crsctl status resource ora.umb.cch.svc -f |grep -i car
CARDINALITY=4
CARDINALITY_ID=0
[oracle@ORA01 test_cub]$ srvctl config service -d umbapps -s ncs
Service name: ncs
Server pool:
Cardinality: 3
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Failover type:
Failover method:
TAF failover retries:
TAF failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: NONE
Edition:
Pluggable database name:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: umb2,umb3,umb4
Available instances: umb1
CSS critical: no
So the connection using services i.e. ncs works
Test connection using java connection string
[oracle@ORA01 test_cub]$ /home/oracle/test_cub status
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
Global Name
UMBAPPS
Test JDBC connection
Connection using - jdbc:oracle:thin:@ora-scan:1521/ncs
but not the services jdbc:oracle:thin:@ora-scan:1521:ncs as would be expected.

[oracle@ORA01 test_cub]$ test_cub status
Exception in thread "main" java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:774)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at status.main(status.java:17)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
at oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:271)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:317)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1438)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:518)
... 6 more
No tnsnames services for ncs

[oracle@ORA01 admin]$ pwd
/u01/app/oracle/product/12.2.0/dbhome/network/admin

[oracle@ORA01 admin]$ ls -la | grep tns
-rw-------. 1 oracle oinstall 916 Apr 3 23:30 tnsnames_abp.ora
-rw-------. 1 oracle oinstall 916 Apr 3 23:34 tnsnames_cch.ora
-rw-------. 1 oracle oinstall 916 Apr 3 22:19 tnsnames_cmc.ora
.....(cut for brevity)

i.e.
[oracle@ORA01 admin]$ sqlplus  GUEST/………..@ncs
SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 8 05:01:37 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

For ODS database
srvctl status database -db ods –v
Instance ods1 is running on node ora01 with online services ods_1. Instance status: Open.
Instance ods2 is running on node ora02 with online services ods_2. Instance status: Open.
Instance ods3 is running on node ora03 with online services ods_3. Instance status: Open.
Instance ods4 is running on node ora04 with online services ods_4. Instance status: Open.

The service connection and the tnsnames connection both work.
[oracle@ORA01 test_cub]$ test_cub status
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
Global Name
ODS
Test JDBC connection
Connection using - jdbc:oracle:thin:@ora-scan:1521:ods

[oracle@ORA01 admin]$ sqlplus edw/……..@ods

SQL*Plus: Release 12.2.0.1.0 Production on Wed Aug 8 04:56:20 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Wed Aug 08 2018 04:53:03 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

We could do this to get the SID to work :


No comments:
Subscribe to: Posts (Atom)