According to the original text, the construction was successful, hereby record
Pull the Oracle image, check it after the pull is successful
docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g docker images
Create a temporary container
Create a temporary container, map the data persistently to the local machine, and authorize the file, remember the copied file path, and use it for subsequent mapping
#Start a temporary container to obtain initialization data docker run -d --name test --restart unless-stopped \ -p 1521:1521 registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g mkdir -p /data/oracle/ docker cp test:/home/oracle/app/oracle/oradata/ /data/oracle/ cd /data/oracle/oradata/ #Authorize the file to the Oracle user in the container chown -R 500.500 helowin
Create container
The mapping file is the temporary Oracle data file path copied in the previous step
#Start the container docker rm -f test docker run -d --name oracle11g --restart unless-stopped \ -v /data/oracle/oradata/helowin:/home/oracle/app/oracle/oradata/helowin \ -p 1521:1521 registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
Enter the image to configure
#Enter the container as root user docker exec -it -u root <your own container ID or name> bash
Modify configuration file
#Modify the configuration file after switching the root user, the root password is helowin vi /etc/profile
The following configuration information is added internally:
# oracle home directory export ORACLE_HOME=/home/oracle/app/oracle/prouct/11.2.0/dbhome_2 # oracle service name or SID name, remember this name helowin, it is an important option for naivcat or DBeaver login export ORACLE_SID=helowin # oracle environment variable export PATH=$ORACLE_HOME/bin:$PATH
Refresh environment variables
source /etc/profile
Create soft link
ln -s $ORACLE_HOME/bin/sqlplus /usr/bin
Change user password
After switching the Oracle user, log in to sqlplus and modify the sys and system user passwords
su-oracle sqlplus /nolog conn /as sysdba alter user system identified by system;--modify system user account password; alter user sys identified by system;--modify the sys user account password; create user test identified by test; -- Create an internal administrator account password; grant connect,resource,dba to test; --Authorize the dba authority to the internal administrator account and password; ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; --Modify the password rule policy so that the password never expires; (There will be pits, which will be explained later) alter system set processes=1000 scope=spfile; --Modify the maximum connection data of the database;
If an error is reported, perform the following operations, and then change the password again:
Delete the newly generated version control file, and copy the version control file in the data volume to the newly generated version control file. When operating, ensure that the user in the container is the oracle user instead of root, otherwise an error will be reported in the control file.
Re-designate the configuration file as the configuration file that created the temporary Oracle path before:
rm -rf /home/oracle/app/oracle/flash_recovery_area/helowin/control02.ctl cp /home/oracle/app/oracle/oradata/helowin/control01.ctl /home/oracle/app/oracle/flash_recovery_area/helowin/control02.ctl sqlplus / as sysdba # connect to oracle database as dba shutdown immediate # Close the database instance (an error will be reported here, don't worry about it) startup
Restart the database
Restart the database after modifying the above information
sqlplus / as sysdba
shutdown immediate; -- close the database
# adjust character set
# Start the database mount,
startup mount
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
# start the database
alter database open;
# Modify the database
ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
# restart the database
shutdown immediate;
startup; --start the database
# query character set
select userenv ('language') from dual;
exit: Exit the soft link
Set oracle to support external connection access
A total of two files need to be configured, one is the listener listener.ora and the other is tnsnames.ora
find / | grep /network/admin #Find your own oracle monitoring configuration file directory vi /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora #This should be the .ora address queried in the previous step vi /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora #same as above
Before modifying the file, enter hostname, and the current hostname (the name in the Oracle container) will be displayed after pressing Enter
listener.ora:
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) # localhost:1521
)
)
ADR_BASE_LISTENER = /home/oracle/app/oracle
Replace localhost with the previously queried hostname
tnsnames.ora is the same:
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_HELOWIN =
(ADDRESS = (PROTOCOL = TCP)(HOST = loaclhost)(PORT = 1521))
HELOWIN=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = helowin)
)
)
Refresh configuration
Every time you change oracle’s monitoring configuration file, you need to refresh the configuration. 90% of the connection exceptions are caused by monitoring configuration problems.
exit#Exit the container systemctl restart docker.service#Restart service docker start "Own container id/name" #Restart the container docker exec -it -u root 《Own container id/name》 bash# Re-enter the container su - oracle#Switch the oracle account sqlplus / as sysdba #Login to the database SQL>alter system register;#Forcibly re-register the instance SQL>quit#Exit the database lsnrctl reload#Restart the monitor in the container lsnrctl status#View the monitoring service status
Oracle creates users and tablespaces
Oracle tablespace classification
permanent tablespace
- A tablespace is a logical division of a database, and a tablespace can only belong to one database. All database objects are stored in the specified table space, but the main storage is the table, so use the table space.
temporary table space
- It is mainly used for querying and storing some buffer data. The main reason for temporary tablespace consumption is the need to sort the intermediate results of queries. Restarting the database can free up temporary tablespaces.
Create tablespace
Set the tablespace name, corresponding file, initial size, and incremental size.
create tablespace tableplacename datafile '/data/oracle/BGTEST.DBF' size 512M autoextend on next 24M permanent online;
Create user
create user username identified by password default tablespace tableplacename; -- Directly give dba permissions, or custom permissions grant dba to tableplacename;
Other operations:
1. First, create a (new) user:
create user username identified by password;
username: the username of the new username
password: the new user's password
It is also possible not to create a new user, but still use the previous user, such as: continue to use the scott user
2. Create a table space:
create tablespace tablespacename datafile 'd:\data.dbf' size xxxm;
tablespacename: the name of the tablespace
d:\data.dbf': the storage location of the table space
The size of the xxx table space, the unit of m is mega (M)
3. Assign space to users:
alert user username default tablespace tablespacename;
Assign the tablespace named tablespacename to username
4. Authorize the user:
grant create session, create table, unlimited tablespace to username;
5. Then log in with the user created by the landlord himself, and create a table after logging in.
conn username/password; 6. Check the service name env | grep SID 7. Grant dba permission grant dba to username 7. Use the above username, password, and sid to log in to plsql
SQL executed in each step: (sjzx is the database name, user name, password, table space name)
(1) create user sjzx identified by sjzx
(2) create tablespace sjzx datafile 'D:\db\app\oradata\orcl\sjzx.dbf'
size 100m
autoextend on next 32m maxsize 2048m
(3) alter user sjzx default tablespace sjzx
(4) grant create session, create table, unlimited tablespace to sjzx
1. Create a user
create user user_name identified by "user_password"
default tablespace tbs_name
temporary tablespace temp profile DEFAULT;
2. Authorization
grant connect to user_name;
grant create indextype to user_name;
grant create job to user_name;
grant create materialized view to user_name;
grant create procedure to user_name;
grant create public synonym to user_name;
grant create sequence to user_name;
grant create session to user_name;
grant create table to user_name;
grant create trigger to user_name;
grant create type to user_name;
grant create view to user_name;
grant unlimited tablespace to user_name;
alter user user_name quota unlimited on tbs_name;
#In the oracle command line, view the location of the oracle tablespace data file
select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;
original