If it's anything like any normal exporter, then you can install it wherever you like, as long as there is network connectivity.
You could install it on the prometheus server, and configure the exporter to make outbound connections to your Oracle database(s) from there. However if you have firewalls which prevent this, or the TNS listener config doesn't allow it, then you could install the exporter on your database server. Or you could install it in a third location, such as in its own docker container.
In all cases, the connections go: prometheus --> exporter --> target. The connection from prometheus to exporter is called a "scrape" and is HTTP(S); you can use curl to test it out. The connection from exporter to target will be the Oracle database protocol (OCI).
You might want to get yourself familiar first with another exporter which works in this way, such as blackbox_exporter (because sorting out problems with Oracle OCI client libraries can be a pain).